"""Mcube Classic agent resolution: callhistory links agents via eid → {bid}_employee."""

from __future__ import annotations

from typing import Any, Dict, List, Optional, Tuple

from mcube_group_util import _column_exists, _first_col, _table_exists
from mcube_phone_util import valid_phone_sql


def _nullif_trim(expr: str) -> str:
    return f"NULLIF(TRIM(CAST({expr} AS CHAR)), '')"


def _direction_case(
    direction_sql: str,
    outbound_expr: str,
    inbound_expr: str,
    default_expr: str,
) -> str:
    if direction_sql == "NULL":
        return default_expr
    return (
        "CASE "
        f"WHEN LOWER(TRIM(CAST({direction_sql} AS CHAR))) = 'outbound' THEN {outbound_expr} "
        f"WHEN LOWER(TRIM(CAST({direction_sql} AS CHAR))) = 'inbound' THEN {inbound_expr} "
        f"ELSE {default_expr} END"
    )


def mcube_customer_callinfo_sql(
    source_cols: Dict[str, str],
    *,
    call_alias: str = "c",
) -> str:
    """Direction-aware customer phone SQL (Mcube Classic defaults to inbound)."""
    callfrom_col = _first_col(source_cols, "callfrom")
    callto_col = _first_col(source_cols, "callto")
    direction_col = _first_col(source_cols, "direction")
    direction_sql = f"{call_alias}.`{direction_col}`" if direction_col else "NULL"
    callfrom_expr = _nullif_trim(f"{call_alias}.`{callfrom_col}`") if callfrom_col else "NULL"
    callto_expr = _nullif_trim(f"{call_alias}.`{callto_col}`") if callto_col else "NULL"

    customer_parts: List[str] = []
    if "customer_callinfo" in source_cols:
        col = source_cols["customer_callinfo"]
        customer_parts.append(valid_phone_sql(f"{call_alias}.`{col}`"))
    if callfrom_col and callto_col:
        direction_aware = _direction_case(
            direction_sql, callto_expr, callfrom_expr, callfrom_expr
        )
        customer_parts.append(valid_phone_sql(direction_aware))
        customer_parts.append(valid_phone_sql(callto_expr))
        customer_parts.append(valid_phone_sql(callfrom_expr))
    elif callfrom_col:
        customer_parts.append(valid_phone_sql(callfrom_expr))
    elif callto_col:
        customer_parts.append(valid_phone_sql(callto_expr))
    if not customer_parts:
        return "NULL"
    return f"COALESCE({', '.join(customer_parts + ['NULL'])})"


def resolve_mcube_agent_sql(
    cursor,
    src_bid: str,
    source_table: str,
    source_cols: Dict[str, str],
    *,
    call_alias: str = "c",
) -> Tuple[str, List[Any], str, str, str]:
    """
    Return (join_sql, join_params, agentname_select_sql, agent_phone_select_sql, customer_select_sql).

    Mcube Classic callhistory (no direction column) is treated as inbound:
    - callfrom = customer phone
    - callto / employee.empnumber = agent line
    """
    src_bid = str(src_bid).strip()
    eid_col = _first_col(source_cols, "eid")
    agentname_col = _first_col(source_cols, "agentname", "agent_name")
    callfrom_col = _first_col(source_cols, "callfrom")
    callto_col = _first_col(source_cols, "callto")
    direction_col = _first_col(source_cols, "direction")
    emp_phone_col = _first_col(source_cols, "emp_phone")

    direction_sql = f"{call_alias}.`{direction_col}`" if direction_col else "NULL"
    callfrom_expr = _nullif_trim(f"{call_alias}.`{callfrom_col}`") if callfrom_col else "NULL"
    callto_expr = _nullif_trim(f"{call_alias}.`{callto_col}`") if callto_col else "NULL"

    agent_phone_fallback = _direction_case(direction_sql, callfrom_expr, callto_expr, callto_expr)
    if emp_phone_col:
        agent_phone_fallback = (
            f"COALESCE({_nullif_trim(f'{call_alias}.`{emp_phone_col}`')}, {agent_phone_fallback})"
        )

    customer_expr = mcube_customer_callinfo_sql(source_cols, call_alias=call_alias)
    customer_sql = f"{customer_expr} AS customer_callinfo"

    join_sql = ""
    join_params: List[Any] = []
    employee_table = f"{src_bid}_employee"

    # Agent display name: employee table and explicit agentname column only.
    # Do NOT use callername for inbound calls — in Mcube it is the customer/caller name.
    name_candidates: List[str] = []

    if (
        eid_col
        and _table_exists(cursor, employee_table)
        and _column_exists(cursor, employee_table, "empname")
    ):
        join_sql = (
            f"LEFT JOIN `{employee_table}` e "
            f"ON {call_alias}.`{eid_col}` = e.eid"
        )
        name_candidates.append(_nullif_trim("e.empname"))
        empnumber_expr = _nullif_trim("e.empnumber")
        agent_phone_sql = f"COALESCE({empnumber_expr}, {agent_phone_fallback}, '') AS emp_phone"
    else:
        agent_phone_sql = f"COALESCE({agent_phone_fallback}, '') AS emp_phone"

    if agentname_col:
        name_candidates.append(_nullif_trim(f"{call_alias}.`{agentname_col}`"))

    if name_candidates:
        agentname_sql = f"COALESCE({', '.join(name_candidates + ['NULL'])}) AS agentname"
    else:
        agentname_sql = "'' AS agentname"

    return join_sql, join_params, agentname_sql, agent_phone_sql, customer_sql