-- RUHONDO BEACH RESORT HMS + POS System
-- Initial Seed Data
-- Developer: JOACHIM'S INNOTECH
-- Version: 1.0

USE ruhondo_hms;

-- =====================================================
-- INSERT DEFAULT ROLES
-- =====================================================
INSERT INTO roles (role_name, role_description, is_system) VALUES
('Super Admin', 'Full system access with all permissions', TRUE),
('Admin', 'Administrative access with most permissions', TRUE),
('Front Desk Manager', 'Manages front desk operations and reservations', TRUE),
('Front Desk Agent', 'Handles check-in, check-out, and reservations', TRUE),
('POS Manager', 'Manages POS operations and outlets', TRUE),
('Cashier', 'Handles POS sales and transactions', TRUE),
('Housekeeping Manager', 'Manages housekeeping team and tasks', TRUE),
('Housekeeper', 'Performs cleaning tasks', TRUE),
('Maintenance Staff', 'Handles maintenance requests', TRUE),
('Inventory Manager', 'Manages inventory and suppliers', TRUE),
('Accountant', 'Handles billing, invoices, and payments', TRUE),
('Guest Relations', 'Manages guest information and relations', TRUE),
('Restricted User', 'View-only access to basic information', TRUE);

-- =====================================================
-- INSERT DEFAULT PERMISSIONS
-- =====================================================
INSERT INTO permissions (permission_name, permission_key, module, description) VALUES
-- User Management
('View Users', 'users.view', 'admin', 'Can view user list and details'),
('Create Users', 'users.create', 'admin', 'Can create new users'),
('Edit Users', 'users.edit', 'admin', 'Can edit existing users'),
('Delete Users', 'users.delete', 'admin', 'Can delete users'),
('Manage Roles', 'users.roles', 'admin', 'Can manage roles and permissions'),
('View Audit Logs', 'users.audit', 'admin', 'Can view audit logs'),

-- Settings
('View Settings', 'settings.view', 'admin', 'Can view system settings'),
('Edit Settings', 'settings.edit', 'admin', 'Can edit system settings'),
('Manage Backup', 'settings.backup', 'admin', 'Can perform backup and restore'),

-- Rooms Management
('View Room Types', 'roomtypes.view', 'rooms', 'Can view room types'),
('Create Room Types', 'roomtypes.create', 'rooms', 'Can create room types'),
('Edit Room Types', 'roomtypes.edit', 'rooms', 'Can edit room types'),
('Delete Room Types', 'roomtypes.delete', 'rooms', 'Can delete room types'),
('View Rooms', 'rooms.view', 'rooms', 'Can view rooms'),
('Create Rooms', 'rooms.create', 'rooms', 'Can create rooms'),
('Edit Rooms', 'rooms.edit', 'rooms', 'Can edit rooms'),
('Delete Rooms', 'rooms.delete', 'rooms', 'Can delete rooms'),
('Manage Room Images', 'rooms.images', 'rooms', 'Can manage room images'),
('Change Room Status', 'rooms.status', 'rooms', 'Can change room status'),
('View Rates', 'rates.view', 'rooms', 'Can view rates'),
('Manage Rates', 'rates.manage', 'rooms', 'Can manage rates and seasons'),

-- Reservations
('View Reservations', 'reservations.view', 'reservations', 'Can view reservations'),
('Create Reservations', 'reservations.create', 'reservations', 'Can create reservations'),
('Edit Reservations', 'reservations.edit', 'reservations', 'Can edit reservations'),
('Cancel Reservations', 'reservations.cancel', 'reservations', 'Can cancel reservations'),
('Check-in Guest', 'reservations.checkin', 'reservations', 'Can perform check-in'),
('Check-out Guest', 'reservations.checkout', 'reservations', 'Can perform check-out'),
('Manage Folio', 'reservations.folio', 'reservations', 'Can manage guest folio'),

-- Guests
('View Guests', 'guests.view', 'guests', 'Can view guest information'),
('Create Guests', 'guests.create', 'guests', 'Can create new guests'),
('Edit Guests', 'guests.edit', 'guests', 'Can edit guest information'),
('Blacklist Guests', 'guests.blacklist', 'guests', 'Can blacklist guests'),
('View Guest Documents', 'guests.documents', 'guests', 'Can view guest documents'),

-- POS
('View Outlets', 'pos.outlets.view', 'pos', 'Can view POS outlets'),
('Manage Outlets', 'pos.outlets.manage', 'pos', 'Can manage POS outlets'),
('View Categories', 'pos.categories.view', 'pos', 'Can view product categories'),
('Manage Categories', 'pos.categories.manage', 'pos', 'Can manage product categories'),
('View Products', 'pos.products.view', 'pos', 'Can view products'),
('Manage Products', 'pos.products.manage', 'pos', 'Can manage products'),
('Process Sales', 'pos.sales.process', 'pos', 'Can process POS sales'),
('View Sales', 'pos.sales.view', 'pos', 'Can view sales transactions'),
('Void Refund', 'pos.sales.void', 'pos', 'Can void transactions and process refunds'),
('Manage Shifts', 'pos.shifts.manage', 'pos', 'Can open/close shifts'),
('View POS Reports', 'pos.reports.view', 'pos', 'Can view POS reports'),

-- Inventory
('View Suppliers', 'inventory.suppliers.view', 'inventory', 'Can view suppliers'),
('Manage Suppliers', 'inventory.suppliers.manage', 'inventory', 'Can manage suppliers'),
('View Purchase Orders', 'inventory.purchases.view', 'inventory', 'Can view purchase orders'),
('Manage Purchase Orders', 'inventory.purchases.manage', 'inventory', 'Can manage purchase orders'),
('Receive Stock', 'inventory.receive', 'inventory', 'Can receive stock'),
('Manage Stock', 'inventory.stock.manage', 'inventory', 'Can manage stock movements'),

-- Housekeeping
('View Tasks', 'hk.tasks.view', 'housekeeping', 'Can view housekeeping tasks'),
('Manage Tasks', 'hk.tasks.manage', 'housekeeping', 'Can manage housekeeping tasks'),
('View Maintenance', 'hk.maintenance.view', 'housekeeping', 'Can view maintenance tickets'),
('Manage Maintenance', 'hk.maintenance.manage', 'housekeeping', 'Can manage maintenance tickets'),

-- Billing
('View Invoices', 'billing.invoices.view', 'billing', 'Can view invoices'),
('Create Invoices', 'billing.invoices.create', 'billing', 'Can create invoices'),
('Manage Payments', 'billing.payments.manage', 'billing', 'Can manage payments'),
('Process Refunds', 'billing.refunds', 'billing', 'Can process refunds'),
('View Debtors', 'billing.debtors.view', 'billing', 'Can view debtor information'),

-- Reports
('View Reports', 'reports.view', 'reports', 'Can view reports'),
('Export Reports', 'reports.export', 'reports', 'Can export reports'),

-- Dashboard Access
('View Executive Dashboard', 'dashboard.executive', 'dashboards', 'Can view executive dashboard'),
('View Front Desk Dashboard', 'dashboard.frontdesk', 'dashboards', 'Can view front desk dashboard'),
('View Rooms Dashboard', 'dashboard.rooms', 'dashboards', 'Can view rooms dashboard'),
('View POS Dashboard', 'dashboard.pos', 'dashboards', 'Can view POS dashboard'),
('View Inventory Dashboard', 'dashboard.inventory', 'dashboards', 'Can view inventory dashboard'),
('View Housekeeping Dashboard', 'dashboard.housekeeping', 'dashboards', 'Can view housekeeping dashboard'),
('View Billing Dashboard', 'dashboard.billing', 'dashboards', 'Can view billing dashboard');

-- =====================================================
-- ASSIGN PERMISSIONS TO ROLES (Super Admin gets all)
-- =====================================================
-- Super Admin role_id = 1
INSERT INTO role_permissions (role_id, permission_id)
SELECT 1, permission_id FROM permissions;

-- =====================================================
-- INSERT DEFAULT USERS (passwords: 'Admin@123' for all)
-- =====================================================
INSERT INTO users (username, email, password_hash, first_name, last_name, phone, role_id, department, is_active) VALUES
-- Super Admin
('superadmin', 'superadmin@ruhondo.com', '$2y$10$YourHashHere92K5qX8Zq5qX8Zq5qX8Zq5qX8Zq5qX8Zq5qX8Z', 'Super', 'Admin', '+250788111111', 1, 'IT', TRUE),

-- Admin
('admin', 'admin@ruhondo.com', '$2y$10$YourHashHere92K5qX8Zq5qX8Zq5qX8Zq5qX8Zq5qX8Zq5qX8Z', 'System', 'Administrator', '+250788111112', 2, 'Administration', TRUE),

-- Front Desk Staff
('fdmanager', 'fdmanager@ruhondo.com', '$2y$10$YourHashHere92K5qX8Zq5qX8Zq5qX8Zq5qX8Zq5qX8Zq5qX8Z', 'Front', 'Desk Manager', '+250788111113', 3, 'Front Office', TRUE),
('fdagent1', 'fdagent1@ruhondo.com', '$2y$10$YourHashHere92K5qX8Zq5qX8Zq5qX8Zq5qX8Zq5qX8Zq5qX8Z', 'John', 'Doe', '+250788111114', 4, 'Front Office', TRUE),
('fdagent2', 'fdagent2@ruhondo.com', '$2y$10$YourHashHere92K5qX8Zq5qX8Zq5qX8Zq5qX8Zq5qX8Zq5qX8Z', 'Jane', 'Smith', '+250788111115', 4, 'Front Office', TRUE),

-- POS Staff
('posmanager', 'posmanager@ruhondo.com', '$2y$10$YourHashHere92K5qX8Zq5qX8Zq5qX8Zq5qX8Zq5qX8Zq5qX8Z', 'POS', 'Manager', '+250788111116', 5, 'F&B', TRUE),
('cashier1', 'cashier1@ruhondo.com', '$2y$10$YourHashHere92K5qX8Zq5qX8Zq5qX8Zq5qX8Zq5qX8Zq5qX8Z', 'Peter', 'Cashier', '+250788111117', 6, 'F&B', TRUE),
('cashier2', 'cashier2@ruhondo.com', '$2y$10$YourHashHere92K5qX8Zq5qX8Zq5qX8Zq5qX8Zq5qX8Zq5qX8Z', 'Mary', 'Johnson', '+250788111118', 6, 'F&B', TRUE),

-- Housekeeping
('hkmanager', 'hkmanager@ruhondo.com', '$2y$10$YourHashHere92K5qX8Zq5qX8Zq5qX8Zq5qX8Zq5qX8Zq5qX8Z', 'Housekeeping', 'Manager', '+250788111119', 7, 'Housekeeping', TRUE),
('housekeeper1', 'housekeeper1@ruhondo.com', '$2y$10$YourHashHere92K5qX8Zq5qX8Zq5qX8Zq5qX8Zq5qX8Zq5qX8Z', 'Alice', 'Cleaner', '+250788111120', 8, 'Housekeeping', TRUE),
('maintenance1', 'maintenance1@ruhondo.com', '$2y$10$YourHashHere92K5qX8Zq5qX8Zq5qX8Zq5qX8Zq5qX8Zq5qX8Z', 'Bob', 'Repair', '+250788111121', 9, 'Maintenance', TRUE),

-- Inventory
('invmanager', 'invmanager@ruhondo.com', '$2y$10$YourHashHere92K5qX8Zq5qX8Zq5qX8Zq5qX8Zq5qX8Zq5qX8Z', 'Inventory', 'Manager', '+250788111122', 10, 'Inventory', TRUE),

-- Accounting
('accountant', 'accountant@ruhondo.com', '$2y$10$YourHashHere92K5qX8Zq5qX8Zq5qX8Zq5qX8Zq5qX8Zq5qX8Z', 'Chief', 'Accountant', '+250788111123', 11, 'Finance', TRUE),

-- Guest Relations
('guestrelations', 'guestrelations@ruhondo.com', '$2y$10$YourHashHere92K5qX8Zq5qX8Zq5qX8Zq5qX8Zq5qX8Zq5qX8Z', 'Guest', 'Relations', '+250788111124', 12, 'Guest Services', TRUE),

-- Restricted User
('guestview', 'guestview@ruhondo.com', '$2y$10$YourHashHere92K5qX8Zq5qX8Zq5qX8Zq5qX8Zq5qX8Zq5qX8Z', 'View', 'Only', '+250788111125', 13, 'External', TRUE);

-- =====================================================
-- INSERT RESORT SETTINGS
-- =====================================================
INSERT INTO resort_settings (resort_name, resort_address, resort_city, resort_state, resort_country, resort_phone, resort_email, resort_website, checkin_time, checkout_time, default_currency, timezone) VALUES
('Ruhondo Beach Resort', 'Lake Ruhondo', 'Musanze', 'Northern Province', 'Rwanda', '+250 788 888 888', 'info@ruhondo.com', 'https://www.ruhondo.com', '14:00:00', '11:00:00', 'RWF', 'Africa/Kigali');

-- =====================================================
-- INSERT TAX RATES
-- =====================================================
INSERT INTO tax_rates (tax_name, tax_rate, tax_type, applies_to_rooms, applies_to_fnb, applies_to_services) VALUES
('VAT 18%', 18.00, 'exclusive', TRUE, TRUE, TRUE),
('Service Charge 10%', 10.00, 'exclusive', TRUE, TRUE, TRUE),
('Tourism Levy 2%', 2.00, 'exclusive', TRUE, FALSE, TRUE),
('Zero Rated', 0.00, 'inclusive', FALSE, FALSE, FALSE);

-- =====================================================
-- INSERT CURRENCY RATES
-- =====================================================
INSERT INTO currency_rates (currency_code, currency_name, currency_symbol, exchange_rate, is_base_currency) VALUES
('RWF', 'Rwandan Franc', 'FRw', 1.0000, TRUE),
('USD', 'US Dollar', '$', 1150.0000, FALSE),
('EUR', 'Euro', '€', 1250.0000, FALSE),
('GBP', 'British Pound', '£', 1450.0000, FALSE),
('KES', 'Kenyan Shilling', 'KSh', 8.5000, FALSE),
('UGX', 'Ugandan Shilling', 'USh', 0.3100, FALSE),
('TZS', 'Tanzanian Shilling', 'TSh', 0.4500, FALSE);

-- =====================================================
-- INSERT PAYMENT METHODS
-- =====================================================
INSERT INTO payment_methods (method_code, method_name, method_type, requires_approval, sort_order) VALUES
('CASH', 'Cash', 'cash', FALSE, 1),
('VISA', 'Visa Card', 'card', FALSE, 2),
('MC', 'Mastercard', 'card', FALSE, 3),
('AMEX', 'American Express', 'card', TRUE, 4),
('MOMO', 'Mobile Money', 'mobile_money', FALSE, 5),
('BK', 'Bank Transfer', 'bank_transfer', TRUE, 6),
('INV', 'Invoice', 'invoice', TRUE, 7),
('ROOM', 'Room Charge', 'room_charge', FALSE, 8),
('VOUCHER', 'Voucher', 'voucher', TRUE, 9);

-- =====================================================
-- INSERT POS OUTLETS
-- =====================================================
INSERT INTO pos_outlets (outlet_code, outlet_name, outlet_type, location, tax_id) VALUES
('MAIN-REST', 'Main Restaurant', 'restaurant', 'Ground Floor', 1),
('POOL-BAR', 'Pool Bar', 'bar', 'Pool Area', 1),
('BEACH-GRL', 'Beach Grill', 'restaurant', 'Beach Front', 1),
('SPA-WELL', 'Spa & Wellness', 'spa', 'East Wing', 1),
('GIFT-SHP', 'Gift Shop', 'gift_shop', 'Lobby', 1),
('LOUNGE', 'Executive Lounge', 'bar', '5th Floor', 1);

-- =====================================================
-- INSERT POS CATEGORIES
-- =====================================================
INSERT INTO pos_categories (category_code, category_name, description, tax_id, sort_order) VALUES
('BEV-HOT', 'Hot Beverages', 'Coffee, Tea, Hot Chocolate', 1, 1),
('BEV-COLD', 'Cold Beverages', 'Soft Drinks, Juices, Water', 1, 2),
('ALCOHOL', 'Alcoholic Drinks', 'Beer, Wine, Spirits', 1, 3),
('APP', 'Appetizers', 'Starters and Small Plates', 1, 4),
('MAIN', 'Main Courses', 'Breakfast, Lunch, Dinner', 1, 5),
('DESSERT', 'Desserts', 'Cakes, Ice Cream, Pastries', 1, 6),
('SERVICE', 'Services', 'Spa, Activities, Tours', 3, 7),
('MERCH', 'Merchandise', 'Souvenirs, Gifts', 1, 8);

-- =====================================================
-- INSERT ROOM TYPES
-- =====================================================
INSERT INTO room_types (type_code, type_name, description, max_adults, max_children, max_occupancy, size_sqft, bed_type, base_price, amenities) VALUES
('STD', 'Standard Room', 'Comfortable room with garden view', 2, 1, 3, 300, 'Queen Bed', 150.00, 'TV, WiFi, AC, Mini Fridge'),
('DLX', 'Deluxe Room', 'Spacious room with lake view', 2, 2, 4, 400, 'King Bed', 200.00, 'TV, WiFi, AC, Mini Bar, Safe'),
('EXE', 'Executive Suite', 'Luxury suite with separate living area', 2, 2, 4, 600, 'King Bed', 300.00, 'TV, WiFi, AC, Mini Bar, Safe, Jacuzzi'),
('FAM', 'Family Room', 'Two connecting rooms for families', 4, 2, 6, 800, '2 Queen Beds', 350.00, 'TV, WiFi, AC, Mini Fridge, Kitchenette'),
('PRES', 'Presidential Suite', 'Ultimate luxury with panoramic views', 2, 0, 2, 1200, 'King Bed', 500.00, 'TV, WiFi, AC, Full Kitchen, Jacuzzi, Butler Service');

-- =====================================================
-- INSERT ROOMS
-- =====================================================
-- Standard Rooms (101-120)
INSERT INTO rooms (room_number, room_type_id, floor, status, housekeeping_status) VALUES
('101', 1, '1', 'available', 'clean'),
('102', 1, '1', 'available', 'clean'),
('103', 1, '1', 'available', 'clean'),
('104', 1, '1', 'available', 'dirty'),
('105', 1, '1', 'occupied', 'dirty'),
('106', 1, '1', 'occupied', 'dirty'),
('107', 1, '1', 'maintenance', 'out_of_service'),
('108', 1, '1', 'available', 'clean'),
('109', 1, '1', 'available', 'clean'),
('110', 1, '1', 'housekeeping', 'dirty'),
('111', 1, '1', 'available', 'clean'),
('112', 1, '1', 'available', 'clean'),
('113', 1, '1', 'available', 'clean'),
('114', 1, '1', 'occupied', 'dirty'),
('115', 1, '1', 'occupied', 'dirty'),
('116', 1, '1', 'available', 'clean'),
('117', 1, '1', 'available', 'clean'),
('118', 1, '1', 'available', 'clean'),
('119', 1, '1', 'blocked', 'clean'),
('120', 1, '1', 'available', 'clean'),

-- Deluxe Rooms (201-215)
('201', 2, '2', 'available', 'clean'),
('202', 2, '2', 'available', 'clean'),
('203', 2, '2', 'occupied', 'dirty'),
('204', 2, '2', 'occupied', 'dirty'),
('205', 2, '2', 'available', 'clean'),
('206', 2, '2', 'available', 'clean'),
('207', 2, '2', 'available', 'clean'),
('208', 2, '2', 'maintenance', 'out_of_service'),
('209', 2, '2', 'available', 'clean'),
('210', 2, '2', 'housekeeping', 'dirty'),
('211', 2, '2', 'available', 'clean'),
('212', 2, '2', 'available', 'clean'),
('213', 2, '2', 'occupied', 'dirty'),
('214', 2, '2', 'occupied', 'dirty'),
('215', 2, '2', 'available', 'clean'),

-- Executive Suites (301-310)
('301', 3, '3', 'available', 'clean'),
('302', 3, '3', 'occupied', 'dirty'),
('303', 3, '3', 'occupied', 'dirty'),
('304', 3, '3', 'available', 'clean'),
('305', 3, '3', 'available', 'clean'),
('306', 3, '3', 'available', 'clean'),
('307', 3, '3', 'occupied', 'dirty'),
('308', 3, '3', 'occupied', 'dirty'),
('309', 3, '3', 'available', 'clean'),
('310', 3, '3', 'available', 'clean'),

-- Family Rooms (401-405)
('401', 4, '4', 'occupied', 'dirty'),
('402', 4, '4', 'occupied', 'dirty'),
('403', 4, '4', 'available', 'clean'),
('404', 4, '4', 'available', 'clean'),
('405', 4, '4', 'available', 'clean'),

-- Presidential Suite (501)
('501', 5, '5', 'available', 'clean');

-- =====================================================
-- INSERT SEASONS
-- =====================================================
INSERT INTO seasons (season_name, start_date, end_date, multiplier, is_active) VALUES
('Low Season', '2024-03-01', '2024-05-31', 0.80, TRUE),
('Shoulder Season', '2024-06-01', '2024-06-30', 1.00, TRUE),
('High Season', '2024-07-01', '2024-08-31', 1.30, TRUE),
('Peak Season', '2024-12-15', '2025-01-15', 1.50, TRUE),
('Conference Season', '2024-09-01', '2024-11-30', 1.20, TRUE);

-- =====================================================
-- INSERT RATE PLANS
-- =====================================================
INSERT INTO rate_plans (plan_code, plan_name, description, room_type_id, season_id, base_rate, adjustment_type, adjustment_value, min_nights, is_refundable) VALUES
('STD-BB', 'Standard Bed & Breakfast', 'Room with breakfast included', 1, NULL, 150.00, 'fixed', 0.00, 1, TRUE),
('STD-HB', 'Standard Half Board', 'Room with breakfast and dinner', 1, NULL, 180.00, 'fixed', 0.00, 2, TRUE),
('STD-FB', 'Standard Full Board', 'Room with all meals', 1, NULL, 210.00, 'fixed', 0.00, 3, TRUE),
('DLX-BB', 'Deluxe Bed & Breakfast', 'Deluxe room with breakfast', 2, NULL, 200.00, 'fixed', 0.00, 1, TRUE),
('DLX-HB', 'Deluxe Half Board', 'Deluxe room with breakfast and dinner', 2, NULL, 230.00, 'fixed', 0.00, 2, TRUE),
('EXE-ALL', 'Executive All Inclusive', 'Executive suite with all meals and drinks', 3, NULL, 350.00, 'fixed', 0.00, 2, FALSE),
('FAM-PKG', 'Family Package', 'Family room with meals for family of 4', 4, NULL, 400.00, 'fixed', 0.00, 2, TRUE),
('ROMANCE', 'Romance Package', 'Deluxe room with candlelight dinner', 2, NULL, 280.00, 'fixed', 0.00, 2, FALSE);

-- =====================================================
-- INSERT SAMPLE GUESTS
-- =====================================================
INSERT INTO guests (guest_code, first_name, last_name, email, phone, address, city, country, id_type, id_number, nationality, date_of_birth) VALUES
('G-20240001', 'Jean', 'Ntakirutimana', 'jean.n@email.com', '+250788123456', 'KG 123 St', 'Kigali', 'Rwanda', 'national_id', '1199880012345678', 'Rwandan', '1985-06-15'),
('G-20240002', 'Marie', 'Uwase', 'marie.u@email.com', '+250788234567', 'KN 456 St', 'Kigali', 'Rwanda', 'passport', 'RC123456', 'Rwandan', '1990-03-22'),
('G-20240003', 'John', 'Smith', 'john.smith@email.com', '+254722123456', '123 Nairobi St', 'Nairobi', 'Kenya', 'passport', 'AB123456', 'Kenyan', '1978-11-08'),
('G-20240004', 'Sarah', 'Johnson', 'sarah.j@email.com', '+25677123456', '45 Kampala Rd', 'Kampala', 'Uganda', 'passport', 'UG987654', 'Ugandan', '1982-09-30'),
('G-20240005', 'Peter', 'Mukasa', 'peter.m@email.com', '+255755123456', '78 Dar es Salaam', 'Dar es Salaam', 'Tanzania', 'passport', 'TZ456789', 'Tanzanian', '1975-12-10'),
('G-20240006', 'Emma', 'Watson', 'emma.w@email.com', '+447911123456', '45 London St', 'London', 'UK', 'passport', 'UK123456789', 'British', '1988-04-25'),
('G-20240007', 'Michael', 'Brown', 'michael.b@email.com', '+12025551234', '789 New York Ave', 'New York', 'USA', 'passport', 'US987654321', 'American', '1972-07-18');

-- =====================================================
-- INSERT SAMPLE RESERVATIONS
-- =====================================================
INSERT INTO reservations (booking_code, guest_id, room_type_id, room_id, adults, children, check_in_date, check_out_date, booking_source, booking_status, payment_status, total_amount, paid_amount, tax_amount, special_requests) VALUES
('BK-2024-0001', 1, 2, 203, 2, 0, CURDATE(), DATE_ADD(CURDATE(), INTERVAL 3 DAY), 'direct', 'checked_in', 'paid', 600.00, 600.00, 108.00, 'Late check-in requested'),
('BK-2024-0002', 2, 3, 302, 2, 1, CURDATE(), DATE_ADD(CURDATE(), INTERVAL 2 DAY), 'website', 'checked_in', 'paid', 600.00, 300.00, 108.00, 'Extra towels please'),
('BK-2024-0003', 3, 2, 213, 1, 0, CURDATE(), DATE_ADD(CURDATE(), INTERVAL 1 DAY), 'walk_in', 'checked_in', 'paid', 200.00, 200.00, 36.00, NULL),
('BK-2024-0004', 4, 4, 401, 2, 2, CURDATE(), DATE_ADD(CURDATE(), INTERVAL 4 DAY), 'agent', 'confirmed', 'unpaid', 1400.00, 0.00, 252.00, 'Need baby cot'),
('BK-2024-0005', 5, 1, 106, 2, 0, DATE_ADD(CURDATE(), INTERVAL 2 DAY), DATE_ADD(CURDATE(), INTERVAL 5 DAY), 'phone', 'confirmed', 'partial', 450.00, 200.00, 81.00, NULL),
('BK-2024-0006', 6, 5, 501, 2, 0, DATE_ADD(CURDATE(), INTERVAL 3 DAY), DATE_ADD(CURDATE(), INTERVAL 5 DAY), 'ota', 'confirmed', 'unpaid', 1000.00, 0.00, 180.00, 'Airport pickup needed'),
('BK-2024-0007', 7, 2, 214, 2, 0, DATE_ADD(CURDATE(), INTERVAL -1 DAY), DATE_ADD(CURDATE(), INTERVAL 2 DAY), 'direct', 'checked_in', 'paid', 600.00, 600.00, 108.00, NULL),
('BK-2024-0008', 1, 1, 115, 1, 0, DATE_ADD(CURDATE(), INTERVAL -2 DAY), CURDATE(), 'walk_in', 'checked_out', 'paid', 300.00, 300.00, 54.00, NULL);

-- =====================================================
-- INSERT SAMPLE FOLIO CHARGES
-- =====================================================
INSERT INTO folio_charges (reservation_id, transaction_code, charge_type, description, quantity, unit_price, amount, tax_amount, posted_by) VALUES
(1, 'CHG-2024-0001', 'room', 'Room charge - Deluxe Room x 3 nights', 3, 200.00, 600.00, 108.00, 4),
(1, 'CHG-2024-0002', 'fnb', 'Restaurant - Dinner', 1, 45.00, 45.00, 8.10, 6),
(1, 'CHG-2024-0003', 'fnb', 'Pool Bar - Drinks', 1, 25.00, 25.00, 4.50, 6),
(2, 'CHG-2024-0004', 'room', 'Room charge - Executive Suite x 2 nights', 2, 300.00, 600.00, 108.00, 4),
(2, 'CHG-2024-0005', 'fnb', 'Breakroom - Breakfast x 2 days', 2, 15.00, 30.00, 5.40, 7),
(3, 'CHG-2024-0006', 'room', 'Room charge - Deluxe Room x 1 night', 1, 200.00, 200.00, 36.00, 5),
(4, 'CHG-2024-0007', 'room', 'Room charge - Family Room x 4 nights', 4, 350.00, 1400.00, 252.00, 4),
(7, 'CHG-2024-0008', 'room', 'Room charge - Deluxe Room x 3 nights', 3, 200.00, 600.00, 108.00, 5),
(7, 'CHG-2024-0009', 'fnb', 'Spa - Massage', 2, 50.00, 100.00, 10.00, 7),
(8, 'CHG-2024-0010', 'room', 'Room charge - Standard Room x 2 nights', 2, 150.00, 300.00, 54.00, 4);

-- =====================================================
-- INSERT SAMPLE POS PRODUCTS
-- =====================================================
INSERT INTO pos_products (product_code, barcode, product_name, description, category_id, unit_price, cost_price, tax_id, stock_item, current_stock, min_stock, unit_of_measure) VALUES
('P-0001', '8901234567890', 'Espresso', 'Single shot espresso', 1, 3.50, 0.50, 1, TRUE, 500, 100, 'cup'),
('P-0002', '8901234567891', 'Cappuccino', 'Italian coffee with milk foam', 1, 4.50, 0.70, 1, TRUE, 400, 100, 'cup'),
('P-0003', '8901234567892', 'Latte', 'Coffee with steamed milk', 1, 4.50, 0.70, 1, TRUE, 400, 100, 'cup'),
('P-0004', '8901234567893', 'Tea', 'Assorted tea selection', 1, 3.00, 0.20, 1, TRUE, 600, 200, 'cup'),
('P-0005', '8901234567894', 'Hot Chocolate', 'Rich hot chocolate with cream', 1, 4.00, 0.60, 1, TRUE, 300, 50, 'cup'),
('P-0006', '8901234567895', 'Mineral Water', '500ml bottled water', 2, 1.50, 0.30, 1, TRUE, 1000, 200, 'bottle'),
('P-0007', '8901234567896', 'Coca Cola', '330ml can', 2, 2.00, 0.50, 1, TRUE, 800, 150, 'can'),
('P-0008', '8901234567897', 'Fresh Orange Juice', 'Freshly squeezed', 2, 4.00, 1.00, 1, TRUE, 200, 50, 'glass'),
('P-0009', '8901234567898', 'Mutzig Beer', 'Local lager 500ml', 3, 3.50, 1.20, 1, TRUE, 600, 100, 'bottle'),
('P-0010', '8901234567899', 'Primus Beer', 'Local lager 500ml', 3, 3.50, 1.20, 1, TRUE, 600, 100, 'bottle'),
('P-0011', '8901234567900', 'Tusker Beer', 'Kenyan beer 500ml', 3, 4.00, 1.50, 1, TRUE, 400, 50, 'bottle'),
('P-0012', '8901234567901', 'House Wine Red', 'Glass of red wine', 3, 6.00, 2.00, 1, FALSE, NULL, NULL, 'glass'),
('P-0013', '8901234567902', 'House Wine White', 'Glass of white wine', 3, 6.00, 2.00, 1, FALSE, NULL, NULL, 'glass'),
('P-0014', '8901234567903', 'Spring Rolls', 'Vegetable spring rolls (4 pcs)', 4, 8.00, 2.50, 1, TRUE, 150, 30, 'serving'),
('P-0015', '8901234567904', 'Chicken Wings', 'Spicy chicken wings (6 pcs)', 4, 10.00, 3.00, 1, TRUE, 120, 25, 'serving'),
('P-0016', '8901234567905', 'Caesar Salad', 'Classic caesar salad', 4, 9.00, 2.80, 1, TRUE, 100, 20, 'serving'),
('P-0017', '8901234567906', 'Grilled Fish', 'Fresh lake fish with vegetables', 5, 18.00, 6.00, 1, TRUE, 80, 15, 'serving'),
('P-0018', '8901234567907', 'Beef Steak', 'Grilled beef steak with potatoes', 5, 22.00, 7.00, 1, TRUE, 70, 15, 'serving'),
('P-0019', '8901234567908', 'Chicken Curry', 'Chicken curry with rice', 5, 16.00, 5.00, 1, TRUE, 90, 20, 'serving'),
('P-0020', '8901234567909', 'Vegetable Pasta', 'Pasta with fresh vegetables', 5, 14.00, 4.00, 1, TRUE, 100, 20, 'serving'),
('P-0021', '8901234567910', 'Chocolate Cake', 'Slice of chocolate cake', 6, 6.00, 1.50, 1, TRUE, 80, 15, 'slice'),
('P-0022', '8901234567911', 'Ice Cream', 'Vanilla/Chocolate scoop', 6, 4.00, 0.80, 1, TRUE, 200, 40, 'scoop'),
('P-0023', '8901234567912', 'Fresh Fruit Platter', 'Seasonal fruits', 6, 7.00, 2.00, 1, TRUE, 60, 10, 'serving');

-- =====================================================
-- INSERT SAMPLE POS SALES
-- =====================================================
-- First, create a shift
INSERT INTO pos_shifts (shift_code, user_id, outlet_id, opened_at, opening_cash, status) VALUES
('SHIFT-2024-0001', 6, 1, NOW(), 200.00, 'open');

-- Sample sales
INSERT INTO pos_sales (receipt_number, shift_id, user_id, outlet_id, guest_id, subtotal, tax_amount, total_amount, payment_method_id, sale_time) VALUES
('RCP-2024-0001', 1, 6, 1, 1, 45.00, 8.10, 53.10, 1, DATE_SUB(NOW(), INTERVAL 2 HOUR)),
('RCP-2024-0002', 1, 6, 1, NULL, 32.00, 5.76, 37.76, 2, DATE_SUB(NOW(), INTERVAL 1 HOUR)),
('RCP-2024-0003', 1, 7, 2, 2, 28.50, 5.13, 33.63, 1, DATE_SUB(NOW(), INTERVAL 30 MINUTE));

-- Sale items
INSERT INTO pos_sale_items (sale_id, product_id, quantity, unit_price, total_amount) VALUES
(1, 17, 1, 18.00, 18.00),
(1, 18, 1, 22.00, 22.00),
(1, 6, 2, 1.50, 3.00),
(1, 7, 1, 2.00, 2.00),
(2, 1, 2, 3.50, 7.00),
(2, 3, 1, 4.50, 4.50),
(2, 9, 3, 3.50, 10.50),
(2, 14, 1, 8.00, 8.00),
(3, 10, 2, 3.50, 7.00),
(3, 11, 1, 4.00, 4.00),
(3, 15, 1, 10.00, 10.00),
(3, 22, 2, 4.00, 8.00);

-- =====================================================
-- INSERT SAMPLE HOUSEKEEPING TASKS
-- =====================================================
INSERT INTO hk_tasks (task_code, room_id, task_type, priority, status, assigned_to, notes, created_by) VALUES
('HK-2024-0001', 105, 'cleaning', 'high', 'assigned', 11, 'Deep clean required, guest checked out', 10),
('HK-2024-0002', 106, 'cleaning', 'high', 'assigned', 11, 'Deep clean required, guest checked out', 10),
('HK-2024-0003', 114, 'cleaning', 'medium', 'assigned', 11, 'Regular cleaning', 10),
('HK-2024-0004', 115, 'cleaning', 'medium', 'assigned', 12, 'Regular cleaning', 10),
('HK-2024-0005', 203, 'cleaning', 'low', 'pending', NULL, 'Turndown service', 10),
('HK-2024-0006', 213, 'cleaning', 'medium', 'assigned', 11, 'Regular cleaning', 10),
('HK-2024-0007', 214, 'cleaning', 'medium', 'pending', NULL, 'Regular cleaning', 10),
('HK-2024-0008', 302, 'cleaning', 'high', 'assigned', 12, 'VIP guest - extra attention', 10),
('HK-2024-0009', 307, 'cleaning', 'medium', 'pending', NULL, 'Regular cleaning', 10),
('HK-2024-0010', 401, 'cleaning', 'high', 'assigned', 11, 'Family with children - extra towels', 10),
('HK-2024-0011', 110, 'cleaning', 'urgent', 'assigned', 12, 'Guest request - immediate cleaning', 10),
('HK-2024-0012', 107, 'maintenance', 'urgent', 'assigned', 13, 'AC not working', 10);

-- =====================================================
-- INSERT SAMPLE MAINTENANCE TICKETS
-- =====================================================
INSERT INTO maintenance_tickets (ticket_code, room_id, reported_by, issue_type, description, priority, status, assigned_to) VALUES
('MT-2024-0001', 107, 4, 'HVAC', 'Air conditioning not cooling properly', 'urgent', 'in_progress', 13),
('MT-2024-0002', 208, 4, 'Plumbing', 'Sink is clogged', 'high', 'assigned', 13),
('MT-2024-0003', 107, 5, 'Electrical', 'Light fixture not working', 'medium', 'open', NULL),
('MT-2024-0004', NULL, 6, 'General', 'Pool pump making noise', 'high', 'assigned', 13);

-- =====================================================
-- INSERT SYSTEM SETTINGS
-- =====================================================
INSERT INTO system_settings (setting_key, setting_value, setting_type, description) VALUES
('app_name', 'Ruhondo Beach Resort HMS', 'text', 'Application name'),
('app_version', '1.0.0', 'text', 'System version'),
('company_name', 'Ruhondo Beach Resort', 'text', 'Company name'),
('date_format', 'Y-m-d', 'text', 'Date display format'),
('time_format', 'H:i:s', 'text', 'Time display format'),
('items_per_page', '25', 'number', 'Default pagination items per page'),
('enable_email_notifications', 'true', 'boolean', 'Enable email notifications'),
('enable_sms_notifications', 'false', 'boolean', 'Enable SMS notifications'),
('session_timeout', '3600', 'number', 'Session timeout in seconds'),
('maintenance_mode', 'false', 'boolean', 'System maintenance mode');

-- =====================================================
-- INSERT NOTIFICATION TEMPLATES
-- =====================================================
INSERT INTO notification_templates (template_code, template_name, template_type, subject, body, variables) VALUES
('booking_confirmation', 'Booking Confirmation', 'email', 'Your Booking Confirmation - Ruhondo Beach Resort', 'Dear {guest_name},\n\nThank you for choosing Ruhondo Beach Resort. Your booking has been confirmed.\n\nBooking Details:\nBooking Code: {booking_code}\nCheck-in: {check_in}\nCheck-out: {check_out}\nRoom Type: {room_type}\nGuests: {adults} Adults, {children} Children\n\nTotal Amount: {total_amount}\n\nWe look forward to welcoming you!\n\nBest regards,\nRuhondo Beach Resort Team', 'guest_name,booking_code,check_in,check_out,room_type,adults,children,total_amount'),
('welcome_email', 'Welcome Email', 'email', 'Welcome to Ruhondo Beach Resort', 'Dear {guest_name},\n\nWelcome to Ruhondo Beach Resort! We are delighted to have you with us.\n\nYour room {room_number} is ready. Please let us know if you need anything.\n\nEnjoy your stay!\n\nBest regards,\nFront Desk Team', 'guest_name,room_number'),
('invoice_email', 'Invoice', 'email', 'Your Invoice from Ruhondo Beach Resort', 'Dear {guest_name},\n\nPlease find attached your invoice for your recent stay at Ruhondo Beach Resort.\n\nInvoice Number: {invoice_number}\nTotal Amount: {total_amount}\n\nThank you for choosing Ruhondo Beach Resort.\n\nBest regards,\nAccounts Department', 'guest_name,invoice_number,total_amount'),
('reset_password', 'Password Reset', 'email', 'Password Reset Request - Ruhondo Beach Resort', 'Dear {first_name},\n\nYou have requested to reset your password. Click the link below to reset it:\n\n{reset_link}\n\nThis link will expire in 1 hour.\n\nIf you did not request this, please ignore this email.\n\nBest regards,\nSystem Administrator', 'first_name,reset_link'),
('blacklist_alert', 'Guest Blacklist Alert', 'email', 'Guest Blacklist Alert - {guest_name}', 'A guest has been added to the blacklist:\n\nGuest: {guest_name}\nReason: {reason}\nAdded by: {added_by}\n\nPlease update all departments.', 'guest_name,reason,added_by');

-- =====================================================
-- CREATE INDEXES FOR PERFORMANCE
-- =====================================================
-- Additional indexes for common queries
CREATE INDEX idx_reservations_dates_status ON reservations(check_in_date, check_out_date, booking_status);
CREATE INDEX idx_folio_charges_reservation ON folio_charges(reservation_id, posted_at);
CREATE INDEX idx_pos_sales_date ON pos_sales(sale_time, outlet_id);
CREATE INDEX idx_payments_date ON payments(payment_date, status);
CREATE INDEX idx_invoices_due_date ON invoices(due_date, status);
CREATE INDEX idx_audit_logs_user_date ON audit_logs(user_id, created_at);
CREATE INDEX idx_notifications_user_read ON notifications(user_id, is_read, created_at);