<?php

declare(strict_types=1);

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

/**
 * Per-субъект supplier_projects (эпик переделки миграции проектов, v8.26).
 *
 * +subject_code SMALLINT NULL (1..89 субъект РФ; NULL = пул «Вся РФ»).
 * Старый unique (platform, unique_key) → (platform, unique_key, subject_code)
 * NULLS NOT DISTINCT — пул «Вся РФ» уникален per (platform, unique_key).
 *
 * Guard: migrate:fresh грузит schema.sql v8.26 (delta уже там) до миграций.
 */
return new class extends Migration
{
    public function up(): void
    {
        if (! Schema::hasColumn('supplier_projects', 'subject_code')) {
            DB::statement('ALTER TABLE supplier_projects ADD COLUMN subject_code SMALLINT');
        }

        DB::statement(<<<'SQL'
            DO $$ BEGIN
              IF NOT EXISTS (
                SELECT 1 FROM pg_constraint WHERE conname = 'chk_supplier_projects_subject_code'
              ) THEN
                ALTER TABLE supplier_projects
                  ADD CONSTRAINT chk_supplier_projects_subject_code
                  CHECK (subject_code IS NULL OR (subject_code BETWEEN 1 AND 89)) NOT VALID;
              END IF;
            END $$;
        SQL);
        DB::statement('ALTER TABLE supplier_projects VALIDATE CONSTRAINT chk_supplier_projects_subject_code');

        DB::statement('DROP INDEX IF EXISTS supplier_projects_platform_unique_key_unique');
        DB::statement(
            'CREATE UNIQUE INDEX IF NOT EXISTS supplier_projects_platform_key_subject_unique '
            .'ON supplier_projects (platform, unique_key, subject_code) NULLS NOT DISTINCT'
        );

        DB::statement(
            'COMMENT ON COLUMN supplier_projects.subject_code IS '
            ."'Субъект РФ 1..89 (resources/js/constants/regions.ts). NULL = пул «Вся РФ». Эпик миграции проектов v8.26.'"
        );
    }

    public function down(): void
    {
        DB::statement('DROP INDEX IF EXISTS supplier_projects_platform_key_subject_unique');
        DB::statement(
            'CREATE UNIQUE INDEX IF NOT EXISTS supplier_projects_platform_unique_key_unique '
            .'ON supplier_projects (platform, unique_key)'
        );
        DB::statement('ALTER TABLE supplier_projects DROP CONSTRAINT IF EXISTS chk_supplier_projects_subject_code');

        if (Schema::hasColumn('supplier_projects', 'subject_code')) {
            Schema::table('supplier_projects', fn ($t) => $t->dropColumn('subject_code'));
        }
    }
};
