<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class AllowCompetitionWinnerRankTies extends Migration
{
    public function up()
    {
        $db = \Config\Database::connect();
        if (!$db->tableExists('competition_winners')) {
            return;
        }

        if ($this->indexExists($db, 'competition_winners', 'competition_id_class_section_id_rank')) {
            $db->query('DROP INDEX competition_id_class_section_id_rank ON competition_winners');
        }

        if (! $this->indexExists($db, 'competition_winners', 'competition_id_class_section_id_rank')) {
            $db->query(
                'CREATE INDEX competition_id_class_section_id_rank ON competition_winners (competition_id, class_section_id, rank)'
            );
        }
    }

    public function down()
    {
        $db = \Config\Database::connect();
        if (!$db->tableExists('competition_winners')) {
            return;
        }

        if ($this->indexExists($db, 'competition_winners', 'competition_id_class_section_id_rank')) {
            $db->query('DROP INDEX competition_id_class_section_id_rank ON competition_winners');
        }

        $db->query(
            'ALTER TABLE competition_winners ADD UNIQUE KEY competition_id_class_section_id_rank (competition_id, class_section_id, rank)'
        );
    }

    private function indexExists($db, string $table, string $index): bool
    {
        if (method_exists($db, 'indexExists')) {
            return $db->indexExists($table, $index);
        }

        $dbName = $db->getDatabase();
        $builder = $db->table('information_schema.STATISTICS');
        $row = $builder
            ->select('INDEX_NAME')
            ->where('TABLE_SCHEMA', $dbName)
            ->where('TABLE_NAME', $table)
            ->where('INDEX_NAME', $index)
            ->get()
            ->getRowArray();

        return ! empty($row);
    }
}