
    j%E                    
   d Z ddlmZ 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  ee      j                         j                   j                   Zej$                  j'                  d ee             edz  Zej-                         r"ej$                  j'                  d ee             ddlZddlZg dZd	d	d	d	d
dZi dddddddddddddddddddddd d!d"d#d$d%d&d'd(d)d*d+d,i d-d.d/d0d1d2d3d4d5d6d7d8d9d:d;d<d=d>d?d@dAdBdCdDdEdFdGdHdIdJdKdLdMdNi dOdPdQdRdSdTdUdVdWdXdYdZd[d\d]d^d_d`dadbdcdddedfdgdhdidjdkdldmdndodpi dqdrdsdtdudjdvdXdwdxdydzd{d^d|d}d~dddXddddddddddndd6dddTdd^d}d6dxdZddddddddZddZddZ ej@                  d      Z! ej@                  d      Z"ddZ#	 	 d	 	 	 	 	 ddZ$ddZ%dddZ&e'dk(  r e( e&             y)ac  Turn a rights/distribution grid PDF into an EPG workbook Crystal can read.

What the input actually is
--------------------------
Not an EPG. It is the grid a rights team publishes: one row per
(territory, platform, broadcast partner), one COLUMN per session, and the
channel name written in the cell:

    Spain | Linear (FTA) | RTVE | N/A | N/A | N/A | N/A | Teledeporte

Crystal wants the transpose of that -- one row per telecast. So each non-N/A
cell becomes a row, and the column it came from decides the telecast type.

Why the parse is done on word coordinates
-----------------------------------------
`extract_text()` interleaves wrapped cells: a territory whose name runs to two
lines comes back with its characters woven into the neighbouring cell's, which
produced entries like "Pan-Africa ( T E u x n c i l s . i A a l ...". Words are
clustered by their y position instead and assigned to a column by x against the
header centres, which survives wrapping.

Times
-----
The grid carries almost no times. Two forms appear in cells and both are the
market's OWN local clock, because that is how a broadcaster publishes a slot:

    Eurosport 1 (Delayed 23:00)      -> 23:00 local, same day, RECORDED
    Eurosport 1 (18/8 - 21:00)       -> 21:00 local on 18 Aug

Everything else is a live simultaneous session, so it has no local time in the
document at all -- it has ONE moment, and each market sees it on its own clock.
Those need `--sessions`, given in UTC, and are converted per territory.

    python3 worker/pdf_to_epg.py schedule.pdf out.xlsx         --event "2026 London E-Prix"         --sessions '{"FP1":"2026-08-16T08:30Z","FP2":"2026-08-16T12:00Z",
                     "Qualifying":"2026-08-16T14:00Z","Race":"2026-08-17T14:03Z"}'

There is no default. A session time invented here would travel all the way to
an audience figure without anyone seeing it was a guess.
    )annotationsN)datetime	timedeltatimezone)Path)ZoneInfovendor)FP1FP2
QualifyingRace
HighlightsLIVE
HIGHLIGHTSAlbania & KosovozEurope/Tirane	ArgentinazAmerica/Argentina/Buenos_Aires	AustraliazAustralia/SydneyAustriazEurope/Vienna
Bangladeshz
Asia/DhakaBelgiumzEurope/BrusselsBosniazEurope/SarajevoBrazilzAmerica/Sao_PauloBulgariazEurope/SofiaCanadazAmerica/TorontoChilezAmerica/SantiagoCroatiazEurope/ZagrebzCzech RepubliczEurope/PragueDenmarkzEurope/CopenhagenEstoniazEurope/TallinnFinlandzEurope/HelsinkiFrancezEurope/ParisGermanyzEurope/BerlinGreecezEurope/AthensHungaryzEurope/BudapestIcelandzAtlantic/ReykjavikIndiazAsia/Kolkata	IndonesiazAsia/JakartaIsraelzAsia/JerusalemItalyzEurope/RomeJapanz
Asia/TokyoLatviazEurope/Riga	LithuaniazEurope/VilniusMalaysiazAsia/Kuala_LumpurMexicozAmerica/Mexico_CityNetherlandszEurope/AmsterdamzNew ZealandzPacific/AucklandNorwayzEurope/OsloPhilippineszAsia/ManilaPolandzEurope/WarsawPortugalzEurope/LisbonRepublic of IrelandzEurope/DublinRomaniazEurope/Bucharest	SingaporezAsia/SingaporeSlovakiazEurope/BratislavaSloveniazEurope/LjubljanazSouth AfricazAfrica/JohannesburgzSouth Koreaz
Asia/SeoulSpainzEurope/MadridSwedenzEurope/StockholmSwitzerlandzEurope/ZurichTurkeyzEurope/IstanbulUKzEurope/LondonUkrainezEurope/KyivUSAzAmerica/New_YorkVietnamzAsia/Ho_Chi_MinhThailandzAsia/BangkokTaiwanzAsia/TaipeizPan - EuropezPan - South East AsiaPan South & Central AmericazAmerica/BogotazPan EurasiazAsia/Almaty
Pan-AfricaPan-MENAz
Asia/DubaiGreater ChinazAsia/Shanghaiz
Pan - APAC
LuxembourgzEurope/LuxembourgMonacozEurope/MonacozEl SalvadorzAmerica/El_SalvadorPakistanzAsia/KarachizNorth AmericaIndian SubcontinentChina)IrelandAlbania
Pan AfricaPan Middle EastPan Asia (Indian Subcontinent)Pan C&S America (Latin America)rK   rM   rP   rO   rL   rN   )rE   r3   rC   rB   rI   r   rD   c                |    t        j                  dd|       } t        j                  dd|       j                  d      S )aQ  Territory name without its parenthetical exclusion list.

    Those lists wrap, and a wrapped line in this PDF comes back letter-spaced
    ("( E x c l . A l g e r ia ,"), which then reads as a separate territory.
    They carry nothing Crystal uses -- it matches on the country name -- so
    they are dropped rather than repaired.
    z
\([^)]*\)? z\s+     -–,)resubstrip)ts    3/var/www/html/crystal/crystal2/worker/pdf_to_epg.pyclean_territoryrZ      s4     	}b!$A66&#q!''11    c                   g }t        j                  t        |             5 }|j                  D ]  }|j	                         }|D ci c]  }|d   dgt
        v r|d   | }}d|vr;|j                         D ci c]  \  }}||d   |d   z   dz   c}}t        d |j                         D              }i }	|D ];  }|d   |k  r|	j                  t        |d   dz        g       j                  |       = d	}
t        |	j                               D ]  \  }}|j                  d
        t        d |D        d	      }|y|
t        |      dkD  r@|D ]b  }t        |d         dk(  r|d   |d   z   dz  d   dz
  k  r/t!        t
        fd      }|
|   dz   |d   z   j#                         |
|<   d dddgt
        D ci c]  }|g  }}|D ]  }|d   |d   z   dz  |d   |d   k  r|d   j                  |d          4d   dz
  k  r+|d   |d   dz   k  r|d   n|d   j                  |d          jt!        t
        fd      }||   j                  |d           |j                         D ci c]%  \  }}|dj%                  |      j#                         ' }}}t'        j(                  d|d         st+        |d         }t,        j/                  ||      |d<   |j                  |       |}
  	 d	d	d	       t1        j2                  |      S c c}w c c}}w c c}w c c}}w # 1 sw Y   4xY w)z+The PDF's rows, as they appear on the page.textPlatformx0x1   c              3  &   K   | ]	  }|d      yw)bottomN .0ws     rY   	<genexpr>zparse_grid.<locals>.<genexpr>   s     :(:s   top   Nc                    | d   S )Nr_   rd   )rg   s    rY   <lambda>zparse_grid.<locals>.<lambda>   s
    ag r[   )keyc              3  .   K   | ]  }|d    dv r|  yw)r]   )LinearDigitalNrd   re   s     rY   rh   zparse_grid.<locals>.<genexpr>   s&      FQ"#F)/D"D  ! Fs         r
   F   c                &    t        |    z
        S Nabsscentresmids    rY   rl   zparse_grid.<locals>.<lambda>       C'!*<L8M r[   rS   	TerritoryPartner   c                &    t        |    z
        S ru   rv   rx   s    rY   rl   zparse_grid.<locals>.<lambda>   r|   r[   z^(Linear|Digital))
pdfplumberopenstrpagesextract_wordsSESSIONSitemsmaxvalues
setdefaultroundappendsortedsortnextlenminrW   joinrU   matchrZ   COUNTRY_ALIASgetpd	DataFrame)pdf_pathoutpdfpgwordsrg   hdrkbelowlinesprev_wsanchorcellsvrowrX   rz   r{   s                     @@rY   
parse_gridr      s   
C	X	' B3)) A	B$$&E). <AiJ#:#:: V9a< <C <$>AiikJdaq1T7QtW,11JG:SZZ\::E
 &(E DU8e#  qx!|!4b9@@CD D. *2-. F" FGKM > |s2w|  F
 qy>Q.$ w40A5"!44$.MN#'7S=1V9#<"C"C"EQF )4j)(Wh(WX1BXX 	3AT7QtW,1Cw&,.k*11!F)<wu~22./gtr8I.Iz*#I.qy0A.MNa&	2	3 ;@++-H$!Qq#((1+++--HHxx 4c*oF#C$45#0#4#4Q#:K 

3U*/A	BF <<A< KT Y IyB BsD   %ML-$M>L2
D7M
L8
B/M*L=1AM-MMz(\d{1,2})[:.](\d{2})z(\d{1,2})\s*/\s*(\d{1,2})c                   | r | j                         j                         dv rg dddfS d| j                         v }t        j                  d|       }dx}}|D ]  }t
        j                  |      x}r/t        |j                  d            dd|j                  d	       }t        j                  |      x}sat        |j                  d            t        |j                  d	            f} t        j                  d
d|       j                  d      }|j                  d      D cg c]#  }|j                         s|j                         % }	}|	r(t        j                  dd|	d         j                         nd}
g }t        |	      D ]?  \  }}|r'|
r%|j                         st        |      d	k  r|
 d| }|j                  |       A ||||fS c c}w )z?(channels, HH:MM, (day, month), delayed?) out of one grid cell.)zN/ANA-rR   NFdelayz\(([^)]*)\)rr   02d:ra   z	\([^)]*\)rR   rT   /z[\s\d]+$r   rS   )rW   upperlowerrU   findall_TIMEsearchintgroup_DATErV   split	enumerateisdigitr   r   )celldelayedqualtmdtqmnamecpartsstemchansis                rY   _split_cellr      s   4::<%%'+AA4u$$%G::nd+DNB 4a A 
OC(!''!*6Ba A aggaj/3qwwqz?3B	4
 66,D)//9D !%

3=11779QWWY=E= 9>266+r58,2242DE%  1199;#a&A+&!AQ "b'!! >s   'G=Gc                   g g t               }}}| j                         D ]  \  }}	|	d   j                         }
t        j	                  |
      }||j                  |
       |rt        |      nt        j                  }|	d   j                         j                  d      }t        D ]  }t        |	j	                  |d            \  }}}}|s'|j	                  |      }|||j                  |
 d| d       T||j                  |      }nF|j	                  d      xs" t        t!        |j#                                     }|j                  |      }|r:|j%                  d	      \  }}|j'                  t)        |      t)        |      d
      }|r|j'                  |d
   |d         }|rdnt*        |   }|D ]n  }|j                  ||
| d| |j'                  d      |t-        |      z   j'                  d      |j/                         ||xs |||xs d|	d   |	d   d       p   |r/|j                  ddj1                  t3        |            z   dz          t5        j6                  |      |fS )z0One row per telecast. Returns (frame, warnings).r}   Nr^   digitalrR   u    · z&: no session time and none in the cellr   r   r   )hourminutesecondrr   )daymonthRECORDEDz - )tzinfo)minutesr~   )channel_namechannel_countries
prog_titleprog_st_timeprog_en_time	prog_datetelecast_typesports_eventmatch_levelcountry_timezoneplatformbroadcast_partnerzno timezone for: z, u.    — these were left at UTC, add them to ZONES)setiterrowsrW   ZONESr   addr   r   utcr   
startswithr   r   r   
astimezoner   iterr   r   replacer   TELECASTr   dater   r   r   r   )gridsessionseventdefault_len_mincompetitionrowswarnunknownr   gterrzonetzr   ry   r   r   dmyr   baselocalr   hhmmttypechs                             rY   to_epgr     sc    b#%$D 51~##%yy<KK#Xd^J-%%'229= -	A&1!%%2,&?#E2sG<<?D|
tfD+QRS+ "f-Nd8??;L6M1N))"-#B3r73r71M#a&A?")Jx{E $&)-%*G3qc"2$)MMM$>%*Y-O%O'.wdw';!&%*
 %0$85#$(,
 !"*)*9) /-	5l '$))F7O*DDFG 	H<<t##r[   c                $   i }t        j                  |       j                         D ]g  \  }}|t        vrt	        d|dt               t        j                  |j                  dd            ||<   ||   j                  [t	        | d       |S )Nzunknown session z; expected one of Zz+00:00z.: give the time in UTC, e.g. 2026-08-17T14:03Z)	jsonloadsr   r   
SystemExitr   fromisoformatr   r   )rawr   r   r   s       rY   _parse_sessionsr  G  s    
C

3%%' S1H/u4FxjQRR''		#x(@AAq6== s"PQRRS Jr[   c           	     b   t        j                  t        t         j                        }|j	                  dt
               |j	                  dt
               |j	                  ddd       |j	                  d	d d
       |j	                  ddd       |j	                  dt        dd       |j	                  ddd       |j                  |       }t        |j                        }t        dt        |      dd|d   j                          d       t        |t        |j                        |j                   |j"                  |j$                        \  }}|j&                  rat        |      }||d   j(                  j+                         j(                  j-                  d          }t        d|t        |      z
  dd       |j/                  |j0                  d        t        d!|j0                   d"t        |      dd#|d$   j                          d%       |D ]  }t        d&|         y')(N)descriptionformatter_classr   )typer   z--eventTz7commercial name of the round, e.g. '2026 London E-Prix')requiredhelpz--competitionz|the series as Crystal knows it, e.g. 'Formula E World Championship'. Defaults to --event, which usually resolves to nothing.)defaultr  z
--sessionsz4UTC start times, e.g. '{"Race":"2026-08-17T14:03Z"}'z	--minutes<   z$assumed telecast length (default 60))r	  r  r  z--linear-only
store_trueu1   drop Digital rows — Crystal estimates linear TV)actionr  z[pdf] ,u    grid rows · r}   z territoriesr   r   z[pdf] dropped z digital rowsF)indexz[pdf] wrote u    — z telecasts, r   z	 channelsz[warn] r   )argparseArgumentParser__doc__RawDescriptionHelpFormatteradd_argumentr   r   
parse_argsr   r   printr   nuniquer   r  r   r   r   r   linear_onlyr   r   r   to_excelr   )argvapar   epgr   beforerg   s           rY   mainr!  R  s   		 	 W191U1U
WBOOEO%OOEO%OOIR  TOOOT>  ? OOL4Q  SOOKc2?  AOOOLL  N
dAaeeD	F3t9Q-~d;.?.G.G.I-J,
WXt_QZZ8!''199}}&IC}}S3z?&&,,.22==iHHIvC03=ABLLeL$	LuSXaL ((*+96 7 smr[   __main__)rX   r   returnr   )r   r   r#  pd.DataFrame)r   r   r#  z:tuple[list[str], str | None, tuple[int, int] | None, bool])r  N)r   r$  r   dictr   r   r   r   r   z
str | Noner#  ztuple[pd.DataFrame, list[str]])r  r   r#  r%  ru   )r#  r   ))r  
__future__r   r  r   rU   sysr   r   r   pathlibr   zoneinfor   __file__resolveparentBASEpathinsertr   _vendoris_dirpandasr   r   r   r   r   r   rZ   r   compiler   r   r   r   r  r!  __name__r  rd   r[   rY   <module>r5     s  (R #   	 
 2 2  H~&&-- 3t9 
/
>>HHOOAs7|$  = &,8.	.	)46V.	#.	%..	 ,.	 !*+<.	 	.	 "*+>	.	
 .	
 !)*;.	 .	 "+O.	 o.	 (12E.	 .	 "+,=.	 n.	 (.	 o.	  )*;.	 #.	 &-n.	 .	 "*+;.	 ].	 $\.	 m.	 )*:.	 #.	 &./D.	  %!.	  (56H!.	" m#.	" +M#.	$ o%.	$  *?%.	& ?'.	& -67I'.	( !).	( $./B).	* "+.	* %34I+.	, <-.	, ")/-.	.  /.	. #0/.	0 1.	0 "&1.	2 }3.	2 $%73.	4 !5.	4 $.~5.	6 m7.	< O=.	> -?.	@ "#3A.	B =C.	B #/0EC.	D E.	F _G.	F '34DG.	H %I.	H (0I.	J (K.	J +5nK.	L 'M.	L *?M.	R _S.	R *9.C#&4'7[.	r $#D; " "	2FR 	

*+

/0": #%%)>$>$">$.L>$B"J z
TV
 r[   