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

SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;
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));

-- ------------------------------------------------------------
-- Users (default passwords)
--  bcrypt for 'password': $2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi
--  Change immediately after first login.
-- ------------------------------------------------------------
INSERT INTO users (company_id, name, email, password_hash, role_id, last_login)
VALUES
  (
    (SELECT id FROM companies WHERE name='Brownhill Group Limited'),
    'System Administrator',
    'admin@brownhill.example',
    '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',
    (SELECT id FROM roles WHERE name='Admin'),
    NULL
  ),
  (
    (SELECT id FROM companies WHERE name='Brownhill Group Limited'),
    'Operations User',
    'ops@brownhill.example',
    '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',
    (SELECT id FROM roles WHERE name='Ops'),
    NULL
  ),
  (
    (SELECT id FROM companies WHERE name='Brownhill Group Limited'),
    'Accounts User',
    'accounts@brownhill.example',
    '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',
    (SELECT id FROM roles WHERE name='Accounts'),
    NULL
  );

-- ------------------------------------------------------------
-- 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)
  );

-- ------------------------------------------------------------
-- Bookings
-- ------------------------------------------------------------
INSERT INTO bookings (
  company_id, booking_ref, partner_id, booking_type, hours,
  client_name, client_phone, client_email,
  pickup_date, pickup_time, pickup_address, via, dropoff_address,
  pax_count, luggage_count, notes, status,
  total_client_price, total_driver_price, created_by, created_at
)
VALUES
  (
    (SELECT id FROM companies WHERE name='Brownhill Group Limited'),
    'BKG-0001',
    (SELECT id FROM partners WHERE name='City Chauffeur Partners'),
    'Transfer',
    NULL,
    'Alex Turner', '+44 7700 111111', 'alex.turner@example.com',
    DATE_ADD(CURDATE(), INTERVAL 1 DAY), '08:30:00',
    'Hilton Park Lane, London',
    'Via: Buckingham Palace',
    'Heathrow Terminal 5',
    2, 2,
    'Meet & greet required.',
    'Confirmed',
    120.00, 80.00,
    (SELECT id FROM users WHERE email='ops@brownhill.example'),
    NOW()
  ),
  (
    (SELECT id FROM companies WHERE name='Brownhill Group Limited'),
    'BKG-0002',
    NULL,
    'As Directed',
    4.00,
    'Emma Watson', '+44 7700 222222', 'emma.watson@example.com',
    DATE_ADD(CURDATE(), INTERVAL 2 DAY), '14:00:00',
    'St. Pancras Renaissance Hotel',
    NULL,
    'London City (multiple stops)',
    3, 3,
    'Client requests bottled water and Wi-Fi.',
    'New',
    300.00, 180.00,
    (SELECT id FROM users WHERE email='ops@brownhill.example'),
    NOW()
  );

-- ------------------------------------------------------------
-- 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
  );

-- ------------------------------------------------------------
-- Audit Logs
-- ------------------------------------------------------------
INSERT INTO audit_logs (user_id, action, entity_type, entity_id, diff, timestamp)
VALUES
  (
    (SELECT id FROM users WHERE email='ops@brownhill.example'),
    'booking.create',
    'booking',
    (SELECT id FROM bookings WHERE booking_ref='BKG-0001'),
    JSON_OBJECT('status_from', NULL, 'status_to', 'Confirmed'),
    NOW()
  ),
  (
    (SELECT id FROM users WHERE email='ops@brownhill.example'),
    'booking.assign_driver',
    'booking',
    (SELECT id FROM bookings WHERE booking_ref='BKG-0001'),
    JSON_OBJECT('driver', 'Michael Green', 'vehicle', 'LS69 SCL'),
    NOW()
  ),
  (
    (SELECT id FROM users WHERE email='accounts@brownhill.example'),
    'invoice.create',
    'invoice',
    (SELECT id FROM invoices WHERE invoice_no='INV-0001'),
    JSON_OBJECT('total', 144.00),
    NOW()
  );

COMMIT;