-- ============================================================
--  MJPOS – Sri Lankan Supermarket Products ADD-ON
--  Safe to run on LIVE database — only INSERTS new rows
--  Existing sales, purchases, stock NOT touched
--  Generated for: mjpos_db (backup 2026-05-23)
-- ============================================================

USE mjpos_db;
SET FOREIGN_KEY_CHECKS = 0;
SET NAMES utf8mb4;

-- ============================================================
-- STEP 1: NEW CATEGORIES  (INSERT IGNORE = skip if exists)
-- ============================================================
INSERT IGNORE INTO categories (category_name, description, status) VALUES
  ('Flour & Baking',        'Wheat flour, kurakkan, baking ingredients',  'active'),
  ('Pulses & Dhal',         'Red lentils, chickpeas, green gram, cowpea', 'active'),
  ('Spices & Condiments',   'Curry powder, chilli, sauces, pickles',      'active'),
  ('Cooking Oil',           'Coconut oil, sunflower oil, margarine',      'active'),
  ('Sugar & Salt',          'White sugar, jaggery, salt, vinegar',        'active'),
  ('Noodles & Pasta',       'Instant noodles, egg noodles, pasta',        'active'),
  ('Canned & Preserved',    'Canned fish, jams, coconut milk',            'active'),
  ('Baby Products',         'Baby food, diapers, baby care',              'active');

-- ============================================================
-- STEP 2: NEW BRANDS
-- ============================================================
INSERT IGNORE INTO brands (brand_name, status) VALUES
  ('Harischandra',   'active'),
  ('Prima',          'active'),
  ('Dilmah',         'active'),
  ('Lipton',         'active'),
  ('MD',             'active'),
  ('Kotmale',        'active'),
  ('Ambewela',       'active'),
  ('Munchee',        'active'),
  ('Kandos',         'active'),
  ('Ritzbury',       'active'),
  ('Colgate',        'active'),
  ('Dettol',         'active'),
  ('Harpic',         'active'),
  ('Surf Excel',     'active'),
  ('Lucky',          'active'),
  ('Astra',          'active'),
  ('Johnson',        'active'),
  ('Pampers',        'active'),
  ('Huggies',        'active'),
  ('Cowbell',        'active'),
  ('Larich',         'active'),
  ('Oral-B',         'active'),
  ('Sunflower Oil',  'active'),
  ('Anchor Butter',  'active'),
  ('Highland',       'active'),
  ('Elephant House', 'active'),
  ('Magic',          'active'),
  ('Lanka Soy',      'active'),
  ('Tiara',          'active'),
  ('Sathosa',        'active');

-- ============================================================
-- STEP 3: RESOLVE IDs  (reads actual IDs from YOUR database)
-- ============================================================

-- Categories (existing)
SET @cat_groc    = (SELECT id FROM categories WHERE category_name = 'Groceries'          LIMIT 1);
SET @cat_bev     = (SELECT id FROM categories WHERE category_name = 'Beverages'          LIMIT 1);
SET @cat_dairy   = (SELECT id FROM categories WHERE category_name = 'Dairy Products'     LIMIT 1);
SET @cat_care    = (SELECT id FROM categories WHERE category_name = 'Personal Care'      LIMIT 1);
SET @cat_house   = (SELECT id FROM categories WHERE category_name = 'Household'          LIMIT 1);
SET @cat_conf    = (SELECT id FROM categories WHERE category_name = 'Confectionery'      LIMIT 1);
SET @cat_rice    = (SELECT id FROM categories WHERE category_name = 'සහල්'              LIMIT 1);

-- Categories (new)
SET @cat_flour   = (SELECT id FROM categories WHERE category_name = 'Flour & Baking'     LIMIT 1);
SET @cat_pulse   = (SELECT id FROM categories WHERE category_name = 'Pulses & Dhal'      LIMIT 1);
SET @cat_spice   = (SELECT id FROM categories WHERE category_name = 'Spices & Condiments' LIMIT 1);
SET @cat_oil     = (SELECT id FROM categories WHERE category_name = 'Cooking Oil'        LIMIT 1);
SET @cat_sugar   = (SELECT id FROM categories WHERE category_name = 'Sugar & Salt'       LIMIT 1);
SET @cat_noodle  = (SELECT id FROM categories WHERE category_name = 'Noodles & Pasta'    LIMIT 1);
SET @cat_canned  = (SELECT id FROM categories WHERE category_name = 'Canned & Preserved' LIMIT 1);
SET @cat_baby    = (SELECT id FROM categories WHERE category_name = 'Baby Products'      LIMIT 1);

-- Units (from backup: 1=pcs, 2=kg, 3=g, 4=l, 5=ml, 6=box, 7=pkt, 8=btl, 9=doz, 10=pr, 11=m)
SET @u_pcs  = (SELECT id FROM units WHERE short_name = 'pcs'  LIMIT 1);
SET @u_kg   = (SELECT id FROM units WHERE short_name = 'kg'   LIMIT 1);
SET @u_g    = (SELECT id FROM units WHERE short_name = 'g'    LIMIT 1);
SET @u_L    = (SELECT id FROM units WHERE short_name = 'l'    LIMIT 1);
SET @u_ml   = (SELECT id FROM units WHERE short_name = 'ml'   LIMIT 1);
SET @u_box  = (SELECT id FROM units WHERE short_name = 'box'  LIMIT 1);
SET @u_pkt  = (SELECT id FROM units WHERE short_name = 'pkt'  LIMIT 1);
SET @u_btl  = (SELECT id FROM units WHERE short_name = 'btl'  LIMIT 1);

-- Brands (existing)
SET @b_anchor  = (SELECT id FROM brands WHERE brand_name = 'Anchor'       LIMIT 1);
SET @b_malib   = (SELECT id FROM brands WHERE brand_name = 'Maliban'      LIMIT 1);
SET @b_nestle  = (SELECT id FROM brands WHERE brand_name = 'Nestlé'       LIMIT 1);
SET @b_unilev  = (SELECT id FROM brands WHERE brand_name = 'Unilever'     LIMIT 1);
SET @b_genr    = (SELECT id FROM brands WHERE brand_name = 'Generic'      LIMIT 1);
SET @b_sunsh   = (SELECT id FROM brands WHERE brand_name = 'Sunshine'     LIMIT 1);

-- Brands (new)
SET @b_hari    = (SELECT id FROM brands WHERE brand_name = 'Harischandra' LIMIT 1);
SET @b_prima   = (SELECT id FROM brands WHERE brand_name = 'Prima'        LIMIT 1);
SET @b_dilmah  = (SELECT id FROM brands WHERE brand_name = 'Dilmah'       LIMIT 1);
SET @b_lipton  = (SELECT id FROM brands WHERE brand_name = 'Lipton'       LIMIT 1);
SET @b_md      = (SELECT id FROM brands WHERE brand_name = 'MD'           LIMIT 1);
SET @b_kotmal  = (SELECT id FROM brands WHERE brand_name = 'Kotmale'      LIMIT 1);
SET @b_ambew   = (SELECT id FROM brands WHERE brand_name = 'Ambewela'     LIMIT 1);
SET @b_munch   = (SELECT id FROM brands WHERE brand_name = 'Munchee'      LIMIT 1);
SET @b_kandos  = (SELECT id FROM brands WHERE brand_name = 'Kandos'       LIMIT 1);
SET @b_ritz    = (SELECT id FROM brands WHERE brand_name = 'Ritzbury'     LIMIT 1);
SET @b_colg    = (SELECT id FROM brands WHERE brand_name = 'Colgate'      LIMIT 1);
SET @b_dettol  = (SELECT id FROM brands WHERE brand_name = 'Dettol'       LIMIT 1);
SET @b_harpic  = (SELECT id FROM brands WHERE brand_name = 'Harpic'       LIMIT 1);
SET @b_surf    = (SELECT id FROM brands WHERE brand_name = 'Surf Excel'   LIMIT 1);
SET @b_lucky   = (SELECT id FROM brands WHERE brand_name = 'Lucky'        LIMIT 1);
SET @b_astra   = (SELECT id FROM brands WHERE brand_name = 'Astra'        LIMIT 1);
SET @b_john    = (SELECT id FROM brands WHERE brand_name = 'Johnson'      LIMIT 1);
SET @b_pampers = (SELECT id FROM brands WHERE brand_name = 'Pampers'      LIMIT 1);
SET @b_hugg    = (SELECT id FROM brands WHERE brand_name = 'Huggies'      LIMIT 1);
SET @b_cowb    = (SELECT id FROM brands WHERE brand_name = 'Cowbell'      LIMIT 1);
SET @b_larich  = (SELECT id FROM brands WHERE brand_name = 'Larich'       LIMIT 1);
SET @b_oralb   = (SELECT id FROM brands WHERE brand_name = 'Oral-B'       LIMIT 1);
SET @b_sunflo  = (SELECT id FROM brands WHERE brand_name = 'Sunflower Oil' LIMIT 1);
SET @b_highland= (SELECT id FROM brands WHERE brand_name = 'Highland'     LIMIT 1);
SET @b_eleph   = (SELECT id FROM brands WHERE brand_name = 'Elephant House' LIMIT 1);
SET @b_magic   = (SELECT id FROM brands WHERE brand_name = 'Magic'        LIMIT 1);
SET @b_tiara   = (SELECT id FROM brands WHERE brand_name = 'Tiara'        LIMIT 1);
SET @b_sath    = (SELECT id FROM brands WHERE brand_name = 'Sathosa'      LIMIT 1);

-- ============================================================
-- STEP 4: NEW PRODUCTS
-- product_code must be unique — continuing from P011
-- INSERT IGNORE skips silently if code already exists
-- ============================================================

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

-- ── RICE & GRAINS (extending existing සහල් category) ──────
('P011','Keeri Samba Rice 5kg',          @cat_rice,  @b_sath,  @u_pkt, 1100.00,1380.00,1320.00, 15, 50,  0,0,'active'),
('P012','Nadu Rice 5kg',                  @cat_rice,  @b_sath,  @u_pkt,  870.00,1080.00,1040.00, 20, 80,  0,0,'active'),
('P013','Red Rice 1kg',                   @cat_rice,  @b_genr,  @u_kg,   195.00, 250.00, 240.00,  8, 60,  0,0,'active'),
('P014','Samba Rice 1kg (Loose)',          @cat_rice,  @b_genr,  @u_kg,   190.00, 240.00, 230.00, 20,150,  0,0,'active'),
('P015','Basmati Rice 1kg',               @cat_rice,  @b_hari,  @u_kg,   290.00, 365.00, 350.00,  5, 40,  0,0,'active'),
('P016','Kurakkan Flour 500g',            @cat_rice,  @b_hari,  @u_pkt,  130.00, 168.00, 160.00,  5, 30,  0,0,'active'),

-- ── FLOUR & BAKING ─────────────────────────────────────────
('P017','Wheat Flour 1kg',                @cat_flour, @b_prima, @u_kg,   175.00, 225.00, 215.00, 30,120,  0,0,'active'),
('P018','Wheat Flour 5kg',                @cat_flour, @b_prima, @u_pkt,  860.00,1075.00,1040.00, 15, 50,  0,0,'active'),
('P019','Self Raising Flour 1kg',         @cat_flour, @b_prima, @u_kg,   205.00, 260.00, 250.00, 10, 40,  0,0,'active'),
('P020','Semolina (Ravai) 500g',          @cat_flour, @b_genr,  @u_pkt,  110.00, 142.00, 136.00, 10, 35,  0,0,'active'),
('P021','Corn Flour 400g',                @cat_flour, @b_prima, @u_pkt,  145.00, 188.00, 180.00, 10, 30,  0,0,'active'),
('P022','Baking Powder 100g',             @cat_flour, @b_genr,  @u_pkt,   80.00, 105.00, 100.00,  5, 25,  0,0,'active'),
('P023','Astra Margarine 200g',           @cat_flour, @b_astra, @u_g,    165.00, 215.00, 205.00, 10, 40,  0,0,'active'),
('P024','Magic Margarine 250g',           @cat_flour, @b_magic, @u_g,    170.00, 220.00, 210.00, 10, 35,  0,0,'active'),

-- ── PULSES & DHAL ──────────────────────────────────────────
('P025','Red Lentils (Dhal) 500g',        @cat_pulse, @b_hari,  @u_pkt,  220.00, 278.00, 268.00, 15, 80,  0,0,'active'),
('P026','Red Lentils 1kg',                @cat_pulse, @b_hari,  @u_kg,   425.00, 540.00, 520.00, 10, 60,  0,0,'active'),
('P027','Green Gram (Mung) 500g',         @cat_pulse, @b_genr,  @u_pkt,  235.00, 298.00, 285.00, 10, 50,  0,0,'active'),
('P028','Chickpeas 500g',                 @cat_pulse, @b_genr,  @u_pkt,  215.00, 272.00, 260.00, 10, 40,  0,0,'active'),
('P029','Black-eyed Cowpea 500g',         @cat_pulse, @b_genr,  @u_pkt,  200.00, 255.00, 245.00,  8, 35,  0,0,'active'),
('P030','Soya Meat 90g',                  @cat_pulse, @b_genr,  @u_pkt,   90.00, 118.00, 112.00, 15, 60,  0,0,'active'),

-- ── SPICES & CONDIMENTS ────────────────────────────────────
('P031','Roasted Curry Powder 100g',      @cat_spice, @b_hari,  @u_pkt,  110.00, 145.00, 138.00, 15, 60,  0,0,'active'),
('P032','Unroasted Curry Powder 100g',    @cat_spice, @b_hari,  @u_pkt,  100.00, 132.00, 125.00, 15, 60,  0,0,'active'),
('P033','Chilli Powder 100g',             @cat_spice, @b_hari,  @u_pkt,   90.00, 120.00, 114.00, 12, 50,  0,0,'active'),
('P034','Turmeric Powder 100g',           @cat_spice, @b_hari,  @u_pkt,   80.00, 108.00, 102.00, 12, 50,  0,0,'active'),
('P035','Pepper Powder 50g',              @cat_spice, @b_hari,  @u_pkt,  130.00, 168.00, 160.00, 10, 40,  0,0,'active'),
('P036','Cinnamon Sticks 50g',            @cat_spice, @b_genr,  @u_pkt,   95.00, 125.00, 118.00,  8, 30,  0,0,'active'),
('P037','MD Tomato Sauce 400g',           @cat_spice, @b_md,    @u_pkt,  190.00, 248.00, 238.00, 10, 45,  0,0,'active'),
('P038','MD Chilli Sauce 200ml',          @cat_spice, @b_md,    @u_btl,  150.00, 198.00, 188.00, 10, 40,  0,0,'active'),
('P039','MD Soya Sauce 200ml',            @cat_spice, @b_md,    @u_btl,  125.00, 165.00, 158.00,  8, 35,  0,0,'active'),
('P040','Larich Tomato Ketchup 340g',     @cat_spice, @b_larich,@u_pkt,  155.00, 202.00, 192.00, 10, 40,  0,0,'active'),
('P041','Iodised Salt 500g',              @cat_sugar, @b_genr,  @u_pkt,   40.00,  56.00,  52.00, 25,100,  0,0,'active'),
('P042','Maldive Fish 100g',              @cat_spice, @b_genr,  @u_pkt,  350.00, 452.00, 430.00,  8, 30,  0,0,'active'),

-- ── COOKING OIL ────────────────────────────────────────────
('P043','Coconut Oil 1L',                 @cat_oil,   @b_genr,  @u_btl,  820.00,1055.00,1010.00, 15, 60,  0,0,'active'),
('P044','Coconut Oil 750ml',              @cat_oil,   @b_genr,  @u_btl,  630.00, 808.00, 775.00, 12, 50,  0,0,'active'),
('P045','Sunflower Oil 1L',               @cat_oil,   @b_sunflo,@u_btl,  480.00, 620.00, 592.00, 12, 60,  0,0,'active'),
('P046','Lucky Vegetable Oil 1L',         @cat_oil,   @b_lucky, @u_btl,  450.00, 580.00, 555.00, 12, 60,  0,0,'active'),

-- ── SUGAR & SALT ───────────────────────────────────────────
('P047','White Sugar 1kg',                @cat_sugar, @b_genr,  @u_kg,   195.00, 252.00, 242.00, 35,150,  0,0,'active'),
('P048','White Sugar 5kg',                @cat_sugar, @b_genr,  @u_pkt,  950.00,1218.00,1165.00, 15, 60,  0,0,'active'),
('P049','Jaggery (Hakuru) 250g',          @cat_sugar, @b_genr,  @u_pkt,  120.00, 158.00, 150.00, 10, 40,  0,0,'active'),
('P050','White Vinegar 400ml',            @cat_sugar, @b_genr,  @u_btl,   80.00, 106.00, 100.00,  8, 30,  0,0,'active'),

-- ── DAIRY & EGGS ───────────────────────────────────────────
('P051','Kotmale Yoghurt Plain 80g',      @cat_dairy, @b_kotmal,@u_pkt,   60.00,  82.00,  78.00, 15, 50,  0,0,'active'),
('P052','Kotmale Yoghurt Fruit 80g',      @cat_dairy, @b_kotmal,@u_pkt,   65.00,  86.00,  82.00, 15, 50,  0,0,'active'),
('P053','Ambewela Fresh Milk 1L',         @cat_dairy, @b_ambew, @u_btl,  195.00, 252.00, 240.00, 10, 40,  0,0,'active'),
('P054','Anchor Butter 227g',             @cat_dairy, @b_anchor,@u_pkt,  580.00, 745.00, 710.00,  8, 25,  0,0,'active'),
('P055','Anchor Milk Powder 1kg',         @cat_dairy, @b_anchor,@u_pkt, 2050.00,2620.00,2510.00,  5, 20,  0,0,'active'),
('P056','Highland Milk Powder 400g',      @cat_dairy, @b_highland,@u_pkt,820.00,1045.00,1000.00,  8, 30,  0,0,'active'),
('P057','Cowbell Condensed Milk 397g',    @cat_dairy, @b_cowb,  @u_pkt,  280.00, 362.00, 346.00,  8, 35,  0,0,'active'),
('P058','Eggs (Pack of 6)',               @cat_dairy, @b_genr,  @u_pcs,  195.00, 252.00, 240.00, 10, 40,  0,0,'active'),
('P059','Eggs Tray 30 pcs',               @cat_dairy, @b_genr,  @u_pcs,  990.00,1255.00,1200.00,  5, 15,  0,0,'active'),

-- ── BEVERAGES ──────────────────────────────────────────────
('P060','Dilmah Tea Bags 100s',           @cat_bev,   @b_dilmah,@u_box,  680.00, 875.00, 840.00, 10, 40,  0,0,'active'),
('P061','Dilmah Tea Bags 50s',            @cat_bev,   @b_dilmah,@u_box,  360.00, 465.00, 445.00, 10, 40,  0,0,'active'),
('P062','Lipton Yellow Label 100s',       @cat_bev,   @b_lipton,@u_box,  635.00, 818.00, 780.00, 10, 35,  0,0,'active'),
('P063','Harischandra Orange Pekoe 200g', @cat_bev,   @b_hari,  @u_pkt,  315.00, 402.00, 385.00, 10, 35,  0,0,'active'),
('P064','Nescafé Classic 50g',            @cat_bev,   @b_nestle,@u_pkt,  398.00, 512.00, 490.00, 10, 40,  0,0,'active'),
('P065','Nescafé Classic 200g',           @cat_bev,   @b_nestle,@u_pkt, 1485.00,1905.00,1820.00,  5, 20,  0,0,'active'),
('P066','Milo 200g Sachet',               @cat_bev,   @b_nestle,@u_pkt,  375.00, 482.00, 460.00, 12, 50,  0,0,'active'),
('P067','Elephant Ginger Beer 400ml',     @cat_bev,   @b_eleph, @u_btl,  100.00, 138.00, 130.00, 20, 80,  0,0,'active'),
('P068','Elephant Cream Soda 400ml',      @cat_bev,   @b_eleph, @u_btl,   95.00, 130.00, 124.00, 20, 80,  0,0,'active'),
('P069','Coca-Cola 400ml',                @cat_bev,   @b_genr,  @u_btl,  100.00, 138.00, 130.00, 25,100,  0,0,'active'),
('P070','Sprite 400ml',                   @cat_bev,   @b_genr,  @u_btl,   95.00, 132.00, 125.00, 20, 80,  0,0,'active'),
('P071','Mineral Water 500ml',            @cat_bev,   @b_genr,  @u_btl,   50.00,  72.00,  68.00, 35,150,  0,0,'active'),
('P072','Mineral Water 1.5L',             @cat_bev,   @b_genr,  @u_btl,  100.00, 138.00, 130.00, 20, 80,  0,0,'active'),
('P073','Orange Juice 200ml Tetra',       @cat_bev,   @b_genr,  @u_ml,    95.00, 128.00, 122.00, 15, 60,  0,0,'active'),

-- ── BISCUITS & SNACKS (Confectionery category) ─────────────
('P074','Maliban Cream Cracker 200g',     @cat_conf,  @b_malib, @u_pkt,  130.00, 172.00, 165.00, 15, 60,  0,0,'active'),
('P075','Maliban Marie Biscuit 200g',     @cat_conf,  @b_malib, @u_pkt,  120.00, 158.00, 150.00, 12, 50,  0,0,'active'),
('P076','Maliban Chocolate Puff 90g',     @cat_conf,  @b_malib, @u_pkt,  100.00, 132.00, 125.00, 12, 50,  0,0,'active'),
('P077','Munchee Super Cream 180g',       @cat_conf,  @b_munch, @u_pkt,  125.00, 165.00, 158.00, 15, 55,  0,0,'active'),
('P078','Munchee Lemon Puff 100g',        @cat_conf,  @b_munch, @u_pkt,   95.00, 128.00, 122.00, 15, 60,  0,0,'active'),
('P079','Tiara Butter Cookies 200g',      @cat_conf,  @b_tiara, @u_pkt,  145.00, 192.00, 182.00, 10, 40,  0,0,'active'),
('P080','Kandos Milk Chocolate 100g',     @cat_conf,  @b_kandos,@u_pkt,  225.00, 298.00, 285.00, 10, 40,  0,0,'active'),
('P081','Ritzbury Cashew Crunch 90g',     @cat_conf,  @b_ritz,  @u_pkt,  220.00, 288.00, 275.00, 10, 35,  0,0,'active'),
('P082','Masala Cashew 100g',             @cat_conf,  @b_genr,  @u_pkt,  280.00, 362.00, 345.00,  8, 30,  0,0,'active'),
('P083','Potato Chips Local 50g',         @cat_conf,  @b_genr,  @u_pkt,   65.00,  88.00,  83.00, 15, 60,  0,0,'active'),

-- ── NOODLES & PASTA ────────────────────────────────────────
('P084','Prima Kottu Noodles 400g',       @cat_noodle,@b_prima, @u_pkt,  175.00, 228.00, 218.00, 15, 60,  0,0,'active'),
('P085','Prima Instant Noodles Chicken 75g',@cat_noodle,@b_prima,@u_pkt,  65.00,  88.00,  83.00, 25,100,  0,0,'active'),
('P086','Prima Instant Noodles Masala 75g', @cat_noodle,@b_prima,@u_pkt,  65.00,  88.00,  83.00, 25,100,  0,0,'active'),
('P087','Maggi Noodles Chicken 77g',      @cat_noodle,@b_nestle,@u_pkt,   70.00,  95.00,  90.00, 20, 80,  0,0,'active'),
('P088','Egg Noodles 200g',               @cat_noodle,@b_genr,  @u_pkt,  120.00, 158.00, 150.00, 10, 40,  0,0,'active'),
('P089','Spaghetti 500g',                 @cat_noodle,@b_prima, @u_pkt,  185.00, 242.00, 232.00,  8, 30,  0,0,'active'),

-- ── CANNED & PRESERVED ─────────────────────────────────────
('P090','Canned Sardines 425g',           @cat_canned,@b_genr,  @u_pcs,  265.00, 342.00, 328.00, 12, 50,  0,0,'active'),
('P091','Canned Mackerel Tomato 425g',    @cat_canned,@b_genr,  @u_pcs,  280.00, 362.00, 346.00, 12, 50,  0,0,'active'),
('P092','Canned Tuna in Brine 185g',      @cat_canned,@b_genr,  @u_pcs,  245.00, 318.00, 305.00, 15, 60,  0,0,'active'),
('P093','MD Mango Jam 500g',              @cat_canned,@b_md,    @u_pkt,  280.00, 362.00, 346.00,  8, 30,  0,0,'active'),
('P094','Coconut Milk Tin 400ml',         @cat_canned,@b_genr,  @u_pcs,  175.00, 228.00, 218.00, 12, 50,  0,0,'active'),

-- ── PERSONAL CARE ──────────────────────────────────────────
('P095','Colgate Strong Teeth 175g',      @cat_care,  @b_colg,  @u_pkt,  195.00, 252.00, 242.00, 12, 50,  0,0,'active'),
('P096','Colgate MaxFresh 150g',          @cat_care,  @b_colg,  @u_pkt,  222.00, 288.00, 275.00, 10, 40,  0,0,'active'),
('P097','Oral-B Toothbrush Medium',       @cat_care,  @b_oralb, @u_pcs,  135.00, 178.00, 170.00, 10, 40,  0,0,'active'),
('P098','Dettol Soap Original 125g',      @cat_care,  @b_dettol,@u_pcs,  120.00, 158.00, 150.00, 15, 60,  0,0,'active'),
('P099','Dove Soap 100g',                 @cat_care,  @b_unilev,@u_pcs,  165.00, 218.00, 208.00, 12, 50,  0,0,'active'),
('P100','Lifebuoy Soap 125g',             @cat_care,  @b_unilev,@u_pcs,   95.00, 128.00, 122.00, 15, 60,  0,0,'active'),
('P101','Sunsilk Shampoo 340ml',          @cat_care,  @b_unilev,@u_btl,  480.00, 625.00, 598.00,  8, 30,  0,0,'active'),
('P102','Dettol Handwash 200ml',          @cat_care,  @b_dettol,@u_btl,  295.00, 382.00, 365.00, 10, 40,  0,0,'active'),
('P103','Vaseline Lotion 200ml',          @cat_care,  @b_unilev,@u_btl,  340.00, 442.00, 422.00, 10, 35,  0,0,'active'),

-- ── HOUSEHOLD CLEANING ─────────────────────────────────────
('P104','Surf Excel Blue 1kg',            @cat_house, @b_surf,  @u_kg,   420.00, 548.00, 525.00, 10, 40,  0,0,'active'),
('P105','Surf Excel Blue 500g',           @cat_house, @b_surf,  @u_pkt,  225.00, 292.00, 280.00, 12, 50,  0,0,'active'),
('P106','Harpic Toilet Cleaner 500ml',    @cat_house, @b_harpic,@u_btl,  295.00, 382.00, 365.00,  8, 30,  0,0,'active'),
('P107','Dettol Multi-Surface 500ml',     @cat_house, @b_dettol,@u_btl,  380.00, 492.00, 470.00,  6, 25,  0,0,'active'),
('P108','Bleach Liquid 1L',               @cat_house, @b_genr,  @u_btl,  195.00, 255.00, 245.00, 10, 35,  0,0,'active'),
('P109','Sunlight Dish Paste 400g',       @cat_house, @b_unilev,@u_pkt,  185.00, 242.00, 232.00, 10, 40,  0,0,'active'),
('P110','Mosquito Coil Box 12s',          @cat_house, @b_genr,  @u_box,  145.00, 192.00, 182.00, 12, 50,  0,0,'active'),
('P111','Garbage Bags 10pcs Medium',      @cat_house, @b_genr,  @u_pkt,   90.00, 122.00, 115.00, 15, 60,  0,0,'active'),

-- ── BABY PRODUCTS ──────────────────────────────────────────
('P112','Pampers Active Baby S 30pcs',    @cat_baby,  @b_pampers,@u_pcs, 880.00,1125.00,1075.00,  5, 20,  0,0,'active'),
('P113','Pampers Active Baby M 28pcs',    @cat_baby,  @b_pampers,@u_pcs, 890.00,1140.00,1090.00,  5, 20,  0,0,'active'),
('P114','Huggies Ultra Dry S 30pcs',      @cat_baby,  @b_hugg,  @u_pcs,  850.00,1088.00,1040.00,  5, 20,  0,0,'active'),
('P115','Johnson Baby Powder 200g',       @cat_baby,  @b_john,  @u_pkt,  295.00, 382.00, 365.00,  6, 25,  0,0,'active'),
('P116','Johnson Baby Shampoo 200ml',     @cat_baby,  @b_john,  @u_btl,  380.00, 492.00, 470.00,  5, 20,  0,0,'active'),
('P117','Nestlé Cerelac Rice 400g',       @cat_baby,  @b_nestle,@u_pkt,  780.00, 998.00, 955.00,  4, 15,  0,0,'active'),
('P118','Baby Wet Wipes 80s',             @cat_baby,  @b_genr,  @u_pcs,  195.00, 252.00, 242.00,  8, 30,  0,0,'active');

-- ============================================================
-- STEP 5: OPENING STOCK MOVEMENTS for new products
-- ============================================================
-- Opening stock for new products (uses admin user id=1)
INSERT INTO stock_movements
  (product_id, movement_type, quantity_in, quantity_out,
   stock_after, reference_no, note, created_by)
SELECT
  p.id, 'opening', p.current_stock, 0, p.current_stock,
  'OPEN-2026', 'Opening stock – SL supermarket setup', 1
FROM products p
WHERE p.product_code REGEXP '^P(0[1-9][1-9]|1[0-1][0-9])$'
  AND NOT EXISTS (
    SELECT 1 FROM stock_movements sm
    WHERE sm.product_id = p.id AND sm.movement_type = 'opening'
      AND sm.reference_no = 'OPEN-2026'
  );

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
-- VERIFICATION
-- ============================================================
SELECT CONCAT('Categories: ', COUNT(*)) AS summary FROM categories WHERE status='active'
UNION ALL
SELECT CONCAT('Brands: ',     COUNT(*)) FROM brands     WHERE status='active'
UNION ALL
SELECT CONCAT('Units: ',      COUNT(*)) FROM units
UNION ALL
SELECT CONCAT('Products: ',   COUNT(*)) FROM products   WHERE status='active'
UNION ALL
SELECT CONCAT('New products added (P011-P118): ',
  COUNT(*)) FROM products WHERE product_code REGEXP '^P[0-9]{3}$'
  AND CAST(SUBSTRING(product_code,2) AS UNSIGNED) >= 11;
