-- RetailOS demo seed (run in phpPgAdmin after schema.sql)
-- WARNING: Deletes all data in RetailOS tables, then inserts demo rows.
-- Staff password for ahmed, zainab, usman, hira: password

BEGIN;

TRUNCATE TABLE
  held_orders, orders, purchase_orders, shifts, registers, products,
  customers, suppliers, staff, categories
RESTART IDENTITY CASCADE;

INSERT INTO categories (id, name, emoji, tax) VALUES
  ('beverages', 'Beverages', '🥤', 13),
  ('snacks', 'Snacks', '🍿', 13),
  ('dairy', 'Dairy', '🥛', 13),
  ('household', 'Household', '🧴', 5),
  ('bakery', 'Bakery', '🥖', 13);

INSERT INTO products (name, sku, category, price, cost, stock, emoji) VALUES
  ('Coca-Cola 1.5L', 'BEV001', 'beverages', 280, 200, 45, '🥤'),
  ('Pepsi 1.5L', 'BEV002', 'beverages', 270, 195, 12, '🥤'),
  ('Mineral Water 500ml', 'BEV003', 'beverages', 60, 35, 120, '💧'),
  ('Lay''s Salted', 'SNK001', 'snacks', 80, 50, 78, '🍟'),
  ('Dairy Milk Bar', 'SNK002', 'snacks', 150, 100, 8, '🍫'),
  ('Mixed Nuts Pack', 'SNK003', 'snacks', 450, 320, 24, '🥜'),
  ('Olper''s Milk 1L', 'DRY001', 'dairy', 320, 250, 32, '🥛'),
  ('Yogurt 500g', 'DRY002', 'dairy', 180, 130, 5, '🥣'),
  ('Surf Excel 1kg', 'HSH001', 'household', 480, 360, 18, '🧺'),
  ('Dish Soap', 'HSH002', 'household', 220, 150, 36, '🧴'),
  ('Whole Wheat Bread', 'BKY001', 'bakery', 140, 95, 22, '🍞'),
  ('Croissant 4-pack', 'BKY002', 'bakery', 360, 240, 14, '🥐');

INSERT INTO customers (name, phone, email, points, spent, visits) VALUES
  ('Ali Khan', '0301-1234567', 'ali@example.com', 2450, 122500, 48),
  ('Sara Ahmed', '0312-9876543', 'sara@example.com', 1820, 91000, 36),
  ('Bilal Hussain', '0345-5556677', NULL, 980, 49000, 22),
  ('Fatima Sheikh', '0333-1112233', 'fatima@example.com', 540, 27000, 14),
  ('Hamza Malik', '0321-4445566', NULL, 220, 11000, 6);

INSERT INTO suppliers (name, emoji, contact, email, phone, categories, terms, status, orders_count) VALUES
  ('Karachi Beverages Co', '🏭', 'Imran Sheikh', 'imran@kbc.pk', '021-1234567', 'Beverages', 'Net 30', 'active', 12),
  ('Lahore Snacks Ltd', '🥨', 'Aysha Tariq', 'aysha@lsl.pk', '042-2345678', 'Snacks', 'Net 15', 'active', 8),
  ('Pakistan Dairy', '🐄', 'Hassan Khan', 'hassan@pkdairy.pk', '051-3456789', 'Dairy, Bakery', 'Net 30', 'active', 15),
  ('Clean Living Ltd', '🧼', 'Maryam Ali', 'maryam@cll.pk', '021-4567890', 'Household', 'Net 45', 'pending', 4);

INSERT INTO purchase_orders (po_number, supplier_id, supplier_name, items, total, status, notes, expected_date) VALUES
  (
    'PO-2026-0001',
    1,
    'Karachi Beverages Co',
    '[{"name":"Coca-Cola 1.5L","qty":50,"cost":200},{"name":"Pepsi 1.5L","qty":50,"cost":195}]'::jsonb,
    19750,
    'received',
    'Weekly beverages restock',
    NOW() - INTERVAL '3 days'
  ),
  (
    'PO-2026-0002',
    2,
    'Lahore Snacks Ltd',
    '[{"name":"Lay''s Salted","qty":100,"cost":50},{"name":"Dairy Milk Bar","qty":60,"cost":100}]'::jsonb,
    11000,
    'pending',
    'Restock low-running snacks',
    NOW() + INTERVAL '2 days'
  ),
  (
    'PO-2026-0003',
    3,
    'Pakistan Dairy',
    '[{"name":"Olper''s Milk 1L","qty":80,"cost":250},{"name":"Yogurt 500g","qty":40,"cost":130}]'::jsonb,
    25200,
    'draft',
    'Dairy order — pending approval',
    NOW() + INTERVAL '5 days'
  );

-- bcrypt hash for password: password
INSERT INTO staff (name, role, email, username, password_hash, status) VALUES
  ('Ahmed Raza', 'Manager', 'ahmed@retailos.pk', 'ahmed', '$2b$10$j.XwLN7L79EujR/rs4jsJe3M18QRGYQ5XH0Qnk8HMgFlYwy9JjuOe', 'active'),
  ('Zainab Iqbal', 'Cashier', 'zainab@retailos.pk', 'zainab', '$2b$10$j.XwLN7L79EujR/rs4jsJe3M18QRGYQ5XH0Qnk8HMgFlYwy9JjuOe', 'active'),
  ('Usman Tariq', 'Supervisor', 'usman@retailos.pk', 'usman', '$2b$10$j.XwLN7L79EujR/rs4jsJe3M18QRGYQ5XH0Qnk8HMgFlYwy9JjuOe', 'active'),
  ('Hira Khan', 'Cashier', 'hira@retailos.pk', 'hira', '$2b$10$j.XwLN7L79EujR/rs4jsJe3M18QRGYQ5XH0Qnk8HMgFlYwy9JjuOe', 'inactive');

INSERT INTO registers (name, location, status, cashier, shift, sales, txns) VALUES
  ('Front Counter', 'Front of store', 'open', 'Zainab Iqbal', 'Morning', 24580, 32),
  ('Side Counter', 'Side of store', 'open', 'Usman Tariq', 'Morning', 18230, 24),
  ('Express Lane', 'Express checkout', 'closed', NULL, NULL, 0, 0);

INSERT INTO shifts (register_id, register_name, cashier, shift_name, opened_at, closed_at, sales, txns, status, opening_cash) VALUES
  (1, 'Front Counter', 'Zainab Iqbal', 'Morning', NOW() - INTERVAL '5 hours', NULL, 24580, 32, 'open', 5000),
  (2, 'Side Counter', 'Usman Tariq', 'Morning', NOW() - INTERVAL '4 hours', NULL, 18230, 24, 'open', 5000),
  (1, 'Front Counter', 'Hira Khan', 'Closing', NOW() - INTERVAL '24 hours', NOW() - INTERVAL '18 hours', 31420, 41, 'closed', 5000);

COMMIT;