#!/usr/bin/env python3
"""Backfill agentname / agent_callinfo / customer_callinfo from Mcube source for existing raw_calls rows."""

from __future__ import annotations

import argparse
import os
import sys

import pymysql
from dotenv import load_dotenv
from pymysql.cursors import DictCursor

from mcube_agent_util import resolve_mcube_agent_sql
from mcube_group_util import _table_exists

load_dotenv()


def _source_config():
    return {
        "host": os.getenv("SYNC_SOURCE_DB_HOST", os.getenv("DB_HOST", "127.0.0.1")),
        "port": int(os.getenv("SYNC_SOURCE_DB_PORT", os.getenv("DB_PORT", "3306"))),
        "user": os.getenv("SYNC_SOURCE_DB_USER", os.getenv("DB_USER", "admin")),
        "password": os.getenv("SYNC_SOURCE_DB_PASSWORD", os.getenv("DB_PASSWORD", "")),
        "database": os.getenv("SYNC_SOURCE_DB_NAME", os.getenv("DB_NAME", "voicebot_cluster")),
        "charset": "utf8mb4",
        "cursorclass": DictCursor,
    }


def _dest_config():
    return {
        "host": os.getenv("DB_HOST", "127.0.0.1"),
        "port": int(os.getenv("DB_PORT", "3306")),
        "user": os.getenv("DB_USER", "admin"),
        "password": os.getenv("DB_PASSWORD", ""),
        "database": os.getenv("DB_NAME", "voicebot_cluster"),
        "charset": "utf8mb4",
        "cursorclass": DictCursor,
    }


def _source_cols_map(cursor, table_name: str):
    cursor.execute(f"SHOW COLUMNS FROM `{table_name}`")
    return {str(row["Field"]).lower(): str(row["Field"]) for row in cursor.fetchall()}


def _resolve_source_table(cursor, bid: str):
    for candidate in (f"{bid}_callhistory", f"{bid}_call_history", f"{bid}_callarchive", f"{bid}_call_archive"):
        if _table_exists(cursor, candidate):
            return candidate
    return None


def main():
    parser = argparse.ArgumentParser(description="Backfill Mcube agent names into raw_calls.")
    parser.add_argument("--bid", required=True, help="Business ID, e.g. 94")
    parser.add_argument("--days", type=int, default=30, help="Look back N days (default: 30; ignored when --all)")
    parser.add_argument("--all", action="store_true", help="Backfill every row in raw_calls (not just recent)")
    parser.add_argument("--dry-run", action="store_true", help="Print changes without updating")
    args = parser.parse_args()

    bid = str(args.bid).strip()
    source_conn = pymysql.connect(**_source_config())
    dest_conn = pymysql.connect(**_dest_config())
    try:
        source_cur = source_conn.cursor()
        dest_cur = dest_conn.cursor()

        source_table = _resolve_source_table(source_cur, bid)
        if not source_table:
            print(f"No Mcube source call table found for BID {bid}")
            return 1

        source_cols = _source_cols_map(source_cur, source_table)
        emp_join_sql, emp_join_params, agentname_sql, agent_phone_sql, customer_sql = (
            resolve_mcube_agent_sql(source_cur, bid, source_table, source_cols, call_alias="c")
        )

        if args.all:
            dest_cur.execute(f"SELECT callid FROM `{bid}_raw_calls`")
        else:
            dest_cur.execute(
                f"""
                SELECT callid FROM `{bid}_raw_calls`
                WHERE call_starttime >= DATE_SUB(NOW(), INTERVAL %s DAY)
                """,
                (args.days,),
            )
        callids = [str(row["callid"]) for row in (dest_cur.fetchall() or []) if row.get("callid")]
        if not callids:
            print("No local calls to backfill.")
            return 0

        placeholders = ", ".join(["%s"] * len(callids))
        query = f"""
            SELECT
                c.callid,
                {agentname_sql},
                {agent_phone_sql},
                {customer_sql}
            FROM `{source_table}` c
            {emp_join_sql}
            WHERE c.callid IN ({placeholders})
        """
        source_cur.execute(query, tuple(emp_join_params + callids))
        rows = source_cur.fetchall() or []
        if not rows:
            print("No matching source rows found.")
            return 0

        updated = 0
        for row in rows:
            agentname = str(row.get("agentname") or "").strip()
            agent_phone = str(row.get("emp_phone") or "").strip()
            customer = str(row.get("customer_callinfo") or "").strip()
            if not agentname and not agent_phone and not customer:
                continue
            print(
                f"{row['callid']}: agent={agentname or '-'} "
                f"agent_phone={agent_phone or '-'} customer={customer or '-'}"
            )
            if args.dry_run:
                updated += 1
                continue
            dest_cur.execute(
                f"""
                UPDATE `{bid}_raw_calls`
                SET
                    agentname = CASE WHEN %s != '' THEN %s ELSE agentname END,
                    agent_callinfo = CASE WHEN %s != '' THEN %s ELSE agent_callinfo END,
                    customer_callinfo = CASE WHEN %s != '' THEN %s ELSE customer_callinfo END
                WHERE callid = %s
                """,
                (agentname, agentname, agent_phone, agent_phone, customer, customer, row["callid"]),
            )
            updated += dest_cur.rowcount

        if not args.dry_run:
            dest_conn.commit()
        print(f"{'Would update' if args.dry_run else 'Updated'} {updated} row(s) for BID {bid}.")
        return 0
    finally:
        source_conn.close()
        dest_conn.close()


if __name__ == "__main__":
    sys.exit(main() or 0)