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

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

    public function up()
    {
        $this->db->query("INSERT INTO `tbl_menu` (`label`, `link`, `icon`, `parent`, `sort`, `time`, `status`) SELECT 'bank_reconciliation', 'admin/accounting/bank_reconciliation', 'fa fa-circle-o', m.menu_id, '7', '2026-04-28 00:00:00', '1' FROM (SELECT menu_id FROM tbl_menu WHERE label = 'accounting' ORDER BY menu_id DESC LIMIT 1) m WHERE NOT EXISTS (SELECT 1 FROM tbl_menu WHERE label = 'bank_reconciliation' AND link = 'admin/accounting/bank_reconciliation' LIMIT 1);");
        $this->db->query("INSERT INTO `tbl_menu` (`label`, `link`, `icon`, `parent`, `sort`, `time`, `status`) SELECT 'accounting_periods', 'admin/accounting/accounting_periods', 'fa fa-circle-o', m.menu_id, '8', '2026-04-28 00:00:00', '1' FROM (SELECT menu_id FROM tbl_menu WHERE label = 'accounting' ORDER BY menu_id DESC LIMIT 1) m WHERE NOT EXISTS (SELECT 1 FROM tbl_menu WHERE label = 'accounting_periods' AND link = 'admin/accounting/accounting_periods' LIMIT 1);");

        foreach (array(
            array('tbl_journals', 'reversal_of_journal_id', "ALTER TABLE `tbl_journals` ADD COLUMN `reversal_of_journal_id` INT NULL AFTER `source_hash`"),
            array('tbl_journals', 'reversal_journal_id', "ALTER TABLE `tbl_journals` ADD COLUMN `reversal_journal_id` INT NULL AFTER `reversal_of_journal_id`"),
            array('tbl_journals', 'is_reversal', "ALTER TABLE `tbl_journals` ADD COLUMN `is_reversal` TINYINT(1) NOT NULL DEFAULT '0' AFTER `reversal_journal_id`"),
            array('tbl_journals', 'reconciled_status', "ALTER TABLE `tbl_journals` ADD COLUMN `reconciled_status` varchar(20) NOT NULL DEFAULT 'unreconciled' AFTER `is_reversal`"),
            array('tbl_journals', 'reconciled_at', "ALTER TABLE `tbl_journals` ADD COLUMN `reconciled_at` datetime NULL AFTER `reconciled_status`"),
            array('tbl_journals', 'reconciled_by', "ALTER TABLE `tbl_journals` ADD COLUMN `reconciled_by` INT NULL AFTER `reconciled_at`"),
        ) as $column) {
            if (!$this->db->field_exists($column[1], $column[0])) {
                $this->db->query($column[2]);
            }
        }

        $this->db->query("CREATE TABLE IF NOT EXISTS `tbl_accounting_periods` (
  `period_id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(150) NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'open',
  `notes` text NULL,
  `closed_by` int NULL,
  `closed_at` datetime NULL,
  `created_by` int NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NULL,
  PRIMARY KEY (`period_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;");

        $this->db->query("CREATE TABLE IF NOT EXISTS `tbl_bank_reconciliations` (
  `reconciliation_id` int NOT NULL AUTO_INCREMENT,
  `account_id` int NOT NULL,
  `journal_id` int NULL,
  `statement_date` date NOT NULL,
  `statement_reference` varchar(150) NULL,
  `statement_description` varchar(255) NULL,
  `statement_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `status` varchar(20) NOT NULL DEFAULT 'matched',
  `matched_by` int NULL,
  `matched_at` datetime NULL,
  `created_by` int NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`reconciliation_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;");

        $this->update_config_version('6.1.3');

        $this->flag_finance_indexes_pending();
        $this->defer_indexes('613');
    }

    public function down()
    {
        $this->db->query("DELETE FROM `tbl_menu` WHERE `link` IN ('admin/accounting/bank_reconciliation', 'admin/accounting/accounting_periods');");
        $this->db->query("DROP TABLE IF EXISTS `tbl_bank_reconciliations`;");
        $this->db->query("DROP TABLE IF EXISTS `tbl_accounting_periods`;");
        foreach (array('reconciled_by', 'reconciled_at', 'reconciled_status', 'is_reversal', 'reversal_journal_id', 'reversal_of_journal_id') as $field) {
            if ($this->db->field_exists($field, 'tbl_journals')) {
                $this->db->query("ALTER TABLE `tbl_journals` DROP COLUMN `" . $field . "`");
            }
        }
        $this->update_config_version('6.1.2');
    }

    private function should_defer_finance_indexes()
    {
        return $this->table_row_count('tbl_journals') > 50000 || $this->table_row_count('tbl_bank_reconciliations') > 50000;
    }

    private function table_row_count($table)
    {
        if (!$this->db->table_exists($table)) {
            return 0;
        }

        $row = $this->db->query("SELECT COUNT(*) AS c FROM `{$table}`")->row_array();
        return (int) ($row['c'] ?? 0);
    }

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

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

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

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

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

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

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