"""
Import callto/callfrom from an Excel file into {bid}_raw_calls.

Mapping:
  callto   -> customer_callinfo
  callfrom -> agent_callinfo
"""
import argparse
from typing import List, Dict

from config import Config
from db_handler import DatabaseHandler


class ConfigWrapper:
    def __init__(self, config):
        self._config = config

    def get(self, key, default=None):
        return getattr(self._config, key, default)

    def __getattr__(self, key):
        return getattr(self._config, key)


def normalize_header(value: str) -> str:
    if value is None:
        return ""
    return "".join(ch for ch in str(value).lower() if ch.isalnum())


def load_rows_with_pandas(path: str) -> List[Dict]:
    import pandas as pd

    df = pd.read_excel(path, dtype=str)
    return df.to_dict(orient="records")


def load_rows_with_openpyxl(path: str) -> List[Dict]:
    from openpyxl import load_workbook

    wb = load_workbook(path, read_only=True, data_only=True)
    ws = wb.active
    rows = ws.iter_rows(values_only=True)
    headers = next(rows, None)
    if not headers:
        return []
    normalized = [normalize_header(h) for h in headers]
    result = []
    for row in rows:
        data = {}
        for idx, value in enumerate(row):
            data[normalized[idx]] = value
        result.append(data)
    return result


def load_excel_rows(path: str) -> List[Dict]:
    try:
        return load_rows_with_pandas(path)
    except Exception:
        return load_rows_with_openpyxl(path)


def pick_value(row: Dict, keys: List[str]):
    for key in keys:
        if key in row and row[key] is not None:
            text = str(row[key]).strip()
            if text and text.lower() not in {"nan", "none", "null"}:
                return text
    return ""


def main():
    parser = argparse.ArgumentParser(description="Import callto/callfrom into raw_calls.")
    parser.add_argument("--bid", default="1713", help="Business ID (default: 1713)")
    parser.add_argument("--file", default="Windlass_Calls_2.xlsx", help="Excel file path")
    args = parser.parse_args()

    rows = load_excel_rows(args.file)
    if not rows:
        print(f"No rows found in {args.file}")
        return

    config = Config()
    db_handler = DatabaseHandler(ConfigWrapper(config))

    with db_handler.get_connection() as conn:
        cursor = conn.cursor()
        cursor.execute(f"SHOW COLUMNS FROM `{args.bid}_raw_calls`")
        columns = {row["Field"] for row in cursor.fetchall()}

        missing_cols = [col for col in ["callid", "agent_callinfo", "customer_callinfo"] if col not in columns]
        if missing_cols:
            print(f"Missing columns in `{args.bid}_raw_calls`: {', '.join(missing_cols)}")
            return

        updates = []
        for row in rows:
            normalized_row = {normalize_header(k): v for k, v in row.items()}
            callid = pick_value(normalized_row, ["callid"])
            if not callid:
                continue
            callto = pick_value(normalized_row, ["callto", "call_to"])
            callfrom = pick_value(normalized_row, ["callfrom", "call_from"])
            updates.append((callto, callfrom, callid))

        if not updates:
            print("No matching callid rows found to update.")
            return

        update_query = f"""
            UPDATE `{args.bid}_raw_calls`
            SET
                customer_callinfo = COALESCE(NULLIF(%s, ''), customer_callinfo),
                agent_callinfo = COALESCE(NULLIF(%s, ''), agent_callinfo)
            WHERE callid = %s
        """

        cursor.executemany(update_query, updates)
        conn.commit()
        print(f"Updated {cursor.rowcount} rows in `{args.bid}_raw_calls`.")


if __name__ == "__main__":
    main()