-- Quality Assurance Demo Data
-- Realistic data for agriculture wholesale & packing industry

-- Assuming org_id = 1, and some existing products, suppliers, locations, users

-- QA Standards (Quality criteria for different product categories)
INSERT INTO qa_standards (org_id, standard_code, name, description, category, product_category_id, criteria, status, effective_date, created_by) VALUES
(1, 'STD-FRESH-001', 'Fresh Produce - Grade A', 'Premium quality fresh produce standards for retail distribution', 'appearance', NULL, 
 '{"color": "vibrant", "firmness": "firm", "blemishes": "max_2%", "size_variance": "max_10%"}', 'active', '2024-01-01', 1),

(1, 'STD-FRESH-002', 'Fresh Produce - Grade B', 'Standard quality fresh produce for processing', 'appearance', NULL,
 '{"color": "acceptable", "firmness": "moderately_firm", "blemishes": "max_10%", "size_variance": "max_20%"}', 'active', '2024-01-01', 1),

(1, 'STD-WEIGHT-001', 'Packaged Weight Standard', 'Net weight requirements for packaged goods', 'weight', NULL,
 '{"tolerance": "±2%", "min_compliance": "98%"}', 'active', '2024-01-01', 1),

(1, 'STD-TEMP-001', 'Cold Chain Temperature', 'Temperature requirements for refrigerated products', 'temperature', NULL,
 '{"min_temp": "2°C", "max_temp": "4°C", "critical_limit": "8°C"}', 'active', '2024-01-01', 1),

(1, 'STD-PKG-001', 'Packaging Integrity', 'Standards for packaging quality and sealing', 'packaging', NULL,
 '{"seal_strength": "min_5N", "visual_defects": "max_1%", "label_quality": "clear_readable"}', 'active', '2024-01-01', 1),

(1, 'STD-SIZE-TOMATO', 'Tomato Size Grading', 'Size classification for tomatoes', 'size', NULL,
 '{"small": "40-60mm", "medium": "60-80mm", "large": "80-100mm"}', 'active', '2024-01-01', 1),

(1, 'STD-FRESH-LETTUCE', 'Lettuce Freshness Standard', 'Freshness criteria for lettuce products', 'freshness', NULL,
 '{"leaf_color": "bright_green", "wilting": "none", "bruising": "max_5%", "shelf_life_days": "min_7"}', 'active', '2024-01-01', 1),

(1, 'STD-CONTAM-001', 'Contamination Prevention', 'Foreign object and contamination standards', 'contamination', NULL,
 '{"foreign_objects": "zero_tolerance", "pest_evidence": "zero_tolerance", "chemical_residue": "within_limits"}', 'active', '2024-01-01', 1);

-- QA Inspections (Various inspection scenarios)
INSERT INTO qa_inspections (org_id, inspection_number, inspection_type, inspection_date, product_id, batch_number, supplier_id, location_id, quantity_inspected, unit, inspector_id, standard_id, overall_result, defect_rate, notes, status, priority, created_by) VALUES
-- Receiving inspections
(1, 'INS-2024-001', 'receiving', '2024-11-15 08:30:00', 1, 'BATCH-TOM-20241115', 1, 1, 500, 'kg', 1, 1, 'pass', 1.2, 'Excellent quality tomatoes from Farm Fresh Suppliers. Minor blemishes within acceptable range.', 'completed', 'high', 1),

(1, 'INS-2024-002', 'receiving', '2024-11-15 09:45:00', 2, 'BATCH-LET-20241115', 1, 1, 300, 'kg', 1, 7, 'pass', 0.5, 'Fresh crisp lettuce, good color and texture.', 'completed', 'normal', 1),

(1, 'INS-2024-003', 'receiving', '2024-11-16 07:15:00', 3, 'BATCH-CAR-20241116', 2, 1, 800, 'kg', 2, 1, 'fail', 12.5, 'Significant bruising and size variance. Rejected 100kg. Contacted supplier for replacement.', 'completed', 'urgent', 1),

(1, 'INS-2024-004', 'receiving', '2024-11-16 10:00:00', 4, 'BATCH-POT-20241116', 1, 1, 1200, 'kg', 1, 3, 'pass', 2.8, 'Weight variance within tolerance. Minor dirt on some potatoes cleaned during processing.', 'completed', 'normal', 1),

-- In-process inspections
(1, 'INS-2024-005', 'in-process', '2024-11-17 11:30:00', 1, 'PKG-TOM-500G-001', NULL, 2, 200, 'packages', 2, 5, 'pass', 0.0, 'Packaging line inspection. All seals intact, labels clear.', 'completed', 'normal', 1),

(1, 'INS-2024-006', 'in-process', '2024-11-17 14:00:00', 2, 'PKG-LET-200G-001', NULL, 2, 150, 'packages', 2, 5, 'conditional', 3.3, '5 packages with weak seals. Line adjusted and re-inspected.', 'completed', 'high', 1),

(1, 'INS-2024-007', 'in-process', '2024-11-18 09:00:00', 5, 'BATCH-ORG-20241118', NULL, 2, 400, 'kg', 1, 6, 'pass', 1.0, 'Size grading inspection during packing. Good size distribution.', 'completed', 'normal', 1),

-- Final inspections
(1, 'INS-2024-008', 'final', '2024-11-18 15:30:00', 1, 'SHIP-RETAIL-001', NULL, 3, 50, 'pallets', 2, 1, 'pass', 0.5, 'Final inspection before shipment to retail chain. All quality criteria met.', 'completed', 'high', 1),

(1, 'INS-2024-009', 'final', '2024-11-19 08:00:00', 6, 'SHIP-EXPORT-001', NULL, 3, 30, 'pallets', 1, 1, 'pass', 0.0, 'Export shipment inspection. Premium grade cucumbers. Documentation complete.', 'completed', 'urgent', 1),

-- Temperature monitoring inspections
(1, 'INS-2024-010', 'random', '2024-11-19 12:00:00', 2, 'COLD-STORE-A', NULL, 4, 0, 'n/a', 2, 4, 'pass', 0.0, 'Cold storage temperature check. All zones within 2-4°C range.', 'completed', 'high', 1),

(1, 'INS-2024-011', 'random', '2024-11-20 06:30:00', 3, 'COLD-STORE-B', NULL, 4, 0, 'n/a', 1, 4, 'fail', 0.0, 'Temperature spike detected in zone B3 (6.5°C). Investigated cooling unit malfunction.', 'completed', 'urgent', 1),

-- Random quality checks
(1, 'INS-2024-012', 'random', '2024-11-20 10:15:00', 7, 'BATCH-PEP-20241120', NULL, 2, 100, 'kg', 2, 1, 'pass', 2.0, 'Random check during packing. Quality consistent with standards.', 'completed', 'normal', 1),

(1, 'INS-2024-013', 'random', '2024-11-20 13:45:00', 8, 'BATCH-ONI-20241120', NULL, 2, 150, 'kg', 1, 3, 'pass', 1.5, 'Weight and size verification. Good batch quality.', 'completed', 'normal', 1),

-- Customer complaint inspection
(1, 'INS-2024-014', 'customer-complaint', '2024-11-20 16:00:00', 1, 'BATCH-TOM-20241115', NULL, 1, 10, 'kg', 1, 1, 'fail', 40.0, 'Customer complaint: premature spoilage. Retained samples show signs of early bruising not detected in receiving inspection. Root cause analysis initiated.', 'completed', 'urgent', 1),

-- Pending/In-progress inspections
(1, 'INS-2024-015', 'receiving', '2024-11-21 08:00:00', 9, 'BATCH-APP-20241121', 3, 1, 600, 'kg', 1, 1, 'pending', NULL, 'Incoming apple shipment scheduled for inspection.', 'in-progress', 'normal', 1),

(1, 'INS-2024-016', 'in-process', '2024-11-21 10:00:00', 10, 'PKG-MIX-VEG-001', NULL, 2, 100, 'packages', 2, 5, 'pending', NULL, 'Mixed vegetable packaging line inspection in progress.', 'in-progress', 'normal', 1);

-- QA Tests (Detailed test results for inspections)
INSERT INTO qa_tests (org_id, inspection_id, test_code, test_name, test_category, parameter, expected_value, actual_value, tolerance, result, test_method, equipment_used, tested_by, tested_at, notes) VALUES
-- Tests for INS-2024-001 (Tomatoes - Pass)
(1, 1, 'TEST-VIS-001', 'Visual Appearance Check', 'visual', 'color', 'Red/Vibrant', 'Red/Vibrant', 'N/A', 'pass', 'Visual inspection against color chart', 'Color chart TC-001', 1, '2024-11-15 08:35:00', 'Uniform deep red color'),
(1, 1, 'TEST-PHY-001', 'Firmness Test', 'physical', 'firmness', 'Firm (7-9 on scale)', '8', '±1', 'pass', 'Manual firmness scale', 'Firmness gauge FG-100', 1, '2024-11-15 08:40:00', 'Good firmness throughout sample'),
(1, 1, 'TEST-VIS-002', 'Blemish Count', 'visual', 'surface_defects', '<2%', '1.2%', '±0.5%', 'pass', 'Visual count per 100 units', 'None', 1, '2024-11-15 08:45:00', '6 blemished tomatoes in 500 sample'),
(1, 1, 'TEST-PHY-002', 'Size Measurement', 'physical', 'diameter', '60-80mm', '65-78mm', '±5mm', 'pass', 'Caliper measurement', 'Digital caliper DC-200', 1, '2024-11-15 08:50:00', 'Size variance 9.2%'),

-- Tests for INS-2024-003 (Carrots - Fail)
(1, 3, 'TEST-VIS-003', 'Visual Inspection', 'visual', 'appearance', 'Orange/Clean', 'Orange/Some bruising', 'N/A', 'fail', 'Visual assessment', 'None', 2, '2024-11-16 07:20:00', 'Significant bruising visible on 10% of batch'),
(1, 3, 'TEST-PHY-003', 'Size Variance', 'physical', 'length', '150-200mm', '120-220mm', '±10mm', 'fail', 'Length measurement', 'Measuring tape', 2, '2024-11-16 07:30:00', 'Size variance 25% - exceeds standard'),
(1, 3, 'TEST-VIS-004', 'Defect Assessment', 'visual', 'damage', '<5%', '12.5%', '±2%', 'fail', 'Sample assessment', 'None', 2, '2024-11-16 07:40:00', '100 units with visible bruising/damage'),

-- Tests for INS-2024-005 (Packaging - Pass)
(1, 5, 'TEST-PKG-001', 'Seal Strength Test', 'physical', 'seal_integrity', '≥5N', '6.2N', '±0.5N', 'pass', 'Peel test', 'Seal tester ST-500', 2, '2024-11-17 11:35:00', 'All seals above minimum strength'),
(1, 5, 'TEST-VIS-005', 'Label Quality', 'visual', 'print_quality', 'Clear/Readable', 'Clear/Readable', 'N/A', 'pass', 'Visual check', 'None', 2, '2024-11-17 11:40:00', 'All labels properly printed and positioned'),
(1, 5, 'TEST-PHY-004', 'Package Weight', 'physical', 'net_weight', '500g ±2%', '498-502g', '±10g', 'pass', 'Weight verification', 'Scale SC-1000', 2, '2024-11-17 11:45:00', 'Weight variance within tolerance'),

-- Tests for INS-2024-006 (Packaging - Conditional)
(1, 6, 'TEST-PKG-002', 'Seal Integrity Check', 'physical', 'seal_strength', '≥5N', '4.2-6.5N', '±0.5N', 'marginal', 'Peel test', 'Seal tester ST-500', 2, '2024-11-17 14:05:00', '5 packages below minimum - line adjusted'),
(1, 6, 'TEST-VIS-006', 'Visual Defects', 'visual', 'package_appearance', 'No defects', 'Minor wrinkles', 'N/A', 'pass', 'Visual inspection', 'None', 2, '2024-11-17 14:10:00', 'Some film wrinkling but acceptable'),

-- Tests for INS-2024-010 (Temperature - Pass)
(1, 10, 'TEST-TEMP-001', 'Zone A Temperature', 'physical', 'temperature', '2-4°C', '3.2°C', '±0.5°C', 'pass', 'Digital thermometer', 'Thermometer TH-200', 2, '2024-11-19 12:05:00', 'Stable temperature'),
(1, 10, 'TEST-TEMP-002', 'Zone B Temperature', 'physical', 'temperature', '2-4°C', '2.8°C', '±0.5°C', 'pass', 'Digital thermometer', 'Thermometer TH-200', 2, '2024-11-19 12:10:00', 'Stable temperature'),
(1, 10, 'TEST-TEMP-003', 'Zone C Temperature', 'physical', 'temperature', '2-4°C', '3.5°C', '±0.5°C', 'pass', 'Digital thermometer', 'Thermometer TH-200', 2, '2024-11-19 12:15:00', 'Stable temperature'),

-- Tests for INS-2024-011 (Temperature - Fail)
(1, 11, 'TEST-TEMP-004', 'Zone B3 Temperature', 'physical', 'temperature', '2-4°C', '6.5°C', '±0.5°C', 'fail', 'Digital thermometer', 'Thermometer TH-200', 1, '2024-11-20 06:35:00', 'Temperature above critical limit'),

-- Tests for INS-2024-014 (Customer Complaint - Fail)
(1, 14, 'TEST-VIS-007', 'Retained Sample Inspection', 'visual', 'bruising', '<2%', '40%', '±2%', 'fail', 'Visual assessment', 'None', 1, '2024-11-20 16:10:00', 'Significant hidden bruising discovered'),
(1, 14, 'TEST-SENS-001', 'Firmness Assessment', 'sensory', 'texture', 'Firm', 'Soft spots', 'N/A', 'fail', 'Manual assessment', 'None', 1, '2024-11-20 16:15:00', 'Early deterioration signs');

-- QA Results (Detailed measurements)
INSERT INTO qa_results (org_id, inspection_id, test_id, sample_number, measurement_type, measurement_value, measurement_unit, pass_fail, grade, notes, recorded_by) VALUES
-- Results for tomato inspection
(1, 1, 1, 1, 'color_score', 9.0, 'scale_1-10', 'pass', 'A', 'Excellent color', 1),
(1, 1, 2, 1, 'firmness', 8.0, 'scale_1-10', 'pass', 'A', 'Good firmness', 1),
(1, 1, 3, 1, 'blemish_count', 6.0, 'count', 'pass', 'A', '6 blemished in 500', 1),
(1, 1, 4, 1, 'diameter', 65.0, 'mm', 'pass', 'A', 'Within range', 1),
(1, 1, 4, 2, 'diameter', 72.0, 'mm', 'pass', 'A', 'Within range', 1),
(1, 1, 4, 3, 'diameter', 68.0, 'mm', 'pass', 'A', 'Within range', 1),

-- Results for carrot inspection (failed)
(1, 3, 5, 1, 'bruise_percentage', 12.5, 'percent', 'fail', 'reject', 'Excessive bruising', 2),
(1, 3, 6, 1, 'length_variance', 25.0, 'percent', 'fail', 'reject', 'Size variance too high', 2),

-- Results for packaging inspection
(1, 5, 7, 1, 'seal_strength', 6.2, 'N', 'pass', 'A', 'Good seal', 2),
(1, 5, 7, 2, 'seal_strength', 6.5, 'N', 'pass', 'A', 'Good seal', 2),
(1, 5, 9, 1, 'weight', 500.0, 'g', 'pass', 'A', 'Exact weight', 2),
(1, 5, 9, 2, 'weight', 498.0, 'g', 'pass', 'A', 'Within tolerance', 2),
(1, 5, 9, 3, 'weight', 502.0, 'g', 'pass', 'A', 'Within tolerance', 2),

-- Results for temperature monitoring
(1, 10, 11, 1, 'temperature', 3.2, '°C', 'pass', 'A', 'Zone A stable', 2),
(1, 10, 12, 1, 'temperature', 2.8, '°C', 'pass', 'A', 'Zone B stable', 2),
(1, 10, 13, 1, 'temperature', 3.5, '°C', 'pass', 'A', 'Zone C stable', 2),
(1, 11, 14, 1, 'temperature', 6.5, '°C', 'fail', 'reject', 'Temperature exceeded', 1);

-- Non-Conformances (Quality issues and corrective actions)
INSERT INTO qa_non_conformances (org_id, nc_number, inspection_id, test_id, detected_date, category, severity, description, root_cause, immediate_action, corrective_action, preventive_action, responsible_person_id, due_date, completion_date, status, verification_notes, verified_by, verified_at, cost_impact, created_by) VALUES
(1, 'NC-2024-001', 3, 5, '2024-11-16 07:30:00', 'product-defect', 'major', 
'Carrot shipment BATCH-CAR-20241116 received with excessive bruising (12.5%) and high size variance (25%). 100kg rejected.',
'Rough handling during transport and mixing of different grades by supplier.',
'Rejected 100kg of damaged carrots. Segregated acceptable product (700kg) for processing grade use. Contacted supplier immediately.',
'1) Meet with supplier to review handling procedures. 2) Implement stricter receiving criteria. 3) Request supplier implement better grading system.',
'1) Add handling quality to supplier scorecard. 2) Implement photographic quality standards for supplier reference. 3) Consider alternative suppliers for comparison.',
2, '2024-11-23', '2024-11-22', 'closed', 'Supplier meeting completed. New handling procedures implemented. Next delivery monitored with improved results.', 1, '2024-11-22 15:00:00', 450.00, 1),

(1, 'NC-2024-002', 6, 10, '2024-11-17 14:00:00', 'equipment-failure', 'minor',
'Packaging line sealer producing weak seals. 5 out of 150 packages failed seal strength test (4.2N vs minimum 5N).',
'Heat sealer temperature drift due to aging heating element.',
'Line stopped immediately. Affected packages removed and resealed. Temperature calibration adjusted.',
'Replace heating element. Implement daily temperature verification protocol.',
'Add heating element to preventive maintenance schedule (quarterly replacement). Install temperature monitoring alarm.',
2, '2024-11-20', '2024-11-19', 'closed', 'New heating element installed. Temperature monitoring system implemented. Line running within specifications.', 2, '2024-11-19 16:30:00', 85.00, 1),

(1, 'NC-2024-003', 11, 14, '2024-11-20 06:30:00', 'equipment-failure', 'critical',
'Cold storage Zone B3 temperature exceeded critical limit (6.5°C vs max 4°C). Cooling unit compressor malfunction detected.',
'Compressor bearing failure in cooling unit CU-B3. Unit had been showing minor performance issues for past week but not flagged as critical.',
'Products in affected zone (approx 800kg lettuce) moved to alternate cold storage immediately. Maintenance called for emergency repair.',
'1) Replace compressor in unit CU-B3. 2) Conduct full maintenance inspection of all cooling units. 3) Affected products placed under quarantine pending quality assessment.',
'1) Implement real-time temperature monitoring with automated alerts. 2) Establish predictive maintenance program for cooling equipment. 3) Reduce alert threshold to prevent reaching critical temperatures.',
1, '2024-11-22', '2024-11-21', 'closed', 'Compressor replaced. All affected products inspected - 750kg released as acceptable, 50kg downgraded. Temperature monitoring system enhanced.', 1, '2024-11-21 14:00:00', 1850.00, 1),

(1, 'NC-2024-004', 14, 15, '2024-11-20 16:00:00', 'process-deviation', 'major',
'Customer complaint: tomatoes from BATCH-TOM-20241115 showing premature spoilage (2 days vs expected 7+ days shelf life). Retained sample analysis shows 40% hidden bruising not detected during receiving inspection.',
'Initial receiving inspection focused on visible surface defects. Hidden bruising from rough handling not detected by standard visual inspection. Current firmness test may not be sensitive enough to detect early bruising.',
'Initiated customer replacement. Contacted all customers who received product from same batch. Retained samples for further analysis.',
'1) Review and enhance receiving inspection protocol to include more thorough bruising detection. 2) Implement sample cutting/cross-section inspection for high-value shipments. 3) Add pressure-point firmness testing. 4) Retrain inspection staff on subtle bruising indicators.',
'1) Invest in non-destructive bruising detection technology (acoustic or near-infrared). 2) Implement statistical sampling with destructive testing. 3) Add supplier handling audit to quality program. 4) Develop photographic reference guide for acceptable/reject criteria.',
1, '2024-11-30', NULL, 'action-taken', 'Enhanced inspection protocol implemented. Staff training completed. Awaiting verification of next shipment quality.', NULL, NULL, 2400.00, 1),

(1, 'NC-2024-005', NULL, NULL, '2024-11-18 13:00:00', 'documentation', 'minor',
'Inspection records for BATCH-POT-20241113 missing detailed test results. Only summary results recorded.',
'Inspector did not complete full documentation form. Workload pressure during busy receiving period.',
'Reconstructed missing data from inspector notes and memory. Updated training on documentation requirements.',
'Implement mandatory documentation checklist. System should prevent inspection completion without all required fields.',
'Implement digital inspection forms with required field validation. Consider mobile app for real-time data entry at inspection point.',
2, '2024-11-21', '2024-11-20', 'closed', 'Documentation procedures reinforced. Checklist system implemented.', 1, '2024-11-20 17:00:00', 0.00, 1),

(1, 'NC-2024-006', NULL, NULL, '2024-11-19 15:30:00', 'supplier-issue', 'minor',
'Organic certification documentation missing for BATCH-ORG-20241118 organic produce shipment from Green Valley Farms.',
'Supplier failed to include organic certification copies with shipment. Communication breakdown.',
'Product placed on hold pending documentation. Contacted supplier - certificates sent via email within 2 hours.',
'Add documentation checklist to receiving SOP. Verify certification before accepting organic shipments.',
'Implement supplier portal where current certifications are maintained and verified. Add certificate expiry tracking to system.',
2, '2024-11-25', '2024-11-19', 'closed', 'Certificates received and verified. Product released. Supplier added to documentation follow-up list.', 2, '2024-11-19 17:45:00', 0.00, 1);

-- Add some open/in-progress NCs
INSERT INTO qa_non_conformances (org_id, nc_number, inspection_id, detected_date, category, severity, description, root_cause, immediate_action, corrective_action, responsible_person_id, due_date, status, created_by) VALUES
(1, 'NC-2024-007', 12, '2024-11-20 10:30:00', 'product-defect', 'minor',
'Bell pepper batch shows slight color inconsistency - mix of red and orange peppers when batch should be uniformly red.',
'Supplier quality control issue - mixed harvest from different ripeness stages.',
'Segregated miscolored peppers (20kg). Contacted supplier to discuss grading standards.',
'Schedule supplier audit to review harvest and grading procedures. Define stricter color specifications in purchase orders.',
2, '2024-11-27', 'investigating', 1),

(1, 'NC-2024-008', NULL, '2024-11-21 09:00:00', 'process-deviation', 'minor',
'Batch number labeling error on 50 packages - wrong date code printed.',
'Operator error during label printer setup. Date not updated from previous day.',
'Stopped line immediately. Removed affected packages and relabeled correctly.',
'Implement automated date code verification. Add operator checklist for shift startup procedures.',
2, '2024-11-24', 'action-taken', 1);

-- Update some statistics based on the demo data
COMMENT ON TABLE qa_inspections IS 'Quality inspection records - Demo data represents typical agriculture/packing operations';
COMMENT ON TABLE qa_non_conformances IS 'Non-conformance tracking - Examples include supplier issues, equipment failures, process deviations';
