-- Relational seed data for Cacao Ixchel P4
-- Includes edge cases: batch with no orders, customer with no orders, farm with one batch

-- Farms
INSERT INTO farms (id, name, location, altitude_m, variety) VALUES
(1, 'Finca Rosario', 'San Juan Chamelco, Alta Verapaz', 1350, 'Criollo'),
(2, 'Finca Las Nubes', 'Coban, Alta Verapaz', 1420, 'Trinitario'),
(3, 'Finca El Mirador', 'Lanquin, Alta Verapaz', 980, 'Criollo-Trinitario hybrid');

-- Batches (linked to farms via farm_id)
-- Finca Rosario has 2 batches, Las Nubes has 2, El Mirador has 1 (single-batch farm edge case)
INSERT INTO batches (id, batch_code, farm_id, harvest_date, stage, weight_kg, flavour_profile) VALUES
(1, '2024-H1-01', 1, '2024-03-15', 'finished', 45.0, 'dark fruit, low acidity, long finish'),
(2, '2024-H1-02', 2, '2024-04-02', 'finished', 38.0, 'bright citrus, medium body'),
(3, '2024-H2-03', 1, '2024-09-20', 'finished', 52.0, 'earthy, mild bitterness, short finish'),
(4, '2024-H2-04', 2, '2024-10-05', 'roasting', 41.0, NULL),
(5, '2024-H2-05', 3, '2024-10-18', 'finished', 28.0, 'floral, honey notes, delicate');

-- Products (linked to batches via batch_id)
INSERT INTO products (id, name, batch_id, bar_type, cacao_percentage, weight_g, units_produced) VALUES
(1, '72% Lacandon Bar', 1, 'dark', 72, 70, 200),
(2, '85% Single Origin', 1, 'extra dark', 85, 50, 150),
(3, 'Citrus Blend Bar', 2, 'dark', 68, 70, 180),
(4, '72% Lacandon Bar', 3, 'dark', 72, 70, 220),
(5, 'Drinking Chocolate', 3, 'drinking', 60, 200, 100),
(6, 'Floral Reserve Bar', 5, 'dark', 75, 70, 80),
(7, 'Cacao Nibs', 2, 'nibs', 100, 150, 120),
(8, '90% Intense Bar', 1, 'extra dark', 90, 50, 60);

-- Customers
INSERT INTO customers (id, name, city, country, contact_email) VALUES
(1, 'Munich Chocolate Boutique', 'Munich', 'Germany', 'orders@munchchoc.de'),
(2, 'Portland Bean & Bar', 'Portland', 'United States', 'buying@portlandbean.com'),
(3, 'Tokyo Cacao House', 'Tokyo', 'Japan', 'procurement@tokyocacao.jp'),
(4, 'Antigua Gourmet', 'Antigua Guatemala', 'Guatemala', 'pedidos@antiguagourmet.gt');

-- Orders (linked to customers via customer_id)
-- Customer 4 (Antigua Gourmet) has no orders -- edge case for empty relationship
INSERT INTO orders (id, customer_id, order_date, status, shipping_address) VALUES
(1, 1, '2024-10-01', 'shipped', 'Maximilianstr. 12, 80539 Munich, Germany'),
(2, 2, '2024-10-03', 'delivered', '1234 NW 23rd Ave, Portland, OR 97210'),
(3, 3, '2024-10-05', 'confirmed', '2-8-1 Nishi-Shinjuku, Tokyo 160-0023'),
(4, 1, '2024-10-15', 'in_production', 'Maximilianstr. 12, 80539 Munich, Germany'),
(5, 2, '2024-10-18', 'confirmed', '1234 NW 23rd Ave, Portland, OR 97210'),
(6, 3, '2024-10-20', 'shipped', '2-8-1 Nishi-Shinjuku, Tokyo 160-0023');

-- Order items (linked to orders and products)
-- These create the traceability chain: order -> order_item -> product -> batch -> farm
-- Batch 2024-H2-05 (id=5) has products but NO order items referencing those products
-- This means the batch exists with products but zero orders -- critical LEFT JOIN test case
INSERT INTO order_items (id, order_id, product_id, quantity, unit_price_gtq) VALUES
(1, 1, 4, 50, 35.00),
(2, 1, 5, 20, 85.00),
(3, 2, 1, 80, 35.00),
(4, 2, 8, 30, 42.00),
(5, 3, 3, 60, 35.00),
(6, 3, 7, 40, 55.00),
(7, 4, 4, 30, 35.00),
(8, 5, 2, 45, 42.00),
(9, 5, 1, 25, 35.00),
(10, 6, 3, 35, 35.00),
(11, 6, 5, 15, 85.00),
(12, 6, 4, 20, 35.00);

-- Summary of edge cases:
-- Farm 3 (Finca El Mirador): only 1 batch (id=5)
-- Batch 5 (2024-H2-05): has product (Floral Reserve Bar) but ZERO orders reference it
-- Customer 4 (Antigua Gourmet): exists but has ZERO orders
-- Batch 3 (2024-H2-03, Finca Rosario): this is the Munich incident batch
--   Products 4 and 5 came from this batch
--   Orders 1, 4, 6, 7 contain items from these products
--   Customers 1 (Munich), 3 (Tokyo) received bars from this batch