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

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

    public function up()
    {
        $this->db->query("CREATE TABLE IF NOT EXISTS `tbl_stock_movements` (
            `stock_movement_id` INT(11) NOT NULL AUTO_INCREMENT,
            `product_id` INT(11) NOT NULL,
            `warehouse_id` INT(11) NOT NULL,
            `qty_before` DECIMAL(16,4) NOT NULL DEFAULT '0.0000',
            `qty_change` DECIMAL(16,4) NOT NULL DEFAULT '0.0000',
            `qty_after` DECIMAL(16,4) NOT NULL DEFAULT '0.0000',
            `movement_type` VARCHAR(40) NOT NULL DEFAULT 'adjust',
            `source_type` VARCHAR(80) NULL DEFAULT NULL,
            `source_id` INT(11) NULL DEFAULT NULL,
            `user_id` INT(11) NULL DEFAULT NULL,
            `note` TEXT NULL,
            `created_at` DATETIME NOT NULL,
            PRIMARY KEY (`stock_movement_id`),
            KEY `idx_stock_movements_product` (`product_id`),
            KEY `idx_stock_movements_warehouse` (`warehouse_id`),
            KEY `idx_stock_movements_source` (`source_type`, `source_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");

        if (!$this->db->field_exists('order', 'tbl_saved_items')) {
		    $this->db->query("ALTER TABLE `tbl_saved_items` ADD `order` INT NULL DEFAULT NULL AFTER `hsn_code`;");
        }

        if (!$this->db->field_exists('status', 'tbl_saved_items')) {
            $this->db->query("ALTER TABLE `tbl_saved_items` ADD `status` VARCHAR(50) NULL DEFAULT 'active' AFTER `order`;");
        }

        $this->db->query("UPDATE `tbl_saved_items` SET `status` = 'active' WHERE `status` IS NULL OR `status` = '';");

		// Set default order for existing items
		$this->db->query("SET @row_number = 0;");
		$this->db->query("UPDATE `tbl_saved_items` SET `order` = (@row_number := @row_number + 1) WHERE `order` IS NULL ORDER BY `saved_items_id` ASC;");
    }
}
