-- Quality Assurance Demo Data (Fixed for UUID schema)
-- Populate QA tables with sample data for testing

DO $$
DECLARE
    sample_org_id UUID;
    sample_user_id UUID;
    sample_product_id UUID;
    sample_location_id INTEGER;
    sample_category_id UUID;
    standard_id_1 UUID;
    standard_id_2 UUID;
    inspection_id_1 UUID;
    inspection_id_2 UUID;
    inspection_id_3 UUID;
BEGIN
    -- Get sample IDs from existing data
    SELECT org_id INTO sample_org_id FROM orgs LIMIT 1;
    SELECT user_id INTO sample_user_id FROM users LIMIT 1;
    SELECT product_id INTO sample_product_id FROM products LIMIT 1;
    SELECT id INTO sample_location_id FROM locations LIMIT 1;
    SELECT category_id INTO sample_category_id FROM product_categories LIMIT 1;
    
    RAISE NOTICE 'Using org_id: %, user_id: %, product_id: %', sample_org_id, sample_user_id, sample_product_id;
    
    -- Insert QA Standards
    INSERT INTO qa_standards (id, org_id, standard_code, name, description, category, product_category_id, status, created_by)
    VALUES 
        (uuid_generate_v7(), sample_org_id, 'STD-FRESH-001', 'Fresh Produce Standard', 'Quality standard for fresh fruits and vegetables', 'freshness', sample_category_id, 'active', sample_user_id),
        (uuid_generate_v7(), sample_org_id, 'STD-SIZE-001', 'Size Grading Standard', 'Standard sizing criteria for produce', 'size', sample_category_id, 'active', sample_user_id),
        (uuid_generate_v7(), sample_org_id, 'STD-PKG-001', 'Packaging Standard', 'Packaging and labeling requirements', 'packaging', sample_category_id, 'active', sample_user_id);
    
    -- Get the standard IDs
    SELECT id INTO standard_id_1 FROM qa_standards WHERE standard_code = 'STD-FRESH-001';
    SELECT id INTO standard_id_2 FROM qa_standards WHERE standard_code = 'STD-SIZE-001';
    
    -- Insert QA Inspections
    INSERT INTO qa_inspections (id, org_id, inspection_number, inspection_type, inspection_date, product_id, product_name, batch_number, location_id, quantity_inspected, unit, inspector_id, standard_id, overall_result, defect_rate, status, priority, created_by)
    VALUES 
        (uuid_generate_v7(), sample_org_id, 'INS-2024-001', 'receiving', CURRENT_TIMESTAMP - INTERVAL '5 days', sample_product_id, 'Sample Product', 'BATCH-001', sample_location_id, 500.00, 'kg', sample_user_id, standard_id_1, 'pass', 2.5, 'completed', 'normal', sample_user_id),
        (uuid_generate_v7(), sample_org_id, 'INS-2024-002', 'in-process', CURRENT_TIMESTAMP - INTERVAL '3 days', sample_product_id, 'Sample Product', 'BATCH-002', sample_location_id, 750.00, 'kg', sample_user_id, standard_id_2, 'conditional', 5.8, 'completed', 'normal', sample_user_id),
        (uuid_generate_v7(), sample_org_id, 'INS-2024-003', 'final', CURRENT_TIMESTAMP - INTERVAL '1 day', sample_product_id, 'Sample Product', 'BATCH-003', sample_location_id, 300.00, 'kg', sample_user_id, standard_id_1, 'fail', 12.3, 'completed', 'high', sample_user_id),
        (uuid_generate_v7(), sample_org_id, 'INS-2024-004', 'random', CURRENT_TIMESTAMP, sample_product_id, 'Sample Product', 'BATCH-004', sample_location_id, 450.00, 'kg', sample_user_id, standard_id_2, 'pending', NULL, 'pending', 'normal', sample_user_id);
    
    -- Get inspection IDs
    SELECT id INTO inspection_id_1 FROM qa_inspections WHERE inspection_number = 'INS-2024-001';
    SELECT id INTO inspection_id_2 FROM qa_inspections WHERE inspection_number = 'INS-2024-002';
    SELECT id INTO inspection_id_3 FROM qa_inspections WHERE inspection_number = 'INS-2024-003';
    
    -- Insert QA Tests
    INSERT INTO qa_tests (id, org_id, inspection_id, test_code, test_name, test_category, parameter, expected_value, actual_value, result, tested_by, tested_at)
    VALUES 
        (uuid_generate_v7(), sample_org_id, inspection_id_1, 'TEST-001', 'Weight Check', 'physical', 'weight', '100-150g', '125g', 'pass', sample_user_id, CURRENT_TIMESTAMP - INTERVAL '5 days'),
        (uuid_generate_v7(), sample_org_id, inspection_id_1, 'TEST-002', 'Color Inspection', 'visual', 'color', 'Green', 'Green', 'pass', sample_user_id, CURRENT_TIMESTAMP - INTERVAL '5 days'),
        (uuid_generate_v7(), sample_org_id, inspection_id_2, 'TEST-003', 'Size Grading', 'physical', 'diameter', '60-80mm', '75mm', 'pass', sample_user_id, CURRENT_TIMESTAMP - INTERVAL '3 days'),
        (uuid_generate_v7(), sample_org_id, inspection_id_2, 'TEST-004', 'Brix Level', 'chemical', 'brix', '12-14%', '11.5%', 'marginal', sample_user_id, CURRENT_TIMESTAMP - INTERVAL '3 days'),
        (uuid_generate_v7(), sample_org_id, inspection_id_3, 'TEST-005', 'Temperature Check', 'physical', 'temperature', '0-4°C', '8°C', 'fail', sample_user_id, CURRENT_TIMESTAMP - INTERVAL '1 day');
    
    -- Insert Non-Conformances
    INSERT INTO qa_non_conformances (id, org_id, nc_number, inspection_id, detected_date, category, severity, description, status, responsible_person_id, created_by)
    VALUES 
        (uuid_generate_v7(), sample_org_id, 'NC-2024-001', inspection_id_3, CURRENT_TIMESTAMP - INTERVAL '1 day', 'product-defect', 'major', 'Product temperature exceeds acceptable range. Cold chain may have been broken during transport.', 'open', sample_user_id, sample_user_id),
        (uuid_generate_v7(), sample_org_id, 'NC-2024-002', inspection_id_2, CURRENT_TIMESTAMP - INTERVAL '3 days', 'product-defect', 'minor', 'Brix level slightly below expected range. Product may be under-ripe.', 'investigating', sample_user_id, sample_user_id);
    
    RAISE NOTICE 'Demo data inserted successfully!';
END $$;
