
    iP                         d dl Z d dlZd dlZd dlZd dlmZmZmZ d dl	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 Zd Zd Zdde fdZ!d Z"d Z#dS )    N)datetime	timedeltatimezone)text)get_async_db)defaultdictx   z   ){   |   }   r   c                 H    | sg S d |                      d          D             S )Nc                 x    g | ]7}|                                 t          |                                           8S  )stripint).0xs     ;/var/www/html/ls-ibe-bot-apis/app/services/predict_price.py
<listcomp>zparse_ids.<locals>.<listcomp>   s3    FFFqAGGIIFC		NNFFF    ,)split)	id_strings    r   	parse_idsr      s/     	FFIOOC$8$8FFFFr   c                 D    | d S t          t          |           d          S )N   )roundfloat)vals    r   
safe_floatr!      s"    
{tsQr   c                     |                                  | j        |                                 d         |                                  dk    rdnddS )N      r   )Day_of_WeekMonthWeek_of_Year
Is_Weekend)weekdaymonthisocalendar)date_objs    r   compute_time_featuresr-      sV    ''))~ ,,..q1$,,..!33qq	  r   c                    K   t          d          }|                     |||t          t          d           d{V }|                                S )a>  
    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,..."
    a  
        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_hotelsNr   executeHOTEL_ID
SUB_HOTELSfetchalldbr/   r0   queryresults        r   fetch_occupancy_historyr<   '   sy        
 
	 
	E ::e  	& &        F ??r   c                    K   t          d          }|                     |t          t          d           d{V }|                                S )u   
    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
    z
        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
    )r1   r2   Nr3   )r9   r:   r;   s      r   fetch_productsr>   D   ss         	 	E ::e & &        F ??r   c                    K   t          d          }|                     |||t          t          d           d {V }|                                S )Nao  
        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
    )r/   r0   rate_plan_idsub_hotel_id)r   r4   RATE_PLAN_IDTARGET_SUB_HOTELr7   r8   s        r   fetch_future_pricesrD   W   sw        	 	E" ::e" $(	& &        F ??r   c                    K   t          d          }|                     |||t          d           d {V }|                                S )Na  
        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
    )r/   r0   rA   )r   r4   rC   r7   r8   s        r   fetch_stored_featuresrF   q   st        	 	E( ::e" (& &        F
 ??r   c                 l   t          d           }| D ]}t          t          |j                            }t          t          |j                            }|D ]Y}|                    |          }|s|j        |j        |f}||         dxx         dz  cc<   ||v r||         dxx         dz  cc<   Z|S )u9  
    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.
    c                      dddS )Nr   )totalbookedr   r   r   r   <lambda>z%build_daily_metrics.<locals>.<lambda>   s    !q(A(A r   rI   r#   rJ   )r   setr   total_active_roomstotal_rentedgetdaterA   )	occ_rowsproduct_mapdaily_metricsrowactiverentedpid	room_typekeys	            r   build_daily_metricsrZ      s       A ABBM 2 2Ys56677Ys/0011 	2 	2C#,,I 8S-y9C#w'''1,'''f}}c"8,,,1,,,	2 r   c                     |                      |||f          }|r|d         dk    rdS |d         }|d         }||||z  fS )z7Returns (total, booked, occupancy_rate) for a given keyrI   r   )r   r           rJ   )rO   )rS   rP   	sub_hotelrX   datarI   rJ   s          r   get_metricsr_      sZ    dIy9::D 4=A%%y']E(^F&&5.((r   c                 J   | | d         |k    | d         t          j        |          k     z                               d          }|d                             t                    }|d                             t                    }|d                             t                    }d } ||d          } ||d          }	 ||d	          }
 ||d          } ||d          } ||d          } ||d          }t          |          d
k    rt	          |j        d                   nt          j        }t          |          dk    rt	          |j        d                   nt          j        }t          |          dk    rt	          |j        d                   nt          j        }t          |          d
k    rt	          |j        d                   nt          j        }t          |          dk    rt	          |j        d                   nt          j        }t          |          dk    rt	          |j        d                   nt          j        }t          j	        |          s|dk    rt	          ||z  d
z
            nd}t          j	        |          s|dk    rt	          ||z  d
z
            nd}t          j	        |          s|dk    rt	          ||z  d
z
            nd}t          j	        |          s|dk    rt	          ||z  d
z
            nd}||	|
||||||||ddS )Nroom_type_idrP   room_type_priceOccupancy_Rate
rack_pricec                     t          |           dk    rdS t          |           |k    r/t          | j        | d                                                    n t          |                                           S )Nr   r\   )lenr   ilocmean)seriesns     r   rolling_meanz.compute_rolling_features.<locals>.rolling_mean   sd    v;;!314V1A1AuV[!%**,,---uV[[]]G[G[[r            r#      ir   r\   )Rolling_7d_priceRolling_14d_priceRolling_30d_priceRolling_7d_occupancyRolling_14d_occupancyRolling_7d_rackRolling_14d_rackPrice_Change_1dPrice_Change_7dRack_Change_1dRack_Change_7dOccupancy_vs_7d_avg)
pd	Timestampsort_valuesastyper   rf   rg   npnanisnan)rolling_historyra   prediction_date	room_histpast_pricespast_occupancy	past_rackrk   rolling_7d_pricerolling_14d_pricerolling_30d_pricerolling_7d_occupancyrolling_14d_occupancyrolling_7d_rackrolling_14d_rackprice_1d_agoprice_2d_agoprice_7d_agorack_1d_agorack_2d_agorack_7d_agoprice_change_1dprice_change_7drack_change_1drack_change_7ds                            r   compute_rolling_featuresr      sm   		(L	8		 2<#@#@	@	B k& 
 0188??K/077>>N|,33E::I\ \ \
 )La88(Lb99(Lb99(L;;(L<<(LA66O(LB7725k2B2Ba2G2G5)"-...RVL25k2B2Ba2G2G5)"-...RVL25k2B2Ba2G2G5)"-...RVL14Y11D1D5+,,,"&K14Y11D1D5+,,,"&K14Y11D1D5+,,,"&K &&	G+71+<+< 	l\)A-...CF  &&	G+71+<+< 	l\)A-...CF  %%	E*5*:*: 	kK'!+,,,AD  %%	E*5*:*: 	kK'!+,,,AD  "2!2!2!5!6!0!1!0!0!/!/!$  r   ra   c                   '()K   t          j        d          }t          j        d          }t          j        t          j                                                  }|t          d          z
  }|t          d          z   }|t          d          z
  }t                      4 d {V }t          |||           d {V }t          |           d {V }	t          |||           d {V }
t          |||           d {V }d d d           d {V  n# 1 d {V swxY w Y   d |	D             }t          ||          't          d	 '                                D                       }t                               d
t$           dt'          |                      t)          j        d |D                       }|j        rt/          dt$           d          t)          j        |d                   |d<   i }|
D ]^}t3          |j                  ||j        |j        |j        f<   |j        rt3          |j                  nd ||j        |j        |j        df<   _t'          ||d         t$          k             d                                                   }| r| g}t                               d|            g }|D ]')||d         )k    |d         t$          k    z                               d                                           }tC          d          D ]}|t          |          z   (tE          '(t$          )          \  }}}'()fd}tE          '(t          d          z
  t$          )          \  }}}tE          '(t          d          z
  t$          )          \  }}}tE          '(t          d          z
  t$          )          \  }}}tE          '(t          d          z
  t$          )          \  }}}|dk    rd|z  d|z  z   d|z  z   }d}tG          |)(          }||d         z
  |d<   |$                    (t$          )f          }|$                    (t$          )df          p|d         } )||tK          |          dtM          (           |d           |d            |d           |d          tK          |          tK          |          tK          |          tK          |          tK          |           tK          | o
|d!         | z            tK          | o
|d!         | z
            d"|}!t)          j        |!g          }"t)          j'        |"dg#          }"|"(                    |d$          }"t3          |)                    |"          d                   }#tU          d%|#          }#tW          |#|d&         d'z            }#tU          dtW          d(d(tY          |#|d!         z
            tU          |d!         d          z  d(z  z
                      }$|rt[          |#|z
  d'          nd }%|.                    ((/                    d)          )|t[          |#d'          |%|$d*           t)          j        t)          j0        (          )t$          |#t3          |          t3          |           d+g          }&t)          j1        ||&gd,-          }̐)t)          j        |          S ).Nz:/var/www/html/ls-bot-apis/app/services/xgb_price_model.pklz:/var/www/html/ls-bot-apis/app/services/feature_columns.pkltzr#   days   <   c                 (    i | ]}|j         |j        S r   )
product_idra   )r   ps     r   
<dictcomp>z#predict_30_days.<locals>.<dictcomp>  s    FFFA1<FFFr   c              3   8   K   | ]\  }}}|t           k    |V  d S N)rC   )r   _shrts       r   	<genexpr>z"predict_30_days.<locals>.<genexpr>  s@        2r!!! 	!!!! r   zRoom types found for sub_hotel z: c                 6    g | ]}t          |j                  S r   )dict_mapping)r   rT   s     r   r   z#predict_30_days.<locals>.<listcomp>  s"    MMMctCL11MMMr   z*No stored features found for sub_hotel_id=z'. Check daily_room_type_features table.rP   rackrA   ra   zForecasting for room types: rn   c                 `    t          fdt          d| dz             D                       S )Nc              3   t   K   | ]2}t          t          |           z
  t                    d         V  3dS )r   r#   N)r_   r   rC   )r   drS   	pred_datert_ids     r   r   z8predict_30_days.<locals>.sum_bookings.<locals>.<genexpr>J  s]          y9!;L;L;L/LN^`effghi     r   r#   )sumrange)r   rS   r   r   s    r   sum_bookingsz%predict_30_days.<locals>.sum_bookingsI  sS          "1dQh//     r   rl   rm   r   g333333?g333333?g?ru   r}   rw   )ra   total_inventorybooked_inventoryrc      rr   )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_30rd   Price_to_Rack_RatioPrice_vs_Rack_Gap)columns)r   
fill_value2   rt   rp   d   z%A)DateDay	Room_TypeActual_PricePredicted_PriceDiffConfidence_%)rP   ra   rA   rb   rc   rd   T)ignore_index)2joblibloadr   nowr   utcrP   r   r   r<   r>   rD   rF   rZ   rL   keyslogwarningrC   sortedr~   	DataFrameempty
ValueErrorto_datetimer   rb   rA   ra   rd   uniquer   copyr   r_   r   rO   r!   r-   get_dummiesreindexpredictmaxminabsr   appendstrftimer   concat)*ra   modelfeature_columnstoday	yesterdayforecast_endhistory_startr9   rQ   product_rowsfuture_pricesstored_featuresrR   sub125_room_types
history_dffuture_price_mapfp
room_typesresultsr   
day_offsetrI   rJ   occ_rater   r   lag_1lag_7lag_14lag_30rollingactual_future_pricerd   feature_rowdf_inputpredicted_price
confidencediffnew_rowrS   r   r   s*                                          @@@r   predict_30_daysr     s     k"^__Ek"^__O<8<0005577E9!,,,,I9"----LI2....M~~ T T T T T T T0]EJJJJJJJJ ,B//////// 2"e\JJJJJJJJ !6b- S SSSSSSST T T T T T T T T T T T T T T T T T T T T T T T T T T GFFFFK'+>>M   +0022     KKa2BaafM^F_F_aabbb MM_MMMNNJ 
59I 5 5 5
 
 	
 
6(:;;Jv  @ @PUVXVhPiPi"'2?BODEhjhuPPUVXVcPdPdPd{"'2?BOVLMM :n-1AAB>RYY[[ J  $"^
KK;z;;<<<G qW qW %'50'+;;=
 +f

ddff 	
  )) i	W i	WJ	z : : ::I '2y*:E' '#E68       '}i)QRBSBSBS6SVfhmnnKAq%&}i)QRBSBSBS6SVfhmnnKAq%&}i)QSBTBTBT6TVfhmnnLAq&&}i)QSBTBTBT6TVfhmnnLAq& zz%K%K &L! 
  /yQQG-5@V8W-WG)* #3"6"6,e4# #
 *--,eV<  ,*+  */).)/)3H)=)=	 
 (	22 *6a)5a)5a)5b)9)9)3E):):)3E):):)3F););)3F););)3J)?)?)3J4k7K]C^akCk)l)l)3J4k7K]C^akCk)l)l!  " #K* |[M22H~h8HIIIH''A'NNH#EMM($;$;A$>??O!"o66O "/7;N3ORS3STTO QCs?W5G-HHII 23Q778;>? ?! !  J
 GZc5+>>BBB_cDNN#,#,#5#5d#;#;#(#6#(!#<#<#'#-     l#%<	#:#:#(#3#2#(??#(#4#4% % $  G !i'(BQUVVVOOSi	WV <   s   ,AD
D#&D#c                    K   t                       d {V } i }t          | d                                                   D ]}| | d         |k             }g }|                                D ]\  }}|                    t          |d                   t          j        |d                   rt          |d                   nd t          |d                   t          |d                   d           ||t          |          <   |S )Nr   r   r   r   r   )rP   actual	predictedr  )
r  r   r   iterrowsr   strr~   notnar   r   )dfr;   r   room_df	room_listr   rs          r   predict_30_days_datar    s       	 	 	 	 	 	 BF;..0011 ' 'R_-.	$$&& 	 	DAqAfI68hq?P6Q6Q[%. 1222W["1%6#788#An$566	      's5zzMr   c                    K   t                       d {V } t          j        t          j                                                  }t          dd            t          dt                      t          dt          j                                        d                      t          d| d|t          d	
          z               t          d            t          | d                                                   D ]}| | d         |k             }||d                                                  }d}t          |          rHt          j        t          j        |d         |d         z
  |d         z                      dz  }d|dd}t          d| | d           t          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d#            |                                D ]\  }}|d         rd&|d         d'nd(d}	|d         |d         d)k    rd*nd}
|
 d&|d         d}nd+}t          dt%          |d                   dd|d         dd|	dd,|d         d'd|dd|d-         d.d           | | d                                                  }t          |          ryt          j        t          j        |d         |d         z
  |d         z                      dz  }t          dd            t          d/|dd           t          d d           | S )0Nr   
zU=====================================================================================u&     30-Day Price Forecast — Sub Hotel z  Generated : z%Y-%m-%d %H:%Mz  Period    : u    → r   r   r   r    r   r   z	   MAPE: z.1f%u   
── Room Type u    ──z  r   z<13 r   z<11Actualz>10	Predictedr   z>9
Confidencez>11u'   ─────────────u!   ───────────u   ──────────u   ─────────u   ₹z>8.2fzN/Ar   +u   —u    ₹r   z>9.1fz)  Overall MAPE vs actual future prices : )r  r   r   r   r   rP   printrC   r   r   r   r   r  rf   r   rh   r   r  r  )r  r   r   r  with_actualmape_strmaper   r  
actual_strsigndiff_stroverall_mapes                r   mainr$    sC       	 	 	 	 	 	 BLHL)))..00E	-v--	
E3C
E
EFFF	
F8<>>223CDD
F
FGGG	
C5
C
Cuyb/A/A/A'A
C
CDDD	V+;..0011  R_-.gn5;;==>{ 	/7N3kBS6TT$^45 6 6  D /4....H<E<8<<<===k6kkkkkkHkkk;kkkVkkkYekkklllS8SShSSSSHSSwSSSSTTT$$&& 	 	DAq<=n<M`8q08888V[S`S`Jy$i1nnss""66qy666 .S6^^* . .U8". .$. . )*3. . !	. .
 ^$-. . .   	" R'--//0K
; wFK/+>O2PP 01 2 2
 
  	m6mmM,MMMMNNNmmmIr   r   )$loggingpandasr~   numpyr   r   r   r   r   
sqlalchemyr   app.models.connectionr   collectionsr   	getLogger__name__r   rB   r5   r6   rC   r   r!   r-   r<   r>   rD   rF   rZ   r_   r   r   r  r  r$  r   r   r   <module>r-     s             2 2 2 2 2 2 2 2 2 2       . . . . . . # # # # # #g!!!
 G G G
     
    :  &  4  @  4) ) )@ @ @Lp! p! p! p! p! p!d  *6 6 6 6 6r   