<?php

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

return new class extends Migration {
    public function up(): void
    {
        Schema::table('content_moderation_findings', function (Blueprint $table): void {
            $table->string('content_hash_normalized', 64)->nullable()->after('content_hash');
            $table->string('group_key', 128)->nullable()->after('content_hash_normalized')->index();
            $table->json('rule_hits_json')->nullable()->after('matched_keywords_json');
            $table->json('domain_ids_json')->nullable()->after('rule_hits_json');
            $table->integer('user_risk_score')->nullable()->after('domain_ids_json');
            $table->boolean('is_auto_hidden')->default(false)->after('user_risk_score')->index();
            $table->string('auto_action_taken', 50)->nullable()->after('is_auto_hidden');
            $table->timestamp('auto_hidden_at')->nullable()->after('auto_action_taken');
            $table->unsignedBigInteger('resolved_by')->nullable()->after('reviewed_by');
            $table->timestamp('resolved_at')->nullable()->after('reviewed_at');
            $table->unsignedBigInteger('restored_by')->nullable()->after('resolved_at');
            $table->timestamp('restored_at')->nullable()->after('restored_by');
        });

        Schema::create('content_moderation_domains', function (Blueprint $table): void {
            $table->id();
            $table->string('domain', 255)->unique();
            $table->string('status', 20)->default('neutral')->index();
            $table->unsignedInteger('times_seen')->default(0);
            $table->unsignedInteger('times_flagged')->default(0);
            $table->unsignedInteger('times_confirmed_spam')->default(0);
            $table->timestamp('first_seen_at')->nullable();
            $table->timestamp('last_seen_at')->nullable();
            $table->text('notes')->nullable();
            $table->timestamps();
        });

        Schema::create('content_moderation_rules', function (Blueprint $table): void {
            $table->id();
            $table->string('type', 40)->index();
            $table->text('value');
            $table->boolean('enabled')->default(true)->index();
            $table->integer('weight')->nullable();
            $table->text('notes')->nullable();
            $table->unsignedBigInteger('created_by')->nullable();
            $table->timestamps();
        });

        Schema::create('content_moderation_action_logs', function (Blueprint $table): void {
            $table->id();
            $table->unsignedBigInteger('finding_id')->nullable()->index();
            $table->string('target_type', 50)->index();
            $table->unsignedBigInteger('target_id')->nullable()->index();
            $table->string('action_type', 50)->index();
            $table->string('actor_type', 20)->default('system')->index();
            $table->unsignedBigInteger('actor_id')->nullable()->index();
            $table->string('old_status', 30)->nullable();
            $table->string('new_status', 30)->nullable();
            $table->string('old_visibility', 30)->nullable();
            $table->string('new_visibility', 30)->nullable();
            $table->text('notes')->nullable();
            $table->json('meta_json')->nullable();
            $table->timestamp('created_at')->useCurrent();
        });

        DB::table('content_moderation_findings')
            ->update([
                'content_hash_normalized' => DB::raw('content_hash'),
                'group_key' => DB::raw('content_hash'),
                'resolved_at' => DB::raw("CASE WHEN status = 'resolved' THEN reviewed_at ELSE NULL END"),
                'resolved_by' => DB::raw("CASE WHEN status = 'resolved' THEN reviewed_by ELSE NULL END"),
            ]);

        Schema::table('content_moderation_findings', function (Blueprint $table): void {
            $table->dropUnique('cmf_content_unique');
            $table->unique(['content_type', 'content_id', 'content_hash', 'scanner_version'], 'cmf_content_scanner_unique');
        });
    }

    public function down(): void
    {
        Schema::table('content_moderation_findings', function (Blueprint $table): void {
            $table->dropUnique('cmf_content_scanner_unique');
            $table->unique(['content_type', 'content_id', 'content_hash'], 'cmf_content_unique');
        });

        Schema::dropIfExists('content_moderation_action_logs');
        Schema::dropIfExists('content_moderation_rules');
        Schema::dropIfExists('content_moderation_domains');

        Schema::table('content_moderation_findings', function (Blueprint $table): void {
            $table->dropColumn([
                'content_hash_normalized',
                'group_key',
                'rule_hits_json',
                'domain_ids_json',
                'user_risk_score',
                'is_auto_hidden',
                'auto_action_taken',
                'auto_hidden_at',
                'resolved_by',
                'resolved_at',
                'restored_by',
                'restored_at',
            ]);
        });
    }
};