<?php

declare(strict_types=1);

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

/**
 * M:N pivot между projects (tenant) и supplier_projects (SaaS, shared) — v8.26.
 *
 * Заменяет 3 FK-слота projects.supplier_b{1,2,3}_project_id (которые не вмещают
 * per-субъект модель: N субъектов × до 3 платформ = до 3N связей).
 * SaaS-level (без RLS, как supplier_projects): пишется sync-флоу, читается
 * sharing-флоу через BYPASSRLS-роль crm_supplier_worker.
 */
return new class extends Migration
{
    public function up(): void
    {
        $exists = DB::selectOne("SELECT to_regclass('public.project_supplier_links') AS r");
        if ($exists !== null && $exists->r !== null) {
            return;
        }

        DB::unprepared(<<<'SQL'
            CREATE TABLE project_supplier_links (
                id BIGSERIAL PRIMARY KEY,
                project_id BIGINT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
                supplier_project_id BIGINT NOT NULL REFERENCES supplier_projects(id) ON DELETE CASCADE,
                platform VARCHAR(4) NOT NULL,
                subject_code SMALLINT,
                created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
                CONSTRAINT chk_psl_platform CHECK (platform IN ('B1', 'B2', 'B3')),
                CONSTRAINT uq_psl_project_supplier UNIQUE (project_id, supplier_project_id)
            );

            CREATE INDEX idx_psl_supplier_project ON project_supplier_links (supplier_project_id);
            CREATE INDEX idx_psl_project ON project_supplier_links (project_id);
        SQL);
    }

    public function down(): void
    {
        DB::statement('DROP TABLE IF EXISTS project_supplier_links');
    }
};
