""" predict_prices.py (production) -------------------------------- Updated for ratio-target model: - Model predicts price_ratio = day_N_price / same_day_price - same_day_price = today's actual price for the room (lead_day=0) - Derived features: target_dow, target_month, season, season_x_dow - Booking_lead_days and Week_of_Year removed from features """ import joblib import logging import numpy as np import pandas as pd from sqlalchemy import text from collections import defaultdict from zoneinfo import ZoneInfo from datetime import datetime, timedelta, timezone from app.configs.settings import settings from app.models.connection import get_async_db log = logging.getLogger(__name__) RATE_PLAN_ID = 120 HOTEL_ID = 122 SUB_HOTELS = (123, 124, 125) VALID_ROOM_TYPES = ( 285, 286, 287, 288, 289, 290, 291, 292, 293, 294, 295, 296, 297, 298, 299, 300, 301, 302, 303, 304, 305, 306, 307, 308, 309 ) def parse_ids(id_string): if not id_string: return [] return [int(x.strip()) for x in id_string.split(",") if x.strip()] def safe_float(val): if val is None: return None return round(float(val), 4) def get_season(month): if month in [7, 8, 9, 10, 11]: return 0 # off-peak elif month in [12, 1, 6]: return 1 # shoulder else: return 2 # peak (Feb–May) def compute_time_features(date_obj): """Snapshot date (today) time features — excludes Week_of_Year (dropped from model).""" return { "Day_of_Week": date_obj.weekday(), "Month": date_obj.month, "Is_Weekend": 1 if date_obj.weekday() >= 5 else 0, } def compute_target_features(snapshot_date, lead_day): """Features about the TARGET date being priced (snapshot_date + lead_day).""" target_date = snapshot_date + timedelta(days=lead_day) target_dow = (lead_day + snapshot_date.weekday()) % 7 target_month = target_date.month season = get_season(target_month) return { "target_dow": target_dow, "target_month": target_month, "season": season, "season_x_dow": season * target_dow, } # ───────────────────────────────────────────── # DB FETCHERS # ───────────────────────────────────────────── async def fetch_occupancy_history(db, start_date, end_date): query = text(""" SELECT date, sub_hotel_id, total_active_rooms, total_rented FROM hotel_future_occupancies WHERE date BETWEEN :start_date AND :end_date AND hotel_id = :hotel_id AND sub_hotel_id IN :sub_hotels """) result = await db.execute(query, { "start_date": start_date, "end_date": end_date, "hotel_id": HOTEL_ID, "sub_hotels": SUB_HOTELS, }) return result.fetchall() async def fetch_products(db): query = text(""" SELECT product_id, sub_hotel_id, type_id AS room_type_id FROM products WHERE hotel_id = :hotel_id AND sub_hotel_id IN :sub_hotels """) result = await db.execute(query, { "hotel_id": HOTEL_ID, "sub_hotels": SUB_HOTELS, }) return result.fetchall() async def fetch_today_prices(db, today, sub_hotel_id: int): """Fetch today's actual prices — used as same_day_price anchor for ratio model.""" query = text(""" SELECT room_type_id, room_type_price FROM hotel_price_122 WHERE date = :today AND rate_plan_id = :rate_plan_id AND channel_id = 1 AND sub_hotel_id = :sub_hotel_id """) result = await db.execute(query, { "today": today, "rate_plan_id": RATE_PLAN_ID, "sub_hotel_id": sub_hotel_id, }) return {row.room_type_id: float(row.room_type_price) for row in result.fetchall()} async def fetch_future_prices(db, start_date, end_date, sub_hotel_id: int): query = text(""" SELECT date, sub_hotel_id, room_type_id, room_type_price FROM hotel_price_122 WHERE date BETWEEN :start_date AND :end_date AND rate_plan_id = :rate_plan_id AND channel_id = 1 AND sub_hotel_id = :sub_hotel_id ORDER BY date, room_type_id """) result = await db.execute(query, { "start_date": start_date, "end_date": end_date, "rate_plan_id": RATE_PLAN_ID, "sub_hotel_id": sub_hotel_id, }) return result.fetchall() async def fetch_stored_features(db, start_date, end_date, sub_hotel_id: int): query = text(""" SELECT f.date, f.sub_hotel_id, f.room_type_id, f.room_type_price, f.total_inventory, f.booked_inventory, f.Occupancy_Rate, f.Day_of_Week, f.Month, f.Week_of_Year, f.Is_Weekend, f.Bookings_Last_1_Day, f.Bookings_Last_3_Days, f.Bookings_Last_7_Days, f.Bookings_Last_14_Days, f.Occupancy_Lag_1, f.Occupancy_Lag_7, f.Occupancy_Lag_14, f.Occupancy_Lag_30 FROM daily_room_type_features f WHERE f.date BETWEEN :start_date AND :end_date AND f.sub_hotel_id = :sub_hotel_id ORDER BY f.room_type_id, f.date """) result = await db.execute(query, { "start_date": start_date, "end_date": end_date, "sub_hotel_id": sub_hotel_id, }) return result.fetchall() # ───────────────────────────────────────────── # FEATURE HELPERS # ───────────────────────────────────────────── def build_daily_metrics(occ_rows, product_map): daily_metrics = defaultdict(lambda: {"total": 0, "booked": 0}) for row in occ_rows: active = set(parse_ids(row.total_active_rooms)) rented = set(parse_ids(row.total_rented)) for pid in active: room_type = product_map.get(pid) if not room_type: continue key = (row.date, row.sub_hotel_id, room_type) daily_metrics[key]["total"] += 1 if pid in rented: daily_metrics[key]["booked"] += 1 return daily_metrics def get_metrics(daily_metrics, date, sub_hotel, room_type): data = daily_metrics.get((date, sub_hotel, room_type)) if not data or data["total"] == 0: return 0, 0, 0.0 total = data["total"] booked = data["booked"] return total, booked, booked / total # ───────────────────────────────────────────── # MAIN FORECAST # ───────────────────────────────────────────── async def predict_30_days(sub_hotel_id: int, room_type_id: int = None): dir_name = ( "ls-ibe-bot-apis" if settings.ENV == "production" else "ls-ibe-bot-apis-staging" if settings.ENV == "staging" else "hotel-projects/ls-ibe-bot-apis" ) base = f"/var/www/html/{dir_name}/app/services" model = joblib.load(f"{base}/xgb_price_model.pkl") feature_columns = joblib.load(f"{base}/feature_columns.pkl") today = datetime.now(ZoneInfo("America/New_York")).date() yesterday = today - timedelta(days=1) forecast_end = today + timedelta(days=30) history_start = today - timedelta(days=60) async with get_async_db() as db: occ_rows = await fetch_occupancy_history(db, history_start, forecast_end) product_rows = await fetch_products(db) today_prices = await fetch_today_prices(db, today, sub_hotel_id) future_prices = await fetch_future_prices(db, today, forecast_end, sub_hotel_id) stored_features = await fetch_stored_features(db, history_start, yesterday, sub_hotel_id) product_map = {p.product_id: p.room_type_id for p in product_rows} daily_metrics = build_daily_metrics(occ_rows, product_map) # History DataFrame for occupancy lag features history_df = pd.DataFrame([dict(row._mapping) for row in stored_features]) if history_df.empty: raise ValueError(f"No stored features for sub_hotel_id={sub_hotel_id}.") history_df["date"] = pd.to_datetime(history_df["date"]) # Future price map for actual price comparison future_price_map = { (fp.date, fp.sub_hotel_id, fp.room_type_id): float(fp.room_type_price) for fp in future_prices } room_types = sorted( rt for rt in history_df[history_df["sub_hotel_id"] == sub_hotel_id]["room_type_id"].unique() if rt in VALID_ROOM_TYPES ) if room_type_id: room_types = [room_type_id] if room_type_id in VALID_ROOM_TYPES else [] log.warning(f"Forecasting sub_hotel={sub_hotel_id} room types: {room_types}") results = [] for rt_id in room_types: # same_day_price anchor — today's actual price for this room same_day_price = today_prices.get(rt_id) if not same_day_price: log.warning(f"No today price for sub_hotel={sub_hotel_id} room={rt_id} — skipping.") continue # Latest stored occupancy features for this room (use most recent row) room_hist = history_df[ (history_df["room_type_id"] == rt_id) & (history_df["sub_hotel_id"] == sub_hotel_id) ].sort_values("date") if room_hist.empty: log.warning(f"No history for room={rt_id} — skipping.") continue latest = room_hist.iloc[-1] for lead_day in range(0, 30): # lead_day 1–30 (0 = today, already known) pred_date = today + timedelta(days=lead_day) # Occupancy features from today's snapshot (fixed for all lead days) total, booked, occ_rate = get_metrics(daily_metrics, pred_date, sub_hotel_id, rt_id) if total == 0: total = int(latest.get("total_inventory", 1)) occ_rate = float(latest.get("Occupancy_Rate", 0.0)) def sum_bookings(days): return sum( get_metrics(daily_metrics, today - timedelta(days=d), sub_hotel_id, rt_id)[1] for d in range(1, days + 1) ) _, _, lag_1 = get_metrics(daily_metrics, today - timedelta(days=1), sub_hotel_id, rt_id) _, _, lag_7 = get_metrics(daily_metrics, today - timedelta(days=7), sub_hotel_id, rt_id) _, _, lag_14 = get_metrics(daily_metrics, today - timedelta(days=14), sub_hotel_id, rt_id) _, _, lag_30 = get_metrics(daily_metrics, today - timedelta(days=30), sub_hotel_id, rt_id) if total == 0: total = int(latest.get("total_inventory", 1)) occ_rate = float(latest.get("Occupancy_Rate", 0.0)) # Feature row — matches train.py feature set exactly feature_row = { "sub_hotel_id": sub_hotel_id, "room_type_id": rt_id, "total_inventory": total, "Occupancy_Rate": safe_float(occ_rate), "same_day_price": safe_float(same_day_price), **compute_time_features(today), # Day_of_Week, Month, Is_Weekend **compute_target_features(today, lead_day), # target_dow, target_month, season, season_x_dow "Bookings_Last_1_Day": sum_bookings(1), "Bookings_Last_3_Days": sum_bookings(3), "Bookings_Last_7_Days": sum_bookings(7), "Bookings_Last_14_Days": sum_bookings(14), "Occupancy_Lag_1": safe_float(lag_1), "Occupancy_Lag_7": safe_float(lag_7), "Occupancy_Lag_14": safe_float(lag_14), "Occupancy_Lag_30": safe_float(lag_30), } df_input = pd.DataFrame([feature_row]) df_input = pd.get_dummies(df_input, columns=["room_type_id"]) df_input = df_input.reindex(columns=feature_columns, fill_value=0) # Model predicts ratio — multiply by same_day_price for absolute price predicted_ratio = float(model.predict(df_input)[0]) predicted_price = round(predicted_ratio * same_day_price, 2) predicted_price = max(50.0, predicted_price) actual_price = future_price_map.get((pred_date, sub_hotel_id, rt_id)) diff = round(predicted_price - actual_price, 2) if actual_price else None confidence = max(0, min(100, 100 - abs(predicted_ratio - 1.0) * 100 )) results.append({ "Date": pred_date, "Day": pred_date.strftime("%A"), "Room_Type": rt_id, "Lead_Day": lead_day, "Same_Day_Price": same_day_price, "Ratio": round(predicted_ratio, 4), "Actual_Price": actual_price, "Predicted_Price": predicted_price, "Diff": diff, "Confidence_%": round(confidence, 1), }) return pd.DataFrame(results) async def predict_30_days_data(sub_hotel_id: int): """Returns {room_type_id: [{date, actual, predicted, confidence}, ...]}""" df = await predict_30_days(sub_hotel_id=sub_hotel_id) result = {} for rt_id in sorted(df["Room_Type"].unique()): room_df = df[df["Room_Type"] == rt_id] result[int(rt_id)] = [ { "date": str(r["Date"]), "lead_day": int(r["Lead_Day"]), "actual": float(r["Actual_Price"]) if pd.notna(r["Actual_Price"]) else None, "predicted": float(r["Predicted_Price"]), "confidence": float(r["Confidence_%"]), } for _, r in room_df.iterrows() ] return result async def main(): """CLI debug runner.""" sub_hotel_id = 125 df = await predict_30_days(sub_hotel_id=sub_hotel_id) today = datetime.now(tz=timezone.utc).date() print(f"\n{'='*90}") print(f" 30-Day Price Forecast — Sub Hotel {sub_hotel_id}") print(f" Generated : {datetime.now().strftime('%Y-%m-%d %H:%M')}") print(f" Base Date : {today} (lead_day 1–30)") print(f"{'='*90}") for rt_id in sorted(df["Room_Type"].unique()): room_df = df[df["Room_Type"] == rt_id] same_day = room_df.iloc[0]["Same_Day_Price"] with_actual = room_df[room_df["Actual_Price"].notna()] mape_str = "" if len(with_actual): mape = np.mean( np.abs((with_actual["Actual_Price"] - with_actual["Predicted_Price"]) / with_actual["Actual_Price"]) ) * 100 mape_str = f" MAPE: {mape:.1f}%" print(f"\n── Room Type {rt_id} (same_day_price: ${same_day:.2f}){mape_str} ──") print(f" {'Lead':>4} {'Date':<13} {'Day':<11} {'Actual':>10} {'Predicted':>10} {'Ratio':>7} {'Diff':>9} {'Conf':>8}") print(f" {'─'*4} {'─'*13} {'─'*11} {'─'*10} {'─'*10} {'─'*7} {'─'*9} {'─'*8}") for _, r in room_df.iterrows(): actual_str = f"${r['Actual_Price']:>8.2f}" if r["Actual_Price"] else f"{'N/A':>9}" diff_str = (f"{'+' if r['Diff'] >= 0 else ''}${r['Diff']:.1f}" if r["Diff"] is not None else "—") print( f" {int(r['Lead_Day']):>4} {str(r['Date']):<13} {r['Day']:<11} " f"{actual_str:>10} ${r['Predicted_Price']:>8.2f} {r['Ratio']:>7.4f} {diff_str:>9} {r['Confidence_%']:>7.1f}%" ) with_actual = df[df["Actual_Price"].notna()] if len(with_actual): overall_mape = np.mean( np.abs((with_actual["Actual_Price"] - with_actual["Predicted_Price"]) / with_actual["Actual_Price"]) ) * 100 print(f"\n{'='*90}") print(f" Overall MAPE: {overall_mape:.1f}%") print(f"{'='*90}\n") return df