-- Packaging Module Demo Data
-- Realistic data for agriculture wholesale & packing operations

-- Assuming org_id = 1, and some existing products, suppliers, locations, users

-- Packaging Materials (Common materials used in agriculture packing)
INSERT INTO packaging_materials (org_id, material_code, name, description, material_type, material_category, supplier_id, unit_of_measure, cost_per_unit, current_stock, minimum_stock, reorder_quantity, weight_per_unit, status, created_by) VALUES
(1, 'MAT-BOX-001', 'Corrugated Box 5kg', 'Small corrugated cardboard box for 5kg produce', 'box', 'secondary', 1, 'each', 0.85, 5000, 1000, 2000, 0.180, 'active', 1),
(1, 'MAT-BOX-002', 'Corrugated Box 10kg', 'Medium corrugated box for 10kg produce', 'box', 'secondary', 1, 'each', 1.20, 3000, 800, 2000, 0.320, 'active', 1),
(1, 'MAT-BOX-003', 'Export Carton 15kg', 'Heavy duty export-grade cardboard carton', 'box', 'secondary', 1, 'each', 2.50, 1500, 500, 1000, 0.550, 'active', 1),
(1, 'MAT-BOX-004', 'Wax Carton 10kg', 'Wax-coated box for moisture protection', 'box', 'secondary', 1, 'each', 1.80, 2000, 600, 1500, 0.400, 'active', 1),

(1, 'MAT-BAG-001', 'PE Bag 500g', 'Clear polyethylene bag 500g capacity', 'bag', 'primary', 2, 'roll', 0.02, 50000, 10000, 20000, 0.005, 'active', 1),
(1, 'MAT-BAG-002', 'PE Bag 1kg', 'Clear polyethylene bag 1kg capacity', 'bag', 'primary', 2, 'roll', 0.03, 40000, 8000, 15000, 0.008, 'active', 1),
(1, 'MAT-BAG-003', 'Mesh Bag 5kg', 'Breathable mesh bag for onions/potatoes', 'bag', 'primary', 2, 'each', 0.15, 10000, 2000, 5000, 0.012, 'active', 1),
(1, 'MAT-BAG-004', 'Vacuum Bag 2kg', 'Vacuum-seal bag for extended shelf life', 'bag', 'primary', 2, 'roll', 0.08, 5000, 1000, 3000, 0.015, 'active', 1),

(1, 'MAT-FILM-001', 'Stretch Film 500mm', 'LLDPE stretch film for pallet wrapping', 'film', 'tertiary', 2, 'roll', 12.50, 200, 50, 100, 2.500, 'active', 1),
(1, 'MAT-FILM-002', 'Cling Film 300mm', 'Food-grade cling film for product wrapping', 'film', 'primary', 2, 'roll', 8.00, 150, 40, 80, 1.200, 'active', 1),

(1, 'MAT-TRAY-001', 'PET Tray 250g', 'Clear PET tray for berries/cherry tomatoes', 'tray', 'primary', 3, 'each', 0.12, 8000, 2000, 5000, 0.018, 'active', 1),
(1, 'MAT-TRAY-002', 'Foam Tray 500g', 'Foam tray with absorbent pad', 'tray', 'primary', 3, 'each', 0.18, 5000, 1500, 3000, 0.025, 'active', 1),

(1, 'MAT-LBL-001', 'Barcode Label 100x50mm', 'Thermal transfer barcode labels', 'label', 'primary', 4, 'roll', 0.01, 100000, 20000, 50000, 0.001, 'active', 1),
(1, 'MAT-LBL-002', 'Product Label Color', 'Full-color product labels with logo', 'label', 'primary', 4, 'roll', 0.05, 50000, 10000, 25000, 0.002, 'active', 1),

(1, 'MAT-TAPE-001', 'Packing Tape 48mm', 'PP packing tape brown 48mm x 100m', 'tape', 'secondary', 1, 'roll', 1.50, 500, 100, 200, 0.240, 'active', 1),
(1, 'MAT-TAPE-002', 'Fragile Tape 48mm', 'Printed "FRAGILE" warning tape', 'tape', 'secondary', 1, 'roll', 2.00, 200, 50, 100, 0.240, 'active', 1),

(1, 'MAT-PLT-001', 'Euro Pallet 1200x800', 'Standard EUR-EPAL wooden pallet', 'pallet', 'tertiary', 5, 'each', 15.00, 150, 30, 50, 25.000, 'active', 1),
(1, 'MAT-PLT-002', 'Plastic Pallet 1200x1000', 'Hygienic plastic export pallet', 'pallet', 'tertiary', 5, 'each', 35.00, 80, 20, 40, 18.000, 'active', 1);

-- Packaging Templates (Standard packaging configurations)
INSERT INTO packaging_templates (org_id, template_code, name, description, product_category_id, package_type, target_weight, target_quantity, estimated_time_minutes, cost_estimate, instructions, status, created_by) VALUES
(1, 'TPL-RETAIL-500G', 'Retail Pack 500g', 'Standard retail packaging for 500g produce', NULL, 'retail', 0.500, 1, 2, 0.25,
 'Pack 500g of sorted produce in PE bag. Seal bag leaving small air gap. Apply product label with batch number and date. Place in display tray if required.',
 'active', 1),

(1, 'TPL-RETAIL-1KG', 'Retail Pack 1kg', 'Standard retail packaging for 1kg produce', NULL, 'retail', 1.000, 1, 3, 0.35,
 'Pack 1kg of graded produce in PE bag. Ensure product quality visible through bag. Seal securely. Apply barcode and product labels. Quality check before boxing.',
 'active', 1),

(1, 'TPL-BULK-5KG', 'Bulk Box 5kg', 'Bulk packaging in 5kg boxes for wholesale', NULL, 'bulk', 5.000, 1, 5, 1.10,
 'Line box with liner if needed. Pack 5kg of produce ensuring even distribution. Close and seal box. Apply batch labels on two sides. Stack maximum 5 high.',
 'active', 1),

(1, 'TPL-BULK-10KG', 'Bulk Box 10kg', 'Standard 10kg box for wholesale distribution', NULL, 'bulk', 10.000, 1, 8, 1.85,
 'Use wax carton for moisture-sensitive products. Pack 10kg maintaining quality standards. Ensure no overpacking. Seal with packing tape. Label with product, batch, weight, and destination.',
 'active', 1),

(1, 'TPL-EXPORT-15KG', 'Export Carton 15kg', 'Export-grade packaging meeting international standards', NULL, 'export', 15.000, 1, 12, 3.20,
 'Use export-grade carton. Line with food-grade liner. Pack produce in layers with separator sheets. Ensure ventilation holes not blocked. Apply phytosanitary certificate sticker. Label in English with all required export information.',
 'active', 1),

(1, 'TPL-TRAY-250G', 'Berry Tray 250g', 'Premium tray packaging for berries and cherry tomatoes', NULL, 'retail', 0.250, 1, 4, 0.45,
 'Hand-select premium grade only. Arrange in PET tray for visual appeal. Apply cling film covering. Label with variety, weight, and "Best Before" date. Place in retail display box.',
 'active', 1),

(1, 'TPL-MESH-5KG', 'Mesh Bag 5kg', 'Breathable mesh bag for onions, potatoes, citrus', NULL, 'retail', 5.000, 1, 3, 0.30,
 'Fill mesh bag with 5kg of produce. Ensure no damaged items included. Secure top with metal clip. Attach hanging label with product name and weight. Suitable for ambient display.',
 'active', 1),

(1, 'TPL-VACUUM-2KG', 'Vacuum Pack 2kg', 'Vacuum-sealed pack for extended shelf life', NULL, 'retail', 2.000, 1, 6, 0.85,
 'Pre-cool produce if warm. Place 2kg in vacuum bag ensuring even distribution. Vacuum seal leaving 2cm from edge. Check seal integrity. Label with batch, pack date, and use-by date. Refrigerate immediately.',
 'active', 1);

-- Packaging Orders (Work orders in various states)
INSERT INTO packaging_orders (org_id, order_number, order_date, product_id, template_id, source_batch_number, target_batch_number, quantity_to_pack, quantity_packed, unit, packages_required, packages_completed, assigned_to, assigned_location_id, priority, status, scheduled_start, scheduled_end, actual_start, actual_end, notes, created_by) VALUES
-- Completed orders
(1, 'PKG-2024-001', '2024-11-18 08:00:00', 1, 3, 'BATCH-TOM-20241118', 'PKG-TOM-5KG-001', 500.00, 500.00, 'kg', 100, 100, 1, 2, 'high', 'completed', 
 '2024-11-18 08:00:00', '2024-11-18 17:00:00', '2024-11-18 08:15:00', '2024-11-18 16:45:00',
 'Tomatoes packed for major supermarket chain. All 100 boxes completed. Quality excellent.', 1),

(1, 'PKG-2024-002', '2024-11-19 07:30:00', 2, 2, 'BATCH-LET-20241119', 'PKG-LET-1KG-001', 300.00, 300.00, 'kg', 300, 300, 2, 2, 'normal', 'completed',
 '2024-11-19 07:30:00', '2024-11-19 15:00:00', '2024-11-19 07:45:00', '2024-11-19 14:30:00',
 'Lettuce packed in 1kg retail bags. Completed ahead of schedule.', 1),

(1, 'PKG-2024-003', '2024-11-19 13:00:00', 3, 7, 'BATCH-CAR-20241119', 'PKG-CAR-MESH-001', 250.00, 250.00, 'kg', 50, 50, 1, 2, 'normal', 'completed',
 '2024-11-19 13:00:00', '2024-11-19 16:00:00', '2024-11-19 13:10:00', '2024-11-19 15:50:00',
 'Carrots packed in 5kg mesh bags. Good quality throughout.', 1),

(1, 'PKG-2024-004', '2024-11-20 06:00:00', 4, 4, 'BATCH-POT-20241120', 'PKG-POT-10KG-001', 800.00, 800.00, 'kg', 80, 80, 2, 2, 'normal', 'completed',
 '2024-11-20 06:00:00', '2024-11-20 14:00:00', '2024-11-20 06:15:00', '2024-11-20 13:45:00',
 'Potatoes packed in 10kg wax cartons. All labels applied correctly.', 1),

-- In-progress orders
(1, 'PKG-2024-005', '2024-11-21 07:00:00', 1, 6, 'BATCH-CHERRY-001', 'PKG-CHERRY-TRAY-001', 100.00, 65.00, 'kg', 400, 260, 2, 2, 'high', 'in-progress',
 '2024-11-21 07:00:00', '2024-11-21 16:00:00', '2024-11-21 07:10:00', NULL,
 'Premium cherry tomatoes in trays. In progress - about 65% complete. High demand from retail.', 1),

(1, 'PKG-2024-006', '2024-11-21 09:00:00', 5, 5, 'BATCH-ORG-EXPORT-001', 'PKG-ORG-EXPORT-001', 450.00, 180.00, 'kg', 30, 12, 1, 2, 'urgent', 'in-progress',
 '2024-11-21 09:00:00', '2024-11-21 18:00:00', '2024-11-21 09:15:00', NULL,
 'Organic oranges for export to Singapore. Must complete today for shipping schedule. Currently 40% done.', 1),

(1, 'PKG-2024-007', '2024-11-21 10:30:00', 6, 1, 'BATCH-CUC-20241121', 'PKG-CUC-500G-001', 200.00, 100.00, 'kg', 400, 200, 2, 2, 'normal', 'in-progress',
 '2024-11-21 10:30:00', '2024-11-21 17:00:00', '2024-11-21 10:45:00', NULL,
 'Cucumbers in 500g retail packs. Halfway through packing run.', 1),

-- Pending orders
(1, 'PKG-2024-008', '2024-11-21 14:00:00', 7, 3, 'BATCH-PEP-20241121', 'PKG-PEP-5KG-001', 350.00, 0.00, 'kg', 70, 0, 1, 2, 'normal', 'pending',
 '2024-11-21 14:00:00', '2024-11-21 18:00:00', NULL, NULL,
 'Bell peppers to be packed in 5kg boxes. Scheduled for afternoon shift.', 1),

(1, 'PKG-2024-009', '2024-11-22 06:00:00', 8, 7, 'BATCH-ONI-20241121', 'PKG-ONI-MESH-001', 500.00, 0.00, 'kg', 100, 0, 2, 2, 'normal', 'pending',
 '2024-11-22 06:00:00', '2024-11-22 12:00:00', NULL, NULL,
 'Onions in 5kg mesh bags. Scheduled for tomorrow morning.', 1),

(1, 'PKG-2024-010', '2024-11-22 08:00:00', 9, 2, 'BATCH-APP-20241122', 'PKG-APP-1KG-001', 600.00, 0.00, 'kg', 600, 0, 1, 2, 'high', 'pending',
 '2024-11-22 08:00:00', '2024-11-22 17:00:00', NULL, NULL,
 'Apples in 1kg retail bags. Large order for multiple retail stores.', 1),

-- On-hold order
(1, 'PKG-2024-011', '2024-11-20 08:00:00', 10, 4, 'BATCH-MIX-VEG-001', 'PKG-MIX-10KG-001', 400.00, 0.00, 'kg', 40, 0, NULL, 2, 'low', 'on-hold',
 '2024-11-20 08:00:00', '2024-11-20 16:00:00', NULL, NULL,
 'Mixed vegetables order. On hold pending customer confirmation of quantities.', 1);

-- Packaging Lines (Materials used in orders)
INSERT INTO packaging_lines (org_id, order_id, line_number, material_id, quantity_required, quantity_used, unit, cost_per_unit, total_cost, notes) VALUES
-- Lines for PKG-2024-001 (Tomatoes 5kg boxes)
(1, 1, 1, 1, 100, 100, 'each', 0.85, 85.00, '5kg boxes for tomatoes'),
(1, 1, 2, 13, 100, 100, 'each', 0.01, 1.00, 'Barcode labels'),
(1, 1, 3, 15, 3, 3, 'roll', 1.50, 4.50, 'Packing tape'),

-- Lines for PKG-2024-002 (Lettuce 1kg bags)
(1, 2, 1, 6, 300, 300, 'each', 0.03, 9.00, '1kg PE bags'),
(1, 2, 2, 14, 300, 300, 'each', 0.05, 15.00, 'Product labels'),

-- Lines for PKG-2024-003 (Carrots mesh bags)
(1, 3, 1, 7, 50, 50, 'each', 0.15, 7.50, '5kg mesh bags'),
(1, 3, 2, 14, 50, 50, 'each', 0.05, 2.50, 'Hanging labels'),

-- Lines for PKG-2024-004 (Potatoes wax cartons)
(1, 4, 1, 4, 80, 80, 'each', 1.80, 144.00, 'Wax cartons 10kg'),
(1, 4, 2, 13, 80, 80, 'each', 0.01, 0.80, 'Barcode labels'),
(1, 4, 3, 15, 2, 2, 'roll', 1.50, 3.00, 'Packing tape'),

-- Lines for PKG-2024-005 (Cherry tomatoes trays) - in progress
(1, 5, 1, 11, 400, 260, 'each', 0.12, 48.00, 'PET trays'),
(1, 5, 2, 10, 2, 1, 'roll', 8.00, 8.00, 'Cling film'),
(1, 5, 3, 14, 400, 260, 'each', 0.05, 20.00, 'Product labels');

-- Packaging Batches (Completed batches ready for storage/shipping)
INSERT INTO packaging_batches (org_id, batch_number, order_id, product_id, template_id, packed_date, packed_by, location_id, total_packages, total_weight, total_units, package_type, lot_number, quality_checked, quality_check_date, quality_check_by, quality_result, storage_location, labels_printed, label_print_date, status, notes, created_by) VALUES
(1, 'PKG-TOM-5KG-001', 1, 1, 3, '2024-11-18 16:45:00', 1, 2, 100, 500.000, 100, 'bulk', 'LOT-TOM-20241118', true, '2024-11-18 17:00:00', 1, 'pass', 'COLD-A-01', true, '2024-11-18 16:50:00', 'stored',
 '100 boxes of 5kg tomatoes. All passed QA inspection. Stored in cold room A.', 1),

(1, 'PKG-LET-1KG-001', 2, 2, 2, '2024-11-19 14:30:00', 2, 2, 300, 300.000, 300, 'retail', 'LOT-LET-20241119', true, '2024-11-19 14:45:00', 2, 'pass', 'COLD-A-03', true, '2024-11-19 14:35:00', 'stored',
 '300 units of 1kg lettuce bags. Quality excellent. Ready for distribution.', 1),

(1, 'PKG-CAR-MESH-001', 3, 3, 7, '2024-11-19 15:50:00', 1, 2, 50, 250.000, 50, 'retail', 'LOT-CAR-20241119', true, '2024-11-19 16:00:00', 1, 'pass', 'DRY-B-02', true, '2024-11-19 15:55:00', 'stored',
 '50 mesh bags of carrots. All labels applied. Stored in dry storage.', 1),

(1, 'PKG-POT-10KG-001', 4, 4, 4, '2024-11-20 13:45:00', 2, 2, 80, 800.000, 80, 'bulk', 'LOT-POT-20241120', true, '2024-11-20 14:00:00', 2, 'pass', 'DRY-A-05', true, '2024-11-20 13:50:00', 'shipped',
 '80 cartons of 10kg potatoes. Shipped to wholesale customer.', 1),

(1, 'PKG-CHERRY-TRAY-001', 5, 1, 6, '2024-11-21 12:00:00', 2, 2, 260, 65.000, 260, 'retail', 'LOT-CHERRY-20241121', false, NULL, NULL, NULL, 'COLD-A-02', true, '2024-11-21 12:05:00', 'packed',
 'Partial batch of cherry tomato trays. Packing still in progress. QA inspection pending completion.', 1);

-- Packaging Performance (Efficiency tracking)
INSERT INTO packaging_performance (org_id, date, shift, location_id, operator_id, packages_produced, hours_worked, packages_per_hour, downtime_minutes, waste_percentage, quality_issues, notes) VALUES
(1, '2024-11-18', 'morning', 2, 1, 100, 8.5, 11.76, 15, 0.5, 0, 'Good productivity. Minor downtime for tape roll change.'),
(1, '2024-11-19', 'morning', 2, 2, 300, 7.75, 38.71, 10, 0.3, 0, 'Excellent day. Lettuce packing very efficient.'),
(1, '2024-11-19', 'afternoon', 2, 1, 50, 3.0, 16.67, 5, 1.0, 0, 'Carrots mesh bagging. Some waste from quality sorting.'),
(1, '2024-11-20', 'morning', 2, 2, 80, 7.5, 10.67, 20, 0.8, 1, 'Potato packing. One box needed repack due to overfilling.'),
(1, '2024-11-21', 'morning', 2, 2, 260, 4.83, 53.83, 8, 0.2, 0, 'Cherry tomato trays. High piece count per hour.'),
(1, '2024-11-21', 'morning', 2, 1, 12, 2.25, 5.33, 30, 1.5, 0, 'Export cartons. Slower due to detailed labeling requirements. Downtime for label printer issue.');

-- Update materials stock based on usage
UPDATE packaging_materials SET current_stock = 4897 WHERE material_code = 'MAT-BOX-001'; -- Used 100 + 3
UPDATE packaging_materials SET current_stock = 2996 WHERE material_code = 'MAT-BOX-002'; -- Used 4
UPDATE packaging_materials SET current_stock = 1420 WHERE material_code = 'MAT-BOX-004'; -- Used 80
UPDATE packaging_materials SET current_stock = 49700 WHERE material_code = 'MAT-BAG-001'; -- Used 300
UPDATE packaging_materials SET current_stock = 9950 WHERE material_code = 'MAT-BAG-003'; -- Used 50
UPDATE packaging_materials SET current_stock = 7740 WHERE material_code = 'MAT-TRAY-001'; -- Used 260
UPDATE packaging_materials SET current_stock = 147 WHERE material_code = 'MAT-FILM-002'; -- Used 3 rolls
UPDATE packaging_materials SET current_stock = 99330 WHERE material_code = 'MAT-LBL-001'; -- Used 670
UPDATE packaging_materials SET current_stock = 49050 WHERE material_code = 'MAT-LBL-002'; -- Used 950
UPDATE packaging_materials SET current_stock = 490 WHERE material_code = 'MAT-TAPE-001'; -- Used 10 rolls

COMMENT ON TABLE packaging_orders IS 'Packaging work orders - Demo data shows various order states and types';
COMMENT ON TABLE packaging_batches IS 'Completed packaging batches - Examples of different package types and statuses';
COMMENT ON TABLE packaging_performance IS 'Packaging efficiency metrics - Real productivity data for different operations';
