import sqlite3
from pathlib import Path

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

con = sqlite3.connect(DB)
con.execute("PRAGMA foreign_keys = ON")

# Neue direkte Beziehung zum echten 3CX-CDR-Datensatz.
cols = {
    row[1]
    for row in con.execute("PRAGMA table_info(transcripts)")
}

if "cdr_row_id" not in cols:
    con.execute("""
        ALTER TABLE transcripts
        ADD COLUMN cdr_row_id INTEGER
    """)

# call_id darf künftig NULL sein.
# SQLite erlaubt keine direkte Änderung von NOT NULL.
# Bestehende 0 wird deshalb als "nicht verknüpft" behandelt.
# Die echte Beziehung läuft über cdr_row_id.

analysis_cols = {
    row[1]
    for row in con.execute("PRAGMA table_info(analyses)")
}

if "cdr_row_id" not in analysis_cols:
    con.execute("""
        ALTER TABLE analyses
        ADD COLUMN cdr_row_id INTEGER
    """)

con.execute("""
    CREATE INDEX IF NOT EXISTS idx_transcripts_cdr_row_id
    ON transcripts(cdr_row_id)
""")

con.execute("""
    CREATE INDEX IF NOT EXISTS idx_analyses_cdr_row_id
    ON analyses(cdr_row_id)
""")

# Das bereits analysierte Recording 4648 eindeutig zuordnen.
cdr = con.execute("""
    SELECT id
    FROM cdr_calls
    WHERE src_rec_id = 4648
       OR dst_rec_id = 4648
    ORDER BY id
    LIMIT 1
""").fetchone()

if cdr:
    cdr_row_id = cdr[0]

    con.execute("""
        UPDATE transcripts
        SET cdr_row_id = ?
        WHERE rec_id = 4648
    """, (cdr_row_id,))

    con.execute("""
        UPDATE analyses
        SET cdr_row_id = ?
        WHERE transcript_id IN (
            SELECT id
            FROM transcripts
            WHERE rec_id = 4648
        )
    """, (cdr_row_id,))

    print(f"recId 4648 → cdr_calls.id {cdr_row_id}")
else:
    print("WARNUNG: CDR für recId 4648 nicht gefunden.")

con.commit()

print()
print("=== VERKNÜPFUNG ===")

row = con.execute("""
    SELECT
        t.id AS transcript_id,
        t.cdr_row_id,
        t.rec_id,
        a.id AS analysis_id,
        c.cdr_id,
        c.start_time,
        c.source_caller_id,
        c.src_rec_id
    FROM transcripts t
    LEFT JOIN analyses a
        ON a.transcript_id = t.id
    LEFT JOIN cdr_calls c
        ON c.id = t.cdr_row_id
    WHERE t.rec_id = 4648
    ORDER BY t.id DESC
    LIMIT 1
""").fetchone()

if row:
    print(
        "transcript_id =", row[0],
        "| cdr_row_id =", row[1],
        "| rec_id =", row[2],
        "| analysis_id =", row[3],
        "| cdr_id =", row[4],
        "| start =", row[5],
        "| caller =", row[6],
        "| recording =", row[7],
    )

con.close()