<?php

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

return new class extends Migration
{
    private const CURRENT_TARGET_TYPES = [
        'message',
        'conversation',
        'user',
        'story',
        'collection',
        'collection_comment',
        'collection_submission',
        'nova_card',
        'nova_card_challenge',
        'nova_card_challenge_entry',
    ];

    private const PREVIOUS_TARGET_TYPES = [
        'message',
        'conversation',
        'user',
        'story',
        'collection',
        'collection_comment',
        'collection_submission',
    ];

    public function up(): void
    {
        $this->syncTargetTypes(self::CURRENT_TARGET_TYPES);
    }

    public function down(): void
    {
        $this->syncTargetTypes(self::PREVIOUS_TARGET_TYPES);
    }

    /**
     * @param  array<int, string>  $targetTypes
     */
    private function syncTargetTypes(array $targetTypes): void
    {
        if (! Schema::hasTable('reports') || ! Schema::hasColumn('reports', 'target_type')) {
            return;
        }

        $driver = DB::getDriverName();

        if ($driver === 'mysql') {
            $allowed = implode("','", $targetTypes);
            DB::statement("ALTER TABLE reports MODIFY target_type ENUM('{$allowed}') NOT NULL");

            return;
        }

        if ($driver === 'sqlite') {
            $this->rebuildSqliteReportsTable($targetTypes);
        }
    }

    /**
     * @param  array<int, string>  $targetTypes
     */
    private function rebuildSqliteReportsTable(array $targetTypes): void
    {
        $quotedTargetTypes = collect($targetTypes)
            ->map(fn (string $type): string => "'{$type}'")
            ->implode(', ');

        DB::statement('DROP TABLE IF EXISTS reports_temp');

        DB::statement(
            <<<SQL
            CREATE TABLE reports_temp (
                id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
                reporter_id INTEGER NOT NULL,
                target_type VARCHAR NOT NULL CHECK (target_type IN ({$quotedTargetTypes})),
                target_id INTEGER NOT NULL,
                reason VARCHAR(120) NOT NULL,
                details TEXT NULL,
                status VARCHAR NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'reviewing', 'closed')),
                created_at DATETIME NULL,
                updated_at DATETIME NULL,
                FOREIGN KEY (reporter_id) REFERENCES users(id) ON DELETE CASCADE
            )
            SQL
        );

        DB::statement(
            'INSERT INTO reports_temp (id, reporter_id, target_type, target_id, reason, details, status, created_at, updated_at) '
            .'SELECT id, reporter_id, target_type, target_id, reason, details, status, created_at, updated_at FROM reports'
        );

        DB::statement('DROP TABLE reports');
        DB::statement('ALTER TABLE reports_temp RENAME TO reports');
        DB::statement('CREATE INDEX reports_target_type_target_id_index ON reports (target_type, target_id)');
        DB::statement('CREATE INDEX reports_status_index ON reports (status)');
        DB::statement('CREATE INDEX reports_reporter_id_index ON reports (reporter_id)');
    }
};