<?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 → пропускаем.
        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) {
            // На окружениях без роли — продолжаем как postgres superuser.
        }

        DB::unprepared(<<<'SQL'
            CREATE TABLE IF NOT EXISTS project_routing_snapshots (
                snapshot_date      DATE        NOT NULL,
                project_id         BIGINT      NOT NULL,
                tenant_id          BIGINT      NOT NULL,
                daily_limit        INT         NOT NULL CHECK (daily_limit >= 0),
                delivery_days_mask INT         NOT NULL CHECK (delivery_days_mask BETWEEN 0 AND 127),
                regions            INT[]       NOT NULL DEFAULT '{}',
                signal_type        TEXT        NOT NULL CHECK (signal_type IN ('call','site','sms')),
                signal_identifier  TEXT,
                sms_senders        JSONB,
                sms_keyword        TEXT,
                expected_volume    INT         NOT NULL CHECK (expected_volume >= 0),
                delivered_count    INT         NOT NULL DEFAULT 0 CHECK (delivered_count >= 0),
                created_at         TIMESTAMP   NOT NULL DEFAULT NOW(),
                PRIMARY KEY (snapshot_date, project_id),
                FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
                -- NB: НЕ ссылаемся на projects(id) — проект может быть удалён,
                --     а snapshot должен пережить (хвост слепка ещё летит).
            ) PARTITION BY RANGE (snapshot_date);

            CREATE INDEX IF NOT EXISTS project_routing_snapshots_tenant_date_idx
                ON project_routing_snapshots (tenant_id, snapshot_date);
            CREATE INDEX IF NOT EXISTS project_routing_snapshots_signal_idx
                ON project_routing_snapshots (snapshot_date, signal_type, lower(signal_identifier));

            ALTER TABLE project_routing_snapshots ENABLE ROW LEVEL SECURITY;
            -- Idempotent: drop policy first так как PG не знает CREATE POLICY IF NOT EXISTS,
            -- а migrate:fresh (schema.sql уже создал политику) иначе упадёт.
            DROP POLICY IF EXISTS project_routing_snapshots_tenant_isolation ON project_routing_snapshots;
            CREATE POLICY project_routing_snapshots_tenant_isolation
                ON project_routing_snapshots
                USING (tenant_id = current_setting('app.current_tenant_id', true)::bigint);

            GRANT SELECT, INSERT, UPDATE ON project_routing_snapshots TO crm_app_user;
            GRANT SELECT, INSERT, UPDATE, DELETE ON project_routing_snapshots TO crm_supplier_worker;

            -- Партиция для текущего месяца (создаётся также через partitions:create-months).
            CREATE TABLE IF NOT EXISTS project_routing_snapshots_y2026_m05
                PARTITION OF project_routing_snapshots
                FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');
            CREATE TABLE IF NOT EXISTS project_routing_snapshots_y2026_m06
                PARTITION OF project_routing_snapshots
                FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');
        SQL);

        // Регистрация в retention (system_settings).
        $exists = DB::table('system_settings')
            ->where('key', 'partition_retention_months_project_routing_snapshots')
            ->exists();
        if (! $exists) {
            DB::table('system_settings')->insert([
                'key' => 'partition_retention_months_project_routing_snapshots',
                'value' => '3',
                'type' => 'int',
                'description' => 'Retention в месяцах для project_routing_snapshots (90 дней)',
                '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) {
            // На окружениях без роли — продолжаем как postgres superuser.
        }
        DB::statement('DROP TABLE IF EXISTS project_routing_snapshots CASCADE');
        DB::table('system_settings')->where('key', 'partition_retention_months_project_routing_snapshots')->delete();
    }
};
