""" Hotel seed data: room types, rooms, clients, reservations, folios, folio items. Generates realistic hotel operational data for 2023, 2024, and 2025 to populate actual revenue figures used in budget vs actual comparison reports. """ from datetime import date, datetime from sqlalchemy.orm import Session from app.models.room import Room, RoomType, RoomStatus from app.models.client import Client from app.models.reservation import Reservation, ReservationStatus, ReservationSource from app.models.folio import Folio, FolioItem, FolioStatus from app.models.payment import Payment, PaymentMethod, PaymentStatus from app.models.establishment import Establishment from app.models.user import User # --------------------------------------------------------------------------- # Seasonality: monthly weight out of annual total (sums to 1.0) # --------------------------------------------------------------------------- MONTHLY_WEIGHTS = { 1: 0.100, 2: 0.095, 3: 0.085, 4: 0.075, 5: 0.070, 6: 0.065, 7: 0.055, 8: 0.050, 9: 0.060, 10: 0.085, 11: 0.100, 12: 0.110, } # --------------------------------------------------------------------------- # Actual annual revenue per year per department (XOF) # Calibrated to produce realistic budget vs actual variances # --------------------------------------------------------------------------- ACTUAL_REVENUE_BY_YEAR = { 2023: { "HEBERG": 50_400_000, "REST": 12_096_000, "BAR": 3_914_000, "ROOM_SERVICE": 1_764_000, "SPA": 3_680_000, "BOUTIQUE": 1_330_000, "PARKING": 969_000, "MINIBAR": 605_000, "MISC": 472_500, }, 2024: { "HEBERG": 60_480_000, "REST": 13_750_000, "BAR": 4_494_000, "ROOM_SERVICE": 2_205_000, "SPA": 4_484_000, "BOUTIQUE": 1_648_000, "PARKING": 1_092_000, "MINIBAR": 669_600, "MISC": 583_000, }, 2025: { "HEBERG": 10_800_000, # ~2 months at ~-10% vs budget "REST": 2_380_000, "BAR": 768_000, "ROOM_SERVICE": 384_000, "SPA": 704_000, "BOUTIQUE": 304_000, "PARKING": 192_000, "MINIBAR": 115_200, "MISC": 97_600, }, } DEPT_TAX_RATE = { "HEBERG": 0.10, "REST": 0.10, "BAR": 0.20, "ROOM_SERVICE": 0.10, "SPA": 0.20, "BOUTIQUE": 0.20, "PARKING": 0.20, "LAUNDRY": 0.20, "MINIBAR": 0.20, "PHONE": 0.20, "MISC": 0.10, } DEPT_DESCRIPTIONS = { "HEBERG": "Nuitées hébergement", "REST": "Restaurant - repas", "BAR": "Bar - consommations", "ROOM_SERVICE": "Room service", "SPA": "Spa & Bien-être", "BOUTIQUE": "Boutique - achats", "PARKING": "Stationnement", "LAUNDRY": "Blanchisserie", "MINIBAR": "Minibar", "PHONE": "Communications", "MISC": "Prestations diverses", } SEED_ROOM_TYPE_CODES = {"STD", "SUP", "DLX", "JR_STE", "STE"} def seed_hotel_data(db: Session) -> None: """Main entry point — seeds all hotel operational data.""" establishment = db.query(Establishment).first() if not establishment: print("No establishment found — skipping hotel seeds") return admin = db.query(User).first() from sqlalchemy import extract as sa_extract existing_items = db.query(FolioItem).count() if existing_items >= 100: # Check if items have correct historical dates (not all in current year) current_year = datetime.utcnow().year wrong_year_count = db.query(FolioItem).filter( sa_extract('year', FolioItem.created_at) == current_year ).count() if wrong_year_count >= 100: print(f"Resetting {wrong_year_count} folio items with wrong dates...") # Delete seed reservations and folios to recreate with correct dates seed_res = db.query(Reservation).filter( Reservation.confirmation_number.like('RES-SEED-%') ).all() for r in seed_res: if r.folio: db.delete(r.folio) db.delete(r) db.flush() print(" Deleted seed reservations/folios — will recreate with correct dates") else: print(f"Hotel seed data already exists ({existing_items} folio items) — skipping") return print("Seeding hotel operational data (rooms, clients, reservations, folios)...") room_types = _seed_room_types(db, establishment.id) db.flush() # Ensure IDs are populated after flush for rt in room_types: if rt.id is None: db.refresh(rt) rooms = _seed_rooms(db, establishment.id, room_types) db.flush() for r in rooms: if r.id is None: db.refresh(r) clients = _seed_clients(db) db.flush() for c in clients: if c.id is None: db.refresh(c) if not rooms: print(" No rooms available — skipping reservations") db.commit() return if not clients: print(" No clients available — skipping reservations") db.commit() return _seed_reservations_and_folios(db, establishment, rooms, clients, admin) db.commit() print("Hotel seed data created successfully.") # --------------------------------------------------------------------------- # Room types — create by code, skip if already exists # --------------------------------------------------------------------------- def _seed_room_types(db: Session, establishment_id: int) -> list: seed_codes = ["STD", "SUP", "DLX", "JR_STE", "STE"] types_data = { "STD": { "name": "Chambre Standard", "name_en": "Standard Room", "capacity_adults": 2, "capacity_children": 1, "capacity_beds": 1, "base_price": 45_000.0, "description": "Chambre confortable avec lit double, climatisation, TV", }, "SUP": { "name": "Chambre Supérieure", "name_en": "Superior Room", "capacity_adults": 2, "capacity_children": 2, "capacity_beds": 2, "base_price": 65_000.0, "description": "Chambre spacieuse avec vue jardin, balcon privé", }, "DLX": { "name": "Chambre Deluxe", "name_en": "Deluxe Room", "capacity_adults": 2, "capacity_children": 2, "capacity_beds": 1, "base_price": 85_000.0, "description": "Chambre deluxe avec vue mer, jacuzzi", }, "JR_STE": { "name": "Junior Suite", "name_en": "Junior Suite", "capacity_adults": 2, "capacity_children": 2, "capacity_beds": 1, "base_price": 120_000.0, "description": "Junior suite avec salon séparé et vue panoramique", }, "STE": { "name": "Suite Présidentielle", "name_en": "Presidential Suite", "capacity_adults": 4, "capacity_children": 2, "capacity_beds": 2, "base_price": 250_000.0, "description": "Suite luxueuse avec terrasse privée, piscine et butler service", }, } result = [] created = 0 for code in seed_codes: existing = db.query(RoomType).filter( RoomType.establishment_id == establishment_id, RoomType.code == code ).first() if existing: result.append(existing) else: data = types_data[code] rt = RoomType(establishment_id=establishment_id, code=code, **data) db.add(rt) result.append(rt) created += 1 if created: print(f" Created {created} room types") return result # --------------------------------------------------------------------------- # Rooms — create by room_number, skip if already exists # --------------------------------------------------------------------------- def _seed_rooms(db: Session, establishment_id: int, room_types: list) -> list: type_map = {rt.code: rt for rt in room_types} rooms_config = [ ("101", 1, "STD"), ("102", 1, "STD"), ("103", 1, "STD"), ("104", 1, "STD"), ("105", 1, "STD"), ("106", 1, "SUP"), ("107", 1, "SUP"), ("201", 2, "STD"), ("202", 2, "STD"), ("203", 2, "STD"), ("204", 2, "STD"), ("205", 2, "STD"), ("206", 2, "SUP"), ("207", 2, "SUP"), ("208", 2, "SUP"), ("301", 3, "DLX"), ("302", 3, "DLX"), ("303", 3, "DLX"), ("401", 4, "JR_STE"), ("402", 4, "JR_STE"), ("501", 5, "STE"), ] result = [] created = 0 for room_number, floor, type_code in rooms_config: rt = type_map.get(type_code) if not rt: continue existing_room = db.query(Room).filter( Room.establishment_id == establishment_id, Room.room_number == room_number ).first() if existing_room: result.append(existing_room) else: room = Room( establishment_id=establishment_id, room_type_id=rt.id, room_number=room_number, floor=floor, status=RoomStatus.AVAILABLE, is_active=True, ) db.add(room) result.append(room) created += 1 if created: print(f" Created {created} rooms") return result # --------------------------------------------------------------------------- # Clients — create by id_number, skip if already exists # --------------------------------------------------------------------------- def _seed_clients(db: Session) -> list: clients_data = [ ("Mamadou", "Diallo", "mamadou.diallo@email.sn", "+221 77 100 0001", "SN", "CIN", "SN001"), ("Fatou", "Ndiaye", "fatou.ndiaye@email.sn", "+221 77 100 0002", "SN", "CIN", "SN002"), ("Ibrahim", "Sow", "ibrahim.sow@gmail.com", "+221 76 100 0003", "SN", "PASSEPORT", "SN003"), ("Aissatou", "Ba", "aissatou.ba@email.sn", "+221 77 100 0004", "SN", "CIN", "SN004"), ("Ousmane", "Cissé", "ousmane.cisse@email.sn", "+221 70 100 0005", "SN", "CIN", "SN005"), ("Marie", "Dupont", "marie.dupont@email.fr", "+33 6 10 00 00 06", "FR", "PASSEPORT", "FR006"), ("Jean-Pierre", "Martin", "jp.martin@email.fr", "+33 6 10 00 00 07", "FR", "PASSEPORT", "FR007"), ("Sophie", "Leroy", "sophie.leroy@email.fr", "+33 6 10 00 00 08", "FR", "PASSEPORT", "FR008"), ("Kofi", "Asante", "kofi.asante@gmail.com", "+233 24 100 0009", "GH", "PASSEPORT", "GH009"), ("Amina", "Coulibaly", "amina.coulibaly@email.ml", "+223 70 100 0010", "ML", "PASSEPORT", "ML010"), ("Thomas", "Schmidt", "t.schmidt@email.de", "+49 170 100 0011", "DE", "PASSEPORT", "DE011"), ("Chiara", "Rossi", "chiara.rossi@email.it", "+39 340 100 0012", "IT", "PASSEPORT", "IT012"), ("Carlos", "Garcia", "carlos.garcia@email.es", "+34 600 100 013", "ES", "PASSEPORT", "ES013"), ("Anna", "Kowalski", "anna.kowalski@email.pl", "+48 500 100 014", "PL", "PASSEPORT", "PL014"), ("Aliou", "Mbaye", "aliou.mbaye@email.sn", "+221 77 100 0015", "SN", "CIN", "SN015"), ("Rokhaya", "Fall", "rokhaya.fall@email.sn", "+221 76 100 0016", "SN", "CIN", "SN016"), ("David", "Johnson", "d.johnson@email.us", "+1 202 100 0017", "US", "PASSEPORT", "US017"), ("Sarah", "Wilson", "s.wilson@email.us", "+1 202 100 0018", "US", "PASSEPORT", "US018"), ("Abdoulaye", "Toure", "a.toure@email.sn", "+221 70 100 0019", "SN", "CIN", "SN019"), ("Ndeye", "Sarr", "ndeye.sarr@email.sn", "+221 77 100 0020", "SN", "CIN", "SN020"), ("Emmanuel", "Osei", "e.osei@email.gh", "+233 24 100 0021", "GH", "PASSEPORT", "GH021"), ("Yasmine", "Benali", "y.benali@email.ma", "+212 6 10 00 00 22", "MA", "PASSEPORT", "MA022"), ("Pierre", "Leblanc", "p.leblanc@email.fr", "+33 6 10 00 00 23", "FR", "PASSEPORT", "FR023"), ("Hawa", "Konate", "hawa.konate@email.ml", "+223 70 100 0024", "ML", "PASSEPORT", "ML024"), ("Ibrahima", "Gueye", "i.gueye@email.sn", "+221 77 100 0025", "SN", "CIN", "SN025"), ] result = [] created = 0 for first, last, email, phone, country, id_type, id_number in clients_data: existing_client = db.query(Client).filter(Client.id_number == id_number).first() if existing_client: result.append(existing_client) else: c = Client( first_name=first, last_name=last, email=email, phone=phone, country=country, nationality=country, id_type=id_type, id_number=id_number, is_active=True, vip=False, ) db.add(c) result.append(c) created += 1 if created: print(f" Created {created} clients") return result # --------------------------------------------------------------------------- # Reservations, Folios, FolioItems, Payments # --------------------------------------------------------------------------- def _seed_reservations_and_folios( db: Session, establishment, rooms: list, clients: list, admin, ) -> None: admin_id = admin.id if admin else None folio_counter = [db.query(Folio).count() + 1] conf_counter = [db.query(Reservation).count() + 1] def next_folio_number() -> str: n = folio_counter[0] folio_counter[0] += 1 return f"FOL-{n:05d}" def next_conf_number() -> str: n = conf_counter[0] conf_counter[0] += 1 return f"RES-SEED-{n:06d}" total_created = 0 for year in [2023, 2024, 2025]: # 2025: only Jan-Feb (2 months of data so far) max_month = 2 if year == 2025 else 12 actual_rev = ACTUAL_REVENUE_BY_YEAR[year] for month in range(1, max_month + 1): weight = MONTHLY_WEIGHTS[month] room_idx = ((year - 2023) * 12 + month - 1) % len(rooms) client_idx = ((year - 2023) * 12 + month - 1) % len(clients) room = rooms[room_idx] client = clients[client_idx] check_in = date(year, month, 1) if month == 12: check_out = date(year + 1, 1, 1) else: check_out = date(year, month + 1, 1) nights = (check_out - check_in).days heberg_monthly = round(actual_rev.get("HEBERG", 0) * weight) rate_per_night = round(heberg_monthly / nights) if nights > 0 else 0 conf_number = next_conf_number() # Skip if this confirmation already exists existing = db.query(Reservation).filter( Reservation.confirmation_number == conf_number ).first() if existing: continue # Ensure room and client have IDs if room.id is None or client.id is None: continue res = Reservation( client_id=client.id, room_id=room.id, check_in_date=check_in, check_out_date=check_out, actual_check_in=datetime(year, month, 1, 14, 0, 0), actual_check_out=datetime(check_out.year, check_out.month, check_out.day, 11, 0, 0), status=ReservationStatus.CHECKED_OUT, source=ReservationSource.DIRECT, adults=2, children=0, rate_per_night=float(rate_per_night), total_price=float(rate_per_night * nights), deposit_amount=0.0, currency="XOF", confirmation_number=conf_number, ) db.add(res) db.flush() folio_number = next_folio_number() folio = Folio( reservation_id=res.id, status=FolioStatus.CLOSED, folio_number=folio_number, total_charges=0.0, total_payments=0.0, balance=0.0, currency="XOF", closed_at=datetime(check_out.year, check_out.month, check_out.day, 12, 0, 0), closed_by_id=admin_id, ) db.add(folio) db.flush() total_charges = 0.0 ancillary_depts = [ "HEBERG", "REST", "BAR", "ROOM_SERVICE", "SPA", "BOUTIQUE", "PARKING", "MINIBAR", "MISC" ] for dept in ancillary_depts: dept_annual = actual_rev.get(dept) if not dept_annual: continue monthly_amount = round(dept_annual * weight) if monthly_amount <= 0: continue tax_rate = DEPT_TAX_RATE.get(dept, 0.10) total_ttc = monthly_amount total_ht = round(monthly_amount / (1 + tax_rate)) qty = float(nights) if dept == "HEBERG" else 1.0 unit_price = round(total_ht / nights) if dept == "HEBERG" and nights else float(total_ht) item = FolioItem( folio_id=folio.id, department_code=dept, description=DEPT_DESCRIPTIONS.get(dept, dept) + (f" — {nights} nuits" if dept == "HEBERG" else ""), quantity=qty, unit_price=float(unit_price), tax_rate=tax_rate, total_ht=float(total_ht), total_ttc=float(total_ttc), posted_by_id=admin_id, is_voided=False, created_at=datetime(year, month, 15, 10, 0, 0), updated_at=datetime(year, month, 15, 10, 0, 0), ) db.add(item) total_charges += total_ttc folio.total_charges = round(total_charges) folio.total_payments = round(total_charges) folio.balance = 0.0 payment = Payment( folio_id=folio.id, payment_method=PaymentMethod.BANK_TRANSFER, status=PaymentStatus.COMPLETED, amount=round(total_charges), currency="XOF", payment_date=datetime(check_out.year, check_out.month, check_out.day, 12, 30, 0), reference=f"PMT-{year}-{month:02d}", received_by_id=admin_id, ) db.add(payment) total_created += 1 db.flush() print(f" Year {year}: {max_month} monthly folios created") print(f" Total folios created: {total_created}")