
    Akj?                     *   d Z ddlZddlZddlZddlZddlmZ ddl	m
Z
 ddlmZ ddlmZmZmZ ddlmZ ddlmZ  ej(                  e      Zd	Zd
ZdZdZd Zd Zd Zd Zd Zd Z d Z!de"fdZ#de"fdZ$de"fdZ%d Z&d Z'dde"de"fdZ(de"fdZ)d Z*y)af  
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
    N)text)defaultdict)ZoneInfo)datetime	timedeltatimezone)settings)get_async_dbx   z   ){   |   }   )i  i  i  i   i!  i"  i#  i$  i%  i&  i'  i(  i)  i*  i+  i,  i-  i.  i/  i0  i1  i2  i3  i4  i5  c                     | sg S | j                  d      D cg c],  }|j                         st        |j                               . c}S c c}w )N,)splitstripint)	id_stringxs     C/var/www/html/ls-ibe-bot-apis-staging/app/services/predict_price.py	parse_idsr   #   s?    	$-OOC$8F$8qAGGIC	N$8FFFs
   AAc                 2    | y t        t        |       d      S )N   )roundfloat)vals    r   
safe_floatr   )   s    
{sQ    c                     | dv ry| dv ryy)N)      	   
      r   )         r'       )months    r   
get_seasonr,   /   s    !!1	*	A,-r   c                 l    | j                         | j                  | j                         dk\  rddS ddS )uS   Snapshot date (today) time features — excludes Week_of_Year (dropped from model).   r'   r   )Day_of_WeekMonth
Is_Weekend)weekdayr+   )date_objs    r   compute_time_featuresr4   5   sC      '')~~$,,.!3q  :; r   c                     | t        |      z   }|| j                         z   dz  }|j                  }t        |      }|||||z  dS )zGFeatures about the TARGET date being priced (snapshot_date + lead_day).daysr!   )
target_dowtarget_monthseasonseason_x_dow)r   r2   r+   r,   )snapshot_datelead_daytarget_dater8   r9   r:   s         r   compute_target_featuresr?   >   sY     9(#;;K}4466!;J$$Ll+F#%*,	 r   c                    K   t        d      }| j                  |||t        t        d       d {   }|j	                         S 7 w)Nz
        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
    )
start_dateend_datehotel_id
sub_hotelsr   executeHOTEL_ID
SUB_HOTELSfetchall)dbrA   rB   queryresults        r   fetch_occupancy_historyrM   P   sQ       	E ::e  	&  F ??s   .AAAc                    K   t        d      }| j                  |t        t        d       d {   }|j	                         S 7 w)Nz
        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
    )rC   rD   rE   )rJ   rK   rL   s      r   fetch_productsrO   a   sK       	E ::e &  F ??	s   ,AAAsub_hotel_idc                    K   t        d      }| j                  ||t        |d       d{   }|j                         D ci c]"  }|j                  t        |j                        $ c}S 7 @c c}w w)uN   Fetch today's actual prices — used as same_day_price anchor for ratio model.z
        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
    )todayrate_plan_idrP   N)r   rF   RATE_PLAN_IDrI   room_type_idr   room_type_price)rJ   rR   rP   rK   rL   rows         r   fetch_today_pricesrX   o   s}       	E ::e$$&  F
 EKOODUVDUSCeC$7$788DUVV
 Ws!   )A3A,A3'A.)A3.A3c                    K   t        d      }| j                  |||t        |d       d {   }|j                         S 7 w)Na+  
        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
    )rA   rB   rS   rP   )r   rF   rT   rI   rJ   rA   rB   rP   rK   rL   s         r   fetch_future_pricesr[      sQ       	E ::e" $$	&  F ??s   *AAAc                    K   t        d      }| j                  ||||d       d {   }|j                         S 7 w)Nae  
        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
    )rA   rB   rP   )r   rF   rI   rZ   s         r   fetch_stored_featuresr]      sN       	E ::e" $&  F
 ??s   %><>c                 V   t        d       }| D ]  }t        t        |j                              }t        t        |j                              }|D ]T  }|j                  |      }|s|j                  |j                  |f}||   dxx   dz  cc<   ||v sE||   dxx   dz  cc<   V  |S )Nc                      dddS )Nr   )totalbookedr*   r*   r   r   <lambda>z%build_daily_metrics.<locals>.<lambda>   s	    !q(Ar   r`   r'   ra   )r   setr   total_active_roomstotal_rentedgetdaterP   )	occ_rowsproduct_mapdaily_metricsrW   activerentedpid	room_typekeys	            r   build_daily_metricsrp      s     ABMYs5567Ys//01C#,I88S--y9C#w'1,'f}c"8,1,   r   c                 d    | j                  |||f      }|r|d   dk(  ry|d   }|d   }||||z  fS )Nr`   r   )r   r           ra   )rf   )rj   rg   	sub_hotelrn   datar`   ra   s          r   get_metricsru      sM    dIy9:D4=A%']E(^F&&5.((r   rU   c                 *   ,-.K   t         j                  dk(  rdnt         j                  dk(  rdnd}d| d}t        j                  | d      }t        j                  | d	      }t	        j
                  t        d
            j                         ..t        d      z
  }.t        d      z   }.t        d      z
  }t               4 d {   }	t        |	||       d {   }
t        |	       d {   }t        |	.        d {   }t        |	.|        d {   }t        |	||        d {   }d d d       d {    D ci c]  }|j                  |j                    }}t#        
|      ,t%        j&                  D cg c]  }t)        |j*                         c}      }|j,                  rt/        d  d      t%        j0                  |d         |d<   D ci c]9  }|j                  |j2                  |j                   ft5        |j6                        ; }}t9        d ||d    k(     d   j;                         D              }|r|t<        v r|gng }t>        jA                  d  d|        g }|D ]8  -jC                  -      }|st>        jA                  d  d- d       4||d   -k(  |d    k(  z     jE                  d      }|j,                  rt>        jA                  d- d       }|jF                  d   }tI        dd      D ]  }.t        |      z   }tK        ,| -      \  }}}|dk(  r6tM        |jC                  dd            }t5        |jC                  dd            },- .fd }tK        ,.t        d      z
   -      \  } } }!tK        ,.t        d!      z
   -      \  } } }"tK        ,.t        d"      z
   -      \  } } }#tK        ,.t        d      z
   -      \  } } }$|dk(  r6tM        |jC                  dd            }t5        |jC                  dd            } -|tO        |      tO        |      d#tQ        .      tS        .|       |d       |d$       |d!       |d"      tO        |!      tO        |"      tO        |#      tO        |$      d%}%t%        j&                  |%g      }&t%        jT                  |&dg&      }&|&jW                  |d'      }&t5        |jY                  |&      d         }'t[        |'|z  d(      }(t]        d)|(      }(|jC                  | -f      })|)rt[        |(|)z
  d(      nd }*t]        dt_        d*d*ta        |'d+z
        d*z  z
              }+|jc                  ||je                  d,      -||t[        |'d-      |)|(|*t[        |+d      d.
        ; t%        j&                  |      S 7 	7 7 7 7 7 7 # 1 d {  7  sw Y   xY wc c}w c c}w c c}w w)/N
productionzls-ibe-bot-apisstagingzls-ibe-bot-apis-stagingzhotel-projects/ls-ibe-bot-apisz/var/www/html/z/app/servicesz/xgb_price_model.pklz/feature_columns.pklzAmerica/New_Yorkr'   r6      <   z$No stored features for sub_hotel_id=.rg   c              3   0   K   | ]  }|t         v r|  y wN)VALID_ROOM_TYPES).0rts     r   	<genexpr>z"predict_30_days.<locals>.<genexpr>   s!      dr!! 	ds   rP   rU   zForecasting sub_hotel=z room types: zNo today price for sub_hotel=z room=u    — skipping.zNo history for room=r   total_inventoryOccupancy_Raterr   c                 L    t        fdt        d| dz         D              S )Nc              3   Z   K   | ]"  }t        t        |       z
        d    $ yw)r6   r'   N)ru   r   )r   drj   rt_idrP   rR   s     r   r   z8predict_30_days.<locals>.sum_bookings.<locals>.<genexpr>  s5      /  uya7H/H,X]^_`a/s   (+r'   )sumrange)r7   rj   r   rP   rR   s    r   sum_bookingsz%predict_30_days.<locals>.sum_bookings  s(     "1dQh/  r   r!      )rP   rU   r   r   same_day_price   )Bookings_Last_1_DayBookings_Last_3_DaysBookings_Last_7_DaysBookings_Last_14_DaysOccupancy_Lag_1Occupancy_Lag_7Occupancy_Lag_14Occupancy_Lag_30)columns)r   
fill_valuer)   g      I@d   g      ?z%Ar   )
DateDay	Room_TypeLead_DaySame_Day_PriceRatioActual_PricePredicted_PriceDiffConfidence_%)3r	   ENVjoblibloadr   nowr   rg   r   r
   rM   rO   rX   r[   r]   
product_idrU   rp   pd	DataFramedict_mappingempty
ValueErrorto_datetimerP   r   rV   sorteduniquer~   logwarningrf   sort_valuesilocr   ru   r   r   r4   r?   get_dummiesreindexpredictr   maxminabsappendstrftime)/rP   rU   dir_namebasemodelfeature_columns	yesterdayforecast_endhistory_startrJ   rh   product_rowstoday_pricesfuture_pricesstored_featurespri   rW   
history_dffpfuture_price_map
room_typesresultsr   	room_histlatestr=   	pred_dater`   ra   occ_rater   _lag_1lag_7lag_14lag_30feature_rowdf_inputpredicted_ratiopredicted_priceactual_pricediff
confidencerj   r   rR   s/   `                                           @@@r   predict_30_daysr      s    %-\\\%A%-\\Y%>!( 
 H:]3DkkTF*>"?@EkkTF*>"?@OLL*<!=>CCEEI1--II2..LI2..M~~ 7M< XX .r 22 22ul KK 3B|\ ZZ 5b-T` aa ~ <HH<aQ\\1>>1<KH'+>M _M_ctCLL1_MNJ?~QOPP
6(:;Jv
  B 
"//2??3U2;M;M5NN  
  
> :l JKN[bbd J
 '37G'Gl^R
KK(mJ<PQG &))%0KK7~VE7R`ab '50'<79
 +f
 	
 ??KK.ug^DE#aH	x 88I '2-LZ_&`#E68zvzz*;Q?@ ,<c!BC '}eiQ>O6OR^`efKAq%&}eiQ>O6OR^`efKAq%&}eiR>P6PR^`efLAq&&}eiR>P6PR^`efLAq&zvzz*;Q?@ ,<c!BC *6).).)3H)=)3N)C (. *%: *6a)5a)5a)5b)9)3E):)3E):)3F);)3F);K$ ||[M2H~~h8HIH''A'NH $EMM($;A$>?O#On$DaHO!$8O+//L%0PQL?K5<7;QUDQCc/C/0366! J NN#,#,#5#5d#;#(#+#1#(!#<#/#2#'#(Q#7 y %) z <<   X2KZa ~~~ I Ns   C
XWXW.%W&W.8W9W.W"W.#W%$W.9W(:W.>X	W+
XX0!XX	-AX.>X,N.XW.W."W.%W.(W.+X.X4W75X<Xc                   K   t        |        d{   }i }t        |d   j                               D ]  }||d   |k(     }|j                         D cg c]c  \  }}t	        |d         t        |d         t        j                  |d         rt        |d         ndt        |d         t        |d         d	e c}}|t        |      <    |S 7 c c}}w w)
zDReturns {room_type_id: [{date, actual, predicted, confidence}, ...]}rP   Nr   r   r   r   r   r   )rg   r=   actual	predictedr   )	r   r   r   iterrowsstrr   r   notnar   )rP   dfrL   r   room_dfr   rs          r   predict_30_days_datar   ]  s     L9	9BF;..01R_-.  ((*	
 +1 "!F)n!!J-0:<((1^CT:UeAn$56[_#A&7$89#An$56 +	
s5z 2 M 
:
	
s#   CCACA(C>CCc                    K   d} t        |        d{   }t        j                  t        j                        j                         }t        dd        t        d|         t        dt        j                         j                  d	              t        d
| d       t        d        t        |d   j                               D ]~  }||d   |k(     }|j                  d   d   }||d   j                            }d}t        |      rAt        j                  t        j                  |d   |d   z
  |d   z              dz  }d|dd}t        d| d|dd| d       t        ddddddd d!d"d d#d$d d%d$d d&d'd d(d)d d*d+       t        dd, dd- d d. d d/ d d/ d d0 d d1 d d2        |j!                         D ]  \  }	}
|
d   r	d3|
d   d4nd5d)}|
d(   |
d(   dk\  rd6nd d3|
d(   dnd7}t        dt#        |
d8         ddt%        |
d         dd |
d!   d"d |d$d9|
d   d4d |
d&   d:d |d)d |
d;   d<d         ||d   j                            }t        |      rft        j                  t        j                  |d   |d   z
  |d   z              dz  }t        dd        t        d=|dd       t        d d       |S 7 ĭw)>zCLI debug runner.r   r   N)tz
zZ==========================================================================================u&     30-Day Price Forecast — Sub Hotel z  Generated : z%Y-%m-%d %H:%Mz  Base Date : u     (lead_day 1–30)r   r   r   r    r   r   z	   MAPE: z.1f%u   
── Room Type z  (same_day_price: $z.2f)u    ──z  Leadz>4r   z<13 r   z<11Actualz>10	Predictedr   z>7r   z>9Confz>8u   ────u'   ─────────────u!   ───────────u   ──────────u   ───────u   ─────────u   ────────$z>8.2fzN/A+u   —r   z $z>7.4fr   z>7.1fz  Overall MAPE: )r   r   r   r   utcrg   printr   r   r   r   r   lennpmeanr   r   r   r   )rP   r   rR   r   r   same_daywith_actualmape_strmaper   r   
actual_strdiff_stroverall_mapes                 r   mainr  q  s    LL9	9BLLHLL)..0E	Bvh-	2<.
AB	N8<<>223CDE
FG	N5'!4
56	VH;..01R_-.<<?#34gn5;;=>{77N3kBS6TT$^45 6 D #4*A.H#E7*>xnAhZW^_`6"+Rs|1U3Kq#aTWGXXYZabdYeefgmnpfqqrsyz|r}~7)2hZq
!H:Qxj'RST[S\\]^e]fgh$$&DAq:;N:K1Q~.u56TYZ\S]JPQRXPYPeAfINS;1QvYsOLkpHS:'+2c!F)nS-A1U8C.PQc""Q'8%9%$@!G*UASSTU]^`Taabcdesctuzb{{|~ '# 22 R'--/0K
;wwFFK/+>O2PP 01 2
  	6(m c 2!45mIY 
:s   KKKKr}   )+__doc__r   loggingnumpyr   pandasr   
sqlalchemyr   collectionsr   zoneinfor   r   r   r   app.configs.settingsr	   app.models.connectionr
   	getLogger__name__r   rT   rG   rH   r~   r   r   r,   r4   r?   rM   rO   r   rX   r[   r]   rp   ru   r   r   r  r*   r   r   <module>r     s         #  2 2 ) .g!
 G .$"Wc W$c & 4 )O! O!3 O!dS (/r   