-- ============================================================
-- FashCognitive Cutting Planning System
-- Tables + seed data. Additive (IF NOT EXISTS), follows the
-- project convention: company_id scoping, no FK constraints.
-- ============================================================

-- ------------------------------------------------------------------
-- 1. CUTTING PLANS (planning entity per production order)
-- ------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `cutting_plans` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT '1',
  `plan_no` varchar(40) NOT NULL,
  `production_order_id` int unsigned NOT NULL,
  `style_id` int unsigned DEFAULT NULL,
  `color` varchar(50) DEFAULT NULL,
  `size_ratio` json DEFAULT NULL COMMENT 'e.g. {"S":2,"M":3,"L":3,"XL":2}',
  `planned_qty` int unsigned NOT NULL DEFAULT '0',
  `fabric_required_kg` decimal(12,2) NOT NULL DEFAULT '0.00',
  `fabric_allocated_kg` decimal(12,2) NOT NULL DEFAULT '0.00',
  `marker_efficiency_pct` decimal(5,2) NOT NULL DEFAULT '0.00',
  `planned_cut_date` date DEFAULT NULL,
  `status` enum('draft','approved','released','completed','cancelled') NOT NULL DEFAULT 'draft',
  `notes` text,
  `created_by` int unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `plan_no` (`plan_no`,`company_id`),
  KEY `company_id` (`company_id`),
  KEY `production_order_id` (`production_order_id`),
  KEY `style_id` (`style_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ------------------------------------------------------------------
-- 2. CUT ORDERS
-- ------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `cut_orders` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT '1',
  `cut_order_no` varchar(40) NOT NULL,
  `cutting_plan_id` int unsigned DEFAULT NULL,
  `production_order_id` int unsigned DEFAULT NULL,
  `buyer_po_id` int unsigned DEFAULT NULL,
  `style_id` int unsigned DEFAULT NULL,
  `color` varchar(50) DEFAULT NULL,
  `shade` varchar(20) DEFAULT NULL,
  `order_qty` int unsigned NOT NULL DEFAULT '0',
  `planned_cut_qty` int unsigned NOT NULL DEFAULT '0',
  `cut_qty` int unsigned NOT NULL DEFAULT '0',
  `cutting_table_id` int unsigned DEFAULT NULL,
  `marker_id` int unsigned DEFAULT NULL,
  `scheduled_date` date DEFAULT NULL,
  `target_date` date DEFAULT NULL,
  `status` enum('planned','released','spreading','cutting','completed','cancelled') NOT NULL DEFAULT 'planned',
  `notes` text,
  `created_by` int unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `cut_order_no` (`cut_order_no`,`company_id`),
  KEY `company_id` (`company_id`),
  KEY `cutting_plan_id` (`cutting_plan_id`),
  KEY `production_order_id` (`production_order_id`),
  KEY `style_id` (`style_id`),
  KEY `cutting_table_id` (`cutting_table_id`),
  KEY `marker_id` (`marker_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ------------------------------------------------------------------
-- 3. CUT ORDER LINES (size breakdown)
-- ------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `cut_order_lines` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT '1',
  `cut_order_id` int unsigned NOT NULL,
  `size` varchar(20) NOT NULL,
  `quantity` int unsigned NOT NULL DEFAULT '0',
  `cut_qty` int unsigned NOT NULL DEFAULT '0',
  `rejected_qty` int unsigned NOT NULL DEFAULT '0',
  `checked_qty` int unsigned NOT NULL DEFAULT '0',
  `status` enum('pending','cut','inspected','rejected') NOT NULL DEFAULT 'pending',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `cut_order_id` (`cut_order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ------------------------------------------------------------------
-- 4. MARKER LIBRARY
-- ------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `marker_library` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT '1',
  `marker_code` varchar(30) NOT NULL,
  `style_id` int unsigned NOT NULL,
  `color` varchar(50) DEFAULT NULL,
  `fabric_width` decimal(8,2) DEFAULT NULL COMMENT 'inches',
  `marker_length` decimal(10,2) DEFAULT NULL COMMENT 'meters',
  `efficiency_pct` decimal(5,2) NOT NULL DEFAULT '0.00',
  `ply_count` int unsigned NOT NULL DEFAULT '0',
  `pieces_per_marker` int unsigned NOT NULL DEFAULT '0',
  `size_ratio` json DEFAULT NULL COMMENT 'e.g. {"S":2,"M":3,"L":3,"XL":2}',
  `fabric_consumption_kg` decimal(10,4) DEFAULT NULL COMMENT 'per garment',
  `pattern_file` varchar(255) DEFAULT NULL,
  `status` enum('draft','active','archived') NOT NULL DEFAULT 'draft',
  `notes` text,
  `created_by` int unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `marker_code` (`marker_code`,`company_id`),
  KEY `company_id` (`company_id`),
  KEY `style_id` (`style_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ------------------------------------------------------------------
-- 5. MARKER VERSIONS
-- ------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `marker_versions` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT '1',
  `marker_id` int unsigned NOT NULL,
  `version_no` int unsigned NOT NULL DEFAULT '1',
  `marker_length` decimal(10,2) DEFAULT NULL,
  `efficiency_pct` decimal(5,2) NOT NULL DEFAULT '0.00',
  `size_ratio` json DEFAULT NULL,
  `changes` text,
  `created_by` int unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `marker_id` (`marker_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ------------------------------------------------------------------
-- 6. FABRIC ROLLS (cutting-floor roll inventory)
-- ------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `fabric_rolls` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT '1',
  `roll_no` varchar(40) NOT NULL,
  `source_inspection_roll_id` int unsigned DEFAULT NULL COMMENT 'fabric_inspection_rolls.id',
  `fabric_item_id` int unsigned DEFAULT NULL COMMENT 'master_items.id',
  `fabric_name` varchar(255) DEFAULT NULL,
  `color` varchar(50) DEFAULT NULL,
  `shade` varchar(20) DEFAULT NULL,
  `gsm` decimal(8,2) DEFAULT NULL,
  `roll_width` decimal(8,2) DEFAULT NULL COMMENT 'cm',
  `roll_length_m` decimal(10,2) NOT NULL DEFAULT '0.00',
  `roll_weight_kg` decimal(10,2) NOT NULL DEFAULT '0.00',
  `remaining_length_m` decimal(10,2) NOT NULL DEFAULT '0.00',
  `remaining_weight_kg` decimal(10,2) NOT NULL DEFAULT '0.00',
  `defect_points` int NOT NULL DEFAULT '0',
  `defect_count` int NOT NULL DEFAULT '0',
  `location` varchar(100) DEFAULT NULL,
  `status` enum('available','allocated','spreading','cut','finished','remnant','blocked') NOT NULL DEFAULT 'available',
  `remarks` varchar(500) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `roll_no` (`roll_no`,`company_id`),
  KEY `company_id` (`company_id`),
  KEY `fabric_item_id` (`fabric_item_id`),
  KEY `shade` (`shade`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ------------------------------------------------------------------
-- 7. FABRIC ALLOCATIONS (fabric requirement per cut order)
-- ------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `fabric_allocations` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT '1',
  `cut_order_id` int unsigned NOT NULL,
  `fabric_item_id` int unsigned DEFAULT NULL,
  `fabric_name` varchar(255) DEFAULT NULL,
  `color` varchar(50) DEFAULT NULL,
  `shade` varchar(20) DEFAULT NULL,
  `consumption_per_unit` decimal(10,4) NOT NULL DEFAULT '0.0000' COMMENT 'kg per garment',
  `order_qty` int unsigned NOT NULL DEFAULT '0',
  `fabric_required_kg` decimal(12,2) NOT NULL DEFAULT '0.00',
  `marker_efficiency_pct` decimal(5,2) NOT NULL DEFAULT '100.00',
  `actual_need_kg` decimal(12,2) NOT NULL DEFAULT '0.00',
  `allocated_kg` decimal(12,2) NOT NULL DEFAULT '0.00',
  `consumed_kg` decimal(12,2) NOT NULL DEFAULT '0.00',
  `status` enum('pending','allocated','partial','consumed','blocked') NOT NULL DEFAULT 'pending',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `cut_order_id` (`cut_order_id`),
  KEY `fabric_item_id` (`fabric_item_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ------------------------------------------------------------------
-- 8. FABRIC ROLL ALLOCATIONS (which rolls feed which allocation)
-- ------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `fabric_roll_allocations` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT '1',
  `allocation_id` int unsigned NOT NULL,
  `fabric_roll_id` int unsigned NOT NULL,
  `shade` varchar(20) DEFAULT NULL,
  `allocated_kg` decimal(10,2) NOT NULL DEFAULT '0.00',
  `allocated_length_m` decimal(10,2) NOT NULL DEFAULT '0.00',
  `status` enum('allocated','spread','returned','consumed') NOT NULL DEFAULT 'allocated',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `allocation_id` (`allocation_id`),
  KEY `fabric_roll_id` (`fabric_roll_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ------------------------------------------------------------------
-- 9. CUTTING TABLES
-- ------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `cutting_tables` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT '1',
  `table_no` varchar(20) NOT NULL,
  `location` varchar(100) DEFAULT NULL,
  `length_m` decimal(8,2) DEFAULT NULL,
  `width_m` decimal(8,2) DEFAULT NULL,
  `status` enum('available','in_use','maintenance') NOT NULL DEFAULT 'available',
  `current_cut_order_id` int unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `table_no` (`table_no`,`company_id`),
  KEY `company_id` (`company_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ------------------------------------------------------------------
-- 10. LAY PLANS
-- ------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `lay_plans` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT '1',
  `cut_order_id` int unsigned NOT NULL,
  `lay_no` varchar(30) NOT NULL,
  `marker_id` int unsigned NOT NULL,
  `cutting_table_id` int unsigned DEFAULT NULL,
  `color` varchar(50) DEFAULT NULL,
  `shade` varchar(20) DEFAULT NULL,
  `plies` int unsigned NOT NULL DEFAULT '0',
  `lay_length_m` decimal(10,2) NOT NULL DEFAULT '0.00',
  `lay_width_m` decimal(8,2) NOT NULL DEFAULT '0.00',
  `pieces_per_lay` int unsigned NOT NULL DEFAULT '0',
  `expected_pieces` int unsigned NOT NULL DEFAULT '0',
  `marker_efficiency_pct` decimal(5,2) NOT NULL DEFAULT '0.00',
  `status` enum('planned','spreading','lay_ready','cutting','completed','cancelled') NOT NULL DEFAULT 'planned',
  `started_at` datetime DEFAULT NULL,
  `completed_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `lay_no` (`lay_no`,`company_id`),
  KEY `company_id` (`company_id`),
  KEY `cut_order_id` (`cut_order_id`),
  KEY `marker_id` (`marker_id`),
  KEY `cutting_table_id` (`cutting_table_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ------------------------------------------------------------------
-- 11. SPREAD PLANS
-- ------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `spread_plans` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT '1',
  `lay_plan_id` int unsigned NOT NULL,
  `cutting_table_id` int unsigned DEFAULT NULL,
  `spreader_machine` varchar(50) DEFAULT NULL,
  `operator_name` varchar(100) DEFAULT NULL,
  `start_time` datetime DEFAULT NULL,
  `end_time` datetime DEFAULT NULL,
  `relaxation_minutes` int unsigned NOT NULL DEFAULT '0',
  `relaxation_end_time` datetime DEFAULT NULL,
  `plies_spread` int unsigned NOT NULL DEFAULT '0',
  `status` enum('planned','spreading','lay_ready','cutting','completed') NOT NULL DEFAULT 'planned',
  `notes` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `lay_plan_id` (`lay_plan_id`),
  KEY `cutting_table_id` (`cutting_table_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ------------------------------------------------------------------
-- 12. CUTTING SCHEDULE (board)
-- ------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `cutting_schedule` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT '1',
  `schedule_date` date NOT NULL,
  `cutting_table_id` int unsigned NOT NULL,
  `cut_order_id` int unsigned NOT NULL,
  `shift` enum('day','night','general') NOT NULL DEFAULT 'general',
  `start_time` time DEFAULT NULL,
  `end_time` time DEFAULT NULL,
  `operator_name` varchar(100) DEFAULT NULL,
  `status` enum('planned','spreading','cutting','completed','delayed') NOT NULL DEFAULT 'planned',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `schedule_date` (`schedule_date`),
  KEY `cutting_table_id` (`cutting_table_id`),
  KEY `cut_order_id` (`cut_order_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ------------------------------------------------------------------
-- 13. BUNDLE HEADERS (cutting-floor bundles)
-- ------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `bundle_headers` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT '1',
  `bundle_no` varchar(40) NOT NULL,
  `barcode` varchar(100) DEFAULT NULL,
  `qr_code` varchar(255) DEFAULT NULL,
  `lay_plan_id` int unsigned DEFAULT NULL,
  `cut_order_id` int unsigned NOT NULL,
  `production_order_id` int unsigned DEFAULT NULL,
  `style_id` int unsigned DEFAULT NULL,
  `color` varchar(50) DEFAULT NULL,
  `shade` varchar(20) DEFAULT NULL,
  `size` varchar(20) DEFAULT NULL,
  `quantity` int unsigned NOT NULL DEFAULT '0',
  `checked_qty` int unsigned NOT NULL DEFAULT '0',
  `rejected_qty` int unsigned NOT NULL DEFAULT '0',
  `status` enum('created','inspection','transfer_ready','transferred') NOT NULL DEFAULT 'created',
  `created_by` int unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `bundle_no` (`bundle_no`,`company_id`),
  KEY `company_id` (`company_id`),
  KEY `lay_plan_id` (`lay_plan_id`),
  KEY `cut_order_id` (`cut_order_id`),
  KEY `production_order_id` (`production_order_id`),
  KEY `size` (`size`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ------------------------------------------------------------------
-- 14. BUNDLE DETAILS (panels per bundle)
-- ------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `bundle_details` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT '1',
  `bundle_id` int unsigned NOT NULL,
  `panel_name` varchar(100) NOT NULL,
  `quantity` int unsigned NOT NULL DEFAULT '0',
  `checked_qty` int unsigned NOT NULL DEFAULT '0',
  `rejected_qty` int unsigned NOT NULL DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `bundle_id` (`bundle_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ------------------------------------------------------------------
-- 15. FABRIC CONSUMPTION (per cut order / lay)
-- ------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `fabric_consumption` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT '1',
  `cut_order_id` int unsigned NOT NULL,
  `lay_plan_id` int unsigned DEFAULT NULL,
  `fabric_item_id` int unsigned DEFAULT NULL,
  `fabric_name` varchar(255) DEFAULT NULL,
  `allocated_kg` decimal(12,2) NOT NULL DEFAULT '0.00',
  `consumed_kg` decimal(12,2) NOT NULL DEFAULT '0.00',
  `balance_kg` decimal(12,2) NOT NULL DEFAULT '0.00',
  `waste_kg` decimal(12,2) NOT NULL DEFAULT '0.00',
  `waste_pct` decimal(5,2) NOT NULL DEFAULT '0.00',
  `marker_efficiency_pct` decimal(5,2) NOT NULL DEFAULT '0.00',
  `recorded_by` int unsigned DEFAULT NULL,
  `recorded_at` datetime DEFAULT NULL,
  `notes` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `cut_order_id` (`cut_order_id`),
  KEY `lay_plan_id` (`lay_plan_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ------------------------------------------------------------------
-- 16. FABRIC REMNANTS
-- ------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `fabric_remnants` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT '1',
  `roll_no` varchar(40) NOT NULL,
  `fabric_item_id` int unsigned DEFAULT NULL,
  `fabric_name` varchar(255) DEFAULT NULL,
  `color` varchar(50) DEFAULT NULL,
  `shade` varchar(20) DEFAULT NULL,
  `remnant_length_m` decimal(10,2) NOT NULL DEFAULT '0.00',
  `remnant_weight_kg` decimal(10,2) NOT NULL DEFAULT '0.00',
  `remnant_width_cm` decimal(8,2) DEFAULT NULL,
  `location` varchar(100) DEFAULT NULL,
  `status` enum('available','reserved','consumed') NOT NULL DEFAULT 'available',
  `ai_suggestion` varchar(500) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `fabric_item_id` (`fabric_item_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ------------------------------------------------------------------
-- 17. PANEL INSPECTION
-- ------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `panel_inspection` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT '1',
  `bundle_id` int unsigned NOT NULL,
  `lay_plan_id` int unsigned DEFAULT NULL,
  `inspector_name` varchar(100) DEFAULT NULL,
  `inspected_at` datetime DEFAULT NULL,
  `panel_count` int unsigned NOT NULL DEFAULT '0',
  `checked_count` int unsigned NOT NULL DEFAULT '0',
  `defect_count` int unsigned NOT NULL DEFAULT '0',
  `shade_ok` tinyint(1) NOT NULL DEFAULT '1',
  `marker_ok` tinyint(1) NOT NULL DEFAULT '1',
  `result` enum('pass','fail','rework') NOT NULL DEFAULT 'pass',
  `notes` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `bundle_id` (`bundle_id`),
  KEY `lay_plan_id` (`lay_plan_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ------------------------------------------------------------------
-- 18. CUTTING QUALITY (checks along the cutting floor)
-- ------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `cutting_quality` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT '1',
  `cut_order_id` int unsigned DEFAULT NULL,
  `lay_plan_id` int unsigned DEFAULT NULL,
  `bundle_id` int unsigned DEFAULT NULL,
  `quality_type` enum('marker_check','shade_check','panel_check','bundle_verify') NOT NULL,
  `result` enum('pass','fail','warn') NOT NULL DEFAULT 'pass',
  `defect_type` varchar(100) DEFAULT NULL,
  `defect_count` int unsigned NOT NULL DEFAULT '0',
  `notes` text,
  `checked_by` int unsigned DEFAULT NULL,
  `checked_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `cut_order_id` (`cut_order_id`),
  KEY `lay_plan_id` (`lay_plan_id`),
  KEY `bundle_id` (`bundle_id`),
  KEY `quality_type` (`quality_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ------------------------------------------------------------------
-- 19. CUTTING REPORTS (generated snapshots)
-- ------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `cutting_reports` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT '1',
  `report_type` enum('daily_cutting','cut_order_summary','lay_summary','roll_consumption','marker_efficiency','fabric_utilization','remnant','bundle','cutting_cost') NOT NULL,
  `report_date` date NOT NULL,
  `period_start` date DEFAULT NULL,
  `period_end` date DEFAULT NULL,
  `report_data` json DEFAULT NULL,
  `generated_by` int unsigned DEFAULT NULL,
  `generated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `report_type` (`report_type`),
  KEY `report_date` (`report_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ------------------------------------------------------------------
-- 20. CUTTING AI ANALYSIS
-- ------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `cutting_ai_analysis` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT '1',
  `analysis_type` enum('marker_optimization','roll_allocation','table_scheduling','waste_prediction','camera_verification') NOT NULL,
  `entity_id` int unsigned DEFAULT NULL,
  `input_snapshot` json DEFAULT NULL,
  `result` json DEFAULT NULL,
  `score` decimal(5,2) DEFAULT NULL,
  `recommendation` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `analysis_type` (`analysis_type`),
  KEY `entity_id` (`entity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ============================================================
-- SEED DATA
-- ============================================================

-- Cutting tables (6)
INSERT INTO `cutting_tables` (company_id, table_no, location, length_m, width_m, status) VALUES
(1, 'Table-01', 'Cutting Floor A', 20.00, 1.80, 'in_use'),
(1, 'Table-02', 'Cutting Floor A', 20.00, 1.80, 'in_use'),
(1, 'Table-03', 'Cutting Floor A', 25.00, 1.90, 'in_use'),
(1, 'Table-04', 'Cutting Floor B', 18.00, 1.70, 'available'),
(1, 'Table-05', 'Cutting Floor B', 18.00, 1.70, 'available'),
(1, 'Table-06', 'Cutting Floor B', 22.00, 1.80, 'maintenance');

-- Marker library (style 1 is HM-101 Basic Cotton T-Shirt; others library-only)
INSERT INTO `marker_library`
(company_id, marker_code, style_id, color, fabric_width, marker_length, efficiency_pct, ply_count, pieces_per_marker, size_ratio, fabric_consumption_kg, status) VALUES
(1, 'M001', 1, 'Black',    72.00, 18.40, 89.00, 120, 960, JSON_OBJECT('S',2,'M',3,'L',3,'XL',2), 1.2000, 'active'),
(1, 'M002', 1, 'Black',    68.00, 17.20, 86.00, 100, 780, JSON_OBJECT('S',2,'M',2,'L',3,'XL',2), 1.2200, 'active'),
(1, 'M003', 1, 'White',    72.00, 18.80, 90.00, 110, 900, JSON_OBJECT('S',2,'M',4,'L',3,'XL',2), 1.1900, 'draft'),
(1, 'M004', 5, 'Grey',     58.00, 15.30, 87.00, 120, 900, JSON_OBJECT('XS',2,'S',3,'M',3,'L',2), 0.4500, 'active'),
(1, 'M005', 6, 'Olive',    72.00, 20.10, 88.50, 90, 720, JSON_OBJECT('S',2,'M',3,'L',3,'XL',1), 1.6000, 'active'),
(1, 'M006', 2, 'Khaki',    60.00, 16.80, 85.00, 130, 910, JSON_OBJECT('30',2,'32',3,'34',3,'36',2), 1.3500, 'archived');

-- Marker versions
INSERT INTO `marker_versions` (company_id, marker_id, version_no, marker_length, efficiency_pct, size_ratio, changes, created_by) VALUES
(1, 1, 1, 19.10, 86.00, JSON_OBJECT('S',2,'M',3,'L',3,'XL',2), 'Initial marker', 1),
(1, 1, 2, 18.40, 89.00, JSON_OBJECT('S',2,'M',3,'L',3,'XL',2), 'Nested shoulder seam to gain 3% efficiency', 1),
(1, 2, 1, 17.20, 86.00, JSON_OBJECT('S',2,'M',2,'L',3,'XL',2), 'Narrow width marker for 68 inch fabric', 1);

-- Fabric rolls (180gsm jersey ~144kg per 500m roll)
INSERT INTO `fabric_rolls`
(company_id, roll_no, fabric_item_id, fabric_name, color, shade, gsm, roll_width, roll_length_m, roll_weight_kg, remaining_length_m, remaining_weight_kg, defect_points, defect_count, location, status) VALUES
(1, 'R001', NULL, '100% Combed Cotton Jersey 180gsm', 'Black', 'A', 180.00, 160.00, 512.00, 147.46, 512.00, 147.46, 4,  1, 'Rack A-01', 'available'),
(1, 'R002', NULL, '100% Combed Cotton Jersey 180gsm', 'Black', 'A', 180.00, 160.00, 505.00, 145.44, 505.00, 145.44, 0,  0, 'Rack A-01', 'available'),
(1, 'R003', NULL, '100% Combed Cotton Jersey 180gsm', 'Black', 'A', 180.00, 160.00, 518.00, 149.18, 518.00, 149.18, 8,  2, 'Rack A-02', 'available'),
(1, 'R004', NULL, '100% Combed Cotton Jersey 180gsm', 'Black', 'A', 180.00, 160.00, 498.00, 143.42, 498.00, 143.42, 12, 3, 'Rack A-02', 'available'),
(1, 'R005', NULL, '100% Combed Cotton Jersey 180gsm', 'Black', 'A', 180.00, 160.00, 510.00, 146.88, 250.00, 72.00,  6,  1, 'Table-01', 'spreading'),
(1, 'R006', NULL, '100% Combed Cotton Jersey 180gsm', 'Black', 'A', 180.00, 160.00, 506.00, 145.73, 60.00,  17.28, 0,  0, 'Table-02', 'cut'),
(1, 'R007', NULL, '100% Combed Cotton Jersey 180gsm', 'Black', 'B', 180.00, 160.00, 502.00, 144.58, 502.00, 144.58, 2,  1, 'Rack A-03', 'blocked'),
(1, 'R008', NULL, '100% Combed Cotton Jersey 180gsm', 'Black', 'B', 180.00, 160.00, 515.00, 148.32, 515.00, 148.32, 0,  0, 'Rack A-03', 'available'),
(1, 'R009', NULL, '100% Combed Cotton Jersey 180gsm', 'White', 'A', 180.00, 160.00, 508.00, 146.30, 508.00, 146.30, 5,  1, 'Rack B-01', 'available'),
(1, 'R010', NULL, 'Cotton Twill 98/2 Elastane 230gsm', 'Khaki', 'A', 230.00, 150.00, 480.00, 165.60, 480.00, 165.60, 0,  0, 'Rack C-01', 'available'),
(1, 'R011', NULL, 'Cotton/Poly Fleece 320gsm', 'Olive', 'A', 320.00, 160.00, 410.00, 209.92, 410.00, 209.92, 9,  2, 'Rack C-02', 'available'),
(1, 'R012', NULL, '100% Combed Cotton Jersey 180gsm', 'Black', 'A', 180.00, 160.00, 505.00, 145.44, 505.00, 145.44, 0,  0, 'Rack A-01', 'available');

-- Cutting plans (one per existing production order)
INSERT INTO `cutting_plans`
(company_id, plan_no, production_order_id, style_id, color, size_ratio, planned_qty, fabric_required_kg, fabric_allocated_kg, marker_efficiency_pct, planned_cut_date, status, created_by) VALUES
(1, 'CUT-PLAN-0001', 1, 1, 'Black', JSON_OBJECT('S',2,'M',3,'L',3,'XL',2), 4000, 4800.00, 4800.00, 89.00, '2026-07-15', 'completed', 1),
(1, 'CUT-PLAN-0002', 2, 1, 'Black', JSON_OBJECT('S',2,'M',3,'L',3,'XL',2), 4000, 4800.00, 4200.00, 89.00, '2026-07-20', 'released', 1),
(1, 'CUT-PLAN-0003', 3, 1, 'Black', JSON_OBJECT('S',2,'M',3,'L',3,'XL',2), 4000, 4800.00, 1200.00, 86.00, '2026-08-03', 'approved', 1);

-- Cut orders (2 per plan)
INSERT INTO `cut_orders`
(company_id, cut_order_no, cutting_plan_id, production_order_id, buyer_po_id, style_id, color, shade, order_qty, planned_cut_qty, cut_qty, cutting_table_id, marker_id, scheduled_date, target_date, status, created_by) VALUES
(1, 'CUT-2026-0001', 1, 1, 1, 1, 'Black', 'A', 2000, 2050, 2050, 1, 1, '2026-07-15', '2026-07-17', 'completed', 1),
(1, 'CUT-2026-0002', 1, 1, 1, 1, 'Black', 'A', 2000, 2050, 2050, 2, 2, '2026-07-16', '2026-07-18', 'completed', 1),
(1, 'CUT-2026-0003', 2, 2, 1, 1, 'Black', 'A', 2000, 2050, 1250, 3, 1, '2026-07-21', '2026-07-25', 'cutting', 1),
(1, 'CUT-2026-0004', 2, 2, 1, 1, 'Black', 'A', 2000, 2050, 950, 4, 2, '2026-07-23', '2026-07-27', 'spreading', 1),
(1, 'CUT-2026-0005', 3, 3, 1, 1, 'Black', 'A', 2000, 2050, 0, 5, 1, '2026-08-03', '2026-08-06', 'released', 1),
(1, 'CUT-2026-0006', 3, 3, 1, 1, 'Black', 'A', 2000, 2050, 0, 6, 2, '2026-08-04', '2026-08-07', 'planned', 1);

-- Cut order lines (S/M/L/XL for each cut order)
INSERT INTO `cut_order_lines` (company_id, cut_order_id, size, quantity, cut_qty, rejected_qty, checked_qty, status) VALUES
(1, 1, 'S',  400, 400,  2, 398, 'inspected'),
(1, 1, 'M',  600, 600,  3, 597, 'inspected'),
(1, 1, 'L',  600, 600,  2, 598, 'inspected'),
(1, 1, 'XL', 400, 400,  1, 399, 'inspected'),
(1, 2, 'S',  400, 400,  1, 399, 'inspected'),
(1, 2, 'M',  600, 600,  4, 596, 'inspected'),
(1, 2, 'L',  600, 600,  2, 598, 'inspected'),
(1, 2, 'XL', 400, 400,  3, 397, 'inspected'),
(1, 3, 'S',  400, 250,  0, 250, 'cut'),
(1, 3, 'M',  600, 400,  0, 400, 'cut'),
(1, 3, 'L',  600, 380,  0, 380, 'cut'),
(1, 3, 'XL', 400, 220,  0, 220, 'cut'),
(1, 4, 'S',  400, 200,  0, 0, 'cut'),
(1, 4, 'M',  600, 300,  0, 0, 'cut'),
(1, 4, 'L',  600, 250,  0, 0, 'cut'),
(1, 4, 'XL', 400, 200,  0, 0, 'cut'),
(1, 5, 'S',  400, 0, 0, 0, 'pending'),
(1, 5, 'M',  600, 0, 0, 0, 'pending'),
(1, 5, 'L',  600, 0, 0, 0, 'pending'),
(1, 5, 'XL', 400, 0, 0, 0, 'pending'),
(1, 6, 'S',  400, 0, 0, 0, 'pending'),
(1, 6, 'M',  600, 0, 0, 0, 'pending'),
(1, 6, 'L',  600, 0, 0, 0, 'pending'),
(1, 6, 'XL', 400, 0, 0, 0, 'pending');

-- Fabric allocations
-- style 1: 1.2 kg/pc; cut order 1: 2050 pcs -> 2460 kg @ 89% eff = 2764 kg
INSERT INTO `fabric_allocations`
(company_id, cut_order_id, fabric_item_id, fabric_name, color, shade, consumption_per_unit, order_qty, fabric_required_kg, marker_efficiency_pct, actual_need_kg, allocated_kg, consumed_kg, status) VALUES
(1, 1, NULL, '100% Combed Cotton Jersey 180gsm', 'Black', 'A', 1.2000, 2050, 2460.00, 89.00, 2764.04, 2764.04, 2764.04, 'consumed'),
(1, 2, NULL, '100% Combed Cotton Jersey 180gsm', 'Black', 'A', 1.2200, 2050, 2501.00, 86.00, 2908.14, 2908.14, 2908.14, 'consumed'),
(1, 3, NULL, '100% Combed Cotton Jersey 180gsm', 'Black', 'A', 1.2000, 2050, 2460.00, 89.00, 2764.04, 2764.04, 1650.00, 'partial'),
(1, 4, NULL, '100% Combed Cotton Jersey 180gsm', 'Black', 'A', 1.2200, 2050, 2501.00, 86.00, 2908.14, 2000.00, 1100.00, 'partial'),
(1, 5, NULL, '100% Combed Cotton Jersey 180gsm', 'Black', 'A', 1.2000, 2050, 2460.00, 89.00, 2764.04, 1200.00, 0.00, 'allocated'),
(1, 6, NULL, '100% Combed Cotton Jersey 180gsm', 'Black', 'A', 1.2200, 2050, 2501.00, 86.00, 2908.14, 0.00, 0.00, 'pending');

-- Roll allocations (R005/R006 feeding cut order 3, R001..R004 feeding 1&2)
INSERT INTO `fabric_roll_allocations`
(company_id, allocation_id, fabric_roll_id, shade, allocated_kg, allocated_length_m, status) VALUES
(1, 1, 1, 'A', 147.46, 512.00, 'consumed'),
(1, 1, 2, 'A', 145.44, 505.00, 'consumed'),
(1, 2, 3, 'A', 149.18, 518.00, 'consumed'),
(1, 3, 5, 'A', 74.88, 260.00, 'spread'),
(1, 3, 12, 'A', 145.44, 505.00, 'allocated'),
(1, 4, 6, 'A', 128.45, 446.00, 'allocated'),
(1, 5, 8, 'B', 148.32, 515.00, 'allocated');

-- Lay plans
INSERT INTO `lay_plans`
(company_id, cut_order_id, lay_no, marker_id, cutting_table_id, color, shade, plies, lay_length_m, lay_width_m, pieces_per_lay, expected_pieces, marker_efficiency_pct, status, started_at, completed_at) VALUES
(1, 1, 'LAY-0001', 1, 1, 'Black', 'A', 120, 18.40, 1.60, 8, 960, 89.00, 'completed', '2026-07-15 08:00:00', '2026-07-15 16:00:00'),
(1, 1, 'LAY-0002', 1, 2, 'Black', 'A', 120, 18.40, 1.60, 8, 960, 89.00, 'completed', '2026-07-16 08:00:00', '2026-07-16 15:30:00'),
(1, 2, 'LAY-0003', 2, 3, 'Black', 'A', 130, 17.20, 1.60, 8, 1040, 86.00, 'completed', '2026-07-17 08:00:00', '2026-07-17 17:00:00'),
(1, 3, 'LAY-0004', 1, 3, 'Black', 'A', 120, 18.40, 1.60, 8, 960, 89.00, 'cutting', '2026-07-21 08:00:00', NULL),
(1, 4, 'LAY-0005', 2, 4, 'Black', 'A', 100, 17.20, 1.60, 8, 800, 86.00, 'spreading', '2026-07-23 09:00:00', NULL),
(1, 5, 'LAY-0006', 1, 5, 'Black', 'A', 120, 18.40, 1.60, 8, 960, 89.00, 'planned', NULL, NULL);

-- Spread plans
INSERT INTO `spread_plans`
(company_id, lay_plan_id, cutting_table_id, spreader_machine, operator_name, start_time, end_time, relaxation_minutes, relaxation_end_time, plies_spread, status) VALUES
(1, 1, 1, 'SP-1001', 'Rahim Uddin', '2026-07-15 08:00:00', '2026-07-15 11:00:00', 60, '2026-07-15 12:00:00', 120, 'completed'),
(1, 3, 3, 'SP-1002', 'Karim Ahmed', '2026-07-17 08:00:00', '2026-07-17 12:00:00', 45, '2026-07-17 12:45:00', 130, 'completed'),
(1, 4, 3, 'SP-1003', 'Rahim Uddin', '2026-07-21 08:00:00', '2026-07-21 12:30:00', 60, '2026-07-21 13:30:00', 90, 'cutting'),
(1, 5, 4, 'SP-1004', 'Nazmul Haque', '2026-07-23 09:00:00', NULL, 60, NULL, 40, 'spreading');

-- Cutting schedule (board view)
INSERT INTO `cutting_schedule`
(company_id, schedule_date, cutting_table_id, cut_order_id, shift, start_time, end_time, operator_name, status) VALUES
(1, '2026-08-03', 1, 5, 'day',   '08:00:00', '16:00:00', 'Rahim Uddin', 'planned'),
(1, '2026-08-03', 2, 6, 'day',   '08:00:00', '16:00:00', 'Karim Ahmed', 'planned'),
(1, '2026-08-03', 3, 3, 'day',   '08:00:00', '16:00:00', 'Nazmul Haque', 'cutting'),
(1, '2026-08-03', 4, 4, 'day',   '08:00:00', '16:00:00', 'Jahid Hasan', 'spreading'),
(1, '2026-08-04', 1, 5, 'day',   '08:00:00', '16:00:00', 'Rahim Uddin', 'planned'),
(1, '2026-08-04', 2, 6, 'night', '22:00:00', '06:00:00', 'Mamun Khan', 'planned'),
(1, '2026-08-04', 3, 3, 'day',   '08:00:00', '16:00:00', 'Nazmul Haque', 'cutting'),
(1, '2026-08-05', 1, 5, 'day',   '08:00:00', '16:00:00', 'Rahim Uddin', 'planned');

-- Bundle headers (cutting-floor bundles)
INSERT INTO `bundle_headers`
(company_id, bundle_no, barcode, qr_code, lay_plan_id, cut_order_id, production_order_id, style_id, color, shade, size, quantity, checked_qty, rejected_qty, status) VALUES
(1, 'BUN-0001', '890000000001', 'FCQC:BUN-0001', 1, 1, 1, 1, 'Black', 'A', 'S', 20, 20, 0, 'transferred'),
(1, 'BUN-0002', '890000000002', 'FCQC:BUN-0002', 1, 1, 1, 1, 'Black', 'A', 'M', 20, 20, 1, 'transferred'),
(1, 'BUN-0003', '890000000003', 'FCQC:BUN-0003', 1, 1, 1, 1, 'Black', 'A', 'L', 20, 20, 0, 'transferred'),
(1, 'BUN-0004', '890000000004', 'FCQC:BUN-0004', 1, 1, 1, 1, 'Black', 'A', 'XL', 20, 20, 0, 'transferred'),
(1, 'BUN-0005', '890000000005', 'FCQC:BUN-0005', 3, 2, 1, 1, 'Black', 'A', 'M', 20, 20, 0, 'transferred'),
(1, 'BUN-0006', '890000000006', 'FCQC:BUN-0006', 4, 3, 2, 1, 'Black', 'A', 'S', 20, 20, 0, 'inspection'),
(1, 'BUN-0007', '890000000007', 'FCQC:BUN-0007', 4, 3, 2, 1, 'Black', 'A', 'M', 20, 20, 0, 'transfer_ready'),
(1, 'BUN-0008', '890000000008', 'FCQC:BUN-0008', 4, 3, 2, 1, 'Black', 'A', 'L', 20, 18, 2, 'inspection'),
(1, 'BUN-0009', '890000000009', 'FCQC:BUN-0009', 5, 4, 2, 1, 'Black', 'A', 'M', 20, 0, 0, 'created');

-- Bundle details (panels)
INSERT INTO `bundle_details` (company_id, bundle_id, panel_name, quantity, checked_qty, rejected_qty) VALUES
(1, 1, 'Front Panel',  20, 20, 0),
(1, 1, 'Back Panel',   20, 20, 0),
(1, 1, 'Sleeve L',     20, 20, 0),
(1, 1, 'Sleeve R',     20, 20, 0),
(1, 1, 'Collar',       20, 20, 0),
(1, 2, 'Front Panel',  20, 20, 1),
(1, 2, 'Back Panel',   20, 20, 0),
(1, 2, 'Sleeve L',     20, 20, 0),
(1, 2, 'Sleeve R',     20, 20, 0),
(1, 2, 'Collar',       20, 20, 0),
(1, 6, 'Front Panel',  20, 20, 0),
(1, 6, 'Back Panel',   20, 20, 0),
(1, 6, 'Sleeve L',     20, 20, 0),
(1, 6, 'Sleeve R',     20, 20, 0),
(1, 6, 'Collar',       20, 20, 0);

-- Fabric consumption
INSERT INTO `fabric_consumption`
(company_id, cut_order_id, lay_plan_id, fabric_item_id, fabric_name, allocated_kg, consumed_kg, balance_kg, waste_kg, waste_pct, marker_efficiency_pct, recorded_by, recorded_at, notes) VALUES
(1, 1, 1, NULL, '100% Combed Cotton Jersey 180gsm', 2764.04, 2725.00, 39.04, 68.00, 2.46, 89.00, 1, '2026-07-17 17:00:00', 'Clean cut, minimal waste'),
(1, 2, 3, NULL, '100% Combed Cotton Jersey 180gsm', 2908.14, 2860.00, 48.14, 74.00, 2.54, 86.00, 1, '2026-07-18 17:00:00', NULL),
(1, 3, 4, NULL, '100% Combed Cotton Jersey 180gsm', 2764.04, 1650.00, 1114.04, 40.00, 2.37, 89.00, 1, '2026-08-01 12:00:00', 'Mid-order consumption snapshot');

-- Fabric remnants
INSERT INTO `fabric_remnants`
(company_id, roll_no, fabric_item_id, fabric_name, color, shade, remnant_length_m, remnant_weight_kg, remnant_width_cm, location, status, ai_suggestion) VALUES
(1, 'R006', NULL, '100% Combed Cotton Jersey 180gsm', 'Black', 'A', 60.00, 17.28, 160.00, 'Rack A-15', 'available', 'Fits marker M001 short lay (~60m) for style HM-101'),
(1, 'R005', NULL, '100% Combed Cotton Jersey 180gsm', 'Black', 'A', 250.00, 72.00, 160.00, 'Table-01', 'available', 'Reserve for CUT-2026-0003 fill-in lay'),
(1, 'R012', NULL, '100% Combed Cotton Jersey 180gsm', 'Black', 'A', 505.00, 145.44, 160.00, 'Rack A-01', 'available', NULL);

-- Panel inspection
INSERT INTO `panel_inspection`
(company_id, bundle_id, lay_plan_id, inspector_name, inspected_at, panel_count, checked_count, defect_count, shade_ok, marker_ok, result, notes) VALUES
(1, 1, 1, 'Shakil Islam', '2026-07-16 09:00:00', 100, 100, 0, 1, 1, 'pass', 'All panels matched'),
(1, 2, 1, 'Shakil Islam', '2026-07-16 09:30:00', 100, 100, 1, 1, 1, 'rework', '1 panel shade variance at collar'),
(1, 3, 1, 'Shakil Islam', '2026-07-16 10:00:00', 100, 100, 0, 1, 1, 'pass', NULL),
(1, 6, 4, 'Tanvir Rahman', '2026-08-01 11:00:00', 100, 100, 0, 1, 1, 'pass', NULL),
(1, 7, 4, 'Tanvir Rahman', '2026-08-01 11:15:00', 100, 100, 0, 1, 1, 'pass', NULL),
(1, 8, 4, 'Tanvir Rahman', '2026-08-01 11:30:00', 100, 90, 2, 0, 1, 'rework', '2 panels wrong shade - blocked');

-- Cutting quality
INSERT INTO `cutting_quality`
(company_id, cut_order_id, lay_plan_id, bundle_id, quality_type, result, defect_type, defect_count, notes, checked_by, checked_at) VALUES
(1, 1, 1, NULL, 'marker_check', 'pass', NULL, 0, 'Marker M001 verified against lay', 1, '2026-07-15 07:55:00'),
(1, 1, 1, 1, 'bundle_verify', 'pass', NULL, 0, 'Bundle count OK', 1, '2026-07-16 09:00:00'),
(1, 3, 4, 8, 'shade_check', 'fail', 'Shade variance', 2, 'Shade B roll mixed into Shade A lay - blocked', 1, '2026-08-01 11:30:00'),
(1, 4, 5, NULL, 'marker_check', 'warn', 'Width mismatch', 0, 'Fabric 68in vs marker 68in - tight', 1, '2026-07-23 08:50:00');

-- Cutting reports
INSERT INTO `cutting_reports`
(company_id, report_type, report_date, period_start, period_end, report_data, generated_by) VALUES
(1, 'daily_cutting', '2026-07-31', '2026-07-31', '2026-07-31', JSON_OBJECT('cut_orders', 2, 'pieces_cut', 2200, 'tables_in_use', 3, 'marker_efficiency', 88.4, 'fabric_utilization', 91.2), 1),
(1, 'cut_order_summary', '2026-07-31', '2026-07-01', '2026-07-31', JSON_OBJECT('total_cut_orders', 4, 'completed', 2, 'in_progress', 2, 'pieces_cut', 6150), 1),
(1, 'marker_efficiency', '2026-07-31', '2026-07-01', '2026-07-31', JSON_OBJECT('best_marker', 'M001', 'best_efficiency', 89.0, 'avg_efficiency', 87.5), 1);

-- AI analysis
INSERT INTO `cutting_ai_analysis`
(company_id, analysis_type, entity_id, input_snapshot, result, score, recommendation) VALUES
(1, 'marker_optimization', 5, JSON_OBJECT('order_qty', 2050, 'style_id', 1), JSON_OBJECT('suggested_marker', 'M001', 'efficiency', 89.0, 'fabric_saving_kg', 86.4), 95.00, 'Use marker M001 (89%) for CUT-2026-0005 - saves ~86 kg vs M002'),
(1, 'roll_allocation', 3, JSON_OBJECT('need_kg', 2764), JSON_OBJECT('selected_rolls', JSON_ARRAY('R005','R012'), 'shade', 'A', 'utilization', 97.5), 92.00, 'Allocate R012 (145 kg) + R005 remnant (72 kg) - avoids opening shade B rolls'),
(1, 'table_scheduling', NULL, JSON_OBJECT('week', '2026-08-03'), JSON_OBJECT('optimal', 'Table-01: CUT-2026-0005, Table-02: CUT-2026-0006'), 88.00, 'Move CUT-2026-0005 to Table-01 to cut 4h earlier for shipment date'),
(1, 'waste_prediction', 4, JSON_OBJECT('marker', 'M002', 'plies', 100), JSON_OBJECT('expected_waste', 7.2, 'current_plan', 8.8, 'suggested', 6.9), 91.00, 'Re-nest collar to save 1.9% fabric waste on LAY-0005');
