<?php

namespace App\Sync;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

/**
 * Portable sync helpers shared by the server (ingest/produce) and the desktop
 * (produce/apply). Everything goes through the query builder (DB::table) on
 * purpose so that Eloquent global scopes (e.g. CompanyScope), soft-delete
 * filtering and model observers never interfere with raw replication, and so
 * applying a pulled change never echoes back into an outbox.
 *
 * Rows are matched across databases only by `sync_uuid`. Foreign keys are carried
 * as companion `<column>__uuid` values pointing at the parent's `sync_uuid`, and
 * translated back to local integer ids on apply.
 *
 * This file is intentionally identical on the API (server) and desktop apps.
 */
class SyncEngine
{
    /** @var array<string,array<string,int|null>> parent table => [uuid => local id] */
    protected array $idCache = [];

    /** @var array<string,array<int,string|null>> parent table => [local id => uuid] */
    protected array $uuidCache = [];

    /** @var array<string,array<string>> table => column list */
    protected array $columnCache = [];

    public function columns(string $table): array
    {
        if (!isset($this->columnCache[$table])) {
            $this->columnCache[$table] = Schema::getColumnListing($table);
        }

        return $this->columnCache[$table];
    }

    /**
     * Look up a parent row's local id from its sync_uuid.
     */
    public function resolveId(string $parentTable, ?string $uuid): ?int
    {
        if ($uuid === null || $uuid === '') {
            return null;
        }

        if (array_key_exists($uuid, $this->idCache[$parentTable] ?? [])) {
            return $this->idCache[$parentTable][$uuid];
        }

        $id = DB::table($parentTable)->where('sync_uuid', $uuid)->value('id');
        $id = $id !== null ? (int) $id : null;
        $this->idCache[$parentTable][$uuid] = $id;

        return $id;
    }

    /**
     * Look up a parent row's sync_uuid from its local id.
     */
    public function resolveUuid(string $parentTable, $id): ?string
    {
        if ($id === null || $id === '' || (int) $id === 0) {
            return null;
        }

        $id = (int) $id;
        if (array_key_exists($id, $this->uuidCache[$parentTable] ?? [])) {
            return $this->uuidCache[$parentTable][$id];
        }

        $uuid = DB::table($parentTable)->where('id', $id)->value('sync_uuid');
        $this->uuidCache[$parentTable][$id] = $uuid;

        return $uuid;
    }

    /**
     * Forget cached ids/uuids for a table (call after inserting new rows so later
     * children in the same batch can resolve freshly-created parents).
     */
    public function forget(string $table): void
    {
        unset($this->idCache[$table], $this->uuidCache[$table]);
    }

    /**
     * Convert a raw DB row (assoc array) into a transport payload:
     *  - drops the local integer `id`
     *  - replaces each foreign-key column with a `<col>__uuid` pointing at the parent
     */
    public function toPayload(string $table, array $row): array
    {
        $config = SyncRegistry::config($table) ?? ['fks' => []];
        $fks = $config['fks'] ?? [];

        $out = $row;
        unset($out['id']);

        foreach ($fks as $col => [$parentTable, $hard]) {
            if (!array_key_exists($col, $row)) {
                continue;
            }
            $out[$col . '__uuid'] = $this->resolveUuid($parentTable, $row[$col]);
            unset($out[$col]);
        }

        return $out;
    }

    /**
     * Apply a transport payload to the local DB: resolve FK uuids to local ids,
     * then upsert by sync_uuid using last-writer-wins on updated_at.
     *
     * @param array $overrides columns to force after FK resolution (e.g. company_id)
     * @return array{status:string, reason?:string}
     */
    public function applyPayload(string $table, array $payload, array $overrides = []): array
    {
        $uuid = $payload['sync_uuid'] ?? null;
        if (!$uuid) {
            return ['status' => 'skipped', 'reason' => 'missing sync_uuid'];
        }

        $config = SyncRegistry::config($table) ?? ['fks' => []];
        $fks = $config['fks'] ?? [];
        $data = $payload;

        foreach ($fks as $col => [$parentTable, $hard]) {
            $companion = $col . '__uuid';
            $parentUuid = $data[$companion] ?? null;
            unset($data[$companion]);

            if ($parentUuid === null || $parentUuid === '') {
                $data[$col] = null;
                continue;
            }

            $parentId = $this->resolveId($parentTable, $parentUuid);
            if ($parentId === null) {
                if ($hard) {
                    return ['status' => 'deferred', 'reason' => "missing {$parentTable}:{$parentUuid}"];
                }
                $data[$col] = null;
            } else {
                $data[$col] = $parentId;
            }
        }

        foreach ($overrides as $k => $v) {
            $data[$k] = $v;
        }

        // Keep only real columns for this table.
        $columns = array_flip($this->columns($table));
        $data = array_intersect_key($data, $columns);
        unset($data['id']);

        $existing = DB::table($table)->where('sync_uuid', $uuid)->first();

        if ($existing) {
            if (!$this->incomingWins($existing, $data)) {
                return ['status' => 'applied', 'reason' => 'kept-local'];
            }
            DB::table($table)->where('id', $existing->id)->update($data);

            return ['status' => 'applied', 'reason' => 'updated'];
        }

        // No row with this uuid yet. Both databases were seeded independently, so a
        // pre-existing row may already represent this entity under a different (or
        // null) uuid. Adopt the incoming uuid onto that natural-key match instead of
        // inserting a duplicate.
        $matched = $this->findByMatch($table, $config, $data);
        if ($matched) {
            DB::table($table)->where('id', $matched->id)->update($data);
            $this->forget($table);

            return ['status' => 'applied', 'reason' => 'merged'];
        }

        DB::table($table)->insert($data);
        $this->forget($table);

        return ['status' => 'applied', 'reason' => 'inserted'];
    }

    /**
     * Find a pre-existing row by the table's natural-key `match` columns so an
     * independently-seeded row can adopt the incoming sync_uuid instead of being
     * duplicated. Only rows with a NULL or different uuid are considered.
     */
    protected function findByMatch(string $table, array $config, array $data)
    {
        $matchCols = $config['match'] ?? [];
        if (empty($matchCols)) {
            return null;
        }

        $query = DB::table($table);
        foreach ($matchCols as $col) {
            if (!array_key_exists($col, $data) || $data[$col] === null || $data[$col] === '') {
                // Incomplete natural key -> do not risk a wrong merge.
                return null;
            }
            $query->where($col, $data[$col]);
        }

        return $query->orderBy('id')->first();
    }

    /**
     * Hard-delete a row by sync_uuid (used for outbox delete ops).
     */
    public function deleteByUuid(string $table, string $uuid): array
    {
        DB::table($table)->where('sync_uuid', $uuid)->delete();
        $this->forget($table);

        return ['status' => 'applied', 'reason' => 'deleted'];
    }

    /**
     * Last-writer-wins comparison. Incoming wins unless the existing row has a
     * strictly newer updated_at.
     */
    protected function incomingWins($existing, array $incoming): bool
    {
        $existingTs = $existing->updated_at ?? null;
        $incomingTs = $incoming['updated_at'] ?? null;

        if ($existingTs === null || $incomingTs === null) {
            return true;
        }

        return strtotime((string) $incomingTs) >= strtotime((string) $existingTs);
    }
}
