
    5iikN              
         S r SSKJr  SSKrSSKrSSKrSSKrSSKrSSKrSSK	J
r
  SSKJrJr  SSKrSSKrSSKrSSKJr  SSKJr  SSKJr  SSKJr  SS	KJr  S
/rSr Sr!\
" S5      r"\"RG                  SSS9  \" S5      r$\" S5      r%\" S5      r&STS jr'SUS jr(SVS jr)S r*\RV                  S 5       r,S r-\R\                  " SSS9SWS j5       r/\R\                  " SSS9SXS j5       r0SYS jr1SZS jr2S[S  jr3S\S! jr4S\S" jr5S]S^S# jjr60 S$S%_S&S'_S(S)_S*S+_S,S-_S.S/_S0S1_S2S3_S4S5_S6S7_S8S9_S:S;_S<S%_S=S'_S>S)_S?S+_S@S-_S/S1S3S5S5S7S9S;SA.Er7\Rp                  " SB\Rr                  5      r:S_SC jr;S`SD jr<SaSE jr=SbSF jr>ScSG jr?\R\                  " SHSI9SdSJ j5       r@SeSK jrA\R\                  " SLSS3SM9ScSN j5       rBSfSO jrA\R\                  " SPSS3SM9SgSQ j5       rC\R\                  " SRSI9ScSS j5       rDg)hu  
modules/drive.py
----------------
Utilidades de SOLO LECTURA para Google Drive usando Service Account.

Objetivo:
- Minimizar RAM y latencia (cache local + indexación de carpeta + filtros pushdown en Parquet)
- Contratos consistentes (bytes in / DataFrame out)
- Soportar binarios (parquet/xlsx/csv) y Google Sheets (export -> xlsx)

Requisitos:
- google-api-python-client
- google-auth
- pandas
- openpyxl
- pyarrow
    )annotationsN)Path)AnyIterator)service_account)build)MediaIoBaseDownload)get_settingz.https://www.googleapis.com/auth/drive.readonlyz'application/vnd.google-apps.spreadsheetzAapplication/vnd.openxmlformats-officedocument.spreadsheetml.sheetz/tmp/cabanna_drive_cacheT)parentsexist_okFOLDER_ID_FOLIOSFOLDER_ID_LINEASFILE_IDc                    [        U 5      nUR                  5       (       d<  [        [        5      R                  5       R                  S   nX!-  R                  5       n[        U5      $ )N   )r   is_absolute__file__resolver   str)path_strpROOTs      [C:\Users\julio\OneDrive\Documentos\Trabajo\Ideas Frescas\Proyectos\Cabanna\modules\drive.py_resolve_sa_pathr   9   sL    XA==??H~%%'//2X q6M    c                    U =(       d    SR                  5       n U R                  S5      =(       a    U R                  S5      $ )N {})strip
startswithendswithss    r   _looks_like_jsonr%   A   s.    	
bA<<0C0r   c                    [        U 5      n U R                  S5      n[        U[        5      (       a7  SU;   a  UR	                  SS5      nUR                  S5      (       d  US-  nXS'   U $ )Nprivate_keyz\n
)dictget
isinstancer   replacer"   )infopks     r   _normalize_private_keyr/   F   sb    :D	-	 B"cB;E4(B{{4  $JB ]Kr   c                    S[         R                  ;   aG  [        [        [         R                  S   5      5      n [        R
                  R                  U [        S9$ [        R                  " S5      nU(       d  [        S5      eUR                  5       R                  SS5      n[        U5      (       aC  [        R                  " U5      n [        U 5      n [        R
                  R                  U [        S9$ [!        U5      n[        R"                  R%                  U5      (       d  [        SU 35      e[        R
                  R'                  U[        S9$ )z
Prioridad:
1) st.secrets["gcp_service_account"] (dict)
2) ENV GDRIVE_SA_JSON como JSON inline
3) ENV GDRIVE_SA_JSON como ruta al .json (local)
gcp_service_account)scopesGDRIVE_SA_JSONz|Falta credencial. Define:
- Streamlit Secrets: [gcp_service_account]
o
- ENV: GDRIVE_SA_JSON (JSON completo o ruta al .json)z
r(   z-No encuentro el JSON del Service Account en: )stsecretsr/   r)   r   Credentialsfrom_service_account_infoSCOPESosgetenvFileNotFoundErrorr    r,   r%   jsonloadsr   pathexistsfrom_service_account_file)r-   raw	json_paths      r   _load_sa_credentials_from_envrC   R   s    

*%d2::6K+L&MN**DDTRXDYY
))$
%CD
 	
 ))+

fd
+Czz#%d+**DDTRXDYY %I77>>)$$"OPY{ [\\&&@@SY@ZZr   c                 .    [        5       n [        SSU SS9$ )Ndrivev3F)credentialscache_discovery)rC   r   )credss    r   get_drive_service_cachedrJ   t   s    )+E$E5IIr   c                     [        5       $ N)rJ    r   r   get_drive_servicerN   z   s    #%%r   iX  F)ttlshow_spinnerc                p    [        5       nUR                  5       R                  U SSS9R                  5       $ )u*   
Metadata mínima para caching/branching.
z"id,name,modifiedTime,mimeType,sizeT)fileIdfieldssupportsAllDrives)rN   filesr*   execute)file_idservices     r   get_drive_metarY      s;    
  !G	7" 
 


 
r   c           	     p   [        5       nSU  S3n0 nSn UR                  5       R                  USSUSSS9R                  5       nUR	                  S/ 5       H?  nUS	   UR	                  S
5      UR	                  S5      UR	                  S5      S.X6S   '   MA     UR	                  S5      nU(       d   U$ M  )zz
Indexa la carpeta 1 vez: name -> {id, modifiedTime, size, mimeType}
Reduce N calls (find por archivo) a 1 list paginada.
'z' in parents and trashed=falseNTz8nextPageToken, files(id,name,modifiedTime,size,mimeType)i  )qrS   pageSize	pageTokenrT   includeItemsFromAllDrivesrU   idmodifiedTimesizemimeType)r`   ra   rb   rc   namenextPageToken)rN   rU   listrV   r*   )	folder_idrX   r\   out
page_tokenresfs          r   index_folder_filesrl      s      !G
I;45ACJ
MMOTQ$"&*.   WY 	 "%Ag !n 5fEE*-	C&	N & WW_-
J5 r   c                V    [        U 5      nUR                  U5      nU(       a  US   $ S $ )Nr`   )rl   r*   )rg   filenameidxrecs       r   find_file_id_in_folderrq      s+    
Y
'C
''(
C3t9%%r   c                    [         R                  " U  SU 3R                  S5      5      R                  5       n[        U S3-  $ )N|utf-8.binhashlibmd5encode	hexdigest	CACHE_DIR)rW   rn   keys      r   _cache_path_forr}      s@    
++	8*-44W=
>
H
H
JC#d|##r   c                    [         R                  " U R                  S5      5      R                  5       n[        U S3-  $ )Nrt   ru   rv   )r|   hs     r   _cache_path_for_genericr      s3    CJJw'(224A!Dz!!r   c                   [        X5      nUR                  S5      nUR                  5       (       aP  UR                  5       (       a;  U(       a4   UR                  5       R	                  5       U:X  a  UR                  5       $  [        5       nUR                  5       R                  U S9n[        R                  " 5       n[        XvSS9nSn	U	(       d  UR                  5       u  pU	(       d  M  UR                  5       n UR                  U5        U(       a  UR!                  U5        U$ ! [         a     Nf = f! [         a     U$ f = f)z
Descarga binarios con cache local por modifiedTime.
IMPORTANTE: SOLO para archivos descargables con get_media (no Google Sheets/Docs).
.meta)rR      	chunksizeF)r}   with_suffixr?   	read_textr    
read_bytes	ExceptionrN   rU   	get_mediaioBytesIOr	   
next_chunkgetvaluewrite_bytes
write_text)rW   rn   ra   r   meta_prX   requestfh
downloaderdone_datas               r   download_file_bytesr      s   
 	*A]]7#Fxxzzfmmoo,	!'')\9||~% :
  !Gmmo''w'7G	B$ROLJD'') d ;;=D	dl+ K-  		&  Ks$   1D( =)D8 (
D54D58
EEc                   SU  SU 3n[        U5      nUR                  S5      nUR                  5       (       aP  UR                  5       (       a;  U(       a4   UR                  5       R	                  5       U:X  a  UR                  5       $  [        5       nUR                  5       R                  U [        S9n[        R                  " 5       n[        XSS9n	Sn
U
(       d  U	R                  5       u  pU
(       d  M  UR                  5       n UR!                  U5        U(       a  UR#                  U5        U$ ! [         a     Nf = f! [         a     U$ f = f)z@
Exporta Google Sheet -> XLSX con cache local por modifiedTime.
zexport_xlsx|rs   r   )rR   rc   r   r   F)r   r   r?   r   r    r   r   rN   rU   export_media	XLSX_MIMEr   r   r	   r   r   r   r   )rW   rn   ra   	cache_keyr   r   rX   r   r   r   r   r   r   s                r   export_sheet_xlsx_bytesr      s2    wiq
3I	*A]]7#Fxxzzfmmoo,	!'')\9||~% :
  !Gmmo**'I*NG	B$ROLJD'') d ;;=D	dl+ K-  		&  Ks$   1D5 
)E 5
EE
EEc                   [        U 5      nUR                  SU 5      nUR                  S5      nUR                  S5      nU[        :X  a  [        XU5      nO[	        XU5      n[
        R                  " [        R                  " U5      US9$ )zr
Lee un Excel desde Drive como DataFrame.
Soporta:
- Google Sheet: exporta a XLSX
- XLSX binario: descarga normal
rd   ra   rc   
sheet_name)	rY   r*   GOOGLE_SHEET_MIMEr   r   pd
read_excelr   r   )rW   r   metarn   ra   rc   contents          r   read_excel_from_driver     su     '"Dxx(H88N+Lxx
#H$$)'\J%gF==G,DDr   ENEROr   FEBRERO   MARZO   ABRIL   MAYO   JUNIO   JULIO   AGOSTO   
SEPTIEMBRE	   OCTUBRE
   	NOVIEMBRE   	DICIEMBRE   ENEFEBMARABRMAY)JUNJULAGOSEPSEPTOCTNOVDICuD   ^\s*(\d{4})\s*[-_/ ]\s*([A-Za-zÁÉÍÓÚÜÑáéíóúüñ\.]+)\s*$c                    [        U 5      R                  5       R                  5       R                  SS5      n SR	                  S [
        R                  " SU 5       5       5      n U $ )N.r   c              3  `   #    U  H$  n[         R                  " U5      (       a  M   Uv   M&     g 7frL   )unicodedata	combining).0cs     r   	<genexpr>'_strip_accents_upper.<locals>.<genexpr>:  s"     \;a;CXCXYZC[;s   .	.NFKD)r   r    upperr,   joinr   	normalizer#   s    r   _strip_accents_upperr   8  sM    A&&sB/A
\;00;\\AHr   c                   [        U 5      n[        R                  U5      nU(       aK  [        UR	                  S5      5      n[        UR	                  S5      5      nU[
        R                  U5      4$ S [
        R                  U5      4$ )Nr   r   )r   _SHEET_YM_REmatchintgroup	MESES_MAPr*   )r   r$   myearmon_raws        r   _parse_sheet_year_monthr   =  sl    Z(A1A1771:&qwwqz2Y]]7+++ )--"##r   c                    U R                    H2  n[        U5      R                  5       R                  5       S:X  d  M0  Us  $    g )NSUCURSAL)columnsr   r    r   )dfr   s     r   _detect_sucursal_colr   H  s5    ZZq6<<>!Z/H  r   c                    / nU R                    Hb  n[        U5      R                  5       nUR                  5       (       d  M3  [	        U5      nSUs=::  a  S::  d  MM  O  MQ  UR                  U5        Md     U$ )Nr      )r   r   r    isdigitr   append)r   day_colsr   r$   ds        r   _detect_day_colsr   N  sX    HZZFLLN99;;AAA|||"  Or   c           	     L   [         R                  " U 5      n/ n[        5       n/ nUR                   H<  n[	        U5      u  pgUc  M  Ub  UR                  U5        UR                  XVU45        M>     [        U5      S:  nU GH  u  pYn
U	c	  U(       a  M  [         R                  " XS9n[        U5      nUc  M7  [        U5      nU(       d  MK  X/U-      R                  5       nUR                  US0S9nUS   R                  [        5      R                  R                  5       US'   UR!                  S/USSS9nUS   R                  [        5      R                  R                  5       R                  ["        5      US'   [         R$                  " US   SS	9US'   U	c  GM  [         R&                  " [)        XUS   S
9SS	9US'   UR+                  SS/S9nUS   R-                  S5      R                  ["        5      US'   UR                  U/ SQ   5        GM     U(       d  [         R.                  " / SQS9$ [         R0                  " USS9nUS   R                  R3                  SSSS9R                  R                  5       US'   UR5                  SS/5      R7                  SS9nU$ )u   
Hojas soportadas:
  - "2025-Enero", "2025 Enero", "2025_Ene", etc.
  - (compat) "ENERO"... "DICIEMBRE" (sin año) -> solo se usa si NO hay años explícitos.

Formato por hoja:
  SUCURSAL | 1 | 2 | ... | 31

Output:
  fecha | NombreSucursal | comensales
r   r   NombreSucursalr   dia
comensales)id_vars
value_varsvar_name
value_namecoerceerrors)r   monthdayfecha)subsetr   )r   r   r   T)ignore_index\s+ regex)drop)r   	ExcelFilesetsheet_namesr   addr   lenr   r   r   copyrenameastyper   r    meltr   
to_numericto_datetimer)   dropnaround	DataFrameconcatr,   sort_valuesreset_index)excel_bytes_or_pathxlsframesyears_detectedparsedsheetyr   has_explicit_yearsr   mes_numr   col_sucr   tmprh   s                   r   transformar_comensales_excelr!  X  s    ,,*
+C!#FUN02F&u-9=q!um$ ! ^,1 &W<.]]31&r*?#B'X%&++-jj'+;!<j= #$4 5 < <S A E E K K Mhh%&#	  
 Z&&s+//557>>sCE
MM#l*;HML <~~ds5z:
G
 jj, 7j8-33A6==cBLcCDEQ !'T ||$MNN
))F
.C 0155==fcQU=VZZ``bC
//7$45
6
B
B
B
MCJr   z)Cargando comensales desde Google Drive...)rP   c           
        [        U 5      nUR                  SU 5      nUR                  S5      nUR                  S5      nU[        :X  a  [        XU5      nO[	        XU5      n[        [        R                  " U5      5      nUR                  (       a  U$ SUR                  ;   a!  SUR                  ;  a  UR                  SS0S9nSUR                  ;   a  XfS   R                  S5         n[        R                  " US   S	S
9R                  R                  5       US'   SSSSSSSSSS.	nUS   R!                  S5      nUR"                  R%                  SSSS9R"                  R'                  5       R%                  U5      R"                  R)                  5       R!                  S5      US'   [        R*                  " US   S	S
9US'   U$ )Nrd   ra   rc   r   Fechar   r   TOTALr   r   z	CD JUAREZCULIACANz	AV MEXICOGOURMETERIAMEXICALIMETROPOLITANPOLANCOPUEBLATIJUANA)	u   Cd. Juárezu	   Culiacánu   Guadalajara - Av. Méxicou   Guadalajara - GourmeteríaMexicali	MonterreyPolancoPueblaTijuanastringr   r  Tr  categoryr   )rY   r*   r   r   r   r!  r   r   emptyr   r  ner   r  dtr   r  r   r,   r    r   r  )	rW   r   rn   ra   rc   r   r   MAP_SUCr$   s	            r   load_comensalesr7    s   '"Dxx(H88N+Lxx
#H$$)'\J%gF	%bjj&9	:B	xx	 "**

!:YY1Y22::%#$''01..GX>AAKKMBwK #%0&3#
G 	##H-A	fc.
#eeg
''

#eeg
&
  }}R%5hGB|Ir   c              #     #    U R                  5       R                  SS9n[        R                  " U5      nX!:  a<  Uv   U[        R                  R                  S5      -   R                  5       nX!:  a  M;  g g 7fNr   )r   r   r,   r   r  offsets
MonthBeginstartendcurs      r   _months_betweenrA    `     
//

#
#
#
*C
..
C
)	RZZ**1--88: )   A2A86A8zCargando datos de ventas...)rP   rO   max_entriesc           	        [         R                  " U 5      n[         R                  " U5      n[        (       d  [        S5      e[	        [        5      nSSUR                  5       4SSUR                  5       4/n/ n[        X#5       H  nSUR                  S5       S3nUR                  U5      n	U	(       d  M2  U	S   n
U	R                  S	5      n[        XU5      nU(       d  M]   [        R                  " [        R                  " U5      US
S9nUc  M  UR,                  (       d  M  UR%                  U5        M     U(       d  [         R.                  " 5       $ [&        R0                  " US
S9nUR3                  S
S
S9nSUR                   ;   a  [         R                  " US   SS9US'   U$ ! [         a    [         R                  " [        R                  " U5      5      nSUR                   ;   a.  [         R                  " US   SS9US'   XS   U:  US   U:  -     nUR"                  (       d-  UR%                  [&        R(                  R+                  USS95         GM  f = f)Nz<Falta FOLDER_ID_FOLIOS (ID de la carpeta 'folios' en Drive).r#  >=<folios_%Y_%m.parquetr`   ra   Tfiltersuse_threadsr   r   Fpreserve_indexpromoteself_destructsplit_blocks)r   r  r   RuntimeErrorrl   to_pydatetimerA  strftimer*   r   pq
read_tabler   r   r   read_parquetr   r3  r   paTablefrom_pandasnum_rowsr  concat_tables	to_pandas)ini_qfin_qstart_dtend_dtro   rL  tablesr   fnamerp   rW   ra   r   tbldf_mout_tblr   s                    r   load_foliosrj    s   ~~e$H^^E"FYZZ
-
.C 
$..01	#v++-.G
  FX.!**W-.h7ggend)ww~.%glC
	--

7 3WRVWC ?s|||MM#5 /8 ||~vt4G			D		AB"**nnR[B7I-  	??2::g#67D$,,& "tG}X NW']h64=6;QRS::bhh2242NO	s   #*F((B0IIc              #     #    U R                  5       R                  SS9n[        R                  " U5      nX!:  a<  Uv   U[        R                  R                  S5      -   R                  5       nX!:  a  M;  g g 7fr9  r:  r=  s      r   rA  rA    rB  rC  u)   Indexando parquets de líneas en Drive...c                      [        [        5      $ rL   )rl   r   rM   r   r   _lineas_indexrm  $  s     .//r   u   Cargando líneas desde Drive...c                   [         R                  " U 5      n[         R                  " U5      n[        5       nSSUR                  5       4SSUR                  5       4/n/ n[	        X#5       H  nSUR                  S5       S3nUR                  U5      n	U	(       d  M2  U	S   n
U	R                  S5      n[        XU5      nU(       d  M]   [        R                  " [        R                  " U5      US	S
9nUb"  UR                  (       a  UR                  U5        M     U(       d  [         R*                  " 5       $ [$        R,                  " US	S9nUR/                  S	S	S9nSUR                   ;   a  [         R                  " US   SS9US'   U$ ! [         a     Of = f[         R                  " [        R                  " U5      5      nSUR                   ;   a.  [         R                  " US   SS9US'   XS   U:  US   U:  -     nUR"                  (       a  GM  UR                  [$        R&                  R)                  USS95        GM  )Nr#  rF  rG  lineas_rI  rJ  r`   ra   TrK  r   r   FrN  rP  rR  )r   r  rm  rV  rA  rW  r*   r   rX  rY  r   r   r^  r   r   rZ  r   r3  r[  r\  r]  r  r_  r`  )ra  rb  rc  rd  ro   rL  re  r   rf  rp   rW   ra   r   rg  rh  ri  rh   s                    r   load_lineasrp  *  s   ~~e$H~~e$F
/C 
$..01	#,,./G
  FX.!**W-.h7ggend)ww~.%glC	--

7 3WRVWC3<<c"% /@ ||~vt4G 

$T

BC#++~~c'l8DGJ1  		 rzz'23dll"NN4=JDMg(2tG}v7MNODzzzMM"((..tE.JKs   AF		
FF)r   r   returnr   )r$   r   rq  bool)r-   r)   rq  r)   )rW   r   rq  zdict[str, Any])rg   r   rq  dict[str, dict])rg   r   rn   r   rq  
str | None)rW   r   rn   r   rq  r   )r|   r   rq  r   )rW   r   rn   r   ra   rt  rq  bytes)r   )rW   r   r   z	str | intrq  pd.DataFrame)r$   r   rq  r   )r   r   rq  ztuple[int | None, int | None])r   rv  rq  rt  )r   rv  rq  z	list[Any])rq  rv  )rW   r   rq  rv  )r>  pd.Timestampr?  rw  rq  zIterator[pd.Timestamp])r>  rw  r?  rw  )rq  rs  )E__doc__
__future__r   r   r9   rer<   rw   r   pathlibr   typingr   r   pandasr   	streamlitr4   pyarrowr[  pyarrow.parquetparquetrX  google.oauth2r   googleapiclient.discoveryr   googleapiclient.httpr	   modules.configr
   r8   r   r   r{   mkdirr   r   r   r   r%   r/   rC   cache_resourcerJ   rN   
cache_datarY   rl   rq   r}   r   r   r   r   r   compileUNICODEr   r   r   r   r   r!  r7  rA  rj  rm  rp  rM   r   r   <module>r     s  $ # 	 	 	           ) + 4 &
 ;	;= O	+,	 	t ,12 12 	Y	1
	[D J J
& 3U+ ,  3U+% ,%P&$
"
"J"NE,Q1%q*116<aAH!Q!)1.7=H"NY[] 
1 Q q #( -21 >?Qq!Br"		 zzacecmcmn
	$L\ GH. I.f; 9sPQR5 S5t; GS^_`0 a0
 =>9 ?9r   