<?php

namespace App\Support;

use App\Models\SyncQueue;
use Illuminate\Support\Carbon;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Str;
use Throwable;

/**
 * Keeps the local mirror and Aiven in step.
 *
 * Two directions, in this order:
 *   push() — replays writes made while the cloud was offline, oldest first.
 *   pull() — copies Aiven's current rows into the mirror, so going offline
 *            later starts from a complete picture.
 *
 * Aiven stays the source of truth: on a genuine conflict the cloud row wins,
 * except for rows the queue proves were changed locally and never sent.
 */
class SyncManager
{
    /** Tables mirrored, parents before children (category_id points at categories). */
    public const TABLES = ['categories', 'expenses'];

    public static function run(): array
    {
        if (! DbRouter::ping()) {
            return ['ok' => false, 'reason' => 'primary-offline', 'pushed' => 0, 'pulled' => 0];
        }

        $pushed = static::push();
        $pulled = static::pull();

        return ['ok' => true, 'pushed' => $pushed, 'pulled' => $pulled];
    }

    /** Replays the offline queue against Aiven. Returns how many entries went through. */
    public static function push(): int
    {
        $done = 0;

        foreach (SyncQueue::pending()->get() as $entry) {
            try {
                static::apply($entry);

                $entry->update(['synced_at' => Carbon::now(), 'last_error' => null]);
                $done++;
            } catch (Throwable $e) {
                // Leave it queued — a later run retries it.
                $entry->update([
                    'attempts' => $entry->attempts + 1,
                    'last_error' => Str::limit($e->getMessage(), 500),
                ]);

                Log::error('Sync push failed.', [
                    'uuid' => $entry->row_uuid,
                    'action' => $entry->action,
                    'error' => $e->getMessage(),
                ]);
            }
        }

        return $done;
    }

    /** Applies one queued change to the cloud. */
    protected static function apply(SyncQueue $entry): void
    {
        $cloud = DB::connection(DbRouter::PRIMARY);
        $row = $cloud->table($entry->table_name)->where('uuid', $entry->row_uuid);

        if ($entry->action === 'delete') {
            $row->delete();

            return;
        }

        $values = static::resolveForeignKeys($entry->table_name, $entry->payload ?? [], DbRouter::PRIMARY);
        $values['uuid'] = $entry->row_uuid;

        // create and update are both an upsert: the row may already exist from
        // another device, or may have been removed there.
        if ($row->exists()) {
            $row->update($values);

            return;
        }

        $cloud->table($entry->table_name)->insert($values);
    }

    /**
     * Copies Aiven's rows into the mirror.
     *
     * Rows still waiting in the queue are skipped, so a pull cannot overwrite a
     * local change that has not been pushed yet.
     */
    public static function pull(): int
    {
        $copied = 0;
        $held = SyncQueue::pending()->pluck('row_uuid')->all();

        foreach (static::TABLES as $table) {
            $rows = DB::connection(DbRouter::PRIMARY)->table($table)->get();
            $local = DB::connection(DbRouter::FALLBACK);

            foreach ($rows as $row) {
                $values = static::resolveForeignKeys($table, (array) $row, DbRouter::FALLBACK);

                unset($values['id']);

                if (in_array($values['uuid'] ?? null, $held, true)) {
                    continue;
                }

                $existing = $local->table($table)->where('uuid', $values['uuid']);

                $existing->exists()
                    ? $existing->update($values)
                    : $local->table($table)->insert($values);

                $copied++;
            }

            // Anything the cloud no longer has was deleted elsewhere.
            $cloudUuids = $rows->pluck('uuid')->filter()->all();

            $local->table($table)
                ->when($cloudUuids, fn ($q) => $q->whereNotIn('uuid', $cloudUuids))
                ->whereNotIn('uuid', $held ?: [''])
                ->delete();
        }

        return $copied;
    }

    /**
     * Translates a row's foreign keys for the target connection.
     *
     * Ids are per-database, so expenses carry category_uuid between the two and
     * it is turned back into a local category_id here.
     */
    protected static function resolveForeignKeys(string $table, array $values, string $target): array
    {
        if ($table !== 'expenses') {
            return $values;
        }

        $uuid = $values['category_uuid'] ?? null;
        unset($values['category_uuid']);

        // Rows read straight from a database still carry the source's id, which
        // means nothing on the other side — translate through the category uuid.
        if ($uuid === null && array_key_exists('category_id', $values) && $values['category_id']) {
            $source = $target === DbRouter::PRIMARY ? DbRouter::FALLBACK : DbRouter::PRIMARY;

            $uuid = DB::connection($source)->table('categories')
                ->where('id', $values['category_id'])->value('uuid');
        }

        $values['category_id'] = $uuid
            ? DB::connection($target)->table('categories')->where('uuid', $uuid)->value('id')
            : null;

        return $values;
    }
}
