<?php

declare(strict_types=1);

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

/**
 * Sprint 4 (H1+H2) — историческая миграция лидов §6.
 *
 * H1: новая таблица import_unknown_statuses (tenant-level resolved mappings).
 * H2: enrichment import_log — +5 колонок.
 *
 * Guard'ы: migrate:fresh грузит schema.sql v8.21+ (где delta уже есть) до миграций,
 * поэтому каждый кусок применяется только при отсутствии.
 */
return new class extends Migration
{
    public function up(): void
    {
        foreach ([
            'entity_type' => "ALTER TABLE import_log ADD COLUMN entity_type VARCHAR(20) NOT NULL DEFAULT 'leads' CHECK (entity_type IN ('leads','projects'))",
            'source_system' => "ALTER TABLE import_log ADD COLUMN source_system VARCHAR(50) NOT NULL DEFAULT 'crm.bp-gr.ru'",
            'mapping_config' => 'ALTER TABLE import_log ADD COLUMN mapping_config JSONB',
            'unknown_statuses_count' => 'ALTER TABLE import_log ADD COLUMN unknown_statuses_count INT NOT NULL DEFAULT 0',
            'dry_run' => 'ALTER TABLE import_log ADD COLUMN dry_run BOOLEAN NOT NULL DEFAULT FALSE',
        ] as $column => $ddl) {
            if (! Schema::hasColumn('import_log', $column)) {
                DB::statement($ddl);
            }
        }

        if (! Schema::hasTable('import_unknown_statuses')) {
            DB::statement(<<<'SQL'
                CREATE TABLE import_unknown_statuses (
                    id              BIGSERIAL PRIMARY KEY,
                    tenant_id       BIGINT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
                    import_log_id   BIGINT REFERENCES import_log(id) ON DELETE SET NULL,
                    status_ru       VARCHAR(100) NOT NULL,
                    occurrences     INT NOT NULL DEFAULT 0,
                    mapped_to_slug  VARCHAR(50) REFERENCES lead_statuses(slug),
                    resolved_at     TIMESTAMPTZ,
                    resolved_by     BIGINT REFERENCES users(id),
                    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
                    updated_at      TIMESTAMPTZ,
                    UNIQUE (tenant_id, status_ru)
                )
                SQL);

            DB::statement(
                'CREATE INDEX idx_import_unknown_statuses_unresolved
                 ON import_unknown_statuses (tenant_id) WHERE mapped_to_slug IS NULL'
            );

            DB::statement('ALTER TABLE import_unknown_statuses ENABLE ROW LEVEL SECURITY');
            DB::statement(
                "CREATE POLICY tenant_isolation ON import_unknown_statuses
                 USING (tenant_id = current_setting('app.current_tenant_id')::bigint)"
            );
        }
    }

    public function down(): void
    {
        // down() не симметричен: на проекте rollback применяется только после
        // migrate:fresh (см. add_archived_at_to_projects). Для отката v8.21 —
        // отдельный schema-bump, не эта миграция.
        DB::statement('DROP TABLE IF EXISTS import_unknown_statuses');

        foreach (['entity_type', 'source_system', 'mapping_config', 'unknown_statuses_count', 'dry_run'] as $column) {
            if (Schema::hasColumn('import_log', $column)) {
                Schema::table('import_log', fn ($table) => $table->dropColumn($column));
            }
        }
    }
};
