-- =============================================================
-- FashCognitive Quality OS - Seed Data for FRI Inspections
-- =============================================================
-- This seeds a complete Standard FRI Template with:
--   9 universal pages (overview, measurements, defects, aql_calculator,
--     packaging, photos, signatures, notes, decision)
--   5 FRI-specific pages (carton_verification, labeling,
--     shipment_readiness, accessories, color_check)
--   Master defect library, measurement templates, quality plans, rules
-- =============================================================

SET FOREIGN_KEY_CHECKS = 0;

-- ========================
-- 1. STANDARD FRI TEMPLATE
-- ========================
INSERT IGNORE INTO `inspection_templates` (`id`, `buyer_id`, `template_type`, `name`, `version`, `status`, `config`, `company_id`, `created_by`)
VALUES (1, NULL, 'FRI', 'Standard FRI Template', 1, 'active',
  '{"auto_save_interval": 30, "gps_required": true, "photo_required": true, "fast_mode_enabled": true}',
  NULL, NULL);

-- ========================
-- 2. PAGES (14 total: 9 universal + 5 FRI-specific)
-- ========================
INSERT IGNORE INTO `inspection_pages` (`id`, `template_id`, `page_key`, `label`, `sort_order`, `is_conditional`, `condition_rule`, `category`, `config`) VALUES

-- === UNIVERSAL PAGES ===
(1, 1, 'overview', 'Overview', 1, 0, NULL, 'universal',
  '{"columns": 2, "show_progress": true}'),

(2, 1, 'measurements', 'Body Measurements', 2, 0, NULL, 'universal',
  '{"columns": 1, "show_tolerances": true, "measurement_template_id": 1}'),

(3, 1, 'defects', 'Defect Recording', 3, 0, NULL, 'universal',
  '{"columns": 1, "show_defect_grid": true, "allow_multi_select": true}'),

(4, 1, 'aql_calculator', 'AQL Calculator', 4, 0, NULL, 'universal',
  '{"columns": 1, "auto_calculate": true, "quality_plan_id": 1}'),

(5, 1, 'packaging', 'Packaging Inspection', 5, 0, NULL, 'universal',
  '{"columns": 2, "require_photo": true}'),

(6, 1, 'photos', 'Photo Evidence', 6, 0, NULL, 'universal',
  '{"columns": 1, "min_photos": 4, "max_photos": 20}'),

(7, 1, 'signatures', 'Digital Signatures', 7, 0, NULL, 'universal',
  '{"columns": 1, "require_inspector_sig": true, "require_factory_sig": false}'),

(8, 1, 'notes', 'Inspector Notes', 8, 0, NULL, 'universal',
  '{"columns": 1}'),

(9, 1, 'decision', 'Final Decision', 9, 0, NULL, 'universal',
  '{"columns": 1, "require_decision_reason": true}'),

-- === FRI-SPECIFIC PAGES ===
(10, 1, 'carton_verification', 'Carton Verification', 10, 0, NULL, 'fri',
  '{"columns": 2, "require_photo": true, "measurement_template_id": 2}'),

(11, 1, 'labeling', 'Labeling Check', 11, 0, NULL, 'fri',
  '{"columns": 2, "require_photo": true}'),

(12, 1, 'shipment_readiness', 'Shipment Readiness', 12, 0, NULL, 'fri',
  '{"columns": 2}'),

(13, 1, 'accessories', 'Accessory Verification', 13, 1,
  '{"field": "style.has_accessories", "operator": "eq", "value": true}', 'fri',
  '{"columns": 2}'),

(14, 1, 'color_check', 'Color Assessment', 14, 0, NULL, 'fri',
  '{"columns": 2, "require_photo": true, "require_color_standard": true}');

-- ========================
-- 3. FIELDS (by page)
-- ========================

-- Page 1: Overview
INSERT IGNORE INTO `inspection_fields` (`id`, `page_id`, `field_key`, `field_type`, `label`, `is_required`, `sort_order`, `validation_rules`, `options`, `config`) VALUES

(1, 1, 'po_number', 'text', 'PO Number', 1, 1,
  '{"required": true, "min_length": 3, "max_length": 50}',
  NULL, '{"placeholder": "Enter PO number", "help_text": "Purchase Order number from buyer"}'),

(2, 1, 'style_number', 'text', 'Style Number', 1, 2,
  '{"required": true, "min_length": 3, "max_length": 50}',
  NULL, '{"placeholder": "Enter style number"}'),

(3, 1, 'buyer_name', 'text', 'Buyer Name', 1, 3,
  '{"required": true}',
  NULL, '{"placeholder": "Enter buyer name"}'),

(4, 1, 'inspector_name', 'text', 'Inspector Name', 1, 4,
  '{"required": true}',
  NULL, '{"placeholder": "Full name"}'),

(5, 1, 'inspection_date', 'date', 'Inspection Date', 1, 5,
  '{"required": true}',
  NULL, '{"default_value": "today"}'),

(6, 1, 'lot_size', 'number', 'Lot Size', 1, 6,
  '{"required": true, "min": 1}',
  NULL, '{"unit": "pcs", "help_text": "Total quantity in this lot"}'),

(7, 1, 'sample_size', 'number', 'Sample Size', 1, 7,
  '{"required": true, "min": 1}',
  NULL, '{"unit": "pcs", "help_text": "Quantity inspected"}'),

(8, 1, 'factory', 'text', 'Factory Location', 1, 8,
  '{"required": true}',
  NULL, '{"placeholder": "Factory name or code"}'),

(9, 1, 'gps_coordinates', 'text', 'GPS Coordinates', 0, 9,
  NULL, NULL, '{"help_text": "Auto-captured from device", "readonly": true}'),

(10, 1, 'has_accessories', 'select', 'Has Accessories?', 1, 10,
  '{"required": true}',
  '[{"label": "Yes", "value": true}, {"label": "No", "value": false}]',
  '{"help_text": "Determines if accessory page is shown"}'),

(11, 1, 'inspection_type', 'select', 'Inspection Type', 0, 11,
  NULL,
  '[{"label": "FRI - Final Random Inspection", "value": "FRI"}, {"label": "DUPRO - During Production", "value": "DUPRO"}, {"label": "Fabric Inspection", "value": "fabric"}, {"label": "Loading Inspection", "value": "loading"}]',
  '{"default_value": "FRI"}'),

(12, 1, 'divider_1', 'divider', '', 0, 12, NULL, NULL, NULL),

(13, 1, 'additional_notes_overview', 'textarea', 'Additional Notes', 0, 13,
  NULL, NULL, '{"placeholder": "Any additional notes about this inspection...", "rows": 3}');

-- Page 2: Measurements
INSERT IGNORE INTO `inspection_fields` (`id`, `page_id`, `field_key`, `field_type`, `label`, `is_required`, `sort_order`, `validation_rules`, `options`, `config`) VALUES

(14, 2, 'measurement_info', 'info_text', 'Record body measurements below. Measurements outside tolerance will be flagged automatically.', 0, 0,
  NULL, NULL, NULL),

(15, 2, 'measurement_grid', 'measurement_grid', 'Measurement Points', 1, 1,
  '{"required": true}',
  NULL, '{"measurement_template_id": 1, "show_tolerances": true, "allow_photo_per_point": false}'),

(16, 2, 'measurement_notes', 'textarea', 'Measurement Notes', 0, 2,
  NULL, NULL, '{"placeholder": "Any observations about measurements...", "rows": 2}');

-- Page 3: Defects
INSERT IGNORE INTO `inspection_fields` (`id`, `page_id`, `field_key`, `field_type`, `label`, `is_required`, `sort_order`, `validation_rules`, `options`, `config`) VALUES

(17, 3, 'defect_info', 'info_text', 'Record all defects found during inspection. Categorize by severity (Critical, Major, Minor).', 0, 0,
  NULL, NULL, NULL),

(18, 3, 'defect_grid', 'defect_grid', 'Defect Log', 1, 1,
  '{"required": true}',
  NULL, '{"show_photo_button": true, "allow_multi_select": true, "defect_sources": ["master_defect_library"]}'),

(19, 3, 'defect_summary', 'info_text', 'Defect summary will be auto-calculated after entry.', 0, 2,
  NULL, NULL, NULL),

(20, 3, 'critical_count', 'number', 'Critical Defects', 0, 3,
  '{"min": 0}',
  NULL, '{"readonly": true, "help_text": "Auto-calculated"}'),

(21, 3, 'major_count', 'number', 'Major Defects', 0, 4,
  '{"min": 0}',
  NULL, '{"readonly": true, "help_text": "Auto-calculated"}'),

(22, 3, 'minor_count', 'number', 'Minor Defects', 0, 5,
  '{"min": 0}',
  NULL, '{"readonly": true, "help_text": "Auto-calculated"}'),

(23, 3, 'total_defects', 'number', 'Total Defects', 0, 6,
  '{"min": 0}',
  NULL, '{"readonly": true, "help_text": "Auto-calculated"}');

-- Page 4: AQL Calculator
INSERT IGNORE INTO `inspection_fields` (`id`, `page_id`, `field_key`, `field_type`, `label`, `is_required`, `sort_order`, `validation_rules`, `options`, `config`) VALUES

(24, 4, 'aql_info', 'info_text', 'AQL (Acceptable Quality Limit) is calculated based on sample size, lot size, and defect counts.', 0, 0,
  NULL, NULL, NULL),

(25, 4, 'aql_calculator', 'aql_calculator', 'AQL Calculation', 1, 1,
  '{"required": true}',
  NULL, '{"quality_plan_id": 1, "auto_calculate": true, "show_accept_reject": true}'),

(26, 4, 'aql_result', 'select', 'AQL Result', 1, 2,
  '{"required": true}',
  '[{"label": "Accept (Pass)", "value": "accept"}, {"label": "Reject (Fail)", "value": "reject"}]',
  '{"help_text": "Based on AQL calculation"}'),

(27, 4, 'aql_notes', 'textarea', 'AQL Notes', 0, 3,
  NULL, NULL, '{"placeholder": "Notes about AQL calculation...", "rows": 2}');

-- Page 5: Packaging
INSERT IGNORE INTO `inspection_fields` (`id`, `page_id`, `field_key`, `field_type`, `label`, `is_required`, `sort_order`, `validation_rules`, `options`, `config`) VALUES

(28, 5, 'packaging_info', 'info_text', 'Verify packaging quality, polybag condition, and packing method.', 0, 0,
  NULL, NULL, NULL),

(29, 5, 'packaging_type', 'select', 'Packaging Type', 1, 1,
  '{"required": true}',
  '[{"label": "Polybag", "value": "polybag"}, {"label": "Master Polybag", "value": "master_polybag"}, {"label": "Box", "value": "box"}, {"label": "Hanger Pack", "value": "hanger_pack"}, {"label": "Flat Pack", "value": "flat_pack"}, {"label": "Other", "value": "other"}]',
  NULL),

(30, 5, 'packaging_condition', 'select', 'Packaging Condition', 1, 2,
  '{"required": true}',
  '[{"label": "Excellent", "value": "excellent"}, {"label": "Good", "value": "good"}, {"label": "Fair", "value": "fair"}, {"label": "Poor", "value": "poor"}]',
  NULL),

(31, 5, 'polybag_thickness', 'number', 'Polybag Thickness (microns)', 0, 3,
  '{"min": 10, "max": 200}',
  NULL, '{"unit": "microns"}'),

(32, 5, 'packaging_defects', 'select', 'Packaging Defects Found?', 1, 4,
  '{"required": true}',
  '[{"label": "None", "value": "none"}, {"label": "Torn polybag", "value": "torn"}, {"label": "Wrong size", "value": "wrong_size"}, {"label": "Missing polybag", "value": "missing"}, {"label": "Damaged box", "value": "damaged_box"}, {"label": "Other", "value": "other"}]',
  NULL),

(33, 5, 'packaging_photo', 'photo', 'Packaging Photo', 0, 5,
  NULL, NULL, '{"require_gps": true, "max_files": 3}'),

(34, 5, 'packaging_notes', 'textarea', 'Packaging Notes', 0, 6,
  NULL, NULL, '{"placeholder": "Additional packaging observations...", "rows": 2}');

-- Page 6: Photos
INSERT IGNORE INTO `inspection_fields` (`id`, `page_id`, `field_key`, `field_type`, `label`, `is_required`, `sort_order`, `validation_rules`, `options`, `config`) VALUES

(35, 6, 'photos_info', 'info_text', 'Attach photo evidence. Minimum 4 photos required (front view, back view, label, defect sample).', 0, 0,
  NULL, NULL, NULL),

(36, 6, 'front_view_photo', 'photo', 'Front View', 1, 1,
  '{"required": true, "max_files": 2}',
  NULL, '{"require_gps": true, "require_annotation": false}'),

(37, 6, 'back_view_photo', 'photo', 'Back View', 1, 2,
  '{"required": true, "max_files": 2}',
  NULL, '{"require_gps": true, "require_annotation": false}'),

(38, 6, 'label_photo', 'photo', 'Label / Care Label Photo', 1, 3,
  '{"required": true, "max_files": 2}',
  NULL, '{"require_annotation": true}'),

(39, 6, 'defect_sample_photo', 'photo', 'Defect Sample Photo', 1, 4,
  '{"required": true, "max_files": 5}',
  NULL, '{"require_annotation": true, "require_gps": false}'),

(40, 6, 'additional_photos', 'photo', 'Additional Photos', 0, 5,
  '{"max_files": 10}',
  NULL, '{"require_gps": false}');

-- Page 7: Signatures
INSERT IGNORE INTO `inspection_fields` (`id`, `page_id`, `field_key`, `field_type`, `label`, `is_required`, `sort_order`, `validation_rules`, `options`, `config`) VALUES

(41, 7, 'signatures_info', 'info_text', 'Digital signatures confirm inspection completion and acceptance.', 0, 0,
  NULL, NULL, NULL),

(42, 7, 'inspector_signature', 'signature', 'Inspector Signature', 1, 1,
  '{"required": true}',
  NULL, '{"role": "inspector", "pad_height": 200, "pad_width": 500}'),

(43, 7, 'inspector_name_sig', 'text', 'Inspector Name (printed)', 1, 2,
  '{"required": true}',
  NULL, '{"placeholder": "Full name"}'),

(44, 7, 'inspector_title', 'text', 'Inspector Title', 1, 3,
  '{"required": true}',
  NULL, '{"placeholder": "e.g. Quality Inspector"}'),

(45, 7, 'factory_representative_signature', 'signature', 'Factory Representative Signature', 0, 4,
  NULL, NULL, '{"role": "factory_qa", "pad_height": 200, "pad_width": 500}'),

(46, 7, 'factory_rep_name', 'text', 'Factory Representative Name', 0, 5,
  NULL, NULL, '{"placeholder": "Full name"}');

-- Page 8: Notes
INSERT IGNORE INTO `inspection_fields` (`id`, `page_id`, `field_key`, `field_type`, `label`, `is_required`, `sort_order`, `validation_rules`, `options`, `config`) VALUES

(47, 8, 'notes_info', 'info_text', 'Record any additional observations, comments, or non-conformance details.', 0, 0,
  NULL, NULL, NULL),

(48, 8, 'inspector_comments', 'textarea', 'Inspector Comments', 1, 1,
  '{"required": true, "min_length": 10}',
  NULL, '{"placeholder": "Detailed comments about the inspection findings...", "rows": 5}'),

(49, 8, 'recommendations', 'textarea', 'Recommendations', 0, 2,
  NULL, NULL, '{"placeholder": "Suggestions for improvement...", "rows": 3}'),

(50, 8, 'divider_notes', 'divider', '', 0, 3, NULL, NULL, NULL),

(51, 8, 'rework_required', 'checkbox', 'Rework Required', 0, 4,
  NULL, NULL, '{"help_text": "Check if rework is needed before shipment"}'),

(52, 8, 'rework_details', 'textarea', 'Rework Details', 0, 5,
  NULL, NULL, '{"placeholder": "Describe rework required...", "rows": 2}');

-- Page 9: Decision
INSERT IGNORE INTO `inspection_fields` (`id`, `page_id`, `field_key`, `field_type`, `label`, `is_required`, `sort_order`, `validation_rules`, `options`, `config`) VALUES

(53, 9, 'decision_info', 'info_text', 'Final inspection decision based on all collected data and AQL results.', 0, 0,
  NULL, NULL, NULL),

(54, 9, 'final_decision', 'select', 'Final Decision', 1, 1,
  '{"required": true}',
  '[{"label": "PASS", "value": "PASS"}, {"label": "Conditional Pass", "value": "CONDITIONAL_PASS"}, {"label": "FAIL", "value": "FAIL"}, {"label": "Re-Inspection Required", "value": "RE_INSPECTION"}, {"label": "On Hold", "value": "HOLD"}]',
  '{"help_text": "Select the final inspection outcome"}'),

(55, 9, 'decision_reason', 'textarea', 'Decision Reason', 1, 2,
  '{"required": true, "min_length": 10}',
  NULL, '{"placeholder": "Explain the rationale for this decision...", "rows": 4}'),

(56, 9, 'next_steps', 'select', 'Recommended Next Steps', 0, 3,
  NULL,
  '[{"label": "Shipment approved", "value": "ship_approved"}, {"label": "Conditional shipment", "value": "conditional_ship"}, {"label": "Hold for re-inspection", "value": "hold_reinspect"}, {"label": "Return to factory", "value": "return_factory"}, {"label": "Initiate CAPA", "value": "initiate_capa"}, {"label": "Escalate to buyer", "value": "escalate_buyer"}]',
  NULL),

(57, 9, 'divider_decision', 'divider', '', 0, 4, NULL, NULL, NULL),

(58, 9, 'final_decision_notes', 'textarea', 'Final Notes', 0, 5,
  NULL, NULL, '{"placeholder": "Any final notes...", "rows": 2}');

-- Page 10: Carton Verification (FRI)
INSERT IGNORE INTO `inspection_fields` (`id`, `page_id`, `field_key`, `field_type`, `label`, `is_required`, `sort_order`, `validation_rules`, `options`, `config`) VALUES

(59, 10, 'carton_info', 'info_text', 'Verify carton markings, dimensions, and contents.', 0, 0,
  NULL, NULL, NULL),

(60, 10, 'carton_count', 'number', 'Total Cartons in Lot', 1, 1,
  '{"required": true, "min": 1}',
  NULL, '{"unit": "cartons"}'),

(61, 10, 'cartons_inspected', 'number', 'Cartons Inspected', 1, 2,
  '{"required": true, "min": 1}',
  NULL, '{"unit": "cartons"}'),

(62, 10, 'carton_marking', 'select', 'Carton Marking Correct?', 1, 3,
  '{"required": true}',
  '[{"label": "Correct", "value": "correct"}, {"label": "Minor issues", "value": "minor_issues"}, {"label": "Incorrect", "value": "incorrect"}]',
  NULL),

(63, 10, 'carton_condition', 'select', 'Carton Condition', 1, 4,
  '{"required": true}',
  '[{"label": "Excellent", "value": "excellent"}, {"label": "Good", "value": "good"}, {"label": "Damaged", "value": "damaged"}]',
  NULL),

(64, 10, 'carton_quantity_accuracy', 'select', 'Quantity per Carton Accurate?', 1, 5,
  '{"required": true}',
  '[{"label": "Yes - all correct", "value": "correct"}, {"label": "Minor discrepancies", "value": "minor"}, {"label": "Major discrepancies", "value": "major"}]',
  NULL),

(65, 10, 'carton_measurements', 'measurement_grid', 'Carton Measurements', 0, 6,
  NULL, NULL, '{"measurement_template_id": 2, "show_tolerances": true}'),

(66, 10, 'carton_photo', 'photo', 'Carton Photo', 0, 7,
  NULL, NULL, '{"max_files": 3, "require_annotation": true}'),

(67, 10, 'carton_notes', 'textarea', 'Carton Notes', 0, 8,
  NULL, NULL, '{"placeholder": "Carton verification notes...", "rows": 2}');

-- Page 11: Labeling Check (FRI)
INSERT IGNORE INTO `inspection_fields` (`id`, `page_id`, `field_key`, `field_type`, `label`, `is_required`, `sort_order`, `validation_rules`, `options`, `config`) VALUES

(68, 11, 'labeling_info', 'info_text', 'Verify all labels including hang tags, care labels, price tags, and barcodes.', 0, 0,
  NULL, NULL, NULL),

(69, 11, 'hang_tag_present', 'checkbox', 'Hang Tag Present', 1, 1,
  '{"required": true}',
  NULL, NULL),

(70, 11, 'hang_tag_correct', 'select', 'Hang Tag Information Correct?', 1, 2,
  '{"required": true}',
  '[{"label": "Correct", "value": "correct"}, {"label": "Incorrect", "value": "incorrect"}, {"label": "N/A", "value": "na"}]',
  NULL),

(71, 11, 'care_label_present', 'checkbox', 'Care Label Present', 1, 3,
  '{"required": true}',
  NULL, NULL),

(72, 11, 'care_label_correct', 'select', 'Care Label Information Correct?', 1, 4,
  '{"required": true}',
  '[{"label": "Correct", "value": "correct"}, {"label": "Incorrect", "value": "incorrect"}, {"label": "N/A", "value": "na"}]',
  NULL),

(73, 11, 'price_tag_present', 'checkbox', 'Price Tag Present (if required)', 0, 5,
  NULL, NULL, NULL),

(74, 11, 'barcode_verified', 'select', 'Barcode / QR Verified?', 1, 6,
  '{"required": true}',
  '[{"label": "Verified - correct", "value": "verified"}, {"label": "Not readable", "value": "not_readable"}, {"label": "Wrong code", "value": "wrong_code"}, {"label": "N/A", "value": "na"}]',
  NULL),

(75, 11, 'label_photo', 'photo', 'Label Photo Evidence', 0, 7,
  NULL, NULL, '{"max_files": 3}'),

(76, 11, 'labeling_notes', 'textarea', 'Labeling Notes', 0, 8,
  NULL, NULL, '{"placeholder": "Labeling observations...", "rows": 2}');

-- Page 12: Shipment Readiness (FRI)
INSERT IGNORE INTO `inspection_fields` (`id`, `page_id`, `field_key`, `field_type`, `label`, `is_required`, `sort_order`, `validation_rules`, `options`, `config`) VALUES

(77, 12, 'shipment_info', 'info_text', 'Verify overall shipment readiness before release.', 0, 0,
  NULL, NULL, NULL),

(78, 12, 'shipment_ready', 'select', 'Shipment Ready?', 1, 1,
  '{"required": true}',
  '[{"label": "Yes - ready to ship", "value": "ready"}, {"label": "Conditional - minor issues", "value": "conditional"}, {"label": "No - hold shipment", "value": "hold"}]',
  NULL),

(79, 12, 'quantity_confirmed', 'checkbox', 'Total Quantity Confirmed', 1, 2,
  '{"required": true}',
  NULL, '{"help_text": "Confirm total quantity matches PO"}'),

(80, 12, 'ex_factory_date', 'date', 'Ex-Factory Date', 0, 3,
  NULL, NULL, NULL),

(81, 12, 'etd_date', 'date', 'Estimated Departure Date', 0, 4,
  NULL, NULL, NULL),

(82, 12, 'shipping_method', 'select', 'Shipping Method', 0, 5,
  NULL,
  '[{"label": "Sea Freight", "value": "sea"}, {"label": "Air Freight", "value": "air"}, {"label": "Rail", "value": "rail"}, {"label": "Courier", "value": "courier"}]',
  NULL),

(83, 12, 'documents_ready', 'checkbox', 'Export Documents Ready', 0, 6,
  NULL, NULL, NULL),

(84, 12, 'shipment_notes', 'textarea', 'Shipment Notes', 0, 7,
  NULL, NULL, '{"placeholder": "Additional shipment readiness notes...", "rows": 2}');

-- Page 13: Accessory Verification (FRI - conditional)
INSERT IGNORE INTO `inspection_fields` (`id`, `page_id`, `field_key`, `field_type`, `label`, `is_required`, `sort_order`, `validation_rules`, `options`, `config`) VALUES

(85, 13, 'accessory_info', 'info_text', 'Verify all accessories including buttons, zippers, thread, trims, etc.', 0, 0,
  NULL, NULL, NULL),

(86, 13, 'accessory_type', 'multi_select', 'Accessories Verified', 1, 1,
  '{"required": true}',
  '[{"label": "Buttons", "value": "buttons"}, {"label": "Zippers", "value": "zippers"}, {"label": "Thread", "value": "thread"}, {"label": "Elastic", "value": "elastic"}, {"label": "Lining", "value": "lining"}, {"label": "Interlining", "value": "interlining"}, {"label": "Rib/Knit trim", "value": "rib_trim"}, {"label": "Drawstrings", "value": "drawstrings"}, {"label": "Labels (woven)", "value": "woven_labels"}, {"label": "Rivets", "value": "rivets"}, {"label": "Snaps", "value": "snaps"}, {"label": "Other", "value": "other"}]',
  NULL),

(87, 13, 'accessory_quality', 'select', 'Accessory Quality', 1, 2,
  '{"required": true}',
  '[{"label": "All good", "value": "good"}, {"label": "Minor issues", "value": "minor"}, {"label": "Major defects", "value": "major"}]',
  NULL),

(88, 13, 'accessory_photo', 'photo', 'Accessory Photo', 0, 3,
  NULL, NULL, '{"max_files": 3}'),

(89, 13, 'accessory_notes', 'textarea', 'Accessory Notes', 0, 4,
  NULL, NULL, '{"placeholder": "Accessory verification notes...", "rows": 2}');

-- Page 14: Color Assessment (FRI)
INSERT IGNORE INTO `inspection_fields` (`id`, `page_id`, `field_key`, `field_type`, `label`, `is_required`, `sort_order`, `validation_rules`, `options`, `config`) VALUES

(90, 14, 'color_info', 'info_text', 'Assess color against approved standard / swatch.', 0, 0,
  NULL, NULL, NULL),

(91, 14, 'color_standard_ref', 'text', 'Color Standard Reference', 1, 1,
  '{"required": true}',
  NULL, '{"placeholder": "e.g. PMS 19-4023 TPX, or swatch number"}'),

(92, 14, 'color_match_result', 'select', 'Color Match Result', 1, 2,
  '{"required": true}',
  '[{"label": "Match - acceptable", "value": "match"}, {"label": "Slight variation - acceptable", "value": "slight_variation"}, {"label": "Significant variation - reject", "value": "reject"}]',
  NULL),

(93, 14, 'color_dE_value', 'number', 'Delta E (dE) Value', 0, 3,
  '{"min": 0, "max": 10}',
  NULL, '{"help_text": "If spectrophotometer reading available", "unit": "dE"}'),

(94, 14, 'color_consistency', 'select', 'Color Consistency Across Pieces', 0, 4,
  NULL,
  '[{"label": "Consistent", "value": "consistent"}, {"label": "Minor variation", "value": "minor_variation"}, {"label": "Inconsistent", "value": "inconsistent"}]',
  NULL),

(95, 14, 'color_standard_photo', 'photo', 'Color Standard Photo', 1, 5,
  '{"required": true, "max_files": 2}',
  NULL, '{"require_annotation": true, "help_text": "Photo of approved color standard"}'),

(96, 14, 'color_sample_photo', 'photo', 'Production Sample Photo', 1, 6,
  '{"required": true, "max_files": 2}',
  NULL, '{"require_annotation": true, "help_text": "Photo of actual production piece"}'),

(97, 14, 'color_notes', 'textarea', 'Color Assessment Notes', 0, 7,
  NULL, NULL, '{"placeholder": "Color observations...", "rows": 2}');

-- ========================
-- 4. MASTER DEFECT LIBRARY
-- ========================
INSERT IGNORE INTO `master_defect_library` (`id`, `category`, `code`, `description`, `detailed_description`, `defect_type`, `inspection_type`, `photo_required`, `auto_capa`, `capa_template`, `is_active`) VALUES

-- Critical defects
(1, 'critical', 'CRT-01', 'Sharp needle in garment', 'Metal needle fragment found in finished garment - safety hazard', 'sewing', 'FRI', 1, 1, 'Immediate containment of all affected lot. 100% needle inspection required. Root cause analysis for needle breakage protocol.', 1),
(2, 'critical', 'CRT-02', 'Chemical smell / off-gassing', 'Strong chemical odor indicating residual formaldehyde or other chemicals', 'finishing', 'FRI', 0, 1, 'Hold shipment. Conduct chemical testing. Identify source of chemical contamination.', 1),
(3, 'critical', 'CRT-03', 'Wrong fabric composition', 'Fabric composition does not match spec (e.g. 100% cotton instead of 65/35 poly-cotton)', 'fabric', 'FRI', 1, 1, 'Hold all production. Verify fabric rolls. Notify buyer and supplier.', 1),
(4, 'critical', 'CRT-04', 'Missing safety component', 'Missing required safety component (e.g. child safety lock, reflective strip)', 'accessory', 'FRI', 1, 1, 'Stop production. Identify missing components. Implement corrective action.', 1),

-- Major defects
(5, 'major', 'MAJ-01', 'Open seam', 'Seam open more than 1cm exposing raw edge', 'sewing', 'FRI', 0, 0, NULL, 1),
(6, 'major', 'MAJ-02', 'Color shading within lot', 'Significant color variation between pieces in same lot', 'color', 'FRI', 1, 0, NULL, 1),
(7, 'major', 'MAJ-03', 'Broken stitch', 'Stitch breakage longer than 1.5cm', 'sewing', 'FRI', 0, 0, NULL, 1),
(8, 'major', 'MAJ-04', 'Incorrect label', 'Wrong care label, size label, or hang tag attached', 'labeling', 'FRI', 1, 0, NULL, 1),
(9, 'major', 'MAJ-05', 'Zipper malfunction', 'Zipper does not open/close properly or is missing teeth', 'sewing', 'FRI', 0, 0, NULL, 1),
(10, 'major', 'MAJ-06', 'Measurement out of tolerance', 'Key measurement point exceeds allowed tolerance', 'sizing', 'FRI', 0, 0, NULL, 1),
(11, 'major', 'MAJ-07', 'Contamination / stains', 'Visible dirt, oil stains, or foreign material on garment', 'finishing', 'FRI', 1, 0, NULL, 1),
(12, 'major', 'MAJ-08', 'Skipped stitch', 'Stitch skipped over 2cm length', 'sewing', 'FRI', 0, 0, NULL, 1),

-- Minor defects
(13, 'minor', 'MIN-01', 'Loose thread ends', 'Uncut thread ends longer than 2cm', 'sewing', 'FRI', 0, 0, NULL, 1),
(14, 'minor', 'MIN-02', 'Slight color variation', 'Minor color difference within acceptable range', 'color', 'FRI', 0, 0, NULL, 1),
(15, 'minor', 'MIN-03', 'Uneven stitch length', 'Stitch length variation outside spec', 'sewing', 'FRI', 0, 0, NULL, 1),
(16, 'minor', 'MIN-04', 'Slight fabric defect', 'Small slub, nep, or yarn irregularity less than 1cm', 'fabric', 'FRI', 0, 0, NULL, 1),
(17, 'minor', 'MIN-05', 'Crooked button', 'Button sewn slightly off alignment', 'sewing', 'FRI', 0, 0, NULL, 1),
(18, 'minor', 'MIN-06', 'Puckered seam', 'Slight seam puckering not affecting garment appearance significantly', 'sewing', 'FRI', 0, 0, NULL, 1),
(19, 'minor', 'MIN-07', 'Pilling', 'Minor surface pilling affecting less than 5% of area', 'fabric', 'FRI', 0, 0, NULL, 1),
(20, 'minor', 'MIN-08', 'Packaging crease', 'Minor crease from packaging that will resolve', 'packaging', 'FRI', 0, 0, NULL, 1);

-- ========================
-- 5. MEASUREMENT TEMPLATES
-- ========================

-- Template 1: Body Measurements (for apparel)
INSERT IGNORE INTO `measurement_templates` (`id`, `inspection_type`, `point_name`, `tolerance_min`, `tolerance_max`, `unit`, `sort_order`, `category`, `is_active`) VALUES
(1, 'FRI', 'Chest Width (1\" below armhole)', -1.00, 1.00, 'cm', 1, 'body', 1),
(2, 'FRI', 'Waist Width (at narrowest)', -1.00, 1.00, 'cm', 2, 'body', 1),
(3, 'FRI', 'Bottom Opening', -1.00, 1.00, 'cm', 3, 'body', 1),
(4, 'FRI', 'Center Back Length (from HPS)', -1.50, 1.50, 'cm', 4, 'body', 1),
(5, 'FRI', 'Shoulder Width (across)', -0.50, 0.50, 'cm', 5, 'body', 1),
(6, 'FRI', 'Sleeve Length (from shoulder)', -1.00, 1.00, 'cm', 6, 'sleeve', 1),
(7, 'FRI', 'Sleeve Opening', -0.50, 0.50, 'cm', 7, 'sleeve', 1),
(8, 'FRI', 'Armhole Curved', -0.50, 0.50, 'cm', 8, 'sleeve', 1),
(9, 'FRI', 'Neck Width (seam to seam)', -0.50, 0.50, 'cm', 9, 'collar', 1),
(10, 'FRI', 'Neck Depth (from HPS)', -0.50, 0.50, 'cm', 10, 'collar', 1),
(11, 'FRI', 'Collar Height (at CB)', -0.30, 0.30, 'cm', 11, 'collar', 1),
(12, 'FRI', 'Cross Back (15cm from HPS)', -1.00, 1.00, 'cm', 12, 'body', 1);

-- Template 2: Carton Measurements
INSERT IGNORE INTO `measurement_templates` (`id`, `inspection_type`, `point_name`, `tolerance_min`, `tolerance_max`, `unit`, `sort_order`, `category`, `is_active`) VALUES
(13, 'FRI', 'Carton Length (external)', -0.50, 0.50, 'cm', 1, 'carton', 1),
(14, 'FRI', 'Carton Width (external)', -0.50, 0.50, 'cm', 2, 'carton', 1),
(15, 'FRI', 'Carton Height (external)', -0.50, 0.50, 'cm', 3, 'carton', 1),
(16, 'FRI', 'Carton Gross Weight', -0.20, 0.20, 'kg', 4, 'carton', 1);

-- ========================
-- 6. INSPECTION RULES
-- ========================
INSERT IGNORE INTO `inspection_rules` (`id`, `template_id`, `priority`, `condition_expression`, `actions`, `label`, `is_active`) VALUES

-- Auto-FAIL if any critical defects
(1, 1, 1, 'defects.critical_count > 0',
  '["set_status:FAIL", "require_manager_approval", "trigger_CAPA", "set_decision:FAIL"]',
  'Critical defect found - auto fail', 1),

-- Fail if major defects exceed limit (based on AQL)
(2, 1, 2, 'defects.major_count > aql_calculator.major_allowable',
  '["set_status:FAIL", "require_manager_approval", "set_decision:FAIL"]',
  'Major defects exceed AQL limit', 1),

-- Conditional pass if minor defects exceed limit
(3, 1, 3, 'defects.minor_count > aql_calculator.minor_allowable AND defects.total_defects > 0',
  '["set_status:CONDITIONAL_PASS", "require_manager_approval", "set_decision:CONDITIONAL_PASS"]',
  'Minor defects exceed limit - conditional pass', 1),

-- Auto-pass if no defects
(4, 1, 4, 'defects.total_defects = 0 AND aql_calculator.aql_result = "accept"',
  '["set_status:PASS", "set_decision:PASS"]',
  'Clean inspection - auto pass', 1),

-- Trigger CAPA for measurement failures
(5, 1, 5, 'measurements.failure_count > 3',
  '["trigger_CAPA", "set_decision:HOLD", "require_manager_approval"]',
  'Multiple measurement failures', 1),

-- Require re-inspection for major packaging defects
(6, 1, 6, 'packaging.packaging_defects != "none" AND packaging.packaging_defects != "minor"',
  '["set_decision:RE_INSPECTION", "set_status:FAIL"]',
  'Packaging issues require re-inspection', 1),

-- Conditional pass for labeling issues
(7, 1, 7, 'labeling.barcode_verified = "not_readable" OR labeling.barcode_verified = "wrong_code"',
  '["set_decision:CONDITIONAL_PASS", "require_manager_approval", "trigger_CAPA"]',
  'Label issues detected', 1),

-- Color reject triggers CAPA
(8, 1, 8, 'color_check.color_match_result = "reject"',
  '["set_decision:FAIL", "trigger_CAPA", "require_manager_approval"]',
  'Color rejection - auto CAPA', 1),

-- Auto-CAPA for certain critical defects
(9, 1, 9, 'defects.critical_defect_codes CONTAINS "CRT-01" OR defects.critical_defect_codes CONTAINS "CRT-02"',
  '["trigger_CAPA", "set_status:FAIL", "set_decision:FAIL", "require_regional_approval"]',
  'Safety-critical defect requires regional manager approval', 1);

-- ========================
-- 7. QUALITY PLAN
-- ========================
INSERT IGNORE INTO `quality_plans` (`id`, `buyer_id`, `template_id`, `name`, `version`, `aql_level`, `aql_critical`, `aql_major`, `aql_minor`, `critical_defects_limit`, `major_defects_limit`, `minor_defects_limit`, `rules`, `config`, `status`) VALUES
(1, NULL, 1, 'Standard AQL Plan (Level II)', 1, 'II', 0, 2.5, 4.0, 0, NULL, NULL,
  '{"auto_calculate": true, "inspection_level": "normal", "lot_size_table": "standard"}',
  '{"default_sample_size_code": "M", "use_reduced_inspection": false, "use_tightened_inspection": false}',
  'active');

-- ========================
-- 8. SAMPLE INSPECTORS
-- ========================
INSERT IGNORE INTO `quality_inspectors` (`id`, `name`, `employee_code`, `designation`, `certification`, `status`) VALUES
(1, 'Rajesh Kumar', 'QC-001', 'Senior Quality Inspector', 'AQL Level II Certified, Six Sigma Green Belt', 'active'),
(2, 'Priya Sharma', 'QC-002', 'Quality Inspector', 'AQL Level II Certified', 'active'),
(3, 'Ahmed Hassan', 'QC-003', 'Junior Quality Inspector', 'In Training', 'active'),
(4, 'Maria Santos', 'QC-004', 'Quality Supervisor', 'AQL Level III Certified, Six Sigma Black Belt', 'active');

SET FOREIGN_KEY_CHECKS = 1;
