
    nj                     &   d dl Z d dlZd dlmZ d dlmZ d dlmZ d dlmZm	Z	m
Z
 g dZg dZg dZd	Zd
d
dddZd Zd Zd Zd Zd Zd Zd ZdZdddddddZi dg ddg dd g d!d"g d#d$g d%d&g d'd(g d)d*g d+d,g d-d.g d-d/g d0d1g d0d2g d3d4g d3d5g d6d7g d8d9g d:Zd;d<g d=d>d?d@g dAd>d$dBd$gd>d&dCd&gd>d dDd gd>dEdFdGgd>dHdIg dJd>d,dKd,d.gd>d(dLd(gd>d*dMd*gd>dNdOdNgd>dPdQdPgd>dRdSdRdTgd>dUdVg dWd>dXdYdXgd>dZd[dZgd>d\d]d\d^gd>d_d`d_dagd>dbdcdbgd>dddeddgd>dfdfdfgd>dgdhg did>djdkg dld>dmdng dod>dpdqg drd>gZi Zds ZeD ]-  Zeeedt   <   ee eedu         <   edv   D ]  Zee ee      <    / d<g d=fd@g dAfdBd$gfdCd&gfdDd gfdFdGgfdIg dJfdKd,d.gfdLd(gfdMd*gfdOdNgfdQdPgfdSdRdTgfdVg dWfdYdXgfd[dZgfd]d\d^gfd`d_dagfdcdbgfdeddgfdfdfgfdhg difdkg dlfdng dofdqg drfgZdwdxdydzd{d|ded}d~Z d Z!d Z"d Z#d Z$d Z%d Z&d Z'd Z(ddZ)ddZ*d Z+d Z,d Z-d Z.d Z/ddZ)ddZ0ddZ1d Z2d Z3ddZ*d Z4d Z5d Z6ddZ7ddZ8d Z9ddZ:ddZ;d Z<dZ=dd d d d ddZ>d Z?d Z@d ZAddZBd ZC	 	 	 ddZDd ZEy)    N)SequenceMatcher)datetime)Path)DB_PATHDATA_DIRYOUTUBE_RESEARCH_DB_PATH)MusterIdeeHookStrukturArgument	AnleitungBeispielVorlage
ChecklisteStory	FrameworkUmsetzungskandidat)offenu   prüfeninteressantumsetzen	verwerfen)niedrigmittelhocha	  
CREATE TABLE IF NOT EXISTS ce_sources (
    id                      INTEGER PRIMARY KEY AUTOINCREMENT,
    source_system           TEXT NOT NULL DEFAULT 'youtube-research',
    source_video_id         INTEGER NOT NULL,
    youtube_id              TEXT,
    title                   TEXT,
    channel                 TEXT,
    language                TEXT,
    published_at            TEXT,
    youtube_url             TEXT,
    transcript_status       TEXT,
    obsidian_artifact_path  TEXT,
    graphiti_status         TEXT,
    graphiti_import_key     TEXT,
    imported_at             TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    source_snapshot_hash    TEXT,
    UNIQUE(source_system, source_video_id)
);

CREATE TABLE IF NOT EXISTS ce_collections (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    slug        TEXT NOT NULL UNIQUE,
    title       TEXT NOT NULL,
    description TEXT,
    created_at  TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS ce_items (
    id             INTEGER PRIMARY KEY AUTOINCREMENT,
    source_id      INTEGER NOT NULL REFERENCES ce_sources(id) ON DELETE CASCADE,
    collection_id  INTEGER REFERENCES ce_collections(id) ON DELETE SET NULL,
    content_type   TEXT NOT NULL DEFAULT 'unbestimmt',
    is_candidate   INTEGER NOT NULL DEFAULT 0,
    status         TEXT NOT NULL DEFAULT 'offen',
    created_at     TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at     TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(source_id)
);

CREATE TABLE IF NOT EXISTS ce_ratings (
    id                  INTEGER PRIMARY KEY AUTOINCREMENT,
    item_id             INTEGER NOT NULL REFERENCES ce_items(id) ON DELETE CASCADE,
    nachahmbarkeit      INTEGER NOT NULL DEFAULT 3 CHECK(nachahmbarkeit BETWEEN 1 AND 5),
    aufwand             TEXT NOT NULL DEFAULT 'mittel' CHECK(aufwand IN ('niedrig','mittel','hoch')),
    umsetzbarkeit       TEXT NOT NULL DEFAULT 'mittel' CHECK(umsetzbarkeit IN ('niedrig','mittel','hoch')),
    erwarteter_nutzen   TEXT NOT NULL DEFAULT 'mittel' CHECK(erwarteter_nutzen IN ('niedrig','mittel','hoch')),
    rated_at            TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(item_id)
);

CREATE TABLE IF NOT EXISTS ce_notes (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    item_id     INTEGER NOT NULL REFERENCES ce_items(id) ON DELETE CASCADE,
    note_text   TEXT NOT NULL,
    created_at  TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
TEXTINTEGER)summary
skill_fileis_high_valueclaude_scorec                      t        j                  dd       t        j                  t	        t
                    } t        j                  | _        | S )zUVerbindung zur eigenen content_extraction.db. Einzige Datei, in die geschrieben wird.T)parentsexist_ok)r   mkdirsqlite3connectstrr   Rowrow_factoryconns    
/tmp/db.pyget_dbr.   U   s3    NN4$/??3w<(D{{DK    c                     | j                  d      j                         D ch c]  }|d   	 }}t        j                         D ]!  \  }}||vs| j                  d| d|        # y c c}w )NzPRAGMA table_info(ce_sources)   z"ALTER TABLE ce_sources ADD COLUMN  )executefetchall_CE_SOURCES_MIGRATION_COLUMNSitems)r,   rowexistingcolcoltypes        r-   _migrate_ce_sources_columnsr;   ]   sr    "&,,/N"O"X"X"Z[3A[H[5;;= OWhLL=cU!G9MNO \s   A)c                 
   | j                  d      j                         D ch c]  }|d   	 }}d|vrK| j                  d      j                         d   }|dk(  r#| j                  d       | j                  d       yyyc c}w )	a  Aeltere Installationen hatten ce_ratings ohne erwarteter_nutzen und mit
    nachahmbarkeit als Text-Enum statt Integer 1-5. Da ce_ratings zum Zeitpunkt
    dieser Migration nachweislich leer war (0 Zeilen), wird die Tabelle sicher
    per Drop+Recreate auf das neue Schema migriert. Enthaelt die Tabelle bereits
    Zeilen, wird NICHT automatisch migriert (kein Datenverlust-Risiko eingehen).zPRAGMA table_info(ce_ratings)r1   erwarteter_nutzenzSELECT COUNT(*) FROM ce_ratingsr   zDROP TABLE ce_ratingsaJ  
                CREATE TABLE ce_ratings (
                    id                  INTEGER PRIMARY KEY AUTOINCREMENT,
                    item_id             INTEGER NOT NULL REFERENCES ce_items(id) ON DELETE CASCADE,
                    nachahmbarkeit      INTEGER NOT NULL DEFAULT 3 CHECK(nachahmbarkeit BETWEEN 1 AND 5),
                    aufwand             TEXT NOT NULL DEFAULT 'mittel' CHECK(aufwand IN ('niedrig','mittel','hoch')),
                    umsetzbarkeit       TEXT NOT NULL DEFAULT 'mittel' CHECK(umsetzbarkeit IN ('niedrig','mittel','hoch')),
                    erwarteter_nutzen   TEXT NOT NULL DEFAULT 'mittel' CHECK(erwarteter_nutzen IN ('niedrig','mittel','hoch')),
                    rated_at            TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    UNIQUE(item_id)
                );
            N)r3   r4   fetchoneexecutescript)r,   r7   r8   counts       r-   _migrate_ce_ratings_schemarA   d   s     #',,/N"O"X"X"Z[3A[H[(*>?HHJ1MA:LL01     + \s   B c                      t               } | j                  t               t        |        t	        |        | j                          | j                          y N)r.   r?   SCHEMAr;   rA   commitcloser+   s    r-   init_dbrG   ~   s9    8Dv%t$KKMJJLr/   c                  D    dt          d} t        j                  | d      S )zRead-only Verbindung zu YouTube Research knowledge.db.
    Wird ausschliesslich lesend genutzt, niemals fuer Schreiboperationen.zfile:z?mode=roTuri)r   r&   r'   rI   s    r-   get_youtube_research_rorK      s%     *+8
4C??3D))r/   c                  ~    t               } | j                  d      j                         d   }| j                          |S )NSELECT COUNT(*) FROM ce_sourcesr   r.   r3   r>   rF   r,   ns     r-   count_sourcesrQ      s4    8D67@@B1EAJJLHr/   c                  ~    t               } | j                  d      j                         d   }| j                          |S )NzSELECT COUNT(*) FROM ce_itemsr   rN   rO   s     r-   count_itemsrS      s4    8D45>>@CAJJLHr/   i        $@g      @g      @       @      ?        )titler   channelr   obsidian_artifact_pathgraphiti_import_keysocial media)r\   social-mediasocial media postssocial-media-post-erstellungpostspost	   beiträgecontentcontent creationcontent-erstellunglinkedin	instagramreelscarouselhookhooks	kurzvideo
kurzvideosshortscaptioncopyzsocial contentr^   )r\   r]   r^   r_   r`   ra   rb   rc   rd   re   rf   rg   rh   ri   rj   rk   rl   rm   rn   ro   rp   rc   )rc   rd   re   content erstellungcontent strategiecontent-strategiezcontent planzcontent-plancontent marketingr\   rf   rg   rh   rj   rk   rd   )rd   re   rq   rc   r^   rf   rg   rh   ri   rj   rk   rf   )rf   zlinkedin contentzlinkedin postsrb   r`   ra   rc   rg   )rg   zinstagram contentrh   ri   r`   ra   rc   rh   )rh   rn   rl   rm   zvideo hooksrj   rk   ri   )ri   	karussellzlinkedin carouselzinstagram carouselr`   rc   rj   )rj   rk   zyoutube hookszsocial hookszcontent hookz
title hookrk   rl   )rl   rm   rn   rh   r^   rj   rm   ra   )ra   r`   rb   beitragr^   rc   rf   rg   r`   social)r\   r^   r]   rc   rf   rg   media)r\   r^   rc   rf   rg   rh   rj   r_   )
r_   r^   rd   re   rf   rg   rh   rj   rk   ri   social_mediaSocial Mediar\   r]   rw   )keylabelneedlessocial_media_postszSocial Media Posts)r^   r_   r`   ra   LinkedIn	InstagramContentcontent_marketingContent Marketingrt   post_erstellungPost-Erstellungzpost-erstellungzpost erstellungr`   ra   rb   rv   r   ReelsCarouselcanvaCanvaclaudeClaudekiKIaiautomatisierungAutomatisierungr   
automationautomateyoutubeYouTubern   ShortsskriptSkriptscriptagentenAgentenagentobsidianObsidiangraphitiGraphitin8nluftqualitat   Luftqualitätu   luftqualitätluftqualitaetzair quality
airqualityfeuchteanalyseFeuchteanalyser   feuchtehumidityluftung   Lüftungu   lüftunglueftungventilationkundenkommunikationKundenkommunikationr   zcustomer communicationkommunikationc                     t        | xs d      j                         }|j                  dd      j                  dd      j                  dd      }t        j                  dd|      }|j                         S N -r2   _/z\s+)r(   lowerreplaceresubstrip)valuetexts     r-   _tag_lookup_normr      sa    u{!!#D<<S!))#s3;;CED66&#t$D::<r/   r|   r}   r~   TitelZusammenfassungKanalz
YouTube-IDzArtefakt-DateinamezSkill-Dateinamez
High Value)rX   r   rY   
youtube_idrZ   r   r[   
high_valuec                     | xs dj                         } | j                  dd      j                  dd      j                  dd      } t        j                  dd|       } | j	                         S r   )r   r   r   r   r   )r   s    r-   _normalize_search_textr   -  s\    JBD<<S!))#s3;;CED66&#t$D::<r/   c                 z    | sy	 t        t        |             j                  S # t        $ r t        |       cY S w xY wNr   )r   r(   name	Exceptionr   s    r-   _filename_textr   4  s;    CJ$$$ 5zs   # ::c                 :    | syt        |       }|syt        |      S r   )r   r   )r   filenames     r-   _searchable_path_textr   =  s#    e$H!(++r/   c           	         | j                  d      | j                  d      | j                  d      t        | j                  d            t        | j                  d            g}t        dj                  d |D                    S )NrX   r   rY   r   rZ   r2   c              3   :   K   | ]  }t        |xs d         ywr   N)r(   ).0parts     r-   	<genexpr>z+_searchable_content_text.<locals>.<genexpr>N  s     *Mt3tzr?*Ms   )getr   r   join)r7   partss     r-   _searchable_content_textr   F  sl    		sww|,-sww789E "#((*Mu*M"MNNr/   c                 (   t        |       }|sg S g t               fd} ||       |j                  d      D ]
  } ||        t        j	                         D ]!  \  }}||v s ||       |D ]
  } ||        # t        t        d      S )Nc                 p    t        |       } | r(| vr#j                  |        j                  |        y y y rC   r   addappendtermseentermss    r-   r   z!_expand_search_terms.<locals>.addX  5    %d+D$HHTNLL %4r/   r2   Tr|   reverse)r   setsplitSEARCH_THEME_EXPANSIONSr6   sortedlen)	query
normalizedr   tokenphrase
expansionsexpr   r   s	          @@r-   _expand_search_termsr   Q  s    '.J	E5D 
O!!#& E
5;;= 
ZK! C
 %S$//r/   c                 z    g }t               }| D ])  }||vs|j                  |       |j                  |       + |S rC   )r   r   r   )valuesoutr   r   s       r-   _unique_preserve_orderr   i  sC    
C5D HHUOJJu Jr/   c                 B     t                t         fd|D              S )Nc              3   .   K   | ]  }|xr |v   y wrC    )r   needler   s     r-   r   z%_text_contains_any.<locals>.<genexpr>u  s     ?Vv(&D.(?s   )r   any)r   r~   s   ` r-   _text_contains_anyr   s  s    !$'D?w???r/   c                     t        |       g }|D ]+  \  }}t        fd|D              s|j                  |       - |S )Nc              3   &   K   | ]  }|v  
 y wrC   r   r   r   combineds     r-   r   z)_collect_result_groups.<locals>.<genexpr>|       8fv!8   )r   r   r   )r7   groupshitsr}   r~   r  s        @r-   _collect_result_groupsr	  x  sF    ',HD  w888KK Kr/   c                 f   	 dg dfddgfddgfdg dfd	d
gfdg dfdddgfddgfddgfddgfddgfdddgfdg dfddgfdd gfd!d"d#gfd$d%d&gfd'd(gfd)d*gfd+d+gfd,g d-fd.g d/fd0g d1fd2g d3fd4d5gfd6d7d8gfg}t        d9j                   fd:d;D                    	xs g D cg c]  }|s|	 c}g }t               }|D ]c  \  }}t        	fd<|D              st        fd=|D              r&||vr"|j	                  |       |j                  |       t        |      |k\  sc n |d | S c c}w )>Nrz   r{   r   rf   r   rg   r   )rt   rs   rr   r   rc   r   r   r   rj   rk   r   rh   r   ri   r   r   r   r   r   r   r   r   r   r   r   r   rn   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   zMallorca AirServiceszmallorca airservicesu   LüftungsProfiu   lüftungsprofilueftungsprofir2   c              3   Z   K   | ]"  }t        j                  |      xs d        $ ywr   )r(   r   )r   fieldr7   s     r-   r   z&_build_visible_tags.<locals>.<genexpr>  s'      /Us3775>3GR/H /s   (+)rX   r   rY   rZ   r   r[   r   youtube_urlc              3   &   K   | ]  }|v  
 y wrC   r   r  s     r-   r   z&_build_visible_tags.<locals>.<genexpr>  r  r  c              3   V   K   | ]   }|t        d j                              v  " yw)r2   N)r   r   )r   r   matched_termss     r-   r   z&_build_visible_tags.<locals>.<genexpr>  s6       @Q  EJ`adaiaijwaxJy@y  @Qs   &))r   r   r   r   r   r   r   )
r7   r  limit	tag_rulesttagsr   r}   r~   r  s
   ``       @r-   _build_visible_tagsr    s   	CD	j\"	{m$	]^	YK 	kl	&'"#	7)	j\"	7)	H:	d|	IJ	YK 	H:	Hh'(	Y()	j\"	j\"		YZ	DE	<=	 bc	"8!9:	,.>?@5I8 &chh / W / ' H "/!4";1Q;MD5D# w888C  @Q  IP  @Q  =QD E"t9 < <s   &D..D.c           	         | xs d} | j                         }d }d }|D ]%  }|j                  |      }|dk7  s|||k  s"|}|}' |*| d | j                         }|t        |       |kD  rdz   S dz   S t	        d|dz
        }	t        t        |       |t	        t        |      d      z         }
| |	|
 j                         }|	dkD  rd|z   }|
t        |       k  r|dz   }|S Nr   u   …r   F   Z   r   findr   r   maxminr   r   r  loweredbest_pos	best_termr   posexcerptstartends              r-   _search_excerptr(        :2DjjlGHI ll4 "9(*cHnHI	
 v,$$&3t9u#4%=="==8b=!E
c$iCI$;;
<C5o##%Gqy'/
SYE/Nr/   c           	         t        | j                  d            t        | j                  d            t        | j                  d            t        | j                  d            t        | j                  d            d}d}g }g }t        |      }|j                         D ]&  \  }}	|	s
d}
|D ])  }|s||	v s|t        |   z  }|j                  |       d	}
+ |r&||	v r"|t        |   d
z  z  }|j                  |       d	}
|
st        |      dk\  rd|vrt        t        j                  d|	            }|D ]x  }t        |      dk  s#t        t        |      t        |      z
        dkD  r5t        d ||      j                         }|dk\  sV|t        |   dz  z  }|j                  |       d	}
 n |
s|j                  |       ) | j                  d      r|dz  }|j                  d       | j                  d      /	 |t        t        | j                  d      xs d      dz  d      z  }t#        |      }t#        |      }|||fS # t        t         f$ r Y ,w xY w)NrX   rY   r   rZ   r   )rX   rY   r   rZ   r   rW   FTrV      r2   u   [a-z0-9äöüß]+   g)\(?g?r    g      @r   r!   r   rT   rU   )r   r   r   r6   SEARCH_FIELD_WEIGHTSr   r   r   r   findallabsr   ratior  float	TypeError
ValueErrorr   )r7   r   r   	haystacksscorer  matched_fields
query_normr  r   field_matchr   
candidates	candidate
similaritys                  r-   _score_source_rowr<    so   '(89)#'')*<=)#'')*<="7@X8Y"Z+CGGL,ABI EMN'.J ( )t 	#D-e44$$T*"		#
 *,)%0366E  ,K s:!3:8MRZZ(<dCDJ' 	y>A%S^c*o-M)NQR)R,T:yIOOQ
%1%83>>E!((3"&K !!%(=)@ wwl+
ww~*	Ssww~6;!<tCSIIE +=9M+N;N-// :& 		s   .I I I c                     g }| D ]0  }t         j                  |      }|s||vs |j                  |       2 |sydj                  |d d       S )NzBegriffs-Match,    )MATCH_FIELD_LABELSr   r   r   )r6  labelsr  r}   s       r-   _format_match_reasonrB    sY    F !"&&u-U&(MM% ! 99VBQZ  r/   c                     t        |       }|sg S g t               fd} ||       |j                         D ]
  } ||        S )Nc                 p    t        |       } | r(| vr#j                  |        j                  |        y y y rC   r   r   s    r-   r   z_query_core_terms.<locals>.add  r   r/   )r   r   r   )r   r   r   r   r   r   s       @@r-   _query_core_termsrE    sS    '.J	E5D 
O!!# E
Lr/   c                 t    t        |       }t        j                  |      }|r|d   S |j                  dd      S )Nr|   r2   r   )r   TAG_RULE_LOOKUPr   r   )tagtag_normrules      r-   _normalize_filter_tagrK     s:    %c*Hx(D4;>H$4$4S#$>>r/   c                 r    | sy t         j                  t        |             xs t         j                  |       S rC   )rG  r   r   )rH  s    r-   _resolve_tag_rulerM  &  s0    5c:;W?R?RSV?WWr/   c                    t        |       t        dj                  d |xs g D                    g }t               }t        D ]u  }t        fd|d   D              st        fd|d   D              r5|d   |vr.|j                  |d   |d   d       |j                  |d          t        |      |k\  su n |d | S )	Nr2   c              3   &   K   | ]	  }|s|  y wrC   r   )r   r  s     r-   r   z&_build_visible_tags.<locals>.<genexpr>.  s     8_q]^8_s   c              3   &   K   | ]  }|v  
 y wrC   r   r  s     r-   r   z&_build_visible_tags.<locals>.<genexpr>2  s     @fv!@r  r~   c              3   &   K   | ]  }|v  
 y wrC   r   )r   r   matched_terms_texts     r-   r   z&_build_visible_tags.<locals>.<genexpr>2  s        HDioRdHd  HDr  r|   r}   )r|   r}   )	r   r   r   r   	TAG_RULESr   r   r   r   )r7   r  r  r  r   rJ  r  rR  s         @@r-   r  r  ,  s    ',H/8_]EXVX8_0_`D5D @Y@@C  HDsw  yB  tC  HD  EDE{$&DK$w-HIe%t9 <r/   c                 d    t        | |t        t                    D cg c]  }|d   	 c}S c c}w )Nr  r|   )r  r   rS  )r7   r  rH  s      r-   _row_tag_keysrV  ;  s*    "5c=PST]P^"_`3CJ```s   -c                 4    t        |      }|t        | |      v S rC   )rK  rV  )r7   tag_keyr  normalized_tag_keys       r-   _row_has_tag_keyrZ  ?  s    .w7sM!BBBr/   c                 `    t        t        |             }|xs g D ]  }t        |      |v s y y)Nengbroad)r   rE  r   )r   r  
core_termsr   s       r-   _classify_match_qualityr_  D  s;    &u-.J# !$':5 r/   c                     | dk(  rdS dS )Nr\  zEnger TrefferzErweiterter Trefferr   )match_qualitys    r-   _format_match_quality_labelrb  L  s    +u4?O:OOr/   c           	         | xs d} | j                         }d }d }|D ]%  }|j                  |      }|dk7  s|||k  s"|}|}' |*| d | j                         }|t        |       |kD  rdz   S dz   S t	        d|dz
        }	t        t        |       |t	        t        |      d      z         }
| |	|
 j                         }|	dkD  rd|z   }|
t        |       k  r|dz   }|S r  r  r   s              r-   r(  r(  P  r)  r/   c                     | sy t        |       j                         }|sy ||j                  dd      g}|D ]  }	 t        j                  |      c S  dD ]  }	 t        j                  ||      c S  y # t
        $ r Y Gw xY w# t
        $ r Y 8w xY w)NZz+00:00)z%Y-%m-%d %H:%M:%Sz%Y-%m-%d %H:%Mz%Y-%m-%dz%d.%m.%Y %H:%M:%Sz%d.%m.%Y %H:%Mz%d.%m.%Y)r(   r   r   r   fromisoformatr3  strptime)r   r   r9  r:  fmts        r-   _parse_source_datetimeri  g  s    u:DS(34J 		)))44
 	$$T3//   		  		s#   A2B2	A>=A>	BBc                    t        | j                  d            }t        | j                  d            }|xs |}|r|j                         nd}|t        | j                  d      xs d      t        | j                  d      xs d      fS )Npublished_atimported_atrW   source_video_idr   id)ri  r   	timestampintr7   	publishedimportedbesttss        r-   _row_sort_key_newestrv    su    &sww~'>?I%cggm&<=H D!	sBCGG-.3!4c#''$-:L16MNNr/   c                 Z   t        | j                  d            }t        | j                  d            }|xs |}|r|j                         nd}t        | j                  d      xs d       | t	        | j                  d      xs d       t	        | j                  d      xs d       fS )Nrk  rl  rW   search_scorerm  r   rn  )ri  r   ro  r1  rp  rq  s        r-   _row_sort_key_scorery    s    &sww~'>?I%cggm&<=H D!	sB377>*1c22RC#cggFW>X>]\]:^9^adehelelmqerewvwax`xyyr/   c                    t        |       }t        |      }|r|d   n
t        |      }|sHg i d| d|d|r|d   n|xs ddg dg d	d
dd
ddd|dd
dd
d|dd
dd
dd
dt        |      fS t	               }	 |j                  d      j                         }|j                          g }	t        |       }
|D ]  }t        |      }t        || |      \  }}}|d
k  r&|j                  d      xs d}|j                  d      xs dj                         }t        ||d      }|D cg c]  }|d   	 }}t        | |      }t        |d      |d<   |d d |d<   ||d<   t!        |      |d <   ||d!<   t#        |      |d"<   |j                         rt%        ||      nd#|d$<   d%|d&    |d'<   |rd(| d)nd|d*<   ||d+<   ||d,<   |	j'                  |        |xs d-j                         j)                         }|d.k(  r|	j+                  t,        d/0       n|	j+                  t.        1       g }t0        D ]4  t3        fd2|	D              }|s|j'                  d   d   |d3       6 |r(|	D cg c]  }|d   |j                  d,g       v s| }}n2|r%|	D cg c]  }||j                  d,g       v s| }}nt5        |	      }t7        |      }t3        d4 |D              }||z
  }|d | }|i d| d|d|r|d   n|xs dd|d|d	|dt7        |      d||kD  d|dt        |      d|dt7        |      d|d|d|dt7        |	      fS # |j                          w xY wc c}w c c}w c c}w )5Nr|   r   
active_tagactive_tag_labelr}   r   expanded_termsper_term_countstotal_countr   shown_counthas_moreFr  total_matchesreturned_countsortnarrow_countbroad_countbase_total_countlimit_paramSELECT * FROM ce_sourcesr   r      rU  r,  rx     match_termsmatch_fieldsmatch_reasonra  match_quality_labelzKeine Zusammenfassung vorhandensummary_excerptz/videos/rn  
detail_urlzhttps://img.youtube.com/vi/z/hqdefault.jpgthumbnail_urlvisible_tagstag_keysr5  newestTr   )r|   c              3   R   K   | ]  }d    |j                  dg       v sd   yw)r|   r  r1   Nr   )r   r7   rJ  s     r-   r   z search_videos.<locals>.<genexpr>  s(     W#UswwzSU?V0VAWs   '')r|   r}   r@   c              3   J   K   | ]  }|j                  d       dk(  sd  yw)ra  r\  r1   Nr  )r   r7   s     r-   r   z search_videos.<locals>.<genexpr>  s      RS0HE0QqRs   ##)r   rM  rK  r(   r.   r3   r4   rF   r   dictr<  r   r   r  r_  roundrB  rb  r(  r   r   r  rv  ry  rS  sumlistr   )r   rH  r  r  r   active_ruleactive_tag_keyr,   rowsbase_scoredr7  r7   r5  r  r6  r   r   r  r  r  ra  r~  r@   filteredr  r  r  slicedrJ  s                               @r-   search_videosr    sX    'E#C(K+6['<QRU<VN  NGU  NL.  NBTnyVabiVj  AD  AJ  HJ  N  M]  _a  N  ct  vx  N  zG  IJ  N  LY  [\  N  ^h  jo  N  qx  z  N  AP  RS  N  Ue  gh  N  jp  rv  N  xF  HI  N  KX  Z[  N  ]o  qr  N  tA  CF  GL  CM  N  N  	N8D||67@@B

K'.J  3i/@eU/S,}nA:'')$*ggl+1r88:
*3QG&23AeH33/}E#E1oN*3B/M,N2>BN,O%@%O!"DKMMO%!@Yz&s4yk2L[e!<ZLWkmO*N"J3+ . OG""$**,Dx14@01O aWWW""4;gY^#_`a
 #.`C+e2DPZ\^H_2_C``	#.\C.CGGJXZD[2[C\\$h-KRHRRL,KfuF n 	KK0ciR 	%	
 	? 	{ 	s6{ 	K%' 	 	s5z 	 	#f+ 	 	 	{  	C,!  g 	

 48 a\s*   ?M -M-5M2M2M79M7M*c                     t        | |      S )NrU  )r  )r   r  s     r-   search_sourcesr    s    e,,r/   c                     | dv ry| dv ryy)zGWandelt 'yes'/'no' (oder leer) aus Query-Parametern in True/False/None.)yesja1trueT)nonein0falseFNr   r   s    r-   _yn_to_boolr    s    **,,r/   c                    | xs i } g g }}| j                  d      xs dj                         }|r,d| d}|j                  d       |j                  ||||g       | j                  d      xs dj                         }|r"|j                  d       |j                  |       | j                  d      xs dj                         }|r"|j                  d       |j                  |       | j                  d	      xs dj                         }|r"|j                  d
       |j                  |       t	        | j                  d            }|&|j                  d       |j                  |rdnd       t	        | j                  d            }	|	du r|j                  d       n|	du r|j                  d       t	        | j                  d            }
|
du r|j                  d       n|
du r|j                  d       d}|r|ddj                  |      z   z  }|dz  }|j                  t               t               }|j                  ||      j                         }|j                  d|rddj                  |      z   ndz   |d d       j                         d   }|j                          |D cg c]  }t        |       c}|fS c c}w )Nqr   %zG(title LIKE ? OR channel LIKE ? OR summary LIKE ? OR youtube_id LIKE ?)rY   zchannel = ?languagezlanguage = ?transcript_statusztranscript_status = ?r    zis_high_value = ?r1   r   has_artifact_pathTzCobsidian_artifact_path IS NOT NULL AND obsidian_artifact_path != ''Fz?(obsidian_artifact_path IS NULL OR obsidian_artifact_path = '')has_itemz&id IN (SELECT source_id FROM ce_items)z*id NOT IN (SELECT source_id FROM ce_items)r   WHERE  AND z ORDER BY id LIMIT ?rM   r  )r   r   r   extendr  r   MAX_RESULTSr.   r3   r4   r>   rF   r  )filterswhereparamsr  likerY   r  r  r    r  r  sqlr,   r  totalrs                   r-   list_sourcesr    s   mG6E	S		R&&(A1#QxU	
 	tT4./{{9%+224G]#gJ'-2446H^$h %89?RFFH,-'(O <=M ()=aa0#GKK0C$DED Z[	e	#VW7;;z23H4=>	U	AB
$Cy7<<...!!C
MM+8D<<V$--/DLL)PUYe9L-L[]^s hjE 	JJL!"DG"E))"s   /Kc                    | xs i } g g }}| j                  d      xs dj                         }|r+d| d}|j                  d       |j                  |||g       | j                  d      xs dj                         }|r"|j                  d       |j                  |       | j                  d      xs dj                         }|r"|j                  d       |j                  |       | j                  d	      xs dj                         }|r+|j                  d
       |j                  t	        |             | j                  d      xs dj                         }|r"|j                  d       |j                  |       | j                  d      xs dj                         }	|	r"|j                  d       |j                  |	       | j                  d      xs dj                         }
|
r"|j                  d       |j                  |
       d}d|z   }|r|ddj                  |      z   z  }|dz  }|j                  t               t               }|j                  ||      j                         }|j                  d|z   |rddj                  |      z   ndz   |d d       j                         d   }|j                          |D cg c]  }t        |       c}|fS c c}w )Nr  r   r  z8(s.title LIKE ? OR s.channel LIKE ? OR s.summary LIKE ?)content_typezi.content_type = ?statuszi.status = ?imitation_scorezr.nachahmbarkeit = ?effortzr.aufwand = ?feasibilityzr.umsetzbarkeit = ?expected_valuezr.erwarteter_nutzen = ?zbFROM ce_items i JOIN ce_sources s ON s.id = i.source_id LEFT JOIN ce_ratings r ON r.item_id = i.idzSELECT i.*, s.title AS source_title, s.channel AS source_channel, r.nachahmbarkeit, r.aufwand, r.umsetzbarkeit, r.erwarteter_nutzen r  r  z ORDER BY i.id LIMIT ?zSELECT COUNT(*) r  r   )r   r   r   r  rp  r   r  r.   r3   r4   r>   rF   r  )r  r  r  r  r  r  r  r  r  r  r  baser  r,   r  r  r  s                    r-   
list_itemsr  -  s   mG6E	S		R&&(A1#QxOPtT4()KK/52<<>L)*l#kk(#)r002F^$f{{#45;BBDO+,c/*+kk(#)r002F_%f;;}-3::<K*+k"kk"239r@@BN./n%	5 		MOS	T  y7<<...##C
MM+8D<<V$--/DLLT!Ye1D%DSUVs hjE 	JJL!"DG"E))"s   Kc                  z   t               } | j                  d      j                         D cg c]  }|d   	 }}| j                  d      j                         D cg c]  }|d   	 }}| j                  d      j                         D cg c]  }|d   	 }}| j                          |||dS c c}w c c}w c c}w )z;Liefert distincte Werte fuer Filter-Dropdowns auf /sources.zRSELECT DISTINCT channel FROM ce_sources WHERE channel IS NOT NULL ORDER BY channelr   zUSELECT DISTINCT language FROM ce_sources WHERE language IS NOT NULL ORDER BY languagezpSELECT DISTINCT transcript_status FROM ce_sources WHERE transcript_status IS NOT NULL ORDER BY transcript_status)channels	languagestranscript_statuses)r.   r3   r4   rF   )r,   r  r  r  statusess        r-   get_distinct_source_valuesr  m  s    8D"ll\hj! H   $||_ hj!1 I  #llzhj! H  	JJL yQYZZs   B.B3
B8a  
SELECT
    v.id                    AS source_video_id,
    v.youtube_id            AS youtube_id,
    v.title                 AS title,
    v.channel               AS channel,
    v.language              AS language,
    v.transcript_status     AS transcript_status,
    v.summary               AS summary,
    v.skill_file            AS skill_file,
    v.is_high_value         AS is_high_value,
    v.claude_score          AS claude_score,
    r.artifact_path         AS artifact_path,
    r.graphiti_status       AS graphiti_status,
    r.graphiti_import_key   AS import_key
FROM videos v
LEFT JOIN source_processing_registry r
       ON r.source_system = 'youtube-research'
      AND r.source_id = CAST(v.id AS TEXT)
ran_at
read_countinsertedupdated	unchangederrorc                       t        t              S rC   )r  _last_import_statsr   r/   r-   get_last_import_statsr    s    "##r/   c                     ddl } | j                   j                         j                         dz   dddddd}t               }	 t        j
                  |_        |j                  t              j                         D cg c]  }t        |       }}|j                          t        |      |d<   t               }	 |D ]  }|d   rd|d    nd}|j                  d|d	   f      j                         }d
|d	   |d   |d   |d   |d   ||d   |d   |d   |d   |d   |d   |d   |d   f}	| |j                  d|	       |dxx   dz  cc<   |d   |d   k7  xs |d   |d   k7  xs |d   |d   k7  xs| |d   |d   k7  xso |d   |d   k7  xsb |d   |d   k7  xsU |d   |d   k7  xsH |d   |d   k7  xs; |d   |d   k7  xs. |d   duxr |d   |d   k7  xs |d   |d   k7  xs |d   |d   k7  }
|
r`|j                  d|d   |d   |d   |d   ||d   |d   |d   |d   |d   |d   |d   n|d   |d   |d   |d   f       |dxx   dz  cc<   |dxx   dz  cc<    |j                          	 |j                          t&        j)                  |       |S c c}w # |j                          w xY w# t         $ r$}|j#                          t%        |      |d<    d}~ww xY w# |j                          w xY w) zLiest videos + source_processing_registry read-only aus knowledge.db
    und spiegelt sie idempotent (Upsert ueber UNIQUE(source_system, source_video_id))
    in ce_sources. Schreibt ausschliesslich in content_extraction.db.r   Nre  r  r  r   zhttps://youtube.com/watch?v=as  
                SELECT id, youtube_id, title, channel, language, transcript_status,
                       summary, skill_file, is_high_value, claude_score,
                       obsidian_artifact_path, graphiti_status, graphiti_import_key
                FROM ce_sources
                WHERE source_system = 'youtube-research' AND source_video_id = ?
                rm  zyoutube-researchrX   rY   r  r  r   r   r    r!   artifact_pathgraphiti_status
import_keya  
                    INSERT INTO ce_sources (
                        source_system, source_video_id, youtube_id, title, channel,
                        language, youtube_url, transcript_status, summary, skill_file,
                        is_high_value, claude_score, obsidian_artifact_path,
                        graphiti_status, graphiti_import_key
                    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                    r  r1   rZ   r[   a  
                        UPDATE ce_sources SET
                            youtube_id = ?, title = ?, channel = ?, language = ?,
                            youtube_url = ?, transcript_status = ?, summary = ?,
                            skill_file = ?, is_high_value = ?, claude_score = ?,
                            obsidian_artifact_path = ?, graphiti_status = ?,
                            graphiti_import_key = ?
                        WHERE id = ?
                        rn  r  r  r  )r   utcnow	isoformatrK   r&   r)   r*   r3   _IMPORT_SOURCE_QUERYr4   r  rF   r   r.   r>   rE   r   rollbackr(   r  update)r   statsro_connr  r  r,   r7   r  r8   insert_valueschangedexcs               r-   $import_sources_from_youtube_researchr    s9     ##**,6683>E &'G%kk!(1E!F!O!O!QRAQRRd)E,8D\ S	,C |$ /s</@.AB*.  || &')	 hj  #%&L!GIJ'(IL!O$N#O$%&L!M$  "
 j!Q&! \*c,.?? L(CL8L	*c)n<L  
+s:>L   34<O8PP	L
  	*c)n<L  -\1BBL  0C4HHL  /3~3FFL O,D8wXF^=_cfgvcw=wL   12c:K6LLL   56#l:KK  LL  -s7|S^SQ[_'-@)A3y>-s?/CSEX474H4TC0Zbc{Z|  B  CT  U  WZ  [g  Wh$TN$ )$)$+&!+&gS	,j 	 	

e$LM Sx  Sg
 	

sC   :J <JJ 9F8J3 J J03	K <KK  K# #K5c                     t               }|j                  d| f      j                         }|j                          |rt	        |      S d S )N%SELECT * FROM ce_sources WHERE id = ?)r.   r3   r>   rF   r  )	source_idr,   r7   s      r-   
get_sourcer  '  s?    8D
,,>
M
V
V
XCJJL49%%r/   c                    |t         vrd}t               }	 |j                  d| f      j                         }|	 |j	                          y|j                  d| f      j                         }|t        |      df|j	                          S |j                  d| |f       |j                          |j                  d| f      j                         }t        |      df|j	                          S # |j	                          w xY w)a  Erzeugt einen ce_item-Kandidaten aus einer ce_source.
    Gibt (item_dict, created_bool) zurueck. Existiert bereits ein Kandidat fuer
    diese Quelle (UNIQUE(source_id)), wird der bestehende Kandidat zurueckgegeben
    und created=False gesetzt -- es entsteht kein Duplikat.
unbestimmtz&SELECT id FROM ce_sources WHERE id = ?)NFz*SELECT * FROM ce_items WHERE source_id = ?Fz^INSERT INTO ce_items (source_id, content_type, is_candidate, status) VALUES (?, ?, 1, 'offen')T)CONTENT_TYPESr.   r3   r>   rF   r  rE   )r  r  r,   sourcer8   new_rows         r-   create_item_from_sourcer  .  s    
 =(#8DFU^^`>& 	

# <<89,

(* 	 >5( 	

 	(%	

 	,,89,

(* 	 G}d"



s   #C- /C- AC- -C?c                 <   t               }	 |j                  d| f      j                         }|	 |j                          y t	        |      }|j                  d|d   f      j                         }|j                  d| f      j                         }|j                  d| f      j                         }|rt	        |      nd |d<   |rt	        |      nd |d<   |D cg c]  }t	        |       c}|d<   ||j                          S c c}w # |j                          w xY w)	Nz#SELECT * FROM ce_items WHERE id = ?r  r  z*SELECT * FROM ce_ratings WHERE item_id = ?zASELECT * FROM ce_notes WHERE item_id = ? ORDER BY created_at DESCr  ratingnotes)r.   r3   r>   rF   r  r4   )item_idr,   itemr  r  r  rP   s          r-   get_item_detailr  P  s   8D||AG:NWWY<  	

 Dz3d;6G5I

(* 	 87*

(* 	 ORYQ[

(* 	 *0fTX)/fTX*/0Qa0W

 1 	

s$   #D	 BD	 D,D	 D	 	Dc                    t               }	 |j                  d| f      j                         }	|		 |j                          y||t        vrt        d|      ||t        vrt        d|      ||g g }}
|"|
j                  d       |j                  |       |"|
j                  d       |j                  |       |
j                  d       |j                  |        |j                  dd	j                  |
       d
|       ||nd}||nd}||nd}||nd}t        |      }d|cxk  rdk  st        d       t        d      d|fd|fd|ffD ]$  \  }}|t        vst        | dt         d|       |j                  d| f      j                         }|r|j                  d||||| f       n|j                  d| ||||f       |r|j                  d| |f       |j                          	 |j                          t        |       S # t        $ r |j                           w xY w# |j                          w xY w)a=  Speichert Content-Typ, Status, Rating und Begruendung fuer einen Kandidaten.
    Alle Parameter ausser item_id sind optional; nur uebergebene Felder werden
    gesetzt bzw. mit sinnvollem Default bewertet. Wirft ValueError bei ungueltigen
    Werten (kein stiller Fallback auf Defaults bei explizit falscher Eingabe).z$SELECT id FROM ce_items WHERE id = ?NzUngueltiger content_type: zUngueltiger status: zcontent_type = ?z
status = ?zupdated_at = CURRENT_TIMESTAMPzUPDATE ce_items SET r>  z WHERE id = ?   r   r1      z+nachahmbarkeit muss zwischen 1 und 5 liegenaufwandumsetzbarkeitr=   z muss einer von z sein, war z+SELECT id FROM ce_ratings WHERE item_id = ?zUPDATE ce_ratings SET nachahmbarkeit=?, aufwand=?, umsetzbarkeit=?, erwarteter_nutzen=?, rated_at=CURRENT_TIMESTAMP WHERE item_id=?zrINSERT INTO ce_ratings (item_id, nachahmbarkeit, aufwand, umsetzbarkeit, erwarteter_nutzen) VALUES (?, ?, ?, ?, ?)z7INSERT INTO ce_notes (item_id, note_text) VALUES (?, ?))r.   r3   r>   rF   r  r3  STATUS_VALUESr   r   rp  LEVEL_VALUESrE   r   r  r  )r  r  r  nachahmbarkeitr  r  r=   	rationaler,   r  fieldsr   rP   auer}   valexisting_ratings                      r-   save_item_ratingr
  h  s    8D=||BWJOXXZ<t 	

q #M(I9,9IJKK&"=3F:>??#v'9FF'01l+!l+f%MM:;MM'"LL/		&0A/B-PRXY,8Na*G*6MH!2!>HFQ!JKK JKK%q>OQ+?BUWXAYZ 	]JE3,& E7*:<.TWSZ![\\	] ,,9G:

(* 	 LLRAq!W% LLK!Q1% LLI)$
 	
 	

7##   	

s%   #H D%H 'BH H..H1 1Ic                      t               } 	 | j                  d      j                         }|D cg c]  }t        |       c}| j	                          S c c}w # | j	                          w xY w)a6  Liest alle ce_items mit Quell- und Ratingdaten fuer die Matrix-Ansicht.
    Sortierung: Nachahmbarkeit absteigend, dann erwarteter Nutzen (hoch>mittel>
    niedrig), dann Umsetzbarkeit (hoch>mittel>niedrig), dann Aufwand (niedrig>
    mittel>hoch). Items ohne Rating erscheinen am Ende (NULL sortiert zuletzt).a  
            SELECT
                i.id                AS item_id,
                i.content_type      AS content_type,
                i.status            AS status,
                s.title             AS title,
                s.channel           AS channel,
                s.youtube_id        AS youtube_id,
                s.youtube_url       AS youtube_url,
                r.nachahmbarkeit    AS nachahmbarkeit,
                r.aufwand           AS aufwand,
                r.umsetzbarkeit     AS umsetzbarkeit,
                r.erwarteter_nutzen AS erwarteter_nutzen
            FROM ce_items i
            JOIN ce_sources s ON s.id = i.source_id
            LEFT JOIN ce_ratings r ON r.item_id = i.id
            ORDER BY
                r.nachahmbarkeit DESC,
                CASE r.erwarteter_nutzen
                    WHEN 'hoch' THEN 1 WHEN 'mittel' THEN 2 WHEN 'niedrig' THEN 3 ELSE 4
                END,
                CASE r.umsetzbarkeit
                    WHEN 'hoch' THEN 1 WHEN 'mittel' THEN 2 WHEN 'niedrig' THEN 3 ELSE 4
                END,
                CASE r.aufwand
                    WHEN 'niedrig' THEN 1 WHEN 'mittel' THEN 2 WHEN 'hoch' THEN 3 ELSE 4
                END,
                i.id
            )r.   r3   r4   r  rF   )r,   r  r  s      r-   list_content_matrixr    s\    
 8D"||
< (*= 	> "&&AQ&

 '

s   #A AA A A+)Nr  )   rC   )N2   r5  )r  )r  )NNNNNNN)Fr   r&   difflibr   r   pathlibr   configr   r   r   r  r   r  rD   r5   r.   r;   rA   rG   rK   rQ   rS   r  r-  r   rS  rG  r   _rule_needleSEARCH_RESULT_TERM_GROUPSr@  r   r   r   r   r   r   r   r	  r  r(  r<  rB  rE  rK  rM  rV  rZ  r_  rb  ri  rv  ry  r  r  r  r  r  r  r  r  r  r  r  r  r  r
  r  r   r/   r-   <module>r     so   	  #   > > M,:
~  	! O4*$  ! & &  &  &$  %&, k-&. d/&0 [1&2 h3&4 \5&6 ]7&8 ]9&: ^;&< o=&> p?&@ hA&B hC&D # %E& R ^@jk +?  MT  UE+;-H	ykB*=K^J_`(9  GV  WV0ABg7)<Eg7)<xXJ?4T4L9(9Fst	ykBxXJ?xXx4HI	y'6JKEEEug6_  BA  B'7Dmn
7^_!,A  OQ  R3	8   ;E$)OE%L!8=O$U7^45# ;5:(12;; ?@bc*;- ./0ghfgwi*wiz	D$<EFz(#$G$%**
UGUV@A89^_3 :  2#%	 ,O00@
+\.90x!&?XaC
P.6OzOd-9*x=*@[, ,  $xB&D0 NRJN $G$`(r/   