
    jFK                        d Z ddlmZ ddlZddlmZ ddlZddlZ	ddl
mZmZmZ ddlmZmZ ddlmZmZ dd	lmZmZ dd
Z	 	 d	 	 	 	 	 ddZdd	 	 	 ddZddZ	 d	 	 	 ddZddZddZd ZdddZy)u   run_pipeline: the worker's single hook. Same contract as engine_stub.
Also: backtest() — accuracy + interval coverage against known truth.    )annotationsN)Path   )attach_intervalsestimate_rowindex_evidence)	adapt_epgis_epg_export)ReferenceDatavalidate_schedule)broadcast_hoursplatform_totalsc                $   | r| j                         rtt        j                  | j                               }|j	                  dd      }|dk(  rt        j                  |      S t        j                  |j	                  dd            S t        d      )zvfixtures mode via worker/config.json {'reference_mode':'fixtures',
    'fixtures_dir': ...}; mysql mode on the server.reference_modefixturesmysqlfixtures_dirz*No worker config found for reference data.)	existsjsonloads	read_textgetr   
from_mysqlfrom_fixturesFileNotFoundError)cfg_pathcfgmodes      1/var/www/html/crystal/crystal2/engine/pipeline.py_load_referencer       sx     HOO%jj++-.ww'47? ++C00**377>:+NOO
H
II    c           
        t        |      }t        |       }g }t        | j                         d      D ]t  \  }\  }	}
|j	                  t        |
j                         |||             |s7|dz  dk(  s||k(  sEdt        d|z  t        d|      z        z   } ||dd|d	d
|d	       v t        j                  | j                  d      t        j                  |      gd      }t        ||      S )Nr   )start   r      -   zEstimating audienceszrow ,z of T)drop)axis)r   len	enumerateiterrowsappendr   to_dictintmaxpdconcatreset_index	DataFramer   )schedulerefon_progresssigma_by_tiercorrection_by_tieridxnresultsi_rowpctouts                r   estimate_framerB      s     
CHAG !2!2!4A> L8As|CKKM3=OPQAFaK16s26C1I-..C3tAa5QqE5JK	L
 ))X))t)4bll76KLST
UCC//r!   )config_pathc                    |ddd       t        j                  |       }t        |      rh |ddd       |xs7 t        t              j                         j                  j                  dz  dz  }t        ||      } |d	dt        |      d
d       t        |      }|rt        dj                  |            t        |||       t        ||      \  }}|r |dd|d
d       d|j                  vr=d|j                  v r/t        j                  |d         j                   j"                  |d<    |ddd       |xs7 t        t              j                         j                  j                  dz  dz  }	t%        |	      }
 |ddt        |
j'                               d
d       |
j)                         }|
j+                         }|s|r |dddt-        |
dd              t/        ||
|||      } |ddd       ||d   j1                  t2              j2                  j5                  d          } |d!d"d#       g d$}||D cg c]  }||j                  v s| c}   }|| d%z  }|| d&z  }	 d'd(lm}  ||||t3        | j:                        |
)       |j?                  |d*+       tA        tC        ||
            }|| d,z  jE                  tG        jH                  |d*-             |jK                         D cg c]  \  }}tM        |      d.z   t3        |jO                  d/d0            t3        |jO                  d1d0            t3        |jO                  d2|jO                  d3d0                  t        jP                  |d4         rd ntS        |d4         d5d6d7d8d9jO                  t3        |d         t3        |d               d: }}}|| d;z  jE                  tG        jH                  |             t        |      tM        |d4   jU                         jW                               t        |      ||d<S c c}w # t<        $ r |j?                  |d*+       Y w xY wc c}}w )=N   zChecking your filezReading columns and dates   zReading EPG listingszTranslating to a scheduleworkerzconfig.json   r'   z  broadcasts after de-duplication 	   zApplying fixturesz  rows matched to a known fixtureweekdaybroadcast_date
   zMatching evidencezLoading reference data   z historical broadcasts   zcalibration: calibration_sourceassumed)r8   r9   X   zRunning safety checkszComparing against market sizesflag)REVIEWNO_EVIDENCE^   zPreparing your reportszFormatting workbooks)	
event_namecountry_namechannel_nametelecast_typeestimated_ama_000interval80_lowinterval80_high
confidencestrategyz_summary.xlsxz_detailed.xlsxr   )write_report)titler6   F)indexz_chartdata.json)	allow_nan   rY    rX   programme_namerW   r[   z2Estimate is above 15% of this market's TV universeu3   Evidence for this row is thin — confidence is lowz4No usable historical evidence was found for this rowu9   Market TV population missing — Crystal refused to guess)REVIEW_ABOVE_15PCT_TVUREVIEW_LOW_CONFIDENCErU   MISSING_TVU_REFUSED)r?   channelcountry	programmeamawhyz_flags.json)
rows_totalrows_estimatedrows_flaggedsummary_pathdetailed_path),r1   
read_excelr
   r   __file__resolveparentr	   r*   r   
ValueErrorjoin_write_events_merge_fixturescolumnsto_datetimedtrK   r    evidencer8   r9   getattrrB   astypestr
startswithexcel_reportr`   name	Exceptionto_excel
_json_safe_build_chart_payload
write_textr   dumpsr,   r/   r   isnafloatnotnasum)upload_pathexports_dirrun_idr7   rC   r5   cfg_for_epgproblemsmergedr   r6   sigmacorrrA   flaggedsummary_colscsummaryrr   rs   r`   chart_payloadr=   rflagss                            r   run_pipeliner   ,   sd   ')DE}}[)H
 XA-/JK! ON""$++22X=M 	X{3A-8}Q''GH	J !*H(+,, (K0 'x=HfA*vaj8X,YZ(((-=AQAQ-Q nnX6F-GHKKSS')AB

\$x.00299@@8Km[C
#
C'C,?+BBX)YZ
 E!!#DB+#GC1Ey$Q#RS	U
3',GC +-MN#f+$$S)--889RSTG,.DE.L l?a3;;.>1?@GF8=!99LVHN";;M4.S,{//0c	; LLeL, 3C=>MfX_--99

=E24 !))+
- A VaZQUU>267QUU>267quu%5quu\27NOP gga(;&<=d5K^I_C`0d/d%[-h C&	NC&	N;	= 
-E 
- fX[))55djj6GHc(!#&9":"@"@"B"F"F"HIJ(=J JQ @  4U34
-s%   P:P:1&P? >CQ"?QQc           	     6   | d   j                         }| j                  |df   j                  t              }||   j                  t              }||z
  }t        t	        j
                  |      j                         |j                         z        }t        |j                         |j                         z        }t        t	        j
                  |      |z  dk  j                               }t        | j                  |df   |k  || j                  |df   k  z  j                               }	t        j                  | j                  |df   j                         t	        j
                  |      |z  d      j                  d      d   j                  d	d
g      j                  d      j                  d      }
t        |j                               t        |j                         d      t        |d      t        |d      t        |d      t        |	d      |
dS )Nr[   g?r\   r]   strategy_tier)tieraper   r   meancountrE   rb      )rowscoverage_rateWAPEbias_pcthit_within_20pctinterval80_coverageper_tier_mean_APE)r   locto_numpyr   npabsr   r   r1   r4   groupbyaggroundr.   r/   )	estimatestruthokpaerrwapebiashit20covper_tiers              r   backtestr      s   	&	'	-	-	/Bb--.77>Ab	5!A
a%C"QUUW,-DQUUW$%D266#;?d*0023E
)--$4 45:r+<'<==?@DHCimmB4G&H&Q&Q&S%'VVC[1_6 7((+VW,=(>uuQxPWHX  ME"'')Q4G$NdA %eQcST!)+ +r!   c                   | d   j                         |dkD  z  }t        j                  ||   j                  t              | j
                  |df   j                  t              z        }t        j                  | j
                  |df   j                         |d      }t	        |d   j                               }i i }}|j                  d      D ]  \  }	}
t        |
      }|dk  r|||z   z  }t	        t        j                  ||
d   j                         z  d|z
  |z  z               |t        |	      <   t	        t        j                  |
d   j                  d	      d
d            |t        |	      <    ||fS )zPer-tier residual calibration in log-space (Crystal's dormant module, live):
    correction = exp(w*mu_tier + (1-w)*mu_global), w = n/(n+shrink_n);
    sigma feeds the 80% intervals. Returns (correction_by_tier, sigma_by_tier).r[   r   r   )r   lrr   r   rH   r   )ddofg?g?)r   r   logr   r   r   r1   r4   r   r   r*   expr/   clipstd)r   r   shrink_nr   r   dfmu_gr   sigtgr;   ws                r   fit_calibrationr      sV   
 
&	'	-	-	/519	=B	b	""5)IMM">Q:Q,R,[,[\a,bb	cB	y}}R-@AJJLTVW	XBD!DB#D

6" E1Fq5XRVVA$$6!a%4$GHISVBGGAdGKKQK$7sCDCFE 9r!   c                    d j                   vryt         fddD        d      }g }  d   j                            j                  d      D ]  \  }}|r(t	        j
                  ||   d      j                         nt	        j                  d      }d	|j                   v r2|d	   j                         j                         j                         dd
 ng }|j                  t        |      t        t        |            t        |j                  dt	        j                  t                     j                         j                         D 	ch c]  }	t        |	       c}	      dd t        |      r|j#                         j%                  d      ndt        |      r|j'                         j%                  d      ndt        |      r#t        |j'                         j(                        nd|D 
cg c]  }
t        |
       c}
d        |j+                  d        || dz  j-                  t/        j0                  |d             yc c}	w c c}
w )zList the distinct tournaments in this schedule, with enough context for
    the enrichment screen to guess the right Wikipedia page.rW   Nc              3  @   K   | ]  }|j                   v s|  y wNr|   .0r   r5   s     r   	<genexpr>z _write_events.<locals>.<genexpr>        ]qqHL\L\G\]   rL   	prog_datedatecoerceerrorszdatetime64[ns])dtype
prog_titler   rX      z%Y-%m-%dre   )rW   r   	countries	date_fromdate_toseasonsample_titlesc                    | d    S )Nr    )r   s    r   <lambda>z_write_events.<locals>.<lambda>   s    QvYJ r!   keyz_events.jsonr   )indent)r|   nextr   r   r1   r}   dropnaSeriesuniquetolistr-   r   r/   r*   sortedr   objectminstrftimer0   yearsortr   r   r   )r5   r   r   dcolr   r   r   datestitlesr   r   s   `          r   rz   rz      s    8+++]C]_cdDDH\288:;CCLQ aEIqwx8??Aryy_oOp!QYY. L/((*113::<Ra@46 	d)AK~ryyW]G^1_1f1f1h1o1o1q rAQ rstwuwx=@Z--j9R;>u:uyy{++J72/25zc%))+**+t.45c!f5
 		 	II&I'fX\**66tzz$q7QR !s 6s   I/Ic           
         |d j                   vr dfS 	 t        j                  t        |      j	                               }ddl}|j                  |d   t        |j                  dd            |d   |d   |j                  d	d
      d|j                  j                        } d   j                         j                         D cg c]  }t        |       }}|s dfS |j                         5 }|j!                  ddj#                  dgt%        |      z        z  |       |j'                         }ddd       |j)                          s dfS i }	|D ]4  }
|	j+                  |
d   t        |
d         fg       j-                  |
       6 t/         fddD        d      }g g g }}}d} j1                         D ]  \  }}d}d}|rt3        j4                  |j                  d            rt3        j4                  |j                  |            rt        |d         t        t3        j6                  ||   d      j9                               f}|	j                  |      xs g }t%        |      }|dk(  r|d   }nd|dkD  r_|j                  d      |D 
cg c]  }
|
j                  d      s|
 }}
|r&t3        j4                        rt;        |fd      }n|d   }|r6|dz  }|j-                  |d    d|d           |j-                  |d          nJ|j-                  |j                  dd      xs d       |j-                  |j                  d d      xs d       |j-                  |         j=                          | d<   | d <   | d!<    |fS # t        $ r  dfcY S w xY wc c}w # 1 sw Y   }xY wc c}
w )"a  Attach teams and stage from confirmed Wikipedia fixtures.

    Joined on (event_name, date). Where a date carries several fixtures every one
    of them is kept in the table; here the closest by kickoff time is chosen when
    the schedule has an hour, otherwise the first. Nothing is ever blanked out --
    an ambiguous date still yields a stage and a best-guess pairing, and
    fixture_candidates records how many it was chosen from.
    NrW   r   db_hostdb_porti  db_userdb_passdb_namecrystalutf8mb4)hostportuserpassworddatabasecharsetcursorclasszSELECT event_name, match_date, match_time, team1, team2, stage FROM wiki_fixtures WHERE match_date IS NOT NULL AND event_name IN (%s)r'   z%s
match_datec              3  @   K   | ]  }|j                   v s|  y wr   r   r   s     r   r   z"_merge_fixtures.<locals>.<genexpr>   r   r   r   r   r   r   hour
match_timec                b    t        t        t        | d         d d       t              z
        S )Nr  rd   )r   r/   r   )fr  s    r   r   z!_merge_fixtures.<locals>.<lambda>	  s/    3s3q;OPRQR;S7TWZ[_W`7`3a r!   r   team1|team2stagesports_teamsre   match_levelfixture_candidates)r|   r   r   r   r   pymysqlconnectr/   r   cursors
DictCursorr   r   r   r   cursorexecutery   r*   fetchallclose
setdefaultr-   r   r,   r1   r   r}   r   r   copy)r5   rC   r   r  conneeventsr   r   by_keyr  r   teamslevelscandshitr>   r   gotr;   r   pooltimedr  s   `                      @r   r{   r{      s    l(2B2BB{	jjk*4467Yc#'')T*B&CY#i.WWY	2I22	  4 '|4;;=DDFGc!fGFG{	  !			UhhvF+,-.4	6 ::<  	JJL{ "F Q1\?C,,@A2FMMaPQ ]C]_cdDr256E
C!!# 1BHHQUU<01bhhquuT{6Kq'R^^AdGH-U-Z-Z-\)]^C::c?(bDD	AAv1gQuuV}$(@qAEE,,?@@RXXd^e)abCq'C1HCLLCL>3w<.9:MM#g,'LL~r28b9MM!%%r28b9Q/2 }}H$H^$H]%*H!"S=i  { H   8 As1   BN 	N+3AN0N=,N=N('N(0N:c                   t        | t              r-| j                         D ci c]  \  }}|t        |       c}}S t        | t        t
        f      r| D cg c]  }t        |       c}S | t        | t        t        f      r| S t        | t        j                  f      rt        |       S t        | t        t        j                  f      r9t        |       }t        j                  |      st        j                  |      rdS |S t        | t        f      r| S | t        j                   u s;t#        t        d      r,t        | t        t        f      st        j$                  |       ryt        |       S c c}}w c c}w )a'  Recursively turn NaN/NaT/numpy scalars into JSON-legal values.

    pandas keeps NaN inside float columns even after .where(notna, None), so
    to_dict('records') hands back float('nan'). Left alone those reach the
    browser as the bare token NaN and kill JSON.parse for the entire file.
    Nr   )
isinstancedictitemsr   listtupler   boolr   integerr/   r   floatingisnanisinfr1   NaThasattrr   )objkvr  s       r   r   r     s    #t-0YY[9TQ:a= 99#e}%'*+!
1++
{jsDk2
#

}%3x#r{{+,#Jrxx{t::#v

bff}V,ZdD\5RWWS\s8O :+s   E2E8c                
   | d   j                         }| j                  |df   }| j                  |df   }| j                  |df   j                  d      }ddddd	d
}g d}g d}t        j                  |||d      j                         j                  |      j                  d      }	t        j                  |g dg dd      }
|
j                         j                  g d      j                  d      }|j                  |      j                         }t        |      D cg c]  }||   |j                  v s||    }}| j                  |   j                  d      d   j                         j                  d      j                  d      }| d   j                  dd      j                         }t!        |       \  }}t#        t%        |            }t'        | |      }g d}| |D cg c]  }|| j(                  v s| c}   j+                         }|j-                  t        j                   |      d      }t#        t/        |             t#        |j                               t/        |      r#t%        t1        |j3                               d      ndt/        |      r#t%        t1        |j5                               d      ndt#        | d   j                  d      j                  t6              j6                  j9                         dk7  j                               t/        |      r#t%        t1        |j                               d      nd||t%        |d   d      t%        |d   d      |d    |d!   d| v rt#        | d   j;                               ndd"| v rt#        | d"   j;                               ndd#||	j=                         D cg c]  }t#        |       c}d$t?        |j                  j                  t6                    |j=                         D cg c]  }t#        |       c}d$||D cg c]  }t#        |jA                  |d             c}d$|j                  j=                         D cg c]  }t7        |       c}|j=                         D cg c]  }t%        t1        |      d       c}d$|j                  j=                         D cg c]  }t7        |       c}|j=                         D cg c]  }t#        |       c}d$|jC                  d%&      d'S c c}w c c}w c c}w c c}w c c}w c c}w c c}w c c}w c c}w )(zAggregates + row table for the results dashboard. Pure JSON, no pandas
    objects leak out, so plain PHP/JS can render it with zero dependencies.r[   r^   r   Int64zSame channelz
Same teamszSame marketzCross-marketzTime-slot history)r   rd   rE   r   rF   )
r   rF      2   d      i  i  i  g   mB)	z0-5kz5-20kz20-50kz50-100kz100-250kz250-500kz500k-1Mz1M-5Mz5M+F)binslabelsrightr   )r   <   K   Z   r@  )LowMediumHigh	Very HighT)rK  rJ  rI  rH  rX   )	ascendingrH   rS   re   None)
rW   rX   rY   rZ   r[   r\   r]   r^   r_   rS   Nr   g        ott_000ooh_000ott_marketsooh_marketsrY   )ro   rp   
median_amamean_confidencer   total_ama_000r   broadcast_hours_noterN  rO  rP  rQ  marketschannels)rC  valuesrecords)orient)kpidistributionconfidence_bandstierstop_marketsr   r   )"r   r   r   r1   cutvalue_countsreindexfillnamapr   rb   r   r   sort_valuesheadreplacer   r/   r   r   r|   r   wherer*   r   medianr   r   stripnuniquer   r0  r   r.   )rA   r6   r   estconfr^  
tier_namesrB  rC  histbandconf_countstier_countsr   
tier_orderr_  flag_counts_hours_hours_note_plat
table_colsr   tabler;  xs                            r   r   r   6  s    
 	!	'	'	)B
''"))
*C772|#$DGGB'(//8E#"':<J ;D(F66#Du=JJLTTU[\ccdefD 66$1?tMD##%--.TU\\]^_K))J'446K)/
);bAz!}P[PaPa?a*Q-bJb772;&&~67JK35u5dd1g  f+%%b&1>>@K *#.FKvF C%E3J J;q!s{{*:;<AACEKK.E c#h-!"&&(m;>s8%cjjl 3Q7?B4yuU499;%7;d CK..r299#>BBHHJbPUUWX
 <?s8U5#3Q7%$/U9-q1U9-q1 / /=Ks=Rs3~.6689XY>LPS>SC/779:YZ)
, $*dkkm5Tc!f5TU'+K,=,=,D,DS,I'J8C8J8J8L'M1A'MO&@JK1C1 56KM3>3D3D3K3K3M"Na3q6"N?J?Q?Q?S"T!5q1#5"TV-8->->-E-E-GHSVH,7,>,>,@AqCFACY/A! !+ c$ <4 6U'MK"N"THAsB   $T';T',T, T,	T1T6
3"T;5U UU
=U)r   Path | Nonereturnr   )NNN)
r5   pd.DataFramer6   r   r8   dict | Noner9   r~  r|  r}  )
r   r   r   r   r   r   rC   r{  r|  r.  )r   r}  r   	pd.Seriesr|  r.  )g       @)r   r}  r   r  r   r   r|  ztuple[dict, dict])r5   r}  r   r   r   r   r|  rM  )r5   r}  rC   r{  r|  ztuple[pd.DataFrame, int]r   )rA   r}  r|  r.  ) __doc__
__future__r   r   pathlibr   numpyr   pandasr1   corer   r   r   epg_adapterr	   r
   	referencer   r   	vr_reportr   r   r    rB   r   r   r   rz   r{   r   r   r   r!   r   <module>r     s   J "     @ @ 1 7 7	J CG5904?0'20>J0  -1cJ)cJ59cJN+( '*#.?(S0GT4Lr!   