o
    zÌÈh“Q  ã                   @   s€  d Z ddl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	m
Z
 ddlmZmZ ddlZddlmZ ddlmZ ejejdd e d	¡Zd
Ze› dZe› dZe› dZddddœZded iZe d¡Ze d¡Zde
e defdd„Z dedefdd„Z!dedededefdd„Z"dededed ed!edefd"d#„Z#ddd$d%d&œd'ej$d(ed)efd*d+„Z%d,d-„ Z&dmd.d/„Z'd0ede(fd1d2„Z)dee fd3d4„Z*d'ej$d)ed5edee fd6d7„Z+d8edee fd9d:„Z,d'ej$d5ed;ed<ed=ed>ede
e fd?d@„Z-d'ej$dAede	ee e
e ef fdBdC„Z.d'ej$de/fdDdE„Z0d'ej$dFe/de	ee ef fdGdH„Z1de/fdIdJ„Z2dKe/dLe/dMe/dNe3de	e(ef f
dOdP„Z4dQe
e dRe
e defdSdT„Z5dQe
e dRe
e defdUdV„Z6dWee dee fdXdY„Z7dZd[œd\edWee d]e/fd^d_„Z8d`edaedWee dee fdbdc„Z9d`edWee dee fddde„Z:dndie3dMe/dNe3defdjdk„Z;e<dlkr¾e=e;ƒ ƒ dS dS )ouJ  
MOTIE ì¡°ì§ë„ í¬ë¡¤ëŸ¬(ìš´ì˜) â€” /view + empSearch ë³‘í–‰ ìˆ˜ì§‘
- ë©”ì¸(ì¡°ì§ë„)ì—ì„œ jsSearchOrgan(...) 4ê°œ ì¸ìž ì¶”ì¶œ â†’ POST /view ë¡œ ì§ì›í‘œ íŒŒì‹±
- empSearch ì „ì²´ íŽ˜ì´ì§€ í¬ë¡¤ë§ ë³‘í–‰ â†’ /view ë¯¸ë…¸ì¶œ ì¸ì› ë³´ê°•(íŒŒê²¬ ë“±)
- ìŠ¤í…Œì´ì§•ì—ì„œ dedupe â†’ finalize_motie_run ë¡œ SCD2 ë°˜ì˜
é    N)ÚListÚDictÚTupleÚOptional)ÚdatetimeÚtimezone)ÚBeautifulSoup)Ú
get_clientz[%(levelname)s] %(message)s)ÚlevelÚformatÚmotie_org_pipelinezhttps://www.motie.go.krz/kor/26/headquartersz/kor/28/institutionz/kor/25/empSearchz*GovBot/3.0 (+https://work.jjickjjicks.com)z?text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8z#ko-KR,ko;q=0.9,en-US;q=0.8,en;q=0.7)ú
User-AgentÚAcceptzAccept-Languager   z\d{2,4}-\d{3,4}-\d{4}z8jsSearchOrgan\('([^']*)','([^']*)','([^']*)','([^']*)'\)ÚsÚreturnc                 C   s"   d  | pd dd¡ dd¡ ¡ ¡S )Nú Ú õ   Â u   â€‹)ÚjoinÚreplaceÚsplit©r   © r   ú3/var/www/html/bot/app/crawler/motie_org_pipeline.pyÚ_clean1   s   "r   c                 C   s   t | ƒS ©N)r   r   r   r   r   Ú_norm4   s   r   ÚnameÚpositionÚ
departmentc                 C   s2   t | ƒ› dt |ƒ› dt |ƒ› }t | ¡ ¡ ¡ S ©Nú|©r   ÚhashlibÚsha256ÚencodeÚ	hexdigest)r   r   r   Úbaser   r   r   Ú	_key_hash7   s    r(   ÚtaskÚphonec              
   C   sF   t | ƒ› dt |ƒ› dt |ƒ› dt |ƒ› dt |ƒ› 	}t | ¡ ¡ ¡ S r    r"   )r   r   r   r)   r*   r'   r   r   r   Ú	_row_hash;   s   4r+   é   g333333ã?)ÚheadersÚdataÚmax_tryÚsleepÚsessÚmethodÚurlc                K   sÐ   d }t tƒ}	|	 |pi ¡ td|d ƒD ]Q}
z)| ¡ dkr+| j|f|	|ddœ|¤Ž}n| j|f|	ddœ|¤Ž}| ¡  |W   S  tye } z|}t	 
d| ¡ |
||¡ t ||
 ¡ W Y d }~qd }~ww |‚)Né   ÚPOSTé   )r-   r.   Útimeout)r-   r7   z%s failed(%d/%d): %s)ÚdictÚBASE_HEADERSÚupdateÚrangeÚupperÚpostÚgetÚraise_for_statusÚ	ExceptionÚloggerÚwarningÚtimer0   )r1   r2   r3   r-   r.   r/   r0   ÚkwÚlastÚhÚiÚrÚer   r   r   Ú_requestB   s    
€ýrJ   c                 K   s   t | d|fi |¤ŽS )NÚGET©rJ   )r1   r3   rD   r   r   r   Ú_getS   s    rM   c                 K   s   t | d|fd|i|¤ŽS )Nr5   r.   rL   )r1   r3   r.   rD   r   r   r   Ú_postT   s    rN   Útxtc                 C   s   t | ƒ}t|ƒo| d¡S )N)u   ê³¼u   ê´€u   êµ­u   ì‹¤u   íŒ€)r   ÚboolÚendswith)rO   Útr   r   r   Ú_is_org_nameY   s   rS   c           
      C   s¼   g | j d}}}|d urC|dk rCdD ] }|j|ddD ]}t| ¡ ƒ}t|ƒr2||vr2| d|¡ qq|j }|d7 }|d urC|dk sg tƒ }}|D ]}	|	|vr[| |	¡ | |	¡ qK|S )Nr   é   )ÚstrongÚspanÚpÚaF)Ú	recursiver4   )	ÚparentÚfind_allr   Úget_textrS   ÚinsertÚsetÚaddÚappend)
rX   ÚpathÚelÚhopsÚselÚxÚtxÚoutÚseenrW   r   r   r   Ú_infer_path]   s"   €ýú€ri   Úscopec              	      sÚ   t | |d|idj}t|dƒ}i }| d¡D ]E}t| ¡ ƒ}t|ƒs#q| d¡p)d}t 	|¡‰ ˆ s2q‡ fdd„t
d	d
ƒD ƒ\}	}
}}||	|
||f}||vr[||	|
||t|ƒ|dœ||< qt| ¡ ƒ}t d|t|ƒ¡ |S )NÚReferer©r-   úhtml.parserza[onclick*="jsSearchOrgan"]Úonclickr   c                 3   s    | ]
}ˆ   |¡ ¡ V  qd S r   )ÚgroupÚstrip)Ú.0rG   ©Úmr   r   Ú	<genexpr>y   s   € z"_parse_org_tree.<locals>.<genexpr>r4   é   )r   ÚdeptCdVÚdepth1IdÚdepth2IdÚdepth3Idra   rj   z[%s] targets: %d)rM   Útextr   Úselectr   r\   rS   r>   ÚJS_ORGAN_REÚsearchr;   ri   ÚlistÚvaluesrA   ÚinfoÚlen)r1   r3   rj   ÚhtmlÚsoupÚtargetsrX   r   Úocrv   rw   rx   ry   Úkeyrg   r   rr   r   Ú_parse_org_treel   s4   

 
ù€	r‡   r‚   c                 C   s0  t | dƒ}| d¡}|r| d¡n	| d¡p| d¡}|sg S g }| d¡D ]o}| d¡}t|ƒdk r4q&t|d  ¡ ƒ}t|d	  ¡ ƒ}t|d
 jdddƒ}	|d }
|
 d¡}|rk| d¡rk| d¡ 	dd¡ 
¡ }nt|
jdddƒ}t |¡}|r€| d¡n|}t|||	|gƒs‹q&| |||	|dœ¡ q&|S )Nrm   zh4.tit-type02Útablez.data-tbl tableztbody trÚtdé   r   r4   é   r   T©rp   r,   úa[href^="tel:"]Úhrefútel:r   )r   r   r)   r*   )r   Ú
select_oneÚ	find_nextr{   r[   r   r   r\   r>   r   rp   ÚPHONE_REr}   ro   Úanyr`   )r‚   rƒ   Úanchorrˆ   ÚrowsÚtrÚtdsr   r   r)   Ú
phone_cellÚtel_ar*   Ú	last_textrs   r   r   r   Ú_parse_detail_table_from_htmlŒ   s0   

"


r›   rv   rw   rx   ry   c                C   sˆ   |dkrt › d}t}nt › d}t}||||dœ}|ddœ}	t d||¡ zt| |||	dj}
t|
ƒr7|
W S W d S  tyC   Y d S w )	NÚheadquartersz/kor/26/headquarters/viewz/kor/28/institution/view©rv   rw   rx   ry   z0application/x-www-form-urlencoded; charset=UTF-8)rk   zContent-Typez[try:POST view] %s %s)r.   r-   )	ÚBASEÚHEADQUARTERS_URLÚINSTITUTION_URLrA   r€   rN   rz   r›   r@   )r1   rj   rv   rw   rx   ry   Úview_urlÚrefererÚformr-   r‚   r   r   r   Ú
_post_viewª   s0   

üþÿþþr¤   Údeptc              
   C   sŠ   |d › d|d › d|d › d|d › d|d	 › 	}t | |d |d |d |d |d	 d
}|s5g d|fS t|ƒ}|s@g d|fS |||fS )zAdept: {"name","scope","deptCdV","depth1Id","depth2Id","depth3Id"}rj   z/view POST deptCdV=rv   z, depth1Id=rw   z, depth2Id=rx   z, depth3Id=ry   r   N)r¤   r›   )r1   r¥   Útriedr‚   r•   r   r   r   Ú_fetch_dept_detailÆ   s   4ú


r§   c                 C   sV   t | ttd}t|jdƒ}| d¡}|sdS t d| dd¡¡}|r)t	| 
d¡ƒS dS )Nrl   rm   za.direction.lastr4   zempSearch\.list\((\d+)\)rn   r   )rM   ÚEMP_BASEÚEMP_HEADERSr   rz   r   Úrer}   r>   Úintro   )r1   rH   rƒ   rE   rs   r   r   r   Ú_emp_fetch_last_pageÛ   s   
r¬   Úpagec              	      s  t › d|› }t| |td}|j}t|dƒ}g }| d¡D ]å}| d¡}t|ƒdk r+qt|d j	dd	ƒ}	t|d
 j	dd	ƒ}
|d }d}ddh‰ | d¡}|D ]}t|j	dd	ƒ}|rd|ˆ vrd|} nqP|s‚t|j	ddd	ƒ}|r‚‡ fdd„| 
¡ D ƒ}d |¡}t|ƒ}|d }| d¡}|r¢| d¡r¢| d¡ dd¡ ¡ }nt|j	ddd	ƒ}t |¡}|r·| d¡n|}t|ƒdkrÏd dd„ |dd… D ƒ¡ ¡ nd}|rÝ|rÝ| |d¡ ¡ }|dkríd|v rít d|	|
¡ qt|	|
|||gƒs÷q| |	|
|||dœ¡ q||fS )Nz?pageIndex=rl   rm   ztable tbody trr‰   rŠ   r   TrŒ   r4   r‹   r   u   ë¶€ì„œì†Œê°œu	   ì¡°ì§ë„rX   r   c                    s   g | ]
}|r|ˆ vr|‘qS r   r   ©rq   rW   ©Ú
bad_labelsr   r   Ú
<listcomp>  ó    z#_emp_parse_page.<locals>.<listcomp>éÿÿÿÿr   rŽ   r   c                 s   s"    | ]}t |jd ddƒV  qdS )r   TrŒ   N)r   r\   )rq   r‰   r   r   r   rt     s   €  z"_emp_parse_page.<locals>.<genexpr>r,   u   íŒŒê²¬u   ê¸°íšìž¬ì •ë¶€u   skip íŒŒê²¬Â·ê¸°ìž¬ë¶€: %s/%s)r   r   r   r)   r*   )r¨   rM   r©   rz   r   r{   r[   r   r   r\   r   r   r   r>   r   rp   r’   r}   ro   rA   Údebugr“   r`   )r1   r­   r3   rH   r‚   rƒ   r•   r–   r—   r   r   Údep_tdr   Úa_listrX   ÚatÚdep_textÚpartsÚphone_tdr™   r*   rš   rs   r)   r   r¯   r   Ú_emp_parse_pageä   sb   


€


 ÿýÿr»   c                 C   s:   z|   d¡jddd ¡ }|jpdW S  ty   Y dS w )u>   í˜„ìž¬(open) ìŠ¤ëƒ…ìƒ· ìˆ˜ â€” ë·°(motie_org_cur)ì—ì„œ ì½ê¸°Úmotie_org_curÚkey_hashÚexact)Úcountr   )rˆ   r{   Úexecuter¿   r@   )ÚsbÚresr   r   r   Ú
_count_cur%  s   ÿrÃ   Ú	stg_countÚ	cur_countÚmin_absÚ	min_ratioc                C   s\   | |k rdd| › d|› fS |dkr,| t || ƒk r,dd| › dt || ƒ› d|› dfS dS )	NFztoo_few_rows: z < r   zratio_drop: z (cur=ú))TÚok)r«   )rÄ   rÅ   rÆ   rÇ   r   r   r   Ú	_validate-  s
   $rÊ   rX   Úbc                 C   sl   dd„ | pd  d¡D ƒdd„ |pd  d¡D ƒ }tƒ g }}|D ]}||vr0| |¡ | |¡ q d |¡S )Nc                 S   s   g | ]
}|  ¡ r|  ¡ ‘qS r   rŒ   r®   r   r   r   r±   8  r²   z _merge_tasks.<locals>.<listcomp>r   ú/z / )r   r^   r_   r`   r   )rX   rË   r¹   rh   rg   rW   r   r   r   Ú_merge_tasks7  s   0€
rÍ   c                 C   s|   | pd  ¡ } |p	d  ¡ }tt | ¡ƒ}tt |¡ƒ}|r |s | S |r&|s&|S | s,|r,|S |s2| r2| S t| ƒt|ƒkr<| S |S )Nr   )rp   rP   r’   Ú	fullmatchr   )rX   rË   Úa_okÚb_okr   r   r   Ú_pick_better_phone?  s   rÑ   r•   c              
   C   s²   i }| D ]/}|d }||vrt |ƒ||< q|| }t| d¡| d¡ƒ|d< t| d¡| d¡ƒ|d< q| ¡ D ]}t|d |d |d | dd¡| dd¡ƒ|d< q8t| ¡ ƒS )	Nr½   r)   r*   r   r   r   r   Úrow_hash)r8   rÍ   r>   rÑ   r   r+   r~   )r•   Úby_keyrH   Úkr'   Úvr   r   r   Ú_dedupe_stage_rowsN  s   &
ÿrÖ   éè  ©Úchunkrˆ   rÙ   c                C   sD   t dt|ƒ|ƒD ]}|||| … }|sq|  |¡ |¡ ¡  qd S )Nr   )r;   r   rˆ   ÚupsertrÀ   )rÁ   rˆ   r•   rÙ   rG   Úpartr   r   r   Ú_chunked_upsert^  s   ürÜ   Úrun_idÚ	dept_namec                 C   s\   g }|D ]%}t |d |d |ƒ}| | |d |d || dd¡| dd¡|dœ¡ qt|ƒS )Nr   r   r)   r   r*   ©rÝ   r   r   r   r)   r*   r½   )r(   r`   r>   rÖ   )rÝ   rÞ   r•   ÚstagedrH   rÔ   r   r   r   Ú_prepare_stage_batch_from_viewe  s   

ùÿrá   c                 C   sh   g }|D ]+}|  dd¡}t|d |d |ƒ}| | |d |d ||  dd¡|  dd¡|dœ¡ qt|ƒS )Nr   r   r   r   r)   r*   rß   )r>   r(   r`   rÖ   )rÝ   r•   rà   rH   ÚdeprÔ   r   r   r   Ú_prepare_stage_batch_from_empv  s   

ùÿrã   çš™™™™™É?éÈ   çš™™™™™¹?Ú	sleep_secc           %         sl  t ƒ }tt ¡ ƒ}| d¡ |dddœ¡ ¡  d}d}zMt ¡ Û}t	|t
dƒ}t	|tdƒ}	||	 }
|
D ]z}|d }t||ƒ\}}}|sp|rpz | d	¡ |d
|d › d|d pX|› |d d… dœ¡ ¡  W n	 tyo   Y nw |rˆt|||ƒ}|rˆt|d|dd |t|ƒ7 }|d ur¥| d	¡ ||d › d|d pœ|› |dœ¡ ¡  |d7 }t | ¡ q4t|ƒ}td|d ƒD ]:}t||ƒ\}}| d	¡ |d|› |dœ¡ ¡  |rët||ƒ}|rët|d|dd |t|ƒ7 }|d7 }t d¡ qºW d   ƒ n1 sÿw   Y  | d¡ ||dd|› dœ¡ ¡  t|ƒ}t||||d\}}|saˆ  tj¡ ¡ }| d¡ d||||dœ¡ d|¡ ¡  | d¡ d|dœ¡ d|¡ ¡  t  !d |¡ |d|d!œW S | "d"d#|i¡ ¡ }t  #d$|j$¡ z­| d	¡ %d%¡ d|¡j&d%d&d' 'd(¡ ¡ j$pŒg }|r|d  (d%¡}|d)  (d%¡}dd*l)m)‰ m*} d+tf‡ fd,d-„}||ƒ} ||ƒ}!| r|!r| |d.d/  ¡ }"|!|d.d/  ¡ }#z| d0¡ d1|i¡ +d1d2¡ ,d3|"¡ -d3|#¡ ¡  W n
 tyó   Y nw z| d0¡ d4|i¡ +d4d2¡j. +d5d2¡ ,d5|"¡ -d5|#¡ ¡  W n
 ty   Y nw W n ty.   t  /d6¡ Y nw ˆ  tj¡ ¡ }| d¡ d7d8i¡ d|¡ ¡  | d¡ d8|||d9œ¡ d|¡ ¡  t  #d:|||¡ |d8||d;œW S  tyµ }$ z?ˆ  tj¡ ¡ }z| d¡ d<|||t|$ƒdœ¡ d|¡ ¡  W n
 tyœ   Y nw t  0d=|$¡ |d<t|$ƒd>œW  Y d }$~$S d }$~$ww )?NÚcrawler_runÚ	motie_orgÚrunning)ÚidÚtargetÚstatusr   rœ   Úinstitutionr   Úmotie_org_rawzMISS:rj   ú:rv   i@  )rÝ   r­   r‚   Úmotie_org_stgr×   rØ   r4   zemp:ræ   Úmotie_org_snapshotÚ	collectedzpages=)rÝ   r•   rí   Únote)rÆ   rÇ   Úfailed)rí   Úfinished_atÚpagesr•   Úfail_reasonrë   )rí   rô   rÝ   z![motie_org] validation failed: %s)rÝ   rí   ÚreasonÚfinalize_motie_runÚp_run_idz"[motie_org] finalize_motie_run: %sÚ
fetched_atF)ÚdesciPÃ  r³   )r   Ú	timedeltar   c                    s.   zˆ   t| ƒ dd¡¡W S  ty   Y d S w )NÚZz+00:00)ÚfromisoformatÚstrr   r@   r   ©r   r   r   Ú_to_dtî  s
   ÿzrun_once.<locals>._to_dtr‹   )ÚhoursÚmotie_org_histÚopen_run_idÚnullÚ
valid_fromÚclose_run_idÚvalid_tozD[motie_org] run_id attribution post-finalize skipped (no raw window)rí   Úpassed)rí   rö   r÷   r•   z4[motie_org] run passed: run_id=%s, rows=%d, pages=%d)rÝ   rí   r•   r÷   Úabortedz[motie_org] run aborted: %s)rÝ   rí   Úerror)1r	   r  ÚuuidÚuuid4rˆ   r]   rÀ   ÚrequestsÚSessionr‡   rŸ   r    r§   rÚ   r@   rá   rÜ   r   rC   r0   r¬   r;   r»   rã   rÃ   rÊ   Únowr   ÚutcÚ	isoformatr:   ÚeqrA   r  Úrpcr€   r.   r{   ÚorderÚlimitr>   r   rþ   Úis_ÚgteÚlteÚnot_rB   Ú	exception)%rç   rÆ   rÇ   rÁ   rÝ   r÷   ró   r1   Ú
hq_targetsÚinst_targetsr„   rR   rÞ   Ú	rows_viewr‚   r¦   ÚstagerE   rW   Úrows_empÚhtml_empÚ	stage_emprÅ   rÉ   rù   r  rÂ   ÚrawÚstart_atÚend_atrþ   r  ÚstÚenÚstbÚenbrI   r   r  r   Úrun_once‹  sü   


ý
ÿ
 ÿ
ÿ
ñÚ
8ÿ
ÿ"ýúø
4ÿ>ÿ€ÿ 
ÿ
ÿÿ€÷r,  Ú__main__r   )rä   rå   ræ   )>Ú__doc__rª   rC   r  r#   ÚloggingÚtypingr   r   r   r   r   r   r  Úbs4r   Úapp.services.supabase_servicer	   ÚbasicConfigÚINFOÚ	getLoggerrA   rž   rŸ   r    r¨   r9   r©   Úcompiler’   r|   r  r   r   r(   r+   r  rJ   rM   rN   rP   rS   ri   r‡   r›   r¤   r§   r«   r¬   r»   rÃ   ÚfloatrÊ   rÍ   rÑ   rÖ   rÜ   rá   rã   r,  Ú__name__Úprintr   r   r   r   Ú<module>   sh   



ý

"$
  ,*$	A&
 " 
ÿ