-- Run this file after selecting the target database, for example:
-- mysql -u <user> -p <cpanel_database> < 001_add_inventory_conditions.sql

DELIMITER //

DROP PROCEDURE IF EXISTS add_inventory_conditions //
CREATE PROCEDURE add_inventory_conditions()
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inventory' AND COLUMN_NAME = 'damaged_quantity'
  ) THEN
    ALTER TABLE inventory ADD COLUMN damaged_quantity DECIMAL(14,2) NOT NULL DEFAULT 0 AFTER quantity;
  END IF;

  IF NOT EXISTS (
    SELECT 1 FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inventory' AND COLUMN_NAME = 'obsolete_quantity'
  ) THEN
    ALTER TABLE inventory ADD COLUMN obsolete_quantity DECIMAL(14,2) NOT NULL DEFAULT 0 AFTER damaged_quantity;
  END IF;

  IF NOT EXISTS (
    SELECT 1 FROM information_schema.TABLE_CONSTRAINTS
    WHERE CONSTRAINT_SCHEMA = DATABASE() AND TABLE_NAME = 'inventory' AND CONSTRAINT_NAME = 'chk_inventory_condition'
  ) THEN
    ALTER TABLE inventory ADD CONSTRAINT chk_inventory_condition
      CHECK (quantity >= 0 AND damaged_quantity >= 0 AND obsolete_quantity >= 0 AND damaged_quantity + obsolete_quantity <= quantity);
  END IF;

  IF NOT EXISTS (
    SELECT 1 FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'stock_check_details' AND COLUMN_NAME = 'damaged_quantity'
  ) THEN
    ALTER TABLE stock_check_details ADD COLUMN damaged_quantity DECIMAL(14,2) NOT NULL DEFAULT 0 AFTER good_quantity;
  END IF;

  IF NOT EXISTS (
    SELECT 1 FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'stock_check_details' AND COLUMN_NAME = 'system_damaged_quantity'
  ) THEN
    ALTER TABLE stock_check_details ADD COLUMN system_damaged_quantity DECIMAL(14,2) NOT NULL DEFAULT 0 AFTER system_quantity;
  END IF;

  IF NOT EXISTS (
    SELECT 1 FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'stock_check_details' AND COLUMN_NAME = 'system_obsolete_quantity'
  ) THEN
    ALTER TABLE stock_check_details ADD COLUMN system_obsolete_quantity DECIMAL(14,2) NOT NULL DEFAULT 0 AFTER system_damaged_quantity;
  END IF;

  IF NOT EXISTS (
    SELECT 1 FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'stock_check_details' AND COLUMN_NAME = 'obsolete_quantity'
  ) THEN
    ALTER TABLE stock_check_details ADD COLUMN obsolete_quantity DECIMAL(14,2) NOT NULL DEFAULT 0 AFTER damaged_quantity;
  END IF;

  IF EXISTS (
    SELECT 1 FROM information_schema.TABLE_CONSTRAINTS
    WHERE CONSTRAINT_SCHEMA = DATABASE() AND TABLE_NAME = 'stock_check_details' AND CONSTRAINT_NAME = 'chk_check_qty'
  ) THEN
    -- MariaDB uses DROP CONSTRAINT for named CHECK constraints. MySQL also
    -- accepts this form, so the migration remains portable across both.
    ALTER TABLE stock_check_details DROP CONSTRAINT chk_check_qty;
  END IF;

  IF EXISTS (
    SELECT 1 FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'stock_check_details' AND COLUMN_NAME = 'unusable_quantity'
  ) THEN
    UPDATE stock_check_details
    SET obsolete_quantity = COALESCE(unusable_quantity, 0),
        good_quantity = actual_quantity - damaged_quantity - COALESCE(unusable_quantity, 0);
    ALTER TABLE stock_check_details DROP COLUMN unusable_quantity;
  ELSE
    UPDATE stock_check_details
    SET good_quantity = actual_quantity - damaged_quantity - obsolete_quantity;
  END IF;

  IF NOT EXISTS (
    SELECT 1 FROM information_schema.TABLE_CONSTRAINTS
    WHERE CONSTRAINT_SCHEMA = DATABASE() AND TABLE_NAME = 'stock_check_details' AND CONSTRAINT_NAME = 'chk_check_condition'
  ) THEN
    ALTER TABLE stock_check_details ADD CONSTRAINT chk_check_condition
      CHECK (system_quantity >= 0 AND system_damaged_quantity >= 0 AND system_obsolete_quantity >= 0 AND system_damaged_quantity + system_obsolete_quantity <= system_quantity AND actual_quantity >= 0 AND good_quantity >= 0 AND damaged_quantity >= 0 AND obsolete_quantity >= 0 AND good_quantity + damaged_quantity + obsolete_quantity = actual_quantity);
  END IF;
END //

CALL add_inventory_conditions() //
DROP PROCEDURE add_inventory_conditions //

DELIMITER ;

CREATE OR REPLACE VIEW vw_inventory_summary AS
SELECT i.inventory_id, w.warehouse_code, w.warehouse_name, p.product_code, p.product_name,
       c.category_name, u.unit_name, i.quantity, p.minimum_stock,
       (i.quantity - i.damaged_quantity - i.obsolete_quantity) AS available_quantity,
       i.damaged_quantity, i.obsolete_quantity,
       CASE WHEN i.quantity - i.damaged_quantity - i.obsolete_quantity = 0 THEN 'OUT_OF_STOCK'
            WHEN i.quantity - i.damaged_quantity - i.obsolete_quantity <= p.minimum_stock THEN 'LOW_STOCK'
            ELSE 'IN_STOCK' END AS stock_status
FROM inventory i
JOIN warehouses w ON w.warehouse_id = i.warehouse_id
JOIN products p ON p.product_id = i.product_id
JOIN categories c ON c.category_id = p.category_id
JOIN units u ON u.unit_id = p.unit_id;

ALTER TABLE inventory_transactions
  MODIFY COLUMN transaction_type ENUM('IMPORT','EXPORT','ADJUSTMENT','STOCK_ADJUSTMENT','DAMAGE_ADJUSTMENT','OBSOLETE_ADJUSTMENT') NOT NULL;

UPDATE inventory_transactions SET transaction_type = 'STOCK_ADJUSTMENT' WHERE transaction_type = 'ADJUSTMENT';

ALTER TABLE inventory_transactions
  MODIFY COLUMN transaction_type ENUM('IMPORT','EXPORT','STOCK_ADJUSTMENT','DAMAGE_ADJUSTMENT','OBSOLETE_ADJUSTMENT') NOT NULL;

UPDATE stock_checks SET status = 'CONFIRMED' WHERE status = 'ADJUSTED';

ALTER TABLE stock_checks
  MODIFY COLUMN status ENUM('DRAFT','CONFIRMED','CANCELLED') NOT NULL DEFAULT 'DRAFT';