-- ============================================================
-- CMRS — Seed Data
-- File: database/seed.sql
-- Requires: database/schema.sql already applied
-- ============================================================

START TRANSACTION;

-- ------------------------------------------------------------
-- Companies
-- ------------------------------------------------------------
INSERT INTO companies (name, base_currency, address, phone, email, logo_url, invoice_template_id)
VALUES
  ('Brownhill Group Limited', 'GBP', 'London, United Kingdom', '+44 20 0000 0000', 'info@brownhill.example', NULL, NULL);

-- ------------------------------------------------------------
-- Roles
-- ------------------------------------------------------------
INSERT INTO roles (name, permissions) VALUES
  ('Admin',       JSON_OBJECT('all', TRUE)),
  ('Ops',         JSON_OBJECT('bookings', TRUE, 'drivers', TRUE, 'vehicles', TRUE, 'invoices', TRUE)),
  ('Sales',       JSON_OBJECT('quotes', TRUE, 'partners', TRUE)),
  ('Accounts',    JSON_OBJECT('invoices', TRUE, 'ledger', TRUE, 'payouts', TRUE)),
  ('Management',  JSON_OBJECT('reports', TRUE));

-- ------------------------------------------------------------
-- Partners
-- ------------------------------------------------------------
INSERT INTO partners (company_id, name, contact_name, phone, email, commission_type, commission_value, notes)
VALUES
  (
    (SELECT id FROM companies WHERE name='Brownhill Group Limited'),
    'City Chauffeur Partners',
    'John Carter',
    '+44 20 1111 1111',
    'john.carter@citychauffeur.example',
    'percent',
    12.50,
    'Standard partner rate 12.5%'
  ),
  (
    (SELECT id FROM companies WHERE name='Brownhill Group Limited'),
    'Heathrow Transfers Ltd',
    'Sophie Miles',
    '+44 20 2222 2222',
    'ops@heathrowtransfers.example',
    'fixed',
    15.00,
    'Fixed per booking commission £15'
  );

-- ------------------------------------------------------------
-- Drivers
-- ------------------------------------------------------------
INSERT INTO drivers (company_id, name, phone, email, bank_details, address, photo_url, is_active)
VALUES
  (
    (SELECT id FROM companies WHERE name='Brownhill Group Limited'),
    'Michael Green',
    '+44 7700 900001',
    'michael.green@drivers.example',
    JSON_OBJECT('bank_name','Barclays','account_name','M GREEN','account_number','12345678','sort_code','12-34-56'),
    '10 Fleet Street, London',
    NULL,
    1
  ),
  (
    (SELECT id FROM companies WHERE name='Brownhill Group Limited'),
    'Amelia Clarke',
    '+44 7700 900002',
    'amelia.clarke@drivers.example',
    JSON_OBJECT('bank_name','HSBC','account_name','A CLARKE','account_number','87654321','sort_code','65-43-21'),
    '22 Regent Street, London',
    NULL,
    1
  );

-- ------------------------------------------------------------
-- Vehicles
-- ------------------------------------------------------------
INSERT INTO vehicles (company_id, reg_no, type, make_model, capacity, current_status, last_service_date)
VALUES
  (
    (SELECT id FROM companies WHERE name='Brownhill Group Limited'),
    'LB70 VCL', 'MPV', 'Mercedes-Benz V-Class', '7 pax, 6 bags', 'available', DATE_SUB(CURDATE(), INTERVAL 30 DAY)
  ),
  (
    (SELECT id FROM companies WHERE name='Brownhill Group Limited'),
    'LS69 SCL', 'Sedan', 'Mercedes-Benz S-Class', '3 pax, 2 bags', 'available', DATE_SUB(CURDATE(), INTERVAL 60 DAY)
  ),
  (
    (SELECT id FROM companies WHERE name='Brownhill Group Limited'),
    'LT22 EQS', 'Sedan', 'Mercedes-Benz EQS', '3 pax, 2 bags', 'maintenance', DATE_SUB(CURDATE(), INTERVAL 10 DAY)
  );

-- ------------------------------------------------------------
-- Booking Vehicles (multi-vehicle/driver per booking)
-- ------------------------------------------------------------
INSERT INTO booking_vehicles (
  booking_id, vehicle_id, vehicle_type, vehicle_reg_no, driver_id, driver_price, client_price, sequence_order
)
VALUES
  (
    (SELECT id FROM bookings WHERE booking_ref='BKG-0001'),
    (SELECT id FROM vehicles WHERE reg_no='LS69 SCL'),
    'Sedan', 'LS69 SCL',
    (SELECT id FROM drivers WHERE name='Michael Green'),
    80.00, 120.00, 1
  ),
  (
    (SELECT id FROM bookings WHERE booking_ref='BKG-0002'),
    (SELECT id FROM vehicles WHERE reg_no='LB70 VCL'),
    'MPV', 'LB70 VCL',
    (SELECT id FROM drivers WHERE name='Amelia Clarke'),
    180.00, 300.00, 1
  );

-- ------------------------------------------------------------
-- Driver Documents
-- ------------------------------------------------------------
INSERT INTO driver_documents (driver_id, doc_type, upload_url, expiry_date, optional_flag)
VALUES
  (
    (SELECT id FROM drivers WHERE name='Michael Green'),
    'license',
    NULL,
    DATE_ADD(CURDATE(), INTERVAL 10 MONTH),
    0
  ),
  (
    (SELECT id FROM drivers WHERE name='Amelia Clarke'),
    'insurance',
    NULL,
    DATE_ADD(CURDATE(), INTERVAL 8 MONTH),
    0
  );

-- ------------------------------------------------------------
-- Rentals
-- ------------------------------------------------------------
INSERT INTO rentals (vehicle_id, renter_name, phone, weekly_rate, deposit_amount, start_date, end_date, deposit_refunded_flag, status)
VALUES
  (
    (SELECT id FROM vehicles WHERE reg_no='LB70 VCL'),
    'Daniel Reed',
    '+44 7700 333333',
    450.00, 300.00,
    DATE_SUB(CURDATE(), INTERVAL 7 DAY),
    DATE_ADD(CURDATE(), INTERVAL 7 DAY),
    0,
    'active'
  );

-- ------------------------------------------------------------
-- Invoices (one for booking, one for rental)
-- ------------------------------------------------------------
INSERT INTO invoices (
  company_id, invoice_no, related_booking_id, related_rental_id,
  client_name, partner_name, issue_date, due_date,
  subtotal, vat, total, pdf_url, status
)
VALUES
  (
    (SELECT id FROM companies WHERE name='Brownhill Group Limited'),
    'INV-0001',
    (SELECT id FROM bookings WHERE booking_ref='BKG-0001'),
    NULL,
    'Alex Turner', 'City Chauffeur Partners',
    CURDATE(), DATE_ADD(CURDATE(), INTERVAL 14 DAY),
    120.00, 24.00, 144.00,
    NULL, 'sent'
  ),
  (
    (SELECT id FROM companies WHERE name='Brownhill Group Limited'),
    'INV-0002',
    NULL,
    (SELECT id FROM rentals WHERE renter_name='Daniel Reed'),
    'Daniel Reed', NULL,
    CURDATE(), DATE_ADD(CURDATE(), INTERVAL 14 DAY),
    450.00, 90.00, 540.00,
    NULL, 'draft'
  );

-- ------------------------------------------------------------
-- Invoice Lines
-- ------------------------------------------------------------
INSERT INTO invoice_lines (invoice_id, description, qty, unit_price, line_total)
VALUES
  (
    (SELECT id FROM invoices WHERE invoice_no='INV-0001'),
    'Airport Transfer (Hilton Park Lane → Heathrow T5)',
    1.00, 120.00, 120.00
  ),
  (
    (SELECT id FROM invoices WHERE invoice_no='INV-0002'),
    'Weekly Rental (Mercedes V-Class LB70 VCL)',
    1.00, 450.00, 450.00
  );

-- ------------------------------------------------------------
-- Transactions (Ledger)
-- ------------------------------------------------------------
INSERT INTO transactions (company_id, date, account_code, type, amount, booking_id, rental_id, notes)
VALUES
  (
    (SELECT id FROM companies WHERE name='Brownhill Group Limited'),
    CURDATE(),
    '4000',
    'income',
    144.00,
    (SELECT id FROM bookings WHERE booking_ref='BKG-0001'),
    NULL,
    'Invoice INV-0001 income incl VAT'
  ),
  (
    (SELECT id FROM companies WHERE name='Brownhill Group Limited'),
    CURDATE(),
    '7000',
    'expense',
    15.00,
    NULL,
    NULL,
    'Congestion charge'
  );

-- ------------------------------------------------------------
-- Payouts (Driver / Partner)
-- ------------------------------------------------------------
INSERT INTO payouts (company_id, driver_id, partner_id, period_start, period_end, total_amount, payment_method, sent_flag)
VALUES
  (
    (SELECT id FROM companies WHERE name='Brownhill Group Limited'),
    (SELECT id FROM drivers WHERE name='Michael Green'),
    NULL,
    DATE_SUB(CURDATE(), INTERVAL 7 DAY), CURDATE(),
    80.00,
    'bank_transfer',
    0
  ),
  (
    (SELECT id FROM companies WHERE name='Brownhill Group Limited'),
    NULL,
    (SELECT id FROM partners WHERE name='City Chauffeur Partners'),
    DATE_SUB(CURDATE(), INTERVAL 7 DAY), CURDATE(),
    15.00,
    'bank_transfer',
    0
  );

COMMIT;
