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

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

    public function up()
    {
        if ($this->db->table_exists('tbl_purchases') && !$this->db->field_exists('project_id', 'tbl_purchases')) {
            $this->db->query("ALTER TABLE `tbl_purchases` ADD `project_id` INT(11) NULL DEFAULT '0' AFTER `supplier_id`");
        }

        $this->defer_indexes('620');

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

		if ($this->db->table_exists('tbl_config')) {
			$this->upsert_config('app_config_version', '2');
			$this->upsert_config('version', '6.2.1');
		}
    }

    public function down()
    {
        if ($this->db->table_exists('tbl_purchases') && $this->index_exists('tbl_purchases', 'idx_tbl_purchases_project_id')) {
            $this->db->query("ALTER TABLE `tbl_purchases` DROP INDEX `idx_tbl_purchases_project_id`");
        }

        if ($this->db->table_exists('tbl_purchases') && $this->db->field_exists('project_id', 'tbl_purchases')) {
            $this->db->query("ALTER TABLE `tbl_purchases` DROP COLUMN `project_id`");
        }

        if ($this->db->table_exists('tbl_config')) {
            $this->db->where('config_key', 'version')->update('tbl_config', array('value' => '6.1.9'));
        }
		
		if ($this->db->table_exists('tbl_config')) {
			$this->upsert_config('app_config_version', '1');
			$this->upsert_config('version', '6.2.0');
		}
    }

    private function index_exists($table, $index)
    {
        $indexes = $this->db->query("SHOW INDEX FROM `" . $this->db->escape_str($table) . "` WHERE Key_name = " . $this->db->escape($index))->result();
        return !empty($indexes);
    }


	private function upsert_config($key, $value)
	{
		$exists = $this->db->where('config_key', $key)->get('tbl_config')->row();
		if ($exists) {
			$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 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));
	}
}
