o
    ÑÞ›h–  ã                   @   sú  d dl Z d dlZd dlmZmZmZmZmZ d dlm	Z	m
Z
 de
fdd„Zdeeeef  defdd	„Zd
Zdedeeeef  fdd„Zdedee ddfdd„Zdeddfdd„Zdee fdd„Zdedefdd„Zd7dedededB ddfdd„Zdedefdd„Z	d8deded eded!edB dedB ddfd"d#„Zd$ee dee fd%d&„Zd'ed(edefd)d*„Zd+edefd,d-„Zddddd.œd+ed'ed(edee d!ee dee d/ee ddfd0d1„Zd'eddfd2d3„Zd4eeeef  ddfd5d6„ZdS )9é    N)ÚListÚDictÚAnyÚOptionalÚIterable)Úcreate_clientÚClientÚreturnc                  C   s.   t  d¡} t  d¡}| r|stdƒ‚t| |ƒS )NÚSUPABASE_URLÚSUPABASE_KEYu%   SUPABASE_URL / SUPABASE_KEY ë¯¸ì„¤ì •)ÚosÚgetenvÚRuntimeErrorr   )ÚurlÚkey© r   ú5/var/www/html/govbot/app/services/supabase_service.pyÚget_supabase   s
   


r   Úitemsc           
      C   s2  | sdS t ƒ }tdd„ | D ƒƒ}tdd„ | D ƒƒ}|r|sdS | d¡ d¡ d|¡ d|¡ ¡ }td	d
„ |jp9g D ƒƒ}g }tj 	¡  
¡ d }| D ]9}| d¡| d¡f}	|	d rb|	d rb|	|v rcqJ| |d |d | dd¡| dd¡| d¡| di ¡|dœ¡ qJ|sˆdS t ƒ  d¡j|dd ¡  t|ƒS )Nr   c                 S   ó   h | ]}|  d ¡r|d  ’qS )Úsource©Úget©Ú.0Úir   r   r   Ú	<setcomp>   ó    z&upsert_appointments.<locals>.<setcomp>c                 S   r   )Ú	source_idr   r   r   r   r   r      r   Úgov_appointmentszsource,source_idr   r   c                 s   s     | ]}|d  |d fV  qdS )r   r   Nr   ©r   Úrr   r   r   Ú	<genexpr>   s   € z&upsert_appointments.<locals>.<genexpr>ÚZé   ÚtitleÚ r   Úpublished_atÚextra)r   r   r%   r   r'   r(   Ú
crawled_at©Úon_conflict)r   ÚlistÚtableÚselectÚin_ÚexecuteÚsetÚdataÚdatetimeÚutcnowÚ	isoformatr   ÚappendÚupsertÚlen)
r   ÚsbÚsourcesÚ
source_idsÚexistingÚexisting_pairsÚ	new_itemsÚnowr   Úpairr   r   r   Úupsert_appointments   sB   û



ù
rA   Úmoef_subscribersÚchat_idc                 C   sB   t ƒ }| t¡ d¡ d| ¡ d¡ ¡ }|jpg }|r|d S d S )NÚ*rC   r$   r   )r   r-   Ú	SUB_TABLEr.   ÚeqÚlimitr0   r2   )rC   r9   ÚresÚrowsr   r   r   Úget_subscriber:   s   "
rJ   Ú
first_namec                 C   s,   t ƒ }| |dœ}| t¡j|dd ¡  d S )N)rC   rK   rC   r*   )r   r-   rE   r7   r0   )rC   rK   r9   Úpayloadr   r   r   Úadd_subscriber@   s
   þrM   c                 C   s$   t ƒ }| t¡ ¡  d| ¡ ¡  d S )NrC   )r   r-   rE   ÚdeleterF   r0   )rC   r9   r   r   r   Údelete_subscriberI   ó   rO   c                  C   s.   t ƒ } |  t¡ d¡ ¡ }dd„ |jpg D ƒS )NrC   c                 S   s   g | ]}t |d  ƒ‘qS )rC   )Úintr    r   r   r   Ú
<listcomp>P   s    z'get_active_chat_ids.<locals>.<listcomp>)r   r-   rE   r.   r0   r2   )r9   rH   r   r   r   Úget_active_chat_idsM   s   rS   Ú
article_idc                 C   ó2   t ƒ }| d¡ d¡ d| ¡ d¡ ¡ }t|jƒS )NÚmotie_idÚidr$   ©r   r-   r.   rF   rG   r0   Úboolr2   )rT   r9   rH   r   r   r   Úhas_motie_idS   ó   "
rZ   r%   Ú	posted_atc                 C   s4   t ƒ }| |dœ}|r||d< | d¡ |¡ ¡  d S )N)rW   r%   r\   rV   ©r   r-   Úinsertr0   )rT   r%   r\   r9   rL   r   r   r   Úinsert_motie_idX   s
   
r_   Úitem_idc                 C   rU   )NÚmoef_idrW   r$   rX   )r`   r9   rH   r   r   r   Úhas_moef_id`   r[   rb   Úbbs_idÚpost_idÚtagc                 C   sL   t ƒ }| |||dœ}|d ur||d< |d ur||d< | d¡ |¡ ¡  d S )N)rW   ÚbbsIdÚpostIdr%   re   r\   ra   r]   )r`   rc   rd   r%   re   r\   r9   rL   r   r   r   Úinsert_moef_ide   s   ürh   Údc           	      C   s¾   | sdS t | ƒ ¡ }| dd¡ dd¡}|dd…  d¡}t|ƒdkr%dS |\}}}| ¡ r6| ¡ r6| ¡ s8dS zt|ƒt|ƒt|ƒ}}}|d›d|d›d|d›W S  ty^   Y dS w )	u^   
    'YYYY.MM.DD' / 'YYYY-MM-DD' / 'YYYY/MM/DD' -> 'YYYY-MM-DD'
    ê·¸ ì™¸/None ì€ None
    NÚ.Ú-ú/é
   é   Ú04dÚ02d)ÚstrÚstripÚreplaceÚsplitr8   ÚisdigitrQ   Ú	Exception)	ri   ÚsÚpartsÚyÚmÚddÚy2Úm2Úd2r   r   r   Ú_norm_date_to_iso{   s    
ÿr   Ú
departmentÚdisclosure_noc                 C   s:   t ƒ }| d¡ d¡ d| ¡ d|¡ d¡ ¡ }t|jƒS )NÚkepco_idrW   r€   r   r$   rX   )r€   r   r9   rH   r   r   r   Úkepco_unique_exists   s   ú
rƒ   Úcompound_idc                 C   rU   )Nr‚   rW   r$   rX   )r„   r9   rH   r   r   r   Úhas_kepco_idœ   r[   r…   )r%   re   r\   Úpdf_urlr†   c           	      C   st   | st dƒ‚|st dƒ‚|st dƒ‚| |||pd||rt|ƒnd|p&d|› dœ}tƒ }| d¡j|d	d
 ¡  dS )uœ   
    kepco_idì— (department, disclosure_no) ê¸°ì¤€ ë©±ë“± upsert.
    NOT NULL í•„ë“œ ëˆ„ë½ì„ ì ˆëŒ€ ë§Œë“¤ì§€ ì•Šë„ë¡ ê¸°ë³¸ê°’/ì •ê·œí™” ì²˜ë¦¬.
    zcompound_id requiredzdepartment requiredzdisclosure_no requiredu   ìž„ì›í˜„í™©Nz2https://alio.go.kr/download/pdf.json?disclosureNo=)rW   r€   r   r%   re   r\   r†   r‚   zdepartment,disclosure_nor*   )Ú
ValueErrorr   r   r-   r7   r0   )	r„   r€   r   r%   re   r\   r†   rL   r9   r   r   r   Úupsert_kepco_id¡   s    ù
rˆ   c                 C   s$   t ƒ }| d¡ ¡  d| ¡ ¡  d S )NÚ	kepco_orgr€   )r   r-   rN   rF   r0   )r€   r9   r   r   r   Úclear_kepco_org_by_departmentÃ   rP   rŠ   rI   c                 C   sê   t | ƒ} | sd S g }| D ]F}| d¡}t|t ƒr#d dd„ |D ƒ¡}n|d ur-t|ƒ ¡ nd }| | d¡| d¡| d¡| d¡| d	¡| d
¡| d¡|dœ¡ qtƒ }d}tdt	|ƒ|ƒD ]}| 
d¡ |||| … ¡ ¡  q`d S )NÚcareerÚ
c                 s   s(    | ]}t |ƒ ¡ rt |ƒ ¡ V  qd S ©N)rq   rr   )r   Úxr   r   r   r"   Ð   s   €& z(insert_kepco_org_rows.<locals>.<genexpr>r€   ÚnameÚpositionÚgenderÚstartÚendÚtask)r€   r   r   r‘   r’   r“   r”   r‹   iô  r   r‰   )r,   r   Ú
isinstanceÚjoinrq   rr   r6   r   Úranger8   r-   r^   r0   )rI   Únormedr!   r‹   Úcareer_textr9   ÚCHUNKr   r   r   r   Úinsert_kepco_org_rowsÇ   s0   


ø"ÿr›   r   )NN)r   r3   Útypingr   r   r   r   r   Úsupabaser   r   r   rq   rQ   rA   rE   rJ   rM   rO   rS   rY   rZ   r_   rb   rh   r   rƒ   r…   rˆ   rŠ   r›   r   r   r   r   Ú<module>   s^    *	 ÿÿÿ
ÿ	ùÿþýüûúù
ù""