-- Quality Assurance Demo Data for UUID-based schema
-- Insert demo data for QA module

-- Get the first org_id
DO $$
DECLARE
    demo_org_id UUID;
BEGIN
    SELECT org_id INTO demo_org_id FROM orgs LIMIT 1;
    
    IF demo_org_id IS NULL THEN
        RAISE NOTICE 'No organization found. Please create an organization first.';
        RETURN;
    END IF;

    -- Insert QA Standards
    INSERT INTO qa_standards (org_id, standard_code, name, description, category, status, effective_date)
    VALUES 
        (demo_org_id, 'STD-FRESH-001', 'Fresh Produce - Grade A', 'Premium quality fresh produce standards', 'appearance', 'active', CURRENT_DATE),
        (demo_org_id, 'STD-WEIGHT-001', 'Packaged Weight Standard', 'Net weight requirements for packaged goods', 'weight', 'active', CURRENT_DATE),
        (demo_org_id, 'STD-TEMP-001', 'Cold Chain Temperature', 'Temperature requirements for refrigerated products', 'temperature', 'active', CURRENT_DATE),
        (demo_org_id, 'STD-PKG-001', 'Packaging Integrity', 'Standards for packaging quality and sealing', 'packaging', 'active', CURRENT_DATE);

    -- Insert QA Inspections
    INSERT INTO qa_inspections (org_id, inspection_number, inspection_type, inspection_date, batch_number, quantity_inspected, unit, overall_result, defect_rate, notes, status, priority)
    VALUES 
        (demo_org_id, 'INS-2024-001', 'receiving', NOW() - INTERVAL '5 days', 'BATCH-001', 500, 'kg', 'pass', 1.2, 'Excellent quality. Minor blemishes within acceptable range.', 'completed', 'normal'),
        (demo_org_id, 'INS-2024-002', 'receiving', NOW() - INTERVAL '4 days', 'BATCH-002', 300, 'kg', 'pass', 0.5, 'Fresh product, good color and texture.', 'completed', 'normal'),
        (demo_org_id, 'INS-2024-003', 'receiving', NOW() - INTERVAL '3 days', 'BATCH-003', 800, 'kg', 'fail', 12.5, 'Significant bruising and size variance. Rejected.', 'completed', 'urgent'),
        (demo_org_id, 'INS-2024-004', 'in-process', NOW() - INTERVAL '2 days', 'PKG-001', 200, 'packages', 'pass', 0.0, 'Packaging line inspection. All seals intact.', 'completed', 'normal'),
        (demo_org_id, 'INS-2024-005', 'final', NOW() - INTERVAL '1 day', 'SHIP-001', 50, 'pallets', 'pass', 0.5, 'Final inspection before shipment. Quality criteria met.', 'completed', 'high'),
        (demo_org_id, 'INS-2024-006', 'random', NOW(), 'COLD-STORE-A', 0, 'n/a', 'pass', 0.0, 'Cold storage temperature check. All zones within range.', 'completed', 'normal'),
        (demo_org_id, 'INS-2024-007', 'receiving', NOW(), 'BATCH-004', 600, 'kg', 'pending', NULL, 'Incoming shipment scheduled for inspection.', 'in-progress', 'normal');

    -- Insert QA Tests for first inspection
    INSERT INTO qa_tests (org_id, inspection_id, test_code, test_name, test_category, parameter, expected_value, actual_value, result, tested_at)
    SELECT 
        demo_org_id,
        i.id,
        'TEST-VIS-001',
        'Visual Appearance Check',
        'visual',
        'color',
        'Fresh/Vibrant',
        'Fresh/Vibrant',
        'pass',
        i.inspection_date
    FROM qa_inspections i 
    WHERE i.org_id = demo_org_id AND i.inspection_number = 'INS-2024-001';

    -- Insert Non-Conformances
    INSERT INTO qa_non_conformances (org_id, nc_number, detected_date, category, severity, description, root_cause, immediate_action, corrective_action, status, due_date)
    VALUES 
        (demo_org_id, 'NC-2024-001', NOW() - INTERVAL '3 days', 'product-defect', 'major', 
         'Shipment received with excessive bruising. Product rejected.',
         'Rough handling during transport.',
         'Rejected damaged product. Contacted supplier.',
         'Review handling procedures with supplier. Implement stricter receiving criteria.',
         'closed', NOW() + INTERVAL '7 days'),
        (demo_org_id, 'NC-2024-002', NOW() - INTERVAL '1 day', 'equipment-failure', 'minor',
         'Packaging line sealer producing weak seals.',
         'Heat sealer temperature drift.',
         'Line stopped. Affected packages resealed.',
         'Replace heating element. Implement daily temperature verification.',
         'action-taken', NOW() + INTERVAL '3 days');

    RAISE NOTICE 'QA demo data inserted successfully for org_id: %', demo_org_id;
END $$;
