<?php

declare(strict_types=1);

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

return new class extends Migration
{
    public function up(): void
    {
        // Всё DDL через pgsql_supplier — избегаем deadlock при смешивании соединений.
        // Laravel оборачивает миграцию в транзакцию на дефолтном pgsql; ALTER TABLE tenants
        // на pgsql + CREATE TABLE с FK на tenants через pgsql_supplier = взаимная блокировка.
        // На dev pgsql_supplier = postgres superuser (те же права), на prod — явные GRANT'ы ниже.
        $supplier = DB::connection('pgsql_supplier');

        // Флаги заморозки. tenant-level — пассивный износ; project-level — точечная перегрузка.
        $supplier->statement('ALTER TABLE tenants ADD COLUMN IF NOT EXISTS frozen_by_balance_at TIMESTAMPTZ NULL');
        $supplier->statement('ALTER TABLE projects ADD COLUMN IF NOT EXISTS preflight_blocked_at TIMESTAMPTZ NULL');

        $supplier->statement('CREATE INDEX IF NOT EXISTS tenants_frozen_by_balance_idx ON tenants (frozen_by_balance_at) WHERE frozen_by_balance_at IS NOT NULL');
        $supplier->statement('CREATE INDEX IF NOT EXISTS projects_preflight_blocked_idx ON projects (preflight_blocked_at) WHERE preflight_blocked_at IS NOT NULL');

        // Журнал заморозок/разморозок. Создаём через pgsql_supplier (урок Спека B — prod-роли).
        $supplier->statement(<<<'SQL'
            CREATE TABLE IF NOT EXISTS balance_freeze_log (
                id BIGSERIAL PRIMARY KEY,
                tenant_id BIGINT NOT NULL REFERENCES tenants(id),
                event_type VARCHAR(30) NOT NULL,
                triggered_by VARCHAR(30) NOT NULL,
                balance_rub_at_event DECIMAL(12,2) NOT NULL,
                required_leads INTEGER NOT NULL,
                capacity_leads INTEGER NOT NULL,
                total_daily_limit INTEGER NOT NULL,
                details JSONB,
                created_at TIMESTAMPTZ NOT NULL DEFAULT now()
            )
        SQL);
        $supplier->statement('ALTER TABLE balance_freeze_log ENABLE ROW LEVEL SECURITY');
        // Idempotent: drop policy first so migrate:fresh (schema.sql already created it) does not fail.
        $supplier->statement('DROP POLICY IF EXISTS tenant_isolation ON balance_freeze_log');
        $supplier->statement(<<<'SQL'
            CREATE POLICY tenant_isolation ON balance_freeze_log
            USING (tenant_id = current_setting('app.current_tenant_id', true)::bigint)
        SQL);
        $supplier->statement('CREATE INDEX IF NOT EXISTS balance_freeze_log_tenant_idx ON balance_freeze_log (tenant_id, created_at DESC)');

        // Гранты для 4 ролей (mirror webhook_dedup_keys / supplier_lead_deliveries).
        foreach (['crm_app_user', 'crm_supplier_worker', 'crm_migrator', 'crm_admin_user'] as $role) {
            $supplier->statement(<<<SQL
                DO \$\$
                BEGIN
                    IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = '{$role}') THEN
                        GRANT SELECT, INSERT ON balance_freeze_log TO {$role};
                        GRANT USAGE, SELECT ON SEQUENCE balance_freeze_log_id_seq TO {$role};
                    END IF;
                END
                \$\$
            SQL);
        }
    }

    public function down(): void
    {
        $supplier = DB::connection('pgsql_supplier');
        $supplier->statement('DROP TABLE IF EXISTS balance_freeze_log');
        $supplier->statement('ALTER TABLE projects DROP COLUMN IF EXISTS preflight_blocked_at');
        $supplier->statement('ALTER TABLE tenants DROP COLUMN IF EXISTS frozen_by_balance_at');
    }
};
