#!/usr/bin/env python3
"""Re-resolve customer_callinfo from Mcube source rows (fixes shared clicktocalldid grouping)."""

from __future__ import annotations

import argparse
import logging
import os
import sys

import pymysql
from pymysql.cursors import DictCursor

BACKEND_DIR = os.path.dirname(os.path.abspath(__file__))
sys.path.insert(0, BACKEND_DIR)
os.chdir(BACKEND_DIR)

from dotenv import load_dotenv

load_dotenv()

from config import Config
from mcube_phone_util import pick_mcube_customer_phone
from orchestrate_pipeline import Orchestrator

logging.basicConfig(level=logging.INFO, format="%(asctime)s [%(levelname)s] %(message)s")
logger = logging.getLogger("backfill_customer_phones_from_source")


def _resolved_phone(raw: dict, source_row: dict | None) -> str:
    if source_row:
        return _source_row_phone({**source_row, "callid": raw.get("callid")}).strip()
    return pick_mcube_customer_phone(
        {
            "customer_callinfo": raw.get("customer_callinfo"),
            "direction": raw.get("direction"),
            "callid": raw.get("callid"),
        }
    ).strip()


def _apply_update(cursor, raw_table: str, callid: str, resolved: str, dry_run: bool) -> str:
    if dry_run:
        return "update" if resolved else "clear"
    cursor.execute(
        f"UPDATE `{raw_table}` SET customer_callinfo = %s WHERE callid = %s",
        (resolved or None, callid),
    )
    return "update" if resolved else "clear"


def _source_row_phone(row: dict) -> str:
    return pick_mcube_customer_phone(row).strip()


def backfill_bid(bid: str, *, batch_size: int, dry_run: bool) -> dict:
    cfg = Config()
    orch = Orchestrator(bid)

    conn = pymysql.connect(
        host=cfg.DB_HOST,
        port=cfg.DB_PORT,
        user=cfg.DB_USER,
        password=cfg.DB_PASSWORD,
        database=cfg.DB_NAME,
        cursorclass=DictCursor,
    )
    stats = {
        "scanned": 0,
        "updated": 0,
        "cleared": 0,
        "unchanged": 0,
        "missing_source": 0,
        "updated_from_callid": 0,
    }
    try:
        with conn.cursor() as cursor:
            raw_table = f"{bid}_raw_calls"
            cursor.execute("SHOW TABLES LIKE %s", (raw_table,))
            if not cursor.fetchone():
                raise RuntimeError(f"{raw_table} not found")

            cursor.execute(f"SELECT callid, customer_callinfo, direction FROM `{raw_table}` ORDER BY callid")
            raw_rows = cursor.fetchall() or []
            if not raw_rows:
                return stats

            source_conn = orch.get_source_db_connection()
            try:
                with source_conn.cursor() as source_cursor:
                    source_table = orch._resolve_source_table(source_cursor)
                    source_cols = orch._source_table_columns(source_cursor, source_table)
                    callid_col = orch._first_source_col(source_cols, "callid", "call_id")
                    if not callid_col:
                        raise RuntimeError(f"Source table {source_table} has no callid column")

                    optional_cols = []
                    for col in ("callto", "callfrom", "clicktocalldid", "direction", "customer_callinfo"):
                        if col in source_cols:
                            optional_cols.append(f"c.`{col}`")

                    select_cols = [f"c.`{callid_col}` AS callid"] + optional_cols
                    offset = 0
                    while offset < len(raw_rows):
                        chunk = raw_rows[offset : offset + batch_size]
                        offset += batch_size
                        callids = [str(row["callid"]) for row in chunk if row.get("callid")]
                        if not callids:
                            continue

                        placeholders = ", ".join(["%s"] * len(callids))
                        source_cursor.execute(
                            f"""
                            SELECT {", ".join(select_cols)}
                            FROM `{source_table}` c
                            WHERE c.`{callid_col}` IN ({placeholders})
                            """,
                            callids,
                        )
                        source_by_id = {
                            str(row["callid"]): row for row in (source_cursor.fetchall() or [])
                        }

                        for raw in chunk:
                            stats["scanned"] += 1
                            callid = str(raw.get("callid") or "")
                            current = str(raw.get("customer_callinfo") or "").strip()
                            source_row = source_by_id.get(callid)
                            if not source_row:
                                stats["missing_source"] += 1

                            resolved = _resolved_phone(raw, source_row)
                            if resolved == current:
                                stats["unchanged"] += 1
                                continue

                            if source_row is None:
                                stats["updated_from_callid"] += 1

                            if not resolved:
                                stats["cleared"] += 1
                                action = "clear"
                            else:
                                stats["updated"] += 1
                                action = "update"

                            if dry_run:
                                logger.info(
                                    "BID %s %s %s: %r -> %r%s",
                                    bid,
                                    action,
                                    callid,
                                    current,
                                    resolved,
                                    " (callid)" if source_row is None else "",
                                )
                                continue

                            _apply_update(cursor, raw_table, callid, resolved, dry_run=False)
            finally:
                source_conn.close()

            if not dry_run:
                conn.commit()
    finally:
        conn.close()

    return stats


def main() -> None:
    parser = argparse.ArgumentParser(
        description="Backfill customer_callinfo from Mcube source (fixes clicktocalldid lead grouping)"
    )
    parser.add_argument("--bid", required=True, help="Business ID, e.g. 0338")
    parser.add_argument("--batch-size", type=int, default=200, help="Source lookup batch size")
    parser.add_argument("--dry-run", action="store_true", help="Log changes without writing")
    args = parser.parse_args()

    stats = backfill_bid(args.bid, batch_size=max(1, args.batch_size), dry_run=args.dry_run)
    logger.info(
        "BID %s done — scanned=%s updated=%s cleared=%s unchanged=%s missing_source=%s "
        "updated_from_callid=%s %s",
        args.bid,
        stats["scanned"],
        stats["updated"],
        stats["cleared"],
        stats["unchanged"],
        stats["missing_source"],
        stats["updated_from_callid"],
        "(dry-run)" if args.dry_run else "",
    )


if __name__ == "__main__":
    main()