<?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 (урок SP1: prod-роли + избегаем deadlock смешивания соединений).
        // На dev/testing pgsql_supplier = postgres superuser, на prod — crm_supplier_worker.
        $supplier = DB::connection('pgsql_supplier');

        $supplier->unprepared(<<<'SQL'
            CREATE TABLE IF NOT EXISTS tenant_requisites (
                id                       BIGSERIAL PRIMARY KEY,
                tenant_id                BIGINT NOT NULL UNIQUE REFERENCES tenants(id) ON DELETE CASCADE,
                subject_type             VARCHAR(20) NOT NULL
                                         CHECK (subject_type IN ('individual','sole_proprietor','legal_entity')),
                contact_name             VARCHAR(255) NOT NULL,
                contact_phone            VARCHAR(16)  NOT NULL,
                inn                      VARCHAR(12),
                legal_name               VARCHAR(255),
                kpp                      VARCHAR(9),
                ogrn                     VARCHAR(15),
                legal_address            TEXT,
                bank_name                VARCHAR(255),
                bank_bik                 VARCHAR(9),
                bank_account             VARCHAR(20),
                corr_account             VARCHAR(20),
                dadata_raw               JSONB,
                dadata_synced_at         TIMESTAMPTZ,
                requisites_completed_at  TIMESTAMPTZ,
                created_at               TIMESTAMPTZ NOT NULL DEFAULT NOW(),
                updated_at               TIMESTAMPTZ
            );

            ALTER TABLE tenant_requisites ENABLE ROW LEVEL SECURITY;
            -- migrate:fresh грузит schema.sql первой миграцией (где политика уже есть);
            -- PostgreSQL не поддерживает CREATE POLICY IF NOT EXISTS → дроп-перед-создание.
            DROP POLICY IF EXISTS tenant_requisites_tenant_isolation ON tenant_requisites;
            CREATE POLICY tenant_requisites_tenant_isolation
                ON tenant_requisites
                USING (tenant_id = current_setting('app.current_tenant_id', true)::bigint)
                WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::bigint);
        SQL);

        // GRANT-ы под прод-роли (на dev — superuser, роли могут отсутствовать → guard по pg_roles).
        foreach (['crm_app_user', 'crm_supplier_worker', 'crm_admin_user'] as $role) {
            $grant = $role === 'crm_supplier_worker'
                ? 'SELECT, INSERT, UPDATE, DELETE'
                : 'SELECT, INSERT, UPDATE';
            $supplier->statement(<<<SQL
                DO \$\$
                BEGIN
                    IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = '{$role}') THEN
                        GRANT {$grant} ON tenant_requisites TO {$role};
                        GRANT USAGE, SELECT ON SEQUENCE tenant_requisites_id_seq TO {$role};
                    END IF;
                END
                \$\$
            SQL);
        }
    }

    public function down(): void
    {
        DB::connection('pgsql_supplier')->statement('DROP TABLE IF EXISTS tenant_requisites CASCADE');
    }
};
