uj=XddlmZddlZddlZddlZddlZddlZddlZddl Z ddl m Z m Z ddl m Z mZddlmZmZdZe ej)de ej)de ej)d e ej)d e ej)d e ej)d e ej)d fZdZe ddz ez Ze ddz dz Zej2dZdZehdZdZdZdddZ dedfdZ!dgdZ" dhdZ#didZ$dd djdZ%dddZ&ddd Z'dgd!Z(dkd"Z)dld#Z*ddd$Z+dmd%Z,dnd&Z-dnd'Z.dod(Z/dpd)Z0dqd*Z1drd+Z2dsd,Z3d-d. dtd/Z4dud0Z5d1d2dvd3Z6dwd4Z7ddd5dddd6 dxd7Z8dddd5d5d8 dyd9Z9d5ddd5d5d5dddddd: dzd;Z:ddZ;d|d?Zdd5ddddD ddEZ?ddd5d5dF ddGZ@dddd5d5d5d5d5dd5d5dd5dBULK_ALL TOP_SORTED AUTO_SEEN_JPG BULK_FILTERED CACHE8701_CDP MANUAL_SINGLETRANSCRIPT_REQUEST)z.jpgz.jpegz.pngz.webpz.avifz.gif)z.mp4ct|jj}|jdk7rt d||j dz S)zEReturn the fixed sibling _StDown directory for the supplied _St root._Stz)st_root must point to the _St directory: _StDown)r expanduserresolvename ValueErrorparentst_rootroots %D:\_St\Shared\storage\sort_storage.pyderive_stdown_rootr!-sK = # # % - - /D yyEDTFKLL ;; ""ctjddt|xsdjjd}|ddxs|S)Nz[^0-9A-Za-z._-]+_z._x)resubstrstrip)valuefallbacktexts r safe_media_componentr.7sC 66%sC ,<,B,B,D E K KD QD : !!r"ct|xsdjj}|r|jdsd|z}t t t t z}||vrtd||S)Nr%.zunsupported media extension: )r)r*lower startswithsetIMAGE_EXTENSIONSVIDEO_EXTENSIONSr)r+ extensionalloweds r normalize_media_extensionr8<srEKR &&(..0I--c2)O "#c*:&;;G8 BCC r"cxt|d}t|d}t|}t|dz |z ||z S)N instagrampost)r.r8r!)r account_name shortcoder6accountcodeexts r build_instagram_media_pathrAFsH #<=G  6 2D #I .C g & 4w >D6# OOr"c t|d}t|dz }||z }|jdd||dz ||dz ||dz |dz g}|j|j d|D]#} |j r|j %dtjj|}d |d |d |d tj|d d|d|d }|dz } | j r| jdd|k7r5| jd} | j|d| j!| | S#t$rYwxYw)zLCreate one minimal account index that opens the account view in Analyze8780.r:Tparentsexist_okz.jsonz.htmlzecharts.min.jsz*.md5zhttp://127.0.0.1:8780/?account=z uA��� · Analyze8780u& Analyze8780 열기 z index.htmutf-8replace)encodingerrorsz.htm.tmprJ)r.r!mkdirextendglobis_fileunlinkOSErrorurllibparsequotejsondumps read_text with_suffix write_textrI) rr<r>instagram_rootfolderretired legacy_pathurlhtmlpathtemps r ensure_instagram_account_indexrcRs"<=G'0;>N g %F LLL- G9E**G9E**G9E""!! G  NN6;;w'(  ""$""$ ,FLL,>,>w,G+H IC $WI.88;u=$$(JJs$G#HIEWI&   K D <<>T^^WY^OSWW + w/ T K'   s4 E EEIMAGE) asset_kindct|d}t|d}t|dz |z }|jsyt|xsdj j }|dvrt Dcgc]}|| } }ntDcgc]}|| } }| D]9} || z } | js| jjdkDs7| cSycc}wcc}w)Nr:r;rd>MP4VIDEOr) r.r!is_dirr)r*upperr5r4rPstatst_size) rr<r=rer>r?r\kindr@namesr candidates r find_instagram_media_filerp{s#<=G  6 2D  (; 6 @F ==? z$W % + + - 3 3 5D +;<+;CD6#+;<+;<+;CD6#+;<TM    9>>#3#;#;a#?  =r0..r0zdatabase filename is invalid) r)r*rrr_DATABASE_FILENAME_RE fullmatchr1endswithr2)r+rawrs r normalize_database_filenamers ekr  "C 566 czTS[DINNc$9DEE D * *4 0LMM ::<  ,  {dooc2788 Kr"ct|} tj|jd}t |j dxst S#t$r t cYSt$r t cYSwxYw)NrHrLfilename) rwrVloadsrXrgetDEFAULT_DB_FILENAMEFileNotFoundError Exception)rrapayloads r read_database_selectionrsg "7 +D#**T^^W^=>*7;;z+B+YFYZZ #"" #""#sAAA6%A65A6ct|jj}t|}t |}|j j dd|j|jdz}|jtjd|idddzd |j|t||z S) NTrCz.tmprFrGindent rHrL)rrrrrwrrMrYsuffixrZrVrWrIrs)rrrrrarbs r write_database_selectionrs = # # % - - /D &x 0D "4 (DKKdT2   DKK&0 1DOODJJ D1aPSWWbiOjLL d #d **r"c~t|jj}t|t |z Sru)rrrrsrrs r default_database_pathrs3 = # # % - - /D d #&=d&C CCr"cft|jj}t|}|j ddt |}t d|jdDtj}t|vr |stg}|t||z j|dS)NTrCc3VK|]!}|js|j#ywru)rPr).0ras r z&list_database_files..sX*E$DII*Es))z *.sqlite3)key)active_filename active_pathfiles) rrrrsrMrsortedrOr)casefoldr)rr directoryactivers r list_database_filesrs = # # % - - /D"4(I OOD4O0 $T *F X)..*EX^a^j^j kE%'$%%c9v;M:V:V:X6Ydi jjr"c@t|jj}t|}t ||z }|j st d|t||t||it|t|j|dS)Nzdatabase file not found: db_pathr) rrrrrsrPrinitialize_databaserrr)rrrrtargets r open_database_filers = # # % - - /D &x 0D  % ,F >> 4TF;<<%T4( \!$' \C8H4IW[ \\r"c@t|jj}t|}t ||z }|j rt d|t||t||it|t|j|dS)Ndatabase file already exists: r) rrrrrsexistsrrrrr)rs r create_database_filers = # # % - - /D &x 0D  % ,F }}9$@AA%T4( \!$' \C8H4IW[ \\r"cHt|jj}t|}t|jj}t ||z }|j rt d|t|||jjddt|}tj|d} |j||j|j|jt||t%||it'|t)|j|dS#t $rD|j|j |j#d#t $rYwxYwwxYw)NrTrC$@timeout) missing_okr)rrrrrsrrrrrMconnect_databasesqlite3connectbackupcommitcloserrQrrr)) rsource_db_pathrrrsourcersrcdsts r save_database_asrsa = # # % - - /D &x 0D . ! , , . 6 6 8F  % ,F }}9$@AA% MMt4 6 "C //&$ /C  3   %T4( \!$' \C8H4IW[ \\     MMTM *      s0!E)F!>FF! FF!FF!cnt|jj}|jj ddt j |d}t j|_|jd|jd|jd|jd|S) NTrCrrzPRAGMA foreign_keys = ONzPRAGMA busy_timeout = 10000zPRAGMA journal_mode = WALzPRAGMA synchronous = NORMAL) rrrrrMrrRow row_factoryexecute)rraconns r rrs = # # % - - /DKKdT2 ??4 .D{{DLL+,LL./LL,-LL./ Kr"cj|jd|dDchc]}t|dc}Scc}w)NzPRAGMA table_info()r)rr))r table_namerows r _table_columnsrs9(, 7I*UV5W(X Y(XCF (X YY Ys0cV|t||vr|jd|d|d|yy)Nz ALTER TABLE z ADD COLUMN  )rr)rr column_name definitions r _ensure_columnrs5.z:: |J<|K=*VW;r"crtD]#}|j|jd%t|dddt|dddt|ddd t|d dd d D]:}t||d d t||d dt||ddt||dd<d}|j d|y)NrHrLsocial_accounts deleted_atTEXTposts media_assets folder_namezTEXT NOT NULL DEFAULT ''media_saved_events)post_stat_latestpost_stat_history post_stat_10msort_by sort_valuezINTEGER NOT NULL DEFAULT 0 source_kindzTEXT NOT NULL DEFAULT 'LOCAL'views_available))initial_storage_foundation)runified_media_saved_flow)$analyze8780_repository_and_10m_stats)&collection_account_queue_sqlite_source),original_filename_and_account_folder_display)-observation_sort_mode_and_hourly_central_sync)r +views_availability_and_central_hour_historyzDINSERT OR IGNORE INTO schema_migrations(version, name) VALUES (?, ?)) SCHEMA_FILES executescriptrXr executemany)r schema_filer migrationss r _apply_schemars#  ;00'0BC$4*L&A4,748RS4-}>XYP tZ4NOtZ7STtZ8WXtZ):># -3C \\**3/F6??(b)//1 ? "4==9I+J 8;FKK ||E!HNN,-uQx4' +D6>2B!DD'$(||C 45;;=CCEsKH 'z>2B! DD    csG G!G GGct|xsdjddj}|s tdt |}|j sd|j vrtd||jS)Nr%rzryzrelative_path is requiredr{z;relative_path must stay below the configured storage root: )r)rIr*rr is_absoluteras_posix) relative_pathrras r normalize_relative_pathrsx m!r " * *4 5 ; ; =C 455  D TTZZ/VWZV[\]] ==?r"r%) batch_key account_idr filter_daysrequested_countmetadatact|}t|xstj} t |} | j d| ||t|xsd|t dt|tjt|xsiddf} | jt| j| jS#| jwxYw)Nz INSERT INTO download_batches( batch_key, source_mode, account_id, sort_by, filter_days, requested_count, metadata_json ) VALUES (?, ?, ?, ?, ?, ?, ?) r%rFTrG sort_keys)rr)uuiduuid4rrmaxrrVrWdictr lastrowidr) rrrrrrrrmoderrcursors r create_download_batchr)s ! -D i'4::< (C G $D GMr"As?+, 4B/etT  " 6##$  s BC C) media_indexbatch_idrank_no source_urlexpected_file_namect|} t|xsdjj} | s t dt |} | j d|t|| | tdt||t|xsdt|xsdf} | jt| j| jS#| jwxYw)Nr%asset_kind is requiredz INSERT INTO download_jobs( batch_id, post_id, source_mode, asset_kind, media_index, rank_no, source_url, expected_file_name ) VALUES (?, ?, ?, ?, ?, ?, ?, ?) r) rr)r*rjrrrrr$rr&r) rpost_idrrer*r+r,r-r.r'rmrr(s r create_download_jobr2s ! -D zR & & ( . . 0D 122 G $D G As;'(J$"%&,"-  $ 6##$  s A4CC') rr*r,md5sha256r-storage_root_iddownload_job_id event_uuid completed_atrc t|}t|xsdjj}|s t dt |}t|xsdj}|s t dt |j}t|dvrdn |j}t|xs|xsdj}tdt|}tdt|}t|xstj}|xsd}t|} |jd|jd |fj}|r@|j!t|d t|d ||d d |j#Sd}||jdt|fj}|st d|t||||f} t|dt|dt|dt|df}!|!| k7rt d| d|!|jdt|fj}"|"st d|| 1|jdj}#|#rt|#d nd} |jdt|||t| xsd| ||||t| xsdt| xsd|f |jdt|||fj}$t|$d }%t%|xsi}&|jd||t||%|"dt|"d|||| ||||t| xsdt| xsd|t'j(|&dd f}'t|'j*}(||jd |t|f|r|d!nd})|)|jd"t|)fj}*|*rt|*d#xsdnd}+|*rt|*d$xsdnd},|*rt|*d%xsdnd}-|-dk(r(|+dks|,|+k\r|jd&|t|)fn|jd't|)f|jd(t|f|jd)|%f|t|t|"d|||| ||t| xsdt| xsd|d* }.|jd+|(t'j(|.dd f|j!|(|%||||dd,|j#S#t,$r|j/wxYw#|j#wxYw)-zRecord the common MEDIA_SAVED result for every download entry path. The server sync payload intentionally excludes the local relative path. The local event retains only a path relative to the configured storage root. r%r0zfile_name is required)r%r0r:rNBEGIN IMMEDIATEz@SELECT id, asset_id FROM media_saved_events WHERE event_uuid = ?idasset_idT)event_idr<r7r idempotentz SELECT id, batch_id, post_id, source_mode, asset_kind, media_index FROM download_jobs WHERE id = ? zdownload_job_id not found: r1rrer*z download job mismatch: expected=z actual=z8SELECT id, account_id, shortcode FROM posts WHERE id = ?post_id not found: zESELECT id FROM storage_roots WHERE is_default = 1 ORDER BY id LIMIT 1a0 INSERT INTO media_assets( post_id, asset_kind, media_index, source_url, storage_root_id, relative_path, file_name, folder_name, file_size, md5, sha256, local_status, verified_at ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'AVAILABLE', COALESCE(?, CURRENT_TIMESTAMP)) ON CONFLICT(post_id, asset_kind, media_index) DO UPDATE SET source_url = CASE WHEN excluded.source_url <> '' THEN excluded.source_url ELSE media_assets.source_url END, storage_root_id = COALESCE(excluded.storage_root_id, media_assets.storage_root_id), relative_path = excluded.relative_path, file_name = excluded.file_name, folder_name = excluded.folder_name, file_size = excluded.file_size, md5 = excluded.md5, sha256 = excluded.sha256, local_status = 'AVAILABLE', verified_at = excluded.verified_at, updated_at = CURRENT_TIMESTAMP zTSELECT id FROM media_assets WHERE post_id = ? AND asset_kind = ? AND media_index = ?a INSERT INTO media_saved_events( event_uuid, download_job_id, post_id, asset_id, account_id, shortcode, source_mode, asset_kind, media_index, rank_no, relative_path, file_name, folder_name, file_size, md5, sha256, completed_at, payload_json ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, COALESCE(?, CURRENT_TIMESTAMP), ?) rr=Fr z UPDATE download_jobs SET job_status = 'COMPLETE', completed_at = COALESCE(?, CURRENT_TIMESTAMP), last_error = '', updated_at = CURRENT_TIMESTAMP WHERE id = ? r+a SELECT b.requested_count, COUNT(j.id) AS total_jobs, SUM(CASE WHEN j.job_status <> 'COMPLETE' THEN 1 ELSE 0 END) AS remaining_jobs FROM download_batches b LEFT JOIN download_jobs j ON j.batch_id = b.id WHERE b.id = ? GROUP BY b.id, b.requested_count r total_jobsremaining_jobsa+ UPDATE download_batches SET batch_status = 'COMPLETE', completed_at = COALESCE(?, CURRENT_TIMESTAMP), updated_at = CURRENT_TIMESTAMP WHERE id = ? z UPDATE download_batches SET batch_status = 'RUNNING', completed_at = NULL, updated_at = CURRENT_TIMESTAMP WHERE id = ? z]UPDATE posts SET media_state = 'MEDIA_AVAILABLE', updated_at = CURRENT_TIMESTAMP WHERE id = ?z INSERT INTO p2p_inventory(asset_id, available, updated_at) VALUES (?, 1, CURRENT_TIMESTAMP) ON CONFLICT(asset_id) DO UPDATE SET available = 1, updated_at = CURRENT_TIMESTAMP ) client_event_idr1r=rrer*r, file_name file_sizer3r4r8z INSERT INTO sync_queue(entity_type, entity_local_id, operation, payload_json) VALUES ('MEDIA_SAVED', ?, 'UPSERT', ?) )r=r<r7rrrCr>)rr)r*rjrrrrrr$rr"r#rrrrrr%rVrWr&rrollback)/rr1rrerrCrrDr*r,r3r4r-r5r6r7r8rr'rmsafe_relative_pathsafe_file_namerelative_parentderived_folder_namesafe_folder_namesizeindex event_keycompleted_valuerexistingjob_rowexpectedactualr; default_rootassetr< local_payload event_cursorr=r+batch_progress requestedr@ remainingserver_payloads/ r record_media_savedr[s4 ! -D zR & & ( . . 0D 1220?b)//1N 011#$67>>O #O 4 A"G[G[;L*=LMSSU q#i. !D 3{# $EJ.$**,/I"*dO G $D_ &'<< N L  (*   KKM/ 45'#" n _  &ll_%'  hj  #>>O!PQQG dD%8HGI&'GM*+GL)*GM*+ F ! 6xl(6*U|| F \O  (* 27)<= =  "<<Whj :Fc,t"454O  (G J$"%" CI2FLb! )" F b \4 '  (* uT{#W]+ || G \"D%&" CI2FLb! =uM%  <|--.  & LL !#o"67 /6wz*4H#!%]$ "(*LZC/@ A FQG_` GUS !=!BC[\ JXC/? @ EAF^_ >yA~y9PLL)#h-8 LL X( k \O    K   )7|T+./ 'syb>&,B'+    tzz.uPTU V     #+'     s 'A#UN-UU44U77V F) root_code is_defaultczt|jj}t|xsdj }|sQt j t|jdjddj}d|}t|} |r|jd|jd|t||rdndf|jd |fj}|jt|d |jS#|jwxYw) Nr%rH LOCAL_DOWNLOAD_zHHJ3BOUUW ) G $D  LLW X  3x=z!q 9 llMPTwW``b 3t9~  s $A3D((D:cF |j|y#t$rYywxYw)NTF) relative_tor)rars r _is_relative_torjs*  s   ) account_pathaccount_folder_mergecTt|jj}|jst t |t |} |jdj}|j|rtt |djj}t||rRt|dt ||j|j|jjxsdddS|j}|} t |xsdj!dd j#d D cgc]} | s|  } } |sU| rS|} d } t%| D]=}| jj'|j'k7rd } n| j} ?| r| } t)|| }|t | |j| j|jjxsdd dS#|jwxYwcc} w)NzPSELECT id, root_path FROM storage_roots WHERE is_default = 1 ORDER BY id LIMIT 1rdr;r:STDOWN_DEFAULT)r5storage_root_pathrrstorage_layoutr%rzryTF)rdLOCAL_DOWNLOAD_ROOT)rrrrPrr)rrrrrjrrirrrrIr reversedrrg)r file_pathrkrlfilerdefaultrSrrr account_partsprobematchedrQroot_ids r resolve_download_storagerzs  ? % % ' / / 1D <<>D ** G $D,, ^ (*  C 456AACKKM 4 .#&wt}#5%(%6!%!1!1,!?!H!H!J#{{//>;"2  [[F D&),*<"&=&E&EdC&P&V&VWZ&[d&[d_cT&[Md M /Hzz""$(9(9(;;LLE 0 D!'T:G" Y))$/88:{{''6;/  9 esHH% H%H")post_urlcaption thumbnail_url video_url published_atstatsrc t|xsdj} t|xsdjjd} | s td| s tdt| xsdjj } | dvr!t|xsdjrdnd} t | || } t |} |jd |jd | d | d f|jd | fj}t|d}|jd|| | t|xsd|t|xsdt|xsdf|jd| fj}t|d}t|xsi}|rt|jdxs|jdxsdjxsd}t|jdxsdt|jdxsdt|jdxsdt|jdxsdf}|jd|g|||jd|g|||j||d|jS#t$r|jwxYw#|jwxYw)Nr%@shortcode is requiredzowner_username is required>rrrrrr:ai INSERT INTO social_accounts(platform, username, profile_url) VALUES ('instagram', ?, ?) ON CONFLICT(platform, username) DO UPDATE SET profile_url = CASE WHEN excluded.profile_url <> '' THEN excluded.profile_url ELSE social_accounts.profile_url END, updated_at = CURRENT_TIMESTAMP rryzLSELECT id FROM social_accounts WHERE platform = 'instagram' AND username = ?r;a4 INSERT INTO posts( account_id, platform, shortcode, post_url, caption, published_at, thumbnail_url, video_url, source_url_observed_at ) VALUES (?, 'instagram', ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP) ON CONFLICT(platform, shortcode) DO UPDATE SET account_id = excluded.account_id, post_url = CASE WHEN excluded.post_url <> '' THEN excluded.post_url ELSE posts.post_url END, caption = CASE WHEN excluded.caption <> '' THEN excluded.caption ELSE posts.caption END, published_at = COALESCE(excluded.published_at, posts.published_at), thumbnail_url = CASE WHEN excluded.thumbnail_url <> '' THEN excluded.thumbnail_url ELSE posts.thumbnail_url END, video_url = CASE WHEN excluded.video_url <> '' THEN excluded.video_url ELSE posts.video_url END, source_url_observed_at = CURRENT_TIMESTAMP, last_observed_at = CURRENT_TIMESTAMP, updated_at = CURRENT_TIMESTAMP zCSELECT id FROM posts WHERE platform = 'instagram' AND shortcode = ? captured_at collected_atviewsrlikescommentsrepostsa INSERT INTO post_stat_latest(post_id, views, likes, comments, reposts, collected_at) VALUES (?, ?, ?, ?, ?, COALESCE(?, CURRENT_TIMESTAMP)) ON CONFLICT(post_id) DO UPDATE SET views = excluded.views, likes = excluded.likes, comments = excluded.comments, reposts = excluded.reposts, collected_at = excluded.collected_at z INSERT OR IGNORE INTO post_stat_history( post_id, views, likes, comments, reposts, collected_at ) VALUES (?, ?, ?, ?, ?, COALESCE(?, CURRENT_TIMESTAMP)) )rr1)r)r*lstriprr1rrrrrr%rrrrrE)rr=owner_usernamer{r|r}r~rrrr?owner kind_hintr_rr>rr;r1valuesr metric_valuess r upsert_download_postrQs yB  % % 'D $" % + + - 4 4S 9E 011 566M'R(..0668I %!)/r288:F &tXY OC G $DV &'  0q9 : ,, Z H  (* '   "GMr"M'R(IO$ # 6|| Q G  (* d4j/ekr" vzz-8\FJJ~ '' THEN excluded.sort_by ELSE download_batches.sort_by END, filter_days = COALESCE(excluded.filter_days, download_batches.filter_days), requested_count = MAX(download_batches.requested_count, excluded.requested_count), batch_status = CASE WHEN download_batches.batch_status = 'COMPLETE' THEN 'COMPLETE' ELSE 'RUNNING' END, metadata_json = excluded.metadata_json, updated_at = CURRENT_TIMESTAMP rFTr z3SELECT id FROM download_batches WHERE batch_key = ?r;)r)r*rrrrr$rrVrWr%rrr) rrrrrrrrrr'rrs r ensure_download_batchrs io2  $ $ &C 011  -D G $D  GMr"As?+, 4B/etT  2llPSVRXYbbd 3t9~  s BC--C?)r*r,r-r.cjt|} t|xsdjj} | s t dt |} | j dt|t|| | tdt||t|xsdt|xsdf| j dt|t|| tdt|fj} | jt| d| jS#| jwxYw)Nr%r0a| INSERT INTO download_jobs( batch_id, post_id, source_mode, asset_kind, media_index, rank_no, source_url, expected_file_name, job_status ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'DOWNLOADING') ON CONFLICT(batch_id, post_id, asset_kind, media_index) DO UPDATE SET rank_no = COALESCE(excluded.rank_no, download_jobs.rank_no), source_url = CASE WHEN excluded.source_url <> '' THEN excluded.source_url ELSE download_jobs.source_url END, expected_file_name = CASE WHEN excluded.expected_file_name <> '' THEN excluded.expected_file_name ELSE download_jobs.expected_file_name END, job_status = CASE WHEN download_jobs.job_status = 'COMPLETE' THEN 'COMPLETE' ELSE 'DOWNLOADING' END, last_error = '', updated_at = CURRENT_TIMESTAMP rz SELECT id FROM download_jobs WHERE batch_id = ? AND post_id = ? AND asset_kind = ? AND media_index = ? r;) rr)r*rjrrrrr$rrr) rr+r1rrer*r,r-r.r'rmrrs r ensure_download_jobrs ! -D zR & & ( . . 0D 122 G $D$  H G As;'(J$"%&,"-  2ll ]CL$As;7G0H I  (*  3t9~  s B?D D2)rr*r,r-r{r|r}r~rr3r4rDrkrlrrrrr7rrc&t|jj}|r't|jjn t|}t ||t |}t |xstj}t|||| | | | ||t |xsdjjdk(rdnd }t||||} t |xsd|}!t||!||d||td t|xsd t |xsd| d d  }"t!||"|d||||| t|j" }#t%|xsi}$|$j'|!| d d t)|fid|dd|d|d| ddt|j"d| dd|d|d|d|d|d| d| dd|#d|d|$}%i|%t ||d|d|"|#| d| dt|j"| d| d d! S#t*$rd}&t-|}' |'j/d t |&|#f|'j1|'j3#|'j3wxYwd}&~&wwxYw)"Nr%rhrr) r=rr{r|r}r~rrr)rsrkrlzsingle-rrrrp)rkrp)rrrrrrrr1)r+r1rrer*r,r-r.)rrprrerrCrrDr*r,r3r4r-r5r6r7rz UPDATE download_jobs SET job_status = 'FAILED', last_error = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ? ) rrr1r+job_idrrrCr5rp)rrrrrrr)r"r#rr*rjrzrr$rrrr%updater[rrrrr)(rrsrrr=rrer*r,r-r{r|r}r~rr3r4rDrkrlrrrrr7rrrresolved_db_pathr'rMcontextstorageeffective_batch_keyr+rrUresultexcrs( r record_downloaded_filer+s<< = # # % - - /D?FtG}//199;LabfLg($/  -DJ.$**,/I"%#! #J$4" 5 ; ; = C C E PfVY G'!1 G i@WYK+@A$%<(As?#7a89 23%&67  H! " ?// FB'M,%&67  "#  I&  "  "/2  9o**   .   $    " $$56 # !! ""# D   '(l+9% 1}-)_))"#45!"23   (  LL S6"   KKM JJL  JJLs1 AH## J, J 8-I6%J 6JJ  Jct|tr|}nqt|xsdj}|s$tjt j }n.|jdr|dddz}tj|}|j |jt j }|jt j S)Nr%Z+00:00)tzinfo) isinstancerr)r*nowrutcr~ fromisoformatrrI astimezone)r+dtrs r _parse_observed_atrs%" %+2$$&hll+B||C #2h)'',B yy ZZx||Z , == &&r"ct|}|jdzdz}|j|ddjjddS)N r)minutesecond microsecondrr)rrrI isoformat)r+rrs r ten_minute_bucketrsK E "Bii2o #F ::VA1: = G G I Q QRZ\_ ``r"cTt|jjddS)Nrr)rrrI)r+s r _iso_utcrs# e $ . . 0 8 83 GGr"LOCAL)rrrrr observed_atplatform_account_id display_name profile_urlr{r|rr}r~rrrrct|xsdjjd}t|xsdj}|s td|s tdt |}t |}t d||||fD}t|xsdrdnd}t|xsdj}tdt|xsd}t|xsdjjxsd}t|} |jd |jd |fj}|jd |fj}|s|r-|jdd||d |rd ndd|jS|jd|fj} | | d|d}!|}"|s0t| dxsdrtdt| dxsd}!d}"t| dxsdt| dxsdt| dxsdt| dxsdt| dxsdf|!|"|d|d|dfk(}#t| xsdj}$|$r-t||$t|xsdjrdndnd}%t!t| dxsdjj#|j#k7t| dxsddk7| ddu| ddut%t| xsdjxr"t| dxsdt| xsdk7t%t| xsdjxr"t| d xsdt| xsdk7t%t| xsdjxr"t| d!xsdt| xsdk7t%|%xr6t|| d"t| d#xsdjrdnd|%k7t%t| xsdxr"t| d$xsdt| xsdk7|duxr"t| d%xsdt|xsdk7t%t|xsdjxr"t| d&xsdt|xsdk7t%t|xsdjxr"t| d#xsdt|xsdk7f }&|#rH|&sF|jt| d't| d(|||"|||d)d d d* |jS|jd+t| xsd|t| xsdt| xsdxsd,|d-ft|jd.|fjd/}'|jd0|fj}(|(rnt| xsdjsQt|xsdjs4t||(d"t|(d#xsdjrdnd})n-t|| t|xsdjrdnd})|jd1|'||)t| xsd|t|xsdt|xsd||f t|jd2|fjd/}*|jd3|*fj}+|d}!|}"|+2|s0t|+dxsdrtdt|+dxsd}!d}"|!|d|d|df},|+duxrkt|+dxsdt|+dxsdt|+dxsdt|+dxsdt|+dxsdf|,d|"|,d|,d|,dfk(}-|-rS|jd4|'f|jd5|*f|j|'|*|||"|||d)d d6 |jS|jd7|*|,d|"|,d|,d|,d||||f |jd8|*|,d|"|,d|,d|,d||||f |jd9|*|||,d|"|,d|,d|,d|||f |jd4|'f|jd5|*f|j|'|*|||"|||d d)d6 |jS#t&$r|j)wxYw#|jwxYw):Nr%rzusername is requiredrc3NK|]}tdt|xsdyw)rN)r$r)rvs r rz%upsert_observation..s"S1RAC3qvA;'1Rs#%rrrr:z`SELECT 1 FROM deletion_events WHERE entity_type='ACCOUNT' AND lower(entity_key)=lower(?) LIMIT 1zOSELECT 1 FROM deletion_events WHERE entity_type='POST' AND entity_key=? LIMIT 1TACCOUNTPOST)rr1 bucket_atrignored_deleteddeleted_entitya{ SELECT p.id AS post_id, p.account_id, p.post_url, p.caption, p.published_at, p.thumbnail_url, p.video_url, p.deleted_at AS post_deleted_at, a.username, a.platform_account_id, a.display_name, a.profile_url, a.active, a.deleted_at AS account_deleted_at, s.views, s.views_available, s.likes, s.comments, s.reposts FROM posts p JOIN social_accounts a ON a.id=p.account_id LEFT JOIN post_stat_latest s ON s.post_id=p.id WHERE p.platform='instagram' AND p.shortcode=? LIMIT 1 rrrrrrrrrrusernameraccount_deleted_atpost_deleted_atrrrr{r~r|rr}rr1F) rr1rrrrrrsavedskipped_unchangedskipped_all_writesa* INSERT INTO social_accounts( platform, platform_account_id, username, display_name, profile_url, active, deleted_at ) VALUES ('instagram', ?, ?, ?, ?, 1, NULL) ON CONFLICT(platform, username) DO UPDATE SET platform_account_id = CASE WHEN excluded.platform_account_id <> '' THEN excluded.platform_account_id ELSE social_accounts.platform_account_id END, display_name = CASE WHEN excluded.display_name <> '' THEN excluded.display_name ELSE social_accounts.display_name END, profile_url = CASE WHEN excluded.profile_url <> '' THEN excluded.profile_url ELSE social_accounts.profile_url END, active = 1, deleted_at = NULL, updated_at = CURRENT_TIMESTAMP rryzHSELECT id FROM social_accounts WHERE platform='instagram' AND username=?r;zPSELECT post_url, video_url FROM posts WHERE platform='instagram' AND shortcode=?a INSERT INTO posts( account_id, platform, shortcode, post_url, caption, published_at, thumbnail_url, video_url, source_url_observed_at, last_observed_at, deleted_at ) VALUES (?, 'instagram', ?, ?, ?, ?, ?, ?, ?, ?, NULL) ON CONFLICT(platform, shortcode) DO UPDATE SET account_id = excluded.account_id, post_url = CASE WHEN excluded.post_url <> '' THEN excluded.post_url ELSE posts.post_url END, caption = CASE WHEN excluded.caption <> '' THEN excluded.caption ELSE posts.caption END, published_at = COALESCE(excluded.published_at, posts.published_at), thumbnail_url = CASE WHEN excluded.thumbnail_url <> '' THEN excluded.thumbnail_url ELSE posts.thumbnail_url END, video_url = CASE WHEN excluded.video_url <> '' THEN excluded.video_url ELSE posts.video_url END, source_url_observed_at = excluded.source_url_observed_at, last_observed_at = excluded.last_observed_at, deleted_at = NULL, updated_at = CURRENT_TIMESTAMP z?SELECT id FROM posts WHERE platform='instagram' AND shortcode=?z SELECT views, views_available, likes, comments, reposts FROM post_stat_latest WHERE post_id=? zMDELETE FROM deletion_events WHERE entity_type='ACCOUNT' AND entity_local_id=?zJDELETE FROM deletion_events WHERE entity_type='POST' AND entity_local_id=?) rr1rrrrrrrra= INSERT INTO post_stat_latest(post_id, views, views_available, likes, comments, reposts, collected_at, sort_by, sort_value, source_kind) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON CONFLICT(post_id) DO UPDATE SET views=excluded.views, views_available=excluded.views_available, likes=excluded.likes, comments=excluded.comments, reposts=excluded.reposts, collected_at=excluded.collected_at, sort_by=excluded.sort_by, sort_value=excluded.sort_value, source_kind=excluded.source_kind a INSERT INTO post_stat_history(post_id, views, views_available, likes, comments, reposts, collected_at, sort_by, sort_value, source_kind) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON CONFLICT(post_id, collected_at) DO UPDATE SET views=excluded.views, views_available=excluded.views_available, likes=excluded.likes, comments=excluded.comments, reposts=excluded.reposts, sort_by=excluded.sort_by, sort_value=excluded.sort_value, source_kind=excluded.source_kind a INSERT INTO post_stat_10m( post_id, bucket_at, observed_at, views, views_available, likes, comments, reposts, sample_count, sort_by, sort_value, source_kind ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, 1, ?, ?, ?) ON CONFLICT(post_id, bucket_at) DO UPDATE SET observed_at=excluded.observed_at, views=excluded.views, views_available=excluded.views_available, likes=excluded.likes, comments=excluded.comments, reposts=excluded.reposts, sort_by=excluded.sort_by, sort_value=excluded.sort_value, source_kind=excluded.source_kind, sample_count=post_stat_10m.sample_count + 1, updated_at=CURRENT_TIMESTAMP )r)r*rrrrtuplerr$rjrrrrrranyr1boolrrE).rrr=rrrrrrrrrr{r|rr}r~rrrrr?observedbucketmetricsviews_available_flag sort_fieldsort_metric_valueobservation_sourcerdeleted_account deleted_postexisting_observationeffective_viewseffective_views_availableunchanged_metricsincoming_post_urlresolved_incoming_post_urlmetadata_changedr existing_postresolved_post_urlr1 previous_stateffective_metrics unchangeds. r upsert_observationrs . B  % % ' . .s 3E yB  % % 'D /00 011 $H { +FS%'1RSSG #O$8q 91qW]#))+JAs:?34[3G4::<BBDO G $Dr &',, n H  (* || ] G  (*  l KKM#'#'/>)F L } $|| G  (*  +0DW0M0Y%ajO(< %'C0DEV0W0\[\,]"%a-A'-J-Oa)P"Q,-)(16Q7():;@qA(16Q7(49:(38q9 ! !:GAJPQ T[\]T^ ! !$HN 3 9 9 ;  % -+,/ R,@,F,F,H&c+- ' #(4:;AACIIKu{{}\(27a8A=$%9:$F$%67tCS,2399;<ZEYZoEpEvtvAw{~@S@YWY|ZBZS+,2245E#>RSa>b>hfh:impq}rDBDnE;ES*+1134B=QR_=`=fdf9gknozpAAlB:B/0o5QRVXlmwXxSVWklwWxW~|~SSESESGIOMP6QUo6oSB'(mS1Ei1P1VTV-W[^_f_ljl[m-mD(wS1En1U1[Y[-\`cdpdvtv`w-wS,"-3356H3?STc?d?jhj;kortAtGEGpHWYjklYmpABCpDFWXYFZ\fhy{M N  [^h]j  X[bZd  (W6bj@Yfp@QbtCZ_`    s.A3dO d5J?dB;dd,,d//er2)page page_sizeinclude_deletedsearch_patternc tdt|xsd}tdtdt|xsd}|rdnd}t|xsd}|}g}|r|dz }|j |t |} t| j d|jd } t| j d||jd } | j d |d g|||dz |zj} | D cgc] } t| c} ||| | |d d | jScc} w#| jwxYw)Nr'rz1=1z#a.active=1 AND a.deleted_at IS NULLr%z% AND a.username LIKE ? COLLATE NOCASEz2SELECT COUNT(*) AS n FROM social_accounts a WHERE na& SELECT a.id, a.username, a.display_name, a.profile_url, a.active, a.deleted_at, COUNT(p.id) AS post_count, COALESCE(MAX(s.views), 0) AS max_views, COALESCE(SUM(s.views), 0) AS total_views, MAX(COALESCE(s.collected_at, p.last_observed_at, a.updated_at)) AS last_observed_at FROM social_accounts a LEFT JOIN posts p ON p.account_id=a.id AND p.deleted_at IS NULL LEFT JOIN post_stat_latest s ON s.post_id=p.id WHERE z GROUP BY a.id ORDER BY COALESCE(last_observed_at, a.updated_at) DESC, a.username COLLATE NOCASE LIMIT ? OFFSET ? r)itemsrrtotal total_allrr) r$rminr)appendrrrfetchallr%r)rrrrr base_wherepatternwhere where_paramsrrrrowsrs r list_accountsrs{ q#dia. !DAs5#io2"678I)/TJ.&B'G E L 88G$ G $D" @ M (*S DLL@ H, (*S|| '   ?l >I >qI'= > (*! $,004Cd3i40""%+  1 s:BD> D9 D>9D>>E)rrrinclude_media_assetsc tdt|xsd}tdtdt|xsd}dddddd d d }|jt |xsd |d }d }|rd}t |} t| j dt|fjd} | j d|d|dt|||dz |zfj} | D cgc] } t| } } i}|r| Dcgc]}t|d}}|rdjd|D}| j d|d|j}|D]F}t|}|jt|jdgj|H| D]}t|jd|jdt |jdxsd jrdnd|d<|sc|jt|dg|d <| ||| |d!| j!Scc} wcc}w#| j!wxYw)"Nrrrz views DESCz likes DESCz comments DESCz reposts DESCzviews_delta DESCzviews_per_hour DESCzp.published_at DESC)rrrr views_deltaviews_per_hourrrr%a, (SELECT ma.folder_name FROM media_assets ma WHERE ma.post_id=p.id AND ma.local_status='AVAILABLE' ORDER BY ma.media_index, ma.id LIMIT 1) AS folder_name, (SELECT ma.file_name FROM media_assets ma WHERE ma.post_id=p.id AND ma.local_status='AVAILABLE' ORDER BY ma.media_index, ma.id LIMIT 1) AS file_name, (SELECT ma.relative_path FROM media_assets ma WHERE ma.post_id=p.id AND ma.local_status='AVAILABLE' ORDER BY ma.media_index, ma.id LIMIT 1) AS relative_path, EXISTS( SELECT 1 FROM media_assets ma WHERE ma.post_id=p.id AND ma.local_status='AVAILABLE' AND UPPER(ma.asset_kind) IN ('IMAGE','JPG','JPEG') ) AS has_jpg, EXISTS( SELECT 1 FROM media_assets ma WHERE ma.post_id=p.id AND ma.local_status='AVAILABLE' AND UPPER(ma.asset_kind) IN ('VIDEO','MP4') ) AS has_mp4zISELECT COUNT(*) AS n FROM posts WHERE account_id=? AND deleted_at IS NULLra WITH ranked AS ( SELECT t.*, ROW_NUMBER() OVER(PARTITION BY t.post_id ORDER BY t.bucket_at DESC) AS rn FROM post_stat_10m t ), recent AS ( SELECT post_id, MAX(CASE WHEN rn=1 THEN views END) AS last_views, MAX(CASE WHEN rn=2 THEN views END) AS prev_views, MAX(CASE WHEN rn=1 THEN bucket_at END) AS bucket_at FROM ranked WHERE rn <= 2 GROUP BY post_id ) SELECT p.id, p.shortcode, p.post_url, p.caption, p.published_at, p.thumbnail_url, p.video_url, p.media_state, p.last_observed_at, COALESCE(s.views,0) AS views, COALESCE(s.views_available,0) AS views_available, COALESCE(s.likes,0) AS likes, COALESCE(s.comments,0) AS comments, COALESCE(s.reposts,0) AS reposts, COALESCE(recent.last_views, s.views, 0)-COALESCE(recent.prev_views, recent.last_views, s.views, 0) AS views_delta, (COALESCE(recent.last_views, s.views, 0)-COALESCE(recent.prev_views, recent.last_views, s.views, 0))*6 AS views_per_hour, CASE WHEN COALESCE(s.views,0)>0 THEN ROUND(COALESCE(s.likes,0)*100.0/s.views,4) ELSE 0 END AS like_rate, CASE WHEN COALESCE(s.views,0)>0 THEN ROUND(COALESCE(s.comments,0)*100.0/s.views,4) ELSE 0 END AS comment_rate, recent.bucket_at AS latest_bucket_at z FROM posts p LEFT JOIN post_stat_latest s ON s.post_id=p.id LEFT JOIN recent ON recent.post_id=p.id WHERE p.account_id=? AND p.deleted_at IS NULL ORDER BY z5, p.id DESC LIMIT ? OFFSET ? r;,c3 K|]}dyw?Nrr$s r rz%list_account_posts.._s'>XX ah SELECT ma.id, ma.post_id, ma.asset_kind, ma.media_index, ma.storage_root_id, ma.folder_name, ma.file_name, ma.relative_path, ma.local_status, sr.root_path FROM media_assets ma LEFT JOIN storage_roots sr ON sr.id=ma.storage_root_id WHERE ma.post_id IN (zu) AND ma.local_status='AVAILABLE' ORDER BY ma.post_id, ma.media_index, ma.id r1r=r{r~rrrr)rrrrr)r$rrrr)rrrrr%r setdefaultpoprrr*r)rrrrrrsort_maporder media_columnsrrrrrassets_by_postitempost_ids placeholders asset_rows asset_rowrTs r list_account_postsr s q#dia. !DAs5#io2"678I /!2D/AVH LLW/0(72C DEM #  G $D=DLL WZ]^hZiYk (*S||("?# W 3 8_i$(i)? @; < (*= >'++dscd+:< 49:EDDJEH:"xx'>X'>> !\\* +78  (*",I OE"--c%))I2F.GLSSTYZ",D;DHH[D#DVEDKV#DD  $E s$,D BD#D 5D DD$c8t|} |jdt|tdt dt|xsdfj }|Dcgc] }t |c}|jScc}w#|jwxYw)Nz SELECT bucket_at, observed_at, views, views_available, likes, comments, reposts, sample_count FROM post_stat_10m WHERE post_id=? ORDER BY bucket_at DESC LIMIT ? ri)rrrr$rrr%r)rr1limitrrrs r get_post_statsrs G $D || \3q#dC ,=">? @  (* &**TcS T* + sABB/BBB>r%rapihomeraboutlegaloauthrtermsdirectemailssearchexploregraphqlprivacystoriesaccountsexplorer challenge developerr:c.t|xsdjjdddjjd}|sy|}d|vs#|j ds|j dr*d|vr|nd|z} t j j|}t|jxsdjjd}|d k7ryt|jxsdjd Dcgc]$}|st j j|&}}|syt|d xsdj}|jtvryt!|dkDr#t|dxsdjd vry|}nd |vry|jjdj}|tvry|rt!|d kDryt#d|Dry|S#t$rYywxYwcc}w)aReturn a canonical Instagram username for the collection queue. The queue stores usernames only. A legacy profile URL is accepted only for migration; feed/detail scopes such as /explore/, /reels/, /p/ and /reel/ are rejected and never become collection accounts. r%u🗑️rrz://zwww.zinstagram.com/zhttps://rryr>rc3$K|]}|dv yw)z&abcdefghijklmnopqrstuvwxyz0123456789._Nr)rchs r rz0normalize_collection_username..s R "2= = s)r)r*rIrr2rSrTrrr r1 removeprefixrar r _COLLECTION_RESERVED_USERNAMESr r) r+rror_rrrrfirsts r normalize_collection_usernamer*s ekr  " * *9b! < B B D K KC PC I |s~~f-@P1Qc\czC'7 \\**3/F6??(b)//1>>vF ? "8;FKKc%(.b1779J !((-335I22 I+ R RR 1   csH H!H HH)rc @t|} |rdnd}|jd|dj}|Dcgc]4}it|t |dt |dd|dd d 6c}|j Scc}w#|j wxYw) Nr%z WHERE enabled=1 AND is_deleted=0z SELECT id, username, position, enabled, is_deleted, source_type, updated_at FROM collection_account_queue z7 ORDER BY position ASC, id ASC enabled is_deletedrrry)r,r- account_url)rrrr%rr)rrrrrrs r list_collection_accountsr/s G $D%+M|| G    (*    s) I/"3|#45!;C O;LAN       s-B 9B3B B Bc Zi}t|xsgD]}t|tr|nd|i}t|j dxs7|j dxs$|j dxs|j d}|spt |j dxs|j drdnd}|rdnt |j d d rdnd}|j |}|$|ds|rdnd|d<|drdn |d r|rdnd|d <|d||d ||<t|j } t|} t| d D] \} }| |d < t|} | jd| jdjD chc]0} t| dxsdjj2}} | D]5}|d|vr d|d<d|d <| jd|d|d |d |df7| rAdjd| D}| jd|dt!t#| n| jdd| Dcgc]'}|d|d t |d t |dd )c}i}| jdt%j&|dd f| j)t+|t-| d| j/Scc} wcc}w#t0$r| j3wxYw#| j/wxYw)zDReplace the SIDE collection queue with a username-only ordered list.rr>r.r_r-deletedrrr,T)rpositionr,r-)startr2r:zBSELECT entity_key FROM deletion_events WHERE entity_type='ACCOUNT' entity_keyr%a INSERT INTO collection_account_queue(username, position, enabled, is_deleted, source_type) VALUES (?, ?, ?, ?, 'SIDE') ON CONFLICT(username) DO UPDATE SET position=excluded.position, enabled=excluded.enabled, is_deleted=excluded.is_deleted, source_type='SIDE', updated_at=CURRENT_TIMESTAMP rc3 K|]}dywrrrs r rz.replace_collection_accounts../s#6ACrzSDELETE FROM collection_account_queue WHERE source_type='SIDE' AND username NOT IN (rz=DELETE FROM collection_account_queue WHERE source_type='SIDE'rzmINSERT INTO sync_queue(entity_type, operation, payload_json) VALUES('COLLECTION_ACCOUNT_QUEUE', 'REPLACE', ?)Fr )rr)listrr r*rrrr3 enumeraterrrr)r*r1rrrrVrWrr/r rrrE)rrnormalized_by_usernameraw_itemrrr1r,previousrseenr2rr deleted_namesrrs r replace_collection_accountsr=sh8:"%%h8xz8>T0 HHZ eDHHY$7 e488M;R eVZV^V^_dVe  DHH\2Idhhy6IJ!PQ!$txx 4/H*IQq)--h7  *2<*@GQQRH\ "'/ '=!R[I\ahAnoHY   ! , x(&.,3356J % &D#Ja8$#Z9 G $D6 &'||Thj  L!'R ( . . 0 6 6 8  DJ=0%&\""#Y LL j!4 #3T)_drus "  ''N34N34N34N34N34N34N34 +8nv-0CC"8nv58VV" #>?)  G#"  P  P P P P  P%\      0DN  #+D k]]]< ZX >!% , , , ,  ,^]`E>!!")-# ## #  #  ###'# #X ' '' '  '  ''''' 'd"&"&!#(,'J JJ J  J  JJJJJJ J JJ J  !J"#J$%J&&'J()J`!       D!& 3 33 3  3  3v#&*n nn n  n  nnnn $nnnl"")-. .. .  .  ...'. .p 5 55 5  5  55555 5x"& #!&"&*(,9D DD D  D  DDDDDDDDDD !D" #D$ %D&'D()D*+D,-D./D01D23D45D6 $7D8&9D:;DP ' a H)-!#+W WW W  W  W WWW'WWWWWW !W"#W$%W&'W()W*+W,-Wv)*BX]0 0"%0690QU000h:;R`g!%Z Z(+Z36ZGJZZ]ZZZ|HL! !"%!@D!!H  "+,"'TNR2Vr:4  . z TV r"