seed.sql

-- POS System Seed Data
-- Created: 2026-02-09

-- ============================================
-- USERS (Various roles for testing)
-- ============================================

INSERT INTO users (id, username, password, pin, full_name, email, phone, role, status) VALUES
  ('user_001', 'admin', 'admin123', '1234', 'John Smith', 'john.smith@store.com', '555-0001', 'Store_Manager', 'active'),
  ('user_002', 'supervisor1', 'super123', '2345', 'Sarah Johnson', 'sarah.j@store.com', '555-0002', 'Supervisor', 'active'),
  ('user_003', 'cashier1', 'cash123', NULL, 'Mike Chen', 'mike.c@store.com', '555-0003', 'Cashier', 'active'),
  ('user_004', 'cashier2', 'cash123', NULL, 'Emily Davis', 'emily.d@store.com', '555-0004', 'Cashier', 'active'),
  ('user_005', 'sco_att1', 'sco123', '3456', 'Robert Wilson', 'robert.w@store.com', '555-0005', 'SCO_Attendant', 'active'),
  ('user_006', 'customer1', 'cust123', NULL, 'Guest Customer', 'guest@example.com', '555-9999', 'Customer', 'active');

-- ============================================
-- STORES
-- ============================================

INSERT INTO stores (id, name, address, city, state, zip, phone, tax_rate, status) VALUES
  ('store_001', 'Downtown Store', '123 Main Street', 'New York', 'NY', '10001', '(555) 123-4567', 0.10, 'active'),
  ('store_002', 'Uptown Store', '456 Park Avenue', 'New York', 'NY', '10022', '(555) 234-5678', 0.10, 'active');

-- ============================================
-- TERMINALS
-- ============================================

INSERT INTO terminals (id, store_id, name, type, status) VALUES
  ('term_001', 'store_001', 'Counter 1 (T001)', 'POS', 'available'),
  ('term_002', 'store_001', 'Counter 2 (T002)', 'POS', 'available'),
  ('term_003', 'store_001', 'SCO Kiosk 1', 'SCO', 'available'),
  ('term_004', 'store_001', 'SCO Kiosk 2', 'SCO', 'available'),
  ('term_005', 'store_002', 'Counter 1 (T005)', 'POS', 'available');

-- ============================================
-- PRODUCT CATEGORIES
-- ============================================

INSERT INTO product_categories (id, name, description, parent_id, sort_order) VALUES
  ('cat_001', 'Beverages', 'All drinks and beverages', NULL, 1),
  ('cat_002', 'Soft Drinks', 'Carbonated soft drinks', 'cat_001', 1),
  ('cat_003', 'Juices', 'Fruit juices and smoothies', 'cat_001', 2),
  ('cat_004', 'Water', 'Bottled water', 'cat_001', 3),
  ('cat_005', 'Dairy', 'Milk and dairy products', NULL, 2),
  ('cat_006', 'Snacks', 'Chips, cookies, and snacks', NULL, 3),
  ('cat_007', 'Fresh Produce', 'Fruits and vegetables', NULL, 4),
  ('cat_008', 'Bakery', 'Bread, pastries, and baked goods', NULL, 5),
  ('cat_009', 'Frozen Foods', 'Frozen meals and ice cream', NULL, 6),
  ('cat_010', 'Household', 'Cleaning and household items', NULL, 7);

-- ============================================
-- PRODUCTS (50+ realistic products)
-- ============================================

INSERT INTO products (id, barcode, name, description, category_id, price, member_price, cost, unit, tax_exempt, stock_qty, status) VALUES
  -- Beverages - Soft Drinks
  ('prod_001', '7890001', 'Coca-Cola 330ml', 'Classic Coke can', 'cat_002', 2.50, 2.25, 1.20, 'EA', 0, 100, 'active'),
  ('prod_002', '7890002', 'Pepsi 330ml', 'Pepsi cola can', 'cat_002', 2.50, 2.25, 1.20, 'EA', 0, 100, 'active'),
  ('prod_003', '7890003', 'Sprite 330ml', 'Lemon-lime soda', 'cat_002', 2.50, 2.25, 1.20, 'EA', 0, 100, 'active'),
  ('prod_004', '7890004', 'Fanta Orange 330ml', 'Orange flavored soda', 'cat_002', 2.50, 2.25, 1.20, 'EA', 0, 100, 'active'),
  ('prod_005', '7890005', 'Dr Pepper 330ml', 'Unique blend soda', 'cat_002', 2.50, 2.25, 1.20, 'EA', 0, 100, 'active'),
  
  -- Beverages - Juices
  ('prod_006', '7890006', 'Tropicana Orange Juice 1L', '100% orange juice', 'cat_003', 5.99, 5.49, 3.00, 'EA', 0, 50, 'active'),
  ('prod_007', '7890007', 'Minute Maid Apple Juice 1L', 'Fresh apple juice', 'cat_003', 5.99, 5.49, 3.00, 'EA', 0, 50, 'active'),
  ('prod_008', '7890008', 'Ocean Spray Cranberry 1L', 'Cranberry juice cocktail', 'cat_003', 6.49, 5.99, 3.50, 'EA', 0, 40, 'active'),
  
  -- Beverages - Water
  ('prod_009', '7890009', 'Dasani Water 500ml', 'Purified water', 'cat_004', 1.99, 1.79, 0.80, 'EA', 0, 200, 'active'),
  ('prod_010', '7890010', 'Aquafina Water 500ml', 'Purified drinking water', 'cat_004', 1.99, 1.79, 0.80, 'EA', 0, 200, 'active'),
  ('prod_011', '7890011', 'Evian Water 750ml', 'Natural spring water', 'cat_004', 3.49, 3.29, 1.50, 'EA', 0, 80, 'active'),
  
  -- Dairy
  ('prod_012', '7890012', 'Fresh Milk 1L', 'Whole milk', 'cat_005', 4.50, 4.20, 2.50, 'EA', 0, 60, 'active'),
  ('prod_013', '7890013', 'Skim Milk 1L', 'Fat-free milk', 'cat_005', 4.50, 4.20, 2.50, 'EA', 0, 60, 'active'),
  ('prod_014', '7890014', 'Almond Milk 1L', 'Plant-based milk alternative', 'cat_005', 5.99, 5.49, 3.20, 'EA', 0, 40, 'active'),
  ('prod_015', '7890015', 'Greek Yogurt 500g', 'Plain Greek yogurt', 'cat_005', 6.99, 6.49, 3.80, 'EA', 0, 50, 'active'),
  ('prod_016', '7890016', 'Cheddar Cheese 250g', 'Sharp cheddar block', 'cat_005', 7.99, 7.49, 4.50, 'EA', 0, 30, 'active'),
  
  -- Snacks
  ('prod_017', '7890017', 'Lays Chips Original 150g', 'Classic potato chips', 'cat_006', 3.99, 3.49, 2.00, 'EA', 0, 80, 'active'),
  ('prod_018', '7890018', 'Doritos Nacho 150g', 'Nacho cheese tortilla chips', 'cat_006', 3.99, 3.49, 2.00, 'EA', 0, 80, 'active'),
  ('prod_019', '7890019', 'Pringles Original 150g', 'Stackable potato crisps', 'cat_006', 4.49, 3.99, 2.20, 'EA', 0, 70, 'active'),
  ('prod_020', '7890020', 'Oreo Cookies 300g', 'Chocolate sandwich cookies', 'cat_006', 4.99, 4.49, 2.50, 'EA', 0, 60, 'active'),
  ('prod_021', '7890021', 'Kit Kat Bar 45g', 'Chocolate wafer bar', 'cat_006', 1.99, 1.79, 0.90, 'EA', 0, 100, 'active'),
  ('prod_022', '7890022', 'Snickers Bar 50g', 'Chocolate peanut bar', 'cat_006', 1.99, 1.79, 0.90, 'EA', 0, 100, 'active'),
  
  -- Fresh Produce
  ('prod_023', '7890023', 'Bananas per lb', 'Fresh bananas', 'cat_007', 0.79, 0.69, 0.40, 'LB', 0, 200, 'active'),
  ('prod_024', '7890024', 'Apples Red Delicious per lb', 'Fresh red apples', 'cat_007', 1.99, 1.79, 1.00, 'LB', 0, 150, 'active'),
  ('prod_025', '7890025', 'Tomatoes per lb', 'Fresh tomatoes', 'cat_007', 2.49, 2.29, 1.30, 'LB', 0, 100, 'active'),
  ('prod_026', '7890026', 'Lettuce Head', 'Fresh iceberg lettuce', 'cat_007', 2.99, 2.79, 1.50, 'EA', 0, 80, 'active'),
  ('prod_027', '7890027', 'Carrots 1lb Bag', 'Fresh carrots', 'cat_007', 1.99, 1.79, 1.00, 'EA', 0, 100, 'active'),
  
  -- Bakery
  ('prod_028', '7890028', 'White Bread Loaf', 'Sliced white bread', 'cat_008', 3.49, 3.29, 1.80, 'EA', 0, 50, 'active'),
  ('prod_029', '7890029', 'Whole Wheat Bread', 'Whole grain bread', 'cat_008', 3.99, 3.79, 2.00, 'EA', 0, 50, 'active'),
  ('prod_030', '7890030', 'Bagels 6-pack', 'Plain bagels', 'cat_008', 4.99, 4.49, 2.50, 'EA', 0, 40, 'active'),
  ('prod_031', '7890031', 'Croissant 4-pack', 'Butter croissants', 'cat_008', 5.99, 5.49, 3.00, 'EA', 0, 30, 'active'),
  ('prod_032', '7890032', 'Chocolate Cake Slice', 'Rich chocolate cake', 'cat_008', 4.99, 4.49, 2.50, 'EA', 0, 20, 'active'),
  
  -- Frozen Foods
  ('prod_033', '7890033', 'Ben & Jerry\'s Ice Cream 500ml', 'Vanilla ice cream', 'cat_009', 8.99, 7.99, 4.50, 'EA', 0, 40, 'active'),
  ('prod_034', '7890034', 'Haagen-Dazs Ice Cream 500ml', 'Strawberry ice cream', 'cat_009', 9.99, 8.99, 5.00, 'EA', 0, 30, 'active'),
  ('prod_035', '7890035', 'Frozen Pizza Margherita', '12 inch frozen pizza', 'cat_009', 7.99, 7.49, 4.00, 'EA', 0, 50, 'active'),
  ('prod_036', '7890036', 'Frozen French Fries 1kg', 'Crispy fries', 'cat_009', 4.99, 4.49, 2.50, 'EA', 0, 60, 'active'),
  ('prod_037', '7890037', 'Frozen Vegetables Mix 500g', 'Mixed vegetables', 'cat_009', 3.99, 3.49, 2.00, 'EA', 0, 70, 'active'),
  
  -- Household
  ('prod_038', '7890038', 'Tide Laundry Detergent 2L', 'Liquid laundry detergent', 'cat_010', 15.99, 14.99, 8.00, 'EA', 0, 40, 'active'),
  ('prod_039', '7890039', 'Dawn Dish Soap 500ml', 'Dishwashing liquid', 'cat_010', 4.99, 4.49, 2.50, 'EA', 0, 60, 'active'),
  ('prod_040', '7890040', 'Paper Towels 6-pack', 'Absorbent paper towels', 'cat_010', 12.99, 11.99, 6.50, 'EA', 0, 50, 'active'),
  ('prod_041', '7890041', 'Toilet Paper 12-pack', 'Soft toilet paper', 'cat_010', 14.99, 13.99, 7.50, 'EA', 0, 60, 'active'),
  ('prod_042', '7890042', 'Trash Bags 30-count', 'Heavy duty trash bags', 'cat_010', 9.99, 8.99, 5.00, 'EA', 0, 50, 'active'),
  
  -- Premium/Special items
  ('prod_043', '7890043', 'Premium Steak per lb', 'Choice ribeye steak', 'cat_007', 19.99, 18.99, 12.00, 'LB', 0, 30, 'active'),
  ('prod_044', '7890044', 'Organic Avocado', 'Fresh organic avocado', 'cat_007', 2.99, 2.79, 1.50, 'EA', 0, 60, 'active'),
  ('prod_045', '7890045', 'Champagne 750ml', 'Sparkling wine', 'cat_001', 29.99, 27.99, 15.00, 'EA', 0, 20, 'active'),
  
  -- Additional items for variety
  ('prod_046', '7890046', 'Energy Drink 250ml', 'Red Bull energy drink', 'cat_002', 3.99, 3.49, 2.00, 'EA', 0, 80, 'active'),
  ('prod_047', '7890047', 'Coffee Beans 500g', 'Premium arabica beans', 'cat_001', 12.99, 11.99, 6.50, 'EA', 0, 40, 'active'),
  ('prod_048', '7890048', 'Instant Noodles 5-pack', 'Chicken flavor ramen', 'cat_009', 4.99, 4.49, 2.50, 'EA', 0, 100, 'active'),
  ('prod_049', '7890049', 'Granola Bar 6-pack', 'Oats and honey bars', 'cat_006', 5.99, 5.49, 3.00, 'EA', 0, 70, 'active'),
  ('prod_050', '7890050', 'Bottled Green Tea 500ml', 'Unsweetened green tea', 'cat_003', 2.99, 2.79, 1.50, 'EA', 0, 90, 'active');

-- ============================================
-- MEMBER TIERS
-- ============================================

INSERT INTO member_tiers (id, name, discount_percentage, points_multiplier, min_purchase, badge_color, sort_order) VALUES
  ('tier_001', 'Bronze', 0, 1.0, 0, '#CD7F32', 1),
  ('tier_002', 'Silver', 3, 1.5, 100, '#C0C0C0', 2),
  ('tier_003', 'Gold', 5, 2.0, 500, '#FFD700', 3),
  ('tier_004', 'Platinum', 10, 3.0, 2000, '#E5E4E2', 4);

-- ============================================
-- MEMBERS (Sample loyalty members)
-- ============================================

INSERT INTO members (id, member_number, full_name, phone, email, date_of_birth, tier_id, points, total_spent, total_visits, member_since, status) VALUES
  ('mem_001', 'M00001', 'Alice Johnson', '555-1001', 'alice.j@email.com', '1985-05-20', 'tier_003', 1250, 850.50, 42, 1704067200, 'active'),
  ('mem_002', 'M00002', 'Bob Smith', '555-1002', 'bob.s@email.com', '1990-08-15', 'tier_002', 450, 320.00, 18, 1709251200, 'active'),
  ('mem_003', 'M00003', 'Carol White', '555-1003', 'carol.w@email.com', '1992-02-09', 'tier_004', 5800, 3200.00, 125, 1672531200, 'active'),
  ('mem_004', 'M00004', 'David Brown', '555-1004', 'david.b@email.com', '1988-11-30', 'tier_001', 120, 85.00, 5, 1735689600, 'active'),
  ('mem_005', 'M00005', 'Emma Davis', '555-1005', 'emma.d@email.com', '1995-03-22', 'tier_002', 680, 445.00, 28, 1714521600, 'active'),
  ('mem_006', 'M00006', 'Frank Miller', '555-1006', 'frank.m@email.com', '1987-07-14', 'tier_003', 1890, 1125.00, 55, 1696118400, 'active'),
  ('mem_007', 'M00007', 'Grace Lee', '555-1007', 'grace.l@email.com', '1993-09-05', 'tier_001', 210, 145.00, 9, 1730419200, 'active'),
  ('mem_008', 'M00008', 'Henry Wilson', '555-1008', 'henry.w@email.com', '1991-12-18', 'tier_002', 540, 385.00, 22, 1717200000, 'active');

-- ============================================
-- DISCOUNT RULES (Comprehensive discount system)
-- ============================================

-- Automatic Discounts (applied automatically when conditions met)
INSERT INTO discount_rules (id, code, name, description, type, calculation_type, value, applies_to, min_purchase, max_discount_amount, valid_from, valid_to, stackable, priority, requires_member, requires_approval, status) VALUES
  -- Flash Sales (High priority, automatic)
  ('disc_001', 'FLASH15', 'Flash Sale 15% Off Beverages', '15% off all beverages', 'automatic', 'percentage', 15, 'category', 0, 10.00, 1738972800, 1741651200, 1, 100, 0, 0, 'active'),
  ('disc_002', 'BOGO_SNACKS', 'Buy One Get One 50% Off Snacks', 'Second snack item 50% off', 'automatic', 'percentage', 50, 'category', 0, NULL, 1738972800, 1741651200, 1, 90, 0, 0, 'active'),
  
  -- Member Tier Discounts (Applied based on tier)
  ('disc_003', 'SILVER_DISC', 'Silver Tier Discount', '3% off for Silver members', 'member_tier', 'percentage', 3, 'bill', 0, NULL, NULL, NULL, 1, 50, 1, 0, 'active'),
  ('disc_004', 'GOLD_DISC', 'Gold Tier Discount', '5% off for Gold members', 'member_tier', 'percentage', 5, 'bill', 0, NULL, NULL, NULL, 1, 50, 1, 0, 'active'),
  ('disc_005', 'PLATINUM_DISC', 'Platinum Tier Discount', '10% off for Platinum members', 'member_tier', 'percentage', 10, 'bill', 0, NULL, NULL, NULL, 1, 50, 1, 0, 'active'),
  
  -- Birthday Discount
  ('disc_006', 'BIRTHDAY', 'Birthday Special', '20% off on birthday (max $25)', 'birthday', 'percentage', 20, 'bill', 0, 25.00, NULL, NULL, 1, 80, 1, 0, 'active'),
  
  -- Coupons (Require code entry)
  ('disc_007', 'SAVE5', '$5 Off $25+ Purchase', '$5 off when you spend $25 or more', 'coupon', 'fixed_amount', 5, 'bill', 25, NULL, 1738972800, 1741651200, 1, 70, 0, 0, 'active'),
  ('disc_008', 'SAVE10', '$10 Off $50+ Purchase', '$10 off when you spend $50 or more', 'coupon', 'fixed_amount', 10, 'bill', 50, NULL, 1738972800, 1741651200, 1, 70, 0, 0, 'active'),
  ('disc_009', 'WELCOME20', '20% Off First Purchase', '20% off for new members', 'coupon', 'percentage', 20, 'bill', 0, 50.00, 1738972800, 1741651200, 0, 75, 1, 0, 'active'),
  
  -- Manual Discounts (Applied by cashier, require approval for high values)
  ('disc_010', 'STAFF_DISC', 'Staff Discount', '15% off for staff purchases', 'manual', 'percentage', 15, 'bill', 0, NULL, NULL, NULL, 0, 60, 0, 1, 'active'),
  ('disc_011', 'DAMAGED_ITEM', 'Damaged Item Discount', 'Discount for damaged products', 'manual', 'percentage', 25, 'line', 0, NULL, NULL, NULL, 1, 60, 0, 1, 'active'),
  ('disc_012', 'MANAGER_DISC', 'Manager Special Discount', 'Manager discretionary discount', 'manual', 'percentage', 30, 'bill', 0, NULL, NULL, NULL, 0, 65, 0, 1, 'active'),
  
  -- Promotional Discounts
  ('disc_013', 'WEEKLY_SPECIAL', 'Weekly Special - Dairy', '10% off all dairy products', 'promotion', 'percentage', 10, 'category', 0, NULL, 1738972800, 1739577600, 1, 85, 0, 0, 'active'),
  ('disc_014', 'CLEARANCE', 'Clearance Sale', '40% off clearance items', 'promotion', 'percentage', 40, 'product', 0, NULL, 1738972800, 1741651200, 1, 95, 0, 0, 'active');

-- ============================================
-- SAMPLE DAILY SESSION (Closed session for demo)
-- ============================================

INSERT INTO daily_sessions (id, terminal_id, store_id, business_date, cash_float, opened_by, open_time, closed_by, close_time, expected_cash, actual_cash, cash_variance, total_sales, total_refunds, total_discounts, transaction_count, status) VALUES
  ('sess_001', 'term_001', 'store_001', '2026-02-08', 200.00, 'user_003', 1739001600, 'user_002', 1739037600, 850.00, 852.50, 2.50, 650.00, 0, 45.50, 28, 'closed');

-- ============================================
-- SAMPLE COMPLETED TRANSACTION (For demo/testing)
-- ============================================

INSERT INTO transactions (id, receipt_number, transaction_type, daily_session_id, terminal_id, store_id, member_id, cashier_id, subtotal, discount_amount, tax_amount, total_amount, payment_status, transaction_time) VALUES
  ('txn_001', 'R001-0001', 'sale', 'sess_001', 'term_001', 'store_001', 'mem_001', 'user_003', 25.47, 2.28, 2.32, 25.51, 'paid', 1739010000);

-- Transaction Lines for sample transaction
INSERT INTO transaction_lines (id, transaction_id, line_number, product_id, barcode, product_name, category_id, unit_price, quantity, subtotal, discount_amount, final_price, tax_amount) VALUES
  ('line_001', 'txn_001', 1, 'prod_001', '7890001', 'Coca-Cola 330ml', 'cat_002', 2.50, 2, 5.00, 0.75, 4.25, 0.43),
  ('line_002', 'txn_001', 2, 'prod_012', '7890012', 'Fresh Milk 1L', 'cat_005', 4.50, 1, 4.50, 0.30, 4.20, 0.42),
  ('line_003', 'txn_001', 3, 'prod_017', '7890017', 'Lays Chips Original 150g', 'cat_006', 3.99, 4, 15.96, 1.20, 14.76, 1.48);

-- Applied Discounts for sample transaction
INSERT INTO transaction_discounts (id, transaction_id, transaction_line_id, discount_rule_id, discount_code, discount_name, discount_type, discount_value, discount_amount, applies_to, requires_approval, applied_by, applied_at) VALUES
  ('tdisc_001', 'txn_001', 'line_001', 'disc_001', 'FLASH15', 'Flash Sale 15% Off Beverages', 'percentage', 15, 0.75, 'line', 0, 'user_003', 1739010000),
  ('tdisc_002', 'txn_001', NULL, 'disc_004', 'GOLD_DISC', 'Gold Tier Discount', 'percentage', 5, 1.27, 'bill', 0, 'user_003', 1739010000),
  ('tdisc_003', 'txn_001', 'line_003', 'disc_002', 'BOGO_SNACKS', 'Buy One Get One 50% Off Snacks', 'percentage', 50, 0.26, 'line', 0, 'user_003', 1739010000);

-- Payment for sample transaction
INSERT INTO payments (id, transaction_id, payment_method, amount, payment_status, payment_time) VALUES
  ('pay_001', 'txn_001', 'Cash', 25.51, 'completed', 1739010000);