<?php

declare(strict_types=1);

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

return new class extends Migration
{
    public function up(): void
    {
        // SET ROLE crm_migrator на проде (postgres superuser может SET ROLE).
        // На dev/testing crm_migrator не имеет GRANT на public schema → RESET ROLE
        // и продолжаем как postgres superuser.
        try {
            DB::statement('SET ROLE crm_migrator');
            $canCreate = DB::selectOne("SELECT has_schema_privilege('crm_migrator', 'public', 'CREATE') AS ok");
            if (! $canCreate || ! $canCreate->ok) {
                DB::statement('RESET ROLE');
            }
        } catch (Throwable) {
            // окружение без роли — продолжаем как superuser
        }

        DB::unprepared(<<<'SQL'
            -- 1. phone_ranges_imports (журнал импортов; на него FK из phone_ranges, создаём первым)
            CREATE TABLE IF NOT EXISTS phone_ranges_imports (
                id              BIGSERIAL PRIMARY KEY,
                imported_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
                source_url      TEXT NOT NULL,
                rows_inserted   INTEGER NOT NULL DEFAULT 0,
                rows_updated    INTEGER NOT NULL DEFAULT 0,
                checksum_sha256 TEXT NOT NULL,
                status          TEXT NOT NULL DEFAULT 'in_progress'
                    CHECK (status IN ('in_progress','completed','failed','rolled_back')),
                error           TEXT,
                completed_at    TIMESTAMPTZ
            );
            COMMENT ON TABLE phone_ranges_imports IS
                'Журнал импортов реестра Россвязи (idempotency по checksum_sha256, atomic-swap откат).';

            -- 2. phone_ranges (реестр диапазонов Россвязи; SaaS-level, без RLS — публичные данные)
            CREATE TABLE IF NOT EXISTS phone_ranges (
                id                BIGSERIAL PRIMARY KEY,
                def_code          SMALLINT NOT NULL,
                from_num          BIGINT NOT NULL,
                to_num            BIGINT NOT NULL,
                operator          TEXT NOT NULL,
                region            TEXT NOT NULL,
                region_normalized TEXT,
                subject_code      SMALLINT,
                imported_at       TIMESTAMPTZ NOT NULL,
                import_id         BIGINT NOT NULL REFERENCES phone_ranges_imports(id),
                CONSTRAINT chk_phone_ranges_def_code CHECK (def_code BETWEEN 300 AND 999),
                CONSTRAINT chk_phone_ranges_subject_code CHECK (subject_code IS NULL OR subject_code BETWEEN 1 AND 89),
                CONSTRAINT chk_phone_ranges_range_valid CHECK (from_num <= to_num)
            );
            CREATE INDEX IF NOT EXISTS idx_phone_ranges_lookup ON phone_ranges (def_code, from_num, to_num);
            COMMENT ON TABLE phone_ranges IS
                'Реестр диапазонов нумерации Россвязи (rossvyaz.gov.ru). Локальный fallback для LeadRegionResolver. Обновляется ежемесячным cron-импортом.';

            GRANT SELECT ON phone_ranges, phone_ranges_imports TO crm_app_user, crm_supplier_worker;

            -- 3. lead_region_resolution_log (SaaS-level, партиционирован по received_at, паттерн activity_log)
            CREATE TABLE IF NOT EXISTS lead_region_resolution_log (
                id                       BIGSERIAL,
                supplier_lead_id         BIGINT NOT NULL,
                received_at              TIMESTAMPTZ NOT NULL,
                phone_masked             TEXT NOT NULL,
                subject_code_resolved    SMALLINT,
                subject_code_from_tag    SMALLINT,
                region_source            TEXT NOT NULL
                    CHECK (region_source IN ('dadata','rossvyaz','tag','unknown')),
                dadata_qc                SMALLINT,
                dadata_provider          TEXT,
                dadata_type              TEXT,
                dadata_response_masked   JSONB,
                rossvyaz_matched         BOOLEAN NOT NULL DEFAULT FALSE,
                actual_subject_code      SMALLINT
                    CHECK (actual_subject_code IS NULL OR actual_subject_code BETWEEN 1 AND 89),
                substituted_subject_code SMALLINT
                    CHECK (substituted_subject_code IS NULL OR substituted_subject_code BETWEEN 1 AND 89),
                routing_step             SMALLINT
                    CHECK (routing_step IS NULL OR routing_step BETWEEN 1 AND 3),
                phone_operator           TEXT,
                cache_hit                BOOLEAN NOT NULL DEFAULT FALSE,
                duration_ms              INTEGER,
                resolved_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
                PRIMARY KEY (id, received_at)
            ) PARTITION BY RANGE (received_at);

            CREATE INDEX IF NOT EXISTS idx_lrrl_lead_id ON lead_region_resolution_log (supplier_lead_id);
            CREATE INDEX IF NOT EXISTS idx_lrrl_source ON lead_region_resolution_log (region_source, received_at);
            COMMENT ON TABLE lead_region_resolution_log IS
                'Аудит каждого резолва региона лида (источник, qc, оператор, шаг каскада). Партиции помесячно по received_at (MonthlyPartitionManager).';

            GRANT SELECT, INSERT ON lead_region_resolution_log TO crm_supplier_worker;
            GRANT SELECT ON lead_region_resolution_log TO crm_app_user;

            -- Стартовые партиции (далее их подхватывает partitions:create-months после Task 1.2).
            CREATE TABLE IF NOT EXISTS lead_region_resolution_log_y2026_m05
                PARTITION OF lead_region_resolution_log
                FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');
            CREATE TABLE IF NOT EXISTS lead_region_resolution_log_y2026_m06
                PARTITION OF lead_region_resolution_log
                FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

            -- 4. supplier_leads: +4 колонки (denormalized display + persistent idempotency для retry).
            ALTER TABLE supplier_leads
                ADD COLUMN IF NOT EXISTS resolved_subject_code SMALLINT
                    CHECK (resolved_subject_code IS NULL OR resolved_subject_code BETWEEN 1 AND 89),
                ADD COLUMN IF NOT EXISTS region_source TEXT
                    CHECK (region_source IN ('dadata','rossvyaz','tag','unknown')),
                ADD COLUMN IF NOT EXISTS dadata_qc SMALLINT,
                ADD COLUMN IF NOT EXISTS phone_operator TEXT;

            -- 5. deals: +2 колонки (UI-карточка + флаг подмены региона).
            ALTER TABLE deals
                ADD COLUMN IF NOT EXISTS phone_operator TEXT,
                ADD COLUMN IF NOT EXISTS region_substituted BOOLEAN NOT NULL DEFAULT FALSE;
        SQL);

        // Регистрация retention для lead_region_resolution_log (system_settings, 12 месяцев ≈ 365 дней).
        $exists = DB::table('system_settings')
            ->where('key', 'partition_retention_months_lead_region_resolution_log')
            ->exists();
        if (! $exists) {
            DB::table('system_settings')->insert([
                'key' => 'partition_retention_months_lead_region_resolution_log',
                'value' => '12',
                'type' => 'int',
                'description' => 'Retention в месяцах для lead_region_resolution_log (~365 дней)',
                'updated_at' => now(),
            ]);
        }
    }

    public function down(): void
    {
        try {
            DB::statement('SET ROLE crm_migrator');
            $canCreate = DB::selectOne("SELECT has_schema_privilege('crm_migrator', 'public', 'CREATE') AS ok");
            if (! $canCreate || ! $canCreate->ok) {
                DB::statement('RESET ROLE');
            }
        } catch (Throwable) {
            // окружение без роли — продолжаем как superuser
        }

        DB::unprepared(<<<'SQL'
            ALTER TABLE deals
                DROP COLUMN IF EXISTS phone_operator,
                DROP COLUMN IF EXISTS region_substituted;

            ALTER TABLE supplier_leads
                DROP COLUMN IF EXISTS resolved_subject_code,
                DROP COLUMN IF EXISTS region_source,
                DROP COLUMN IF EXISTS dadata_qc,
                DROP COLUMN IF EXISTS phone_operator;

            DROP TABLE IF EXISTS lead_region_resolution_log CASCADE;
            DROP TABLE IF EXISTS phone_ranges CASCADE;
            DROP TABLE IF EXISTS phone_ranges_imports CASCADE;
        SQL);

        DB::table('system_settings')
            ->where('key', 'partition_retention_months_lead_region_resolution_log')
            ->delete();
    }
};
