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

class Migration_add_multi_unit_support extends CI_Migration {

    public function up()
    {
        // Add multi-unit fields to db_items table
        $fields_items = array(
            'main_unit_id' => array(
                'type' => 'INT',
                'constraint' => 10,
                'null' => TRUE,
                'comment' => 'Main unit ID (e.g., Box)'
            ),
            'alternate_unit_id' => array(
                'type' => 'INT',
                'constraint' => 10,
                'null' => TRUE,
                'comment' => 'Alternate unit ID (e.g., PCs)'
            ),
            'conversion_factor' => array(
                'type' => 'DECIMAL',
                'constraint' => '20,4',
                'null' => TRUE,
                'default' => 1.0000,
                'comment' => 'Conversion factor (e.g., 1 Box = 12 PCs)'
            ),
            'purchase_price_main' => array(
                'type' => 'DECIMAL',
                'constraint' => '20,4',
                'null' => TRUE,
                'comment' => 'Purchase price in main unit'
            ),
            'purchase_price_alternate' => array(
                'type' => 'DECIMAL',
                'constraint' => '20,4',
                'null' => TRUE,
                'comment' => 'Purchase price in alternate unit'
            ),
            'sales_price_main' => array(
                'type' => 'DECIMAL',
                'constraint' => '20,4',
                'null' => TRUE,
                'comment' => 'Sales price in main unit'
            ),
            'sales_price_alternate' => array(
                'type' => 'DECIMAL',
                'constraint' => '20,4',
                'null' => TRUE,
                'comment' => 'Sales price in alternate unit'
            ),
            'auto_calculate_unit_prices' => array(
                'type' => 'TINYINT',
                'constraint' => 1,
                'default' => 1,
                'comment' => 'Auto calculate prices between units (1=Yes, 0=No)'
            )
        );
        
        // Add fields to db_items if they don't exist
        foreach ($fields_items as $field => $attributes) {
            if (!$this->db->field_exists($field, 'db_items')) {
                $this->dbforge->add_column('db_items', array($field => $attributes));
            }
        }
        
        // Add multi-unit fields to db_purchaseitems table
        $fields_purchase = array(
            'unit_type' => array(
                'type' => 'ENUM',
                'constraint' => array('main', 'alternate'),
                'null' => TRUE,
                'comment' => 'Unit type used in purchase (main or alternate)'
            ),
            'purchase_qty_main' => array(
                'type' => 'DECIMAL',
                'constraint' => '20,4',
                'null' => TRUE,
                'comment' => 'Purchase quantity in main unit'
            ),
            'purchase_qty_alternate' => array(
                'type' => 'DECIMAL',
                'constraint' => '20,4',
                'null' => TRUE,
                'comment' => 'Purchase quantity in alternate unit'
            )
        );
        
        // Add fields to db_purchaseitems if they don't exist
        foreach ($fields_purchase as $field => $attributes) {
            if (!$this->db->field_exists($field, 'db_purchaseitems')) {
                $this->dbforge->add_column('db_purchaseitems', array($field => $attributes));
            }
        }
        
        // Add multi-unit fields to db_salesitems table
        $fields_sales = array(
            'unit_type' => array(
                'type' => 'ENUM',
                'constraint' => array('main', 'alternate'),
                'null' => TRUE,
                'comment' => 'Unit type used in sales (main or alternate)'
            ),
            'sales_qty_main' => array(
                'type' => 'DECIMAL',
                'constraint' => '20,4',
                'null' => TRUE,
                'comment' => 'Sales quantity in main unit'
            ),
            'sales_qty_alternate' => array(
                'type' => 'DECIMAL',
                'constraint' => '20,4',
                'null' => TRUE,
                'comment' => 'Sales quantity in alternate unit'
            )
        );
        
        // Add fields to db_salesitems if they don't exist
        foreach ($fields_sales as $field => $attributes) {
            if (!$this->db->field_exists($field, 'db_salesitems')) {
                $this->dbforge->add_column('db_salesitems', array($field => $attributes));
            }
        }
        
        // Add indexes for better performance
        // Check and add indexes only if they don't exist
        $indexes_to_add = array(
            'db_items' => array(
                'idx_main_unit_id' => 'main_unit_id',
                'idx_alternate_unit_id' => 'alternate_unit_id'
            ),
            'db_purchaseitems' => array(
                'idx_purchase_unit_type' => 'unit_type'
            ),
            'db_salesitems' => array(
                'idx_sales_unit_type' => 'unit_type'
            )
        );
        
        foreach ($indexes_to_add as $table => $table_indexes) {
            foreach ($table_indexes as $index_name => $column) {
                // Check if index exists
                $index_exists = false;
                $index_check = $this->db->query("SHOW INDEXES FROM `{$table}` WHERE Key_name = '{$index_name}'");
                if ($index_check && $index_check->num_rows() > 0) {
                    $index_exists = true;
                }
                
                if (!$index_exists) {
                    $this->db->query("CREATE INDEX `{$index_name}` ON `{$table}` (`{$column}`)");
                }
            }
        }
    }

    public function down()
    {
        // Remove fields from db_items
        $fields_items = array('main_unit_id', 'alternate_unit_id', 'conversion_factor', 
                             'purchase_price_main', 'purchase_price_alternate', 
                             'sales_price_main', 'sales_price_alternate', 'auto_calculate_unit_prices');
        
        foreach ($fields_items as $field) {
            if ($this->db->field_exists($field, 'db_items')) {
                $this->dbforge->drop_column('db_items', $field);
            }
        }
        
        // Remove fields from db_purchaseitems
        $fields_purchase = array('unit_type', 'purchase_qty_main', 'purchase_qty_alternate');
        
        foreach ($fields_purchase as $field) {
            if ($this->db->field_exists($field, 'db_purchaseitems')) {
                $this->dbforge->drop_column('db_purchaseitems', $field);
            }
        }
        
        // Remove fields from db_salesitems
        $fields_sales = array('unit_type', 'sales_qty_main', 'sales_qty_alternate');
        
        foreach ($fields_sales as $field) {
            if ($this->db->field_exists($field, 'db_salesitems')) {
                $this->dbforge->drop_column('db_salesitems', $field);
            }
        }
        
        // Drop indexes if they exist
        $indexes_to_drop = array(
            'db_items' => array('idx_main_unit_id', 'idx_alternate_unit_id'),
            'db_purchaseitems' => array('idx_purchase_unit_type'),
            'db_salesitems' => array('idx_sales_unit_type')
        );
        
        foreach ($indexes_to_drop as $table => $indexes) {
            foreach ($indexes as $index_name) {
                $indexes_check = $this->db->query("SHOW INDEXES FROM `{$table}` WHERE Key_name = '{$index_name}'");
                if ($indexes_check && $indexes_check->num_rows() > 0) {
                    $this->db->query("ALTER TABLE `{$table}` DROP INDEX `{$index_name}`");
                }
            }
        }
    }
}

