<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class AddAdminListIndexes extends Migration
{
    public function up()
    {
        if ($this->db->tableExists('accounts')) {
            $this->addIndexIfMissing('accounts', 'idx_accounts_created_at', 'created_at');
            $this->addIndexIfMissing('accounts', 'idx_accounts_booking_id', 'booking_id');
            $this->addIndexIfMissing('accounts', 'idx_accounts_system_user_created', 'system_user_id, created_at');
        }

        if ($this->db->tableExists('payagents')) {
            $this->addIndexIfMissing('payagents', 'idx_payagents_created_at', 'created_at');
            $this->addIndexIfMissing('payagents', 'idx_payagents_company_created', 'company_id, created_at');
            $this->addIndexIfMissing('payagents', 'idx_payagents_agent_created', 'agent_id, created_at');
        }

        if ($this->db->tableExists('tickets')) {
            $this->addIndexIfMissing('tickets', 'idx_tickets_active_list', 'cancel_status, refund, deleted_at, id');
            $this->addIndexIfMissing('tickets', 'idx_tickets_journeydata', 'journeydata');
            $this->addIndexIfMissing('tickets', 'idx_tickets_bookby_user', 'bookby_user_id');
        }
    }

    public function down()
    {
        $this->dropIndexIfExists('accounts', 'idx_accounts_created_at');
        $this->dropIndexIfExists('accounts', 'idx_accounts_booking_id');
        $this->dropIndexIfExists('accounts', 'idx_accounts_system_user_created');
        $this->dropIndexIfExists('payagents', 'idx_payagents_created_at');
        $this->dropIndexIfExists('payagents', 'idx_payagents_company_created');
        $this->dropIndexIfExists('payagents', 'idx_payagents_agent_created');
        $this->dropIndexIfExists('tickets', 'idx_tickets_active_list');
        $this->dropIndexIfExists('tickets', 'idx_tickets_journeydata');
        $this->dropIndexIfExists('tickets', 'idx_tickets_bookby_user');
    }

    private function addIndexIfMissing(string $table, string $indexName, string $columns): void
    {
        if ($this->indexExists($table, $indexName)) {
            return;
        }

        $this->db->query(sprintf(
            'ALTER TABLE `%s` ADD INDEX `%s` (%s)',
            $table,
            $indexName,
            $columns
        ));
    }

    private function dropIndexIfExists(string $table, string $indexName): void
    {
        if (! $this->indexExists($table, $indexName)) {
            return;
        }

        $this->db->query(sprintf('ALTER TABLE `%s` DROP INDEX `%s`', $table, $indexName));
    }

    private function indexExists(string $table, string $indexName): bool
    {
        $rows = $this->db->query(sprintf('SHOW INDEX FROM `%s` WHERE Key_name = %s', $table, $this->db->escape($indexName)))
            ->getResultArray();

        return $rows !== [];
    }
}
