<?php

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

/**
 * Batch 1 – Performance indexes derived from slow query log analysis (2026-04-26).
 *
 * Queries targeted:
 *  - Q1 / Q2 (83k + 34k slow seconds): artworks public/approved/published scans
 *  - Q12 (923s): artwork_metric_snapshots_hourly DISTINCT bucket range scan
 *  - Q26–Q46 (rank_artwork_scores per-score ORDER BY scans)
 *  - Q6 (2,234s): tag_interaction_daily_metrics tag+date lookup
 *  - Q8 (1,004s): popular tags correlated subquery → denormalised artworks_count
 */
return new class extends Migration
{
    public function up(): void
    {
        // ── artworks ─────────────────────────────────────────────────────────
        // Covers the high-frequency public artwork scan used by the publish
        // pipeline, browse gallery, sitemaps, and ranking jobs
        // (Queries 1, 2, 18, 22, 23, 30, 40, 47).
        // Allows MySQL to satisfy the WHERE + ORDER BY id ASC from the index alone.
        Schema::table('artworks', function (Blueprint $table) {
            if (! $this->indexExists('artworks', 'idx_public_approved_published_id')) {
                $table->index(
                    ['deleted_at', 'is_public', 'is_approved', 'published_at', 'id'],
                    'idx_public_approved_published_id'
                );
            }

            // Covers GROUP BY user_id aggregations (Query 31) and
            // per-user public artwork count (Query 33).
            if (! $this->indexExists('artworks', 'idx_public_approved_user_id')) {
                $table->index(
                    ['deleted_at', 'is_public', 'is_approved', 'user_id'],
                    'idx_public_approved_user_id'
                );
            }
        });

        // ── artwork_metric_snapshots_hourly ───────────────────────────────────
        // Fixes the "SELECT DISTINCT artwork_id WHERE bucket_hour BETWEEN ..."
        // pattern (Query 12) that currently scans up to 8.3M rows per call.
        // Also speeds up the hourly stats JOIN in Queries 3 and 4.
        Schema::table('artwork_metric_snapshots_hourly', function (Blueprint $table) {
            if (! $this->indexExists('artwork_metric_snapshots_hourly', 'idx_bucket_artwork')) {
                $table->index(['bucket_hour', 'artwork_id'], 'idx_bucket_artwork');
            }
        });

        // ── rank_artwork_scores ───────────────────────────────────────────────
        // Each ranking variant queries WHERE model_version = ? ORDER BY score_X DESC.
        // Without a (model_version, score) index MySQL scans ~143–150k rows per call.
        // Queries 26, 27, 29, 37, 39, 41, 42, 43, 46.
        Schema::table('rank_artwork_scores', function (Blueprint $table) {
            if (! $this->indexExists('rank_artwork_scores', 'idx_mv_trending')) {
                $table->index(['model_version', 'score_trending'], 'idx_mv_trending');
            }

            if (! $this->indexExists('rank_artwork_scores', 'idx_mv_new_hot')) {
                $table->index(['model_version', 'score_new_hot'], 'idx_mv_new_hot');
            }

            if (! $this->indexExists('rank_artwork_scores', 'idx_mv_best')) {
                $table->index(['model_version', 'score_best'], 'idx_mv_best');
            }
        });

        // ── tag_interaction_daily_metrics ─────────────────────────────────────
        // Popular-tags query joins on tag_slug and filters by metric_date (Query 6).
        // Note: table uses tag_slug (varchar), not a tag_id FK.
        Schema::table('tag_interaction_daily_metrics', function (Blueprint $table) {
            if (! $this->indexExists('tag_interaction_daily_metrics', 'idx_tag_date')) {
                $table->index(['tag_slug', 'metric_date'], 'idx_tag_date');
            }
        });

        // ── tags ──────────────────────────────────────────────────────────────
        // Denormalised artworks_count column to replace the correlated subquery
        // in the popular-tags ORDER BY artworks_count DESC query (Query 8).
        // After running this migration, backfill via:
        //   php artisan tinker --execute="Tag::query()->each(fn(\$t) => \$t->update(['artworks_count' => \$t->artworks()->published()->count()]))"
        if (! Schema::hasColumn('tags', 'artworks_count')) {
            Schema::table('tags', function (Blueprint $table) {
                $table->unsignedInteger('artworks_count')->default(0)->after('slug');
                $table->index('artworks_count', 'idx_tags_artworks_count');
            });
        }
    }

    public function down(): void
    {
        Schema::table('artworks', function (Blueprint $table) {
            $table->dropIndexIfExists('idx_public_approved_published_id');
            $table->dropIndexIfExists('idx_public_approved_user_id');
        });

        Schema::table('artwork_metric_snapshots_hourly', function (Blueprint $table) {
            $table->dropIndexIfExists('idx_bucket_artwork');
        });

        Schema::table('rank_artwork_scores', function (Blueprint $table) {
            $table->dropIndexIfExists('idx_mv_trending');
            $table->dropIndexIfExists('idx_mv_new_hot');
            $table->dropIndexIfExists('idx_mv_best');
        });

        Schema::table('tag_interaction_daily_metrics', function (Blueprint $table) {
            $table->dropIndexIfExists('idx_tag_date');
        });

        if (Schema::hasColumn('tags', 'artworks_count')) {
            Schema::table('tags', function (Blueprint $table) {
                $table->dropIndexIfExists('idx_tags_artworks_count');
                $table->dropColumn('artworks_count');
            });
        }
    }

    /**
     * Check whether a named index already exists (safe for re-runs on staging/prod).
     */
    private function indexExists(string $table, string $indexName): bool
    {
        if (DB::getDriverName() === 'sqlite') {
            return collect(DB::select("PRAGMA index_list('{$table}')"))
                ->contains(static fn (object $row): bool => ($row->name ?? null) === $indexName);
        }

        return count(DB::select(
            "SHOW INDEX FROM `{$table}` WHERE Key_name = ?",
            [$indexName]
        )) > 0;
    }
};
