<?php

declare(strict_types=1);

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

return new class extends Migration
{
    public function up(): void
    {
        $supplier = DB::connection('pgsql_supplier');

        $supplier->statement(<<<'SQL'
            CREATE TABLE IF NOT EXISTS support_requests (
                id         BIGSERIAL PRIMARY KEY,
                tenant_id  BIGINT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
                user_id    BIGINT NOT NULL REFERENCES users(id),
                name       VARCHAR(255) NOT NULL,
                contact    VARCHAR(255) NOT NULL,
                message    TEXT NOT NULL,
                created_at TIMESTAMPTZ NOT NULL DEFAULT now()
            )
        SQL);
        $supplier->statement('CREATE INDEX IF NOT EXISTS idx_support_requests_tenant ON support_requests (tenant_id, created_at DESC)');
        $supplier->statement('ALTER TABLE support_requests ENABLE ROW LEVEL SECURITY');
        // PG не поддерживает CREATE POLICY IF NOT EXISTS → дроп-перед-создание (идемпотентность к schema.sql).
        $supplier->statement('DROP POLICY IF EXISTS support_requests_tenant_isolation ON support_requests');
        $supplier->statement(<<<'SQL'
            CREATE POLICY support_requests_tenant_isolation ON support_requests
                USING (tenant_id = current_setting('app.current_tenant_id', true)::bigint)
        SQL);
        foreach (['crm_app_user', 'crm_supplier_worker'] as $role) {
            $supplier->statement(<<<SQL
                DO \$\$
                BEGIN
                    IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = '{$role}') THEN
                        GRANT SELECT, INSERT ON support_requests TO {$role};
                        GRANT USAGE, SELECT ON SEQUENCE support_requests_id_seq TO {$role};
                    END IF;
                END
                \$\$
            SQL);
        }
    }

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