import sqlite3
from pathlib import Path

DB = Path("data/telephony.sqlite3")

con = sqlite3.connect(DB)
con.row_factory = sqlite3.Row

# 1. Bereits vorhandene Transkripte anhand rec_id mit cdr_calls verbinden.
con.execute("""
    UPDATE transcripts
    SET cdr_row_id = (
        SELECT c.id
        FROM cdr_calls c
        WHERE c.src_rec_id = transcripts.rec_id
           OR c.dst_rec_id = transcripts.rec_id
        ORDER BY c.id
        LIMIT 1
    )
    WHERE cdr_row_id IS NULL
      AND rec_id IS NOT NULL
""")

# 2. Analysen ebenfalls mit dem CDR verbinden.
con.execute("""
    UPDATE analyses
    SET cdr_row_id = (
        SELECT t.cdr_row_id
        FROM transcripts t
        WHERE t.id = analyses.transcript_id
    )
    WHERE cdr_row_id IS NULL
""")

con.commit()

print("=== STATUS ===")

r = con.execute("""
    SELECT
        COUNT(DISTINCT c.id) AS recordings,
        COUNT(DISTINCT t.rec_id) AS transcripts,
        COUNT(DISTINCT a.cdr_row_id) AS analyses
    FROM cdr_calls c
    LEFT JOIN transcripts t ON t.cdr_row_id = c.id
    LEFT JOIN analyses a ON a.cdr_row_id = c.id
    WHERE c.src_rec_id IS NOT NULL
       OR c.dst_rec_id IS NOT NULL
""").fetchone()

print(dict(r))

print()
print("=== BEREITS VERARBEITET ===")

for r in con.execute("""
    SELECT
        c.id AS cdr_row_id,
        COALESCE(c.src_rec_id, c.dst_rec_id) AS rec_id,
        c.start_time,
        t.id AS transcript_id,
        a.id AS analysis_id
    FROM cdr_calls c
    LEFT JOIN transcripts t ON t.cdr_row_id = c.id
    LEFT JOIN analyses a ON a.cdr_row_id = c.id
    WHERE c.src_rec_id IS NOT NULL
       OR c.dst_rec_id IS NOT NULL
    ORDER BY c.start_time
"""):
    if r["transcript_id"] or r["analysis_id"]:
        print(dict(r))

con.close()