<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class AddNotificationListIndexes extends Migration
{
    public function up()
    {
        if (! $this->db->tableExists('notifications')) {
            return;
        }

        $this->addIndexIfMissing('notifications', 'idx_notifications_created_at', 'created_at');
        $this->addIndexIfMissing('notifications', 'idx_notifications_status_created_at', 'status, created_at');

        if ($this->db->tableExists('user_notifications')) {
            $this->addIndexIfMissing('user_notifications', 'idx_user_notifications_user_sent_at', 'user_id, sent_at');
        }
    }

    public function down()
    {
        $this->dropIndexIfExists('notifications', 'idx_notifications_created_at');
        $this->dropIndexIfExists('notifications', 'idx_notifications_status_created_at');
        $this->dropIndexIfExists('user_notifications', 'idx_user_notifications_user_sent_at');
    }

    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 !== [];
    }
}
