-- Hotel ERP Dummy Data Seed
-- ROLES
INSERT INTO roles (slug, name, permissions) VALUES
('super_admin', 'Super Admin', 'all'),
('hotel_manager', 'Hotel Manager', 'manage_hotel,view_reports,manage_staff'),
('receptionist', 'Receptionist', 'manage_bookings,checkin,checkout'),
('accountant', 'Accountant', 'manage_finance,view_reports'),
('housekeeper', 'Housekeeper', 'manage_rooms,manage_laundry'),
('chef', 'Chef/Kitchen Staff', 'manage_kot,manage_menu'),
('storekeeper', 'Storekeeper', 'manage_inventory,manage_po'),
('hr_officer', 'HR Officer', 'manage_employees,manage_payroll'),
('maintenance', 'Maintenance Staff', 'manage_maintenance,manage_assets');

-- USERS (passwords are 'password' hashed)
-- bcrypt hash of 'password': $2y$12$N2.8hbLETjbm40KNvHAmouA8.1O4ymwOnXGizGjNnPGtXChdP2TUG
INSERT INTO users (name, email, password, role, phone, active) VALUES
('System Admin', 'admin@hotel.test', '$2y$12$N2.8hbLETjbm40KNvHAmouA8.1O4ymwOnXGizGjNnPGtXChdP2TUG', 'super_admin', '+1234567890', 1),
('Sarah Manager', 'manager@hotel.test', '$2y$12$N2.8hbLETjbm40KNvHAmouA8.1O4ymwOnXGizGjNnPGtXChdP2TUG', 'hotel_manager', '+1234567891', 1),
('John Front Desk', 'reception@hotel.test', '$2y$12$N2.8hbLETjbm40KNvHAmouA8.1O4ymwOnXGizGjNnPGtXChdP2TUG', 'receptionist', '+1234567892', 1),
('Mary Accountant', 'accounts@hotel.test', '$2y$12$N2.8hbLETjbm40KNvHAmouA8.1O4ymwOnXGizGjNnPGtXChdP2TUG', 'accountant', '+1234567893', 1),
('Peter Housekeeper', 'housekeeping@hotel.test', '$2y$12$N2.8hbLETjbm40KNvHAmouA8.1O4ymwOnXGizGjNnPGtXChdP2TUG', 'housekeeper', '+1234567894', 1),
('Linda HR', 'hr@hotel.test', '$2y$12$N2.8hbLETjbm40KNvHAmouA8.1O4ymwOnXGizGjNnPGtXChdP2TUG', 'hr_officer', '+1234567895', 1);

-- PROPERTIES
INSERT INTO properties (name, type, address, city, country, phone, email, currency) VALUES
('Grand Royale Hotel', 'hotel', '123 Ocean Drive', 'Miami', 'USA', '+1-555-0100', 'info@grandroyale.test', 'USD'),
('Sunset Beach Resort', 'resort', '456 Palm Avenue', 'Mombasa', 'Kenya', '+254-700-100100', 'stay@sunsetbeach.test', 'KES'),
('City Suites Apartments', 'apartments', '789 Downtown Blvd', 'Nairobi', 'Kenya', '+254-700-200200', 'hello@citysuites.test', 'KES'),
('Mountain View Lodge', 'lodge', '12 Hilltop Road', 'Nakuru', 'Kenya', '+254-700-300300', 'book@mountainview.test', 'KES'),
('Azure Airbnb Studio', 'airbnb', '34 Sky Lane', 'Cape Town', 'South Africa', '+27-21-555-0001', 'stay@azurestudio.test', 'ZAR');

-- ROOM TYPES
INSERT INTO room_types (property_id, name, description, base_rate, max_occupancy, amenities) VALUES
(1, 'Standard Room', 'Comfortable room with queen bed', 120.00, 2, 'WiFi, AC, TV, Mini-bar'),
(1, 'Deluxe Room', 'Spacious room with city view', 180.00, 2, 'WiFi, AC, TV, Mini-bar, Balcony'),
(1, 'Executive Suite', 'Two-room suite for business travelers', 320.00, 3, 'WiFi, AC, TV, Mini-bar, Lounge, Workspace'),
(1, 'Presidential Suite', 'Luxury suite with ocean view', 650.00, 4, 'WiFi, AC, TV, Jacuzzi, Butler service'),
(2, 'Beach Villa', 'Private villa with beach access', 280.00, 4, 'WiFi, Pool, Beach access, Kitchen');

-- ROOMS
INSERT INTO rooms (property_id, room_type_id, room_number, floor, status, key_card_code) VALUES
(1, 1, '101', '1', 'vacant', 'KC101A'),
(1, 1, '102', '1', 'occupied', 'KC102A'),
(1, 2, '201', '2', 'cleaning', 'KC201A'),
(1, 2, '202', '2', 'vacant', 'KC202A'),
(1, 3, '301', '3', 'occupied', 'KC301A'),
(1, 4, '401', '4', 'maintenance', 'KC401A'),
(2, 5, 'V1', 'G', 'vacant', 'KCV1A'),
(2, 5, 'V2', 'G', 'occupied', 'KCV2A');

-- GUESTS
INSERT INTO guests (first_name, last_name, email, phone, id_type, id_number, nationality, vip, loyalty_points) VALUES
('Michael', 'Johnson', 'michael.j@email.test', '+1-555-1001', 'passport', 'P12345678', 'USA', 0, 250),
('Emma', 'Williams', 'emma.w@email.test', '+44-20-555-2001', 'passport', 'P87654321', 'UK', 1, 1500),
('Liam', 'Otieno', 'liam.o@email.test', '+254-722-555001', 'national_id', '12345678', 'Kenya', 0, 120),
('Aisha', 'Mohammed', 'aisha.m@email.test', '+971-50-555-3001', 'passport', 'P98765432', 'UAE', 1, 2200),
('Carlos', 'Garcia', 'carlos.g@email.test', '+34-91-555-4001', 'passport', 'P11223344', 'Spain', 0, 80),
('Yuki', 'Tanaka', 'yuki.t@email.test', '+81-3-555-5001', 'passport', 'P55667788', 'Japan', 0, 340);

-- RESERVATIONS
INSERT INTO reservations (booking_ref, guest_id, room_id, property_id, check_in, check_out, adults, rate, total, paid, status, source) VALUES
('BK-2026-0001', 1, 2, 1, CURDATE(), DATE_ADD(CURDATE(), INTERVAL 3 DAY), 2, 120.00, 360.00, 360.00, 'checked_in', 'direct'),
('BK-2026-0002', 2, 5, 1, CURDATE(), DATE_ADD(CURDATE(), INTERVAL 5 DAY), 1, 320.00, 1600.00, 800.00, 'checked_in', 'booking.com'),
('BK-2026-0003', 3, 4, 1, DATE_ADD(CURDATE(), INTERVAL 2 DAY), DATE_ADD(CURDATE(), INTERVAL 4 DAY), 2, 180.00, 360.00, 0.00, 'confirmed', 'direct'),
('BK-2026-0004', 4, 8, 2, DATE_ADD(CURDATE(), INTERVAL 1 DAY), DATE_ADD(CURDATE(), INTERVAL 7 DAY), 3, 280.00, 1680.00, 1680.00, 'confirmed', 'expedia'),
('BK-2026-0005', 5, 1, 1, DATE_SUB(CURDATE(), INTERVAL 5 DAY), DATE_SUB(CURDATE(), INTERVAL 2 DAY), 1, 120.00, 360.00, 360.00, 'checked_out', 'airbnb'),
('BK-2026-0006', 6, 7, 2, DATE_ADD(CURDATE(), INTERVAL 10 DAY), DATE_ADD(CURDATE(), INTERVAL 14 DAY), 2, 280.00, 1120.00, 0.00, 'pending', 'agoda');

-- RESERVATION GROUPS
INSERT INTO reservation_groups (name, contact_name, contact_phone, rooms_count, notes) VALUES
('Acme Corp Conference', 'David Chen', '+1-555-9001', 15, 'Annual sales conference'),
('Johnson Wedding Party', 'Sarah Johnson', '+1-555-9002', 8, 'Wedding ceremony weekend'),
('Tour Group Asia', 'Mr. Lee', '+82-2-555-9003', 12, 'Korean tour package'),
('Tech Summit 2026', 'Maria Rodriguez', '+1-555-9004', 25, 'Tech industry leaders'),
('Football Team Stay', 'Coach Williams', '+44-20-555-9005', 22, 'Match weekend');

-- NIGHT AUDITS
INSERT INTO night_audits (audit_date, occupancy_rate, total_revenue, rooms_sold, no_shows, audited_by) VALUES
(DATE_SUB(CURDATE(), INTERVAL 1 DAY), 75.50, 4520.00, 6, 1, 1),
(DATE_SUB(CURDATE(), INTERVAL 2 DAY), 80.00, 5100.00, 7, 0, 1),
(DATE_SUB(CURDATE(), INTERVAL 3 DAY), 62.50, 3800.00, 5, 2, 1),
(DATE_SUB(CURDATE(), INTERVAL 4 DAY), 87.50, 6200.00, 7, 0, 1),
(DATE_SUB(CURDATE(), INTERVAL 5 DAY), 70.00, 4100.00, 5, 1, 1);

-- OTAS
INSERT INTO otas (name, code, api_endpoint, active, last_sync_at) VALUES
('Booking.com', 'booking', 'https://api.booking.com/v1', 1, NOW()),
('Airbnb', 'airbnb', 'https://api.airbnb.com/v2', 1, NOW()),
('Expedia Group', 'expedia', 'https://api.expedia.com/v3', 1, NOW()),
('Agoda', 'agoda', 'https://api.agoda.com/v1', 1, NOW()),
('Beds24', 'beds24', 'https://api.beds24.com/v2', 0, NULL);

-- CHANNEL LOGS
INSERT INTO channel_logs (ota_id, direction, action, status, message) VALUES
(1, 'outbound', 'inventory_sync', 'success', 'Synced 50 room availabilities'),
(2, 'inbound', 'new_booking', 'success', 'Imported booking #ABNB-9921'),
(3, 'outbound', 'rate_update', 'success', 'Updated rates for next 30 days'),
(4, 'outbound', 'inventory_sync', 'failed', 'API timeout — retry queued'),
(1, 'inbound', 'cancellation', 'success', 'Processed cancellation #BKG-7782');

-- CHANNEL MAPPINGS
INSERT INTO channel_mappings (ota_id, room_type_id, external_room_id, external_rate_plan) VALUES
(1, 1, 'BKG-RT-1001', 'BAR-FLEX'),
(1, 2, 'BKG-RT-1002', 'BAR-FLEX'),
(2, 1, 'ABNB-RM-2001', 'STANDARD'),
(3, 3, 'EXP-RT-3001', 'CORPORATE'),
(4, 5, 'AGD-RT-4001', 'PROMO');

-- WAKEUP CALLS
INSERT INTO wakeup_calls (reservation_id, guest_id, room_id, wake_time, status) VALUES
(1, 1, 2, DATE_ADD(NOW(), INTERVAL 8 HOUR), 'scheduled'),
(2, 2, 5, DATE_ADD(NOW(), INTERVAL 10 HOUR), 'scheduled'),
(3, 3, 4, DATE_ADD(NOW(), INTERVAL 20 HOUR), 'scheduled'),
(4, 4, 8, DATE_ADD(NOW(), INTERVAL 30 HOUR), 'scheduled'),
(5, 5, 1, DATE_SUB(NOW(), INTERVAL 1 DAY), 'completed');

-- CONCIERGE
INSERT INTO concierge_requests (guest_id, room_id, request_type, description, status, assigned_to) VALUES
(1, 2, 'transport', 'Airport pickup tomorrow 7am', 'pending', 3),
(2, 5, 'reservation', 'Dinner booking at Italian restaurant', 'completed', 3),
(3, 4, 'tour', 'City sightseeing tour for 2', 'in_progress', 3),
(4, 8, 'spa', 'Couples massage booking', 'pending', 3),
(2, 5, 'flowers', 'Anniversary bouquet to room', 'completed', 3);

-- HOUSEKEEPING TASKS
INSERT INTO housekeeping_tasks (room_id, assigned_to, task_type, priority, status, scheduled_at) VALUES
(3, 5, 'cleaning', 'normal', 'in_progress', NOW()),
(1, 5, 'turnover', 'high', 'pending', DATE_ADD(NOW(), INTERVAL 2 HOUR)),
(4, 5, 'cleaning', 'normal', 'pending', DATE_ADD(NOW(), INTERVAL 4 HOUR)),
(6, 5, 'deep_clean', 'urgent', 'pending', NOW()),
(7, 5, 'inspection', 'low', 'completed', DATE_SUB(NOW(), INTERVAL 1 DAY)),
(2, 5, 'cleaning', 'normal', 'verified', DATE_SUB(NOW(), INTERVAL 2 DAY));

-- LAUNDRY
INSERT INTO laundry (room_id, guest_id, item_description, qty, status, cost) VALUES
(2, 1, 'Bed sheets set', 2, 'washing', 0.00),
(5, 2, 'Guest suits (dry clean)', 3, 'received', 45.00),
(4, 3, 'Towels & bathrobes', 8, 'ready', 0.00),
(8, 4, 'Shirts (express)', 5, 'delivered', 25.00),
(7, 6, 'Curtains', 4, 'washing', 0.00);

-- LOST & FOUND
INSERT INTO lost_found (item_description, found_location, found_date, found_by, status) VALUES
('Black leather wallet', 'Room 102', CURDATE(), 5, 'held'),
('iPhone 14 Pro', 'Restaurant', DATE_SUB(CURDATE(), INTERVAL 1 DAY), 5, 'claimed'),
('Gold wedding ring', 'Pool area', DATE_SUB(CURDATE(), INTERVAL 3 DAY), 5, 'held'),
('Sunglasses (Ray-Ban)', 'Lobby', DATE_SUB(CURDATE(), INTERVAL 5 DAY), 5, 'held'),
('Laptop charger', 'Room 301', DATE_SUB(CURDATE(), INTERVAL 7 DAY), 5, 'disposed');

-- POS OUTLETS
INSERT INTO pos_outlets (name, type, active) VALUES
('Royale Restaurant', 'restaurant', 1),
('Sky Bar', 'bar', 1),
('Lobby Café', 'cafe', 1),
('Pool Bar', 'bar', 1),
('Room Service', 'room_service', 1);

-- POS TABLES
INSERT INTO pos_tables (outlet_id, table_number, capacity, status) VALUES
(1, 'T1', 4, 'available'),
(1, 'T2', 2, 'occupied'),
(1, 'T3', 6, 'reserved'),
(1, 'T4', 4, 'available'),
(2, 'B1', 8, 'occupied');

-- POS MENU
INSERT INTO pos_menu (outlet_id, name, category, price, description, available) VALUES
(1, 'Grilled Salmon', 'Main', 28.50, 'Atlantic salmon with lemon butter', 1),
(1, 'Caesar Salad', 'Starter', 12.00, 'Classic with romaine and parmesan', 1),
(1, 'Beef Tenderloin', 'Main', 38.00, '8oz with red wine jus', 1),
(2, 'Mojito', 'Cocktail', 9.50, 'Cuban classic with fresh mint', 1),
(3, 'Cappuccino', 'Hot Drinks', 4.50, 'Italian espresso with steamed milk', 1),
(1, 'Tiramisu', 'Dessert', 8.00, 'Classic Italian dessert', 1);

-- POS ORDERS
INSERT INTO pos_orders (order_number, outlet_id, table_id, reservation_id, guest_id, subtotal, tax, total, status, payment_method, created_by) VALUES
('POS-2026-0001', 1, 2, 1, 1, 40.50, 4.05, 44.55, 'paid', 'card', 3),
('POS-2026-0002', 1, 5, 2, 2, 76.00, 7.60, 83.60, 'paid', 'room', 3),
('POS-2026-0003', 2, 5, NULL, NULL, 19.00, 1.90, 20.90, 'served', 'cash', 3),
('POS-2026-0004', 3, NULL, NULL, NULL, 13.50, 1.35, 14.85, 'open', NULL, 3),
('POS-2026-0005', 1, 3, NULL, NULL, 56.00, 5.60, 61.60, 'kitchen', NULL, 3);

-- POS ORDER ITEMS
INSERT INTO pos_order_items (order_id, menu_id, qty, price) VALUES
(1, 1, 1, 28.50),
(1, 2, 1, 12.00),
(2, 3, 2, 38.00),
(3, 4, 2, 9.50),
(4, 5, 3, 4.50),
(5, 1, 2, 28.00);

-- SUPPLIERS
INSERT INTO suppliers (name, contact_person, email, phone, address, payment_terms) VALUES
('Fresh Foods Co.', 'James Mwangi', 'sales@freshfoods.test', '+254-700-100001', 'Industrial Area, Nairobi', 'Net 30'),
('Premium Linens Ltd', 'Lisa Wang', 'orders@premiumlinens.test', '+1-555-7001', '789 Textile St, Miami', 'Net 15'),
('CleanPro Supplies', 'Ahmed Hassan', 'info@cleanpro.test', '+254-700-100002', 'Westlands, Nairobi', 'Net 30'),
('Beverage Distributors', 'Carlos Mendez', 'sales@bevdist.test', '+34-91-555-7003', '12 Vintner Lane, Madrid', 'COD'),
('Office Equipment Inc', 'Tom Baker', 'office@oeinc.test', '+1-555-7005', '456 Business Park', 'Net 45');

-- INVENTORY STORES
INSERT INTO inventory_stores (name, location) VALUES
('Main Warehouse', 'Basement Level B1'),
('Kitchen Storeroom', '1st Floor Kitchen'),
('Housekeeping Stock', '2nd Floor Linen Room'),
('Bar Storage', 'Sky Bar Backroom'),
('Maintenance Workshop', 'Basement B2');

-- INVENTORY ITEMS
INSERT INTO inventory_items (sku, barcode, name, category, unit, cost_price, sell_price, stock_qty, reorder_level, store_id, supplier_id) VALUES
('TWL-001', '5901234123457', 'Bath Towel (white)', 'Linen', 'pcs', 8.00, 0.00, 250, 50, 3, 2),
('BED-001', '5901234123458', 'King Bed Sheet Set', 'Linen', 'set', 35.00, 0.00, 80, 20, 3, 2),
('SOAP-001', '5901234123459', 'Hand Soap 250ml', 'Toiletries', 'btl', 1.20, 0.00, 500, 100, 3, 3),
('WINE-001', '5901234123460', 'House Red Wine', 'Beverage', 'btl', 12.00, 35.00, 65, 12, 4, 4),
('CHKN-001', '5901234123461', 'Chicken Breast (kg)', 'Food', 'kg', 6.50, 0.00, 25, 10, 2, 1),
('TONR-001', '5901234123462', 'Printer Toner Black', 'Office', 'pcs', 45.00, 0.00, 8, 3, 1, 5);

-- PURCHASE ORDERS
INSERT INTO purchase_orders (po_number, supplier_id, order_date, expected_date, total, status) VALUES
('PO-2026-0001', 1, CURDATE(), DATE_ADD(CURDATE(), INTERVAL 3 DAY), 850.00, 'sent'),
('PO-2026-0002', 2, DATE_SUB(CURDATE(), INTERVAL 5 DAY), DATE_ADD(CURDATE(), INTERVAL 5 DAY), 2400.00, 'received'),
('PO-2026-0003', 3, DATE_SUB(CURDATE(), INTERVAL 2 DAY), DATE_ADD(CURDATE(), INTERVAL 1 DAY), 320.00, 'sent'),
('PO-2026-0004', 4, DATE_SUB(CURDATE(), INTERVAL 1 DAY), CURDATE(), 1200.00, 'draft'),
('PO-2026-0005', 5, DATE_SUB(CURDATE(), INTERVAL 10 DAY), DATE_SUB(CURDATE(), INTERVAL 3 DAY), 540.00, 'received');

-- PURCHASE ORDER ITEMS
INSERT INTO purchase_order_items (po_id, item_id, qty, cost, received_qty) VALUES
(1, 5, 100, 6.50, 0),
(2, 1, 200, 8.00, 200),
(2, 2, 50, 35.00, 50),
(3, 3, 200, 1.20, 0),
(4, 4, 50, 12.00, 0);

-- ACCOUNTS (Chart of Accounts)
INSERT INTO accounts (code, name, type, balance) VALUES
('1000', 'Cash', 'asset', 25000.00),
('1100', 'Bank — Main Account', 'asset', 180000.00),
('1200', 'Accounts Receivable', 'asset', 12500.00),
('2000', 'Accounts Payable', 'liability', 8200.00),
('3000', 'Owner Equity', 'equity', 200000.00),
('4000', 'Room Revenue', 'revenue', 85000.00),
('4100', 'F&B Revenue', 'revenue', 22000.00),
('5000', 'Salaries Expense', 'expense', 35000.00),
('5100', 'Utilities Expense', 'expense', 6500.00),
('5200', 'Maintenance Expense', 'expense', 4200.00);

-- JOURNAL ENTRIES
INSERT INTO journal_entries (entry_date, reference, description, debit_account_id, credit_account_id, amount, created_by) VALUES
(CURDATE(), 'BK-2026-0001', 'Room booking payment', 1, 6, 360.00, 4),
(CURDATE(), 'BK-2026-0002', 'OTA booking deposit', 2, 6, 800.00, 4),
(DATE_SUB(CURDATE(), INTERVAL 1 DAY), 'PO-2026-0002', 'Linen purchase', 8, 4, 2400.00, 4),
(DATE_SUB(CURDATE(), INTERVAL 2 DAY), 'EXP-001', 'Electricity bill', 9, 1, 2200.00, 4),
(DATE_SUB(CURDATE(), INTERVAL 3 DAY), 'POS-2026-0001', 'F&B sale', 1, 7, 44.55, 4);

-- EXPENSES
INSERT INTO expenses (expense_date, category, description, amount, payment_method, vendor, created_by) VALUES
(CURDATE(), 'Utilities', 'Electricity bill — March', 2200.00, 'bank_transfer', 'City Power Co', 4),
(DATE_SUB(CURDATE(), INTERVAL 2 DAY), 'Maintenance', 'AC repair Room 401', 350.00, 'cash', 'Cool Air Services', 4),
(DATE_SUB(CURDATE(), INTERVAL 5 DAY), 'Marketing', 'Online ad campaign', 1500.00, 'card', 'Google Ads', 4),
(DATE_SUB(CURDATE(), INTERVAL 7 DAY), 'Office Supplies', 'Printer ink and paper', 180.00, 'cash', 'Office Equipment Inc', 4),
(DATE_SUB(CURDATE(), INTERVAL 10 DAY), 'Transport', 'Staff transport fuel', 220.00, 'cash', 'Shell Station', 4);

-- INVOICES
INSERT INTO invoices (invoice_number, guest_id, reservation_id, issue_date, due_date, subtotal, tax, total, paid, status) VALUES
('INV-2026-0001', 1, 1, CURDATE(), DATE_ADD(CURDATE(), INTERVAL 7 DAY), 360.00, 36.00, 396.00, 396.00, 'paid'),
('INV-2026-0002', 2, 2, CURDATE(), DATE_ADD(CURDATE(), INTERVAL 7 DAY), 1600.00, 160.00, 1760.00, 880.00, 'sent'),
('INV-2026-0003', 3, 3, DATE_ADD(CURDATE(), INTERVAL 2 DAY), DATE_ADD(CURDATE(), INTERVAL 9 DAY), 360.00, 36.00, 396.00, 0.00, 'draft'),
('INV-2026-0004', 4, 4, DATE_ADD(CURDATE(), INTERVAL 1 DAY), DATE_ADD(CURDATE(), INTERVAL 8 DAY), 1680.00, 168.00, 1848.00, 1848.00, 'paid'),
('INV-2026-0005', 5, 5, DATE_SUB(CURDATE(), INTERVAL 2 DAY), DATE_ADD(CURDATE(), INTERVAL 5 DAY), 360.00, 36.00, 396.00, 396.00, 'paid');

-- PAYMENTS
INSERT INTO payments (invoice_id, reservation_id, amount, method, gateway, transaction_id, paid_at) VALUES
(1, 1, 396.00, 'card', 'stripe', 'pi_3PQABCdef123', NOW()),
(2, 2, 880.00, 'card', 'stripe', 'pi_3PQXYZ456', DATE_SUB(NOW(), INTERVAL 1 DAY)),
(4, 4, 1848.00, 'mpesa', 'mpesa', 'MPS-XYZ-7782', DATE_SUB(NOW(), INTERVAL 2 DAY)),
(5, 5, 396.00, 'cash', NULL, NULL, DATE_SUB(NOW(), INTERVAL 5 DAY)),
(1, 1, 0.00, 'paypal', 'paypal', 'PYP-TEST-001', DATE_SUB(NOW(), INTERVAL 7 DAY));

-- BUDGETS
INSERT INTO budgets (category, period_start, period_end, budget_amount, actual_amount) VALUES
('Utilities', DATE_FORMAT(CURDATE(), '%Y-%m-01'), LAST_DAY(CURDATE()), 8000.00, 2200.00),
('Maintenance', DATE_FORMAT(CURDATE(), '%Y-%m-01'), LAST_DAY(CURDATE()), 5000.00, 350.00),
('Marketing', DATE_FORMAT(CURDATE(), '%Y-%m-01'), LAST_DAY(CURDATE()), 4000.00, 1500.00),
('Supplies', DATE_FORMAT(CURDATE(), '%Y-%m-01'), LAST_DAY(CURDATE()), 3000.00, 180.00),
('Transport', DATE_FORMAT(CURDATE(), '%Y-%m-01'), LAST_DAY(CURDATE()), 1500.00, 220.00);

-- DEPARTMENTS
INSERT INTO departments (name) VALUES
('Front Office'),
('Housekeeping'),
('F&B / Restaurant'),
('Accounting'),
('Human Resources'),
('Maintenance'),
('Sales & Marketing');

-- EMPLOYEES
INSERT INTO employees (employee_code, user_id, first_name, last_name, email, phone, department_id, position, hire_date, salary, status) VALUES
('EMP-001', 2, 'Sarah', 'Manager', 'manager@hotel.test', '+1234567891', 1, 'General Manager', '2022-01-15', 7500.00, 'active'),
('EMP-002', 3, 'John', 'Front Desk', 'reception@hotel.test', '+1234567892', 1, 'Front Desk Supervisor', '2023-03-10', 2800.00, 'active'),
('EMP-003', 4, 'Mary', 'Accountant', 'accounts@hotel.test', '+1234567893', 4, 'Senior Accountant', '2022-06-01', 3500.00, 'active'),
('EMP-004', 5, 'Peter', 'Housekeeper', 'housekeeping@hotel.test', '+1234567894', 2, 'Housekeeping Lead', '2023-08-20', 1800.00, 'active'),
('EMP-005', 6, 'Linda', 'HR', 'hr@hotel.test', '+1234567895', 5, 'HR Officer', '2022-11-05', 3200.00, 'active'),
('EMP-006', NULL, 'Chef', 'Pierre', 'chef.pierre@hotel.test', '+1234567896', 3, 'Executive Chef', '2021-05-12', 4500.00, 'active');

-- ATTENDANCE
INSERT INTO attendance (employee_id, date, clock_in, clock_out, hours_worked, status) VALUES
(1, CURDATE(), '08:00:00', '17:30:00', 9.5, 'present'),
(2, CURDATE(), '07:00:00', '15:30:00', 8.5, 'present'),
(3, CURDATE(), '09:15:00', '17:45:00', 8.5, 'late'),
(4, CURDATE(), '06:00:00', '14:30:00', 8.5, 'present'),
(5, CURDATE(), '08:30:00', NULL, NULL, 'present'),
(6, CURDATE(), NULL, NULL, NULL, 'leave');

-- LEAVES
INSERT INTO leaves (employee_id, leave_type, start_date, end_date, days, reason, status) VALUES
(6, 'sick', CURDATE(), CURDATE(), 1, 'Flu symptoms', 'approved'),
(2, 'annual', DATE_ADD(CURDATE(), INTERVAL 15 DAY), DATE_ADD(CURDATE(), INTERVAL 22 DAY), 7, 'Family vacation', 'pending'),
(4, 'annual', DATE_ADD(CURDATE(), INTERVAL 30 DAY), DATE_ADD(CURDATE(), INTERVAL 35 DAY), 5, 'Personal', 'approved'),
(3, 'maternity', DATE_ADD(CURDATE(), INTERVAL 60 DAY), DATE_ADD(CURDATE(), INTERVAL 150 DAY), 90, 'Maternity leave', 'approved'),
(5, 'sick', DATE_SUB(CURDATE(), INTERVAL 10 DAY), DATE_SUB(CURDATE(), INTERVAL 8 DAY), 2, 'Medical appointment', 'approved');

-- PAYROLL
INSERT INTO payroll (employee_id, period, basic, allowances, deductions, net_pay, status) VALUES
(1, DATE_FORMAT(CURDATE(), '%Y-%m'), 7500.00, 500.00, 1200.00, 6800.00, 'paid'),
(2, DATE_FORMAT(CURDATE(), '%Y-%m'), 2800.00, 200.00, 450.00, 2550.00, 'paid'),
(3, DATE_FORMAT(CURDATE(), '%Y-%m'), 3500.00, 300.00, 600.00, 3200.00, 'paid'),
(4, DATE_FORMAT(CURDATE(), '%Y-%m'), 1800.00, 100.00, 250.00, 1650.00, 'pending'),
(5, DATE_FORMAT(CURDATE(), '%Y-%m'), 3200.00, 250.00, 520.00, 2930.00, 'pending');

-- SHIFTS
INSERT INTO shifts (employee_id, shift_date, start_time, end_time, department_id) VALUES
(2, CURDATE(), '07:00:00', '15:00:00', 1),
(2, DATE_ADD(CURDATE(), INTERVAL 1 DAY), '15:00:00', '23:00:00', 1),
(4, CURDATE(), '06:00:00', '14:00:00', 2),
(4, DATE_ADD(CURDATE(), INTERVAL 1 DAY), '06:00:00', '14:00:00', 2),
(6, DATE_ADD(CURDATE(), INTERVAL 1 DAY), '10:00:00', '22:00:00', 3);

-- PERFORMANCE REVIEWS
INSERT INTO performance_reviews (employee_id, review_date, rating, strengths, improvements, reviewer_id) VALUES
(2, DATE_SUB(CURDATE(), INTERVAL 30 DAY), 4.5, 'Excellent guest service, quick to resolve issues', 'Could delegate more', 1),
(3, DATE_SUB(CURDATE(), INTERVAL 45 DAY), 4.8, 'Highly accurate, takes ownership of finances', 'Communication with other teams', 1),
(4, DATE_SUB(CURDATE(), INTERVAL 60 DAY), 4.2, 'Great team leader, very thorough', 'Time management on busy days', 1),
(5, DATE_SUB(CURDATE(), INTERVAL 90 DAY), 4.0, 'Strong policy knowledge, fair', 'More proactive recruitment', 1),
(6, DATE_SUB(CURDATE(), INTERVAL 120 DAY), 4.7, 'Outstanding culinary skills, creative menu', 'Kitchen budget control', 1);

-- LOYALTY TIERS
INSERT INTO loyalty_tiers (name, min_points, discount_percent, benefits) VALUES
('Bronze', 0, 0.00, 'Welcome drink on arrival'),
('Silver', 500, 5.00, 'Free WiFi, early check-in'),
('Gold', 1500, 10.00, 'Room upgrade subject to availability'),
('Platinum', 3000, 15.00, 'Lounge access, late checkout'),
('Diamond', 5000, 20.00, 'Suite upgrade, butler service, airport transfer');

-- CAMPAIGNS
INSERT INTO campaigns (name, channel, subject, body, target, status, send_at) VALUES
('Summer Beach Promo', 'email', 'Save 25% on beach villas this summer', 'Book now for amazing summer deals...', 'all_guests', 'scheduled', DATE_ADD(NOW(), INTERVAL 2 DAY)),
('Welcome Back Loyalty', 'email', 'We miss you! Here is 15% off', 'Exclusive offer for our loyalty members', 'loyalty_silver+', 'draft', NULL),
('Birthday Wishes', 'sms', NULL, 'Happy Birthday from Grand Royale! Enjoy 10% off your next stay.', 'birthday_today', 'sent', DATE_SUB(NOW(), INTERVAL 1 DAY)),
('Weekend Special', 'whatsapp', NULL, 'Weekend getaway? Book 2 nights, get 3rd free!', 'recent_guests', 'sent', DATE_SUB(NOW(), INTERVAL 7 DAY)),
('Holiday Newsletter', 'email', 'Holiday season at Grand Royale', 'Festive season packages and events...', 'all_guests', 'draft', NULL);

-- FEEDBACK
INSERT INTO feedback (guest_id, reservation_id, rating, comment, response) VALUES
(1, 1, 5, 'Excellent stay, very clean rooms and friendly staff!', 'Thank you Michael, we hope to see you again soon!'),
(2, 2, 4, 'Great hotel, breakfast could be improved.', 'Thanks Emma, we are revising our breakfast menu.'),
(3, 3, 5, 'Best service in the city, will return!', 'We appreciate your kind words Liam.'),
(4, 4, 5, 'The villa was perfect for our family', 'Thank you Aisha, glad you enjoyed Sunset Beach!'),
(5, 5, 3, 'Average — WiFi was slow', 'We have upgraded our WiFi infrastructure, please give us another try.');

-- COMPLAINTS
INSERT INTO complaints (guest_id, reservation_id, subject, description, severity, status, assigned_to) VALUES
(2, 2, 'Noisy neighbours', 'Loud guests in adjacent room until 2am', 'medium', 'resolved', 3),
(3, 3, 'AC not working', 'Room AC unit not cooling properly', 'high', 'in_progress', NULL),
(1, 1, 'Slow room service', 'Order took 90 minutes to arrive', 'low', 'resolved', 3),
(4, 4, 'Pool maintenance', 'Pool was closed during stay without notice', 'medium', 'closed', 3),
(6, NULL, 'Booking issue', 'Charged twice for same booking', 'high', 'open', 4);

-- LEADS
INSERT INTO leads (name, email, phone, source, interest, estimated_value, stage, assigned_to, notes) VALUES
('Robert Anderson', 'r.anderson@bigcorp.test', '+1-555-8001', 'website', 'Corporate event', 12000.00, 'qualified', 2, 'Annual conference for 80 attendees'),
('Linda Martinez', 'linda.m@email.test', '+1-555-8002', 'referral', 'Wedding venue', 25000.00, 'proposal', 2, 'June wedding, 150 guests'),
('Tom Wilson', 't.wilson@startup.test', '+1-555-8003', 'social', 'Team retreat', 8000.00, 'contacted', 2, 'Looking for 3-day retreat'),
('Priya Sharma', 'priya@email.test', '+1-555-8004', 'google', 'Honeymoon package', 5000.00, 'new', 2, 'Asked about Presidential Suite'),
('George Lee', 'george.l@email.test', '+1-555-8005', 'walk_in', 'Long stay', 15000.00, 'won', 2, '30-day corporate stay confirmed'),
('Maria Costa', 'maria.c@tour.test', '+1-555-8006', 'partner', 'Tour group', 18000.00, 'lost', 2, 'Went with competitor — price');

-- ASSETS
INSERT INTO assets (asset_code, name, category, location, purchase_date, purchase_cost, warranty_until, status) VALUES
('AST-001', 'AC Unit Carrier 5T', 'HVAC', 'Roof', '2022-05-10', 8500.00, '2027-05-10', 'active'),
('AST-002', 'Industrial Washing Machine', 'Laundry', 'Basement B1', '2021-08-15', 12000.00, '2024-08-15', 'active'),
('AST-003', 'Diesel Generator 100kVA', 'Power', 'Generator Room', '2020-03-20', 25000.00, '2025-03-20', 'active'),
('AST-004', 'Pool Pump System', 'Pool', 'Pool Equipment Room', '2023-01-15', 4500.00, '2026-01-15', 'active'),
('AST-005', 'Kitchen Hood Extractor', 'Kitchen', 'Main Kitchen', '2022-09-01', 6800.00, '2025-09-01', 'active');

-- MAINTENANCE REQUESTS
INSERT INTO maintenance_requests (asset_id, room_id, reported_by, subject, description, priority, status) VALUES
(1, 6, 5, 'AC not cooling Room 401', 'Guest complaint — AC blowing warm air', 'high', 'in_progress'),
(NULL, 3, 5, 'Leaky bathroom faucet', 'Water dripping from sink faucet', 'low', 'open'),
(NULL, 4, 3, 'Broken curtain rail', 'Curtain rail came loose from wall', 'medium', 'completed'),
(4, NULL, 2, 'Pool filter clogged', 'Pool water becoming cloudy', 'high', 'open'),
(2, NULL, 4, 'Washing machine vibration', 'Loud vibration during spin cycle', 'medium', 'in_progress');

-- PREVENTIVE SCHEDULES
INSERT INTO preventive_schedules (asset_id, title, frequency, next_due, notes) VALUES
(1, 'AC Filter Cleaning', 'monthly', DATE_ADD(CURDATE(), INTERVAL 10 DAY), 'Clean filters and check refrigerant'),
(2, 'Washing Machine Service', 'quarterly', DATE_ADD(CURDATE(), INTERVAL 25 DAY), 'Full service and belt check'),
(3, 'Generator Test Run', 'weekly', DATE_ADD(CURDATE(), INTERVAL 2 DAY), '30-minute test, check fuel'),
(4, 'Pool Pump Inspection', 'monthly', DATE_ADD(CURDATE(), INTERVAL 5 DAY), 'Check pressure, clean strainer'),
(5, 'Kitchen Hood Deep Clean', 'quarterly', DATE_ADD(CURDATE(), INTERVAL 40 DAY), 'Full deep clean and filter replacement');

-- HALLS
INSERT INTO halls (name, capacity, hourly_rate, daily_rate, description, amenities) VALUES
('Royale Grand Ballroom', 500, 800.00, 4500.00, 'Main grand ballroom for major events', 'Stage, AV, dance floor, AC'),
('Crystal Conference Room', 80, 200.00, 1200.00, 'Executive conference room', 'Projector, whiteboard, video conf'),
('Sapphire Meeting Room', 30, 100.00, 600.00, 'Small meeting room', 'TV screen, conference phone'),
('Garden Terrace', 150, 300.00, 2000.00, 'Outdoor garden venue', 'Bar setup, fairy lights, gazebo'),
('Pool Deck Lounge', 100, 250.00, 1500.00, 'Poolside event space', 'Pool access, lounge furniture');

-- EVENT BOOKINGS
INSERT INTO event_bookings (hall_id, event_type, title, client_name, client_phone, start_at, end_at, attendees, catering_included, package, total, paid, status) VALUES
(1, 'wedding', 'Smith-Johnson Wedding', 'Sarah Johnson', '+1-555-9100', DATE_ADD(NOW(), INTERVAL 14 DAY), DATE_ADD(NOW(), INTERVAL 14 DAY) + INTERVAL 8 HOUR, 250, 1, 'Premium Wedding', 18000.00, 9000.00, 'confirmed'),
(2, 'conference', 'Tech Summit Day 1', 'Maria Rodriguez', '+1-555-9101', DATE_ADD(NOW(), INTERVAL 30 DAY), DATE_ADD(NOW(), INTERVAL 30 DAY) + INTERVAL 9 HOUR, 60, 1, 'Conference Standard', 4500.00, 4500.00, 'confirmed'),
(3, 'meeting', 'Acme Board Meeting', 'David Chen', '+1-555-9102', DATE_ADD(NOW(), INTERVAL 2 DAY), DATE_ADD(NOW(), INTERVAL 2 DAY) + INTERVAL 4 HOUR, 15, 1, 'Half-day Meeting', 800.00, 800.00, 'confirmed'),
(4, 'reception', 'Garden Cocktail Party', 'Linda Wang', '+1-555-9103', DATE_ADD(NOW(), INTERVAL 7 DAY), DATE_ADD(NOW(), INTERVAL 7 DAY) + INTERVAL 4 HOUR, 80, 1, 'Cocktail Reception', 3200.00, 1600.00, 'confirmed'),
(5, 'birthday', '50th Birthday Bash', 'James Mwangi', '+254-700-9100', DATE_ADD(NOW(), INTERVAL 21 DAY), DATE_ADD(NOW(), INTERVAL 21 DAY) + INTERVAL 6 HOUR, 75, 1, 'Birthday Package', 2800.00, 1000.00, 'pending');

-- CATERING MENUS
INSERT INTO catering_menus (name, type, price_per_person, description) VALUES
('Continental Breakfast', 'breakfast', 18.00, 'Pastries, fruits, juices, coffee/tea'),
('Business Lunch Buffet', 'lunch', 35.00, 'Soup, salads, mains, desserts, drinks'),
('Cocktail Reception', 'reception', 28.00, 'Canapes, finger foods, welcome drinks'),
('Wedding Dinner Package', 'dinner', 75.00, '4-course plated dinner with wine pairing'),
('Conference Tea Break', 'snack', 12.00, 'Pastries, fruits, hot/cold beverages');

-- INTEGRATIONS
INSERT INTO integrations (provider, type, config, enabled) VALUES
('stripe', 'payment', '{"public_key":"pk_test_xxx","secret_key":"sk_test_xxx"}', 0),
('paypal', 'payment', '{"client_id":"","client_secret":""}', 0),
('mpesa', 'payment', '{"consumer_key":"","consumer_secret":"","shortcode":""}', 0),
('pesapal', 'payment', '{"consumer_key":"","consumer_secret":""}', 0),
('booking_com', 'channel', '{"api_key":"","property_id":""}', 0),
('whatsapp', 'messaging', '{"api_key":"","phone_id":""}', 0),
('twilio_sms', 'messaging', '{"sid":"","token":"","from":""}', 0),
('quickbooks', 'accounting', '{"client_id":"","client_secret":""}', 0),
('xero', 'accounting', '{"client_id":"","client_secret":""}', 0);

-- SETTINGS
INSERT INTO settings (`key`, `value`, `group`) VALUES
('hotel_name', 'Grand Royale Hotel', 'general'),
('hotel_address', '123 Ocean Drive, Miami, USA', 'general'),
('default_currency', 'USD', 'general'),
('currency_symbol', '$', 'general'),
('tax_rate', '10', 'general'),
('checkin_time', '14:00', 'general'),
('checkout_time', '11:00', 'general'),
('ai_enabled', '1', 'features'),
('two_factor', '0', 'features'),
('smart_pricing', '1', 'features'),
('ai_chatbot', '1', 'features'),
('auto_upsell', '1', 'features');

-- AUDIT LOGS
INSERT INTO audit_logs (user_id, action, entity, entity_id, description, ip_address) VALUES
(1, 'login', 'users', 1, 'Admin logged in', '192.168.1.10'),
(3, 'create', 'reservations', 1, 'Reservation BK-2026-0001 created', '192.168.1.20'),
(3, 'update', 'rooms', 2, 'Room 102 status changed to occupied', '192.168.1.20'),
(4, 'create', 'expenses', 1, 'Expense recorded: Electricity bill', '192.168.1.30'),
(5, 'update', 'housekeeping_tasks', 1, 'Task started for Room 201', '192.168.1.40');