import sqlite3 import logging import os import json import threading from datetime import datetime, timedelta from pathlib import Path from typing import Any, Optional import random logger = logging.getLogger(__name__) DB_PATH = "sahara_star.db" # ───────────────────────────────────────────── # DDL: Table Definitions # ───────────────────────────────────────────── SCHEMA_SQL = """ PRAGMA foreign_keys = ON; PRAGMA journal_mode = WAL; PRAGMA synchronous = NORMAL; -- ── Core Hotel Info ─────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS Hotels ( hotel_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, location TEXT NOT NULL, city TEXT NOT NULL DEFAULT 'Mumbai', country TEXT NOT NULL DEFAULT 'India', star_rating INTEGER CHECK(star_rating BETWEEN 1 AND 7), total_rooms INTEGER DEFAULT 0, phone TEXT, email TEXT, website TEXT, check_in_time TEXT DEFAULT '14:00', check_out_time TEXT DEFAULT '12:00', description TEXT, amenities_json TEXT, -- JSON array of amenities created_at DATETIME DEFAULT CURRENT_TIMESTAMP ); -- ── Room Categories ─────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS Room_Types ( type_id INTEGER PRIMARY KEY AUTOINCREMENT, hotel_id INTEGER REFERENCES Hotels(hotel_id), name TEXT NOT NULL, -- 'Deluxe', 'Suite', 'Standard AC' description TEXT, capacity INTEGER DEFAULT 2, bed_type TEXT DEFAULT 'King', view_type TEXT, -- 'Sea View', 'Garden View', 'City View' size_sqft INTEGER, amenities TEXT, -- JSON array price_per_night REAL NOT NULL, weekend_price REAL, is_active INTEGER DEFAULT 1 ); -- ── Individual Rooms ────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS Rooms ( room_id INTEGER PRIMARY KEY AUTOINCREMENT, hotel_id INTEGER REFERENCES Hotels(hotel_id), room_type_id INTEGER REFERENCES Room_Types(type_id), room_number TEXT NOT NULL, floor INTEGER DEFAULT 1, is_available INTEGER DEFAULT 1, is_clean INTEGER DEFAULT 1, is_under_maintenance INTEGER DEFAULT 0, last_cleaned_at DATETIME, notes TEXT, UNIQUE(hotel_id, room_number) ); -- ── Loyalty Programs ────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS Loyalty_Programs ( program_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, -- 'Silver', 'Gold', 'Platinum' min_points INTEGER DEFAULT 0, discount_pct REAL DEFAULT 0, perks_json TEXT, description TEXT ); -- ── Guests ──────────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS Guests ( guest_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, email TEXT UNIQUE, phone TEXT, nationality TEXT DEFAULT 'Indian', id_type TEXT DEFAULT 'Aadhaar', id_number TEXT, date_of_birth TEXT, loyalty_program_id INTEGER REFERENCES Loyalty_Programs(program_id), loyalty_points INTEGER DEFAULT 0, preferred_language TEXT DEFAULT 'en', notes TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP ); -- ── Reservations ────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS Reservations ( res_id INTEGER PRIMARY KEY AUTOINCREMENT, guest_id INTEGER REFERENCES Guests(guest_id), hotel_id INTEGER REFERENCES Hotels(hotel_id), check_in DATE NOT NULL, check_out DATE NOT NULL, num_adults INTEGER DEFAULT 1, num_children INTEGER DEFAULT 0, status TEXT DEFAULT 'confirmed', -- confirmed/cancelled/checked_in/checked_out special_requests TEXT, booking_source TEXT DEFAULT 'direct', -- direct/website/agent total_price REAL, discount_applied REAL DEFAULT 0, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, CHECK(check_out > check_in) ); -- ── Room Bookings (many-to-many: Reservation ↔ Room) ───────────────────────── CREATE TABLE IF NOT EXISTS Room_Bookings ( booking_id INTEGER PRIMARY KEY AUTOINCREMENT, res_id INTEGER REFERENCES Reservations(res_id), room_id INTEGER REFERENCES Rooms(room_id), price_per_night REAL, num_nights INTEGER ); -- ── Bills & Payments ────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS Bills ( bill_id INTEGER PRIMARY KEY AUTOINCREMENT, res_id INTEGER REFERENCES Reservations(res_id), guest_id INTEGER REFERENCES Guests(guest_id), room_charges REAL DEFAULT 0, service_charges REAL DEFAULT 0, food_charges REAL DEFAULT 0, tax_pct REAL DEFAULT 18, discount_amt REAL DEFAULT 0, total_amount REAL, status TEXT DEFAULT 'pending', -- pending/paid/partial issued_at DATETIME DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS Payments ( payment_id INTEGER PRIMARY KEY AUTOINCREMENT, bill_id INTEGER REFERENCES Bills(bill_id), guest_id INTEGER REFERENCES Guests(guest_id), amount REAL NOT NULL, method TEXT DEFAULT 'card', -- card/cash/upi/bank_transfer transaction_ref TEXT, status TEXT DEFAULT 'completed', paid_at DATETIME DEFAULT CURRENT_TIMESTAMP ); -- ── Hotel Services ──────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS Services ( service_id INTEGER PRIMARY KEY AUTOINCREMENT, hotel_id INTEGER REFERENCES Hotels(hotel_id), name TEXT NOT NULL, category TEXT, -- 'spa', 'transport', 'laundry', 'business', 'fitness' description TEXT, price REAL, duration_mins INTEGER, availability TEXT DEFAULT 'available', -- available/busy/closed operating_hours TEXT DEFAULT '09:00-21:00', is_active INTEGER DEFAULT 1 ); CREATE TABLE IF NOT EXISTS Service_Bookings ( sb_id INTEGER PRIMARY KEY AUTOINCREMENT, guest_id INTEGER REFERENCES Guests(guest_id), service_id INTEGER REFERENCES Services(service_id), res_id INTEGER REFERENCES Reservations(res_id), scheduled_date DATE, scheduled_time TEXT, status TEXT DEFAULT 'scheduled', notes TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP ); -- ── F&B ─────────────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS Restaurants ( rest_id INTEGER PRIMARY KEY AUTOINCREMENT, hotel_id INTEGER REFERENCES Hotels(hotel_id), name TEXT NOT NULL, cuisine TEXT, description TEXT, seating_capacity INTEGER, operating_hours TEXT, dress_code TEXT, is_active INTEGER DEFAULT 1 ); CREATE TABLE IF NOT EXISTS Menu_Items ( item_id INTEGER PRIMARY KEY AUTOINCREMENT, rest_id INTEGER REFERENCES Restaurants(rest_id), name TEXT NOT NULL, description TEXT, category TEXT, -- 'starter', 'main', 'dessert', 'beverage' cuisine TEXT, price REAL NOT NULL, is_veg INTEGER DEFAULT 1, is_available INTEGER DEFAULT 1, calories INTEGER, allergens TEXT ); CREATE TABLE IF NOT EXISTS Orders ( order_id INTEGER PRIMARY KEY AUTOINCREMENT, guest_id INTEGER REFERENCES Guests(guest_id), room_id INTEGER REFERENCES Rooms(room_id), rest_id INTEGER REFERENCES Restaurants(rest_id), order_type TEXT DEFAULT 'room_service', items_json TEXT, -- JSON array of {item_id, qty, price} subtotal REAL, tax REAL, total REAL, status TEXT DEFAULT 'placed', placed_at DATETIME DEFAULT CURRENT_TIMESTAMP, delivered_at DATETIME ); -- ── Operations ──────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS Staff ( staff_id INTEGER PRIMARY KEY AUTOINCREMENT, hotel_id INTEGER REFERENCES Hotels(hotel_id), name TEXT NOT NULL, role TEXT, -- 'housekeeping', 'concierge', 'manager', 'chef' department TEXT, phone TEXT, shift TEXT DEFAULT 'morning', is_active INTEGER DEFAULT 1 ); CREATE TABLE IF NOT EXISTS Housekeeping_Tasks ( task_id INTEGER PRIMARY KEY AUTOINCREMENT, room_id INTEGER REFERENCES Rooms(room_id), assigned_to INTEGER REFERENCES Staff(staff_id), task_type TEXT DEFAULT 'regular_cleaning', priority TEXT DEFAULT 'normal', -- low/normal/high/urgent status TEXT DEFAULT 'pending', -- pending/in_progress/completed scheduled_date DATE, completed_at DATETIME, notes TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS Maintenance_Requests ( req_id INTEGER PRIMARY KEY AUTOINCREMENT, room_id INTEGER REFERENCES Rooms(room_id), reported_by INTEGER REFERENCES Guests(guest_id), assigned_to INTEGER REFERENCES Staff(staff_id), issue TEXT NOT NULL, category TEXT, -- 'electrical', 'plumbing', 'ac', 'furniture', 'other' priority TEXT DEFAULT 'normal', status TEXT DEFAULT 'open', -- open/in_progress/resolved/closed created_at DATETIME DEFAULT CURRENT_TIMESTAMP, resolved_at DATETIME ); -- ── Conversational Intelligence & Logging ──────────────────────────────────── CREATE TABLE IF NOT EXISTS sessions ( session_id TEXT PRIMARY KEY, guest_id INTEGER REFERENCES Guests(guest_id), language TEXT DEFAULT 'en', voice_preference TEXT DEFAULT 'female', status TEXT DEFAULT 'active', -- active/closed/escalated turn_count INTEGER DEFAULT 0, last_intent TEXT, last_user_text TEXT, state_json TEXT, started_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP, ended_at DATETIME ); CREATE TABLE IF NOT EXISTS guest_language_preferences ( pref_id INTEGER PRIMARY KEY AUTOINCREMENT, session_id TEXT REFERENCES sessions(session_id), guest_id INTEGER REFERENCES Guests(guest_id), language_code TEXT NOT NULL, confidence REAL DEFAULT 0, source TEXT DEFAULT 'asr', created_at DATETIME DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS ai_conversation_scripts ( script_id INTEGER PRIMARY KEY AUTOINCREMENT, hotel_id INTEGER REFERENCES Hotels(hotel_id), intent_type TEXT NOT NULL, trigger_text TEXT, trigger_mode TEXT DEFAULT 'intent', -- intent/exact/contains language TEXT DEFAULT 'en', voice_preference TEXT DEFAULT 'any', response_text TEXT NOT NULL, is_pre_rendered INTEGER DEFAULT 0, priority INTEGER DEFAULT 100, is_active INTEGER DEFAULT 1, created_at DATETIME DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS upsell_rules ( rule_id INTEGER PRIMARY KEY AUTOINCREMENT, hotel_id INTEGER REFERENCES Hotels(hotel_id), context TEXT NOT NULL, trigger_intent TEXT, offer_text TEXT NOT NULL, price_text TEXT, cta_text TEXT, max_times_per_session INTEGER DEFAULT 1, priority INTEGER DEFAULT 100, is_active INTEGER DEFAULT 1, created_at DATETIME DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS upsell_conversions ( conversion_id INTEGER PRIMARY KEY AUTOINCREMENT, session_id TEXT REFERENCES sessions(session_id), guest_id INTEGER REFERENCES Guests(guest_id), rule_id INTEGER REFERENCES upsell_rules(rule_id), accepted INTEGER DEFAULT 0, offer_text TEXT, response_text TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS complaints ( complaint_id INTEGER PRIMARY KEY AUTOINCREMENT, session_id TEXT REFERENCES sessions(session_id), guest_id INTEGER REFERENCES Guests(guest_id), room_number TEXT, complaint_text TEXT NOT NULL, category TEXT, severity TEXT DEFAULT 'normal', escalation_flag INTEGER DEFAULT 0, status TEXT DEFAULT 'open', created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP, resolved_at DATETIME ); CREATE TABLE IF NOT EXISTS unknown_queries ( query_id INTEGER PRIMARY KEY AUTOINCREMENT, session_id TEXT REFERENCES sessions(session_id), guest_id INTEGER REFERENCES Guests(guest_id), user_text TEXT NOT NULL, detected_intent TEXT, handler_action TEXT, response_mode TEXT, language TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP ); CREATE INDEX IF NOT EXISTS idx_scripts_intent_lang ON ai_conversation_scripts(hotel_id, intent_type, language, is_active, priority); CREATE INDEX IF NOT EXISTS idx_upsell_context ON upsell_rules(hotel_id, context, is_active, priority); CREATE INDEX IF NOT EXISTS idx_sessions_status ON sessions(status, updated_at); CREATE INDEX IF NOT EXISTS idx_complaints_status ON complaints(status, created_at); CREATE INDEX IF NOT EXISTS idx_unknown_queries_created ON unknown_queries(created_at); -- ── Feedback & Analytics ───────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS Feedback ( feedback_id INTEGER PRIMARY KEY AUTOINCREMENT, guest_id INTEGER REFERENCES Guests(guest_id), res_id INTEGER REFERENCES Reservations(res_id), overall_rating INTEGER CHECK(overall_rating BETWEEN 1 AND 5), room_rating INTEGER CHECK(room_rating BETWEEN 1 AND 5), service_rating INTEGER CHECK(service_rating BETWEEN 1 AND 5), food_rating INTEGER CHECK(food_rating BETWEEN 1 AND 5), comment TEXT, submitted_at DATETIME DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS Occupancy_Stats ( stat_id INTEGER PRIMARY KEY AUTOINCREMENT, hotel_id INTEGER REFERENCES Hotels(hotel_id), date DATE NOT NULL, total_rooms INTEGER, occupied_rooms INTEGER, occupancy_pct REAL, revenue REAL, adr REAL, -- Average Daily Rate revpar REAL, -- Revenue Per Available Room UNIQUE(hotel_id, date) ); CREATE TABLE IF NOT EXISTS Revenue_Stats ( stat_id INTEGER PRIMARY KEY AUTOINCREMENT, hotel_id INTEGER REFERENCES Hotels(hotel_id), month TEXT, -- 'YYYY-MM' room_revenue REAL DEFAULT 0, food_revenue REAL DEFAULT 0, service_revenue REAL DEFAULT 0, total_revenue REAL DEFAULT 0, total_guests INTEGER DEFAULT 0 ); CREATE TABLE IF NOT EXISTS Guest_Loyalty ( loyalty_id INTEGER PRIMARY KEY AUTOINCREMENT, guest_id INTEGER REFERENCES Guests(guest_id), points_earned INTEGER DEFAULT 0, points_redeemed INTEGER DEFAULT 0, transaction_type TEXT, -- 'earn'/'redeem' description TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP ); -- ── Indexes for performance ─────────────────────────────────────────────────── CREATE INDEX IF NOT EXISTS idx_rooms_available ON Rooms(is_available, hotel_id); CREATE INDEX IF NOT EXISTS idx_res_guest ON Reservations(guest_id); CREATE INDEX IF NOT EXISTS idx_res_dates ON Reservations(check_in, check_out); CREATE INDEX IF NOT EXISTS idx_res_status ON Reservations(status); CREATE INDEX IF NOT EXISTS idx_guests_email ON Guests(email); CREATE INDEX IF NOT EXISTS idx_hk_status ON Housekeeping_Tasks(status, scheduled_date); CREATE INDEX IF NOT EXISTS idx_maint_status ON Maintenance_Requests(status); CREATE INDEX IF NOT EXISTS idx_orders_guest ON Orders(guest_id, status); """ def create_database(db_path: str = DB_PATH) -> sqlite3.Connection: """Create the SQLite database and all tables.""" conn = sqlite3.connect(db_path) conn.row_factory = sqlite3.Row conn.executescript(SCHEMA_SQL) conn.commit() logger.info(f"✅ Database schema created at: {db_path}") return conn def seed_data(conn: sqlite3.Connection) -> None: """Insert comprehensive sample data for Sahara Star Hotels.""" cur = conn.cursor() # ── Hotel ────────────────────────────────────────────────────────────────── cur.execute(""" INSERT OR IGNORE INTO Hotels (hotel_id, name, location, city, star_rating, total_rooms, phone, email, website, description, amenities_json) VALUES (1, 'Sahara Star', 'Vile Parle East, Near Mumbai Airport', 'Mumbai', 5, 218, '+91-22-6698-5000', 'reservations@saharastar.com', 'https://www.saharastar.com', 'Iconic 5-star hotel with its signature glass-dome lobby, adjacent to Mumbai airport.', '["Swimming Pool","Spa","Gym","Business Center","Multiple Restaurants", "Banquet Halls","Free WiFi","Airport Shuttle","Concierge","Valet Parking"]') """) # ── Loyalty Programs ────────────────────────────────────────────────────── programs = [ (1, 'Silver Star', 0, 5.0, '["Free breakfast on weekends","Priority check-in"]'), (2, 'Gold Star', 5000, 10.0, '["Free breakfast daily","Room upgrade","Late checkout"]'), (3, 'Platinum Star', 15000,15.0, '["All Gold perks","Lounge access","Complimentary night","Butler service"]'), ] cur.executemany(""" INSERT OR IGNORE INTO Loyalty_Programs (program_id, name, min_points, discount_pct, perks_json) VALUES (?, ?, ?, ?, ?) """, programs) # ── Room Types ──────────────────────────────────────────────────────────── room_types = [ (1, 1, 'Standard AC', 'Comfortable standard room with AC', 2, 'Twin', 'City View', 280, 5500, 6000), (2, 1, 'Deluxe Room', 'Premium room with enhanced amenities', 2, 'King', 'Garden View', 380, 8500, 9500), (3, 1, 'Deluxe Sea View', 'Deluxe room with stunning garden views', 2, 'King', 'Garden View', 400, 10500, 12000), (4, 1, 'Club Room', 'Executive club room with lounge access', 2, 'King', 'City View', 450, 14000, 16000), (5, 1, 'Junior Suite', 'Spacious suite for discerning travellers', 3, 'King', 'Garden View', 600, 18000, 21000), (6, 1, 'Sahara Suite', 'Our signature suite experience', 4, 'King', 'Panoramic', 850, 28000, 32000), (7, 1, 'Presidential Suite', 'Ultimate luxury for the most elite guests', 4, 'King', 'Panoramic', 1400,55000, 60000), ] cur.executemany(""" INSERT OR IGNORE INTO Room_Types (type_id, hotel_id, name, description, capacity, bed_type, view_type, size_sqft, price_per_night, weekend_price) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) """, room_types) # ── Rooms (sample of 30 rooms) ──────────────────────────────────────────── rooms_data = [] room_map = [(1,10),(2,8),(3,4),(4,4),(5,2),(6,1),(7,1)] # (type_id, count) room_num = 101 for floor in range(1, 8): for type_id, _ in room_map: rooms_data.append((1, type_id, str(room_num), floor, 1, 1, 0)) room_num += 1 if room_num > 130: break if room_num > 130: break cur.executemany(""" INSERT OR IGNORE INTO Rooms (hotel_id, room_type_id, room_number, floor, is_available, is_clean, is_under_maintenance) VALUES (?, ?, ?, ?, ?, ?, ?) """, rooms_data) # ── Staff ───────────────────────────────────────────────────────────────── staff_data = [ (1, 'Rajan Mehta', 'General Manager', 'Management', '9876500001', 'morning'), (1, 'Priya Sharma', 'Front Desk Manager', 'Front Office', '9876500002', 'morning'), (1, 'Arjun Singh', 'Concierge', 'Front Office', '9876500003', 'morning'), (1, 'Kavya Nair', 'Housekeeping Head', 'Housekeeping', '9876500004', 'morning'), (1, 'Mohammed Rafi', 'Housekeeping', 'Housekeeping', '9876500005', 'evening'), (1, 'Sunita Patel', 'Chef', 'Food & Bev', '9876500006', 'morning'), (1, 'Vikram Rao', 'Spa Manager', 'Spa & Wellness','9876500007', 'morning'), (1, 'Deepa Thomas', 'Reservation Agent', 'Front Office', '9876500008', 'morning'), (1, 'Amir Khan', 'Maintenance', 'Engineering', '9876500009', 'morning'), (1, 'Ritu Gupta', 'Restaurant Manager', 'Food & Bev', '9876500010', 'morning'), ] cur.executemany(""" INSERT OR IGNORE INTO Staff (hotel_id, name, role, department, phone, shift) VALUES (?, ?, ?, ?, ?, ?) """, staff_data) # ── Services ────────────────────────────────────────────────────────────── services = [ (1, 'Spa - Swedish Massage', 'spa', 'Relaxing full body Swedish massage', 3500, 90, 'available', '09:00-21:00'), (1, 'Spa - Deep Tissue Massage','spa', 'Therapeutic deep tissue massage', 4000, 90, 'available', '09:00-21:00'), (1, 'Spa - Ayurvedic Therapy', 'spa', 'Traditional Ayurvedic treatment', 5000, 120, 'available', '10:00-20:00'), (1, 'Gym Access', 'fitness', 'Access to fully equipped gym', 500, None, 'available', '06:00-22:00'), (1, 'Airport Transfer - Sedan', 'transport', 'Sedan car for airport pickup/drop', 1200, 60, 'available', '24/7'), (1, 'Airport Transfer - SUV', 'transport', 'Premium SUV for airport transfer', 2000, 60, 'available', '24/7'), (1, 'Laundry - Express', 'laundry', 'Same-day laundry service', 200, 240, 'available', '08:00-20:00'), (1, 'Laundry - Regular', 'laundry', 'Next-day laundry service', 120, None,'available', '08:00-20:00'), (1, 'Business Center', 'business', 'Meeting room rental per hour', 1500, 60, 'available', '08:00-22:00'), (1, 'Babysitting', 'childcare', 'Professional childcare service', 800, 120, 'available', '09:00-21:00'), (1, 'Swimming Pool', 'recreation','Access to outdoor pool', 300, None, 'available', '07:00-22:00'), (1, 'Doctor on Call', 'medical', 'In-house doctor consultation', 1000, 30, 'available', '24/7'), ] cur.executemany(""" INSERT OR IGNORE INTO Services (hotel_id, name, category, description, price, duration_mins, availability, operating_hours) VALUES (?, ?, ?, ?, ?, ?, ?, ?) """, services) # ── Restaurants ─────────────────────────────────────────────────────────── restaurants = [ (1, 'Riviera', 'International', 'All-day dining with global cuisine', 220, '06:30-23:30', 'Smart Casual'), (1, 'Sahara Grill', 'Indian & BBQ', 'Signature Indian & Continental grill', 80, '12:30-15:00,19:30-23:30', 'Smart Casual'), (1, 'Azure', 'Pan-Asian', 'Contemporary Pan-Asian cuisine', 60, '12:30-15:00,19:30-23:30', 'Smart Casual'), (1, 'The Lobby Bar','Lounge & Bar', 'Cocktails & light bites in stylish bar', 60, '11:00-01:00', 'Casual'), (1, 'Infinity Pool Bar','Poolside', 'Refreshing drinks by the pool', 40, '10:00-20:00', 'Resort Casual'), ] cur.executemany(""" INSERT OR IGNORE INTO Restaurants (hotel_id, name, cuisine, description, seating_capacity, operating_hours, dress_code) VALUES (?, ?, ?, ?, ?, ?, ?) """, restaurants) # ── Menu Items ──────────────────────────────────────────────────────────── menu_items = [ # Riviera (rest_id=1) (1, 'Masala Dosa', 'South Indian breakfast', 'breakfast', 'South Indian', 320, 1, 1), (1, 'Full English Breakfast', 'Continental breakfast', 'breakfast', 'Continental', 750, 0, 1), (1, 'Club Sandwich', 'Classic club sandwich', 'snacks', 'Continental', 450, 0, 1), (1, 'Dal Makhani', 'Creamy black lentils', 'main', 'Indian', 380, 1, 1), (1, 'Butter Chicken', 'Classic butter chicken', 'main', 'Indian', 580, 0, 1), (1, 'Paneer Tikka', 'Grilled cottage cheese', 'starter', 'Indian', 420, 1, 1), (1, 'Gulab Jamun', 'Classic Indian dessert', 'dessert', 'Indian', 180, 1, 1), # Sahara Grill (rest_id=2) (2, 'Tandoori Mixed Grill', 'Assorted tandoori items', 'main', 'Indian', 950, 0, 1), (2, 'Chicken Tikka', 'Spiced chicken tikka', 'starter', 'Indian', 560, 0, 1), (2, 'Seekh Kebab', 'Minced meat kebabs', 'starter', 'Indian', 480, 0, 1), # Azure (rest_id=3) (3, 'Sushi Platter', '12-piece chef selection', 'main', 'Japanese', 1200, 0, 1), (3, 'Pad Thai', 'Classic Thai noodles', 'main', 'Thai', 620, 0, 1), (3, 'Dim Sum Basket', 'Steamed dim sum (6 pcs)', 'starter', 'Chinese', 480, 0, 1), # Lobby Bar (rest_id=4) (4, 'Sahara Signature Cocktail', 'House special cocktail', 'beverage', 'Bar', 650, 1, 1), (4, 'Fresh Lime Soda', 'Refreshing lime soda', 'beverage', 'Bar', 150, 1, 1), (4, 'Cheese Platter', 'Imported cheese selection','snacks', 'Continental', 850, 1, 1), ] cur.executemany(""" INSERT OR IGNORE INTO Menu_Items (rest_id, name, description, category, cuisine, price, is_veg, is_available) VALUES (?, ?, ?, ?, ?, ?, ?, ?) """, menu_items) # ── Sample Guests ───────────────────────────────────────────────────────── today = datetime.now().date() guests = [ (1, 'Rajesh Kumar', 'rajesh.kumar@email.com', '+91-9988776655', 'Indian', 2, 8500, 'hi'), (2, 'Priya Nair', 'priya.nair@email.com', '+91-9977665544', 'Indian', 2, 3200, 'ml'), (3, 'Arjun Sharma', 'arjun.sharma@email.com', '+91-9966554433', 'Indian', 3, 18000, 'hi'), (4, 'Emily Watson', 'emily.watson@email.com', '+44-7700900001', 'British',1, 500, 'en'), (5, 'Mohammed Al-Saud', 'msaud@email.com', '+966-501234567', 'Saudi', 1, 0, 'ar'), (6, 'Sunita Reddy', 'sunita.reddy@email.com', '+91-9955443322', 'Indian', 2, 6200, 'te'), (7, 'Chen Wei', 'chen.wei@email.com', '+86-13800001234','Chinese',1, 0, 'zh'), (8, 'Karthik Murugan', 'karthik.murugan@email.com','+91-9944332211', 'Indian', 2, 1200, 'ta'), ] cur.executemany(""" INSERT OR IGNORE INTO Guests (guest_id, name, email, phone, nationality, loyalty_program_id, loyalty_points, preferred_language) VALUES (?, ?, ?, ?, ?, ?, ?, ?) """, guests) # ── Sample Reservations ─────────────────────────────────────────────────── reservations = [ (1, 1, 1, str(today + timedelta(days=3)), str(today + timedelta(days=6)), 2, 0, 'confirmed', 25500.0), (2, 2, 1, str(today + timedelta(days=1)), str(today + timedelta(days=3)), 2, 0, 'confirmed', 21000.0), (3, 3, 1, str(today), str(today + timedelta(days=2)), 2, 1, 'checked_in', 36000.0), (4, 4, 1, str(today + timedelta(days=7)), str(today + timedelta(days=10)), 1, 0, 'confirmed', 25500.0), (5, 6, 1, str(today - timedelta(days=2)), str(today + timedelta(days=1)), 2, 0, 'checked_in', 31500.0), (6, 1, 1, str(today - timedelta(days=10)), str(today - timedelta(days=7)), 2, 0, 'checked_out', 25500.0), ] cur.executemany(""" INSERT OR IGNORE INTO Reservations (res_id, guest_id, hotel_id, check_in, check_out, num_adults, num_children, status, total_price) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) """, reservations) # ── Occupancy Stats (last 30 days) ──────────────────────────────────────── occ_data = [] for i in range(30): d = today - timedelta(days=i) occ = random.uniform(0.65, 0.92) rev = round(occ * 100 * 9500, 2) occ_data.append((1, str(d), 218, int(218 * occ), round(occ * 100, 1), rev, round(rev / 218, 2), round(rev / 218, 2))) cur.executemany(""" INSERT OR IGNORE INTO Occupancy_Stats (hotel_id, date, total_rooms, occupied_rooms, occupancy_pct, revenue, adr, revpar) VALUES (?, ?, ?, ?, ?, ?, ?, ?) """, occ_data) # ── Feedback ────────────────────────────────────────────────────────────── feedbacks = [ (1, 6, 4, 5, 4, 5, 'Excellent stay! Room was immaculate and food was amazing.'), (2, 5, 5, 5, 5, 5, 'Perfect hotel. Will definitely come back.'), (3, 4, 4, 4, 4, 3, 'Great service overall. Room service could be faster.'), ] cur.executemany(""" INSERT OR IGNORE INTO Feedback (guest_id, res_id, overall_rating, room_rating, service_rating, food_rating, comment) VALUES (?, ?, ?, ?, ?, ?, ?) """, feedbacks) # ── Maintenance Requests ────────────────────────────────────────────────── cur.execute(""" INSERT OR IGNORE INTO Maintenance_Requests (room_id, issue, category, priority, status) VALUES (3, 'AC not cooling properly', 'ac', 'high', 'in_progress') """) # ── Script Library ─────────────────────────────────────────────────────── scripts = [ (1, 1, 'GREETING', 'welcome', 'intent', 'en', 'female', "Welcome to Sahara Star Hotels! I'm Priya, your personal voice concierge. How may I assist you today?", 1, 100), (7, 1, 'GREETING', 'welcome', 'intent', 'en', 'male', "Welcome to Sahara Star Hotels! I'm Raj, your personal voice concierge. How may I assist you today?", 1, 100), (2, 1, 'DB_FILLER', 'checking', 'intent', 'en', 'any', "Let me check that for you right away...", 1, 95), (3, 1, 'FAREWELL', 'goodbye', 'intent', 'en', 'any', "Thank you for calling Sahara Star. I hope we can host you soon. Goodbye!", 1, 100), (4, 1, 'LOW_CONFIDENCE', 'repeat', 'intent', 'en', 'any', "I didn't quite catch that. Could you please repeat?", 1, 95), (5, 1, 'BOOKING_CHECKING', 'availability', 'intent', 'en', 'any', "I'm checking our system for the latest availability...", 1, 95), (6, 1, 'ESCALATION_OFFER', 'front desk', 'intent', 'en', 'any', "Would you like me to connect you with our front desk team?", 1, 90), ] cur.executemany(""" INSERT OR IGNORE INTO ai_conversation_scripts (script_id, hotel_id, intent_type, trigger_text, trigger_mode, language, voice_preference, response_text, is_pre_rendered, priority) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) """, scripts) # ── Upsell Rules ───────────────────────────────────────────────────────── upsell_rules = [ (1, 1, 'room_booking', 'booking', 'Since you are staying with us, would you like to add our Couples Spa Package for ₹3,500?', '₹3,500', 'I can add it to your stay.', 1, 100), (2, 1, 'room_booking', 'booking', 'We also offer airport pickup at ₹1,200 if you would like it.', '₹1,200', 'Should I arrange it?', 1, 90), (3, 1, 'dining', 'restaurant', 'Our Signature Restaurant is a popular choice for guests staying at Sahara Star.', None, 'Would you like me to share restaurant options?', 1, 80), (4, 1, 'check_in', 'faq_check_in', 'Early check-in can be arranged for select rooms when available.', None, 'Should I check early check-in options?', 1, 70), ] cur.executemany(""" INSERT OR IGNORE INTO upsell_rules (rule_id, hotel_id, context, trigger_intent, offer_text, price_text, cta_text, max_times_per_session, priority) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) """, upsell_rules) conn.commit() logger.info("✅ Seed data inserted successfully") def print_summary(conn: sqlite3.Connection) -> None: """Print a summary of the database contents.""" tables = [ 'Hotels', 'Room_Types', 'Rooms', 'Loyalty_Programs', 'Guests', 'Reservations', 'Services', 'Restaurants', 'Menu_Items', 'Staff', 'sessions', 'ai_conversation_scripts', 'upsell_rules', 'complaints', 'unknown_queries', 'guest_language_preferences', 'Feedback', 'Occupancy_Stats' ] print("\n" + "=" * 50) print(" AARA Database Summary — Sahara Star Hotels") print("=" * 50) for table in tables: count = conn.execute(f"SELECT COUNT(*) FROM {table}").fetchone()[0] print(f" {table:<25} {count:>5} rows") print("=" * 50 + "\n") def initialize_database(db_path: str = DB_PATH, verbose: bool = True) -> sqlite3.Connection: """ Full database initialization: create schema + seed data. Args: db_path: Path to the SQLite database file verbose: Print summary after creation Returns: Active database connection """ logging.basicConfig(level=logging.INFO, format='%(asctime)s %(levelname)s %(message)s') if Path(db_path).exists(): logger.info(f"ℹ️ Database already exists at {db_path}. Using existing.") else: logger.info(f"🆕 Creating new database at {db_path}") conn = create_database(db_path) seed_data(conn) if verbose: print_summary(conn) return conn class HotelDatabase: """ High-level interface for hotel database operations. Used by the LLM agent to execute queries. """ def __init__(self, db_path: str = DB_PATH): self.db_path = db_path self._local = threading.local() if not Path(db_path).exists(): logger.info("Database not found — initializing...") initialize_database(db_path, verbose=False) def connect(self) -> sqlite3.Connection: """Get or create a database connection.""" conn = getattr(self._local, "conn", None) if conn is None: conn = sqlite3.connect(self.db_path) conn.row_factory = sqlite3.Row conn.execute("PRAGMA foreign_keys = ON") self._local.conn = conn return conn def close(self) -> None: """Close the database connection.""" conn = getattr(self._local, "conn", None) if conn: conn.close() self._local.conn = None def execute_query(self, sql: str, params: tuple = ()) -> list[dict]: """ Execute a SELECT query and return results as a list of dicts. Args: sql: SQL SELECT statement params: Query parameters (for parameterized queries) Returns: List of row dicts, or error dict on failure """ try: conn = self.connect() # Safety: only allow SELECT statements from LLM stripped = sql.strip().upper() if not (stripped.startswith('SELECT') or stripped.startswith('WITH')): return [{"error": "Only SELECT queries are allowed from voice agent"}] cur = conn.execute(sql, params) rows = [dict(row) for row in cur.fetchall()] return rows if rows else [{"message": "No results found"}] except sqlite3.Error as e: logger.error(f"DB query error: {e} | SQL: {sql[:200]}") return [{"error": str(e)}] def get_hotel_profile(self) -> dict: """Return the primary hotel profile as a dictionary.""" rows = self.execute_query( """ SELECT name, location, city, country, star_rating, total_rooms, phone, email, website, check_in_time, check_out_time, description, amenities_json FROM Hotels WHERE hotel_id = 1 LIMIT 1 """ ) if rows and "error" not in rows[0]: return rows[0] return {} def get_room_types(self, room_type: Optional[str] = None) -> list[dict]: """Return active room types and pricing information.""" if room_type: return self.execute_query( """ SELECT type_id, name, description, capacity, bed_type, view_type, size_sqft, price_per_night, weekend_price FROM Room_Types WHERE hotel_id = 1 AND is_active = 1 AND name LIKE ? ORDER BY price_per_night """, (f"%{room_type}%",), ) return self.execute_query( """ SELECT type_id, name, description, capacity, bed_type, view_type, size_sqft, price_per_night, weekend_price FROM Room_Types WHERE hotel_id = 1 AND is_active = 1 ORDER BY price_per_night """ ) def create_booking(self, booking_details: dict) -> dict: """Create a reservation and room booking from verified details.""" required_fields = ["guest_name", "guest_email", "room_type", "check_in", "check_out"] missing = [field_name for field_name in required_fields if not booking_details.get(field_name)] if missing: return {"error": f"Missing booking fields: {', '.join(missing)}"} try: check_in = datetime.fromisoformat(str(booking_details["check_in"]).split("T")[0]).date() check_out = datetime.fromisoformat(str(booking_details["check_out"]).split("T")[0]).date() except ValueError: return {"error": "Invalid booking dates"} if check_out <= check_in: return {"error": "Check-out must be after check-in"} nights = max((check_out - check_in).days, 1) room_type_name = str(booking_details["room_type"]).strip() available_rooms = self.get_available_rooms(check_in.isoformat(), check_out.isoformat(), room_type_name) if not available_rooms or "error" in available_rooms[0]: return {"error": "No verified room availability for the requested stay"} selected_room_number = available_rooms[0]["room_number"] room_row = self.execute_query( "SELECT room_id, room_type_id FROM Rooms WHERE room_number = ? AND hotel_id = 1 LIMIT 1", (selected_room_number,), ) if not room_row or "error" in room_row[0]: return {"error": "Verified room record not found"} room_id = room_row[0]["room_id"] room_type_id = room_row[0]["room_type_id"] room_type_row = self.execute_query( "SELECT name, price_per_night FROM Room_Types WHERE type_id = ? AND hotel_id = 1 LIMIT 1", (room_type_id,), ) if not room_type_row or "error" in room_type_row[0]: return {"error": "Verified room pricing not found"} price_per_night = float(room_type_row[0]["price_per_night"]) total_price = round(price_per_night * nights, 2) conn = self.connect() try: conn.execute("BEGIN IMMEDIATE") guest_row = conn.execute( "SELECT guest_id FROM Guests WHERE email = ? LIMIT 1", (booking_details["guest_email"],), ).fetchone() if guest_row: guest_id = guest_row[0] conn.execute( """ UPDATE Guests SET name = COALESCE(NULLIF(?, ''), name), phone = COALESCE(NULLIF(?, ''), phone), preferred_language = COALESCE(NULLIF(?, ''), preferred_language) WHERE guest_id = ? """, ( booking_details.get("guest_name", ""), booking_details.get("guest_phone", ""), booking_details.get("preferred_language", "en"), guest_id, ), ) else: cur = conn.execute( """ INSERT INTO Guests (name, email, phone, preferred_language) VALUES (?, ?, ?, ?) """, ( booking_details["guest_name"], booking_details["guest_email"], booking_details.get("guest_phone"), booking_details.get("preferred_language", "en"), ), ) guest_id = cur.lastrowid cur = conn.execute( """ INSERT INTO Reservations (guest_id, hotel_id, check_in, check_out, num_adults, num_children, status, special_requests, booking_source, total_price) VALUES (?, 1, ?, ?, ?, ?, 'confirmed', ?, 'voice', ?) """, ( guest_id, check_in.isoformat(), check_out.isoformat(), int(booking_details.get("num_adults", 1)), int(booking_details.get("num_children", 0)), booking_details.get("special_requests", ""), total_price, ), ) res_id = cur.lastrowid conn.execute( """ INSERT INTO Room_Bookings (res_id, room_id, price_per_night, num_nights) VALUES (?, ?, ?, ?) """, (res_id, room_id, price_per_night, nights), ) conn.commit() except sqlite3.Error as exc: conn.rollback() return {"error": str(exc)} confirmation_number = f"AS{res_id:06d}" return { "confirmation_number": confirmation_number, "reservation_id": res_id, "guest_name": booking_details["guest_name"], "guest_email": booking_details["guest_email"], "room_number": selected_room_number, "room_type": room_type_row[0]["name"], "check_in": check_in.isoformat(), "check_out": check_out.isoformat(), "nights": nights, "total_price": total_price, } def get_available_rooms(self, check_in: str, check_out: str, room_type: Optional[str] = None) -> list[dict]: """Get available rooms for given dates.""" sql = """ SELECT r.room_id, r.room_number, rt.name as room_type, rt.price_per_night, rt.capacity, rt.view_type, rt.bed_type, r.floor FROM Rooms r JOIN Room_Types rt ON r.room_type_id = rt.type_id WHERE r.is_available = 1 AND r.is_under_maintenance = 0 AND r.room_id NOT IN ( SELECT rb.room_id FROM Room_Bookings rb JOIN Reservations res ON rb.res_id = res.res_id WHERE res.status IN ('confirmed','checked_in') AND res.check_in < ? AND res.check_out > ? ) """ params = [check_out, check_in] if room_type: sql += " AND rt.name LIKE ?" params.append(f"%{room_type}%") sql += " ORDER BY rt.price_per_night" return self.execute_query(sql, tuple(params)) def get_guest_reservations(self, guest_email: str) -> list[dict]: """Get all reservations for a guest by email.""" sql = """ SELECT r.res_id, r.check_in, r.check_out, r.status, r.total_price, r.num_adults, rt.name as room_type FROM Reservations r JOIN Guests g ON r.guest_id = g.guest_id LEFT JOIN Room_Bookings rb ON r.res_id = rb.res_id LEFT JOIN Rooms rm ON rb.room_id = rm.room_id LEFT JOIN Room_Types rt ON rm.room_type_id = rt.type_id WHERE g.email = ? ORDER BY r.check_in DESC """ return self.execute_query(sql, (guest_email,)) def get_services_by_category(self, category: Optional[str] = None) -> list[dict]: """Get available services.""" if category: sql = "SELECT name, category, price, description, operating_hours FROM Services WHERE category = ? AND is_active = 1" return self.execute_query(sql, (category,)) else: sql = "SELECT name, category, price, description, operating_hours FROM Services WHERE is_active = 1 ORDER BY category" return self.execute_query(sql) def get_restaurant_menu(self, restaurant_name: Optional[str] = None) -> list[dict]: """Get menu items for a restaurant.""" if restaurant_name: sql = """ SELECT mi.name, mi.category, mi.price, mi.is_veg, mi.description, r.name as restaurant FROM Menu_Items mi JOIN Restaurants r ON mi.rest_id = r.rest_id WHERE r.name LIKE ? AND mi.is_available = 1 ORDER BY mi.category, mi.price """ return self.execute_query(sql, (f"%{restaurant_name}%",)) else: sql = """ SELECT mi.name, mi.category, mi.price, mi.is_veg, r.name as restaurant FROM Menu_Items mi JOIN Restaurants r ON mi.rest_id = r.rest_id WHERE mi.is_available = 1 ORDER BY r.name, mi.category """ return self.execute_query(sql) @staticmethod def _script_scope_matches(row: dict, language: str, voice_preference: str) -> bool: row_lang = str(row.get("language") or "any").lower() row_voice = str(row.get("voice_preference") or "any").lower() if row_lang not in {"any", "", language or "any"}: return False if row_voice not in {"any", "", voice_preference or "any"}: return False return True @staticmethod def _script_trigger_matches(row: dict, intent_type: Optional[str], text: str) -> bool: row_intent = str(row.get("intent_type") or "").lower() trigger_mode = str(row.get("trigger_mode") or "intent").lower() trigger_text = " ".join(str(row.get("trigger_text") or "").lower().split()) if intent_type and row_intent == intent_type.lower() and trigger_mode == "intent": return True if text and trigger_mode == "exact" and text == trigger_text: return True if text and trigger_mode in {"contains", "intent"} and trigger_text and trigger_text in text: return True return False def get_conversation_script( self, intent_type: Optional[str] = None, user_text: Optional[str] = None, language: Optional[str] = None, voice_preference: Optional[str] = None, ) -> Optional[dict]: """Return the best matching approved conversation script.""" rows = self.execute_query( """ SELECT script_id, intent_type, trigger_text, trigger_mode, language, voice_preference, response_text, is_pre_rendered, priority FROM ai_conversation_scripts WHERE hotel_id = 1 AND is_active = 1 ORDER BY priority DESC, script_id ASC """ ) if not rows or "error" in rows[0]: return None text = " ".join((user_text or "").lower().split()) lang = (language or "").lower() voice = (voice_preference or "").lower() best_match = next( ( row for row in rows if self._script_scope_matches(row, lang, voice) and self._script_trigger_matches(row, intent_type, text) ), None, ) if best_match is not None: return best_match if intent_type: return next( ( row for row in rows if str(row.get("intent_type") or "").lower() == intent_type.lower() and self._script_scope_matches(row, lang, voice) ), None, ) return None def get_pre_rendered_scripts(self) -> list[dict]: """Return scripts marked for startup audio pre-rendering.""" return self.execute_query( """ SELECT script_id, intent_type, language, voice_preference, response_text FROM ai_conversation_scripts WHERE hotel_id = 1 AND is_active = 1 AND is_pre_rendered = 1 ORDER BY priority DESC, script_id ASC """ ) def get_upsell_rules(self, context: Optional[str] = None) -> list[dict]: """Return active upsell rules, optionally filtered by context.""" if context: return self.execute_query( """ SELECT rule_id, context, trigger_intent, offer_text, price_text, cta_text, max_times_per_session, priority FROM upsell_rules WHERE hotel_id = 1 AND is_active = 1 AND context = ? ORDER BY priority DESC, rule_id ASC """, (context,), ) return self.execute_query( """ SELECT rule_id, context, trigger_intent, offer_text, price_text, cta_text, max_times_per_session, priority FROM upsell_rules WHERE hotel_id = 1 AND is_active = 1 ORDER BY priority DESC, rule_id ASC """ ) def get_upsell_rule_for_context(self, context: str) -> Optional[dict]: """Return the highest priority upsell rule for a given context.""" rows = self.get_upsell_rules(context) if rows and "error" not in rows[0]: return rows[0] return None def save_session_snapshot( self, session_id: str, *, language: str, voice_preference: str, turn_count: int, last_intent: str = "", last_user_text: str = "", state_json: str = "{}", guest_id: Optional[int] = None, status: str = "active", ended: bool = False, ) -> None: """Create or update a session lifecycle snapshot.""" conn = self.connect() conn.execute( """ INSERT INTO sessions (session_id, guest_id, language, voice_preference, status, turn_count, last_intent, last_user_text, state_json, started_at, updated_at, ended_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, CASE WHEN ? THEN CURRENT_TIMESTAMP ELSE NULL END) ON CONFLICT(session_id) DO UPDATE SET guest_id = COALESCE(excluded.guest_id, sessions.guest_id), language = excluded.language, voice_preference = excluded.voice_preference, status = excluded.status, turn_count = excluded.turn_count, last_intent = excluded.last_intent, last_user_text = excluded.last_user_text, state_json = excluded.state_json, updated_at = CURRENT_TIMESTAMP, ended_at = CASE WHEN excluded.ended_at IS NOT NULL THEN excluded.ended_at ELSE sessions.ended_at END """, ( session_id, guest_id, language, voice_preference, status, turn_count, last_intent, last_user_text, state_json, ended, ), ) conn.commit() def get_session_snapshot(self, session_id: str) -> Optional[dict[str, Any]]: """Fetch the latest stored snapshot for a session id.""" rows = self.execute_query( """ SELECT session_id, guest_id, language, voice_preference, status, turn_count, last_intent, last_user_text, state_json, started_at, updated_at, ended_at FROM sessions WHERE session_id = ? ORDER BY updated_at DESC LIMIT 1 """, (session_id,), ) if rows and "error" not in rows[0]: return rows[0] return None def log_guest_language_preference( self, session_id: str, language_code: str, confidence: float, *, guest_id: Optional[int] = None, source: str = "asr", ) -> None: """Record a detected language preference for analytics.""" conn = self.connect() conn.execute( """ INSERT INTO guest_language_preferences (session_id, guest_id, language_code, confidence, source) VALUES (?, ?, ?, ?, ?) """, (session_id, guest_id, language_code, confidence, source), ) conn.commit() def log_unknown_query( self, session_id: str, user_text: str, detected_intent: str, handler_action: str, response_mode: str, language: str, *, guest_id: Optional[int] = None, ) -> None: """Record a guest request that did not map to a verified hotel answer.""" conn = self.connect() conn.execute( """ INSERT INTO unknown_queries (session_id, guest_id, user_text, detected_intent, handler_action, response_mode, language) VALUES (?, ?, ?, ?, ?, ?, ?) """, (session_id, guest_id, user_text, detected_intent, handler_action, response_mode, language), ) conn.commit() def log_complaint( self, session_id: str, complaint_text: str, *, room_number: Optional[str] = None, category: Optional[str] = None, severity: str = "normal", escalation_flag: int = 0, guest_id: Optional[int] = None, ) -> dict: """Insert a complaint row and return the stored record id.""" conn = self.connect() session_fk: Optional[str] = None if session_id: exists = conn.execute( "SELECT 1 FROM sessions WHERE session_id = ? LIMIT 1", (session_id,), ).fetchone() if exists: session_fk = session_id cur = conn.execute( """ INSERT INTO complaints (session_id, guest_id, room_number, complaint_text, category, severity, escalation_flag, status) VALUES (?, ?, ?, ?, ?, ?, ?, 'open') """, (session_fk, guest_id, room_number, complaint_text, category, severity, escalation_flag), ) conn.commit() return {"complaint_id": cur.lastrowid, "escalation_flag": escalation_flag, "status": "open"} def log_upsell_conversion( self, session_id: str, rule_id: Optional[int], *, accepted: bool, offer_text: str, response_text: str, guest_id: Optional[int] = None, ) -> None: """Record whether an upsell offer was accepted by the guest.""" conn = self.connect() conn.execute( """ INSERT INTO upsell_conversions (session_id, guest_id, rule_id, accepted, offer_text, response_text) VALUES (?, ?, ?, ?, ?, ?) """, (session_id, guest_id, rule_id, 1 if accepted else 0, offer_text, response_text), ) conn.commit() def get_occupancy_summary(self) -> dict: """Get current occupancy statistics.""" today = datetime.now().date().isoformat() row = self.execute_query( "SELECT * FROM Occupancy_Stats WHERE date = ? AND hotel_id = 1", (today,) ) if row and "error" not in row[0]: return row[0] # Compute live total = self.execute_query("SELECT COUNT(*) as cnt FROM Rooms WHERE hotel_id = 1")[0]['cnt'] occupied = self.execute_query( """SELECT COUNT(*) as cnt FROM Reservations WHERE status = 'checked_in' AND hotel_id = 1""" )[0]['cnt'] return {"total_rooms": total, "occupied": occupied, "occupancy_pct": round(occupied / max(total, 1) * 100, 1)} def format_results_for_llm(self, results: list[dict], max_rows: int = 10) -> str: """Format query results as a compact string for LLM context.""" if not results: return "No results." if "error" in results[0]: return f"Database error: {results[0]['error']}" limited = results[:max_rows] lines = [] for row in limited: lines.append(", ".join(f"{k}: {v}" for k, v in row.items() if v is not None)) suffix = f"\n[...and {len(results) - max_rows} more rows]" if len(results) > max_rows else "" return "\n".join(lines) + suffix def __enter__(self): return self def __exit__(self, *_): self.close() # ───────────────────────────────────────────── # CLI Entry Point # ───────────────────────────────────────────── if __name__ == "__main__": import argparse parser = argparse.ArgumentParser(description="Initialize AARA Hotel Database") parser.add_argument("--db", default=DB_PATH, help="Database file path") parser.add_argument("--reset", action="store_true", help="Delete and recreate database") args = parser.parse_args() if args.reset and Path(args.db).exists(): os.remove(args.db) print(f"🗑️ Deleted existing database: {args.db}") conn = initialize_database(args.db) # Quick smoke test db = HotelDatabase(args.db) rooms = db.get_available_rooms( str(datetime.now().date() + timedelta(days=5)), str(datetime.now().date() + timedelta(days=7)) ) print(f"✅ Available rooms test: {len(rooms)} rooms found") db.close() conn.close()