#!/usr/bin/env python3
"""
Backfill raw calls up to a given date from source call tables into {bid}_raw_calls.

Customer mapping rules:
- Prefer source `customer_callinfo` when present.
- Outbound: use `callto`.
- Inbound: use `callfrom`.
- Fallback: `clicktocalldid`, then `callto`.
- Never fallback to `emp_phone` for customer.
"""
import argparse
import os

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


def nz(value):
    return str(value).strip() if value is not None else ""


def table_exists(cursor, table_name):
    cursor.execute("SHOW TABLES LIKE %s", (table_name,))
    return cursor.fetchone() is not None


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


def pick_customer(row):
    explicit = nz(row.get("customer_callinfo"))
    if explicit:
        return explicit

    direction = nz(row.get("direction")).lower()
    callto = nz(row.get("callto"))
    callfrom = nz(row.get("callfrom"))
    click_to_call = nz(row.get("clicktocalldid"))

    if direction == "outbound" and callto:
        return callto
    if direction == "inbound" and callfrom:
        return callfrom
    if click_to_call:
        return click_to_call
    if callto:
        return callto
    return ""


def pick_agent(row):
    emp_phone = nz(row.get("emp_phone"))
    if emp_phone:
        return emp_phone
    return nz(row.get("callfrom"))


def main():
    parser = argparse.ArgumentParser(description="Sync calls up to a date into raw_calls.")
    parser.add_argument("--bid", required=True, help="Business ID, for example 6004")
    parser.add_argument("--date-to", required=True, help="Inclusive upper date, YYYY-MM-DD")
    args = parser.parse_args()

    load_dotenv()

    source_config = {
        "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,
    }
    dest_config = {
        "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,
    }

    source_conn = pymysql.connect(**source_config)
    dest_conn = pymysql.connect(**dest_config)
    try:
        source_cursor = source_conn.cursor()
        dest_cursor = dest_conn.cursor()

        source_table_candidates = [
            f"{args.bid}_callhistory",
            f"{args.bid}_call_history",
            f"{args.bid}_callarchive",
            f"{args.bid}_call_archive",
        ]
        source_tables = [t for t in source_table_candidates if table_exists(source_cursor, t)]
        if not source_tables:
            print("No source call tables found.")
            return

        print(f"Source tables: {source_tables}")

        insert_query = f"""
            INSERT INTO `{args.bid}_raw_calls`
            (
                bid, callid, fileurl, status, agentname, groupname, call_starttime, call_endtime,
                call_status, agent_callinfo, customer_callinfo, direction,
                transcription_requested, transcription_status, selected_for_processing
            )
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            ON DUPLICATE KEY UPDATE
                fileurl = VALUES(fileurl),
                agentname = VALUES(agentname),
                groupname = VALUES(groupname),
                call_starttime = VALUES(call_starttime),
                call_endtime = VALUES(call_endtime),
                call_status = VALUES(call_status),
                agent_callinfo = CASE
                    WHEN VALUES(agent_callinfo) != '' THEN VALUES(agent_callinfo)
                    ELSE agent_callinfo
                END,
                customer_callinfo = CASE
                    WHEN VALUES(customer_callinfo) != '' THEN VALUES(customer_callinfo)
                    ELSE customer_callinfo
                END,
                direction = VALUES(direction)
        """

        total_source = 0
        inserted = 0
        updated = 0

        for table_name in source_tables:
            cols = table_columns(source_cursor, table_name)
            select_cols = [
                "callid",
                "bid",
                "agentname",
                "groupname",
                "starttime",
                "endtime",
                "dialstatus",
                "direction",
                "filename",
            ]
            optional = ["emp_phone", "callto", "callfrom", "clicktocalldid", "customer_callinfo"]
            fields = [col for col in select_cols + optional if col in cols]

            query = (
                f"SELECT {', '.join(fields)} "
                f"FROM `{table_name}` "
                "WHERE starttime IS NOT NULL AND DATE(starttime) <= %s "
                "ORDER BY starttime DESC"
            )
            source_cursor.execute(query, (args.date_to,))
            rows = source_cursor.fetchall() or []
            total_source += len(rows)
            print(f"{table_name}: {len(rows)} rows")

            for row in rows:
                direction = nz(row.get("direction")).lower() or "inbound"
                dest_cursor.execute(
                    insert_query,
                    (
                        nz(row.get("bid")) or args.bid,
                        nz(row.get("callid")),
                        nz(row.get("filename")),
                        0,
                        nz(row.get("agentname")),
                        nz(row.get("groupname")),
                        row.get("starttime"),
                        row.get("endtime"),
                        nz(row.get("dialstatus")),
                        pick_agent(row),
                        pick_customer(row),
                        direction,
                        0,
                        "not_requested",
                        0,
                    ),
                )
                if dest_cursor.rowcount == 1:
                    inserted += 1
                elif dest_cursor.rowcount == 2:
                    updated += 1

            dest_conn.commit()

        print(
            f"Done. source_rows={total_source} inserted={inserted} updated={updated} "
            f"date_to={args.date_to} bid={args.bid}"
        )
    finally:
        source_conn.close()
        dest_conn.close()


if __name__ == "__main__":
    main()