<?php

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

return new class extends Migration
{
    public function up(): void
    {
        Schema::table('reconcile_jobs', function (Blueprint $table): void {
            $table->foreignId('admin_site_id')
                ->nullable()
                ->after('admin_user_id')
                ->constrained('admin_sites')
                ->nullOnDelete();
        });

        if (DB::getDriverName() === 'pgsql') {
            DB::statement(<<<'SQL'
                UPDATE reconcile_jobs AS rj
                SET admin_site_id = sub.admin_site_id
                FROM (
                    SELECT ri.reconcile_job_id, MIN(asite.id) AS admin_site_id
                    FROM reconcile_items AS ri
                    INNER JOIN transfer_orders AS tord ON tord.transfer_no = ri.side_a_ref
                    INNER JOIN players AS p ON p.id = tord.player_id
                    INNER JOIN admin_sites AS asite ON asite.code = p.site_code
                    GROUP BY ri.reconcile_job_id
                ) AS sub
                WHERE rj.id = sub.reconcile_job_id
                  AND rj.admin_site_id IS NULL
                SQL);
        }
    }

    public function down(): void
    {
        Schema::table('reconcile_jobs', function (Blueprint $table): void {
            $table->dropConstrainedForeignId('admin_site_id');
        });
    }
};
