-- Seed brands
INSERT IGNORE INTO brands (name) VALUES
('8 PM'),
('Antiquity'),
('Black & White'),
('Bacardi'),
('Brihans'),
('Budweiser'),
('Directors Special'),
('Grand Master''s'),
('Green Label'),
('ICONIQ'),
('Johnnie Walker'),
('Kingfisher'),
('London Pilsner'),
('Magic Moments'),
('Magic Moments Remix'),
('McDowell''s No.1'),
('Officers Choice'),
('Old Monk'),
('Royal Challenge'),
('Royal Green'),
('Seagram''s'),
('Signature'),
('Smirnoff'),
('Sterling Reserve'),
('Teacher''s'),
('VAT 69'),
('White Mischief');

-- Seed categories
INSERT IGNORE INTO categories (name) VALUES
('Liquor');

-- Seed products
INSERT IGNORE INTO products (name, brand_id, category_id, price, tax) VALUES
('8 PM Special Whisky', (SELECT id FROM brands WHERE name='8 PM'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('Antiquity Blue Whisky', (SELECT id FROM brands WHERE name='Antiquity'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('Black & White Blended Scotch Whisky(OS)', (SELECT id FROM brands WHERE name='Black & White'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('Black by Bacardi Ultra Deluxe Original Premium Crafted Rum', (SELECT id FROM brands WHERE name='Bacardi'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('BRIHANS DOCTOR BRANDY', (SELECT id FROM brands WHERE name='Brihans'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('BRIHANS GOA DRY GIN & LIME', (SELECT id FROM brands WHERE name='Brihans'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('BRIHANS GOA DRY GIN & LIME DUET', (SELECT id FROM brands WHERE name='Brihans'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('BRIHAN''S GOA GIN & LIME DUET', (SELECT id FROM brands WHERE name='Brihans'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('BRIHAN''S GOA TWO IN ONE DRY GIN & LIME', (SELECT id FROM brands WHERE name='Brihans'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('Budweiser Premium International King of Beer', (SELECT id FROM brands WHERE name='Budweiser'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('Directors Special Black Deluxe Whisky', (SELECT id FROM brands WHERE name='Directors Special'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('Grand Master''s Cranberry Platinum Vodka', (SELECT id FROM brands WHERE name='Grand Master''s'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('Grand Master''s Mango Platinum Vodka', (SELECT id FROM brands WHERE name='Grand Master''s'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('Grand Master''s Melon Platinum Vodka', (SELECT id FROM brands WHERE name='Grand Master''s'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('Grand Master''s Orange Platinum Vodka', (SELECT id FROM brands WHERE name='Grand Master''s'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('Green Label Classic Whisky', (SELECT id FROM brands WHERE name='Green Label'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('ICONIQ WHITE FINEST INTERNATIONAL GRAIN WHISKY', (SELECT id FROM brands WHERE name='ICONIQ'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('Johnnie Walker Red Label Blended Scotch Whisky', (SELECT id FROM brands WHERE name='Johnnie Walker'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('Kingfisher Premium Lager Beer', (SELECT id FROM brands WHERE name='Kingfisher'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('Kingfisher Strong Premium Beer', (SELECT id FROM brands WHERE name='Kingfisher'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('London Pilsner Premium Strong Beer', (SELECT id FROM brands WHERE name='London Pilsner'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('M2 MAGIC MOMENTS REMIX SMOOTH FLAVOURED VODKA CHOCOLATE', (SELECT id FROM brands WHERE name='Magic Moments Remix'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('M2 MAGIC MOMENTS REMIX SMOOTH FLAVOURED VODKA GREEN APPLE', (SELECT id FROM brands WHERE name='Magic Moments Remix'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('M2 MAGIC MOMENTS REMIX SMOOTH FLAVOURED VODKA ORANGE', (SELECT id FROM brands WHERE name='Magic Moments Remix'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('MAGIC MOMENTS CHOCOLATE VODKA', (SELECT id FROM brands WHERE name='Magic Moments'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('MAGIC MOMENTS GREEN APPLE VODKA', (SELECT id FROM brands WHERE name='Magic Moments'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('MAGIC MOMENTS ORANGE VODKA', (SELECT id FROM brands WHERE name='Magic Moments'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('MAGIC MOMENTS PURE GRAIN VODKA', (SELECT id FROM brands WHERE name='Magic Moments'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('Magic Moments Remix Vivid Pink Vodka', (SELECT id FROM brands WHERE name='Magic Moments Remix'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('McDowell''s No. 1 Original Reserve Whisky', (SELECT id FROM brands WHERE name='McDowell''s No.1'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('McDowell''s No.1 Celebration XXX Matured Rum', (SELECT id FROM brands WHERE name='McDowell''s No.1'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('McDowell''s No.1 Supreme Special Whisky Original', (SELECT id FROM brands WHERE name='McDowell''s No.1'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('OFFICERS CHOICE Blue Pure Grain whisky', (SELECT id FROM brands WHERE name='Officers Choice'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('OFFICERS CHOICE Prestige Whisky', (SELECT id FROM brands WHERE name='Officers Choice'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('OLD MONK RUM', (SELECT id FROM brands WHERE name='Old Monk'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('Royal Challenge Finest Premium Whisky', (SELECT id FROM brands WHERE name='Royal Challenge'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('ROYAL CHALLENGE Whisky', (SELECT id FROM brands WHERE name='Royal Challenge'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('Royal Green Supreme Blended Whisky', (SELECT id FROM brands WHERE name='Royal Green'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('Seagram''s 100 Pipers Deluxe Blended Scotch Whisky', (SELECT id FROM brands WHERE name='Seagram''s'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('Seagram''s Blenders Pride Reserve Collection Whisky', (SELECT id FROM brands WHERE name='Seagram''s'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('Seagram''s Blenders Pride Ultra Premium Whisky', (SELECT id FROM brands WHERE name='Seagram''s'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('Seagram''s Imperial Blue Superior Grain Whisky', (SELECT id FROM brands WHERE name='Seagram''s'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('Seagram''s Oaken Glow Premium Whisky', (SELECT id FROM brands WHERE name='Seagram''s'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('Seagram''s Royal Stag Barrel Select Whisky', (SELECT id FROM brands WHERE name='Seagram''s'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('Seagram''s Royal Stag Deluxe Whisky', (SELECT id FROM brands WHERE name='Seagram''s'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('Seagram''s Royal Stag Double Dark Premier Peaty Whisky', (SELECT id FROM brands WHERE name='Seagram''s'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('Signature Rare Aged Whisky', (SELECT id FROM brands WHERE name='Signature'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('Smirnoff Triple Distilled Green Apple Vodka(OS)', (SELECT id FROM brands WHERE name='Smirnoff'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('Smirnoff Triple Distilled Vodka', (SELECT id FROM brands WHERE name='Smirnoff'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('STERLING RESERVE B 7 RARE BLENDED WHISKY-LK', (SELECT id FROM brands WHERE name='Sterling Reserve'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('Sterling Reserve B7 Rare Blended Whisky-LK', (SELECT id FROM brands WHERE name='Sterling Reserve'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('TEACHER''S HIGHLAND CREAM BLENDED SCOTCH WHISKY(w)', (SELECT id FROM brands WHERE name='Teacher''s'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('VAT 69 BLENDED SCOTCH WHISKY', (SELECT id FROM brands WHERE name='VAT 69'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('WHITE MISCHIEF GREEN APPLE VODKA', (SELECT id FROM brands WHERE name='White Mischief'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('WHITE MISCHIEF ORANGE VODKA', (SELECT id FROM brands WHERE name='White Mischief'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0),
('WHITE MISCHIEF ULTRA PURE VODKA', (SELECT id FROM brands WHERE name='White Mischief'), (SELECT id FROM categories WHERE name='Liquor'), 0.00, 0);

-- Seed sizes
INSERT IGNORE INTO sizes (name) VALUES
('2000 ML(6)'),
('2000 ML Pet (6)'),
('2000 ML(4)'),
('2000 ML(Pet)-4'),
('1000 ML'),
('750 ML'),
('750 ML (Pet)'),
('650 ML'),
('500 ML (CAN)'),
('375 ML'),
('375 ML (Pet)'),
('330 ML (CAN)'),
('330 ML'),
('200 ML (24)'),
('180 ML'),
('180 ML (Pet)'),
('90 ML(100)'),
('90 ML-(96)'),
('90 ML (Pet)-96'),
('90 ML (Pet)-100'),
('60 ML');

-- Set demo size, tax & price for all products
UPDATE products p
SET p.size_id = (SELECT id FROM sizes WHERE name='750 ML'),
    p.tax = 18.00,
    p.price = CASE
        WHEN p.name LIKE '%Beer%' THEN 150.00
        WHEN p.name LIKE '%Vodka%' THEN 900.00
        WHEN p.name LIKE '%Rum%' THEN 1200.00
        WHEN p.name LIKE '%Brandy%' THEN 1100.00
        WHEN p.name LIKE '%Gin%' THEN 1400.00
        WHEN p.name LIKE '%Whisky%' THEN 1800.00
        ELSE 1000.00
    END;
