-- ============================================================
-- SEED DATA: ASN Digital Shipment Passport + GRN + Vendor Scorecards
-- ============================================================

-- ========================================
-- 1. ASN #2 — DRAFT (just created, not yet shipped)
-- ========================================
INSERT IGNORE INTO asn (id, company_id, asn_no, po_id, supplier_id, warehouse_id,
  ship_date, eta_date, vehicle_no, challan_no, invoice_no,
  total_boxes, total_rolls, total_pallets, total_cartons,
  total_weight_kg, total_volume_cbm, total_qty,
  gate_entry_completed, ai_readiness_score, ai_notes, created_by, status, notes) VALUES
(4, 1, 'ASN-2026-0002', 2, 4, 1,
  '2026-07-28', '2026-07-30', 'MH12DE3456', 'CH-2026-0891', 'INV-2026-789',
  0, 0, 0, 0,
  0, 0, 0,
  0, NULL, NULL, 1, 'draft', 'Waiting for supplier confirmation. Ready to ship.');

-- ASN #3 — IN_TRANSIT (shipped, ETA soon)
INSERT IGNORE INTO asn (id, company_id, asn_no, po_id, supplier_id, warehouse_id,
  ship_date, eta_date, vehicle_no, challan_no, invoice_no,
  container_no, seal_no, driver_name, driver_mobile, transporter_name,
  shipping_mode, shipping_marks, total_boxes, total_rolls, total_pallets, total_cartons,
  total_weight_kg, total_volume_cbm, total_qty,
  gate_entry_completed, ai_readiness_score, ai_notes, created_by, status, notes) VALUES
(5, 1, 'ASN-2026-0003', 1, 2, 2,
  '2026-07-18', '2026-07-22', 'DL01AB1234', 'CH-2026-0654', 'INV-2026-567',
  'MSKU7654321', 'SL-98765', 'Rajesh Kumar', '+919876543211', 'BlueStar Transport',
  'road', 'HANDLE WITH CARE', 45, 0, 4, 45,
  8500.00, 28.500, 15000.0000,
  0, 65.00, '{"readiness_score":65,"vehicle_loading":"Good","traffic_risk":"MEDIUM","late_delivery_risk":"LOW","suggested_dock":"Dock-1","estimated_unloading_min":50,"warehouse_capacity":"Available","recommendation":"Proceed with Standard Protocol","checks_passed":4,"checks_total":5}', 1, 'in_transit', 'Shipment picked up from supplier warehouse in Mumbai.');

-- ASN #4 — ARRIVED (fully processed through gate entry)
INSERT IGNORE INTO asn (id, company_id, asn_no, po_id, supplier_id, warehouse_id,
  ship_date, eta_date, actual_arrival, vehicle_no, challan_no, invoice_no,
  container_no, seal_no, driver_name, driver_mobile, transporter_name,
  shipping_mode, shipping_marks, total_boxes, total_rolls, total_pallets, total_cartons,
  total_weight_kg, total_volume_cbm, total_qty,
  gate_entry_completed, gate_entry_at, dock_no, estimated_unloading_min,
  ai_readiness_score, ai_notes, created_by, status, notes) VALUES
(6, 1, 'ASN-2026-0004', 1, 5, 3,
  '2026-07-15', '2026-07-18', '2026-07-18 09:30:00', 'GJ05CD7890', 'CH-2026-0321', 'INV-2026-234',
  'CMAU1234567', 'SL-54321', 'Mohammad Ali', '+919876543212', 'SwiftCargo Movers',
  'road', 'KEEP DRY - NO STACKING', 60, 0, 5, 60,
  12000.00, 38.400, 20000.0000,
  1, '2026-07-18 10:15:00', 'Dock-2', 45,
  92.00, '{"readiness_score":92,"vehicle_loading":"Excellent","traffic_risk":"LOW","late_delivery_risk":"NONE","suggested_dock":"Dock-2","estimated_unloading_min":45,"warehouse_capacity":"Available","recommendation":"Proceed with Standard Protocol","checks_passed":5,"checks_total":5}', 1, 'arrived', 'Full container load. All documents verified at gate.');

-- ASN #5 — RECEIVED (fully received + GRN created)
INSERT IGNORE INTO asn (id, company_id, asn_no, po_id, supplier_id, warehouse_id,
  ship_date, eta_date, actual_arrival, vehicle_no, challan_no, invoice_no,
  container_no, seal_no, driver_name, driver_mobile, transporter_name,
  shipping_mode, shipping_marks, total_boxes, total_rolls, total_pallets, total_cartons,
  total_weight_kg, total_volume_cbm, total_qty,
  gate_entry_completed, gate_entry_at, dock_no, estimated_unloading_min,
  ai_readiness_score, ai_notes, created_by, status, notes) VALUES
(7, 1, 'ASN-2026-0005', 2, 1, 1,
  '2026-07-10', '2026-07-13', '2026-07-13 08:00:00', 'KA01EF5678', 'CH-2026-0100', 'INV-2026-101',
  'TCNU8765432', 'SL-11223', 'Sunil Sharma', '+919876543213', 'National Logistics',
  'road', 'PO#2 - COTTON FABRIC', 40, 0, 3, 40,
  9800.00, 32.000, 16000.0000,
  1, '2026-07-13 09:00:00', 'Dock-1', 40,
  95.00, '{"readiness_score":95,"vehicle_loading":"Excellent","traffic_risk":"LOW","late_delivery_risk":"NONE","suggested_dock":"Dock-1","estimated_unloading_min":40,"warehouse_capacity":"Available","recommendation":"Proceed with Standard Protocol","checks_passed":5,"checks_total":5}', 1, 'received', 'Fabric received in excellent condition. GRN completed.');

-- ========================================
-- 2. ASN ITEMS
-- ========================================

-- ASN-2026-0003 (In Transit)
INSERT IGNORE INTO asn_items (asn_id, po_item_id, item_id, color, size, lot_no, batch_no, uom_id, shipped_qty, received_qty, boxes, rolls, notes) VALUES
(5, NULL, 1, 'Black', 'S', 'LOT-301', 'BAT-001', 5, 3000.0000, NULL, 8, 0, 'Cotton Jersey 180gsm - Size S'),
(5, NULL, 1, 'Black', 'M', 'LOT-302', 'BAT-001', 5, 4000.0000, NULL, 10, 0, 'Cotton Jersey 180gsm - Size M'),
(5, NULL, 1, 'Black', 'L', 'LOT-303', 'BAT-001', 5, 4000.0000, NULL, 10, 0, 'Cotton Jersey 180gsm - Size L'),
(5, NULL, 1, 'Black', 'XL', 'LOT-304', 'BAT-001', 5, 4000.0000, NULL, 10, 0, 'Cotton Jersey 180gsm - Size XL'),
(5, NULL, 3, 'White', NULL, 'LOT-305', 'BAT-002', 1, 10000.0000, NULL, 7, 0, 'Sewing Thread - Polyester');

-- ASN-2026-0004 (Arrived)
INSERT IGNORE INTO asn_items (asn_id, po_item_id, item_id, color, size, lot_no, batch_no, uom_id, shipped_qty, received_qty, boxes, rolls, notes) VALUES
(6, NULL, 1, 'Black', 'S', 'LOT-201', 'BAT-010', 5, 2500.0000, NULL, 6, 0, 'Cotton Jersey 180gsm - Size S'),
(6, NULL, 1, 'Black', 'M', 'LOT-202', 'BAT-010', 5, 3000.0000, NULL, 8, 0, 'Cotton Jersey 180gsm - Size M'),
(6, NULL, 1, 'Black', 'L', 'LOT-203', 'BAT-010', 5, 3500.0000, NULL, 8, 0, 'Cotton Jersey 180gsm - Size L'),
(6, NULL, 1, 'Black', 'XL', 'LOT-204', 'BAT-010', 5, 3500.0000, NULL, 8, 0, 'Cotton Jersey 180gsm - Size XL'),
(6, NULL, 2, 'Black', NULL, 'LOT-205', 'BAT-011', 5, 4000.0000, NULL, 10, 0, 'Rib Collar 1x1'),
(6, NULL, 3, 'Black', NULL, 'LOT-206', 'BAT-012', 1, 3500.0000, NULL, 20, 0, 'Sewing Thread');

-- ASN-2026-0005 (Received)
INSERT IGNORE INTO asn_items (asn_id, po_item_id, item_id, color, size, lot_no, batch_no, uom_id, shipped_qty, received_qty, boxes, rolls, notes) VALUES
(7, NULL, 1, 'Black', 'S', 'LOT-101', 'BAT-100', 5, 2000.0000, 2000.0000, 5, 0, 'Cotton Jersey - Size S - Received OK'),
(7, NULL, 1, 'Black', 'M', 'LOT-102', 'BAT-100', 5, 2000.0000, 2000.0000, 5, 0, 'Cotton Jersey - Size M - Received OK'),
(7, NULL, 1, 'Black', 'L', 'LOT-103', 'BAT-100', 5, 2000.0000, 2000.0000, 5, 0, 'Cotton Jersey - Size L - Received OK'),
(7, NULL, 1, 'Black', 'XL', 'LOT-104', 'BAT-100', 5, 2000.0000, 2000.0000, 5, 0, 'Cotton Jersey - Size XL - Received OK'),
(7, NULL, 2, 'Black', NULL, 'LOT-105', 'BAT-101', 5, 3000.0000, 3000.0000, 8, 0, 'Rib Collar - Received OK'),
(7, NULL, 3, 'Black', NULL, 'LOT-106', 'BAT-102', 1, 5000.0000, 5000.0000, 12, 0, 'Sewing Thread - Received OK');

-- ========================================
-- 3. ASN DOCUMENTS
-- ========================================

-- ASN-2026-0003 (In Transit)
INSERT IGNORE INTO asn_documents (asn_id, file_name, file_path, file_type, file_size, category, uploaded_by) VALUES
(5, 'INV-2026-567.pdf', '/uploads/asn-documents/INV-2026-567.pdf', 'application/pdf', 245000, 'commercial_invoice', 1),
(5, 'PL-2026-567.pdf', '/uploads/asn-documents/PL-2026-567.pdf', 'application/pdf', 128000, 'packing_list', 1),
(5, 'CH-2026-0654.pdf', '/uploads/asn-documents/CH-2026-0654.pdf', 'application/pdf', 89000, 'transport_lr', 1),
(5, 'QC-2026-101.pdf', '/uploads/asn-documents/QC-2026-101.pdf', 'application/pdf', 312000, 'test_certificate', 1);

-- ASN-2026-0004 (Arrived)
INSERT IGNORE INTO asn_documents (asn_id, file_name, file_path, file_type, file_size, category, uploaded_by) VALUES
(6, 'INV-2026-234.pdf', '/uploads/asn-documents/INV-2026-234.pdf', 'application/pdf', 267000, 'commercial_invoice', 1),
(6, 'PL-2026-234.pdf', '/uploads/asn-documents/PL-2026-234.pdf', 'application/pdf', 145000, 'packing_list', 1),
(6, 'CH-2026-0321.pdf', '/uploads/asn-documents/CH-2026-0321.pdf', 'application/pdf', 92000, 'transport_lr', 1),
(6, 'QC-2026-050.pdf', '/uploads/asn-documents/QC-2026-050.pdf', 'application/pdf', 298000, 'test_certificate', 1),
(6, 'CERT-2026-012.pdf', '/uploads/asn-documents/CERT-2026-012.pdf', 'application/pdf', 185000, 'certificate_of_origin', 1);

-- ASN-2026-0005 (Received)
INSERT IGNORE INTO asn_documents (asn_id, file_name, file_path, file_type, file_size, category, uploaded_by) VALUES
(7, 'INV-2026-101.pdf', '/uploads/asn-documents/INV-2026-101.pdf', 'application/pdf', 234000, 'commercial_invoice', 1),
(7, 'PL-2026-101.pdf', '/uploads/asn-documents/PL-2026-101.pdf', 'application/pdf', 156000, 'packing_list', 1),
(7, 'CH-2026-0100.pdf', '/uploads/asn-documents/CH-2026-0100.pdf', 'application/pdf', 78000, 'transport_lr', 1),
(7, 'QC-2026-020.pdf', '/uploads/asn-documents/QC-2026-020.pdf', 'application/pdf', 342000, 'test_certificate', 1),
(7, 'CERT-2026-008.pdf', '/uploads/asn-documents/CERT-2026-008.pdf', 'application/pdf', 175000, 'certificate_of_origin', 1);

-- ========================================
-- 4. ASN GATE ENTRIES
-- ========================================

-- ASN-2026-0004 (Arrived)
INSERT IGNORE INTO asn_gate_entry (asn_id, company_id, gate_entry_no, entry_time,
  vehicle_verified, seal_verified, documents_verified, invoice_verified, packing_list_verified,
  po_matched, quantity_verified,
  truck_photo, driver_photo, container_photo, seal_photo,
  gate_by, security_by, warehouse_by, notes, status) VALUES
(6, 1, 'GE-2026-0002', '2026-07-18 10:15:00',
  1, 1, 1, 1, 1, 1, 1,
  '/uploads/gate-photos/GE2-truck.jpg', '/uploads/gate-photos/GE2-driver.jpg', '/uploads/gate-photos/GE2-container.jpg', '/uploads/gate-photos/GE2-seal.jpg',
  1, 1, 1, 'All checks passed. Container seal intact. Driver ID verified.', 'completed');

-- ASN-2026-0005 (Received)
INSERT IGNORE INTO asn_gate_entry (asn_id, company_id, gate_entry_no, entry_time,
  vehicle_verified, seal_verified, documents_verified, invoice_verified, packing_list_verified,
  po_matched, quantity_verified,
  truck_photo, driver_photo, container_photo, seal_photo,
  gate_by, security_by, warehouse_by, notes, status) VALUES
(7, 1, 'GE-2026-0003', '2026-07-13 09:00:00',
  1, 1, 1, 1, 1, 1, 1,
  '/uploads/gate-photos/GE3-truck.jpg', '/uploads/gate-photos/GE3-driver.jpg', '/uploads/gate-photos/GE3-container.jpg', '/uploads/gate-photos/GE3-seal.jpg',
  1, 1, 1, 'Full container. All documentation verified. Driver released.', 'completed');

-- ========================================
-- 5. ASN TIMELINE EVENTS
-- ========================================

-- ASN-2026-0003 (In Transit)
INSERT IGNORE INTO asn_timeline (asn_id, event_type, event_label, event_details, performed_by, event_at) VALUES
(5, 'created', 'ASN ASN-2026-0003 created', '{"po_id":1,"supplier_id":2}', 1, '2026-07-17 09:00:00'),
(5, 'shipped', 'Shipment picked up from Mumbai warehouse', '{"vehicle":"DL01AB1234","driver":"Rajesh Kumar"}', 1, '2026-07-18 10:00:00'),
(5, 'in_transit', 'Shipment departed Mumbai distribution center', '{"location":"Mumbai, MH"}', 1, '2026-07-18 14:00:00'),
(5, 'in_transit', 'Shipment crossed Nagpur checkpoint', '{"location":"Nagpur, MH","eta_update":"Jul 22 2pm"}', 1, '2026-07-19 22:00:00'),
(5, 'in_transit', 'Shipment at Raipur rest stop', '{"location":"Raipur, CG","eta_update":"Jul 22 3pm"}', 1, '2026-07-20 18:00:00');

-- ASN-2026-0004 (Arrived)
INSERT IGNORE INTO asn_timeline (asn_id, event_type, event_label, event_details, performed_by, event_at) VALUES
(6, 'created', 'ASN ASN-2026-0004 created', '{"po_id":1,"supplier_id":5}', 1, '2026-07-14 09:00:00'),
(6, 'shipped', 'Shipment picked up from Gujarat warehouse', '{"vehicle":"GJ05CD7890","driver":"Mohammad Ali"}', 1, '2026-07-15 06:00:00'),
(6, 'in_transit', 'Shipment departed Ahmedabad', '{"location":"Ahmedabad, GJ"}', 1, '2026-07-15 10:00:00'),
(6, 'arrived', 'Shipment arrived at warehouse Dock-2', '{"arrival_time":"2026-07-18 09:30:00"}', 1, '2026-07-18 09:30:00'),
(6, 'gate_entry', 'Gate entry GE-2026-0002 completed - all checks passed', '{"vehicle":1,"seal":1,"documents":1,"po_match":1,"qty":1}', 1, '2026-07-18 10:15:00'),
(6, 'ai_analysis', 'AI analysis: 92% readiness - Excellent', '{"readiness_score":92,"recommendation":"Proceed with Standard Protocol"}', 1, '2026-07-18 10:16:00');

-- ASN-2026-0005 (Received)
INSERT IGNORE INTO asn_timeline (asn_id, event_type, event_label, event_details, performed_by, event_at) VALUES
(7, 'created', 'ASN ASN-2026-0005 created', '{"po_id":2,"supplier_id":1}', 1, '2026-07-09 10:00:00'),
(7, 'shipped', 'Shipment picked up from Bangalore warehouse', '{"vehicle":"KA01EF5678","driver":"Sunil Sharma"}', 1, '2026-07-10 06:00:00'),
(7, 'in_transit', 'Shipment departed Bangalore', '{"location":"Bangalore, KA"}', 1, '2026-07-10 10:00:00'),
(7, 'arrived', 'Shipment arrived at warehouse Dock-1', '{"arrival_time":"2026-07-13 08:00:00"}', 1, '2026-07-13 08:00:00'),
(7, 'gate_entry', 'Gate entry GE-2026-0003 completed - all checks passed', '{"vehicle":1,"seal":1,"documents":1,"po_match":1,"qty":1}', 1, '2026-07-13 09:00:00'),
(7, 'ai_analysis', 'AI analysis: 95% readiness - Excellent', '{"readiness_score":95,"recommendation":"Proceed with Standard Protocol"}', 1, '2026-07-13 09:05:00'),
(7, 'grn_created', 'GRN GRN-2026-0002 created from ASN', '{"grn_id":2}', 1, '2026-07-13 10:00:00'),
(7, 'received', 'All items verified and put away to warehouse', '{"warehouse_id":3,"items_received":6}', 1, '2026-07-13 14:00:00');

-- ========================================
-- 6. GRN #2 (from ASN-2026-0005)
-- ========================================
INSERT IGNORE INTO grn (id, company_id, grn_no, po_id, supplier_id, warehouse_id, received_by,
  received_date, invoice_no, invoice_date, vehicle_no, challan_no,
  total_qty, qc_status, status, notes) VALUES
(2, 1, 'GRN-2026-0002', 2, 1, 3, 1,
  '2026-07-13', 'INV-2026-101', '2026-07-10', 'KA01EF5678', 'CH-2026-0100',
  16000.0000, 'passed', 'qc_passed', 'GRN from ASN-2026-0005. All items QC passed.');

-- ========================================
-- 7. GRN ITEMS (for GRN-2)
-- ========================================
INSERT IGNORE INTO grn_items (grn_id, po_item_id, item_id, color, size, lot_no, batch_no, uom_id,
  received_qty, accepted_qty, rejected_qty, location_id, qc_status, notes) VALUES
(2, NULL, 1, 'Black', 'S', 'LOT-101', 'BAT-100', 5, 2000.0000, 2000.0000, 0, 1, 'passed', 'Size S - Accepted'),
(2, NULL, 1, 'Black', 'M', 'LOT-102', 'BAT-100', 5, 2000.0000, 2000.0000, 0, 1, 'passed', 'Size M - Accepted'),
(2, NULL, 1, 'Black', 'L', 'LOT-103', 'BAT-100', 5, 2000.0000, 2000.0000, 0, 1, 'passed', 'Size L - Accepted'),
(2, NULL, 1, 'Black', 'XL', 'LOT-104', 'BAT-100', 5, 2000.0000, 1980.0000, 20, 1, 'passed', 'Size XL - 20 pcs rejected (stain)'),
(2, NULL, 2, 'Black', NULL, 'LOT-105', 'BAT-101', 5, 3000.0000, 3000.0000, 0, 1, 'passed', 'Rib collar - All accepted'),
(2, NULL, 3, 'Black', NULL, 'LOT-106', 'BAT-102', 1, 5000.0000, 5000.0000, 0, 1, 'passed', 'Thread - All accepted');

-- ========================================
-- 8. VENDOR SCORECARDS (all 7 suppliers)
-- ========================================

DELETE FROM vendor_scorecards WHERE company_id = 1;

-- Supplier 1 — Fabric supplier (best performer)
INSERT IGNORE INTO vendor_scorecards (company_id, supplier_id, period_start, period_end,
  total_orders, on_time_deliveries, quality_pass_rate, avg_lead_time_days,
  price_competitiveness, response_rate, overall_score, rating, notes, status) VALUES
(1, 1, '2026-01-01', '2026-06-30',
  12, 11, 95.50, 6,
  82.00, 98.00, 89.42, 'A', 'Top performer. Consistent quality and timely delivery.', 'active');

-- Supplier 2 — Trim supplier
INSERT IGNORE INTO vendor_scorecards (company_id, supplier_id, period_start, period_end,
  total_orders, on_time_deliveries, quality_pass_rate, avg_lead_time_days,
  price_competitiveness, response_rate, overall_score, rating, notes, status) VALUES
(1, 2, '2026-01-01', '2026-06-30',
  8, 6, 92.00, 8,
  75.00, 85.00, 83.06, 'B', 'Good quality but occasional delays on bulk orders.', 'active');

-- Supplier 3 — Accessory supplier
INSERT IGNORE INTO vendor_scorecards (company_id, supplier_id, period_start, period_end,
  total_orders, on_time_deliveries, quality_pass_rate, avg_lead_time_days,
  price_competitiveness, response_rate, overall_score, rating, notes, status) VALUES
(1, 3, '2026-01-01', '2026-06-30',
  15, 14, 97.00, 4,
  88.00, 95.00, 93.60, 'A', 'Excellent labels and accessories. Very responsive.', 'active');

-- Supplier 4 — Packaging supplier
INSERT IGNORE INTO vendor_scorecards (company_id, supplier_id, period_start, period_end,
  total_orders, on_time_deliveries, quality_pass_rate, avg_lead_time_days,
  price_competitiveness, response_rate, overall_score, rating, notes, status) VALUES
(1, 4, '2026-01-01', '2026-06-30',
  6, 4, 85.00, 10,
  90.00, 70.00, 76.40, 'C', 'Competitive pricing but slow response times.', 'active');

-- Supplier 5 — Chemical supplier
INSERT IGNORE INTO vendor_scorecards (company_id, supplier_id, period_start, period_end,
  total_orders, on_time_deliveries, quality_pass_rate, avg_lead_time_days,
  price_competitiveness, response_rate, overall_score, rating, notes, status) VALUES
(1, 5, '2026-01-01', '2026-06-30',
  10, 9, 93.00, 5,
  78.00, 90.00, 86.20, 'B', 'Reliable chemical supplier. Good technical support.', 'active');

-- Supplier 6 — Logistics provider
INSERT IGNORE INTO vendor_scorecards (company_id, supplier_id, period_start, period_end,
  total_orders, on_time_deliveries, quality_pass_rate, avg_lead_time_days,
  price_competitiveness, response_rate, overall_score, rating, notes, status) VALUES
(1, 6, '2026-01-01', '2026-06-30',
  20, 18, 98.00, 3,
  72.00, 92.00, 88.40, 'A', 'Fastest logistics partner. GPS tracking available.', 'active');

-- Supplier 7 — New/poor performer
INSERT IGNORE INTO vendor_scorecards (company_id, supplier_id, period_start, period_end,
  total_orders, on_time_deliveries, quality_pass_rate, avg_lead_time_days,
  price_competitiveness, response_rate, overall_score, rating, notes, status) VALUES
(1, 7, '2026-01-01', '2026-06-30',
  3, 1, 70.00, 15,
  95.00, 50.00, 54.20, 'D', 'New supplier under probation. Quality issues detected.', 'active');

SELECT 'Seed data inserted successfully' as result;
