<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class AddUniqueFinancialAidParentYear extends Migration
{
    private string $table = 'financial_aid_requests';
    private string $index = 'uniq_financial_aid_parent_year';

    public function up()
    {
        if (! $this->db->tableExists($this->table) || $this->indexExists()) {
            return;
        }

        $duplicate = $this->db->table($this->table)
            ->select('parent_id, school_year, COUNT(*) AS total', false)
            ->groupBy(['parent_id', 'school_year'])
            ->having('total >', 1)
            ->limit(1)
            ->get()
            ->getRowArray();

        if ($duplicate !== null) {
            log_message('warning', 'Skipped unique financial aid parent/year index because duplicate requests already exist.');
            return;
        }

        $this->db->query(sprintf(
            'CREATE UNIQUE INDEX %s ON %s (parent_id, school_year)',
            $this->db->escapeIdentifiers($this->index),
            $this->db->escapeIdentifiers($this->table)
        ));
    }

    public function down()
    {
        if (! $this->db->tableExists($this->table) || ! $this->indexExists()) {
            return;
        }

        $this->db->query(sprintf(
            'DROP INDEX %s ON %s',
            $this->db->escapeIdentifiers($this->index),
            $this->db->escapeIdentifiers($this->table)
        ));
    }

    private function indexExists(): bool
    {
        foreach ($this->db->getIndexData($this->table) as $index) {
            if (($index->name ?? '') === $this->index) {
                return true;
            }
        }

        return false;
    }
}