
    ^ji                     `    S r SSKrSSKrSSKJrJr  SSKJrJrJrJ	r	  SSK
Jr   " S S5      rg)u   
Cold Outreach Database
Verwaltet Kontakte und Email-Sequenzen für ausgehende Kaltakquise.
Ergänzt die bestehende EmailDatabase um Outbound-Tabellen.
    N)datetime	timedelta)ListOptionalDictAny)contextmanagerc                   *   \ rS rSrSrSSSS.rSFS\4S jjr\S	 5       r	S
 r
S rSGS\\\4   S\S\4S jjrS\S\\\4   4S jrSHS\S\S\\   4S jjrS\S\4S jrSHS\S\S\S\4S jjrS\4S jrS\S\\   4S jrS\\   4S jr  SIS\S \S!\S"\S\4
S# jjrS\S\\   4S$ jr   SJS%\S&\S'\S(\S)\S*\4S+ jjrSKS%\S,\S-\4S. jjrS%\4S/ jr SHS\S\\   4S0 jjr!S\\   4S1 jr"S2\S\\\4   4S3 jr#S\S\\   4S4 jr$S\S\4S5 jr%SLS\S\S\\   4S6 jjr&SGS\S7\\\4   S8\4S9 jjr'S\S\\   4S: jr(SHS\S\4S; jjr)S\4S< jr*S\\   4S= jr+   SJS%\S&\S'\S(\S)\S*\4S> jjrS%\4S? jr,S%\S@\4SA jr-SB\4SC jr.S\\\4   4SD jr/SEr0g)MColdOutreachDB   u1   Datenbank für Kaltakquise-Kontakte und Sequenzenr         )         db_pathc                 P    Xl         U R                  5         U R                  5         g N)r   _init_schema_migrate_schema)selfr   s     /app/cold_outreach_db.py__init__ColdOutreachDB.__init__   s        c              #      #    [         R                  " U R                  5      n[         R                  Ul         Uv   UR                  5         g ! UR                  5         f = f7fr   )sqlite3connectr   Rowrow_factorycloser   conns     r   get_connectionColdOutreachDB.get_connection   s?     t||,";;	JJJLDJJLs   6A#A A#A  A#c                 f   U R                  5        nUR                  S5        UR                  S5        UR                  S5        UR                  S5        UR                  S5        UR                  S5        UR                  S5        UR                  5         S S S 5        g ! , (       d  f       g = f)NaC  
                CREATE TABLE IF NOT EXISTS cold_contacts (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    first_name TEXT NOT NULL,
                    last_name TEXT,
                    email TEXT NOT NULL UNIQUE,
                    company TEXT,
                    role TEXT,
                    industry TEXT,
                    pain_point TEXT,
                    our_offer TEXT,
                    social_proof TEXT,
                    icp TEXT DEFAULT 'AG',          -- AG / LP / beide
                    status TEXT DEFAULT 'active',    -- active / paused / unsubscribed / replied / converted
                    notes TEXT,
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
                )
            a  
                CREATE TABLE IF NOT EXISTS cold_sequences (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    contact_id INTEGER NOT NULL REFERENCES cold_contacts(id),
                    sequence_step INTEGER NOT NULL,  -- 1, 2, 3
                    scheduled_for DATETIME NOT NULL,
                    sent_at DATETIME,
                    status TEXT DEFAULT 'pending',   -- pending / sent / failed / skipped / replied
                    subject TEXT,
                    body TEXT,
                    error_message TEXT,
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
                )
            aL  
                CREATE TABLE IF NOT EXISTS contact_variants (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    contact_id INTEGER NOT NULL REFERENCES cold_contacts(id),
                    offer_title TEXT,
                    industry_segment TEXT,
                    buyer_relevance TEXT,
                    outreach_angle TEXT,
                    priority_tier TEXT,
                    contact_page TEXT,
                    is_primary BOOLEAN DEFAULT 0,
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
                )
            af  
                CREATE TABLE IF NOT EXISTS outreach_campaigns (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    name TEXT NOT NULL,
                    icp TEXT NOT NULL,
                    source_file TEXT,
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                    contacts_total INTEGER DEFAULT 0,
                    contacts_enriched INTEGER DEFAULT 0,
                    contacts_sent INTEGER DEFAULT 0,
                    contacts_replied INTEGER DEFAULT 0,
                    contacts_converted INTEGER DEFAULT 0
                )
            av  
                CREATE TABLE IF NOT EXISTS enrichment_cache (
                    domain TEXT PRIMARY KEY,
                    scraped_text TEXT,
                    meta_title TEXT,
                    meta_description TEXT,
                    scraped_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                    status TEXT DEFAULT 'success'
                )
            zZCREATE INDEX IF NOT EXISTS idx_cold_sequences_due ON cold_sequences(scheduled_for, status)zLCREATE INDEX IF NOT EXISTS idx_cold_contacts_status ON cold_contacts(status)r$   executecommitr"   s     r   r   ColdOutreachDB._init_schema!   s      "dLL  * LL    LL    LL   LL 	 	 LLuvLLghKKMe #""s   BB""
B0c                 j   0 SS_SS_SS_SS_SS_SS	_S
S_SS_SS_SS_SS_SS_SS_SS_SS_SS_nSSSSSSS.nU R                  5        nUR                  S5       Vs1 s H  oDS   iM	     nnUR                  5        H#  u  pgXe;  d  M  UR                  SU SU 35        M%     UR                  S5       Vs1 s H  oDS   iM	     nnUR                  5        H#  u  pgXh;  d  M  UR                  SU SU 35        M%     UR                  5         S S S 5        g s  snf s  snf ! , (       d  f       g = f)!z6Fuegt neue Spalten hinzu falls sie fehlen. Idempotent.websiteTEXTphonecompany_descriptionenrichment_sourceenrichment_dateDATETIMEicp_confidencezREAL DEFAULT 0.0icp_recommended_productlanguagezTEXT DEFAULT 'de'campaign_idINTEGER	source_idcompany_sizeheadquartersfounding_yeartechnology_stackkey_productslinkedin_urlREALzTEXT DEFAULT 'pending'zTEXT DEFAULT 'karlo')personalization_scorepersonalization_elementsreview_statusreviewed_atreviewed_byknowledge_snippetz PRAGMA table_info(cold_contacts)r   z%ALTER TABLE cold_contacts ADD COLUMN  z!PRAGMA table_info(cold_sequences)z&ALTER TABLE cold_sequences ADD COLUMN N)r$   r(   itemsr)   )	r   contact_migrationsseq_migrationsr#   rowexisting_contactscolcol_typeexisting_seqss	            r   r   ColdOutreachDB._migrate_schemav   s   


 "
  	

 

 (:
 &
 (;
 	
 
 
 
 	
 
  !
" #
( )/(.(@(2(>(.
   "d37<<@b3c d3cCQ3c d!3!9!9!;/LL#HQxj!YZ "< 04||<_/`a/`V/`Ma!/!5!5!7+LL#I#aPXz!Z[ "8 KKM #" d
 b #"s6   D$ D.D$.D$:DD$&+D$
D$$
D2datacreate_sequencereturnc                 H   U R                  5        nUR                  SUR                  SS5      UR                  SS5      UR                  SS5      UR                  SS5      UR                  SS5      UR                  SS5      UR                  S	S5      UR                  S
S5      UR                  SS5      UR                  SS5      UR                  SS5      UR                  SS5      UR                  SS5      UR                  SS5      UR                  SS5      UR                  S5      UR                  SS5      UR                  SS5      45      nUR                  5         UR                  nSSS5        U(       a  U R                  W5        W$ ! , (       d  f       N(= f)u}   
Einzelnen Kontakt hinzufügen. Gibt ID zurück.
create_sequence=False: Sequenz wird erst nach Enrichment/Freigabe angelegt.
a  
                INSERT INTO cold_contacts
                    (first_name, last_name, email, company, role, industry,
                     pain_point, our_offer, social_proof, icp, notes,
                     website, phone, company_description, language,
                     campaign_id, status, source_id)
                VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
            
first_name 	last_nameemailcompanyroleindustry
pain_point	our_offersocial_prooficpAGnotesr,   r.   r/   r5   der6   statusrawr8   N)r$   r(   getr)   	lastrowid_create_sequence)r   rP   rQ   r#   cursor
contact_ids         r   add_contactColdOutreachDB.add_contact   se   
   "d\\ # r*b)"%B'$R(r*b),%"%B'"%.3T*'5)b)%F6 KKM))J; #> !!*-C #"s   E F
F!rh   c                    1 SknUR                  5        VVs0 s H  u  pEXC;   d  M  XE_M     nnnU(       d  g[        R                  " 5       R                  5       US'   SR	                  S U 5       5      nU R                  5        nUR                  SU S3[        UR                  5       5      U/-   5        UR                  5         SSS5        gs  snnf ! , (       d  f       g= f)zKontaktfelder aktualisieren.>   r^   r`   r.   rb   r,   rZ   r5   r\   r[   r9   r:   r=   r>   r;   r3   r1   r<   r0   r/   r4   N
updated_at, c              3   *   #    U  H	  o S 3v   M     g7fz = ?N .0ks     r   	<genexpr>0ColdOutreachDB.update_contact.<locals>.<genexpr>   s     5W3dW   zUPDATE cold_contacts SET  WHERE id = ?)
rG   r   now	isoformatjoinr$   r(   listvaluesr)   )	r   rh   rP   allowedrs   vupdatescolsr#   s	            r   update_contactColdOutreachDB.update_contact   s    
 %)JJLALDAAL414LA ( 8 8 :yy5W55  "dLL+D6?W^^%&*5 KKM #" B
 #"s   CCAC
C%Nrb   r6   c                    SnU/nU(       a  US-  nUR                  U5        U R                  5        nUR                  X45      R                  5        Vs/ s H  n[	        U5      PM     snsSSS5        $ s  snf ! , (       d  f       g= f)z5Kontakte nach Status filtern, optional nach Kampagne.,SELECT * FROM cold_contacts WHERE status = ?z AND campaign_id = ?Nappendr$   r(   fetchalldict)r   rb   r6   queryparamsr#   rs          r   get_contacts_by_status%ColdOutreachDB.get_contacts_by_status   st    >++EMM+&  "d%)\\%%@%I%I%KL%KDG%KL #"L #"   "A;A6*A;6A;;
B	rW   c                     U R                  5        nUR                  SUR                  5       R                  5       45      R	                  5       nUSLsSSS5        $ ! , (       d  f       g= f)u8   Prüft ob Email-Adresse bereits in der DB vorhanden ist.z+SELECT 1 FROM cold_contacts WHERE email = ?N)r$   r(   striplowerfetchone)r   rW   r#   rJ   s       r   email_existsColdOutreachDB.email_exists   sT      "d,,=@S@S@U?Whj  d?	 #""s   AA
A*namer^   source_filec                     U R                  5        nUR                  SXU45      nUR                  5         UR                  sSSS5        $ ! , (       d  f       g= f)u'   Neue Kampagne anlegen. Gibt ID zurück.zt
                INSERT INTO outreach_campaigns (name, icp, source_file)
                VALUES (?,?,?)
            N)r$   r(   r)   re   )r   r   r^   r   r#   rg   s         r   create_campaignColdOutreachDB.create_campaign   sM      "d\\ # [)+F KKM## #""s   0A
Ac           
         U R                  5        nUR                  SU45      R                  5       nUR                  SUS   US   US   US   US   U45        UR                  5         SSS5        g! , (       d  f       g= f)	z6Statistiken einer Kampagne aus DB-Daten neu berechnen.a  
                SELECT
                    COUNT(*) as total,
                    SUM(CASE WHEN status IN ('enriched','active','replied','converted') THEN 1 ELSE 0 END) as enriched,
                    SUM(CASE WHEN status IN ('active','replied','converted') THEN 1 ELSE 0 END) as sent,
                    SUM(CASE WHEN status = 'replied' THEN 1 ELSE 0 END) as replied,
                    SUM(CASE WHEN status = 'converted' THEN 1 ELSE 0 END) as converted
                FROM cold_contacts WHERE campaign_id = ?
            z
                UPDATE outreach_campaigns
                SET contacts_total=?, contacts_enriched=?, contacts_sent=?,
                    contacts_replied=?, contacts_converted=?
                WHERE id=?
            r   r   r   r   r   N)r$   r(   r   r)   )r   r6   r#   statss       r   update_campaign_stats$ColdOutreachDB.update_campaign_stats   s      "dLL " ! "*  LL 
 (E!HeAha%(KPR KKM! #""   AA33
Bc                     U R                  5        nUR                  SU45      R                  5       nU(       a  [        U5      OS sS S S 5        $ ! , (       d  f       g = f)Nz-SELECT * FROM outreach_campaigns WHERE id = ?r$   r(   r   r   )r   r6   r#   rJ   s       r   get_campaignColdOutreachDB.get_campaign  sH      "d,,?+hj  !$49	 #""   5A
Ac                     U R                  5        nUR                  S5      R                  5        Vs/ s H  n[        U5      PM     snsS S S 5        $ s  snf ! , (       d  f       g = f)Nz9SELECT * FROM outreach_campaigns ORDER BY created_at DESCr$   r(   r   r   )r   r#   r   s      r   list_campaignsColdOutreachDB.list_campaigns  s^      "d%)\\K&hj&  &DG &  #" #"s   "AAAA
A'domainscraped_text
meta_titlemeta_descriptionc           
          U R                  5        nUR                  SXX4[        R                  " 5       R	                  5       U45        UR                  5         SSS5        g! , (       d  f       g= f)z!Website-Scraping-Ergebnis cachen.z
                INSERT OR REPLACE INTO enrichment_cache
                    (domain, scraped_text, meta_title, meta_description, scraped_at, status)
                VALUES (?,?,?,?,?,?)
            Nr$   r(   r   rx   ry   r)   )r   r   r   r   r   rb   r#   s          r   cache_enrichmentColdOutreachDB.cache_enrichment  sZ       "dLL  
,,.**,f6	7 KKM #""s   AA""
A0c                     U R                  5        nUR                  SU45      R                  5       nU(       a  [        U5      OSsSSS5        $ ! , (       d  f       g= f)u5   Gecachtes Enrichment-Ergebnis für eine Domain holen.z/SELECT * FROM enrichment_cache WHERE domain = ?Nr   )r   r   r#   rJ   s       r   get_cached_enrichment$ColdOutreachDB.get_cached_enrichment,  sH      "d,,AF9hj  !$49	 #""r   seq_idsubjectbodyr@   rA   rE   c           
          U R                  5        nUR                  SX#UU(       a  [        U5      OSUU45        UR                  5         SSS5        g! , (       d  f       g= f)z.Generierten Emailinhalt speichern (erweitert).a5  
                UPDATE cold_sequences
                SET subject = ?, body = ?,
                    personalization_score = ?,
                    personalization_elements = ?,
                    knowledge_snippet = ?,
                    review_status = 'pending'
                WHERE id = ?
            N)r$   r(   strr)   )r   r   r   r   r@   rA   rE   r#   s           r   save_generated_email#ColdOutreachDB.save_generated_email6  sZ    
   "dLL  %1I,-t! KKM! #""s   :A
A"edited_bodyedited_subjectc                    U R                  5        nS[        R                  " 5       R                  5       S.nU(       a  X%S'   U(       a  X5S'   SR	                  S U 5       5      nUR                  SU S3[        UR                  5       5      U/-   5        UR                  5         S	S	S	5        g	! , (       d  f       g	= f)
u   Email für Versand freigeben.approved)rB   rC   r   r   rm   c              3   *   #    U  H	  o S 3v   M     g7fro   rp   rq   s     r   rt   /ColdOutreachDB.approve_email.<locals>.<genexpr>X  s     9As$Zrv   zUPDATE cold_sequences SET rw   N)	r$   r   rx   ry   rz   r(   r{   r|   r)   )r   r   r   r   r#   r   r   s          r   approve_emailColdOutreachDB.approve_emailM  s      "d!+'||~779G "-%3	"99999DLL,TF-@W^^%&&1 KKM #""s   BB00
B>c                     U R                  5        nUR                  S[        R                  " 5       R	                  5       U45        UR                  5         SSS5        g! , (       d  f       g= f)z%Email ablehnen (wird nicht gesendet).zRUPDATE cold_sequences SET review_status = 'rejected', reviewed_at = ? WHERE id = ?Nr   r   r   r#   s      r   reject_emailColdOutreachDB.reject_email_  sM      "dLLd))+V4 KKM #""   AA  
A.c                     Sn/ nU(       a  US-  nUR                  U5        US-  nU R                  5        nUR                  X#5      R                  5        Vs/ s H  n[	        U5      PM     snsSSS5        $ s  snf ! , (       d  f       g= f)z3Alle generierten Emails die noch auf Review warten.a  
            SELECT s.*, c.first_name, c.last_name, c.email, c.company,
                   c.role, c.industry, c.pain_point, c.icp,
                   c.icp_recommended_product, c.language,
                   c.status AS contact_status
            FROM cold_sequences s
            JOIN cold_contacts c ON s.contact_id = c.id
            WHERE s.review_status = 'pending'
              AND s.body IS NOT NULL
              AND c.status NOT IN ('unsubscribed', 'bounced')
        z AND c.campaign_id = ?z2 ORDER BY s.sequence_step ASC, s.scheduled_for ASCNr   )r   r6   r   r   r#   r   s         r   get_pending_review!ColdOutreachDB.get_pending_reviewh  s    
 --EMM+&EE  "d%)\\%%@%I%I%KL%KDG%KL #"L #"s   "A?A:.A?:A??
Bc                     U R                  5        nUR                  S5      R                  5       nU Vs/ s H  n[        U5      PM     snsSSS5        $ s  snf ! , (       d  f       g= f)z9Alle freigegebenen Emails die noch nicht gesendet wurden.a  
                SELECT s.*, c.first_name, c.last_name, c.email, c.company,
                       c.role, c.industry, c.icp, c.language,
                       c.status AS contact_status
                FROM cold_sequences s
                JOIN cold_contacts c ON s.contact_id = c.id
                WHERE s.review_status = 'approved'
                  AND s.status = 'pending'
                  AND c.status NOT IN ('unsubscribed', 'bounced', 'replied')
                ORDER BY s.scheduled_for ASC
            Nr   r   r#   rowsr   s       r   get_approved_emails"ColdOutreachDB.get_approved_emails}  s]      "d<< 
! 
   &**TDGT* #" + #"s   $AA
AA
A)csv_pathc                    Su  p#[        USSS9 n[        R                  " U5      nU H  n U R                  U5        US-  nM     SSS5        X#S.$ ! [        R
                   a	    US-  n MF  f = f! , (       d  f       N2= f)z
Kontakte aus CSV importieren.

Erwartete Spalten (Mindestzahl):
    first_name, email
Optional:
    last_name, company, role, industry, pain_point,
    our_offer, social_proof, icp, notes
)r   r   rU   zutf-8)newlineencodingr   N)importedskipped)opencsv
DictReaderri   r   IntegrityError)r   r   r   r   freaderrJ   s          r   import_from_csvColdOutreachDB.import_from_csv  s     !(B9Q^^A&F!$$S)MH  : %99 -- !qLG! :9s.   A3AA3A0,A3/A00A33
Bc                     U R                  5        nUR                  SU45      R                  5       nU(       a  [        U5      OS sS S S 5        $ ! , (       d  f       g = f)Nz(SELECT * FROM cold_contacts WHERE id = ?r   )r   rh   r#   rJ   s       r   get_contactColdOutreachDB.get_contact  sC      "d,,IJ=YbbdC #49 #""r   c                     U R                  5        nUR                  SU[        R                  " 5       R	                  5       U45        UR                  5         SSS5        g! , (       d  f       g= f)zCStatus setzen: active / paused / unsubscribed / replied / convertedz@UPDATE cold_contacts SET status = ?, updated_at = ? WHERE id = ?Nr   )r   rh   rb   r#   s       r   set_contact_status!ColdOutreachDB.set_contact_status  sO      "dLLR113Z@ KKM #""s   AA!!
A/c                    SnU/nU(       a  US-  nUR                  U5        U R                  5        nUR                  X45      R                  5        Vs/ s H  n[	        U5      PM     snsS S S 5        $ s  snf ! , (       d  f       g = f)Nr   z AND icp = ?r   )r   rb   r^   r   r   r#   r   s          r   get_contactsColdOutreachDB.get_contacts  ss    >^#EMM#  "d%)\\%%@%I%I%KL%KDG%KL #"L #"r   variant_data
is_primaryc                 r   U R                  5        nUR                  SUUR                  S5      UR                  S5      UR                  S5      UR                  S5      UR                  S5      UR                  S5      U(       a  SOS	45        UR                  5         S
S
S
5        g
! , (       d  f       g
= f)u3   Fügt eine Produkt-Variante zu einem Kontakt hinzu.a	  
                INSERT INTO contact_variants
                    (contact_id, offer_title, industry_segment, buyer_relevance,
                     outreach_angle, priority_tier, contact_page, is_primary)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            offer_titleindustry_segmentbuyer_relevanceoutreach_anglepriority_tiercontact_pager   r   N)r$   r(   rd   r)   )r   rh   r   r   r#   s        r   add_variantColdOutreachDB.add_variant  s      "dLL    /  !34  !23  !12  1  0Q	 KKM! #""s   BB((
B6c                     U R                  5        nUR                  SU45      R                  5       nU Vs/ s H  n[        U5      PM     snsSSS5        $ s  snf ! , (       d  f       g= f)u+   Gibt alle Varianten eines Kontakts zurück.zTSELECT * FROM contact_variants WHERE contact_id = ? ORDER BY is_primary DESC, id ASCNr   )r   rh   r#   r   r   s        r   get_variantsColdOutreachDB.get_variants  s`      "d<<f hj  &**TDGT* #"
 + #"s   &AAAA
A+c                 B   U R                  SUS9nSnU Hu  nUS   nU R                  5        nUR                  SU45      R                  5       S   nSSS5        WS:X  a  U R	                  U5        U R                  USS05        US	-  nMw     U$ ! , (       d  f       ND= f)
z
Aktiviert alle 'enriched' Kontakte: erstellt Sequenzen und setzt Status auf 'active'.
Wird vor der Email-Generierung aufgerufen.

Returns:
    Anzahl aktivierter Kontakte
enriched)r6   r   idz8SELECT COUNT(*) FROM cold_sequences WHERE contact_id = ?Nrb   activer   )r   r$   r(   r   rf   r   )r   r6   contacts	activatedcrh   r#   existings           r   activate_enriched_contacts)ColdOutreachDB.activate_enriched_contacts  s     ..z{.S	A4J$$&$<<NM (*Q  '
 1}%%j1
Xx,@ANI   '&s   %B
B	c           	      D   [         R                  " 5       nU R                  5        nU R                  R	                  5        H3  u  pEU[        US9-   nUR                  SXUR                  5       45        M5     UR                  5         SSS5        g! , (       d  f       g= f)u9   Legt 3 Sequenz-Einträge (pending) für einen Kontakt an.)daysz
                    INSERT INTO cold_sequences (contact_id, sequence_step, scheduled_for)
                    VALUES (?,?,?)
                N)	r   rx   r$   SEQUENCE_DELAYSrG   r   r(   ry   r)   )r   rh   rx   r#   step
delay_days	scheduleds          r   rf   ColdOutreachDB._create_sequence  s    lln  "d$($8$8$>$>$@ )"<<	  !	(;(;(=>@ %A KKM #""s   A"B
Bc                    U R                  5        nUR                  S[        R                  " 5       R	                  5       45      R                  5       nU Vs/ s H  n[        U5      PM     snsSSS5        $ s  snf ! , (       d  f       g= f)u?   Alle fälligen Emails (scheduled_for <= jetzt, status=pending).ak  
                SELECT s.*, c.first_name, c.last_name, c.email, c.company,
                       c.role, c.industry, c.pain_point, c.our_offer,
                       c.social_proof, c.icp, c.notes, c.company_description,
                       c.icp_recommended_product, c.language,
                       c.status AS contact_status
                FROM cold_sequences s
                JOIN cold_contacts c ON s.contact_id = c.id
                WHERE s.status = 'pending'
                  AND s.scheduled_for <= ?
                  AND c.status = 'active'
                ORDER BY s.scheduled_for ASC
            N)r$   r(   r   rx   ry   r   r   r   s       r   get_due_emailsColdOutreachDB.get_due_emails  st      "d<< ! ,,.**,.0 19
  &**TDGT* #" + #"s   AA>A9-A>9A>>
Bc           
          SSK nU(       a  UR                  U5      OSnU R                  5        n	U	R                  SX#XHXa45        U	R	                  5         SSS5        g! , (       d  f       g= f)z"Generierten Emailinhalt speichern.r   Na  
                UPDATE cold_sequences
                SET subject = ?, body = ?,
                    personalization_score = ?,
                    personalization_elements = ?,
                    knowledge_snippet = ?
                WHERE id = ?
            )jsondumpsr$   r(   r)   )
r   r   r   r   r@   rA   rE   _jsonelements_jsonr#   s
             r   r   r     s`    
 	AY$<=_c  "dLL  !6GXac KKM #""s   &A
A,c                     U R                  5        nUR                  S[        R                  " 5       R	                  5       U45        UR                  5         S S S 5        g ! , (       d  f       g = f)NzCUPDATE cold_sequences SET status = 'sent', sent_at = ? WHERE id = ?r   r   s      r   	mark_sentColdOutreachDB.mark_sent   sM      "dLLU))+V4 KKM #""r   errorc                     U R                  5        nUR                  SX!45        UR                  5         S S S 5        g ! , (       d  f       g = f)NzKUPDATE cold_sequences SET status = 'failed', error_message = ? WHERE id = ?r'   )r   r   r  r#   s       r   mark_failedColdOutreachDB.mark_failed(  s;      "dLL] KKM #""s	   $>
Acontact_emailc                    U R                  5        nUR                  S[        R                  " 5       R	                  5       U45        UR                  SU45        UR                  5         SSS5        g! , (       d  f       g= f)uA   Wenn Kontakt antwortet: alle pending Sequenz-Steps überspringen.zy
                UPDATE cold_contacts SET status = 'replied', updated_at = ?
                WHERE email = ?
            z
                UPDATE cold_sequences SET status = 'skipped'
                WHERE contact_id = (SELECT id FROM cold_contacts WHERE email = ?)
                  AND status = 'pending'
            Nr   )r   r  r#   s      r   mark_repliedColdOutreachDB.mark_replied0  sl      "dLL  ,,.**,m<> LL   !	#
 KKM #""r   c           	      4   U R                  5        nUR                  S5      R                  5       S   nUR                  S5      R                  5       S   nUR                  S5      R                  5       S   nUR                  S5      R                  5       S   nUR                  S5      R                  5       S   nUR                  S5      R                  5       S   nUUUUU(       a  [        XB-  S-  S	5      OSUUS
.sS S S 5        $ ! , (       d  f       g = f)Nz"SELECT COUNT(*) FROM cold_contactsr   z8SELECT COUNT(*) FROM cold_contacts WHERE status='active'z9SELECT COUNT(*) FROM cold_contacts WHERE status='replied'z;SELECT COUNT(*) FROM cold_contacts WHERE status='converted'z7SELECT COUNT(*) FROM cold_sequences WHERE status='sent'z:SELECT COUNT(*) FROM cold_sequences WHERE status='pending'd   r   )contacts_totalcontacts_activecontacts_repliedcontacts_converted
reply_rateemails_sentemails_pending)r$   r(   r   round)r   r#   totalr   replied	convertedsentpendings           r   	get_statsColdOutreachDB.get_stats@  s      "d%IJSSUVWXE%_`iiklmnF%`ajjlmnoG%bcllnopqI%^_hhjklmD%abkkmnopG"'#)$+&/AFeGOc$91=A#") #""s   C.D		
D)r   )zemail_agent.db)Fr   )rU   rU   success)NNN)NN)r   N)1__name__
__module____qualname____firstlineno____doc__r   r   r   r	   r$   r   r   r   r   boolintri   r   r   r   r   r   r   r   r   r   r   r   floatr{   r   r   r   r   r   r   r   r   r   r   r   r   rf   r  r  r  r  r'  __static_attributes__rp   r   r   r   r      s3   ;a(O 
  Sj'V&S#X & &RU &P DcN *MS Ms MdSWj M## #$ #$C $c $ $s $ (. . .T
  HJ'0s # %(AD!$.C .HTN . =A>B6:3  C 497; 14.C c RU $3 Mc MT$Z M*+T$Z + : :S#X :*.c .htn .
S # M3 M MtDz Mc c3h UY (+s +tDz +c S 0
3 
+T
 +& =A>B6:3  C 497; 14$ # c #  4S> r   r   )r.  r   r   r   r   typingr   r   r   r   
contextlibr	   r   rp   r   r   <module>r5     s*     
 ( , , %B	 B	r   