-- ============================================================================
-- Phase 7 Inventory Control - Test Data
-- ============================================================================
-- 
-- This file creates comprehensive test data for Phase 7 features:
-- - Multi-unit products (PALLET → CARTON → PIECE)
-- - Purchase Orders with line items
-- - Goods Receipts (partial and full)
-- - Dispatch Notes with unit conversions
-- - Inventory Breaks (breaking bulk into smaller units)
-- - Complete audit trail via inventory_movement_history
--
-- Usage:
--   psql -U postgres -d tafdb -f sql/phase7_test_data.sql
--
-- ============================================================================

-- Ensure we have the UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Get org_id (use first org or create demo org)
DO $$
DECLARE
    v_org_id uuid;
    v_user_id uuid;
    v_vendor1_id uuid;
    v_vendor2_id uuid;
    v_vendor3_id uuid;
    v_branch_main_id uuid;
    v_branch_warehouse_id uuid;
    v_branch_retail_id uuid;
BEGIN
    -- Use existing org or create demo org
    SELECT org_id INTO v_org_id FROM orgs LIMIT 1;
    IF v_org_id IS NULL THEN
        -- Create demo org if none exists
        INSERT INTO orgs (org_id, name, dashboard_name, product_name)
        VALUES (
            '0194a8b2-1234-7123-8123-123456789abc'::uuid,
            'Demo Org',
            'Phase 7 Inventory Demo',
            'Phase 7 Inventory Demo'
        )
        RETURNING org_id INTO v_org_id;
    END IF;

    -- Get or create test user
    SELECT user_id INTO v_user_id FROM users WHERE full_name = 'Test User' LIMIT 1;
    IF v_user_id IS NULL THEN
        INSERT INTO users (user_id, full_name, status) 
        VALUES (uuid_generate_v7(), 'Test User', 'active')
        RETURNING user_id INTO v_user_id;
    END IF;

    -- Ensure vendors exist
    SELECT vendor_id INTO v_vendor1_id FROM vendors WHERE vendorName = 'Acme Corp' LIMIT 1;
    IF v_vendor1_id IS NULL THEN
        INSERT INTO vendors (vendor_id, vendorName) 
        VALUES (uuid_generate_v7(), 'Acme Corp')
        RETURNING vendor_id INTO v_vendor1_id;
    ELSE
        v_vendor1_id := (SELECT vendor_id FROM vendors WHERE vendorName = 'Acme Corp' LIMIT 1);
    END IF;

    SELECT vendor_id INTO v_vendor2_id FROM vendors WHERE vendorName = 'Global Supplies' LIMIT 1;
    IF v_vendor2_id IS NULL THEN
        INSERT INTO vendors (vendor_id, vendorName) 
        VALUES (uuid_generate_v7(), 'Global Supplies')
        RETURNING vendor_id INTO v_vendor2_id;
    ELSE
        v_vendor2_id := (SELECT vendor_id FROM vendors WHERE vendorName = 'Global Supplies' LIMIT 1);
    END IF;

    SELECT vendor_id INTO v_vendor3_id FROM vendors WHERE vendorName = 'Pacific Wholesalers' LIMIT 1;
    IF v_vendor3_id IS NULL THEN
        INSERT INTO vendors (vendor_id, vendorName) 
        VALUES (uuid_generate_v7(), 'Pacific Wholesalers')
        RETURNING vendor_id INTO v_vendor3_id;
    END IF;

    -- Ensure branches exist
    SELECT branch_id INTO v_branch_main_id FROM branches WHERE name = 'Main Branch' LIMIT 1;
    IF v_branch_main_id IS NULL THEN
        INSERT INTO branches (branch_id, name, org_id) 
        VALUES (uuid_generate_v7(), 'Main Branch', v_org_id)
        RETURNING branch_id INTO v_branch_main_id;
    END IF;

    SELECT branch_id INTO v_branch_warehouse_id FROM branches WHERE name = 'Warehouse' LIMIT 1;
    IF v_branch_warehouse_id IS NULL THEN
        INSERT INTO branches (branch_id, name, org_id) 
        VALUES (uuid_generate_v7(), 'Warehouse', v_org_id)
        RETURNING branch_id INTO v_branch_warehouse_id;
    END IF;

    SELECT branch_id INTO v_branch_retail_id FROM branches WHERE name = 'Retail Store' LIMIT 1;
    IF v_branch_retail_id IS NULL THEN
        INSERT INTO branches (branch_id, name, org_id) 
        VALUES (uuid_generate_v7(), 'Retail Store', v_org_id)
        RETURNING branch_id INTO v_branch_retail_id;
    END IF;

    -- Store in temp table for use in subsequent statements
    CREATE TEMP TABLE test_context (
        org_id uuid,
        user_id uuid,
        vendor1_id uuid,
        vendor2_id uuid,
        vendor3_id uuid,
        branch_main_id uuid,
        branch_warehouse_id uuid,
        branch_retail_id uuid
    );
    
    INSERT INTO test_context VALUES (
        v_org_id, v_user_id, 
        v_vendor1_id, v_vendor2_id, v_vendor3_id,
        v_branch_main_id, v_branch_warehouse_id, v_branch_retail_id
    );
END $$;


-- ============================================================================
-- PRODUCTS WITH MULTI-UNIT CONFIGURATIONS
-- ============================================================================

-- Product 1: Coca Cola (PALLET → CARTON → BOTTLE)
INSERT INTO products (
    product_id, name, description, price, org_id, barcode, brand,
    master_unit_2, units_per_master_2,
    master_unit, units_per_master,
    base_unit,
    isDeleted, updatedAt
)
SELECT
    uuid_generate_v7(),
    'Coca Cola 500ml',
    'Coca Cola soft drink 500ml bottles',
    1.50, -- price per bottle
    org_id,
    'CC500ML',
    'Coca-Cola',
    'PALLET',  -- Level 2 (highest)
    48,        -- 1 pallet = 48 cartons
    'CARTON',  -- Level 1 (middle)
    24,        -- 1 carton = 24 bottles
    'BOTTLE',  -- Base unit
    FALSE,
    now()
FROM test_context
ON CONFLICT DO NOTHING;

-- Product 2: FMF Rice (PALLET → BAG → KG)
INSERT INTO products (
    product_id, name, description, price, org_id, barcode, brand,
    master_unit_2, units_per_master_2,
    master_unit, units_per_master,
    base_unit,
    isDeleted, updatedAt
)
SELECT
    uuid_generate_v7(),
    'FMF Sungrown Rice',
    'Premium quality rice 10kg bags',
    12.75, -- price per bag
    org_id,
    'FMF-RICE-10KG',
    'FMF',
    'PALLET',  -- Level 2 (highest)
    100,       -- 1 pallet = 100 bags
    'BAG',     -- Level 1 (middle) - 10kg each
    10,        -- 1 bag = 10 kg
    'KG',      -- Base unit
    FALSE,
    now()
FROM test_context
ON CONFLICT DO NOTHING;

-- Product 3: Cooking Oil (PALLET → CARTON → BOTTLE)
INSERT INTO products (
    product_id, name, description, price, org_id, barcode, brand,
    master_unit_2, units_per_master_2,
    master_unit, units_per_master,
    base_unit,
    isDeleted, updatedAt
)
SELECT
    uuid_generate_v7(),
    'Vegetable Cooking Oil 1L',
    'Pure vegetable cooking oil',
    4.50, -- price per bottle
    org_id,
    'COOKOIL-1L',
    'PacificCook',
    'PALLET',  -- Level 2 (highest)
    60,        -- 1 pallet = 60 cartons
    'CARTON',  -- Level 1 (middle)
    12,        -- 1 carton = 12 bottles
    'BOTTLE',  -- Base unit
    FALSE,
    now()
FROM test_context
ON CONFLICT DO NOTHING;

-- Product 4: Detergent Powder (PALLET → BOX → SACHET)
INSERT INTO products (
    product_id, name, description, price, org_id, barcode, brand,
    master_unit_2, units_per_master_2,
    master_unit, units_per_master,
    base_unit,
    isDeleted, updatedAt
)
SELECT
    uuid_generate_v7(),
    'SuperClean Detergent',
    'Laundry detergent powder sachets',
    0.75, -- price per sachet
    org_id,
    'DETERG-50G',
    'SuperClean',
    'PALLET',  -- Level 2 (highest)
    50,        -- 1 pallet = 50 boxes
    'BOX',     -- Level 1 (middle)
    100,       -- 1 box = 100 sachets
    'SACHET',  -- Base unit
    FALSE,
    now()
FROM test_context
ON CONFLICT DO NOTHING;

-- Product 5: Instant Noodles (PALLET → CARTON → PACK)
INSERT INTO products (
    product_id, name, description, price, org_id, barcode, brand,
    master_unit_2, units_per_master_2,
    master_unit, units_per_master,
    base_unit,
    isDeleted, updatedAt
)
SELECT
    uuid_generate_v7(),
    'Quick Noodles Chicken Flavor',
    'Instant noodles with chicken seasoning',
    0.50, -- price per pack
    org_id,
    'NOODLE-CHKN',
    'QuickMeal',
    'PALLET',  -- Level 2 (highest)
    80,        -- 1 pallet = 80 cartons
    'CARTON',  -- Level 1 (middle)
    40,        -- 1 carton = 40 packs
    'PACK',    -- Base unit
    FALSE,
    now()
FROM test_context
ON CONFLICT DO NOTHING;


-- ============================================================================
-- PURCHASE ORDERS
-- ============================================================================

-- PO #1: Large order for Coca Cola (DRAFT status)
INSERT INTO purchase_orders (
    purchase_order_id, order_number, vendor_id, org_id, branch_id,
    order_date, expected_delivery_date, status, total_amount,
    notes, created_by, isDeleted, updatedAt
)
SELECT
    uuid_generate_v7(),
    'PO-2024-001',
    vendor1_id,
    org_id,
    branch_warehouse_id,
    '2024-10-01'::date,
    '2024-10-15'::date,
    'draft',
    72000.00, -- Will be calculated from lines
    'Initial stock order for Coca Cola - deliver to Warehouse',
    user_id,
    FALSE,
    now()
FROM test_context;

-- PO #1 Lines
INSERT INTO purchase_order_lines (
    purchase_order_line_id, purchase_order_id, product_id,
    line_number, quantity_ordered, unit_type, unit_price,
    quantity_received, org_id, isDeleted, updatedAt
)
SELECT
    uuid_generate_v7(),
    po.purchase_order_id,
    p.product_id,
    1,               -- Line 1
    50.00,           -- 50 pallets
    'PALLET',
    1440.00,         -- $1440 per pallet (48 cartons × 24 bottles × $1.50 - bulk discount)
    0.00,            -- Not received yet
    tc.org_id,
    FALSE,
    now()
FROM test_context tc
CROSS JOIN purchase_orders po
CROSS JOIN products p
WHERE po.order_number = 'PO-2024-001'
  AND p.name = 'Coca Cola 500ml';


-- PO #2: Rice order (CONFIRMED, partially received)
INSERT INTO purchase_orders (
    purchase_order_id, order_number, vendor_id, org_id, branch_id,
    order_date, expected_delivery_date, status, total_amount,
    notes, created_by, approved_by, approved_at, isDeleted, updatedAt
)
SELECT
    uuid_generate_v7(),
    'PO-2024-002',
    vendor2_id,
    org_id,
    branch_warehouse_id,
    '2024-09-15'::date,
    '2024-10-01'::date,
    'confirmed',
    127500.00,
    'Rice stock for Q4 - deliver to Warehouse',
    user_id,
    user_id,
    '2024-09-16 10:30:00'::timestamptz,
    FALSE,
    now()
FROM test_context;

-- PO #2 Lines - Rice
INSERT INTO purchase_order_lines (
    purchase_order_line_id, purchase_order_id, product_id,
    line_number, quantity_ordered, unit_type, unit_price,
    quantity_received, org_id, isDeleted, updatedAt
)
SELECT
    uuid_generate_v7(),
    po.purchase_order_id,
    p.product_id,
    1,               -- Line 1
    100.00,          -- 100 pallets
    'PALLET',
    1275.00,         -- $1275 per pallet (100 bags × 10kg × $1.275 per kg)
    30.00,           -- 30 pallets received (partial)
    tc.org_id,
    FALSE,
    now()
FROM test_context tc
CROSS JOIN purchase_orders po
CROSS JOIN products p
WHERE po.order_number = 'PO-2024-002'
  AND p.name = 'FMF Sungrown Rice';


-- PO #3: Mixed order (CONFIRMED)
INSERT INTO purchase_orders (
    purchase_order_id, order_number, vendor_id, org_id, branch_id,
    order_date, expected_delivery_date, status, total_amount,
    notes, created_by, approved_by, approved_at, isDeleted, updatedAt
)
SELECT
    uuid_generate_v7(),
    'PO-2024-003',
    vendor3_id,
    org_id,
    branch_main_id,
    '2024-10-10'::date,
    '2024-10-25'::date,
    'confirmed',
    0.00, -- Will update with sum of lines
    'Mixed grocery items - deliver to Main Branch',
    user_id,
    user_id,
    '2024-10-11 14:00:00'::timestamptz,
    FALSE,
    now()
FROM test_context;

-- PO #3 Line 1: Cooking Oil
INSERT INTO purchase_order_lines (
    purchase_order_line_id, purchase_order_id, product_id,
    line_number, quantity_ordered, unit_type, unit_price,
    quantity_received, org_id, isDeleted, updatedAt
)
SELECT
    uuid_generate_v7(),
    po.purchase_order_id,
    p.product_id,
    1,               -- Line 1
    20.00,           -- 20 pallets
    'PALLET',
    3240.00,         -- $3240 per pallet (60 cartons × 12 bottles × $4.50)
    0.00,
    tc.org_id,
    FALSE,
    now()
FROM test_context tc
CROSS JOIN purchase_orders po
CROSS JOIN products p
WHERE po.order_number = 'PO-2024-003'
  AND p.name = 'Vegetable Cooking Oil 1L';

-- PO #3 Line 2: Detergent
INSERT INTO purchase_order_lines (
    purchase_order_line_id, purchase_order_id, product_id,
    line_number, quantity_ordered, unit_type, unit_price,
    quantity_received, org_id, isDeleted, updatedAt
)
SELECT
    uuid_generate_v7(),
    po.purchase_order_id,
    p.product_id,
    2,               -- Line 2
    15.00,           -- 15 pallets
    'PALLET',
    3750.00,         -- $3750 per pallet (50 boxes × 100 sachets × $0.75)
    0.00,
    tc.org_id,
    FALSE,
    now()
FROM test_context tc
CROSS JOIN purchase_orders po
CROSS JOIN products p
WHERE po.order_number = 'PO-2024-003'
  AND p.name = 'SuperClean Detergent';

-- PO #3 Line 3: Noodles
INSERT INTO purchase_order_lines (
    purchase_order_line_id, purchase_order_id, product_id,
    line_number, quantity_ordered, unit_type, unit_price,
    quantity_received, org_id, isDeleted, updatedAt
)
SELECT
    uuid_generate_v7(),
    po.purchase_order_id,
    p.product_id,
    3,               -- Line 3
    25.00,           -- 25 pallets
    'PALLET',
    1600.00,         -- $1600 per pallet (80 cartons × 40 packs × $0.50)
    0.00,
    tc.org_id,
    FALSE,
    now()
FROM test_context tc
CROSS JOIN purchase_orders po
CROSS JOIN products p
WHERE po.order_number = 'PO-2024-003'
  AND p.name = 'Quick Noodles Chicken Flavor';

-- Update PO #3 total
UPDATE purchase_orders po
SET total_amount = (
    SELECT SUM(line_total)
    FROM purchase_order_lines pol
    WHERE pol.purchase_order_id = po.purchase_order_id
)
WHERE po.order_number = 'PO-2024-003';


-- ============================================================================
-- GOODS RECEIPTS
-- ============================================================================

-- GR #1: Partial receipt for PO-2024-002 (Rice) - First delivery
INSERT INTO goods_receipts (
    goods_receipt_id, purchase_order_id, org_id, branch_id,
    received_date, quantity_received, delivery_note_number, invoice_number,
    received_by, vehicle_number, notes,
    isDeleted, updatedAt
)
SELECT
    uuid_generate_v7(),
    po.purchase_order_id,
    tc.org_id,
    tc.branch_warehouse_id,
    '2024-10-05'::date,
    30,  -- 30 pallets received
    'DN-2024-0154',
    'INV-GS-2024-0891',
    tc.user_id,
    'FJ-1234',
    'First partial delivery - 30 pallets',
    FALSE,
    now()
FROM test_context tc
CROSS JOIN purchase_orders po
WHERE po.order_number = 'PO-2024-002';

-- GR #1 Line: 30 pallets of rice received
INSERT INTO goods_receipt_lines (
    goods_receipt_line_id, goods_receipt_id, purchase_order_line_id,
    product_id, quantity_received, unit_type, org_id,
    isDeleted, updatedAt
)
SELECT
    uuid_generate_v7(),
    gr.goods_receipt_id,
    pol.purchase_order_line_id,
    pol.product_id,
    30.00,           -- 30 pallets received
    'PALLET',
    tc.org_id,
    FALSE,
    now()
FROM test_context tc
CROSS JOIN goods_receipts gr
INNER JOIN purchase_orders po ON gr.purchase_order_id = po.purchase_order_id
INNER JOIN purchase_order_lines pol ON pol.purchase_order_id = po.purchase_order_id
WHERE gr.delivery_note_number = 'DN-2024-0154';

-- Create inventory records for received rice (at pallet level)
INSERT INTO inventory (
    inventory_id, product_id, branch_id, org_id,
    current_qty, unit_type, reference_table, reference_id,
    isDeleted, updatedAt
)
SELECT
    uuid_generate_v7(),
    p.product_id,
    tc.branch_warehouse_id,
    tc.org_id,
    30.00,           -- 30 pallets
    'PALLET',
    'goods_receipts',
    gr.goods_receipt_id,
    FALSE,
    now()
FROM test_context tc
CROSS JOIN goods_receipts gr
CROSS JOIN products p
WHERE gr.delivery_note_number = 'DN-2024-0154'
  AND p.name = 'FMF Sungrown Rice';

-- Create inventory transaction for the receipt
INSERT INTO inventory_txn (
    inventory_txn_id, product_id, change, unit_type, quantity_in_base_units,
    branch_id, user_id, reference_table, reference_id, org_id, reason,
    data, isDeleted, updatedAt
)
SELECT
    uuid_generate_v7(),
    p.product_id,
    30.00,           -- +30 pallets
    'PALLET',
    30000.00,        -- 30 pallets × 100 bags × 10kg = 30,000 kg
    tc.branch_warehouse_id,
    tc.user_id,
    'goods_receipts',
    gr.goods_receipt_id,
    tc.org_id,
    'goods_receipt',
    jsonb_build_object(
        'delivery_note_number', 'DN-2024-0154',
        'invoice_number', 'INV-GS-2024-0891',
        'vehicle_number', 'FJ-1234'
    ),
    FALSE,
    now()
FROM test_context tc
CROSS JOIN goods_receipts gr
CROSS JOIN products p
WHERE gr.delivery_note_number = 'DN-2024-0154'
  AND p.name = 'FMF Sungrown Rice';


-- ============================================================================
-- INVENTORY BREAKS (Breaking Bulk)
-- ============================================================================

-- Break 1: Break 5 pallets of rice into bags for retail distribution
INSERT INTO inventory_breaks (
    inventory_break_id, product_id, org_id, branch_id,
    from_unit_type, from_qty,
    to_unit_type, to_qty,
    performed_by, break_date, notes,
    isDeleted, updatedAt
)
SELECT
    uuid_generate_v7(),
    p.product_id,
    tc.org_id,
    tc.branch_warehouse_id,
    'PALLET',
    5.00,            -- Break 5 pallets
    'BAG',
    500.00,          -- Into 500 bags (5 × 100)
    tc.user_id,
    '2024-10-08'::date,
    'Breaking bulk for retail distribution',
    FALSE,
    now()
FROM test_context tc
CROSS JOIN products p
WHERE p.name = 'FMF Sungrown Rice';

-- Update inventory: reduce pallets
UPDATE inventory inv
SET 
    current_qty = current_qty - 5.00,
    updatedAt = now()
FROM test_context tc, products p
WHERE inv.product_id = p.product_id
  AND inv.branch_id = tc.branch_warehouse_id
  AND inv.unit_type = 'PALLET'
  AND p.name = 'FMF Sungrown Rice';

-- Add inventory: add bags
INSERT INTO inventory (
    inventory_id, product_id, branch_id, org_id,
    current_qty, unit_type, reference_table, reference_id,
    isDeleted, updatedAt
)
SELECT
    uuid_generate_v7(),
    p.product_id,
    tc.branch_warehouse_id,
    tc.org_id,
    500.00,          -- 500 bags
    'BAG',
    'inventory_breaks',
    ib.inventory_break_id,
    FALSE,
    now()
FROM test_context tc
CROSS JOIN products p
CROSS JOIN inventory_breaks ib
WHERE p.name = 'FMF Sungrown Rice'
  AND ib.from_unit_type = 'PALLET'
  AND ib.to_unit_type = 'BAG'
ON CONFLICT DO NOTHING;

-- Record transaction for the break (deduction)
INSERT INTO inventory_txn (
    inventory_txn_id, product_id, change, unit_type, quantity_in_base_units,
    branch_id, user_id, reference_table, reference_id, org_id, reason,
    data, isDeleted, updatedAt
)
SELECT
    uuid_generate_v7(),
    p.product_id,
    -5.00,           -- -5 pallets
    'PALLET',
    -5000.00,        -- -5 pallets × 100 bags × 10kg = -5,000 kg
    tc.branch_warehouse_id,
    tc.user_id,
    'inventory_breaks',
    ib.inventory_break_id,
    tc.org_id,
    'break_bulk_from',
    jsonb_build_object(
        'from_unit', 'PALLET',
        'to_unit', 'BAG',
        'conversion_note', '5 pallets broken into 500 bags'
    ),
    FALSE,
    now()
FROM test_context tc
CROSS JOIN products p
CROSS JOIN inventory_breaks ib
WHERE p.name = 'FMF Sungrown Rice'
  AND ib.from_unit_type = 'PALLET'
  AND ib.to_unit_type = 'BAG';

-- Record transaction for the break (addition)
INSERT INTO inventory_txn (
    inventory_txn_id, product_id, change, unit_type, quantity_in_base_units,
    branch_id, user_id, reference_table, reference_id, org_id, reason,
    data, isDeleted, updatedAt
)
SELECT
    uuid_generate_v7(),
    p.product_id,
    500.00,          -- +500 bags
    'BAG',
    5000.00,         -- +500 bags × 10kg = +5,000 kg
    tc.branch_warehouse_id,
    tc.user_id,
    'inventory_breaks',
    ib.inventory_break_id,
    tc.org_id,
    'break_bulk_to',
    jsonb_build_object(
        'from_unit', 'PALLET',
        'to_unit', 'BAG',
        'conversion_note', '5 pallets broken into 500 bags'
    ),
    FALSE,
    now()
FROM test_context tc
CROSS JOIN products p
CROSS JOIN inventory_breaks ib
WHERE p.name = 'FMF Sungrown Rice'
  AND ib.from_unit_type = 'PALLET'
  AND ib.to_unit_type = 'BAG';


-- ============================================================================
-- DISPATCH NOTES
-- ============================================================================

-- Dispatch #1: Send 200 bags of rice to Retail Store
INSERT INTO dispatch_notes (
    dispatch_note_id, dispatch_number, org_id,
    from_branch_id, to_branch_id,
    dispatch_date, vehicle_number, driver_name,
    notes, dispatched_by, isDeleted, updatedAt
)
SELECT
    uuid_generate_v7(),
    'DISP-2024-0001',
    tc.org_id,
    tc.branch_warehouse_id,
    tc.branch_retail_id,
    '2024-10-10'::date,
    'FJ-5678',
    'John Driver',
    'Rice transfer to retail store',
    tc.user_id,
    FALSE,
    now()
FROM test_context tc;

-- Dispatch #1 Lines
INSERT INTO dispatch_lines (
    dispatch_line_id, dispatch_note_id, product_id,
    dispatched_qty, unit_type, org_id,
    isDeleted, updatedAt
)
SELECT
    uuid_generate_v7(),
    dn.dispatch_note_id,
    p.product_id,
    200.00,          -- 200 bags
    'BAG',
    tc.org_id,
    FALSE,
    now()
FROM test_context tc
CROSS JOIN dispatch_notes dn
CROSS JOIN products p
WHERE dn.dispatch_number = 'DISP-2024-0001'
  AND p.name = 'FMF Sungrown Rice';

-- Update inventory at warehouse (deduct)
UPDATE inventory inv
SET 
    current_qty = current_qty - 200.00,
    updatedAt = now()
FROM test_context tc, products p
WHERE inv.product_id = p.product_id
  AND inv.branch_id = tc.branch_warehouse_id
  AND inv.unit_type = 'BAG'
  AND p.name = 'FMF Sungrown Rice';

-- Add inventory at retail store
INSERT INTO inventory (
    inventory_id, product_id, branch_id, org_id,
    current_qty, unit_type, reference_table, reference_id,
    isDeleted, updatedAt
)
SELECT
    uuid_generate_v7(),
    p.product_id,
    tc.branch_retail_id,
    tc.org_id,
    200.00,          -- 200 bags
    'BAG',
    'dispatch_notes',
    dn.dispatch_note_id,
    FALSE,
    now()
FROM test_context tc
CROSS JOIN products p
CROSS JOIN dispatch_notes dn
WHERE p.name = 'FMF Sungrown Rice'
  AND dn.dispatch_number = 'DISP-2024-0001'
ON CONFLICT DO NOTHING;

-- Record transaction for dispatch (from warehouse)
INSERT INTO inventory_txn (
    inventory_txn_id, product_id, change, unit_type, quantity_in_base_units,
    branch_id, user_id, reference_table, reference_id, org_id, reason,
    data, isDeleted, updatedAt
)
SELECT
    uuid_generate_v7(),
    p.product_id,
    -200.00,         -- -200 bags
    'BAG',
    -2000.00,        -- -200 bags × 10kg = -2,000 kg
    tc.branch_warehouse_id,
    tc.user_id,
    'dispatch_notes',
    dn.dispatch_note_id,
    tc.org_id,
    'dispatch_out',
    jsonb_build_object(
        'dispatch_number', 'DISP-2024-0001',
        'to_branch', 'Retail Store',
        'vehicle_number', 'FJ-5678'
    ),
    FALSE,
    now()
FROM test_context tc
CROSS JOIN products p
CROSS JOIN dispatch_notes dn
WHERE p.name = 'FMF Sungrown Rice'
  AND dn.dispatch_number = 'DISP-2024-0001';

-- Record transaction for dispatch (to retail store)
INSERT INTO inventory_txn (
    inventory_txn_id, product_id, change, unit_type, quantity_in_base_units,
    branch_id, user_id, reference_table, reference_id, org_id, reason,
    data, isDeleted, updatedAt
)
SELECT
    uuid_generate_v7(),
    p.product_id,
    200.00,          -- +200 bags
    'BAG',
    2000.00,         -- +200 bags × 10kg = +2,000 kg
    tc.branch_retail_id,
    tc.user_id,
    'dispatch_notes',
    dn.dispatch_note_id,
    tc.org_id,
    'dispatch_in',
    jsonb_build_object(
        'dispatch_number', 'DISP-2024-0001',
        'from_branch', 'Warehouse',
        'vehicle_number', 'FJ-5678'
    ),
    FALSE,
    now()
FROM test_context tc
CROSS JOIN products p
CROSS JOIN dispatch_notes dn
WHERE p.name = 'FMF Sungrown Rice'
  AND dn.dispatch_number = 'DISP-2024-0001';


-- ============================================================================
-- REFRESH MATERIALIZED VIEW
-- ============================================================================

REFRESH MATERIALIZED VIEW CONCURRENTLY inventory_movement_history;


-- ============================================================================
-- SUMMARY REPORT
-- ============================================================================

DO $$
DECLARE
    v_product_count int;
    v_po_count int;
    v_gr_count int;
    v_dispatch_count int;
    v_break_count int;
    v_txn_count int;
BEGIN
    SELECT COUNT(*) INTO v_product_count FROM products 
    WHERE name IN ('Coca Cola 500ml', 'FMF Sungrown Rice', 'Vegetable Cooking Oil 1L', 'SuperClean Detergent', 'Quick Noodles Chicken Flavor');
    
    SELECT COUNT(*) INTO v_po_count FROM purchase_orders 
    WHERE order_number IN ('PO-2024-001', 'PO-2024-002', 'PO-2024-003');
    
    SELECT COUNT(*) INTO v_gr_count FROM goods_receipts;
    
    SELECT COUNT(*) INTO v_dispatch_count FROM dispatch_notes;
    
    SELECT COUNT(*) INTO v_break_count FROM inventory_breaks;
    
    SELECT COUNT(*) INTO v_txn_count FROM inventory_txn 
    WHERE reason IN ('goods_receipt', 'break_bulk_from', 'break_bulk_to', 'dispatch_out', 'dispatch_in');

    RAISE NOTICE '';
    RAISE NOTICE '╔══════════════════════════════════════════════════════════════════╗';
    RAISE NOTICE '║          Phase 7 Test Data - Successfully Created!              ║';
    RAISE NOTICE '╚══════════════════════════════════════════════════════════════════╝';
    RAISE NOTICE '';
    RAISE NOTICE '📦 MULTI-UNIT PRODUCTS: % created', v_product_count;
    RAISE NOTICE '   • Coca Cola 500ml (PALLET → CARTON → BOTTLE)';
    RAISE NOTICE '   • FMF Sungrown Rice (PALLET → BAG → KG)';
    RAISE NOTICE '   • Cooking Oil (PALLET → CARTON → BOTTLE)';
    RAISE NOTICE '   • Detergent (PALLET → BOX → SACHET)';
    RAISE NOTICE '   • Noodles (PALLET → CARTON → PACK)';
    RAISE NOTICE '';
    RAISE NOTICE '📋 PURCHASE ORDERS: % created', v_po_count;
    RAISE NOTICE '   • PO-2024-001: Coca Cola (DRAFT)';
    RAISE NOTICE '   • PO-2024-002: Rice (CONFIRMED, partially received)';
    RAISE NOTICE '   • PO-2024-003: Mixed order (CONFIRMED)';
    RAISE NOTICE '';
    RAISE NOTICE '📥 GOODS RECEIPTS: % created', v_gr_count;
    RAISE NOTICE '   • DN-2024-0154: 30 pallets of rice received';
    RAISE NOTICE '';
    RAISE NOTICE '🔨 INVENTORY BREAKS: % created', v_break_count;
    RAISE NOTICE '   • 5 pallets → 500 bags of rice';
    RAISE NOTICE '';
    RAISE NOTICE '🚚 DISPATCH NOTES: % created', v_dispatch_count;
    RAISE NOTICE '   • DISP-2024-0001: 200 bags to retail store';
    RAISE NOTICE '';
    RAISE NOTICE '📊 INVENTORY TRANSACTIONS: % recorded', v_txn_count;
    RAISE NOTICE '';
    RAISE NOTICE '✅ CURRENT INVENTORY STATUS:';
    RAISE NOTICE '   • Warehouse: 25 pallets + 300 bags of rice';
    RAISE NOTICE '   • Retail Store: 200 bags of rice';
    RAISE NOTICE '';
    RAISE NOTICE '══════════════════════════════════════════════════════════════════';
    RAISE NOTICE '';
    RAISE NOTICE '🧪 TESTING SCENARIOS COVERED:';
    RAISE NOTICE '   ✓ Multi-unit product configuration';
    RAISE NOTICE '   ✓ Purchase order creation (draft & confirmed)';
    RAISE NOTICE '   ✓ Partial goods receipt';
    RAISE NOTICE '   ✓ Breaking bulk (PALLET → BAG)';
    RAISE NOTICE '   ✓ Inter-branch dispatch with unit tracking';
    RAISE NOTICE '   ✓ Complete audit trail via inventory_movement_history';
    RAISE NOTICE '';
    RAISE NOTICE '📖 NEXT STEPS:';
    RAISE NOTICE '   1. Navigate to: http://localhost/FrontEnd/Dashboard.php#PurchaseOrders';
    RAISE NOTICE '   2. View PO-2024-001, PO-2024-002, PO-2024-003';
    RAISE NOTICE '   3. Test "Receive Goods" on PO-2024-003';
    RAISE NOTICE '   4. Navigate to: #Inventory';
    RAISE NOTICE '   5. Test "Break Bulk" on rice pallets';
    RAISE NOTICE '   6. Test "Dispatch" to transfer stock';
    RAISE NOTICE '   7. Check "Movement History" for audit trail';
    RAISE NOTICE '';
    RAISE NOTICE '══════════════════════════════════════════════════════════════════';
    RAISE NOTICE '';
END $$;

-- Clean up temp table
