"""Mcube Classic group resolution: callhistory often uses gid, names live in {bid}_groups."""

from __future__ import annotations

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


def _table_exists(cursor, table_name: str) -> bool:
    cursor.execute("SHOW TABLES LIKE %s", (table_name,))
    return cursor.fetchone() is not None


def _column_exists(cursor, table_name: str, column_name: str) -> bool:
    cursor.execute(f"SHOW COLUMNS FROM `{table_name}` LIKE %s", (column_name,))
    return cursor.fetchone() is not None


def _first_col(source_cols: Dict[str, str], *candidates: str) -> Optional[str]:
    for candidate in candidates:
        key = candidate.lower()
        if key in source_cols:
            return source_cols[key]
    return None


def fetch_mcube_source_groups(cursor, src_bid: str, callhistory_table: str) -> List[Dict[str, Any]]:
    """Distinct group names with call counts from Mcube source tables."""
    src_bid = str(src_bid).strip()
    if not callhistory_table or not _table_exists(cursor, callhistory_table):
        return []

    if _column_exists(cursor, callhistory_table, "groupname"):
        cursor.execute(
            f"""
            SELECT groupname, COUNT(*) AS call_count
            FROM `{callhistory_table}`
            WHERE bid = %s
              AND groupname IS NOT NULL
              AND TRIM(groupname) != ''
            GROUP BY groupname
            ORDER BY call_count DESC, groupname
            """,
            (src_bid,),
        )
        return _rows_to_groups(cursor.fetchall() or [])

    groups_table = f"{src_bid}_groups"
    gid_col = "gid" if _column_exists(cursor, callhistory_table, "gid") else None
    if gid_col and _table_exists(cursor, groups_table) and _column_exists(cursor, groups_table, "groupname"):
        cursor.execute(
            f"""
            SELECT g.groupname, COUNT(c.`{gid_col}`) AS call_count
            FROM `{groups_table}` g
            LEFT JOIN `{callhistory_table}` c
              ON c.`{gid_col}` = g.gid AND c.bid = %s
            WHERE g.bid = %s
              AND g.groupname IS NOT NULL
              AND TRIM(g.groupname) != ''
            GROUP BY g.groupname
            ORDER BY call_count DESC, g.groupname
            """,
            (src_bid, src_bid),
        )
        return _rows_to_groups(cursor.fetchall() or [])

    return []


def _rows_to_groups(rows) -> List[Dict[str, Any]]:
    groups: List[Dict[str, Any]] = []
    for row in rows:
        if isinstance(row, dict):
            name = str(row.get("groupname") or "").strip()
            count = int(row.get("call_count") or 0)
        else:
            name = str(row[0] or "").strip()
            count = int(row[1] or 0)
        if name:
            groups.append({"groupname": name, "call_count": count})
    return groups


def resolve_mcube_group_sql(
    cursor,
    src_bid: str,
    source_table: str,
    source_cols: Dict[str, str],
    *,
    call_alias: str = "c",
) -> Tuple[str, List[Any], str, Optional[str]]:
    """
    Return (join_sql, join_params, groupname_select_sql, group_filter_expr).

    group_filter_expr is used in WHERE for allowed group names.
    """
    src_bid = str(src_bid).strip()
    group_col = _first_col(source_cols, "groupname", "group_name", "group")
    if group_col:
        expr = f"{call_alias}.`{group_col}`"
        return "", [], f"{expr} AS groupname", expr

    gid_col = _first_col(source_cols, "gid", "gids")
    groups_table = f"{src_bid}_groups"
    if (
        gid_col
        and _table_exists(cursor, groups_table)
        and _column_exists(cursor, groups_table, "groupname")
    ):
        join_sql = (
            f"LEFT JOIN `{groups_table}` g "
            f"ON {call_alias}.`{gid_col}` = g.gid AND g.bid = %s"
        )
        groupname_sql = f"COALESCE(g.groupname, '') AS groupname"
        filter_expr = "g.groupname"
        return join_sql, [src_bid], groupname_sql, filter_expr

    if gid_col:
        expr = f"CAST({call_alias}.`{gid_col}` AS CHAR)"
        return "", [], f"{expr} AS groupname", expr

    return "", [], "'' AS groupname", None