-- ============================================================
-- FIX PACKING SCHEMA — Add missing columns + new tables
-- ============================================================

DELIMITER $$

-- Helper procedure: add column if not exists
CREATE PROCEDURE IF NOT EXISTS add_col_if_missing(
  IN p_table VARCHAR(64),
  IN p_column VARCHAR(64),
  IN p_defn TEXT
)
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = p_table AND COLUMN_NAME = p_column
  ) THEN
    SET @sql = CONCAT('ALTER TABLE `', p_table, '` ADD COLUMN `', p_column, '` ', p_defn);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;
END$$

DELIMITER ;

-- ============================================================
-- 1. PACKING_ORDER_LINES — add columns the API needs
-- ============================================================
CALL add_col_if_missing('packing_order_lines', 'po_color_size_id', 'int unsigned DEFAULT NULL AFTER `packing_order_id`');
CALL add_col_if_missing('packing_order_lines', 'size_name', 'varchar(100) DEFAULT NULL AFTER `color`');
CALL add_col_if_missing('packing_order_lines', 'qty', 'int unsigned NOT NULL DEFAULT 0 AFTER `size_name`');
CALL add_col_if_missing('packing_order_lines', 'pieces_per_carton', 'int unsigned DEFAULT NULL AFTER `qty`');
CALL add_col_if_missing('packing_order_lines', 'cartons_planned', 'int unsigned DEFAULT 0 AFTER `pieces_per_carton`');

-- ============================================================
-- 2. PACKING_STATIONS — add columns the API needs
-- ============================================================
CALL add_col_if_missing('packing_stations', 'station_type', "varchar(50) DEFAULT 'general' AFTER `station_name`");
CALL add_col_if_missing('packing_stations', 'max_cartons', 'int unsigned DEFAULT 50 AFTER `has_scanner`');

-- ============================================================
-- 3. PACKING_SESSIONS — add status alias column
-- ============================================================
CALL add_col_if_missing('packing_sessions', 'status', "varchar(20) DEFAULT 'active' AFTER `session_status`");

-- ============================================================
-- 4. PACKING_AI_ANALYSIS — add columns the API needs
-- ============================================================
CALL add_col_if_missing('packing_ai_analysis', 'quality_score', 'decimal(5,2) DEFAULT NULL AFTER `efficiency_score`');
CALL add_col_if_missing('packing_ai_analysis', 'weight_efficiency', 'decimal(5,2) DEFAULT NULL AFTER `quality_score`');
CALL add_col_if_missing('packing_ai_analysis', 'overall_score', 'decimal(5,2) DEFAULT NULL AFTER `weight_efficiency`');
CALL add_col_if_missing('packing_ai_analysis', 'recommendations', 'json DEFAULT NULL AFTER `overall_score`');
CALL add_col_if_missing('packing_ai_analysis', 'analyzed_by', 'int unsigned DEFAULT NULL AFTER `recommendations`');

-- ============================================================
-- 5. NEW TABLE: packing_instruction_sections
-- ============================================================
CREATE TABLE IF NOT EXISTS `packing_instruction_sections` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `instruction_id` int unsigned NOT NULL,
  `section_title` varchar(255) NOT NULL,
  `section_content` text DEFAULT NULL,
  `sort_order` int unsigned NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_instruction` (`instruction_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 6. NEW TABLE: carton_labels
-- ============================================================
CREATE TABLE IF NOT EXISTS `carton_labels` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `carton_id` int unsigned NOT NULL,
  `packing_order_id` int unsigned NOT NULL,
  `label_type` enum('carton','pallet','shipping','barcode','qr','custom') NOT NULL DEFAULT 'carton',
  `buyer_name` varchar(255) DEFAULT NULL,
  `po_number` varchar(100) DEFAULT NULL,
  `style_no` varchar(100) DEFAULT NULL,
  `color` varchar(100) DEFAULT NULL,
  `size_range` varchar(100) DEFAULT NULL,
  `quantity` int unsigned DEFAULT NULL,
  `destination` varchar(255) DEFAULT NULL,
  `carton_seq` int unsigned DEFAULT NULL,
  `total_cartons` int unsigned DEFAULT NULL,
  `barcode_data` varchar(500) DEFAULT NULL,
  `qr_data` varchar(500) DEFAULT NULL,
  `label_data` json DEFAULT NULL,
  `is_printed` tinyint(1) NOT NULL DEFAULT 0,
  `printed_at` timestamp NULL DEFAULT NULL,
  `printed_count` int unsigned NOT NULL DEFAULT 0,
  `status` enum('generated','printed','void') NOT NULL DEFAULT 'generated',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_carton` (`carton_id`),
  KEY `idx_packing_order` (`packing_order_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 7. NEW TABLE: packing_checklists
-- ============================================================
CREATE TABLE IF NOT EXISTS `packing_checklists` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `packing_order_id` int unsigned NOT NULL,
  `carton_id` int unsigned DEFAULT NULL,
  `checklist_type` enum('pre_packing','carton_seal','pallet_build','container_load','shipment_final') NOT NULL DEFAULT 'pre_packing',
  `check_item` varchar(255) NOT NULL,
  `check_result` enum('pass','fail','na') DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `checked_by` int unsigned DEFAULT NULL,
  `checked_at` timestamp NULL DEFAULT NULL,
  `status` enum('pending','checked') NOT NULL DEFAULT 'pending',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_packing_order` (`packing_order_id`),
  KEY `idx_carton` (`carton_id`),
  KEY `idx_type` (`checklist_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 8. PACKING_INSTRUCTIONS — add columns the API references
-- ============================================================
CALL add_col_if_missing('packing_instructions', 'instruction_code', "varchar(50) DEFAULT NULL AFTER `company_id`");
CALL add_col_if_missing('packing_instructions', 'title', "varchar(255) DEFAULT NULL AFTER `instruction_code`");
CALL add_col_if_missing('packing_instructions', 'description', 'text DEFAULT NULL AFTER `title`');
CALL add_col_if_missing('packing_instructions', 'created_by', 'int unsigned DEFAULT NULL AFTER `status`');

-- ============================================================
-- 9. CARTON_LABELS — add missing column
-- ============================================================
CALL add_col_if_missing('carton_labels', 'company_id', 'int unsigned NOT NULL DEFAULT 1 AFTER `id`');

-- ============================================================
-- CLEANUP: drop helper procedure
-- ============================================================
DROP PROCEDURE IF EXISTS add_col_if_missing;
