INSERT INTO branches (id, name, type, location) VALUES
(1, 'Main Warehouse', 'warehouse', 'Nairobi'),
(2, 'Retail Store', 'store', 'Nairobi');
INSERT INTO users (id, name, phone, email, password_hash, role, branch_id, active) VALUES
(1, 'System Owner', '0700000001', 'owner@example.com', 'demo123', 'owner', 2, 1),
(2, 'Main Cashier', '0700000002', 'cashier@example.com', 'demo123', 'cashier', 2, 1),
(3, 'Route Rep One', '0700000003', 'rep1@example.com', 'demo123', 'sales_rep', 2, 1),
(4, 'Storekeeper', '0700000004', 'storekeeper@example.com', 'demo123', 'storekeeper', 1, 1);
INSERT INTO routes (id, route_code, route_name, sales_rep_id, active) VALUES
(1, 'RT001', 'Town Center Route', 3, 1),
(2, 'RT002', 'Outer Market Route', 3, 1);
INSERT INTO vans (id, van_code, van_name, reg_no, assigned_rep_id, active) VALUES
(1, 'VAN001', 'Main Route Van', 'KDJ 123A', 3, 1);
INSERT INTO price_groups (id, name) VALUES
(1, 'Retail'),
(2, 'Wholesale');
INSERT INTO customers (id, customer_code, customer_type, name, contact_person, phone, route_id, location, credit_limit, balance, price_group_id, active) VALUES
(1, 'WALKIN', 'walkin', 'Walk-in Customer', NULL, NULL, NULL, 'Store', 0, 0, 1, 1),
(2, 'CUST001', 'business', 'Town Traders', 'Alice', '0711111111', 1, 'Town', 50000, 12500, 2, 1),
(3, 'CUST002', 'business', 'Market Home Supplies', 'Brian', '0722222222', 2, 'Market', 35000, 8000, 2, 1);
INSERT INTO categories (id, name, active) VALUES
(1, 'Kitchenware', 1),
(2, 'Plasticware', 1),
(3, 'Cleaning', 1);
INSERT INTO items (id, sku, barcode, item_name, category_id, brand, description, image_path, base_unit, tax_rate, active) VALUES
(1, 'SKU001', '123456789001', 'Plastic Basin', 2, 'HomePro', 'Durable plastic basin', NULL, 'pcs', 16, 1),
(2, 'SKU002', '123456789002', 'Ceramic Mug', 1, 'Casa', 'Everyday ceramic mug', NULL, 'pcs', 16, 1),
(3, 'SKU003', '123456789003', 'Floor Cleaner 1L', 3, 'Sparkle', 'Liquid floor cleaner', NULL, 'pcs', 16, 1);
INSERT INTO item_units (item_id, unit_name, unit_code, conversion_to_base, active) VALUES
(1, 'Piece', 'pcs', 1, 1),(1, 'Set', 'set', 6, 1),(1, 'Dozen', 'dzn', 12, 1),(1, 'Box', 'box', 48, 1),
(2, 'Piece', 'pcs', 1, 1),(2, 'Set', 'set', 6, 1),(2, 'Dozen', 'dzn', 12, 1),(2, 'Box', 'box', 36, 1),
(3, 'Piece', 'pcs', 1, 1),(3, 'Dozen', 'dzn', 12, 1);
INSERT INTO item_prices (item_id, unit_code, price_group_id, selling_price, min_price, active) VALUES
(1, 'pcs', 1, 250, 230, 1),(1, 'set', 1, 1400, 1300, 1),(1, 'dzn', 1, 2700, 2550, 1),(1, 'box', 1, 9800, 9500, 1),
(1, 'pcs', 2, 230, 220, 1),(1, 'box', 2, 9200, 9000, 1),
(2, 'pcs', 1, 120, 100, 1),(2, 'set', 1, 700, 650, 1),(2, 'dzn', 1, 1350, 1300, 1),(2, 'box', 1, 3900, 3800, 1),
(3, 'pcs', 1, 180, 170, 1),(3, 'dzn', 1, 2000, 1900, 1);
INSERT INTO stock_balances (item_id, location_type, location_id, qty_base) VALUES
(1, 'warehouse', 1, 960),(1, 'store', 2, 144),(1, 'van', 1, 96),
(2, 'warehouse', 1, 720),(2, 'store', 2, 120),(2, 'van', 1, 72),
(3, 'warehouse', 1, 300),(3, 'store', 2, 60),(3, 'van', 1, 24);
INSERT INTO sales_orders (id, order_no, customer_id, route_id, rep_id, status, order_date, delivery_date, subtotal, discount_total, tax_total, grand_total, notes) VALUES
(1, 'SO-0001', 2, 1, 3, 'confirmed', CURRENT_DATE(), DATE_ADD(CURRENT_DATE(), INTERVAL 1 DAY), 5200, 0, 0, 5200, 'Urgent shop order'),
(2, 'SO-0002', 3, 2, 3, 'packed', CURRENT_DATE(), DATE_ADD(CURRENT_DATE(), INTERVAL 2 DAY), 3900, 0, 0, 3900, 'Packed for route');
INSERT INTO sales (id, sale_no, sale_type, customer_id, route_id, rep_id, location_id, subtotal, discount_total, tax_total, grand_total, payment_status, status, sold_at) VALUES
(1, 'SAL-0001', 'walkin', 1, NULL, 2, 2, 500, 0, 0, 500, 'paid', 'posted', NOW()),
(2, 'SAL-0002', 'route_sale', 2, 1, 3, 1, 1400, 0, 0, 1400, 'part_paid', 'posted', NOW());
INSERT INTO sale_lines (sale_id, item_id, unit_code, qty, conversion_to_base, qty_base, unit_price, discount, line_total) VALUES
(1, 2, 'pcs', 4, 1, 4, 120, 0, 480),
(2, 1, 'set', 1, 6, 6, 1400, 0, 1400);
INSERT INTO payments (payment_no, customer_id, sale_id, order_id, method, amount, reference_no, received_by, received_at) VALUES
('PAY-0001', 1, 1, NULL, 'cash', 500, 'CASH', 2, NOW()),
('PAY-0002', 2, 2, NULL, 'mpesa', 800, 'MPESA123', 3, NOW());
INSERT INTO dispatches (dispatch_no, order_id, from_location_id, route_id, vehicle_name, status, dispatched_at, delivered_at) VALUES
('DSP-0001', 1, 1, 1, 'Main Route Van', 'pending', NULL, NULL);
INSERT INTO van_loads (id, load_no, van_id, route_id, loaded_by, load_date, status) VALUES
(1, 'LOAD-0001', 1, 1, 4, CURRENT_DATE(), 'posted');
INSERT INTO van_load_lines (load_id, item_id, qty_base) VALUES
(1, 1, 96),(1, 2, 72),(1, 3, 24);