# import logging # import pandas as pd # import numpy as np # import joblib # from datetime import datetime, timedelta, timezone # from sqlalchemy import text # from app.models.connection import get_async_db # from collections import defaultdict # log = logging.getLogger(__name__) # RATE_PLAN_ID = 120 # HOTEL_ID = 122 # SUB_HOTELS = (123, 124, 125) # TARGET_SUB_HOTEL = 125 # model trained on hotel_125 data only # 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 compute_time_features(date_obj): # return { # "Day_of_Week": date_obj.weekday(), # "Month": date_obj.month, # "Week_of_Year": date_obj.isocalendar()[1], # "Is_Weekend": 1 if date_obj.weekday() >= 5 else 0 # } # # ───────────────────────────────────────────── # # DB FETCHERS # # ───────────────────────────────────────────── # async def fetch_occupancy_history(db, start_date, end_date): # """ # Raw occupancy from hotel_future_occupancies. # total_active_rooms and total_rented are comma-separated product_id strings. # This table has today's data. # Example row: # sub_hotel_id=125 # total_active_rooms = "1125,1126,1127,1124,1128,..." # total_rented = "1132,1136,1135,1133,..." # """ # 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): # """ # Maps product_id → room_type_id (type_id) per sub_hotel. # Example: # product_id=1125, sub_hotel_id=125, type_id=293 # product_id=1126, sub_hotel_id=125, type_id=293 # """ # 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_future_prices(db, start_date, end_date): # query = text(""" # SELECT # p.date, p.sub_hotel_id, p.room_type_id, p.room_type_price, # r.room_type_price AS rack_price # FROM hotel_price_122 p # LEFT JOIN hotel_price_122 r # ON r.date = p.date # AND r.room_type_id = p.room_type_id # AND r.sub_hotel_id = p.sub_hotel_id # AND r.rate_plan_id = 124 # AND r.channel_id = 1 # WHERE p.date BETWEEN :start_date AND :end_date # AND p.rate_plan_id = :rate_plan_id # AND p.channel_id = 1 # AND p.sub_hotel_id = :sub_hotel_id # ORDER BY p.date, p.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": TARGET_SUB_HOTEL # }) # return result.fetchall() # async def fetch_stored_features(db, start_date, end_date): # 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, # r.room_type_price AS rack_price # FROM daily_room_type_features f # LEFT JOIN hotel_price_122 r # ON r.date = f.date # AND r.room_type_id = f.room_type_id # AND r.sub_hotel_id = f.sub_hotel_id # AND r.rate_plan_id = 124 # AND r.channel_id = 1 # 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": TARGET_SUB_HOTEL # }) # return result.fetchall() # # ───────────────────────────────────────────── # # FEATURE HELPERS # # ───────────────────────────────────────────── # def build_daily_metrics(occ_rows, product_map): # """ # Converts raw occupancy rows into: # (date, sub_hotel_id, room_type_id) → {total, booked} # product_map: product_id → room_type_id # Each occ row has comma-separated product_ids in total_active_rooms / total_rented. # We count how many products of each room_type are active/booked per day. # """ # 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): # """Returns (total, booked, occupancy_rate) for a given key""" # 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 # def compute_rolling_features(rolling_history, room_type_id, prediction_date): # room_hist = rolling_history[ # (rolling_history['room_type_id'] == room_type_id) & # (rolling_history['date'] < pd.Timestamp(prediction_date)) # ].sort_values('date') # past_prices = room_hist['room_type_price'].astype(float) # past_occupancy = room_hist['Occupancy_Rate'].astype(float) # past_rack = room_hist['rack_price'].astype(float) # ← NEW # def rolling_mean(series, n): # if len(series) == 0: # return 0.0 # return float(series.iloc[-n:].mean()) if len(series) >= n else float(series.mean()) # rolling_7d_price = rolling_mean(past_prices, 7) # rolling_14d_price = rolling_mean(past_prices, 14) # rolling_30d_price = rolling_mean(past_prices, 30) # rolling_7d_occupancy = rolling_mean(past_occupancy, 7) # rolling_14d_occupancy = rolling_mean(past_occupancy, 14) # rolling_7d_rack = rolling_mean(past_rack, 7) # ← NEW # rolling_14d_rack = rolling_mean(past_rack, 14) # ← NEW # price_1d_ago = float(past_prices.iloc[-1]) if len(past_prices) >= 1 else np.nan # price_2d_ago = float(past_prices.iloc[-2]) if len(past_prices) >= 2 else np.nan # price_7d_ago = float(past_prices.iloc[-7]) if len(past_prices) >= 7 else np.nan # rack_1d_ago = float(past_rack.iloc[-1]) if len(past_rack) >= 1 else np.nan # ← NEW # rack_2d_ago = float(past_rack.iloc[-2]) if len(past_rack) >= 2 else np.nan # ← NEW # rack_7d_ago = float(past_rack.iloc[-7]) if len(past_rack) >= 7 else np.nan # ← NEW # price_change_1d = ( # float(price_1d_ago / price_2d_ago - 1) # if (not np.isnan(price_2d_ago) and price_2d_ago != 0) else 0.0 # ) # price_change_7d = ( # float(price_1d_ago / price_7d_ago - 1) # if (not np.isnan(price_7d_ago) and price_7d_ago != 0) else 0.0 # ) # # ── NEW: rack momentum ────────────────────────────────────────────────── # rack_change_1d = ( # float(rack_1d_ago / rack_2d_ago - 1) # if (not np.isnan(rack_2d_ago) and rack_2d_ago != 0) else 0.0 # ) # rack_change_7d = ( # float(rack_1d_ago / rack_7d_ago - 1) # if (not np.isnan(rack_7d_ago) and rack_7d_ago != 0) else 0.0 # ) # # ─────────────────────────────────────────────────────────────────────── # return { # "Rolling_7d_price": rolling_7d_price, # "Rolling_14d_price": rolling_14d_price, # "Rolling_30d_price": rolling_30d_price, # "Rolling_7d_occupancy": rolling_7d_occupancy, # "Rolling_14d_occupancy": rolling_14d_occupancy, # "Rolling_7d_rack": rolling_7d_rack, # ← NEW # "Rolling_14d_rack": rolling_14d_rack, # ← NEW # "Price_Change_1d": price_change_1d, # "Price_Change_7d": price_change_7d, # "Rack_Change_1d": rack_change_1d, # ← NEW # "Rack_Change_7d": rack_change_7d, # ← NEW # "Occupancy_vs_7d_avg": 0.0, # } # # ───────────────────────────────────────────── # # MAIN FORECAST # # ───────────────────────────────────────────── # async def predict_30_days(room_type_id: int = None): # model = joblib.load("/var/www/html/ls-bot-apis/app/services/xgb_price_model.pkl") # feature_columns = joblib.load("/var/www/html/ls-bot-apis/app/services/feature_columns.pkl") # today = datetime.now(tz=timezone.utc).date() # yesterday = today - timedelta(days=1) # forecast_end = today + timedelta(days=29) # history_start = today - timedelta(days=60) # async with get_async_db() as db: # # raw occupancy up to today (has today's data) # occ_rows = await fetch_occupancy_history(db, history_start, today) # # product_id → room_type_id mapping # product_rows = await fetch_products(db) # # actual prices from hotel_price_122 for next 30 days # future_prices = await fetch_future_prices(db, today, forecast_end) # # stored features up to yesterday (table is 1 day behind) # stored_features = await fetch_stored_features(db, history_start, yesterday) # # Build maps # product_map = {p.product_id: p.room_type_id for p in product_rows} # daily_metrics = build_daily_metrics(occ_rows, product_map) # # Debug: confirm sub_hotel_125 room types found # sub125_room_types = set( # rt for (_, sh, rt) in daily_metrics.keys() # if sh == TARGET_SUB_HOTEL # ) # log.warning(f"Room types found for sub_hotel {TARGET_SUB_HOTEL}: {sorted(sub125_room_types)}") # # History DataFrame from daily_room_type_features (up to yesterday) # history_df = pd.DataFrame([dict(row._mapping) for row in stored_features]) # if history_df.empty: # raise ValueError( # f"No stored features found for sub_hotel_id={TARGET_SUB_HOTEL}. " # f"Check daily_room_type_features table." # ) # history_df['date'] = pd.to_datetime(history_df['date']) # # Future price map: (date, sub_hotel_id, room_type_id) → price # # Future price map — store plan price and rack price separately # future_price_map = {} # for fp in future_prices: # future_price_map[(fp.date, fp.sub_hotel_id, fp.room_type_id)] = float(fp.room_type_price) # future_price_map[(fp.date, fp.sub_hotel_id, fp.room_type_id, 'rack')] = float(fp.rack_price) if fp.rack_price else None # # Room types to forecast # room_types = sorted( # history_df[history_df['sub_hotel_id'] == TARGET_SUB_HOTEL]['room_type_id'].unique() # ) # if room_type_id: # room_types = [room_type_id] # log.warning(f"Forecasting for room types: {room_types}") # results = [] # for rt_id in room_types: # # Per-room rolling history starts from stored features (up to yesterday) # rolling_history = history_df[ # (history_df['room_type_id'] == rt_id) & # (history_df['sub_hotel_id'] == TARGET_SUB_HOTEL) # ].sort_values('date').copy() # for day_offset in range(30): # pred_date = today + timedelta(days=day_offset) # # ── Occupancy ── # total, booked, occ_rate = get_metrics( # daily_metrics, pred_date, TARGET_SUB_HOTEL, rt_id # ) # # ── Booking aggregates ── # def sum_bookings(days): # return sum( # get_metrics(daily_metrics, pred_date - timedelta(days=d), TARGET_SUB_HOTEL, rt_id)[1] # for d in range(1, days + 1) # ) # # ── Occupancy lags ── # _, _, lag_1 = get_metrics(daily_metrics, pred_date - timedelta(days=1), TARGET_SUB_HOTEL, rt_id) # _, _, lag_7 = get_metrics(daily_metrics, pred_date - timedelta(days=7), TARGET_SUB_HOTEL, rt_id) # _, _, lag_14 = get_metrics(daily_metrics, pred_date - timedelta(days=14), TARGET_SUB_HOTEL, rt_id) # _, _, lag_30 = get_metrics(daily_metrics, pred_date - timedelta(days=30), TARGET_SUB_HOTEL, rt_id) # # Future dates won't have occupancy — use same day last week as proxy # if total == 0: # occ_rate = ( # 0.6 * lag_7 + # 0.3 * lag_1 + # 0.1 * lag_14 # ) # total = 1 # # ── Rolling + momentum (no leakage) ── # rolling = compute_rolling_features(rolling_history, rt_id, pred_date) # rolling['Occupancy_vs_7d_avg'] = occ_rate - rolling['Rolling_7d_occupancy'] # # ── Actual price from hotel_price_122 ── # actual_future_price = future_price_map.get( # (pred_date, TARGET_SUB_HOTEL, rt_id) # ) # # ── Rack price for this prediction date ────────────────────── # rack_price = future_price_map.get( # (pred_date, TARGET_SUB_HOTEL, rt_id, 'rack') # see map update below # ) or rolling['Rolling_7d_rack'] # fallback to rolling avg if missing # # ──────────────────────────────────────────────────────────── # # ── Build feature row ── # feature_row = { # "room_type_id": rt_id, # "total_inventory": total, # "booked_inventory": booked, # "Occupancy_Rate": safe_float(occ_rate), # **compute_time_features(pred_date), # "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), # "rack_price": safe_float(rack_price), # ← NEW # "Price_to_Rack_Ratio": safe_float(rack_price and rolling['Rolling_7d_price'] / rack_price), # ← NEW # "Price_vs_Rack_Gap": safe_float(rack_price and rolling['Rolling_7d_price'] - rack_price), # ← NEW # **rolling, # } # # ── Predict ── # 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) # predicted_price = float(model.predict(df_input)[0]) # predicted_price = max(50, predicted_price) # min price guard # # optional cap # predicted_price = min(predicted_price, rolling['Rolling_30d_price'] * 2) # # ── Confidence ── # confidence = max(0, min(100, # 100 - (abs(predicted_price - rolling['Rolling_7d_price']) / # max(rolling['Rolling_7d_price'], 1)) * 100 # )) # diff = round(predicted_price - actual_future_price, 2) if actual_future_price else None # results.append({ # "Date": pred_date, # "Day": pred_date.strftime("%A"), # "Room_Type": rt_id, # "Actual_Price": actual_future_price, # "Predicted_Price": round(predicted_price, 2), # "Diff": diff, # "Confidence_%": confidence, # }) # # ── Autoregressive: feed prediction back for next day's rolling ── # new_row = pd.DataFrame([{ # "date": pd.Timestamp(pred_date), # "room_type_id": rt_id, # "sub_hotel_id": TARGET_SUB_HOTEL, # "room_type_price": predicted_price, # "Occupancy_Rate": float(occ_rate), # "rack_price": float(rack_price), # ← NEW # }]) # rolling_history = pd.concat([rolling_history, new_row], ignore_index=True) # return pd.DataFrame(results) # async def predict_30_days_data(): # df = await predict_30_days() # result = {} # for rt_id in sorted(df['Room_Type'].unique()): # room_df = df[df['Room_Type'] == rt_id] # room_list = [] # for _, r in room_df.iterrows(): # room_list.append({ # "date": str(r["Date"]), # "actual": float(r["Actual_Price"]) if pd.notna(r["Actual_Price"]) else None, # "predicted": float(r["Predicted_Price"]), # "confidence": float(r["Confidence_%"]) # }) # result[int(rt_id)] = room_list # return result # async def main(): # df = await predict_30_days() # today = datetime.now(tz=timezone.utc).date() # print(f"\n{'='*85}") # print(f" 30-Day Price Forecast — Sub Hotel {TARGET_SUB_HOTEL}") # print(f" Generated : {datetime.now().strftime('%Y-%m-%d %H:%M')}") # print(f" Period : {today} → {today + timedelta(days=29)}") # print(f"{'='*85}") # for rt_id in sorted(df['Room_Type'].unique()): # room_df = df[df['Room_Type'] == rt_id] # 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}{mape_str} ──") # print(f" {'Date':<13} {'Day':<11} {'Actual':>10} {'Predicted':>10} {'Diff':>9} {'Confidence':>11}") # print(f" {'─'*13} {'─'*11} {'─'*10} {'─'*10} {'─'*9} {'─'*11}") # for _, r in room_df.iterrows(): # actual_str = f"₹{r['Actual_Price']:>8.2f}" if r['Actual_Price'] else f"{'N/A':>9}" # if r['Diff'] is not None: # sign = '+' if r['Diff'] >= 0 else '' # diff_str = f"{sign}₹{r['Diff']:.1f}" # else: # diff_str = "—" # print( # f" {str(r['Date']):<13} " # f"{r['Day']:<11} " # f"{actual_str:>10} " # f"₹{r['Predicted_Price']:>8.2f} " # f"{diff_str:>9} " # f"{r['Confidence_%']:>9.1f}%" # ) # # Overall MAPE # 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{'='*85}") # print(f" Overall MAPE vs actual future prices : {overall_mape:.1f}%") # print(f"{'='*85}\n") # return df