-- ============================================================
-- FCQC - Extended Inspection Types & Support Tables
-- Extends enums to cover all 17+ garment inspection stages
-- and creates stage-specific support tables.
-- ============================================================

SET FOREIGN_KEY_CHECKS = 0;

-- ========================
-- 1. EXTEND ENUMS
-- ========================

-- inspection_templates.template_type
ALTER TABLE `inspection_templates`
MODIFY COLUMN `template_type` ENUM(
  'FRI','DUPRO','fabric','loading',
  'supplier_audit','fabric_4point','trims','lab_dip','ppm','pilot_run',
  'cutting','printing','embroidery','sewing_inline','sewing_endline',
  'washing','finishing','needle_detection','packing','carton_drop',
  'container_loading','capa_post_shipment'
) NOT NULL DEFAULT 'FRI';

-- inspection_pages.category
ALTER TABLE `inspection_pages`
MODIFY COLUMN `category` ENUM(
  'universal',
  'fri','dupro','fabric','loading',
  'supplier_audit','fabric_4point','trims','lab_dip','ppm','pilot_run',
  'cutting','printing','embroidery','sewing_inline','sewing_endline',
  'washing','finishing','needle_detection','packing','carton_drop',
  'container_loading','capa_post_shipment','all'
) NOT NULL DEFAULT 'universal';

-- inspection_assignments.inspection_type
ALTER TABLE `inspection_assignments`
MODIFY COLUMN `inspection_type` ENUM(
  'FRI','DUPRO','fabric','loading',
  'supplier_audit','fabric_4point','trims','lab_dip','ppm','pilot_run',
  'cutting','printing','embroidery','sewing_inline','sewing_endline',
  'washing','finishing','needle_detection','packing','carton_drop',
  'container_loading','capa_post_shipment'
) NOT NULL DEFAULT 'FRI';

-- master_defect_library.inspection_type
ALTER TABLE `master_defect_library`
MODIFY COLUMN `inspection_type` ENUM(
  'FRI','DUPRO','fabric','loading','all',
  'supplier_audit','fabric_4point','trims','lab_dip','ppm','pilot_run',
  'cutting','printing','embroidery','sewing_inline','sewing_endline',
  'washing','finishing','needle_detection','packing','carton_drop',
  'container_loading','capa_post_shipment'
) NOT NULL DEFAULT 'all';

-- measurement_templates.inspection_type
ALTER TABLE `measurement_templates`
MODIFY COLUMN `inspection_type` ENUM(
  'FRI','DUPRO','fabric','loading','all',
  'supplier_audit','fabric_4point','trims','lab_dip','ppm','pilot_run',
  'cutting','printing','embroidery','sewing_inline','sewing_endline',
  'washing','finishing','needle_detection','packing','carton_drop',
  'container_loading','capa_post_shipment'
) NOT NULL DEFAULT 'all';

-- inspection_fields.field_type - add new field types for stage-specific data
ALTER TABLE `inspection_fields`
MODIFY COLUMN `field_type` ENUM(
  'text','number','select','multi_select','date','photo','signature',
  'defect_grid','measurement_grid','aql_calculator',
  'textarea','checkbox','radio','info_text','divider',
  'roll_table','bundle_table','hourly_check','wash_test','needle_log',
  'drop_test','container_check','supplier_scorecard','color_standard'
) NOT NULL DEFAULT 'text';

-- ========================
-- 2. SUPPORT TABLES
-- ========================

-- -------------------------------------------------------
-- FABRIC INSPECTION ROLLS (4-Point System per roll)
-- -------------------------------------------------------
DROP TABLE IF EXISTS `fabric_inspection_rolls`;
CREATE TABLE `fabric_inspection_rolls` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `session_id` INT UNSIGNED NOT NULL,
  `roll_number` VARCHAR(100) NOT NULL COMMENT 'Supplier roll number or internal ID',
  `supplier` VARCHAR(255) DEFAULT NULL,
  `fabric_type` VARCHAR(100) DEFAULT NULL COMMENT 'e.g. Single Jersey, Twill, Denim',
  `color` VARCHAR(100) DEFAULT NULL,
  `roll_width` DECIMAL(10,2) DEFAULT NULL COMMENT 'Actual width in cm/inches',
  `roll_length` DECIMAL(10,2) DEFAULT NULL COMMENT 'Actual length in yards/meters',
  `total_points` INT DEFAULT 0 COMMENT 'Total 4-point deduction points for this roll',
  `points_per_100_sq_yds` DECIMAL(10,2) DEFAULT NULL COMMENT 'Points per 100 square yards',
  `defect_count` INT DEFAULT 0 COMMENT 'Number of defects found on this roll',
  `result` ENUM('PASS','FAIL','SECOND') DEFAULT NULL,
  `result_reason` VARCHAR(500) DEFAULT NULL,
  `fabric_grade` VARCHAR(50) DEFAULT NULL COMMENT 'A, B, C, or D grade',
  `photo` VARCHAR(500) DEFAULT NULL,
  `notes` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`session_id`) REFERENCES `inspection_sessions`(`id`) ON DELETE CASCADE,
  INDEX `idx_fabric_roll_session` (`session_id`),
  INDEX `idx_fabric_roll_result` (`result`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -------------------------------------------------------
-- CUTTING INSPECTION BUNDLES (bundle/panel-level data)
-- -------------------------------------------------------
DROP TABLE IF EXISTS `cutting_inspection_bundles`;
CREATE TABLE `cutting_inspection_bundles` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `session_id` INT UNSIGNED NOT NULL,
  `bundle_number` VARCHAR(100) NOT NULL,
  `lay_number` INT DEFAULT NULL COMMENT 'Lay number in the cutting table',
  `marker_number` VARCHAR(100) DEFAULT NULL COMMENT 'Marker/pattern reference',
  `total_plys` INT DEFAULT NULL COMMENT 'Total number of plies in bundle',
  `inspected_plys` INT DEFAULT NULL COMMENT 'Number of plies inspected',
  `defect_type` VARCHAR(100) DEFAULT NULL COMMENT 'Fusing, notching, edge damage, etc',
  `defect_count` INT DEFAULT 0,
  `result` ENUM('PASS','FAIL','REWORK') DEFAULT NULL,
  `rework_action` VARCHAR(500) DEFAULT NULL,
  `photo` VARCHAR(500) DEFAULT NULL,
  `notes` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`session_id`) REFERENCES `inspection_sessions`(`id`) ON DELETE CASCADE,
  INDEX `idx_cutting_bundle_session` (`session_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -------------------------------------------------------
-- INLINE INSPECTION CHECKS (hourly traffic-light checks)
-- -------------------------------------------------------
DROP TABLE IF EXISTS `inline_inspection_checks`;
CREATE TABLE `inline_inspection_checks` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `session_id` INT UNSIGNED NOT NULL,
  `check_hour` DATETIME NOT NULL COMMENT 'Timestamp of the hourly check',
  `hour_label` VARCHAR(50) DEFAULT NULL COMMENT 'e.g. Hour 1, Hour 2, or 09:00, 10:00',
  `line_number` VARCHAR(50) DEFAULT NULL COMMENT 'Production line identifier',
  `operator_name` VARCHAR(255) DEFAULT NULL,
  `status` ENUM('GREEN','YELLOW','RED') NOT NULL DEFAULT 'GREEN',
  `defects_found` INT DEFAULT 0,
  `defects_detail` JSON DEFAULT NULL,
  `production_output` INT DEFAULT NULL COMMENT 'Pieces produced in this hour',
  `action_taken` TEXT DEFAULT NULL COMMENT 'If YELLOW or RED, what action was taken',
  `photo` VARCHAR(500) DEFAULT NULL,
  `notes` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`session_id`) REFERENCES `inspection_sessions`(`id`) ON DELETE CASCADE,
  INDEX `idx_inline_session` (`session_id`),
  INDEX `idx_inline_hour` (`check_hour`),
  INDEX `idx_inline_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -------------------------------------------------------
-- WASHING INSPECTION TESTS
-- -------------------------------------------------------
DROP TABLE IF EXISTS `washing_inspection_tests`;
CREATE TABLE `washing_inspection_tests` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `session_id` INT UNSIGNED NOT NULL,
  `test_type` VARCHAR(100) NOT NULL COMMENT 'e.g. Dimensional Stability, Color Fastness, Shrinkage, Spirality',
  `sample_count` INT DEFAULT 1,
  `wash_cycles` INT DEFAULT 1 COMMENT 'Number of wash cycles',
  `wash_temperature` VARCHAR(50) DEFAULT NULL COMMENT 'e.g. 40°C, 60°C',
  `parameter_name` VARCHAR(100) DEFAULT NULL COMMENT 'e.g. Length Change, Width Change, Twist',
  `spec_min` DECIMAL(10,2) DEFAULT NULL COMMENT 'Minimum acceptable value',
  `spec_max` DECIMAL(10,2) DEFAULT NULL COMMENT 'Maximum acceptable value',
  `actual_value` DECIMAL(10,2) DEFAULT NULL,
  `unit` VARCHAR(20) DEFAULT '%',
  `result` ENUM('PASS','FAIL','BORDERLINE') DEFAULT NULL,
  `grade` VARCHAR(50) DEFAULT NULL COMMENT 'AATCC rating or similar',
  `photo` VARCHAR(500) DEFAULT NULL,
  `notes` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`session_id`) REFERENCES `inspection_sessions`(`id`) ON DELETE CASCADE,
  INDEX `idx_wash_session` (`session_id`),
  INDEX `idx_wash_type` (`test_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -------------------------------------------------------
-- NEEDLE DETECTION LOGS (Metal detector / needle scan)
-- -------------------------------------------------------
DROP TABLE IF EXISTS `needle_detection_logs`;
CREATE TABLE `needle_detection_logs` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `session_id` INT UNSIGNED NOT NULL,
  `machine_id` VARCHAR(100) DEFAULT NULL COMMENT 'Needle detector machine ID/serial',
  `machine_name` VARCHAR(255) DEFAULT NULL,
  `scan_time` DATETIME NOT NULL,
  `total_pieces` INT DEFAULT 0 COMMENT 'Pieces scanned in this batch',
  `alarms` INT DEFAULT 0 COMMENT 'Number of alarms triggered',
  `alarm_details` JSON DEFAULT NULL COMMENT 'Details of each alarm (piece ID, location, type)',
  `found_needles` INT DEFAULT 0 COMMENT 'Confirmed broken needles found',
  `result` ENUM('PASS','FAIL','PARTIAL') DEFAULT NULL,
  `operator_name` VARCHAR(255) DEFAULT NULL,
  `photo` VARCHAR(500) DEFAULT NULL,
  `notes` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`session_id`) REFERENCES `inspection_sessions`(`id`) ON DELETE CASCADE,
  INDEX `idx_needle_session` (`session_id`),
  INDEX `idx_needle_result` (`result`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -------------------------------------------------------
-- CARTON DROP TESTS
-- -------------------------------------------------------
DROP TABLE IF EXISTS `carton_drop_tests`;
CREATE TABLE `carton_drop_tests` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `session_id` INT UNSIGNED NOT NULL,
  `carton_id` VARCHAR(100) DEFAULT NULL COMMENT 'Carton reference/number',
  `drop_height` DECIMAL(10,2) DEFAULT NULL COMMENT 'Drop height in cm/inches',
  `drop_orientation` ENUM('face','edge','corner','flat') DEFAULT NULL,
  `drop_count` INT DEFAULT 1,
  `result` ENUM('PASS','FAIL','MINOR_DAMAGE') DEFAULT NULL,
  `damage_description` TEXT DEFAULT NULL,
  `internal_packaging_ok` TINYINT(1) DEFAULT 1,
  `product_damage` TINYINT(1) DEFAULT 0,
  `photo_before` VARCHAR(500) DEFAULT NULL,
  `photo_after` VARCHAR(500) DEFAULT NULL,
  `notes` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`session_id`) REFERENCES `inspection_sessions`(`id`) ON DELETE CASCADE,
  INDEX `idx_drop_session` (`session_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -------------------------------------------------------
-- CONTAINER LOADING CHECKS (CLI data)
-- -------------------------------------------------------
DROP TABLE IF EXISTS `container_loading_checks`;
CREATE TABLE `container_loading_checks` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `session_id` INT UNSIGNED NOT NULL,
  `container_number` VARCHAR(50) NOT NULL COMMENT 'Container number/ID',
  `seal_number` VARCHAR(100) DEFAULT NULL,
  `loading_date` DATE DEFAULT NULL,
  `container_size` VARCHAR(50) DEFAULT NULL COMMENT 'e.g. 20GP, 40GP, 40HQ, 45HC',
  `truck_plate` VARCHAR(50) DEFAULT NULL,
  `driver_name` VARCHAR(255) DEFAULT NULL,
  `carton_count_loaded` INT DEFAULT 0,
  `carton_count_document` INT DEFAULT 0 COMMENT 'Cartons per packing list',
  `quantity_loaded` INT DEFAULT 0 COMMENT 'Pieces loaded',
  `quantity_document` INT DEFAULT 0 COMMENT 'Pieces per document',
  `container_condition` ENUM('good','minor_damage','major_damage','rejected') DEFAULT NULL,
  `interior_clean` TINYINT(1) DEFAULT 1,
  `floor_condition` TINYINT(1) DEFAULT 1,
  `ventilation_ok` TINYINT(1) DEFAULT 1,
  `stuffing_pattern_ok` TINYINT(1) DEFAULT 1 COMMENT 'Loading pattern correct per plan',
  `temperature` DECIMAL(5,1) DEFAULT NULL COMMENT 'Internal container temp if applicable',
  `humidity` DECIMAL(5,1) DEFAULT NULL COMMENT 'Internal humidity %',
  `photo_interior` VARCHAR(500) DEFAULT NULL,
  `photo_seal` VARCHAR(500) DEFAULT NULL,
  `photo_general` VARCHAR(500) DEFAULT NULL,
  `result` ENUM('PASS','FAIL','CONDITIONAL') DEFAULT NULL,
  `inspector_name` VARCHAR(255) DEFAULT NULL,
  `loading_time_start` TIME DEFAULT NULL,
  `loading_time_end` TIME DEFAULT NULL,
  `notes` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`session_id`) REFERENCES `inspection_sessions`(`id`) ON DELETE CASCADE,
  INDEX `idx_container_session` (`session_id`),
  INDEX `idx_container_number` (`container_number`),
  INDEX `idx_container_result` (`result`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -------------------------------------------------------
-- AQL RESULTS (stored evaluation history per session)
-- -------------------------------------------------------
DROP TABLE IF EXISTS `aql_results`;
CREATE TABLE `aql_results` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `session_id` INT UNSIGNED NOT NULL,
  `profile_id` INT DEFAULT NULL,
  `profile_name` VARCHAR(100) DEFAULT NULL,
  `inspection_level` VARCHAR(10) DEFAULT 'II',
  `lot_size` INT DEFAULT NULL,
  `sample_size` INT NOT NULL,
  `code_letter` VARCHAR(2) DEFAULT NULL,
  `critical_aql` DECIMAL(4,2) DEFAULT 0.00,
  `major_aql` DECIMAL(4,2) DEFAULT 2.50,
  `minor_aql` DECIMAL(4,2) DEFAULT 4.00,
  `critical_found` INT DEFAULT 0,
  `major_found` INT DEFAULT 0,
  `minor_found` INT DEFAULT 0,
  `critical_accept` INT DEFAULT 0,
  `major_accept` INT DEFAULT 0,
  `minor_accept` INT DEFAULT 0,
  `critical_reject` INT DEFAULT 0,
  `major_reject` INT DEFAULT 0,
  `minor_reject` INT DEFAULT 0,
  `result` ENUM('PASS','FAIL') NOT NULL,
  `reason` TEXT DEFAULT NULL,
  `score` DECIMAL(5,2) DEFAULT NULL,
  `calculated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`session_id`) REFERENCES `inspection_sessions`(`id`) ON DELETE CASCADE,
  INDEX `idx_aql_result_session` (`session_id`),
  INDEX `idx_aql_result_outcome` (`result`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
