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

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

    public function up()
    {
        $column_changes = array(
            array('table' => 'tbl_accounts', 'field' => 'chart_of_account_id', 'sql' => "ALTER TABLE `tbl_accounts` ADD `chart_of_account_id` INT(11) NULL DEFAULT NULL AFTER `balance`"),
            array('table' => 'tbl_income_category', 'field' => 'chart_of_account_id', 'sql' => "ALTER TABLE `tbl_income_category` ADD `chart_of_account_id` INT(11) NULL DEFAULT NULL AFTER `description`"),
            array('table' => 'tbl_expense_category', 'field' => 'chart_of_account_id', 'sql' => "ALTER TABLE `tbl_expense_category` ADD `chart_of_account_id` INT(11) NULL DEFAULT NULL AFTER `description`"),
            array('table' => 'tbl_transactions', 'field' => 'journal_id', 'sql' => "ALTER TABLE `tbl_transactions` ADD `journal_id` INT(11) NULL DEFAULT NULL AFTER `transfer_id`"),
            array('table' => 'tbl_transfer', 'field' => 'journal_id', 'sql' => "ALTER TABLE `tbl_transfer` ADD `journal_id` INT(11) NULL DEFAULT NULL AFTER `type`"),
            array('table' => 'tbl_journals', 'field' => 'source_module', 'sql' => "ALTER TABLE `tbl_journals` ADD `source_module` VARCHAR(50) NULL DEFAULT NULL AFTER `status`"),
            array('table' => 'tbl_journals', 'field' => 'source_id', 'sql' => "ALTER TABLE `tbl_journals` ADD `source_id` INT(11) NULL DEFAULT NULL AFTER `source_module`"),
            array('table' => 'tbl_journals', 'field' => 'source_hash', 'sql' => "ALTER TABLE `tbl_journals` ADD `source_hash` VARCHAR(64) NULL DEFAULT NULL AFTER `source_id`"),
        );

        foreach ($column_changes as $change) {
            if ($this->table_exists($change['table']) && !$this->db->field_exists($change['field'], $change['table'])) {
                $this->db->query($change['sql']);
            }
        }

        $this->defer_indexes('612');

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

    public function down()
    {
        $indexes = array(
            array('table' => 'tbl_accounts', 'name' => 'idx_tbl_accounts_chart_of_account_id'),
            array('table' => 'tbl_income_category', 'name' => 'idx_tbl_income_category_chart_of_account_id'),
            array('table' => 'tbl_expense_category', 'name' => 'idx_tbl_expense_category_chart_of_account_id'),
            array('table' => 'tbl_transactions', 'name' => 'idx_tbl_transactions_journal_id'),
            array('table' => 'tbl_transfer', 'name' => 'idx_tbl_transfer_journal_id'),
            array('table' => 'tbl_journals', 'name' => 'idx_tbl_journals_source_module_source_id'),
            array('table' => 'tbl_journals', 'name' => 'idx_tbl_journals_source_hash'),
        );

        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']}`");
            }
        }

        $column_drops = array(
            array('table' => 'tbl_accounts', 'field' => 'chart_of_account_id'),
            array('table' => 'tbl_income_category', 'field' => 'chart_of_account_id'),
            array('table' => 'tbl_expense_category', 'field' => 'chart_of_account_id'),
            array('table' => 'tbl_transactions', 'field' => 'journal_id'),
            array('table' => 'tbl_transfer', 'field' => 'journal_id'),
            array('table' => 'tbl_journals', 'field' => 'source_module'),
            array('table' => 'tbl_journals', 'field' => 'source_id'),
            array('table' => 'tbl_journals', 'field' => 'source_hash'),
        );

        foreach ($column_drops as $drop) {
            if ($this->table_exists($drop['table']) && $this->db->field_exists($drop['field'], $drop['table'])) {
                $this->db->query("ALTER TABLE `{$drop['table']}` DROP COLUMN `{$drop['field']}`");
            }
        }

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

    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));
    }
}
