<?php

use App\Sync\SyncRegistry;
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Str;

/**
 * Adds the stable cross-database identity column `sync_uuid` to every synced
 * table, backfills existing rows, and enforces global uniqueness. Also adds a
 * `company_id` to `sales` (which lacks one) so sales can be scoped per company
 * during pull.
 */
return new class extends Migration
{
    public function up(): void
    {
        // sales has no company_id in the legacy schema; add it so device sales
        // can be scoped to a company on the server.
        if (Schema::hasTable('sales') && !Schema::hasColumn('sales', 'company_id')) {
            Schema::table('sales', function (Blueprint $table) {
                $table->unsignedInteger('company_id')->nullable()->after('id');
            });

            // Best-effort backfill from the sale's creating user.
            DB::statement(
                'UPDATE sales s JOIN users u ON u.id = s.create_user SET s.company_id = u.company_id WHERE s.company_id IS NULL'
            );
        }

        foreach (SyncRegistry::order() as $table) {
            if (!Schema::hasTable($table)) {
                continue;
            }

            if (!Schema::hasColumn($table, 'sync_uuid')) {
                Schema::table($table, function (Blueprint $t) {
                    $t->char('sync_uuid', 36)->nullable();
                });
            }

            // Backfill any rows still missing a uuid, in chunks.
            do {
                $ids = DB::table($table)->whereNull('sync_uuid')->limit(500)->pluck('id');
                foreach ($ids as $id) {
                    DB::table($table)->where('id', $id)->update(['sync_uuid' => (string) Str::uuid()]);
                }
            } while ($ids->count() > 0);

            $this->addUniqueIndex($table);
        }
    }

    public function down(): void
    {
        foreach (SyncRegistry::order() as $table) {
            if (!Schema::hasTable($table) || !Schema::hasColumn($table, 'sync_uuid')) {
                continue;
            }
            $this->dropUniqueIndex($table);
            Schema::table($table, function (Blueprint $t) {
                $t->dropColumn('sync_uuid');
            });
        }

        if (Schema::hasTable('sales') && Schema::hasColumn('sales', 'company_id')) {
            Schema::table('sales', function (Blueprint $table) {
                $table->dropColumn('company_id');
            });
        }
    }

    private function indexName(string $table): string
    {
        return $table . '_sync_uuid_unique';
    }

    private function addUniqueIndex(string $table): void
    {
        $index = $this->indexName($table);
        try {
            $exists = DB::table('information_schema.statistics')
                ->where('table_schema', DB::getDatabaseName())
                ->where('table_name', $table)
                ->where('index_name', $index)
                ->exists();
            if (!$exists) {
                DB::statement("ALTER TABLE `{$table}` ADD UNIQUE `{$index}` (`sync_uuid`)");
            }
        } catch (\Throwable $e) {
            // Index creation is best-effort; a duplicate/legacy index should not
            // abort the whole migration.
        }
    }

    private function dropUniqueIndex(string $table): void
    {
        $index = $this->indexName($table);
        try {
            DB::statement("ALTER TABLE `{$table}` DROP INDEX `{$index}`");
        } catch (\Throwable $e) {
            // ignore
        }
    }
};
