<?php

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

return new class extends Migration
{
    public function up(): void
    {
        Schema::table('collections', function (Blueprint $table): void {
            $table->string('workflow_state', 40)->nullable()->after('lifecycle_state');
            $table->string('readiness_state', 40)->nullable()->after('workflow_state');
            $table->string('health_state', 40)->nullable()->after('readiness_state');
            $table->json('health_flags_json')->nullable()->after('health_state');
            $table->foreignId('canonical_collection_id')->nullable()->after('health_flags_json')->constrained('collections')->nullOnDelete();
            $table->string('duplicate_cluster_key', 120)->nullable()->after('canonical_collection_id');
            $table->string('program_key', 80)->nullable()->after('duplicate_cluster_key');
            $table->string('partner_key', 80)->nullable()->after('program_key');
            $table->string('trust_tier', 40)->nullable()->after('partner_key');
            $table->string('experiment_key', 80)->nullable()->after('trust_tier');
            $table->string('recommendation_tier', 40)->nullable()->after('experiment_key');
            $table->string('ranking_bucket', 40)->nullable()->after('recommendation_tier');
            $table->string('search_boost_tier', 40)->nullable()->after('ranking_bucket');
            $table->decimal('metadata_completeness_score', 6, 2)->nullable()->after('search_boost_tier');
            $table->decimal('editorial_readiness_score', 6, 2)->nullable()->after('metadata_completeness_score');
            $table->decimal('freshness_score', 6, 2)->nullable()->after('editorial_readiness_score');
            $table->decimal('engagement_score', 6, 2)->nullable()->after('freshness_score');
            $table->decimal('health_score', 6, 2)->nullable()->after('engagement_score');
            $table->timestamp('last_health_check_at')->nullable()->after('health_score');
            $table->timestamp('last_recommendation_refresh_at')->nullable()->after('last_health_check_at');
            $table->boolean('placement_eligibility')->default(true)->after('last_recommendation_refresh_at');

            $table->index(['workflow_state', 'readiness_state'], 'collections_workflow_readiness_idx');
            $table->index(['health_state', 'placement_eligibility'], 'collections_health_eligibility_idx');
            $table->index(['program_key', 'placement_eligibility'], 'collections_program_eligibility_idx');
            $table->index(['partner_key', 'trust_tier'], 'collections_partner_trust_idx');
            $table->index(['canonical_collection_id', 'duplicate_cluster_key'], 'collections_canonical_duplicate_idx');
        });

        Schema::create('collection_program_assignments', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('collection_id')->constrained('collections')->cascadeOnDelete();
            $table->string('program_key', 80);
            $table->string('campaign_key', 80)->nullable();
            $table->string('placement_scope', 80)->nullable();
            $table->timestamp('starts_at')->nullable();
            $table->timestamp('ends_at')->nullable();
            $table->unsignedInteger('priority')->default(0);
            $table->text('notes')->nullable();
            $table->foreignId('created_by_user_id')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamps();

            $table->index(['program_key', 'priority'], 'collection_program_assignments_program_priority_idx');
            $table->index(['campaign_key', 'placement_scope'], 'collection_program_assignments_campaign_scope_idx');
        });

        Schema::create('collection_quality_snapshots', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('collection_id')->constrained('collections')->cascadeOnDelete();
            $table->date('snapshot_date');
            $table->decimal('quality_score', 6, 2)->nullable();
            $table->decimal('health_score', 6, 2)->nullable();
            $table->decimal('metadata_completeness_score', 6, 2)->nullable();
            $table->decimal('freshness_score', 6, 2)->nullable();
            $table->decimal('engagement_score', 6, 2)->nullable();
            $table->decimal('readiness_score', 6, 2)->nullable();
            $table->json('flags_json')->nullable();
            $table->timestamps();

            $table->unique(['collection_id', 'snapshot_date'], 'collection_quality_snapshots_unique');
        });

        Schema::create('collection_recommendation_snapshots', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('collection_id')->constrained('collections')->cascadeOnDelete();
            $table->string('context_key', 80);
            $table->decimal('recommendation_score', 8, 2)->nullable();
            $table->json('rationale_json')->nullable();
            $table->date('snapshot_date');
            $table->timestamps();

            $table->index(['context_key', 'snapshot_date'], 'collection_recommendation_snapshots_context_date_idx');
        });

        Schema::create('collection_merge_actions', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('source_collection_id')->constrained('collections')->cascadeOnDelete();
            $table->foreignId('target_collection_id')->constrained('collections')->cascadeOnDelete();
            $table->enum('action_type', ['suggested', 'approved', 'completed', 'rejected', 'reverted']);
            $table->foreignId('actor_user_id')->nullable()->constrained('users')->nullOnDelete();
            $table->string('summary', 255)->nullable();
            $table->timestamps();

            $table->index(['source_collection_id', 'target_collection_id'], 'collection_merge_actions_pair_idx');
        });

        Schema::create('collection_entity_links', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('collection_id')->constrained('collections')->cascadeOnDelete();
            $table->string('linked_type', 80);
            $table->unsignedBigInteger('linked_id');
            $table->string('relationship_type', 80);
            $table->json('metadata_json')->nullable();
            $table->timestamps();

            $table->index(['linked_type', 'linked_id'], 'collection_entity_links_target_idx');
            $table->index(['collection_id', 'relationship_type'], 'collection_entity_links_relationship_idx');
        });

        Schema::create('collection_saved_notes', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('user_id')->constrained('users')->cascadeOnDelete();
            $table->foreignId('collection_id')->constrained('collections')->cascadeOnDelete();
            $table->text('note')->nullable();
            $table->timestamps();

            $table->unique(['user_id', 'collection_id'], 'collection_saved_notes_user_collection_unique');
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('collection_saved_notes');
        Schema::dropIfExists('collection_entity_links');
        Schema::dropIfExists('collection_merge_actions');
        Schema::dropIfExists('collection_recommendation_snapshots');
        Schema::dropIfExists('collection_quality_snapshots');
        Schema::dropIfExists('collection_program_assignments');

        Schema::table('collections', function (Blueprint $table): void {
            $table->dropIndex('collections_workflow_readiness_idx');
            $table->dropIndex('collections_health_eligibility_idx');
            $table->dropIndex('collections_program_eligibility_idx');
            $table->dropIndex('collections_partner_trust_idx');
            $table->dropIndex('collections_canonical_duplicate_idx');
            $table->dropConstrainedForeignId('canonical_collection_id');
            $table->dropColumn([
                'workflow_state',
                'readiness_state',
                'health_state',
                'health_flags_json',
                'duplicate_cluster_key',
                'program_key',
                'partner_key',
                'trust_tier',
                'experiment_key',
                'recommendation_tier',
                'ranking_bucket',
                'search_boost_tier',
                'metadata_completeness_score',
                'editorial_readiness_score',
                'freshness_score',
                'engagement_score',
                'health_score',
                'last_health_check_at',
                'last_recommendation_refresh_at',
                'placement_eligibility',
            ]);
        });
    }
};