<?php

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

return new class extends Migration
{
    /**
     * Run the migrations.
     * Optimizations for millions of QR codes with MariaDB
     */
    public function up(): void
    {
        // Optimize codes table for high-volume queries
        DB::statement('ALTER TABLE codes ENGINE = InnoDB ROW_FORMAT = COMPRESSED');
        DB::statement('ALTER TABLE codes ADD FULLTEXT INDEX ft_guid_qr (guid, qr)');

        // Add composite indexes for common query patterns
        Schema::table('codes', function (Blueprint $table) {
            // For verification queries (high frequency)
            $table->index(['status', 'lastverified'], 'idx_status_verified');
            $table->index(['product_id', 'status'], 'idx_product_status');
            $table->index(['created_at', 'status'], 'idx_created_status');

            // For lucky draw queries
            $table->index(['islucky', 'status'], 'idx_lucky_status');

            // For export queries
            $table->index(['exported', 'created_at'], 'idx_export_date');
        });

        // Optimize seller_qr pivot table (high insert/lookup volume)
        DB::statement('ALTER TABLE seller_qr ENGINE = InnoDB ROW_FORMAT = COMPRESSED');
        Schema::table('seller_qr', function (Blueprint $table) {
            $table->index(['created_at'], 'idx_assignment_date');
        });

        // Optimize customers table
        Schema::table('customers', function (Blueprint $table) {
            $table->index(['created_at', 'is_lucky_draw'], 'idx_created_lucky');
            $table->index(['email'], 'idx_customer_email');
        });

        // Optimize products table
        Schema::table('products', function (Blueprint $table) {
            $table->index(['created_at'], 'idx_product_created');
        });

        // Optimize sellers table
        Schema::table('sellers', function (Blueprint $table) {
            $table->index(['created_at', 'type'], 'idx_seller_created_type');
        });

        // Set InnoDB buffer pool size and other settings via SQL
        // These improve performance for millions of records
        DB::statement("SET GLOBAL innodb_buffer_pool_size = 2147483648"); // 2GB
        DB::statement("SET GLOBAL innodb_log_file_size = 536870912"); // 512MB
        DB::statement("SET GLOBAL innodb_flush_log_at_trx_commit = 2"); // Better performance
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::table('codes', function (Blueprint $table) {
            $table->dropIndex('idx_status_verified');
            $table->dropIndex('idx_product_status');
            $table->dropIndex('idx_created_status');
            $table->dropIndex('idx_lucky_status');
            $table->dropIndex('idx_export_date');
        });

        DB::statement('ALTER TABLE codes DROP INDEX ft_guid_qr');

        Schema::table('seller_qr', function (Blueprint $table) {
            $table->dropIndex('idx_assignment_date');
        });

        Schema::table('customers', function (Blueprint $table) {
            $table->dropIndex('idx_created_lucky');
            $table->dropIndex('idx_customer_email');
        });

        Schema::table('products', function (Blueprint $table) {
            $table->dropIndex('idx_product_created');
        });

        Schema::table('sellers', function (Blueprint $table) {
            $table->dropIndex('idx_seller_created_type');
        });
    }
};
