"""
monitor_chats.py — review real tester traffic in the support-ai log tables.

The test DB was polluted by local eval/audit runs (thousands of rows, iUserID NULL) before
2026-07-07. Local logging is now disabled (.env DB_HOST commented), so new rows are real testers.
This report filters to a cutoff so the pollution is excluded, and always shows ALL feedback
(a rating only exists when a human clicked thumbs).

Run:   python3 scripts/monitor_chats.py [SINCE]
       SINCE defaults to '2026-07-07 05:00:00' (after the last local test burst).
       e.g. python3 scripts/monitor_chats.py '2026-07-07 00:00:00'
"""
import os
import sys

import pymysql
from dotenv import load_dotenv

load_dotenv()
SINCE = sys.argv[1] if len(sys.argv) > 1 else "2026-07-07 05:00:00"
HOST = os.environ.get("DB_HOST") or os.environ.get("DB_HOST_DISABLED")


def conn():
    return pymysql.connect(host=HOST, port=int(os.environ.get("DB_PORT", 3306)),
                           user=os.environ["DB_USERNAME"], password=os.environ["DB_PASSWORD"],
                           database=os.environ["DB_DATABASE"], cursorclass=pymysql.cursors.DictCursor,
                           connect_timeout=8, read_timeout=8)


def main():
    with conn() as c, c.cursor() as cur:
        def q(sql, args=()):
            cur.execute(sql, args); return cur.fetchall()

        n = q("SELECT COUNT(*) n FROM tbl_SupportChatLog WHERE dCreated >= %s", (SINCE,))[0]["n"]
        print(f"=== REAL TRAFFIC since {SINCE} ===")
        print(f"chats: {n}")

        if n:
            print("\noutcomes:")
            for r in q("""SELECT bEscalate, vEscalateReason, COUNT(*) n FROM tbl_SupportChatLog
                          WHERE dCreated >= %s GROUP BY bEscalate, vEscalateReason ORDER BY n DESC""", (SINCE,)):
                print(f"  esc={r['bEscalate']} {str(r['vEscalateReason']):20} n={r['n']}")

            print("\nKB GAPS (no_confident_answer — questions we couldn't answer):")
            for r in q("""SELECT vProgram, vMessage, decBestSim FROM tbl_SupportChatLog
                          WHERE dCreated >= %s AND vEscalateReason='no_confident_answer'
                          ORDER BY dCreated DESC LIMIT 40""", (SINCE,)):
                print(f"  [{r['vProgram']}] sim={r['decBestSim']} {(r['vMessage'] or '')[:90]}")

            print("\nWEAK ANSWERS (answered but low retrieval sim < 0.62):")
            for r in q("""SELECT vProgram, vMessage, decBestSim FROM tbl_SupportChatLog
                          WHERE dCreated >= %s AND bEscalate=0 AND vEscalateReason IS NULL
                          AND decBestSim < 0.62 ORDER BY decBestSim ASC LIMIT 30""", (SINCE,)):
                print(f"  [{r['vProgram']}] sim={r['decBestSim']} {(r['vMessage'] or '')[:90]}")

        # feedback is ALWAYS real (only a human clicks it) — show all of it, ignore cutoff
        print("\n=== ALL FEEDBACK (thumbs) ===")
        fb = q("""SELECT f.vRating, f.vNote, c.vProgram, c.vMessage, c.bEscalate, c.vEscalateReason,
                         c.vAnswer, c.dCreated
                  FROM tbl_SupportChatFeedback f LEFT JOIN tbl_SupportChatLog c ON f.vMessageID=c.vMessageID
                  ORDER BY c.dCreated DESC""")
        if not fb:
            print("  (none yet)")
        for r in fb:
            mark = "UP" if r["vRating"] == "up" else "DOWN"
            print(f"\n[{mark}] {r['dCreated']} program={r['vProgram']} note={r['vNote']!r}")
            print(f"  Q: {(r['vMessage'] or '')[:110]}")
            print(f"  A: {' '.join((r['vAnswer'] or '').split())[:220]}")


if __name__ == "__main__":
    main()