-- ============================================================
-- SEED DATA: Enhanced GRN Sub-Table Records
-- ============================================================

-- Update existing GRNs with enhanced fields
UPDATE grn SET
  asn_id = 7, po_no_ref = 'PO-2026-0001', supplier_name_ref = 'Textile Mills India',
  driver_name = 'Sunil Sharma', driver_mobile = '+919876543213', transporter_name = 'National Logistics',
  gate_entry_no = 'GE-2026-0003', shift = 'Morning', total_weight_kg = 9800.00, total_volume_cbm = 32.000,
  total_cartons = 40, total_pallets = 3, buyer_name = 'Nike', season = 'SS2026', currency = 'USD',
  delivery_terms = 'FOB', payment_terms = '60 Days', received_at = '2026-07-13 09:00:00'
WHERE id = 2;

UPDATE grn SET
  po_no_ref = 'PO-2026-0002', supplier_name_ref = 'Fashion Trims Co',
  driver_name = 'Kumar', driver_mobile = '+919876543210', transporter_name = 'FastCargo Logistics',
  gate_entry_no = 'GE-2026-0001', shift = 'Morning', total_weight_kg = 1035.00, total_volume_cbm = 6.900,
  total_cartons = 30, total_pallets = 0, buyer_name = 'Zara', season = 'AW2026', currency = 'USD',
  delivery_terms = 'CIF', payment_terms = '45 Days', received_at = '2026-07-20 11:30:00'
WHERE id = 1;

-- ========================================
-- 1. GRN BATCHES — For GRN-2
-- ========================================
INSERT IGNORE INTO grn_batches (grn_id, grn_item_id, batch_number, mfg_date, expiry_date, supplier_lot, internal_lot, quantity, uom_id, status, notes) VALUES
(2, 3, 'BATCH-2026-0713-001', '2026-07-01', NULL, 'LOT-101', 'FC-LOT-001', 2000.0000, 5, 'active', 'Cotton Jersey Size S'),
(2, 4, 'BATCH-2026-0713-002', '2026-07-01', NULL, 'LOT-102', 'FC-LOT-002', 2000.0000, 5, 'active', 'Cotton Jersey Size M'),
(2, 5, 'BATCH-2026-0713-003', '2026-07-01', NULL, 'LOT-103', 'FC-LOT-003', 2000.0000, 5, 'active', 'Cotton Jersey Size L'),
(2, 6, 'BATCH-2026-0713-004', '2026-07-01', NULL, 'LOT-104', 'FC-LOT-004', 1980.0000, 5, 'active', 'Cotton Jersey Size XL (20 rejected)'),
(2, 7, 'BATCH-2026-0713-005', '2026-06-28', NULL, 'LOT-105', 'FC-LOT-005', 3000.0000, 5, 'active', 'Rib Collar'),
(2, 8, 'BATCH-2026-0713-006', '2026-07-05', NULL, 'LOT-106', 'FC-LOT-006', 5000.0000, 1, 'active', 'Sewing Thread');

-- ========================================
-- 2. GRN ROLLS — For GRN-2 (fabric rolls)
-- ========================================
INSERT IGNORE INTO grn_rolls (grn_id, grn_item_id, roll_no, roll_barcode, length_meters, width_cm, weight_kg, gsm, shade_grade, defect_notes, status) VALUES
(2, 3, 'R-001', 'RL-2026-0001', 58.50, 180.00, 52.65, 178.00, 'A', NULL, 'passed'),
(2, 3, 'R-002', 'RL-2026-0002', 62.00, 180.00, 55.80, 179.00, 'A', NULL, 'passed'),
(2, 3, 'R-003', 'RL-2026-0003', 55.00, 180.00, 49.50, 177.50, 'A', NULL, 'passed'),
(2, 3, 'R-004', 'RL-2026-0004', 60.00, 180.00, 54.00, 178.00, 'A', 'Minor shade variation at edge', 'passed'),
(2, 4, 'R-005', 'RL-2026-0005', 58.00, 180.00, 52.20, 178.50, 'A', NULL, 'passed'),
(2, 4, 'R-006', 'RL-2026-0006', 61.00, 180.00, 54.90, 179.00, 'A', NULL, 'passed'),
(2, 4, 'R-007', 'RL-2026-0007', 57.50, 180.00, 51.75, 178.00, 'A', NULL, 'passed'),
(2, 4, 'R-008', 'RL-2026-0008', 60.50, 180.00, 54.45, 178.50, 'A', NULL, 'passed'),
(2, 5, 'R-009', 'RL-2026-0009', 59.00, 180.00, 53.10, 178.00, 'A', NULL, 'passed'),
(2, 5, 'R-010', 'RL-2026-0010', 61.50, 180.00, 55.35, 179.00, 'A', NULL, 'passed'),
(2, 5, 'R-011', 'RL-2026-0011', 56.00, 180.00, 50.40, 177.50, 'B', 'Slight curling at selvedge', 'passed'),
(2, 6, 'R-012', 'RL-2026-0012', 58.00, 180.00, 52.20, 178.00, 'A', NULL, 'passed');

-- ========================================
-- 3. GRN IMAGES — For GRN-2
-- ========================================
INSERT IGNORE INTO grn_images (grn_id, category, file_name, file_path, file_type, file_size, caption, uploaded_by) VALUES
(2, 'truck', 'truck-arrival.jpg', '/uploads/grn-images/truck-arrival.jpg', 'image/jpeg', 2450000, 'Truck KA01EF5678 arriving at Dock-1', 1),
(2, 'container', 'container-seal.jpg', '/uploads/grn-images/container-seal.jpg', 'image/jpeg', 1890000, 'Container seal intact on arrival', 1),
(2, 'material', 'fabric-pallet-1.jpg', '/uploads/grn-images/fabric-pallet-1.jpg', 'image/jpeg', 3200000, 'First pallet of fabric rolls', 1),
(2, 'material', 'fabric-pallet-2.jpg', '/uploads/grn-images/fabric-pallet-2.jpg', 'image/jpeg', 2800000, 'Second pallet - rib collars', 1),
(2, 'fabric_roll', 'roll-inspection.jpg', '/uploads/grn-images/roll-inspection.jpg', 'image/jpeg', 1500000, 'Roll inspection at receiving dock', 1),
(2, 'barcode_label', 'grn-barcode.jpg', '/uploads/grn-images/grn-barcode.jpg', 'image/jpeg', 890000, 'GRN barcode label applied', 1);

-- ========================================
-- 4. GRN QUALITY RESULTS — For GRN-2
-- ========================================
INSERT IGNORE INTO grn_quality_results (grn_id, grn_item_id, inspection_type, inspector_name, inspection_date, sample_size, passed_qty, rejected_qty, result, fabric_4point, shade_approval, gsm_test, width_check, shrinkage_test, lab_test, defects, remarks) VALUES
(2, 3, 'incoming', 'Fatima Begum', '2026-07-13 14:00:00', 200.0000, 198.0000, 2.0000, 'passed', 'Passed', 'Passed', 'Passed', 'Passed', 'Passed', 'Passed',
  '[{"defect":"Minor shade variation","count":2}]', 'Minor shade variation on 2 rolls. Within tolerance.'),
(2, 7, 'incoming', 'Fatima Begum', '2026-07-13 15:00:00', 500.0000, 500.0000, 0, 'passed', 'Passed', 'Passed', 'N/A', 'N/A', 'N/A', 'N/A', NULL, 'Rib collars passed all checks'),
(2, 8, 'incoming', 'Fatima Begum', '2026-07-13 15:30:00', 1000.0000, 1000.0000, 0, 'passed', 'N/A', 'N/A', 'N/A', 'N/A', 'N/A', 'Passed', NULL, 'Thread tensile strength OK');

-- ========================================
-- 5. GRN REJECTIONS — For GRN-2 (from item 6 / XL)
-- ========================================
INSERT IGNORE INTO grn_rejections (grn_id, grn_item_id, rejection_qty, rejection_type, defect_type, defect_description, disposition, inspected_by, inspected_at) VALUES
(2, 6, 20.0000, 'quality', 'stain', 'Oil stains on 20 fabric pieces (Size XL). Likely from cutting machine.', 'return_to_supplier', 1, '2026-07-13 16:00:00');

-- ========================================
-- 6. GRN PUTAWAY — For GRN-2
-- ========================================
INSERT IGNORE INTO grn_putaway (grn_id, grn_item_id, warehouse_id, rack, bin, location_code, allocated_qty, putaway_qty, status, putaway_by, putaway_at, notes) VALUES
(2, 3, 3, 'A', 'A-01', 'FC-FAB-001', 2000.0000, 2000.0000, 'put_away', 1, '2026-07-13 16:30:00', 'Size S fabric stored'),
(2, 4, 3, 'A', 'A-02', 'FC-FAB-002', 2000.0000, 2000.0000, 'put_away', 1, '2026-07-13 16:35:00', 'Size M fabric stored'),
(2, 5, 3, 'A', 'A-03', 'FC-FAB-003', 2000.0000, 2000.0000, 'put_away', 1, '2026-07-13 16:40:00', 'Size L fabric stored'),
(2, 6, 3, 'A', 'A-04', 'FC-FAB-004', 1980.0000, 1980.0000, 'put_away', 1, '2026-07-13 16:45:00', 'Size XL fabric stored (after QC)'),
(2, 7, 3, 'B', 'B-01', 'FC-TRM-001', 3000.0000, 3000.0000, 'put_away', 1, '2026-07-13 17:00:00', 'Rib collar stored in trim section'),
(2, 8, 3, 'B', 'B-02', 'FC-TRM-002', 5000.0000, 5000.0000, 'put_away', 1, '2026-07-13 17:05:00', 'Sewing thread stored');

-- ========================================
-- 7. GRN BARCODES — For GRN-1 and GRN-2
-- ========================================
INSERT IGNORE INTO grn_barcodes (grn_id, barcode_type, barcode_value, label_text, generated_by) VALUES
(2, 'barcode', 'GRN20260002', 'GRN: GRN-2026-0002', 1),
(2, 'qr_code', 'https://erp.fashcognitive.com/grn/GRN-2026-0002', 'QR: GRN-2026-0002', 1),
(1, 'barcode', 'GRN20260001', 'GRN: GRN-2026-0001', 1),
(1, 'qr_code', 'https://erp.fashcognitive.com/grn/GRN-2026-0001', 'QR: GRN-2026-0001', 1);

-- ========================================
-- 8. GRN APPROVALS — For GRN-2
-- ========================================
INSERT IGNORE INTO grn_approvals (grn_id, approval_level, role_required, approver_name, approver_id, status, comments, approved_at) VALUES
(2, 1, 'warehouse_manager', 'Admin User', 1, 'approved', 'Goods verified at dock', '2026-07-13 17:10:00'),
(2, 2, 'quality_inspector', 'Fatima Begum', 1, 'approved', 'QC passed - all parameters within tolerance', '2026-07-13 17:15:00'),
(2, 3, 'purchase_manager', 'Admin User', 1, 'approved', 'Approved for inventory', '2026-07-13 17:20:00');

-- ========================================
-- 9. GRN AI ANALYSIS — For GRN-2
-- ========================================
INSERT IGNORE INTO grn_ai_analysis (grn_id, grn_item_id, analysis_type, readiness_score, shade_variation, width_variation_pct, moisture_level, defect_density, predicted_grade, ai_recommendation, ai_raw_data) VALUES
(2, 3, 'fabric_inspection', 95.00, 'LOW', 0.25, 'Normal', 'LOW', 'A', 'Accept Material - Excellent Quality',
  '{"readiness_score":95,"shade_variation":"LOW","width_variation_pct":0.25,"moisture_level":"Normal","defect_density":"LOW","predicted_grade":"A","ai_recommendation":"Accept Material - Excellent Quality","fabric_images_analyzed":12,"defects_found":0,"confidence":0.96}'),
(2, 6, 'fabric_inspection', 72.00, 'MEDIUM', 1.20, 'Normal', 'MEDIUM', 'B', 'Conditional Accept - Manual Review Required',
  '{"readiness_score":72,"shade_variation":"MEDIUM","width_variation_pct":1.20,"moisture_level":"Normal","defect_density":"MEDIUM","predicted_grade":"B","ai_recommendation":"Conditional Accept - Manual Review Required","fabric_images_analyzed":8,"defects_found":2,"confidence":0.82}');

-- ========================================
-- 10. GRN INVENTORY TRANSACTIONS — For GRN-2
-- ========================================
INSERT IGNORE INTO grn_inventory_transactions (grn_id, grn_item_id, warehouse_id, item_id, color, size, lot_no, batch_number, uom_id, qty_before, qty_received, qty_after, unit_cost, total_cost, movement_type, reference_type, reference_id, notes) VALUES
(2, 3, 3, 1, 'Black', 'S', 'LOT-101', 'BATCH-2026-0713-001', 5, 0, 2000.0000, 2000.0000, 4.50, 9000.00, 'purchase_in', 'grn', 2, 'Size S fabric received'),
(2, 4, 3, 1, 'Black', 'M', 'LOT-102', 'BATCH-2026-0713-002', 5, 0, 2000.0000, 2000.0000, 4.50, 9000.00, 'purchase_in', 'grn', 2, 'Size M fabric received'),
(2, 5, 3, 1, 'Black', 'L', 'LOT-103', 'BATCH-2026-0713-003', 5, 0, 2000.0000, 2000.0000, 4.50, 9000.00, 'purchase_in', 'grn', 2, 'Size L fabric received'),
(2, 6, 3, 1, 'Black', 'XL', 'LOT-104', 'BATCH-2026-0713-004', 5, 0, 1980.0000, 1980.0000, 4.50, 8910.00, 'purchase_in', 'grn', 2, 'Size XL fabric received (20 rejected)'),
(2, 7, 3, 2, 'Black', NULL, 'LOT-105', 'BATCH-2026-0713-005', 5, 0, 3000.0000, 3000.0000, 8.00, 24000.00, 'purchase_in', 'grn', 2, 'Rib collar received'),
(2, 8, 3, 3, 'Black', NULL, 'LOT-106', 'BATCH-2026-0713-006', 1, 0, 5000.0000, 5000.0000, 0.10, 500.00, 'purchase_in', 'grn', 2, 'Sewing thread received');

SELECT 'GRN seed data inserted successfully' as result;
