-- Ensure branches exist
INSERT INTO branches (name, location) 
SELECT 'Main Branch', 'Downtown' WHERE NOT EXISTS (SELECT 1 FROM branches WHERE name = 'Main Branch');
INSERT INTO branches (name, location) 
SELECT 'City Center', 'Old Town' WHERE NOT EXISTS (SELECT 1 FROM branches WHERE name = 'City Center');

-- Get branch IDs and insert products
DO $$
DECLARE
    main_id INT;
    city_id INT;
BEGIN
    SELECT id INTO main_id FROM branches WHERE name = 'Main Branch';
    SELECT id INTO city_id FROM branches WHERE name = 'City Center';

    -- Insert for Main Branch
    INSERT INTO products (name, sku, category, price, stock_quantity, unit, branch_id) VALUES
    ('Milk 1L', 'SKU001', 'Dairy', 55.00, 100, 'bottles', main_id),
    ('Bread (Slice)', 'SKU002', 'Bakery', 25.00, 50, 'packets', main_id),
    ('Eggs 1 Dozen', 'SKU003', 'Dairy', 80.00, 40, 'dozens', main_id)
    ON CONFLICT (sku) DO NOTHING;

    -- Insert for City Center
    INSERT INTO products (name, sku, category, price, stock_quantity, unit, branch_id) VALUES
    ('Basmati Rice 1kg', 'SKU004', 'Grains', 120.00, 200, 'kg', city_id),
    ('Sugar 1kg', 'SKU005', 'Essentials', 45.00, 150, 'kg', city_id),
    ('Cooking Oil 1L', 'SKU006', 'Essentials', 180.00, 80, 'bottles', city_id)
    ON CONFLICT (sku) DO NOTHING;
END $$;
