<?php

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

return new class extends Migration
{
    public function up(): void
    {
        $this->addIndexIfMissing('tb_agent', 'idx_tb_agent_upline', 'ALTER TABLE `tb_agent` ADD INDEX `idx_tb_agent_upline` (`agent_upline`)');

        $this->addIndexIfMissing('tb_user', 'idx_tb_user_agent_created', 'ALTER TABLE `tb_user` ADD INDEX `idx_tb_user_agent_created` (`agent_code`, `created`)');
        $this->addIndexIfMissing('tb_user', 'idx_tb_user_int_user', 'ALTER TABLE `tb_user` ADD INDEX `idx_tb_user_int_user` (`intUser`)');
        $this->addIndexIfMissing('tb_user', 'idx_tb_user_ext_user', 'ALTER TABLE `tb_user` ADD INDEX `idx_tb_user_ext_user` (`extUser`)');

        $this->addIndexIfMissing('tb_transaction', 'idx_tb_transaction_agent_created', 'ALTER TABLE `tb_transaction` ADD INDEX `idx_tb_transaction_agent_created` (`agent_code`, `created`)');
        $this->addIndexIfMissing('tb_transaction', 'idx_tb_transaction_int_user', 'ALTER TABLE `tb_transaction` ADD INDEX `idx_tb_transaction_int_user` (`intUser`)');
        $this->addIndexIfMissing('tb_transaction', 'idx_tb_transaction_ext_user', 'ALTER TABLE `tb_transaction` ADD INDEX `idx_tb_transaction_ext_user` (`extUser`)');
        $this->addIndexIfMissing('tb_transaction', 'idx_tb_transaction_type', 'ALTER TABLE `tb_transaction` ADD INDEX `idx_tb_transaction_type` (`type`)');
        $this->addIndexIfMissing('tb_transaction', 'idx_tb_transaction_trxid', 'ALTER TABLE `tb_transaction` ADD INDEX `idx_tb_transaction_trxid` (`trxid`(64))');

        $this->addIndexIfMissing('tb_history', 'idx_tb_history_agent', 'ALTER TABLE `tb_history` ADD INDEX `idx_tb_history_agent` (`agent_code`)');
    }

    public function down(): void
    {
        $this->dropIndexIfExists('tb_history', 'idx_tb_history_agent');
        $this->dropIndexIfExists('tb_transaction', 'idx_tb_transaction_trxid');
        $this->dropIndexIfExists('tb_transaction', 'idx_tb_transaction_type');
        $this->dropIndexIfExists('tb_transaction', 'idx_tb_transaction_ext_user');
        $this->dropIndexIfExists('tb_transaction', 'idx_tb_transaction_int_user');
        $this->dropIndexIfExists('tb_transaction', 'idx_tb_transaction_agent_created');
        $this->dropIndexIfExists('tb_user', 'idx_tb_user_ext_user');
        $this->dropIndexIfExists('tb_user', 'idx_tb_user_int_user');
        $this->dropIndexIfExists('tb_user', 'idx_tb_user_agent_created');
        $this->dropIndexIfExists('tb_agent', 'idx_tb_agent_upline');
    }

    private function addIndexIfMissing(string $table, string $index, string $statement): void
    {
        if (!$this->indexExists($table, $index)) {
            DB::statement($statement);
        }
    }

    private function dropIndexIfExists(string $table, string $index): void
    {
        if ($this->indexExists($table, $index)) {
            DB::statement("ALTER TABLE `$table` DROP INDEX `$index`");
        }
    }

    private function indexExists(string $table, string $index): bool
    {
        $result = DB::selectOne(
            'SELECT COUNT(*) AS total
             FROM information_schema.statistics
             WHERE table_schema = DATABASE()
               AND table_name = ?
               AND index_name = ?',
            [$table, $index]
        );

        return (int)($result->total ?? 0) > 0;
    }
};
