-- Migration: Add combo item support
-- This adds combo sell type and items_combo table for component management

-- Update items table to support combo sell_type
ALTER TABLE `m001_items` MODIFY COLUMN `sell_type` ENUM('unit','fraction','combo') NOT NULL COMMENT 'unit: sell as whole, fraction: sell by weight/portion, combo: combination of multiple items';

-- Create items_combo table to store combo components
CREATE TABLE IF NOT EXISTS `m001_items_combo` (
  `combo_id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_item_id` int(11) NOT NULL COMMENT 'ID of the combo item',
  `component_item_id` int(11) NOT NULL COMMENT 'ID of the component item',
  `quantity_required` decimal(10,3) NOT NULL COMMENT 'Quantity of component needed for 1 combo',
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`combo_id`),
  KEY `parent_item_id` (`parent_item_id`),
  KEY `component_item_id` (`component_item_id`),
  UNIQUE KEY `unique_combo_component` (`parent_item_id`, `component_item_id`),
  CONSTRAINT `m001_items_combo_parent_fk` FOREIGN KEY (`parent_item_id`) REFERENCES `m001_items` (`items_id`) ON DELETE CASCADE,
  CONSTRAINT `m001_items_combo_component_fk` FOREIGN KEY (`component_item_id`) REFERENCES `m001_items` (`items_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Stores combo item component relationships';

-- Insert sample combo data for testing (commented out by default)
-- INSERT INTO m001_items (items_name, items_cat_id, sell_type, selling_price, track_inventory) VALUES 
-- ('Chicken Rice Combo', 1, 'combo', 120.00, 1);
-- 
-- SET @combo_id = LAST_INSERT_ID();
-- INSERT INTO m001_items_combo (parent_item_id, component_item_id, quantity_required) VALUES
-- (@combo_id, 1, 1.0),  -- 1 piece chicken
-- (@combo_id, 2, 1.0);  -- 1 cup rice 