-- Add missing columns to support the full Product UI
ALTER TABLE product ADD COLUMN product_code VARCHAR(50) AFTER name;
ALTER TABLE product ADD COLUMN description TEXT AFTER category_id;
ALTER TABLE product ADD COLUMN alias TEXT AFTER description;
ALTER TABLE product ADD COLUMN price DECIMAL(10,2) AFTER alias;

-- Seed sample products matching the UI
INSERT INTO category (name, description) VALUES
('Bag Tags', 'Airport baggage identification tags'),
('Labels', 'Self-adhesive labels for packaging'),
('Letter Sheets', 'Pre-formatted letter sheets');

INSERT INTO presentation (name, units_per_presentation) VALUES
('Roll', 400),
('Sheet (20 labels)', 20),
('Sheet (30 labels)', 30),
('Pack (100)', 100);

INSERT INTO product (name, product_code, category_id, description, alias, price) VALUES
('Bag Tag', '1014:201', 1, 'Non EU Tag Without Green Border', 'Non EU Tag Without Green Border, Non EU', 12.50),
('Bag Tag Premium', '1014:202', 1, 'EU Tag With Green Border Premium Material', 'EU Tag, Premium Bag Tag', 18.75),
('Shipping Label HD', '2010:101', 2, 'Heavy duty shipping label for international freight', 'HD Label, Shipping', 8.00),
('Address Label Standard', '2010:102', 2, 'Standard self-adhesive address label', 'Address, Standard Label', 5.50),
('A4 Letter Sheet', '3005:001', 3, 'Premium A4 letter sheet 120gsm bright white', 'A4, Letter, Premium Sheet', 15.00),
('Waterproof Label', '2010:103', 2, 'Weatherproof vinyl label for outdoor use', 'Waterproof, Outdoor, Vinyl Label', 22.00);

-- Link products to presentations
INSERT INTO product_presentation (product_id, presentation_id) VALUES
(1, 1), -- Bag Tag -> Roll
(2, 1), -- Bag Tag Premium -> Roll
(3, 2), -- Shipping Label HD -> Sheet (20)
(4, 2), -- Address Label -> Sheet (20)
(5, 4), -- A4 Letter Sheet -> Pack (100)
(6, 3); -- Waterproof Label -> Sheet (30)

-- Create packaging for presentations
INSERT INTO packaging (presentation_id, name, quantity) VALUES
(1, 'Box', 6),    -- Box of 6 Rolls = 2400 units
(2, 'Carton', 50), -- Carton of 50 Sheets
(4, 'Case', 10),   -- Case of 10 Packs
(3, 'Carton', 40); -- Carton of 40 Sheets