-- ============================================================
-- Supplier Audit Module — Comprehensive Seed Data
-- Run: mysql -u root fcqc_database < database/seed_supplier_audit.sql
-- ============================================================

SET @company_id = 1;

-- ============================================================
-- 1. SUPPLIER PROFILES (add 20 more diverse suppliers)
-- ============================================================
INSERT IGNORE INTO supplier_profiles (
    company_id, supplier_name, supplier_code, factory_name, country, city,
    supplier_type, contact_person, contact_email, contact_phone,
    audit_status, overall_score, risk_level, last_audit_date,
    year_established, facility_size_sqft, total_employees,
    num_sewing_lines, monthly_capacity, lead_time_days,
    latitude, longitude, status, created_at
) VALUES
(@company_id, 'Apex Textile Mills', 'SUP-017', 'Apex Factory Unit 1', 'Bangladesh', 'Dhaka', 'fabric', 'Rahman Khan', 'rahman@apextextile.com', '+880-171-555-0101', 'approved', 87.50, 'low', '2025-11-15', 2005, 45000, 320, 12, 50000, 45, 23.8103, 90.4125, 'active', NOW()),
(@company_id, 'Star Garments Ltd', 'SUP-018', 'Star Unit A', 'Bangladesh', 'Chittagong', 'cmt', 'Fatima Ahmed', 'fatima@stargarments.com', '+880-181-555-0202', 'approved', 92.10, 'low', '2025-10-20', 2008, 62000, 480, 18, 80000, 40, 22.3569, 91.7832, 'active', NOW()),
(@company_id, 'Delta Knitwears', 'SUP-019', 'Delta Knitting Unit', 'Bangladesh', 'Gazipur', 'full_package', 'Karim Hassan', 'karim@deltaknit.com', '+880-191-555-0303', 'conditional', 68.20, 'medium', '2025-09-10', 2001, 35000, 250, 8, 35000, 50, 23.9999, 90.4075, 'active', NOW()),
(@company_id, 'Bombay Synthetics', 'SUP-020', 'Bombay Mill 3', 'India', 'Mumbai', 'fabric', 'Priya Sharma', 'priya@bombaysynth.in', '+91-98765-43210', 'approved', 91.30, 'low', '2025-12-01', 1998, 80000, 600, 24, 120000, 35, 19.0760, 72.8777, 'active', NOW()),
(@company_id, 'Chennai Exports Pvt', 'SUP-021', 'Chennai Factory Block B', 'India', 'Chennai', 'full_package', 'Anand Raj', 'anand@chennaiexports.in', '+91-98765-54321', 'approved', 84.60, 'low', '2025-08-25', 2010, 55000, 400, 16, 70000, 38, 13.0827, 80.2707, 'active', NOW()),
(@company_id, 'Tirupur Textiles', 'SUP-022', 'Tirupur Processing Unit', 'India', 'Tirupur', 'fabric', 'Meena Devi', 'meena@tirupurtex.in', '+91-98765-65432', 'blocked', 45.80, 'critical', '2025-06-15', 1995, 30000, 180, 6, 25000, 55, 11.1085, 77.3411, 'active', NOW()),
(@company_id, 'Saigon Fashion Co', 'SUP-023', 'SGN Workshop 2', 'Vietnam', 'Ho Chi Minh City', 'cmt', 'Nguyen Van Thanh', 'thanhn@saigonfashion.vn', '+84-901-234-567', 'approved', 89.70, 'low', '2025-11-05', 2012, 42000, 350, 14, 60000, 42, 10.8231, 106.6297, 'active', NOW()),
(@company_id, 'Hanoi Garment JSC', 'SUP-024', 'HN Garment Factory', 'Vietnam', 'Hanoi', 'full_package', 'Tran Thi Mai', 'mai@hanoigarment.vn', '+84-912-345-678', 'conditional', 71.50, 'medium', '2025-07-20', 2006, 50000, 380, 15, 65000, 48, 21.0278, 105.8342, 'active', NOW()),
(@company_id, 'Istanbul Tekstil', 'SUP-025', 'Istanbul Main Factory', 'Turkey', 'Istanbul', 'fabric', 'Mehmet Ozkan', 'mehmet@istanbultekstil.com.tr', '+90-532-123-4567', 'approved', 93.40, 'low', '2025-12-10', 2000, 70000, 520, 20, 90000, 30, 41.0082, 28.9784, 'active', NOW()),
(@company_id, 'Anadolu Giyim', 'SUP-026', 'Anadolu Workshop', 'Turkey', 'Bursa', 'trim', 'Elif Yilmaz', 'elif@anadolugiyim.com.tr', '+90-532-987-6543', 'approved', 86.90, 'low', '2025-10-01', 2015, 25000, 180, 0, 0, 28, 40.1826, 29.0665, 'active', NOW()),
(@company_id, 'Shenzhen Smart Textiles', 'SUP-027', 'SZ Smart Factory', 'China', 'Shenzhen', 'full_package', 'Wei Zhang', 'wei@shenzhensmart.cn', '+86-138-1234-5678', 'approved', 95.20, 'low', '2025-12-15', 2003, 95000, 750, 30, 150000, 25, 22.5431, 114.0579, 'active', NOW()),
(@company_id, 'Guangzhou Fashion Hub', 'SUP-028', 'GZH Unit 1', 'China', 'Guangzhou', 'cmt', 'Liu Wei', 'liu@gzfashion.cn', '+86-139-8765-4321', 'conditional', 65.30, 'high', '2025-05-10', 2009, 38000, 280, 10, 45000, 52, 23.1291, 113.2644, 'active', NOW()),
(@company_id, 'Colombo Clothing Co', 'SUP-029', 'Colombo SEZ Factory', 'Sri Lanka', 'Colombo', 'full_package', 'Sunil Perera', 'sunil@colomboclothing.lk', '+94-77-123-4567', 'approved', 88.40, 'low', '2025-09-30', 2007, 48000, 360, 14, 55000, 44, 6.9271, 79.8612, 'active', NOW()),
(@company_id, 'Lahore Textile Mills', 'SUP-030', 'LTM Weaving Unit', 'Pakistan', 'Lahore', 'fabric', 'Ahmed Raza', 'ahmed@lahoretextile.pk', '+92-321-1234567', 'pending_qualification', 0, 'medium', NULL, 2011, 40000, 290, 10, 40000, 48, 31.5204, 74.3587, 'active', NOW()),
(@company_id, 'Phnom Penh Garment', 'SUP-031', 'PPG Factory', 'Cambodia', 'Phnom Penh', 'cmt', 'Dara Sok', 'dara@phnompenhgarment.kh', '+855-12-345-6789', 'approved', 82.10, 'low', '2025-08-15', 2014, 32000, 240, 10, 40000, 46, 11.5564, 104.9282, 'active', NOW()),
(@company_id, 'Bandung Textile Pro', 'SUP-032', 'BTP Factory', 'Indonesia', 'Bandung', 'fabric', 'Rudi Hartono', 'rudi@bandungtextile.co.id', '+62-812-3456-7890', 'approved', 80.50, 'medium', '2025-07-01', 2002, 58000, 420, 16, 75000, 42, -6.9175, 107.6191, 'active', NOW()),
(@company_id, 'Yangon Fashion Ltd', 'SUP-033', 'YFL Workshop', 'Myanmar', 'Yangon', 'cmt', 'Aung Kyaw', 'aung@yangonfashion.mm', '+95-9-1234-5678', 'blocked', 38.20, 'critical', '2025-04-20', 2016, 22000, 160, 6, 20000, 60, 16.8661, 96.1951, 'active', NOW()),
(@company_id, 'Addis Garment PLC', 'SUP-034', 'Addis Factory Zone', 'Ethiopia', 'Addis Ababa', 'full_package', 'Tadesse Bekele', 'tadesse@addisgarment.et', '+251-91-234-5678', 'pending_qualification', 0, 'medium', NULL, 2019, 28000, 200, 8, 30000, 55, 9.0250, 38.7469, 'active', NOW()),
(@company_id, 'National Threads', 'SUP-035', 'NT Dyeing Unit', 'Bangladesh', 'Narayanganj', 'chemical', 'Habib Rahman', 'habib@nationalethreads.com', '+880-171-555-0808', 'conditional', 72.80, 'medium', '2025-10-15', 2004, 25000, 150, 0, 0, 40, 23.6234, 90.5000, 'active', NOW());


-- ============================================================
-- 2. SUPPLIER CERTIFICATIONS
-- ============================================================
INSERT INTO supplier_certifications (supplier_id, certification_type, certification_name, issuing_body, certificate_number, issue_date, expiry_date, status, created_at)
SELECT sp.id, cert.cert_type, cert.cert_name, cert.issuing_body, cert.cert_number, cert.issued_date, cert.expiry_date, 'valid', NOW()
FROM supplier_profiles sp
CROSS JOIN (
    SELECT 'iso_9001' as cert_type, 'ISO 9001:2015' as cert_name, 'Bureau Veritas' as issuing_body, 'ISO-9001-2024-001' as cert_number, '2024-01-15' as issued_date, '2027-01-14' as expiry_date
    UNION ALL SELECT 'iso_14001', 'ISO 14001:2015', 'SGS', 'ISO-14001-2024-002', '2024-03-20', '2027-03-19'
    UNION ALL SELECT 'oeko_tex', 'OEKO-TEX Standard 100', 'OEKO-TEX Association', 'OEKO-TEX-100-2024', '2024-06-01', '2025-05-31'
) cert
WHERE sp.supplier_code IN ('SUP-017','SUP-018','SUP-020','SUP-021','SUP-023','SUP-025','SUP-027','SUP-029','SUP-031','SUP-032')
AND NOT EXISTS (SELECT 1 FROM supplier_certifications sc WHERE sc.supplier_id = sp.id AND sc.certification_type = cert.cert_type)
LIMIT 30;


-- ============================================================
-- 3. PORTAL INVITES (for several suppliers)
-- ============================================================
INSERT INTO supplier_portal_invites (supplier_id, company_id, invited_by, token, status, expires_at, created_at)
SELECT sp.id, @company_id, 1,
    CONCAT('test_inv_', LOWER(REPLACE(sp.supplier_code, '-', '_')), '_', UNIX_TIMESTAMP()),
    CASE
        WHEN sp.supplier_code IN ('SUP-017','SUP-020','SUP-023') THEN 'activated'
        WHEN sp.supplier_code = 'SUP-025' THEN 'submitted'
        ELSE 'pending'
    END,
    DATE_ADD(NOW(), INTERVAL 30 DAY),
    DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 30) DAY)
FROM supplier_profiles sp
WHERE sp.supplier_code IN ('SUP-017','SUP-018','SUP-020','SUP-023','SUP-025','SUP-027','SUP-029')
AND NOT EXISTS (SELECT 1 FROM supplier_portal_invites pi WHERE pi.supplier_id = sp.id);


-- ============================================================
-- 4. PORTAL USER ACCOUNTS (for activated invites)
-- ============================================================
INSERT INTO supplier_portal_users (supplier_id, name, email, password_hash, phone, role, status, created_at)
SELECT pi.supplier_id, sp.contact_person, sp.contact_email,
    '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',  -- password: "password"
    sp.contact_phone, 'admin', 'active', NOW()
FROM supplier_portal_invites pi
JOIN supplier_profiles sp ON pi.supplier_id = sp.id
WHERE pi.status IN ('activated', 'submitted')
AND NOT EXISTS (SELECT 1 FROM supplier_portal_users pu WHERE pu.supplier_id = pi.supplier_id);


-- ============================================================
-- 5. PORTAL SUBMISSIONS (for activated invites)
-- ============================================================
INSERT INTO supplier_portal_submissions (
    invite_id, supplier_id, company_name, factory_name, factory_address, city, state, country,
    contact_person, contact_email, contact_phone, year_established, facility_size_sqft,
    total_employees, num_sewing_lines, monthly_capacity, lead_time_days,
    supplier_type, progress_pct, current_step, status, created_at
)
SELECT pi.id, pi.supplier_id, sp.supplier_name, sp.factory_name,
    CONCAT(sp.factory_name, ', Industrial Area, ', sp.city), sp.city, '', sp.country,
    sp.contact_person, sp.contact_email, sp.contact_phone,
    sp.year_established, sp.facility_size_sqft, sp.total_employees,
    sp.num_sewing_lines, sp.monthly_capacity, sp.lead_time_days,
    sp.supplier_type,
    CASE pi.status WHEN 'submitted' THEN 100 WHEN 'activated' THEN 60 ELSE 20 END,
    CASE pi.status WHEN 'submitted' THEN 5 WHEN 'activated' THEN 3 ELSE 1 END,
    CASE pi.status WHEN 'submitted' THEN 'submitted' WHEN 'activated' THEN 'draft' ELSE 'draft' END,
    NOW()
FROM supplier_portal_invites pi
JOIN supplier_profiles sp ON pi.supplier_id = sp.id
WHERE NOT EXISTS (SELECT 1 FROM supplier_portal_submissions ps WHERE ps.supplier_id = pi.supplier_id);


-- ============================================================
-- 6. AUDITS (mix of statuses across suppliers)
-- ============================================================

-- Planned audits (upcoming)
INSERT IGNORE INTO supplier_audits (
    company_id, supplier_id, audit_code, audit_type, audit_status, audit_date,
    auditor_name, audit_scope, overall_score, audit_decision, created_at
) VALUES
(@company_id, (SELECT id FROM supplier_profiles WHERE supplier_code='SUP-018'), 'AUD-2026-017', 'routine', 'planned', DATE_ADD(CURDATE(), INTERVAL 5 DAY),
 'John Smith', 'Full factory audit covering quality, compliance, and production processes', 0, 'pending', NOW()),

(@company_id, (SELECT id FROM supplier_profiles WHERE supplier_code='SUP-022'), 'AUD-2026-018', 'follow_up', 'planned', DATE_ADD(CURDATE(), INTERVAL 10 DAY),
 'John Smith', 'Follow-up audit for previously identified non-conformances', 0, 'pending', NOW()),

(@company_id, (SELECT id FROM supplier_profiles WHERE supplier_code='SUP-030'), 'AUD-2026-019', 'initial_qualification', 'planned', DATE_ADD(CURDATE(), INTERVAL 15 DAY),
 'Sarah Johnson', 'Initial qualification audit for new supplier onboarding', 0, 'pending', NOW()),

(@company_id, (SELECT id FROM supplier_profiles WHERE supplier_code='SUP-034'), 'AUD-2026-020', 'initial_qualification', 'planned', DATE_ADD(CURDATE(), INTERVAL 20 DAY),
 'Sarah Johnson', 'Initial qualification audit — Ethiopia facility', 0, 'pending', NOW());

-- In-progress audit (today)
INSERT IGNORE INTO supplier_audits (
    company_id, supplier_id, audit_code, audit_type, audit_status, audit_date,
    auditor_name, audit_scope, overall_score, audit_decision, created_at
) VALUES
(@company_id, (SELECT id FROM supplier_profiles WHERE supplier_code='SUP-019'), 'AUD-2026-021', 'surveillance', 'in_progress', CURDATE(),
 'John Smith', 'Surveillance audit — production quality and compliance', 0, 'pending', NOW());

-- Completed audits (recent past)
INSERT IGNORE INTO supplier_audits (
    company_id, supplier_id, audit_code, audit_type, audit_status, audit_date,
    auditor_name, audit_scope, overall_score, quality_score, compliance_score, production_score,
    total_checklist_items, passed_items, failed_items, na_items,
    major_ncr_count, minor_ncr_count, observation_count,
    audit_decision, decision_notes, executive_summary, strengths, weaknesses, created_at
) VALUES
(@company_id, (SELECT id FROM supplier_profiles WHERE supplier_code='SUP-017'), 'AUD-2026-010', 'routine', 'completed', DATE_SUB(CURDATE(), INTERVAL 30 DAY),
 'John Smith', 'Routine annual audit', 87.50, 90.00, 85.00, 88.00,
 85, 74, 8, 3, 2, 4, 2,
 'approved', 'Supplier meets all quality standards. Minor improvements recommended.',
 'Comprehensive audit covering all production areas. Strong quality management system in place.',
 'Excellent documentation, well-trained workforce, modern equipment', 'Some housekeeping issues in warehouse area, minor calibration gaps',
 NOW()),

(@company_id, (SELECT id FROM supplier_profiles WHERE supplier_code='SUP-020'), 'AUD-2026-011', 'routine', 'completed', DATE_SUB(CURDATE(), INTERVAL 25 DAY),
 'Sarah Johnson', 'Annual quality system audit', 91.30, 93.00, 90.00, 92.00,
 90, 83, 5, 2, 1, 3, 1,
 'approved', 'Outstanding performance. Supplier exceeds expectations.',
 'Top-tier supplier with excellent quality management and compliance culture.',
 'ISO-certified processes, proactive quality team, zero major NCRs in last 3 audits', 'Minor documentation gaps in chemical management section',
 NOW()),

(@company_id, (SELECT id FROM supplier_profiles WHERE supplier_code='SUP-019'), 'AUD-2026-012', 'follow_up', 'completed', DATE_SUB(CURDATE(), INTERVAL 20 DAY),
 'John Smith', 'Follow-up on previous NCRs', 68.20, 65.00, 70.00, 68.00,
 75, 51, 18, 6, 5, 8, 5,
 'conditional_approval', 'Significant improvement needed in production processes. 30-day corrective action plan required.',
 'Follow-up audit revealed persistent issues in cutting and finishing departments.',
 'Good management commitment, responsive to feedback', 'Persistent quality issues in finishing, inadequate training records, poor housekeeping',
 NOW()),

(@company_id, (SELECT id FROM supplier_profiles WHERE supplier_code='SUP-023'), 'AUD-2026-013', 'initial_qualification', 'completed', DATE_SUB(CURDATE(), INTERVAL 15 DAY),
 'Sarah Johnson', 'Initial qualification audit', 89.70, 91.00, 88.00, 90.00,
 88, 80, 6, 2, 1, 4, 1,
 'approved', 'New supplier meets all qualification criteria. Recommended for approved status.',
 'Well-equipped facility with strong production capabilities. Good compliance framework.',
 'Modern machinery, skilled workforce, strong safety culture', 'Minor gaps in environmental management documentation',
 NOW()),

(@company_id, (SELECT id FROM supplier_profiles WHERE supplier_code='SUP-025'), 'AUD-2026-014', 'routine', 'completed', DATE_SUB(CURDATE(), INTERVAL 10 DAY),
 'John Smith', 'Annual audit — fabric supplier', 93.40, 95.00, 92.00, 94.00,
 92, 87, 3, 2, 0, 2, 1,
 'approved', 'Exceptional supplier. Best-in-class quality management.',
 'Highest-scoring supplier in current audit cycle. Exceeds all quality benchmarks.',
 'Zero major NCRs, excellent calibration program, strong continuous improvement culture', 'Minor cosmetic issues in raw material storage area',
 NOW()),

(@company_id, (SELECT id FROM supplier_profiles WHERE supplier_code='SUP-027'), 'AUD-2026-015', 'technical', 'completed', DATE_SUB(CURDATE(), INTERVAL 8 DAY),
 'Sarah Johnson', 'Technical capability assessment', 95.20, 97.00, 94.00, 96.00,
 95, 91, 3, 1, 0, 2, 1,
 'approved', 'World-class technical capabilities. Exceeds industry standards.',
 'Exceptional technical infrastructure and process control. Reference facility.',
 'State-of-the-art equipment, advanced quality labs, Industry 4.0 implementation', 'Minor improvement opportunities in documentation digitalization',
 NOW()),

(@company_id, (SELECT id FROM supplier_profiles WHERE supplier_code='SUP-028'), 'AUD-2026-016', 'social_compliance', 'completed', DATE_SUB(CURDATE(), INTERVAL 5 DAY),
 'John Smith', 'Social compliance and labor audit', 65.30, 60.00, 70.00, 66.00,
 80, 52, 22, 6, 6, 10, 6,
 'rejected', 'Critical non-conformances found. Immediate corrective action required before re-audit.',
 'Multiple critical findings related to working hours, overtime records, and safety equipment.',
 'Cooperative management, basic safety equipment in place', 'Excessive overtime, missing overtime records, inadequate fire safety training, blocked emergency exits',
 NOW());


-- ============================================================
-- 7. AUDIT SECTIONS (for completed audits)
-- ============================================================
INSERT INTO supplier_audit_sections (
    audit_id, section_key, section_name, section_order, max_score, score, weighted_score,
    total_items, passed_items, failed_items, na_items, status, notes
)
SELECT sa.id, LOWER(REPLACE(REPLACE(sec.section_name, ' ', '_'), '&', 'and')), sec.section_name, sec.section_order, sec.max_score,
    ROUND(sec.max_score * (0.6 + RAND() * 0.4)),  -- Random score between 60-100% of max
    sec.max_score,
    sec.total_items,
    GREATEST(0, sec.total_items - FLOOR(RAND() * 4)),
    FLOOR(RAND() * 3),
    FLOOR(RAND() * 2),
    CASE WHEN RAND() > 0.3 THEN 'completed' ELSE 'in_progress' END,
    NULL
FROM supplier_audits sa
CROSS JOIN (
    SELECT 'Quality Management System' as section_name, 1 as section_order, 100 as max_score, 12 as total_items
    UNION ALL SELECT 'Production Process', 2, 100, 15
    UNION ALL SELECT 'Raw Material Management', 3, 100, 10
    UNION ALL SELECT 'In-Process Quality Control', 4, 100, 14
    UNION ALL SELECT 'Final Inspection & Testing', 5, 100, 12
    UNION ALL SELECT 'Facility & Equipment', 6, 100, 10
    UNION ALL SELECT 'Health & Safety', 7, 100, 11
    UNION ALL SELECT 'Environmental Management', 8, 100, 8
    UNION ALL SELECT 'Documentation & Records', 9, 100, 8
) sec
WHERE sa.audit_status = 'completed'
AND sa.audit_code LIKE 'AUD-2026-%'
AND NOT EXISTS (SELECT 1 FROM supplier_audit_sections sas WHERE sas.audit_id = sa.id);


-- ============================================================
-- 8. CHECKLIST ITEMS (for each section)
-- ============================================================
INSERT INTO supplier_audit_checklists (
    audit_id, section_id, question, item_order, status, answer_score, max_score, finding
)
SELECT sas.audit_id, sas.id, cli.item_name, cli.item_order,
    CASE
        WHEN RAND() > 0.15 THEN 'pass'
        WHEN RAND() > 0.5 THEN 'fail'
        ELSE 'na'
    END,
    CASE
        WHEN RAND() > 0.15 THEN sas.max_score / sas.total_items
        WHEN RAND() > 0.5 THEN 0
        ELSE NULL
    END,
    sas.max_score / sas.total_items,
    NULL
FROM supplier_audit_sections sas
CROSS JOIN (
    SELECT 'QMS-01: Quality policy documented' as item_name, 1 as item_order
    UNION ALL SELECT 'QMS-02: Management review records', 2
    UNION ALL SELECT 'QMS-03: Internal audit program', 3
    UNION ALL SELECT 'QMS-04: Corrective action process', 4
    UNION ALL SELECT 'QMS-05: Document control system', 5
    UNION ALL SELECT 'PRD-01: Production planning process', 1
    UNION ALL SELECT 'PRD-02: Work instructions available', 2
    UNION ALL SELECT 'PRD-03: Line balancing adequate', 3
    UNION ALL SELECT 'PRD-04: Sample approval process', 4
    UNION ALL SELECT 'PRD-05: Production tracking system', 5
    UNION ALL SELECT 'PRD-06: Change management process', 6
    UNION ALL SELECT 'RML-01: Incoming material inspection', 1
    UNION ALL SELECT 'RML-02: Supplier evaluation process', 2
    UNION ALL SELECT 'RML-03: Material storage conditions', 3
    UNION ALL SELECT 'RML-04: FIFO system implemented', 4
    UNION ALL SELECT 'IPC-01: In-line inspection coverage', 1
    UNION ALL SELECT 'IPC-02: Defect tracking system', 2
    UNION ALL SELECT 'IPC-03: Measurement tools calibrated', 3
    UNION ALL SELECT 'IPC-04: AQL standards followed', 4
    UNION ALL SELECT 'FIN-01: Final inspection procedure', 1
    UNION ALL SELECT 'FIN-02: Packaging standards met', 2
    UNION ALL SELECT 'FIN-03: Labeling accuracy', 3
    UNION ALL SELECT 'FAC-01: Equipment maintenance schedule', 1
    UNION ALL SELECT 'FAC-02: Machinery in good condition', 2
    UNION ALL SELECT 'FAC-03: Backup power available', 3
    UNION ALL SELECT 'HSE-01: Fire extinguishers accessible', 1
    UNION ALL SELECT 'HSE-02: Emergency exits clear', 2
    UNION ALL SELECT 'HSE-03: PPE provided and used', 3
    UNION ALL SELECT 'HSE-04: First aid kits stocked', 4
    UNION ALL SELECT 'ENV-01: Waste management system', 1
    UNION ALL SELECT 'ENV-02: Water treatment (if applicable)', 2
    UNION ALL SELECT 'ENV-03: Chemical storage compliant', 3
    UNION ALL SELECT 'DOC-01: Records maintained per retention policy', 1
    UNION ALL SELECT 'DOC-02: Training records current', 2
    UNION ALL SELECT 'DOC-03: Calibration certificates available', 3
) cli
JOIN supplier_audits sa ON sa.id = sas.audit_id
WHERE sa.audit_code LIKE 'AUD-2026-%'
AND NOT EXISTS (SELECT 1 FROM supplier_audit_checklists sac WHERE sac.audit_id = sas.audit_id AND sac.section_id = sas.id)
LIMIT 500;


-- ============================================================
-- 9. NCRs (Non-Conformance Reports)
-- ============================================================
INSERT INTO supplier_audit_ncr (
    audit_id, ncr_code, title, description, severity, status, category,
    root_cause, corrective_action, target_date, created_at
)
SELECT sa.id,
    CONCAT('NCR-2026-', LPAD(@rownum := @rownum + 1, 3, '0')),
    ncr.title, ncr.description, ncr.severity, ncr.status, 'quality',
    ncr.root_cause, ncr.corrective_action,
    DATE_ADD(sa.audit_date, INTERVAL ncr.days_to_target DAY),
    sa.created_at
FROM supplier_audits sa
CROSS JOIN (SELECT @rownum := 44) r
CROSS JOIN (
    SELECT 'Cutting defects exceeding AQL' as title, 'Multiple cutting defects found during final inspection. Defect rate 3.2% vs 2.5% AQL.' as description, 'major' as severity, 'corrective_action' as status, 'Inadequate cutting blade maintenance and operator training' as root_cause, 'Increased inspection frequency on cutting section' as containment_action, 'Implement weekly blade replacement schedule and operator retraining' as corrective_action, 30 as days_to_target
    UNION ALL SELECT 'Missing calibration certificates', '3 measuring instruments found without valid calibration certificates.', 'major', 'root_cause', 'Calibration tracking system not maintained', 'Instruments quarantined pending calibration', 'Implement digital calibration tracking system', 14
    UNION ALL SELECT 'Blocked emergency exit', 'Emergency exit in production floor blocked by stored materials.', 'critical', 'open', 'Insufficient storage space and poor housekeeping', 'Exit cleared immediately', 'Reorganize storage area, install signage, conduct housekeeping training', 7
    UNION ALL SELECT 'Excessive overtime records', 'Workers recorded 72+ hour weeks for 3 consecutive weeks.', 'critical', 'containment', 'Production planning deficiencies', 'Temporarily reduced order intake', 'Hire additional staff, improve production planning, implement overtime approval process', 21
    UNION ALL SELECT 'PPE not worn by operators', 'Multiple operators observed without safety goggles in cutting section.', 'major', 'corrective_action', 'Lack of enforcement and training', 'Verbal warning issued to supervisors', 'Install PPE compliance monitoring, mandatory safety training', 14
    UNION ALL SELECT 'Chemical labeling incomplete', '3 chemical containers in dyeing section missing proper hazard labels.', 'major', 'closed', 'Labeling supplies ran out', 'Labels replaced immediately', 'Implement chemical inventory management with automatic label reorder', 21
    UNION ALL SELECT 'Raw material storage temperature', 'Fabric storage area temperature exceeded recommended range.', 'minor', 'closed', 'HVAC system undersized for area', 'Portable cooling units deployed', 'Upgrade HVAC system for storage area', 30
    UNION ALL SELECT 'Document version control', 'Old revision work instructions found at 2 workstation stations.', 'minor', 'corrective_action', 'Document distribution process gaps', 'Old documents collected and destroyed', 'Implement digital document management system', 45
) ncr
WHERE sa.audit_code LIKE 'AUD-2026-%'
AND sa.audit_status = 'completed'
AND NOT EXISTS (SELECT 1 FROM supplier_audit_ncr n WHERE n.audit_id = sa.id);


-- ============================================================
-- 10. CAPAs (Corrective/Preventive Actions)
-- ============================================================
INSERT INTO supplier_audit_capa (
    audit_id, ncr_id, capa_code, capa_type, title, description,
    responsible_person, target_date, status, root_cause,
    action_plan, created_at
)
SELECT ncr.audit_id, ncr.id,
    CONCAT('CAPA-2026-', LPAD(@rownum2 := @rownum2 + 1, 3, '0')),
    CASE WHEN ncr.severity IN ('critical', 'major') THEN 'corrective' ELSE 'preventive' END,
    CONCAT('Action for: ', ncr.title),
    CONCAT('Addressing NCR: ', ncr.description),
    CASE WHEN RAND() > 0.5 THEN 'John Smith' ELSE 'Sarah Johnson' END,
    DATE_ADD(CURDATE(), INTERVAL FLOOR(10 + RAND() * 50) DAY),
    CASE
        WHEN ncr.status = 'closed' THEN 'completed'
        WHEN RAND() > 0.7 THEN 'in_progress'
        WHEN RAND() > 0.4 THEN 'pending_verification'
        ELSE 'open'
    END,
    ncr.root_cause,
    ncr.corrective_action,
    ncr.created_at
FROM supplier_audit_ncr ncr
CROSS JOIN (SELECT @rownum2 := 34) r
WHERE ncr.audit_id IN (SELECT id FROM supplier_audits WHERE audit_code LIKE 'AUD-2026-%' AND audit_status = 'completed')
AND NOT EXISTS (SELECT 1 FROM supplier_audit_capa c WHERE c.ncr_id = ncr.id);


-- ============================================================
-- 11. AUDIT IMAGES
-- ============================================================
INSERT INTO supplier_audit_images (
    audit_id, file_name, file_path, image_type, caption, created_at
)
SELECT sa.id,
    CONCAT('audit_', sa.id, '_', FLOOR(RAND() * 1000), '.jpg'),
    CONCAT('/uploads/supplier-audit-images/audit_', sa.id, '.jpg'),
    ELT(FLOOR(1 + RAND() * 5), 'factory_entrance', 'production_floor', 'warehouse', 'laboratory', 'office'),
    ELT(FLOOR(1 + RAND() * 5), 'Factory entrance view', 'Production floor overview', 'Raw material warehouse', 'Quality testing lab', 'Office area'),
    sa.created_at
FROM supplier_audits sa
CROSS JOIN (SELECT 1 UNION SELECT 2 UNION SELECT 3) t
WHERE sa.audit_code LIKE 'AUD-2026-%'
AND sa.audit_status = 'completed'
AND NOT EXISTS (SELECT 1 FROM supplier_audit_images sai WHERE sai.audit_id = sa.id);


-- ============================================================
-- 12. AUDIT APPROVALS
-- ============================================================
INSERT INTO supplier_audit_approvals (
    audit_id, approver_name, approver_role, status, comments, approved_at, created_at
)
SELECT sa.id,
    CASE
        WHEN seq.level = 1 THEN 'Quality Manager'
        WHEN seq.level = 2 THEN 'Director'
        ELSE 'Final Reviewer'
    END,
    CASE
        WHEN seq.level = 1 THEN 'quality_manager'
        WHEN seq.level = 2 THEN 'director'
        ELSE 'reviewer'
    END,
    CASE
        WHEN sa.audit_decision = 'approved' THEN 'approved'
        WHEN sa.audit_decision = 'rejected' THEN 'rejected'
        WHEN seq.level <= 2 THEN 'approved'
        ELSE 'pending'
    END,
    CASE
        WHEN sa.audit_decision = 'approved' THEN 'Reviewed and approved'
        WHEN sa.audit_decision = 'rejected' THEN 'Does not meet requirements'
        ELSE NULL
    END,
    CASE WHEN seq.level <= 2 OR sa.audit_decision IN ('approved','rejected') THEN sa.updated_at ELSE NULL END,
    sa.created_at
FROM supplier_audits sa
CROSS JOIN (SELECT 1 as level UNION SELECT 2 UNION SELECT 3) seq
WHERE sa.audit_code LIKE 'AUD-2026-%'
AND sa.audit_status = 'completed'
AND NOT EXISTS (SELECT 1 FROM supplier_audit_approvals saa WHERE saa.audit_id = sa.id);


-- ============================================================
-- 13. RISK PROFILES
-- ============================================================
INSERT INTO supplier_risk_profiles (
    supplier_id, assessment_date, overall_risk, quality_risk, delivery_risk,
    compliance_risk, financial_risk, assessed_by, created_at
)
SELECT sp.id,
    COALESCE(sp.last_audit_date, CURDATE()),
    CASE
        WHEN sp.overall_score >= 90 THEN 'low'
        WHEN sp.overall_score >= 75 THEN 'medium'
        WHEN sp.overall_score >= 50 THEN 'high'
        WHEN sp.overall_score > 0 THEN 'critical'
        ELSE 'medium'
    END,
    CASE WHEN RAND() > 0.5 THEN 'low' ELSE 'medium' END,
    CASE WHEN RAND() > 0.5 THEN 'low' ELSE 'medium' END,
    CASE WHEN RAND() > 0.5 THEN 'low' ELSE 'medium' END,
    CASE WHEN RAND() > 0.5 THEN 'low' ELSE 'medium' END,
    'System',
    NOW()
FROM supplier_profiles sp
WHERE NOT EXISTS (SELECT 1 FROM supplier_risk_profiles srp WHERE srp.supplier_id = sp.id);


-- ============================================================
-- 14. PERFORMANCE HISTORY
-- ============================================================
INSERT INTO supplier_performance_history (
    supplier_id, period_start, period_end, quality_pass_rate, overall_score, rating, notes, created_at
)
SELECT sa.supplier_id, sa.audit_date, sa.audit_date,
    CASE WHEN sa.passed_items > 0 THEN ROUND(sa.passed_items * 100.0 / GREATEST(sa.total_checklist_items, 1), 2) ELSE 0 END,
    sa.overall_score,
    CASE WHEN sa.overall_score >= 90 THEN 'A' WHEN sa.overall_score >= 75 THEN 'B' WHEN sa.overall_score >= 60 THEN 'C' ELSE 'D' END,
    CONCAT('Audit ', sa.audit_code, ' — ', sa.audit_decision),
    NOW()
FROM supplier_audits sa
WHERE sa.audit_status = 'completed'
AND sa.audit_code LIKE 'AUD-2026-%'
AND NOT EXISTS (
    SELECT 1 FROM supplier_performance_history sph
    WHERE sph.supplier_id = sa.supplier_id AND sph.period_start = sa.audit_date
);


-- ============================================================
-- 15. CAPA TIMELINE (for some CAPAs)
-- ============================================================
INSERT INTO supplier_capa_timeline (capa_id, action_type, action_by, action_notes, new_status, created_at)
SELECT c.id, 'viewed', 'System', 'CAPA viewed by supplier portal', c.status, c.created_at
FROM supplier_audit_capa c
WHERE c.capa_code LIKE 'CAPA-2026-%'
AND RAND() > 0.5
AND NOT EXISTS (SELECT 1 FROM supplier_capa_timeline ct WHERE ct.capa_id = c.id);


-- ============================================================
-- DONE
-- ============================================================
SELECT 'Seed complete!' as status,
    (SELECT COUNT(*) FROM supplier_profiles) as total_suppliers,
    (SELECT COUNT(*) FROM supplier_audits) as total_audits,
    (SELECT COUNT(*) FROM supplier_audit_ncr) as total_ncrs,
    (SELECT COUNT(*) FROM supplier_audit_capa) as total_capas,
    (SELECT COUNT(*) FROM supplier_portal_users) as portal_users,
    (SELECT COUNT(*) FROM supplier_portal_invites) as portal_invites;
