-- =====================================================================
--  OPTIONAL demo data so the dashboard, statements and reminders have
--  something to show. Dates are relative to the day you import this file.
--  Import AFTER schema.sql:   mysql -u root -p accounting_db < sample_data.sql
-- =====================================================================
USE accounting_db;

INSERT INTO customers (id, code, name, contact_person, email, phone, address, credit_limit, credit_days, opening_balance, status) VALUES
(1, 'CUS-000001', 'Northwind Traders',     'Amelia Ross',   'accounts@northwind.example', '+1 555 0101', '12 Harbour Road',   20000, 30, 0,    'active'),
(2, 'CUS-000002', 'Bluepeak Hardware',     'Daniel Perera', 'daniel@bluepeak.example',    '+1 555 0102', '88 Market Street',  10000, 30, 0,    'active'),
(3, 'CUS-000003', 'Greenleaf Foods',       'Maya Fernando', 'maya@greenleaf.example',     '+1 555 0103', '4 Orchard Lane',     5000, 30, 500,  'active'),
(4, 'CUS-000004', 'Summit Construction',   'Ravi Silva',    'ravi@summit.example',        '+1 555 0104', '210 Ridge Avenue',  15000, 30, 0,    'active'),
(5, 'CUS-000005', 'Orbit Electronics',     'Nina Jayasuriya','nina@orbit.example',        '+1 555 0105', '7 Tech Park',        3000, 7,  0,    'active'),
(6, 'CUS-000006', 'Lakeside Hotel',        'Chris Walker',  NULL,                         '+1 555 0106', '1 Lake View',        8000, 45, 0,    'inactive');

INSERT INTO suppliers (id, code, name, contact_person, email, phone, address, credit_days, opening_balance, status) VALUES
(1, 'SUP-000001', 'Apex Wholesale',       'Tom Bennett',  'tom@apex.example',      '+1 555 0201', '50 Trade Centre',  30, 0, 'active'),
(2, 'SUP-000002', 'City Power & Utilities', NULL,         'billing@citypower.example', '+1 555 0202', '9 Grid Road',   15, 0, 'active'),
(3, 'SUP-000003', 'Prime Packaging',      'Sara Khan',    'sara@primepack.example', '+1 555 0203', '3 Factory Lane',  30, 0, 'active'),
(4, 'SUP-000004', 'OfficeMart',           'Liam Ng',      'liam@officemart.example','+1 555 0204', '22 Central Plaza', 14, 0, 'active');

INSERT INTO invoices (id, invoice_no, customer_id, invoice_date, due_date, reference, amount, paid_amount, status, notes, created_by) VALUES
(1,  'INV-000001', 1, DATE_SUB(CURDATE(), INTERVAL 75 DAY),  DATE_SUB(CURDATE(), INTERVAL 45 DAY), 'PO-7781', 5000.00, 5000.00, 'paid',      'Quarterly supply contract', 1),
(2,  'INV-000002', 1, DATE_SUB(CURDATE(), INTERVAL 40 DAY),  DATE_SUB(CURDATE(), INTERVAL 10 DAY), 'PO-7802', 3200.00, 1000.00, 'partial',   'Additional stock order', 1),
(3,  'INV-000003', 2, DATE_SUB(CURDATE(), INTERVAL 60 DAY),  DATE_SUB(CURDATE(), INTERVAL 30 DAY), NULL,      7800.00,    0.00, 'unpaid',    'Hardware consignment', 1),
(4,  'INV-000004', 3, DATE_SUB(CURDATE(), INTERVAL 20 DAY),  DATE_ADD(CURDATE(), INTERVAL 10 DAY), NULL,      2400.00,    0.00, 'unpaid',    'Monthly delivery', 1),
(5,  'INV-000005', 3, DATE_SUB(CURDATE(), INTERVAL 12 DAY),  DATE_SUB(CURDATE(), INTERVAL 2 DAY),  NULL,      1850.00,  850.00, 'partial',   'Extra delivery', 1),
(6,  'INV-000006', 4, DATE_SUB(CURDATE(), INTERVAL 5 DAY),   DATE_ADD(CURDATE(), INTERVAL 25 DAY), 'SC-2210', 12500.00,   0.00, 'unpaid',    'Site materials', 1),
(7,  'INV-000007', 5, DATE_SUB(CURDATE(), INTERVAL 3 DAY),   DATE_ADD(CURDATE(), INTERVAL 4 DAY),  NULL,       960.00,    0.00, 'unpaid',    'Components', 1),
(8,  'INV-000008', 2, DATE_SUB(CURDATE(), INTERVAL 1 DAY),   DATE_ADD(CURDATE(), INTERVAL 29 DAY), NULL,      4300.00,    0.00, 'unpaid',    'Tools & fittings', 1),
(9,  'INV-000009', 6, DATE_SUB(CURDATE(), INTERVAL 90 DAY),  DATE_SUB(CURDATE(), INTERVAL 45 DAY), NULL,      2100.00, 2100.00, 'paid',      'Linen supply', 1),
(10, 'INV-000010', 4, DATE_SUB(CURDATE(), INTERVAL 110 DAY), DATE_SUB(CURDATE(), INTERVAL 80 DAY), NULL,      1500.00,    0.00, 'cancelled', 'Cancelled order', 1);

INSERT INTO receipts (id, receipt_no, customer_id, invoice_id, receipt_date, amount, method, cheque_no, cheque_date, bank_name, cheque_status, reference, notes, created_by) VALUES
(1, 'RCT-000001', 1, 1,    DATE_SUB(CURDATE(), INTERVAL 50 DAY), 5000.00, 'bank_transfer', NULL, NULL, NULL, NULL, 'TRF-5521', NULL, 1),
(2, 'RCT-000002', 1, 2,    DATE_SUB(CURDATE(), INTERVAL 8 DAY),  1000.00, 'cheque', 'CHQ-100231', DATE_SUB(CURDATE(), INTERVAL 8 DAY), 'Commercial Bank', 'cleared', NULL, NULL, 1),
(3, 'RCT-000003', 3, 5,    DATE_SUB(CURDATE(), INTERVAL 3 DAY),   850.00, 'cash', NULL, NULL, NULL, NULL, NULL, NULL, 1),
(4, 'RCT-000004', 6, 9,    DATE_SUB(CURDATE(), INTERVAL 58 DAY), 2100.00, 'cheque', 'CHQ-558901', DATE_SUB(CURDATE(), INTERVAL 58 DAY), 'Sampath Bank', 'cleared', NULL, NULL, 1),
(5, 'RCT-000005', 4, NULL, DATE_SUB(CURDATE(), INTERVAL 2 DAY),  5000.00, 'cheque', 'CHQ-330415', DATE_ADD(CURDATE(), INTERVAL 5 DAY), 'HNB', 'pending', NULL, 'Advance on account', 1),
(6, 'RCT-000006', 2, 3,    DATE_SUB(CURDATE(), INTERVAL 15 DAY), 3000.00, 'cheque', 'CHQ-771204', DATE_SUB(CURDATE(), INTERVAL 15 DAY), 'Commercial Bank', 'bounced', NULL, 'Returned - insufficient funds', 1),
(7, 'RCT-000007', 5, NULL, DATE_SUB(CURDATE(), INTERVAL 1 DAY),   300.00, 'card', NULL, NULL, NULL, NULL, NULL, NULL, 1);

INSERT INTO bills (id, bill_no, supplier_id, bill_date, due_date, reference, amount, paid_amount, status, notes, created_by) VALUES
(1, 'BILL-000001', 1, DATE_SUB(CURDATE(), INTERVAL 35 DAY), DATE_SUB(CURDATE(), INTERVAL 5 DAY),  'AW-20931', 8400.00, 8400.00, 'paid',    'Stock purchase', 1),
(2, 'BILL-000002', 2, DATE_SUB(CURDATE(), INTERVAL 30 DAY), DATE_SUB(CURDATE(), INTERVAL 2 DAY),  'CP-4410',  2250.00,    0.00, 'unpaid',  'Electricity', 1),
(3, 'BILL-000003', 3, DATE_SUB(CURDATE(), INTERVAL 10 DAY), DATE_ADD(CURDATE(), INTERVAL 20 DAY), 'PP-1187', 15600.00, 5000.00, 'partial', 'Packaging materials', 1),
(4, 'BILL-000004', 1, DATE_SUB(CURDATE(), INTERVAL 4 DAY),  DATE_ADD(CURDATE(), INTERVAL 3 DAY),  'AW-21377', 1180.00,    0.00, 'unpaid',  'Top-up order', 1),
(5, 'BILL-000005', 4, DATE_SUB(CURDATE(), INTERVAL 2 DAY),  DATE_ADD(CURDATE(), INTERVAL 12 DAY), 'OM-9920',   640.00,    0.00, 'unpaid',  'Stationery', 1);

INSERT INTO payments (id, payment_no, supplier_id, bill_id, payment_date, amount, method, cheque_no, cheque_date, bank_name, cheque_status, reference, notes, created_by) VALUES
(1, 'PAY-000001', 1, 1, DATE_SUB(CURDATE(), INTERVAL 10 DAY), 8400.00, 'bank_transfer', NULL, NULL, NULL, NULL, 'TRF-9921', NULL, 1),
(2, 'PAY-000002', 3, 3, DATE_SUB(CURDATE(), INTERVAL 5 DAY),  5000.00, 'cheque', 'CHQ-884120', DATE_ADD(CURDATE(), INTERVAL 2 DAY), 'People''s Bank', 'pending', NULL, 'Part payment', 1);

-- Invoice line items (each invoice's lines add up to its amount)
INSERT INTO invoice_items (invoice_id, line_no, description, quantity, unit_price, tax_rate) VALUES
(1, 1, 'Standard supply pack',        20, 150.00,  0),
(1, 2, 'Handling & delivery',         10, 200.00,  0),
(2, 1, 'Premium stock item',          10, 200.00, 10),
(2, 2, 'Installation service',         1, 1000.00, 0),
(3, 1, 'Power tools (taxable)',        3, 1000.00, 10),
(3, 2, 'Hand tools',                   9,  500.00, 0),
(4, 1, 'Monthly produce delivery',    12,  200.00, 0),
(5, 1, 'Packaged goods (taxable)',     4,  250.00, 10),
(5, 2, 'Fresh produce',                3,  250.00, 0),
(6, 1, 'Cement bags (taxable)',       50,  100.00, 10),
(6, 2, 'Steel rods',                  14,  500.00, 0),
(7, 1, 'Circuit boards',               8,  120.00, 0),
(8, 1, 'Cutting equipment (taxable)',  4,  500.00, 10),
(8, 2, 'Fittings & fixtures',          7,  300.00, 0),
(9, 1, 'Linen sets',                   7,  300.00, 0),
(10,1, 'Cancelled order',              1, 1500.00, 0);

-- Derive each line's net/tax, then the invoice subtotal and tax from its lines
UPDATE invoice_items SET line_net = ROUND(quantity * unit_price, 2), line_tax = ROUND(ROUND(quantity * unit_price, 2) * tax_rate / 100, 2);
UPDATE invoices i SET i.subtotal = (SELECT COALESCE(SUM(t.line_net),0) FROM invoice_items t WHERE t.invoice_id = i.id),
                      i.tax_total = (SELECT COALESCE(SUM(t.line_tax),0) FROM invoice_items t WHERE t.invoice_id = i.id);

-- Bill line items (each bill's lines add up to its amount)
INSERT INTO bill_items (bill_id, line_no, description, quantity, unit_price, tax_rate) VALUES
(1, 1, 'Wholesale stock - bulk lot',     40, 150.00,  0),
(1, 2, 'Wholesale stock - assorted',     16, 150.00,  0),
(2, 1, 'Electricity usage (units)',     150,  15.00,  0),
(3, 1, 'Corrugated boxes (taxable)',    400,  30.00, 10),
(3, 2, 'Packing tape',                   80,  30.00,  0),
(4, 1, 'Top-up order (taxable)',         10, 100.00, 18),
(5, 1, 'Stationery',                      8,  50.00,  0),
(5, 2, 'Printer paper',                   4,  60.00,  0);

UPDATE bill_items SET line_net = ROUND(quantity * unit_price, 2), line_tax = ROUND(ROUND(quantity * unit_price, 2) * tax_rate / 100, 2);
UPDATE bills b SET b.subtotal = (SELECT COALESCE(SUM(t.line_net),0) FROM bill_items t WHERE t.bill_id = b.id),
                   b.tax_total = (SELECT COALESCE(SUM(t.line_tax),0) FROM bill_items t WHERE t.bill_id = b.id);