<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;

return new class extends Migration
{
    public function up(): void
    {
        // Only run for PostgreSQL
        $driver = DB::getDriverName();
        if ($driver !== 'pgsql') {
            return; // No-op for other databases
        }

        // Collect all tables in public schema that have an 'id' column
        $tables = DB::select(<<<'SQL'
            SELECT DISTINCT table_name
            FROM information_schema.columns
            WHERE table_schema = 'public'
              AND column_name = 'id'
            ORDER BY table_name
        SQL);

        foreach ($tables as $row) {
            $table = $row->table_name;
            try {
                // Determine sequence name (returns NULL if not serial/identity)
                $sequence = DB::selectOne("SELECT pg_get_serial_sequence(?, 'id') AS seq", [$table]);
                if (! $sequence || ! $sequence->seq) {
                    continue; // Skip tables without a sequence-backed 'id'
                }

                // Get current max id
                $maxIdRow = DB::selectOne("SELECT COALESCE(MAX(id), 0) AS max_id FROM \"{$table}\"");
                $maxId = (int) $maxIdRow->max_id;

                // Set sequence current value to max(id) so next nextval returns max+1
                DB::statement('SELECT setval(?, ?, true)', [$sequence->seq, $maxId]);
            } catch (\Throwable $e) {
                // Swallow and continue; optionally log if needed
                // Log::warning("Sequence sync failed for table {$table}: " . $e->getMessage());
            }
        }
    }

    public function down(): void
    {
        // Intentionally left blank (cannot reliably revert sequence positions)
    }
};
