<?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_target_type', 60)->nullable()->after('content_id')->index();
            $table->unsignedBigInteger('content_target_id')->nullable()->after('content_target_type')->index();
            $table->string('campaign_key', 80)->nullable()->after('group_key')->index();
            $table->unsignedInteger('cluster_score')->nullable()->after('campaign_key');
            $table->string('cluster_reason', 80)->nullable()->after('cluster_score');
            $table->unsignedInteger('priority_score')->default(0)->after('cluster_reason')->index();
            $table->string('ai_provider', 60)->nullable()->after('priority_score');
            $table->string('ai_label', 60)->nullable()->after('ai_provider')->index();
            $table->string('ai_suggested_action', 60)->nullable()->after('ai_label');
            $table->unsignedTinyInteger('ai_confidence')->nullable()->after('ai_suggested_action');
            $table->text('ai_explanation')->nullable()->after('ai_confidence');
            $table->unsignedInteger('false_positive_count')->default(0)->after('ai_explanation');
            $table->boolean('is_false_positive')->default(false)->after('false_positive_count')->index();
            $table->string('policy_name', 80)->nullable()->after('is_false_positive')->index();
            $table->string('review_bucket', 40)->nullable()->after('policy_name')->index();
            $table->string('escalation_status', 40)->default('none')->after('review_bucket')->index();
            $table->json('score_breakdown_json')->nullable()->after('rule_hits_json');
        });

        Schema::table('content_moderation_domains', function (Blueprint $table): void {
            $table->unsignedInteger('linked_users_count')->default(0)->after('times_confirmed_spam');
            $table->unsignedInteger('linked_findings_count')->default(0)->after('linked_users_count');
            $table->unsignedInteger('linked_clusters_count')->default(0)->after('linked_findings_count');
            $table->json('top_keywords_json')->nullable()->after('last_seen_at');
            $table->json('top_content_types_json')->nullable()->after('top_keywords_json');
            $table->unsignedInteger('false_positive_count')->default(0)->after('top_content_types_json');
        });

        Schema::create('content_moderation_clusters', function (Blueprint $table): void {
            $table->id();
            $table->string('campaign_key', 80)->unique();
            $table->string('cluster_reason', 80)->nullable();
            $table->string('review_bucket', 40)->nullable()->index();
            $table->string('escalation_status', 40)->default('none')->index();
            $table->unsignedInteger('cluster_score')->default(0)->index();
            $table->unsignedInteger('findings_count')->default(0);
            $table->unsignedInteger('unique_users_count')->default(0);
            $table->unsignedInteger('unique_domains_count')->default(0);
            $table->timestamp('latest_finding_at')->nullable()->index();
            $table->json('summary_json')->nullable();
            $table->timestamps();
        });

        Schema::create('content_moderation_ai_suggestions', function (Blueprint $table): void {
            $table->id();
            $table->unsignedBigInteger('finding_id')->index();
            $table->string('provider', 60)->index();
            $table->string('suggested_label', 60)->nullable()->index();
            $table->string('suggested_action', 60)->nullable();
            $table->unsignedTinyInteger('confidence')->nullable();
            $table->text('explanation')->nullable();
            $table->json('campaign_tags_json')->nullable();
            $table->json('raw_response_json')->nullable();
            $table->timestamp('created_at')->useCurrent();
        });

        Schema::create('content_moderation_feedback', function (Blueprint $table): void {
            $table->id();
            $table->unsignedBigInteger('finding_id')->index();
            $table->string('feedback_type', 60)->index();
            $table->unsignedBigInteger('actor_id')->nullable()->index();
            $table->text('notes')->nullable();
            $table->json('meta_json')->nullable();
            $table->timestamp('created_at')->useCurrent();
        });

        DB::table('content_moderation_findings')->update([
            'content_target_type' => DB::raw("CASE WHEN content_type = 'artwork_comment' THEN 'artwork_comment' ELSE 'artwork' END"),
            'content_target_id' => DB::raw("CASE WHEN content_type = 'artwork_comment' THEN content_id ELSE COALESCE(artwork_id, content_id) END"),
            'campaign_key' => DB::raw('group_key'),
            'cluster_score' => DB::raw('score'),
            'cluster_reason' => 'legacy_group_key',
            'priority_score' => DB::raw('score'),
            'policy_name' => 'default',
            'review_bucket' => DB::raw("CASE WHEN score >= 90 THEN 'urgent' WHEN score >= 60 THEN 'high' WHEN score >= 30 THEN 'priority' ELSE 'standard' END"),
            'escalation_status' => DB::raw("CASE WHEN score >= 90 THEN 'urgent' WHEN score >= 60 THEN 'escalated' WHEN score >= 30 THEN 'review_required' ELSE 'none' END"),
        ]);
    }

    public function down(): void
    {
        Schema::dropIfExists('content_moderation_feedback');
        Schema::dropIfExists('content_moderation_ai_suggestions');
        Schema::dropIfExists('content_moderation_clusters');

        Schema::table('content_moderation_domains', function (Blueprint $table): void {
            $table->dropColumn([
                'linked_users_count',
                'linked_findings_count',
                'linked_clusters_count',
                'top_keywords_json',
                'top_content_types_json',
                'false_positive_count',
            ]);
        });

        Schema::table('content_moderation_findings', function (Blueprint $table): void {
            $table->dropColumn([
                'content_target_type',
                'content_target_id',
                'campaign_key',
                'cluster_score',
                'cluster_reason',
                'priority_score',
                'ai_provider',
                'ai_label',
                'ai_suggested_action',
                'ai_confidence',
                'ai_explanation',
                'false_positive_count',
                'is_false_positive',
                'policy_name',
                'review_bucket',
                'escalation_status',
                'score_breakdown_json',
            ]);
        });
    }
};