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

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

    public function up()
    {
        $indexes = array(
            array('table' => 'tbl_items', 'name' => 'idx_tbl_items_invoices_id', 'sql' => "ALTER TABLE `tbl_items` ADD INDEX `idx_tbl_items_invoices_id` (`invoices_id`)"),
            array('table' => 'tbl_payments', 'name' => 'idx_tbl_payments_invoices_id', 'sql' => "ALTER TABLE `tbl_payments` ADD INDEX `idx_tbl_payments_invoices_id` (`invoices_id`)"),
            array('table' => 'tbl_transactions', 'name' => 'idx_tbl_transactions_type_date', 'sql' => "ALTER TABLE `tbl_transactions` ADD INDEX `idx_tbl_transactions_type_date` (`type`, `date`)"),
            array('table' => 'tbl_task', 'name' => 'idx_tbl_task_project_status', 'sql' => "ALTER TABLE `tbl_task` ADD INDEX `idx_tbl_task_project_status` (`project_id`, `task_status`)"),
            array('table' => 'tbl_invoices', 'name' => 'idx_tbl_invoices_status_deleted_client', 'sql' => "ALTER TABLE `tbl_invoices` ADD INDEX `idx_tbl_invoices_status_deleted_client` (`status`, `inv_deleted`, `client_id`)"),
            array('table' => 'tbl_project', 'name' => 'idx_tbl_project_status', 'sql' => "ALTER TABLE `tbl_project` ADD INDEX `idx_tbl_project_status` (`project_status`)"),
            array('table' => 'tbl_tickets', 'name' => 'idx_tbl_tickets_status', 'sql' => "ALTER TABLE `tbl_tickets` ADD INDEX `idx_tbl_tickets_status` (`status`)"),
            array('table' => 'tbl_leads', 'name' => 'idx_tbl_leads_status_source', 'sql' => "ALTER TABLE `tbl_leads` ADD INDEX `idx_tbl_leads_status_source` (`lead_status_id`, `lead_source_id`)"),
            array('table' => 'tbl_opportunities', 'name' => 'idx_tbl_opportunities_stages', 'sql' => "ALTER TABLE `tbl_opportunities` ADD INDEX `idx_tbl_opportunities_stages` (`stages`)"),
            array('table' => 'tbl_account_details', 'name' => 'idx_tbl_account_details_user_id', 'sql' => "ALTER TABLE `tbl_account_details` ADD INDEX `idx_tbl_account_details_user_id` (`user_id`)"),
        );

        foreach ($indexes as $index) {
            if (!$this->index_exists($index['table'], $index['name'])) {
                $this->db->query($index['sql']);
            }
        }
    }

    public function down()
    {
        $indexes = array(
            array('table' => 'tbl_items', 'name' => 'idx_tbl_items_invoices_id'),
            array('table' => 'tbl_payments', 'name' => 'idx_tbl_payments_invoices_id'),
            array('table' => 'tbl_transactions', 'name' => 'idx_tbl_transactions_type_date'),
            array('table' => 'tbl_task', 'name' => 'idx_tbl_task_project_status'),
            array('table' => 'tbl_invoices', 'name' => 'idx_tbl_invoices_status_deleted_client'),
            array('table' => 'tbl_project', 'name' => 'idx_tbl_project_status'),
            array('table' => 'tbl_tickets', 'name' => 'idx_tbl_tickets_status'),
            array('table' => 'tbl_leads', 'name' => 'idx_tbl_leads_status_source'),
            array('table' => 'tbl_opportunities', 'name' => 'idx_tbl_opportunities_stages'),
            array('table' => 'tbl_account_details', 'name' => 'idx_tbl_account_details_user_id'),
        );

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

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