<?php
defined('BASEPATH') or exit('No direct script access allowed');

class Migration_Version_614 extends CI_Migration
{
    public function __construct()
    {
        parent::__construct();
    }

    public function up()
    {
        $this->defer_indexes('614');

        if ($this->table_exists('tbl_config')) {
            $this->db->where('config_key', 'version')->update('tbl_config', array('value' => '6.1.4'));
        }
    }

    public function down()
    {
        $indexes = array(
            array('table' => 'tbl_transactions', 'name' => 'idx_tbl_transactions_account_type_date'),
            array('table' => 'tbl_transactions', 'name' => 'idx_tbl_transactions_account_date'),
            array('table' => 'tbl_transactions', 'name' => 'idx_tbl_transactions_category_date'),
            array('table' => 'tbl_transfer', 'name' => 'idx_tbl_transfer_from_account_date'),
            array('table' => 'tbl_transfer', 'name' => 'idx_tbl_transfer_to_account_date'),
            array('table' => 'tbl_journals', 'name' => 'idx_tbl_journals_date_source_module'),
            array('table' => 'tbl_journals', 'name' => 'idx_tbl_journals_reference_no'),
            array('table' => 'tbl_journal_items', 'name' => 'idx_tbl_journal_items_journal_id'),
            array('table' => 'tbl_journal_items', 'name' => 'idx_tbl_journal_items_chart_of_account_id'),
            array('table' => 'tbl_bank_reconciliations', 'name' => 'idx_tbl_bank_reconciliations_statement_date'),
        );

        foreach ($indexes as $index) {
            if ($this->table_exists($index['table']) && $this->index_exists($index['table'], $index['name'])) {
                $this->db->query("ALTER TABLE `{$index['table']}` DROP INDEX `{$index['name']}`");
            }
        }

        if ($this->table_exists('tbl_config')) {
            $this->db->where('config_key', 'version')->update('tbl_config', array('value' => '6.1.3'));
        }
    }

    private function table_exists($table)
    {
        return $this->db->table_exists($table);
    }

    private function index_exists($table, $indexName)
    {
        $result = $this->db->query("SHOW INDEX FROM `{$table}` WHERE Key_name = " . $this->db->escape($indexName));
        return $result && $result->num_rows() > 0;
    }

    private function defer_indexes($version)
    {
        if (!$this->db->table_exists('tbl_config')) {
            return;
        }

        $key = 'deferred_upgrade_indexes';
        $existing = $this->db->where('config_key', $key)->get('tbl_config')->row_array();
        $versions = !empty($existing['value']) ? array_filter(array_map('trim', explode(',', $existing['value']))) : array();
        if (!in_array($version, $versions, true)) {
            $versions[] = $version;
        }
        $value = implode(',', $versions);

        if (!empty($existing)) {
            $this->db->where('config_key', $key)->update('tbl_config', array('value' => $value));
            return;
        }

        $this->db->insert('tbl_config', array('config_key' => $key, 'value' => $value));
    }
}
