siJdZddlZddlZddlmZmZddlmZddlmZm Z m Z m Z ee jdz dz ZdZ d0d ed ed ed ed ef dZ d1dedededefdZd ed e eee ffdZ d2d ededeee fded ef dZ d2d edede eee fded e ef dZ d3dededeee ffdZ d3dedededeee ffd Z d1ded!ed"eee fd#eee fd$eee ff d%Z d1deded!ed"eee fd#eee fd$eee ff d&Z d3ded'e ed e eee ffd(Z d4d ed)ed'e ed e eee ffd*Zded e eee ffd+Z d5d eded)ed e eee ffd,Z d6d eded-ed)ed e eee ff d.Z d ed eee ffd/Z!y)7z Database operations for opportunities table. This module handles all DB interactions for persisting Layer 3/3.5/4 analysis results. Designed to be imported into analyze.py and video_generation.py. N)datetimetimezone)Path)DictAnyOptionalListoutputztrends_history.dbcdtjt}|j}|j d|j r|j yttjdz dz }|jr|j}|jdD]}|jd}|Dcgc]5}|js|jjdr4|7}}dj|j}|sv |j ||j't)d nt+d ||j y cc}w#tj $r&} dt#| j%vrYd } ~ d } ~ wwxYw) z=Run the opportunities table migration if not already applied.z` SELECT name FROM sqlite_master WHERE type='table' AND name='opportunities' F migrationsz001_add_opportunities_table.sql; z--zduplicate columnNz7[DB] Migration 001 applied: opportunities table createdzMigration file not found: T)sqlite3connectDB_PATHcursorexecutefetchonecloser__file__parentexists read_textsplitstrip startswithjoinOperationalErrorstrlowercommitprintFileNotFoundError) connrmigration_path migration_sql statementlinesl code_linesclean_statementes :/home/sietch6/trending-topics-pipeline/db_opportunities.py run_migrationr.sk ??7 #D [[]F NN  (^**\9<]]N&002 &,,S1 IOOD)E%*[aggi @T@TUY@Z![J["ii 399;ONN?3   GH"<^s~ ??7 #D [[]F ,,x|| $ . . 0C NN c>?M J L   FKKMJJL Mr9opportunities_foundstatus error_messagectjt}|j}g}g}|"|j d|j ||w|j d|j ||dvrQ|j d|j t j tjj|"|j d|j ||rF|j ||jddj|d ||j|jy) z0Update an existing channel_analysis_runs record.Nzopportunities_found = ?z status = ?) completedfailedzcompleted_at = ?zerror_message = ?z!UPDATE channel_analysis_runs SET z, z WHERE id = ?)rrrrappendrr5rr6r7rrr!r)r9r<r=r>r$rupdatesparamss r-update_analysis_runrE[s ??7 #D [[]FG F&01 )* |$ f , , NN- . MM(,,x||4>>@ A *+ m$ f/ '0B/C= Q   JJLr;ctjt}tj|_|j }|j d|f|j}|j|r t|Sy)z/Get the most recent analysis run for a channel.zo SELECT * FROM channel_analysis_runs WHERE channel_id = ? ORDER BY id DESC LIMIT 1 N) rrrRow row_factoryrrrrdict)r/r$rrows r-get_latest_analysis_runrKsf ??7 #D{{D [[]F NN   // CJJL Cy r;analysis_run_id opportunityprompt_versionc8tjt}|j}t j t jj}|jd}|jdd}|jdd} |jdt|jdg} |jdd} |jdd } |jd i} | rtj| nd }gd }|Dcic]}||vs||j|}}|rtj|nd }|jd ||||| | | | ||||f |j}|j|j!|Scc}w)z^ Insert a new opportunity from Layer 3 analysis. Returns the new opportunity ID. cluster_idthemesuggested_title topic_counttopics_in_clusterverdictweighted_scorerscoresN confusion_analysisexisting_contentwhy_existing_fails missing_angletarget_audience opening_hookcritical_success_factor structureverdict_reasoningrUcluster_topicssources merged_fromax INSERT INTO opportunities ( channel_id, analysis_run_id, cluster_id, theme, suggested_title, source_item_count, verdict, weighted_score, scores_json, analysis_json, research_status, video_gen_status, prompt_version, created_at ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'pending', 'pending', ?, ?) )rrrrrr5rr6r7getlenjsondumpsrr8r!r)r/rLrMrNr$rr5rPrQrSsource_item_countrVrWrX scores_jsonanalysis_fieldsk analysis_data analysis_jsonopp_ids r-insert_opportunityrqst ??7 #D [[]F ,,x|| $ . . 0C.J OOGR (E!oo&7DJJ}-DM NN OZ  17N]     FKKMJJL M-Ys  FF opportunitiescltjt}|j}t j t jj}g}gd}|D](} | jd} | jdd} | jdd} | jdt| jdg} | jdd}| jd d }| jd i}|rtj|nd }|Dcic]}|| vs|| j|}}|rtj|nd }|jd ||| | | | ||||||f |j|j+|j!|j#|Scc}w)zj Insert multiple opportunities in a single transaction. Returns list of new opportunity IDs. rYrPrQrRrSrTrUrVrWrrXNa INSERT INTO opportunities ( channel_id, analysis_run_id, cluster_id, theme, suggested_title, source_item_count, verdict, weighted_score, scores_json, analysis_json, research_status, video_gen_status, prompt_version, created_at ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'pending', 'pending', ?, ?) )rrrrrr5rr6r7rfrgrhrirrBr8r!r)r/rLrrrNr$rr5opp_idsrlopprPrQrSrjrVrWrXrkrmrnros r-insert_opportunities_batchrvs ??7 #D [[]F ,,x|| $ . . 0CGO)WW\* $''"3R8GGM3sww?RTV7W3XY'')R(!1152&,2djj( 0?L118CGGAJL L5B =1    ?$5w  C    v''(;)> KKMJJL N/Ms  F1$F1opportunity_idresearch_status research_datacVtjt}|j}t j t jj}|rtj|nd}|jd||||f|j|jy)a Update an opportunity with Layer 3.5 research results. Args: opportunity_id: DB ID of the opportunity research_status: 'completed', 'failed', or 'skipped' research_data: Dict containing research_report_content, metadata, etc. Nzz UPDATE opportunities SET research_status = ?, research_json = ?, updated_at = ? WHERE id = ? rrrrrr5rr6r7rhrirr!r)rwrxryr$rr5 research_jsons r-update_opportunity_researchr}s~ ??7 #D [[]F ,,x|| $ . . 0C1>DJJ}-DM NN =#~ > @  KKMJJLr;rPcXtjt}|j}t j t jj}|rtj|nd}|jd|||||f|j|jy)z Update opportunity research by cluster_id (for when we don't have DB ID). This is used when research runs after initial Layer 3 insert. Nz UPDATE opportunities SET research_status = ?, research_json = ?, updated_at = ? WHERE analysis_run_id = ? AND cluster_id = ? r{)rLrPrxryr$rr5r|s r-&update_opportunity_research_by_clusterr5s ??7 #D [[]F ,,x|| $ . . 0C1>DJJ}-DM NN =# K M  KKMJJLr;video_gen_statusvideo_conceptsproduction_spec asset_specsc tjt}|j}t j t jj}|rtj|nd}|rtj|nd} |rtj|nd} |jd||| | ||f|j|jy)a6 Update an opportunity with Layer 4 video generation results. Args: opportunity_id: DB ID of the opportunity video_gen_status: 'completed', 'failed', or 'skipped' video_concepts: Layer 4a output production_spec: Layer 4b output asset_specs: Layer 4c output Nz UPDATE opportunities SET video_gen_status = ?, video_concepts_json = ?, production_spec_json = ?, asset_specs_json = ?, updated_at = ? WHERE id = ? r{) rwrrrrr$rr5 concepts_json spec_json assets_jsons r-update_opportunity_video_genrUs" ??7 #D [[]F ,,x|| $ . . 0C2@DJJ~.dM/> ?+DI-8$**[)dK NN M9k3 WY KKMJJLr;c tjt}|j}t j t jj}|rtj|nd} |rtj|nd} |rtj|nd} |jd|| | | |||f|j|jy)zT Update opportunity video gen by cluster_id (for when we don't have DB ID). Na UPDATE opportunities SET video_gen_status = ?, video_concepts_json = ?, production_spec_json = ?, asset_specs_json = ?, updated_at = ? WHERE analysis_run_id = ? AND cluster_id = ? r{) rLrPrrrrr$rr5rrrs r-'update_opportunity_video_gen_by_clusterr}s ??7 #D [[]F ,,x|| $ . . 0C2@DJJ~.dM/> ?+DI-8$**[)dK NN M9k3Yc df KKMJJLr;verdict_filterctjt}tj|_|j }d}|g}|r7dj dt|z}|d|dz }|j||dz }|j|||j}|j|Dcgc] }t|c}Scc}w)z3Get all opportunities from a specific analysis run.z5SELECT * FROM opportunities WHERE analysis_run_id = ?,?z AND verdict IN ()z ORDER BY weighted_score DESC) rrrrGrHrrrgextendrfetchallrrI) rLrr$rqueryrD placeholdersrowsrJs r-get_opportunities_for_runrs ??7 #D{{D [[]F CE Fxxc.&9 9:  $\N!44 n% ,,E NN5&! ?? DJJL!% &#DI && &s8C limitc@t|}|sgSt|d|S)z=Get opportunities from the latest analysis run for a channel.id)r)rKr)r/rr latest_runs r-get_latest_opportunitiesrs. )4J  $4% r;ctjt}tj|_|j }|j d|f|j}|j|rBt|}dD]0}|j|s tj||||<2|Sy#tj$rYKwxYw)z&Get a single opportunity by its DB ID.z(SELECT * FROM opportunities WHERE id = ?)rkror|video_concepts_jsonproduction_spec_jsonasset_specs_jsonN)rrrrGrHrrrrrIrfrhloadsJSONDecodeError)rwr$rrJresult json_fields r-get_opportunity_by_idrs ??7 #D{{D [[]F NN=?PQ // CJJL c^ Jzz*%)-F:4F)GF:&   ++sB77C  C ctjt}tj|_|j }d}g}|r|dz }|j ||r|dz }|j ||dz }|j ||j|||j}|j|Dcgc] }t|c}Scc}w)z4Get opportunities that haven't had research run yet.z=SELECT * FROM opportunities WHERE research_status = 'pending' AND channel_id = ? AND analysis_run_id = ? ORDER BY CASE verdict WHEN 'HIGH_PRIORITY' THEN 1 WHEN 'WORTH_MAKING' THEN 2 WHEN 'CONDITIONAL' THEN 3 ELSE 4 END, weighted_score DESC LIMIT ? rrrrGrHrrBrrrrI) r/rLrr$rrrDrrJs r-"get_opportunities_needing_researchrs ??7 #D{{D [[]F KE F && j! ++ o&  E MM% NN5&! ?? DJJL!% &#DI && &s?Crequire_researchctjt}tj|_|j }d}g}|r|dz }|r|dz }|j ||r|dz }|j ||dz }|j ||j|||j}|j|D cgc] } t| c} Scc} w)z-Get opportunities that need video generation.z>SELECT * FROM opportunities WHERE video_gen_status = 'pending'z" AND research_status = 'completed'rrrr) r/rLrrr$rrrDrrJs r-#get_opportunities_needing_video_genrs ??7 #D{{D [[]F LE F 55 && j! ++ o&  E MM% NN5&! ?? DJJL!% &#DI && &sCc$tjt}|j}|j d|f|j d}|j d|ft |j}|j d|ft |j}|j d|ft |j}|j d|f|j d}|j|||||dS)z-Get statistics for a channel's opportunities.z7SELECT COUNT(*) FROM opportunities WHERE channel_id = ?rzw SELECT verdict, COUNT(*) FROM opportunities WHERE channel_id = ? GROUP BY verdict z SELECT research_status, COUNT(*) FROM opportunities WHERE channel_id = ? GROUP BY research_status z SELECT video_gen_status, COUNT(*) FROM opportunities WHERE channel_id = ? GROUP BY video_gen_status z?SELECT COUNT(*) FROM channel_analysis_runs WHERE channel_id = ?)total_opportunities by_verdictby_research_statusby_video_gen_status analysis_runs) rrrrrrrIrr)r/r$rtotalr by_researchby_video runs_counts r-get_channel_opportunity_statsr@s ??7 #D [[]F NNA   OO a E NN   foo'(J NN   v()K NN   FOO%&H NNI  "1%JJJL % )'#  r;)rrr)NNN)v3)N)2N)NN )NNTr)"__doc__rhrrrpathlibrtypingrrrr rrrr.rintr:rErKrqrvr}rrrrrrrboolrrr;r-rs ',, x.  ( *-@ @&^    > $ $ $$ $ $Nc3h0H8 999c3h9 9  9@ :::S#X': :  #Y :H%)S>D%) S> F&*&*"& %%%cN%#s(^ % c3h %X&*&*"& cN  #s(^  c3h P!%''I' $sCx.'8 $    I  $sCx.  #(4S>2J2'''''' '' $sCx. ''V! *'*'*'*'  *'  $sCx. *'b6c6d38n6r;