-- ============================================================
--  MJPOS – Sri Lankan Supermarket Seed Data
--  Run AFTER mjpos_database.sql
--  All prices in Sri Lankan Rupees (LKR)
-- ============================================================

USE mjpos_db;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ============================================================
-- 1. SETTINGS – LKR
-- ============================================================
INSERT INTO settings (setting_key, setting_value) VALUES
  ('shop_name',       'Super Mart Lanka'),
  ('shop_address',    'No. 45, Main Street, Colombo 03'),
  ('shop_phone',      '011-2345678'),
  ('shop_email',      'info@supermartlanka.lk'),
  ('currency_symbol', 'Rs.'),
  ('currency_code',   'LKR'),
  ('tax_name',        'VAT'),
  ('tax_percent',     '0'),
  ('invoice_prefix',  'INV'),
  ('purchase_prefix', 'PUR'),
  ('return_prefix',   'RET'),
  ('low_stock_alert', '10'),
  ('timezone',        'Asia/Colombo'),
  ('receipt_footer',  'ස්තූතියි! ආපසු එන්නට ආරාධනා කෙරේ!')
ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value);

-- ============================================================
-- 2. UNITS
-- ============================================================
INSERT INTO units (unit_name, short_name, status) VALUES
  ('Kilogram',    'kg',   'active'),
  ('Gram',        'g',    'active'),
  ('Litre',       'L',    'active'),
  ('Millilitre',  'mL',   'active'),
  ('Piece',       'pcs',  'active'),
  ('Packet',      'pkt',  'active'),
  ('Bottle',      'btl',  'active'),
  ('Box',         'box',  'active'),
  ('Tin',         'tin',  'active'),
  ('Dozen',       'doz',  'active'),
  ('Bar',         'bar',  'active'),
  ('Sachet',      'sct',  'active')
ON DUPLICATE KEY UPDATE unit_name = VALUES(unit_name);

-- ============================================================
-- 3. CATEGORIES
-- ============================================================
INSERT INTO categories (category_name, description, status) VALUES
  ('Rice & Grains',         'All rice varieties, wheat, kurakkan and other grains',  'active'),
  ('Flour & Baking',        'Wheat flour, pol flour, baking items',                  'active'),
  ('Pulses & Dhal',         'Red lentils, chickpeas, green gram, cowpea',            'active'),
  ('Spices & Condiments',   'Curry powder, chilli, turmeric, sauces, pickles',       'active'),
  ('Cooking Oil',           'Coconut oil, vegetable oil, margarine',                 'active'),
  ('Sugar, Salt & Vinegar', 'White sugar, Jaggery, salt, vinegar',                  'active'),
  ('Dairy & Eggs',          'Fresh milk, yoghurt, cheese, butter, eggs',             'active'),
  ('Beverages',             'Tea, coffee, milo, soft drinks, juices, water',         'active'),
  ('Biscuits & Snacks',     'Cream crackers, short eats, chips, wafers',             'active'),
  ('Noodles & Pasta',       'Instant noodles, egg noodles, spaghetti',               'active'),
  ('Canned & Preserved',    'Canned fish, dhal, vegetables, jams',                   'active'),
  ('Personal Care',         'Soap, shampoo, toothpaste, lotion, deodorant',          'active'),
  ('Household Cleaning',    'Washing powder, dish wash, floor cleaner, bleach',      'active'),
  ('Baby & Infant',         'Baby food, diapers, baby powder, wet wipes',            'active'),
  ('Confectionery',         'Chocolate, toffee, candy, gum',                        'active')
ON DUPLICATE KEY UPDATE description = VALUES(description);

-- ============================================================
-- 4. BRANDS
-- ============================================================
INSERT INTO brands (brand_name, status) VALUES
  ('Maliban',        'active'),  -- iconic Lankan biscuit brand
  ('Munchee',        'active'),  -- CBL snacks
  ('Kandos',         'active'),  -- chocolate
  ('Ritzbury',       'active'),  -- CBL chocolate
  ('Sunshine',       'active'),  -- crackers
  ('Tiara',          'active'),  -- biscuits
  ('Nestlé',         'active'),  -- Milo, Nescafé, KitKat
  ('Unilever',       'active'),  -- Signal, Dove, Sunlight
  ('Prima',          'active'),  -- flour, noodles
  ('Harischandra',   'active'),  -- rice, pulses, spices
  ('Elephant House', 'active'),  -- ginger beer, ice cream, dairy
  ('Kotmale',        'active'),  -- dairy, yoghurt
  ('Ambewela',       'active'),  -- fresh milk
  ('Milco',          'active'),  -- dairy
  ('Dilmah',         'active'),  -- premium Ceylon tea
  ('Lipton',         'active'),  -- tea
  ('MD',             'active'),  -- sauces, pickles, jams
  ('Larich',         'active'),  -- tomato sauce, condiments
  ('Sunflower',      'active'),  -- sunflower oil
  ('Astra',          'active'),  -- margarine, cooking fat
  ('Magic',          'active'),  -- margarine, baking
  ('Lanka Soy',      'active'),  -- soy products
  ('CIC',            'active'),  -- agri products, cooking
  ('Dettol',         'active'),  -- antiseptic, soap
  ('Harpic',         'active'),  -- toilet cleaner
  ('Surf Excel',     'active'),  -- washing powder
  ('Pears',          'active'),  -- soap
  ('Colgate',        'active'),  -- toothpaste, toothbrush
  ('Oral-B',         'active'),  -- dental care
  ('Johnson',        'active'),  -- baby products
  ('Pampers',        'active'),  -- diapers
  ('Huggies',        'active'),  -- diapers
  ('Anchor',         'active'),  -- milk powder, butter
  ('Cowbell',        'active'),  -- condensed milk
  ('Highland',       'active'),  -- milk powder
  ('Lucky',          'active'),  -- edible oil
  ('Sathosa',        'active'),  -- government brand rice/pulses
  ('Generic',        'active')   -- unbranded / house brand
ON DUPLICATE KEY UPDATE brand_name = VALUES(brand_name);

-- ============================================================
-- 5. PRODUCTS  (cost_price, selling_price in LKR)
-- Markup: ~25-35% retail margin
-- ============================================================

-- Helper: get IDs using variables for cleanliness
SET @rice_cat     = (SELECT id FROM categories WHERE category_name = 'Rice & Grains'         LIMIT 1);
SET @flour_cat    = (SELECT id FROM categories WHERE category_name = 'Flour & Baking'        LIMIT 1);
SET @pulses_cat   = (SELECT id FROM categories WHERE category_name = 'Pulses & Dhal'         LIMIT 1);
SET @spices_cat   = (SELECT id FROM categories WHERE category_name = 'Spices & Condiments'   LIMIT 1);
SET @oil_cat      = (SELECT id FROM categories WHERE category_name = 'Cooking Oil'           LIMIT 1);
SET @sugar_cat    = (SELECT id FROM categories WHERE category_name = 'Sugar, Salt & Vinegar' LIMIT 1);
SET @dairy_cat    = (SELECT id FROM categories WHERE category_name = 'Dairy & Eggs'          LIMIT 1);
SET @bev_cat      = (SELECT id FROM categories WHERE category_name = 'Beverages'             LIMIT 1);
SET @biscuit_cat  = (SELECT id FROM categories WHERE category_name = 'Biscuits & Snacks'     LIMIT 1);
SET @noodle_cat   = (SELECT id FROM categories WHERE category_name = 'Noodles & Pasta'       LIMIT 1);
SET @canned_cat   = (SELECT id FROM categories WHERE category_name = 'Canned & Preserved'    LIMIT 1);
SET @care_cat     = (SELECT id FROM categories WHERE category_name = 'Personal Care'         LIMIT 1);
SET @clean_cat    = (SELECT id FROM categories WHERE category_name = 'Household Cleaning'    LIMIT 1);
SET @baby_cat     = (SELECT id FROM categories WHERE category_name = 'Baby & Infant'         LIMIT 1);
SET @conf_cat     = (SELECT id FROM categories WHERE category_name = 'Confectionery'         LIMIT 1);

SET @kg_unit   = (SELECT id FROM units WHERE short_name = 'kg'  LIMIT 1);
SET @g_unit    = (SELECT id FROM units WHERE short_name = 'g'   LIMIT 1);
SET @L_unit    = (SELECT id FROM units WHERE short_name = 'L'   LIMIT 1);
SET @mL_unit   = (SELECT id FROM units WHERE short_name = 'mL'  LIMIT 1);
SET @pcs_unit  = (SELECT id FROM units WHERE short_name = 'pcs' LIMIT 1);
SET @pkt_unit  = (SELECT id FROM units WHERE short_name = 'pkt' LIMIT 1);
SET @btl_unit  = (SELECT id FROM units WHERE short_name = 'btl' LIMIT 1);
SET @box_unit  = (SELECT id FROM units WHERE short_name = 'box' LIMIT 1);
SET @tin_unit  = (SELECT id FROM units WHERE short_name = 'tin' LIMIT 1);
SET @bar_unit  = (SELECT id FROM units WHERE short_name = 'bar' LIMIT 1);
SET @sct_unit  = (SELECT id FROM units WHERE short_name = 'sct' LIMIT 1);

SET @hari_b    = (SELECT id FROM brands WHERE brand_name = 'Harischandra' LIMIT 1);
SET @prima_b   = (SELECT id FROM brands WHERE brand_name = 'Prima'        LIMIT 1);
SET @sath_b    = (SELECT id FROM brands WHERE brand_name = 'Sathosa'      LIMIT 1);
SET @gen_b     = (SELECT id FROM brands WHERE brand_name = 'Generic'      LIMIT 1);
SET @md_b      = (SELECT id FROM brands WHERE brand_name = 'MD'           LIMIT 1);
SET @larich_b  = (SELECT id FROM brands WHERE brand_name = 'Larich'       LIMIT 1);
SET @lucky_b   = (SELECT id FROM brands WHERE brand_name = 'Lucky'        LIMIT 1);
SET @sunf_b    = (SELECT id FROM brands WHERE brand_name = 'Sunflower'    LIMIT 1);
SET @astra_b   = (SELECT id FROM brands WHERE brand_name = 'Astra'        LIMIT 1);
SET @magic_b   = (SELECT id FROM brands WHERE brand_name = 'Magic'        LIMIT 1);
SET @nestle_b  = (SELECT id FROM brands WHERE brand_name = 'Nestlé'       LIMIT 1);
SET @dilmah_b  = (SELECT id FROM brands WHERE brand_name = 'Dilmah'       LIMIT 1);
SET @lipton_b  = (SELECT id FROM brands WHERE brand_name = 'Lipton'       LIMIT 1);
SET @eleph_b   = (SELECT id FROM brands WHERE brand_name = 'Elephant House' LIMIT 1);
SET @kotmal_b  = (SELECT id FROM brands WHERE brand_name = 'Kotmale'      LIMIT 1);
SET @ambew_b   = (SELECT id FROM brands WHERE brand_name = 'Ambewela'     LIMIT 1);
SET @anchor_b  = (SELECT id FROM brands WHERE brand_name = 'Anchor'       LIMIT 1);
SET @maliban_b = (SELECT id FROM brands WHERE brand_name = 'Maliban'      LIMIT 1);
SET @munch_b   = (SELECT id FROM brands WHERE brand_name = 'Munchee'      LIMIT 1);
SET @sun_b     = (SELECT id FROM brands WHERE brand_name = 'Sunshine'     LIMIT 1);
SET @tiara_b   = (SELECT id FROM brands WHERE brand_name = 'Tiara'        LIMIT 1);
SET @kandos_b  = (SELECT id FROM brands WHERE brand_name = 'Kandos'       LIMIT 1);
SET @ritz_b    = (SELECT id FROM brands WHERE brand_name = 'Ritzbury'     LIMIT 1);
SET @colg_b    = (SELECT id FROM brands WHERE brand_name = 'Colgate'      LIMIT 1);
SET @oralb_b   = (SELECT id FROM brands WHERE brand_name = 'Oral-B'       LIMIT 1);
SET @uni_b     = (SELECT id FROM brands WHERE brand_name = 'Unilever'     LIMIT 1);
SET @dettol_b  = (SELECT id FROM brands WHERE brand_name = 'Dettol'       LIMIT 1);
SET @harpic_b  = (SELECT id FROM brands WHERE brand_name = 'Harpic'       LIMIT 1);
SET @surf_b    = (SELECT id FROM brands WHERE brand_name = 'Surf Excel'    LIMIT 1);
SET @john_b    = (SELECT id FROM brands WHERE brand_name = 'Johnson'       LIMIT 1);
SET @pampers_b = (SELECT id FROM brands WHERE brand_name = 'Pampers'       LIMIT 1);
SET @huggies_b = (SELECT id FROM brands WHERE brand_name = 'Huggies'       LIMIT 1);
SET @cowbell_b = (SELECT id FROM brands WHERE brand_name = 'Cowbell'       LIMIT 1);

INSERT INTO products
  (product_code, product_name, category_id, brand_id, unit_id,
   cost_price, selling_price, tax_percent,
   current_stock, reorder_level, status)
VALUES

-- ── RICE & GRAINS ──────────────────────────────────────────
('RG001','Samba Rice 5kg',          @rice_cat,@hari_b,  @kg_unit,  950.00, 1190.00, 0, 80,  20, 'active'),
('RG002','Keeri Samba Rice 5kg',    @rice_cat,@hari_b,  @kg_unit, 1050.00, 1320.00, 0, 60,  15, 'active'),
('RG003','Red Basmati Rice 1kg',    @rice_cat,@gen_b,   @kg_unit,  280.00,  350.00, 0, 50,  10, 'active'),
('RG004','Nadu Rice 5kg',           @rice_cat,@sath_b,  @kg_unit,  850.00, 1050.00, 0, 100, 25, 'active'),
('RG005','Samba Rice 1kg (Loose)',  @rice_cat,@gen_b,   @kg_unit,  190.00,  240.00, 0, 200, 50, 'active'),
('RG006','Red Rice 1kg',            @rice_cat,@gen_b,   @kg_unit,  200.00,  250.00, 0, 80,  20, 'active'),
('RG007','Kurakkan Flour 500g',     @rice_cat,@hari_b,  @g_unit,   130.00,  165.00, 0, 40,  10, 'active'),
('RG008','Oats 500g (Quick Cook)',  @rice_cat,@gen_b,   @g_unit,   220.00,  280.00, 0, 35,  10, 'active'),
('RG009','Corn Flour 400g',         @rice_cat,@prima_b, @g_unit,   145.00,  185.00, 0, 30,  8,  'active'),
('RG010','Semolina (Ravai) 500g',   @rice_cat,@gen_b,   @g_unit,   110.00,  140.00, 0, 40,  10, 'active'),

-- ── FLOUR & BAKING ─────────────────────────────────────────
('FB001','Wheat Flour 1kg',         @flour_cat,@prima_b,@kg_unit,  175.00,  220.00, 0, 150, 30, 'active'),
('FB002','Wheat Flour 5kg',         @flour_cat,@prima_b,@kg_unit,  850.00, 1050.00, 0, 60,  15, 'active'),
('FB003','Self Raising Flour 1kg',  @flour_cat,@prima_b,@kg_unit,  200.00,  255.00, 0, 50,  10, 'active'),
('FB004','Bread Flour 1kg',         @flour_cat,@prima_b,@kg_unit,  195.00,  248.00, 0, 40,  10, 'active'),
('FB005','Pol Piti (Coconut Flour) 250g',@flour_cat,@gen_b,@g_unit,90.00,  115.00, 0, 30,  8,  'active'),
('FB006','Baking Powder 100g',      @flour_cat,@gen_b,  @g_unit,   80.00,   105.00, 0, 25,  5,  'active'),
('FB007','Vanilla Essence 50mL',    @flour_cat,@gen_b,  @mL_unit,  95.00,   125.00, 0, 20,  5,  'active'),
('FB008','Margarine Astra 200g',    @flour_cat,@astra_b,@g_unit,  165.00,   210.00, 0, 40,  10, 'active'),
('FB009','Magic Margarine 250g',    @flour_cat,@magic_b,@g_unit,  170.00,   215.00, 0, 35,  10, 'active'),

-- ── PULSES & DHAL ──────────────────────────────────────────
('PD001','Red Lentils (Dhal) 500g', @pulses_cat,@hari_b,@g_unit,  220.00,  275.00, 0, 80,  20, 'active'),
('PD002','Red Lentils 1kg',         @pulses_cat,@hari_b,@kg_unit,  420.00,  530.00, 0, 60,  15, 'active'),
('PD003','Green Gram (Mung) 500g',  @pulses_cat,@gen_b, @g_unit,  230.00,  290.00, 0, 50,  12, 'active'),
('PD004','Chickpeas 500g',          @pulses_cat,@gen_b, @g_unit,  210.00,  265.00, 0, 40,  10, 'active'),
('PD005','Black-eyed Cowpea 500g',  @pulses_cat,@gen_b, @g_unit,  200.00,  255.00, 0, 35,  8,  'active'),
('PD006','Soya Meat 90g',           @pulses_cat,@gen_b, @g_unit,   95.00,  125.00, 0, 60,  15, 'active'),

-- ── SPICES & CONDIMENTS ────────────────────────────────────
('SC001','Roasted Curry Powder 100g',  @spices_cat,@hari_b, @g_unit, 110.00, 145.00, 0, 60, 15, 'active'),
('SC002','Unroasted Curry Powder 100g',@spices_cat,@hari_b, @g_unit, 100.00, 135.00, 0, 60, 15, 'active'),
('SC003','Chilli Powder 100g',         @spices_cat,@hari_b, @g_unit,  90.00, 120.00, 0, 50, 12, 'active'),
('SC004','Turmeric Powder 100g',       @spices_cat,@hari_b, @g_unit,  80.00, 105.00, 0, 50, 12, 'active'),
('SC005','Pepper Powder 50g',          @spices_cat,@hari_b, @g_unit, 130.00, 165.00, 0, 40, 10, 'active'),
('SC006','Cinnamon Sticks 50g',        @spices_cat,@gen_b,  @g_unit,  95.00, 125.00, 0, 30,  8, 'active'),
('SC007','Cardamom Pods 25g',          @spices_cat,@gen_b,  @g_unit, 200.00, 255.00, 0, 25,  5, 'active'),
('SC008','MD Tomato Sauce 400g',       @spices_cat,@md_b,   @g_unit, 190.00, 245.00, 0, 45, 10, 'active'),
('SC009','MD Chilli Sauce 200mL',      @spices_cat,@md_b,   @mL_unit,150.00, 195.00, 0, 40, 10, 'active'),
('SC010','MD Soya Sauce 200mL',        @spices_cat,@md_b,   @mL_unit,125.00, 160.00, 0, 35, 8,  'active'),
('SC011','Larich Tomato Ketchup 340g', @spices_cat,@larich_b,@g_unit,155.00, 200.00, 0, 40, 10, 'active'),
('SC012','Mustard Paste 100g',         @spices_cat,@gen_b,  @g_unit,  70.00,  90.00, 0, 25,  5, 'active'),
('SC013','Salt (Iodised) 500g',        @sugar_cat, @gen_b,  @g_unit,  40.00,  55.00, 0, 100,25, 'active'),
('SC014','Maldive Fish 100g',          @spices_cat,@gen_b,  @g_unit, 350.00, 450.00, 0, 30,  8, 'active'),

-- ── COOKING OIL ────────────────────────────────────────────
('CO001','Coconut Oil 1L',            @oil_cat,@gen_b,   @L_unit,  820.00, 1050.00, 0, 60, 15, 'active'),
('CO002','Coconut Oil 750mL',         @oil_cat,@gen_b,   @mL_unit, 630.00,  800.00, 0, 50, 12, 'active'),
('CO003','Sunflower Oil 1L',          @oil_cat,@sunf_b,  @L_unit,  480.00,  615.00, 0, 60, 15, 'active'),
('CO004','Lucky Vegetable Oil 1L',    @oil_cat,@lucky_b, @L_unit,  450.00,  575.00, 0, 60, 15, 'active'),
('CO005','Lucky Palm Oil 1L',         @oil_cat,@lucky_b, @L_unit,  420.00,  540.00, 0, 50, 12, 'active'),
('CO006','Margarine Astra 500g',      @oil_cat,@astra_b, @g_unit,  380.00,  490.00, 0, 35, 10, 'active'),

-- ── SUGAR, SALT & VINEGAR ──────────────────────────────────
('SS001','White Sugar 1kg',           @sugar_cat,@gen_b, @kg_unit, 195.00,  250.00, 0, 150,35, 'active'),
('SS002','White Sugar 5kg',           @sugar_cat,@gen_b, @kg_unit, 950.00, 1210.00, 0, 60, 15, 'active'),
('SS003','Jaggery (Hakuru) 250g',     @sugar_cat,@gen_b, @g_unit,  120.00,  155.00, 0, 40, 10, 'active'),
('SS004','Coconut Treacle 500mL',     @sugar_cat,@gen_b, @mL_unit, 250.00,  320.00, 0, 30,  8, 'active'),
('SS005','White Vinegar 400mL',       @sugar_cat,@gen_b, @mL_unit,  80.00,  105.00, 0, 30,  8, 'active'),
('SS006','Salt Fine 1kg',             @sugar_cat,@gen_b, @kg_unit,  60.00,   80.00, 0, 80, 20, 'active'),

-- ── DAIRY & EGGS ───────────────────────────────────────────
('DA001','Kotmale Yoghurt Plain 80g', @dairy_cat,@kotmal_b,@g_unit,  60.00,   80.00, 0, 50, 15, 'active'),
('DA002','Kotmale Yoghurt Fruit 80g', @dairy_cat,@kotmal_b,@g_unit,  65.00,   85.00, 0, 50, 15, 'active'),
('DA003','Ambewela Fresh Milk 1L',    @dairy_cat,@ambew_b, @L_unit, 195.00,  250.00, 0, 40, 10, 'active'),
('DA004','Milco Full Cream Milk 200mL',@dairy_cat,@gen_b,  @mL_unit, 70.00,   90.00, 0, 80, 20, 'active'),
('DA005','Anchor Butter 227g',        @dairy_cat,@anchor_b,@g_unit, 580.00,  740.00, 0, 25,  8, 'active'),
('DA006','Anchor Milk Powder 400g',   @dairy_cat,@anchor_b,@g_unit, 850.00, 1075.00, 0, 30,  8, 'active'),
('DA007','Highland Milk Powder 400g', @dairy_cat,@gen_b,   @g_unit, 820.00, 1040.00, 0, 30,  8, 'active'),
('DA008','Cowbell Condensed Milk 397g',@dairy_cat,@cowbell_b,@tin_unit,280.00,360.00, 0,35,  8, 'active'),
('DA009','Eggs (Tray of 30)',         @dairy_cat,@gen_b,   @pcs_unit,990.00,1250.00, 0, 20,  5, 'active'),
('DA010','Eggs (Pack of 6)',          @dairy_cat,@gen_b,   @pcs_unit,195.00, 250.00, 0, 40, 10, 'active'),

-- ── BEVERAGES ──────────────────────────────────────────────
('BV001','Dilmah Tea Bags 100s',      @bev_cat,@dilmah_b,@box_unit, 680.00,  870.00, 0, 40, 10, 'active'),
('BV002','Dilmah Tea Bags 50s',       @bev_cat,@dilmah_b,@box_unit, 360.00,  460.00, 0, 40, 10, 'active'),
('BV003','Lipton Tea Bags 100s',      @bev_cat,@lipton_b,@box_unit, 630.00,  810.00, 0, 35, 10, 'active'),
('BV004','Harischandra Orange Pekoe 200g',@bev_cat,@hari_b,@g_unit,310.00, 395.00, 0, 35, 10, 'active'),
('BV005','Nescafé Classic 50g',       @bev_cat,@nestle_b,@g_unit,  395.00,  505.00, 0, 40, 10, 'active'),
('BV006','Nescafé Classic 200g',      @bev_cat,@nestle_b,@g_unit, 1480.00, 1890.00, 0, 20,  5, 'active'),
('BV007','Milo 200g Sachet',          @bev_cat,@nestle_b,@g_unit,  370.00,  475.00, 0, 50, 12, 'active'),
('BV008','Milo 400g Tin',             @bev_cat,@nestle_b,@tin_unit,780.00,  995.00, 0, 35, 10, 'active'),
('BV009','Milo 1kg Stand-Up Pouch',   @bev_cat,@nestle_b,@g_unit, 1760.00, 2250.00, 0, 20,  5, 'active'),
('BV010','Elephant Ginger Beer 400mL',@bev_cat,@eleph_b, @btl_unit,100.00,  135.00, 0, 80, 20, 'active'),
('BV011','Elephant Cream Soda 400mL', @bev_cat,@eleph_b, @btl_unit, 95.00,  125.00, 0, 80, 20, 'active'),
('BV012','Coca-Cola 400mL',           @bev_cat,@gen_b,   @btl_unit,100.00,  135.00, 0, 100,25, 'active'),
('BV013','Sprite 400mL',              @bev_cat,@gen_b,   @btl_unit, 95.00,  130.00, 0, 80, 20, 'active'),
('BV014','Mineral Water 500mL',       @bev_cat,@gen_b,   @btl_unit, 50.00,   70.00, 0, 150,35, 'active'),
('BV015','Mineral Water 1.5L',        @bev_cat,@gen_b,   @btl_unit,100.00,  135.00, 0, 80, 20, 'active'),
('BV016','Orange Juice 200mL (Tetra)',@bev_cat,@gen_b,   @mL_unit,  95.00,  125.00, 0, 60, 15, 'active'),

-- ── BISCUITS & SNACKS ──────────────────────────────────────
('BS001','Maliban Cream Cracker 200g',  @biscuit_cat,@maliban_b,@g_unit, 130.00, 170.00, 0, 60,15, 'active'),
('BS002','Maliban Chocolate Puff 90g',  @biscuit_cat,@maliban_b,@g_unit, 100.00, 130.00, 0, 50,12, 'active'),
('BS003','Maliban Marie 200g',          @biscuit_cat,@maliban_b,@g_unit, 120.00, 155.00, 0, 50,12, 'active'),
('BS004','Munchee Super Cream Cracker 180g',@biscuit_cat,@munch_b,@g_unit,125.00,165.00, 0,55,15, 'active'),
('BS005','Munchee Lemon Puff 100g',     @biscuit_cat,@munch_b,@g_unit,   95.00, 125.00, 0, 60,15, 'active'),
('BS006','Munchee Chocolate Biscuit 100g',@biscuit_cat,@munch_b,@g_unit,100.00, 130.00, 0, 55,12, 'active'),
('BS007','Sunshine Cream Cracker 175g', @biscuit_cat,@sun_b,  @g_unit,  115.00, 150.00, 0, 50,12, 'active'),
('BS008','Tiara Butter Cookies 200g',   @biscuit_cat,@tiara_b,@g_unit,  145.00, 190.00, 0, 40,10, 'active'),
('BS009','Masala Cashew 100g',          @biscuit_cat,@gen_b,  @g_unit,  280.00, 360.00, 0, 30, 8, 'active'),
('BS010','Lays Chips Masala 78g',       @biscuit_cat,@gen_b,  @g_unit,  170.00, 220.00, 0, 40,10, 'active'),
('BS011','Potato Chips (Local) 50g',    @biscuit_cat,@gen_b,  @g_unit,   65.00,  85.00, 0, 60,15, 'active'),

-- ── NOODLES & PASTA ────────────────────────────────────────
('NP001','Prima Kottu Noodles 400g',  @noodle_cat,@prima_b,@g_unit,  175.00, 225.00, 0, 60,15, 'active'),
('NP002','Prima Instant Noodles Chicken 75g',@noodle_cat,@prima_b,@g_unit,65.00, 85.00, 0, 100,25,'active'),
('NP003','Prima Instant Noodles Masala 75g', @noodle_cat,@prima_b,@g_unit,65.00, 85.00, 0, 100,25,'active'),
('NP004','Maggi Noodles Chicken 77g',  @noodle_cat,@nestle_b,@g_unit,  70.00,  92.00, 0, 80,20, 'active'),
('NP005','Glass Noodles 100g',         @noodle_cat,@gen_b,  @g_unit,   90.00, 118.00, 0, 40,10, 'active'),
('NP006','Egg Noodles 200g',           @noodle_cat,@gen_b,  @g_unit,  120.00, 155.00, 0, 40,10, 'active'),
('NP007','Spaghetti 500g',             @noodle_cat,@prima_b,@g_unit,  185.00, 240.00, 0, 30, 8, 'active'),

-- ── CANNED & PRESERVED ─────────────────────────────────────
('CP001','Canned Sardines in Brine 425g',  @canned_cat,@gen_b, @tin_unit,265.00, 340.00, 0, 50,12, 'active'),
('CP002','Canned Mackerel in Tomato 425g', @canned_cat,@gen_b, @tin_unit,280.00, 360.00, 0, 50,12, 'active'),
('CP003','Canned Tuna in Brine 185g',      @canned_cat,@gen_b, @tin_unit,245.00, 315.00, 0, 60,15, 'active'),
('CP004','MD Mango Jam 500g',              @canned_cat,@md_b,  @g_unit,  280.00, 360.00, 0, 30, 8, 'active'),
('CP005','MD Pineapple Jam 500g',          @canned_cat,@md_b,  @g_unit,  275.00, 355.00, 0, 30, 8, 'active'),
('CP006','Canned Chickpeas 400g',          @canned_cat,@gen_b, @tin_unit,160.00, 210.00, 0, 35,10, 'active'),
('CP007','Canned Mixed Vegetables 400g',   @canned_cat,@gen_b, @tin_unit,195.00, 250.00, 0, 30, 8, 'active'),
('CP008','Coconut Milk 400mL Tin',         @canned_cat,@gen_b, @tin_unit,175.00, 225.00, 0, 50,12, 'active'),

-- ── PERSONAL CARE ──────────────────────────────────────────
('PC001','Colgate Strong Teeth 175g',      @care_cat,@colg_b, @g_unit,  195.00, 250.00, 0, 50,12, 'active'),
('PC002','Colgate MaxFresh 150g',          @care_cat,@colg_b, @g_unit,  220.00, 285.00, 0, 40,10, 'active'),
('PC003','Oral-B Toothbrush (Medium)',     @care_cat,@oralb_b,@pcs_unit,135.00, 175.00, 0, 40,10, 'active'),
('PC004','Lifebuoy Total 10 Soap 125g',    @care_cat,@uni_b,  @bar_unit, 95.00, 125.00, 0, 80,20, 'active'),
('PC005','Dettol Soap Original 125g',      @care_cat,@dettol_b,@bar_unit,120.00,155.00, 0, 60,15, 'active'),
('PC006','Dove Soap 100g',                 @care_cat,@uni_b,  @bar_unit,165.00, 215.00, 0, 50,12, 'active'),
('PC007','Sunsilk Shampoo 340mL',          @care_cat,@uni_b,  @mL_unit, 480.00, 620.00, 0, 30, 8, 'active'),
('PC008','Pantene Shampoo 340mL',          @care_cat,@gen_b,  @mL_unit, 520.00, 670.00, 0, 30, 8, 'active'),
('PC009','Vaseline Lotion 200mL',          @care_cat,@uni_b,  @mL_unit, 340.00, 440.00, 0, 35,10, 'active'),
('PC010','Signal Toothpaste 160g',         @care_cat,@uni_b,  @g_unit,  175.00, 225.00, 0, 40,10, 'active'),
('PC011','Dettol Handwash 200mL',          @care_cat,@dettol_b,@mL_unit,295.00, 380.00, 0, 40,10, 'active'),

-- ── HOUSEHOLD CLEANING ─────────────────────────────────────
('HC001','Surf Excel Blue 1kg',            @clean_cat,@surf_b,@kg_unit,  420.00, 545.00, 0, 40,10, 'active'),
('HC002','Surf Excel Blue 500g',           @clean_cat,@surf_b,@g_unit,   225.00, 290.00, 0, 50,12, 'active'),
('HC003','Sunlight Dish Wash 300mL',       @clean_cat,@uni_b, @mL_unit,  175.00, 225.00, 0, 50,12, 'active'),
('HC004','Harpic Toilet Cleaner 500mL',    @clean_cat,@harpic_b,@mL_unit,295.00,380.00, 0, 30, 8, 'active'),
('HC005','Dettol Multi-Surface 500mL',     @clean_cat,@dettol_b,@mL_unit,380.00,490.00, 0, 25, 6, 'active'),
('HC006','Bleach (Clorox) 1L',             @clean_cat,@gen_b, @L_unit,   195.00, 255.00, 0, 35,10, 'active'),
('HC007','Floor Cleaner Lavender 1L',      @clean_cat,@gen_b, @L_unit,   175.00, 225.00, 0, 30, 8, 'active'),
('HC008','Vim Dishwash Paste 400g',        @clean_cat,@uni_b, @g_unit,   185.00, 240.00, 0, 40,10, 'active'),
('HC009','Mosquito Coil (Box 12 coils)',   @clean_cat,@gen_b, @box_unit, 145.00, 190.00, 0, 50,12, 'active'),
('HC010','Garbage Bags 10pcs Medium',      @clean_cat,@gen_b, @pkt_unit,  90.00, 120.00, 0, 60,15, 'active'),

-- ── BABY & INFANT ──────────────────────────────────────────
('BI001','Pampers Active Baby S (30 pcs)', @baby_cat,@pampers_b,@pcs_unit,880.00,1120.00, 0, 20,5, 'active'),
('BI002','Pampers Active Baby M (28 pcs)', @baby_cat,@pampers_b,@pcs_unit,890.00,1135.00, 0, 20,5, 'active'),
('BI003','Huggies Ultra Dry S (30 pcs)',   @baby_cat,@huggies_b,@pcs_unit,850.00,1085.00, 0, 20,5, 'active'),
('BI004','Johnson Baby Powder 200g',       @baby_cat,@john_b, @g_unit,  295.00,  380.00, 0, 25,6, 'active'),
('BI005','Johnson Baby Shampoo 200mL',     @baby_cat,@john_b, @mL_unit, 380.00,  490.00, 0, 20,5, 'active'),
('BI006','Johnson Baby Oil 200mL',         @baby_cat,@john_b, @mL_unit, 360.00,  465.00, 0, 20,5, 'active'),
('BI007','Nestlé Cerelac Rice 400g',       @baby_cat,@nestle_b,@g_unit, 780.00,  995.00, 0, 15,4, 'active'),
('BI008','Wet Wipes 80s',                  @baby_cat,@gen_b,  @pcs_unit,195.00,  250.00, 0, 30,8, 'active'),

-- ── CONFECTIONERY ──────────────────────────────────────────
('CF001','Kandos Milk Chocolate 100g',     @conf_cat,@kandos_b,@g_unit, 225.00, 295.00, 0, 40,10, 'active'),
('CF002','Kandos Dark Chocolate 90g',      @conf_cat,@kandos_b,@g_unit, 210.00, 275.00, 0, 35,10, 'active'),
('CF003','Ritzbury Cashew Crunch 90g',     @conf_cat,@ritz_b, @g_unit, 220.00, 285.00, 0, 35,10, 'active'),
('CF004','Ritzbury Peanut Crunch 100g',    @conf_cat,@ritz_b, @g_unit, 185.00, 240.00, 0, 40,10, 'active'),
('CF005','Eclairs Toffee (25 pcs)',        @conf_cat,@gen_b,  @pcs_unit, 90.00, 120.00, 0, 60,15, 'active'),
('CF006','Chewing Gum Spearmint 12s',      @conf_cat,@gen_b,  @pcs_unit, 65.00,  85.00, 0, 50,12, 'active'),
('CF007','Polo Mints Roll',                @conf_cat,@gen_b,  @pcs_unit, 55.00,  72.00, 0, 60,15, 'active'),
('CF008','Kit Kat 45g',                    @conf_cat,@nestle_b,@g_unit, 175.00, 228.00, 0, 50,12, 'active');

-- ============================================================
-- 6. STOCK MOVEMENTS – opening stock entries
-- ============================================================
INSERT INTO stock_movements
  (product_id, movement_type, quantity_in, quantity_out, stock_after,
   reference_no, note, created_by)
SELECT
  id,
  'opening',
  current_stock,
  0,
  current_stock,
  'OPEN-2025',
  'Opening stock – Sri Lankan supermarket setup',
  1
FROM products
WHERE product_code IN (
  'RG001','RG002','RG003','RG004','RG005','RG006','RG007','RG008','RG009','RG010',
  'FB001','FB002','FB003','FB004','FB005','FB006','FB007','FB008','FB009',
  'PD001','PD002','PD003','PD004','PD005','PD006',
  'SC001','SC002','SC003','SC004','SC005','SC006','SC007','SC008','SC009',
  'SC010','SC011','SC012','SC013','SC014',
  'CO001','CO002','CO003','CO004','CO005','CO006',
  'SS001','SS002','SS003','SS004','SS005','SS006',
  'DA001','DA002','DA003','DA004','DA005','DA006','DA007','DA008','DA009','DA010',
  'BV001','BV002','BV003','BV004','BV005','BV006','BV007','BV008','BV009',
  'BV010','BV011','BV012','BV013','BV014','BV015','BV016',
  'BS001','BS002','BS003','BS004','BS005','BS006','BS007','BS008','BS009','BS010','BS011',
  'NP001','NP002','NP003','NP004','NP005','NP006','NP007',
  'CP001','CP002','CP003','CP004','CP005','CP006','CP007','CP008',
  'PC001','PC002','PC003','PC004','PC005','PC006','PC007','PC008','PC009','PC010','PC011',
  'HC001','HC002','HC003','HC004','HC005','HC006','HC007','HC008','HC009','HC010',
  'BI001','BI002','BI003','BI004','BI005','BI006','BI007','BI008',
  'CF001','CF002','CF003','CF004','CF005','CF006','CF007','CF008'
);

-- ============================================================
-- 7. EXPENSE CATEGORIES
-- ============================================================
INSERT INTO expense_categories (category_name, status) VALUES
  ('Rent',           'active'),
  ('Electricity',    'active'),
  ('Water',          'active'),
  ('Staff Salaries', 'active'),
  ('Transport',      'active'),
  ('Advertising',    'active'),
  ('Packaging',      'active'),
  ('Repairs',        'active'),
  ('Telephone',      'active'),
  ('Miscellaneous',  'active')
ON DUPLICATE KEY UPDATE category_name = VALUES(category_name);

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
-- Summary
-- ============================================================
SELECT '=== SEED COMPLETE ===' AS '';
SELECT CONCAT(COUNT(*), ' products inserted') AS 'Products' FROM products WHERE product_code REGEXP '^[A-Z]{2}[0-9]{3}$';
SELECT CONCAT(COUNT(*), ' categories')        AS 'Categories' FROM categories;
SELECT CONCAT(COUNT(*), ' brands')            AS 'Brands'     FROM brands;
SELECT CONCAT(COUNT(*), ' units')             AS 'Units'      FROM units;
