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

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

    public function up()
    {
        $this->defer_indexes('611');
        $this->update_config_version('6.1.1');
    }

    public function down()
    {
        @set_time_limit(0);

        $indexes = array(
            array('table' => 'tbl_client', 'name' => 'idx_tbl_client_primary_contact'),
            array('table' => 'tbl_client', 'name' => 'idx_tbl_client_customer_group_id'),
            array('table' => 'tbl_client', 'name' => 'idx_tbl_client_leads_id'),
            array('table' => 'tbl_bug', 'name' => 'idx_tbl_bug_project_id'),
            array('table' => 'tbl_bug', 'name' => 'idx_tbl_bug_reporter_status'),
            array('table' => 'tbl_bug', 'name' => 'idx_tbl_bug_opportunities_id'),
            array('table' => 'tbl_tickets', 'name' => 'idx_tbl_tickets_project_id'),
            array('table' => 'tbl_tickets', 'name' => 'idx_tbl_tickets_reporter'),
            array('table' => 'tbl_tickets', 'name' => 'idx_tbl_tickets_department_status'),
            array('table' => 'tbl_task', 'name' => 'idx_tbl_task_opportunities_status'),
            array('table' => 'tbl_task', 'name' => 'idx_tbl_task_leads_status'),
            array('table' => 'tbl_task', 'name' => 'idx_tbl_task_bug_id'),
            array('table' => 'tbl_task', 'name' => 'idx_tbl_task_module_field'),
            array('table' => 'tbl_payments', 'name' => 'idx_tbl_payments_paid_by_date'),
            array('table' => 'tbl_payments', 'name' => 'idx_tbl_payments_account_id'),
            array('table' => 'tbl_payments', 'name' => 'idx_tbl_payments_trans_id'),
            array('table' => 'tbl_invoices', 'name' => 'idx_tbl_invoices_project_id'),
            array('table' => 'tbl_invoices', 'name' => 'idx_tbl_invoices_client_due_date'),
            array('table' => 'tbl_estimates', 'name' => 'idx_tbl_estimates_client_status_due_date'),
            array('table' => 'tbl_estimates', 'name' => 'idx_tbl_estimates_project_id'),
            array('table' => 'tbl_estimate_items', 'name' => 'idx_tbl_estimate_items_estimates_id'),
            array('table' => 'tbl_proposals', 'name' => 'idx_tbl_proposals_module_status_due_date'),
            array('table' => 'tbl_proposals_items', 'name' => 'idx_tbl_proposals_items_proposals_id'),
            array('table' => 'tbl_purchases', 'name' => 'idx_tbl_purchases_supplier_status_due_date'),
            array('table' => 'tbl_purchase_items', 'name' => 'idx_tbl_purchase_items_purchase_id'),
            array('table' => 'tbl_purchase_payments', 'name' => 'idx_tbl_purchase_payments_purchase_id'),
            array('table' => 'tbl_purchase_payments', 'name' => 'idx_tbl_purchase_payments_trans_id'),
            array('table' => 'tbl_notes', 'name' => 'idx_tbl_notes_user_client'),
            array('table' => 'tbl_task_comment', 'name' => 'idx_tbl_task_comment_module_field'),
            array('table' => 'tbl_attachments', 'name' => 'idx_tbl_attachments_module_field'),
            array('table' => 'tbl_contracts', 'name' => 'idx_tbl_contracts_client_id'),
            array('table' => 'tbl_transactions', 'name' => 'idx_tbl_transactions_project_type'),
            array('table' => 'tbl_transactions', 'name' => 'idx_tbl_transactions_paid_by_type_date'),
            array('table' => 'tbl_transactions', 'name' => 'idx_tbl_transactions_transfer_id'),
        );

        $alterClauses = array();
        foreach ($indexes as $index) {
            if (!$this->table_exists($index['table'])) {
                continue;
            }
            if ($this->index_exists($index['table'], $index['name'])) {
                $alterClauses[$index['table']][] = "DROP INDEX `{$index['name']}`";
            }
        }

        foreach ($alterClauses as $table => $clauses) {
            if (!empty($clauses)) {
                $this->db->query("ALTER TABLE `{$table}` " . implode(', ', $clauses));
            }
        }
    }

    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 add_index_clause($sql)
    {
        return preg_replace('/^ALTER TABLE `[^`]+`\\s+/i', '', $sql);
    }

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

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