"""
Aggregations for dashboard / reports from per-business tables on the `cluster` DB.
"""

from __future__ import annotations

import logging
from datetime import datetime, timedelta
from typing import Any

from django.db import connections
from django.db.utils import InterfaceError, OperationalError

from apps.cluster.dynamic_tables import _q_ident, _table_names

logger = logging.getLogger(__name__)


def _legacy_profile_for_user(user) -> dict[str, Any] | None:
    """Match logged-in Django user to a row in master `users` (same idea as auth)."""
    email = (getattr(user, "email", None) or "").strip()
    username = (getattr(user, "username", None) or "").strip()
    ident = email or username
    if not ident:
        return None
    sql = """
        SELECT business_id, agent_id, role
        FROM users
        WHERE (email = %s OR username = %s)
        LIMIT 1
    """
    for attempt in (1, 2):
        try:
            with connections["default"].cursor() as cur:
                cur.execute(sql, [ident, ident])
                row = cur.fetchone()
                if not row:
                    return None
                return {
                    "business_id": int(row[0]) if row[0] is not None else None,
                    "agent_id": int(row[1]) if row[1] not in (None, "") else None,
                    "role": row[2],
                }
        except (InterfaceError, OperationalError) as exc:
            # Likely a dropped MySQL connection; close and retry once.
            logger.warning(
                "reporting_utils._legacy_profile_for_user db error attempt=%s ident=%s exc=%s",
                attempt,
                ident,
                exc,
            )
            try:
                connections["default"].close_if_unusable_or_obsolete()
            except Exception:
                logger.exception("reporting_utils._legacy_profile_for_user close failed")
            if attempt == 2:
                return None
        except Exception:
            logger.exception("reporting_utils._legacy_profile_for_user failed")
            return None
    return None


def _admin_role(role: Any) -> bool:
    r = str(role or "").lower().replace(" ", "_")
    return r in frozenset({"admin", "superadmin", "super_admin", "owner"})


def _cluster_db_name() -> str:
    return str(connections["cluster"].settings_dict.get("NAME") or "")


def cluster_table_exists(table: str) -> bool:
    db = _cluster_db_name()
    if not db or not table:
        return False
    try:
        with connections["cluster"].cursor() as cur:
            cur.execute(
                """
                SELECT 1 FROM information_schema.tables
                WHERE table_schema = %s AND table_name = %s
                LIMIT 1
                """,
                [db, table],
            )
            return cur.fetchone() is not None
    except Exception:
        logger.exception("cluster_table_exists failed table=%s", table)
        return False


def call_history_table_name(business_id: int) -> str:
    _, _, ch, _ = _table_names(int(business_id))
    return ch


def _agent_sql_fragment(legacy: dict[str, Any]) -> tuple[str, list[Any]]:
    if _admin_role(legacy.get("role")):
        return "", []
    aid = legacy.get("agent_id")
    if aid is None:
        return "", []
    return "agent_id = %s", [aid]


def fetch_dashboard_stats(legacy: dict[str, Any]) -> dict[str, Any]:
    bid = legacy.get("business_id")
    if not bid:
        return _empty_stats()

    table = call_history_table_name(int(bid))
    if not cluster_table_exists(table):
        return _empty_stats()

    agent_frag, agent_params = _agent_sql_fragment(legacy)
    where_parts = ["business_id = %s"]
    if agent_frag:
        where_parts.append(agent_frag)
    where_sql = " AND ".join(where_parts)
    sql = f"""
        SELECT
            COUNT(*) AS total_calls,
            COALESCE(SUM(transferred = 1), 0) AS transferred_calls,
            COALESCE(SUM(ongoing = 1), 0) AS active_calls,
            COALESCE(AVG(call_duration_secs), 0) AS avg_secs,
            COALESCE(SUM(campaign_id IS NOT NULL AND campaign_id <> 0), 0) AS outbound_calls,
            COALESCE(SUM(campaign_id IS NULL OR campaign_id = 0), 0) AS inbound_calls,
            COALESCE(SUM(
                (call_successful = 1)
                OR LOWER(COALESCE(call_status, '')) IN ('success', 'completed', 'resolved')
            ), 0) AS resolved_calls
        FROM {_q_ident(table)}
        WHERE {where_sql}
    """
    params: list[Any] = [int(bid), *agent_params]
    try:
        with connections["cluster"].cursor() as cur:
            cur.execute(sql, params)
            row = cur.fetchone()
            if not row:
                return _empty_stats()
            total, transferred, active, avg_secs, outbound, inbound, resolved = row
            total = int(total or 0)
            transferred = int(transferred or 0)
            resolved = int(resolved or 0)
            bot_handled = max(0, total - transferred)
            avg_min = round(float(avg_secs or 0) / 60.0, 2) if avg_secs else 0
            return {
                "totalCalls": total,
                "botHandledCalls": bot_handled,
                "resolvedCalls": resolved,
                "transferredCalls": transferred,
                "activeCallsCount": int(active or 0),
                "avgHandlingTime": avg_min,
                "availMin": 0,
                "totalMin": 0,
                "remainMin": 0,
                "inboundCalls": int(inbound or 0),
                "outboundCalls": int(outbound or 0),
            }
    except Exception:
        logger.exception("fetch_dashboard_stats failed business_id=%s", bid)
        return _empty_stats()


def _empty_stats() -> dict[str, Any]:
    return {
        "totalCalls": 0,
        "botHandledCalls": 0,
        "resolvedCalls": 0,
        "transferredCalls": 0,
        "activeCallsCount": 0,
        "avgHandlingTime": 0,
        "availMin": 0,
        "totalMin": 0,
        "remainMin": 0,
        "inboundCalls": 0,
        "outboundCalls": 0,
    }


def fetch_dashboard_charts(
    legacy: dict[str, Any],
    date_from: str | None,
    date_to: str | None,
) -> list[dict[str, Any]]:
    bid = legacy.get("business_id")
    if not bid:
        return []

    table = call_history_table_name(int(bid))
    if not cluster_table_exists(table):
        return []

    if not date_from or not date_to:
        end = datetime.utcnow().date()
        start = end - timedelta(days=7)
        date_from = start.isoformat()
        date_to = end.isoformat()

    agent_frag, agent_params = _agent_sql_fragment(legacy)
    where_parts = [
        "business_id = %s",
        "call_start_time >= %s",
        "call_start_time < DATE_ADD(%s, INTERVAL 1 DAY)",
    ]
    if agent_frag:
        where_parts.append(agent_frag)
    where_sql = " AND ".join(where_parts)
    sql = f"""
        SELECT
            DATE_FORMAT(call_start_time, '%%H:00') AS t,
            COUNT(*) AS calls,
            COALESCE(SUM(
                (call_successful = 1)
                OR LOWER(COALESCE(call_status, '')) IN ('success', 'completed', 'resolved')
            ), 0) AS resolved,
            COALESCE(SUM(transferred = 1), 0) AS transferred
        FROM {_q_ident(table)}
        WHERE {where_sql}
        GROUP BY t
        ORDER BY t
    """
    params: list[Any] = [int(bid), date_from, date_to, *agent_params]
    try:
        with connections["cluster"].cursor() as cur:
            cur.execute(sql, params)
            rows = cur.fetchall()
            out: list[dict[str, Any]] = []
            for t, calls, resolved, transferred in rows:
                if not t:
                    continue
                out.append(
                    {
                        "time": str(t),
                        "calls": int(calls or 0),
                        "resolved": int(resolved or 0),
                        "transferred": int(transferred or 0),
                    }
                )
            return out
    except Exception:
        logger.exception("fetch_dashboard_charts failed business_id=%s", bid)
        return []


def fetch_active_calls_count(legacy: dict[str, Any], recent_minutes: int) -> int:
    bid = legacy.get("business_id")
    if not bid:
        return 0
    table = call_history_table_name(int(bid))
    if not cluster_table_exists(table):
        return 0
    agent_frag, agent_params = _agent_sql_fragment(legacy)
    minutes = max(1, min(int(recent_minutes or 5), 1440))
    where_parts = [
        "business_id = %s",
        """(
            (ongoing = 1 AND call_start_time >= (NOW() - INTERVAL %s MINUTE))
            OR (
              call_end_time IS NULL
              AND call_start_time IS NOT NULL
              AND call_start_time >= (NOW() - INTERVAL %s MINUTE)
            )
        )""",
    ]
    if agent_frag:
        where_parts.append(agent_frag)
    where_sql = " AND ".join(where_parts)
    sql = f"""
        SELECT COUNT(*) FROM {_q_ident(table)}
        WHERE {where_sql}
    """
    params: list[Any] = [int(bid), minutes, minutes, *agent_params]
    try:
        with connections["cluster"].cursor() as cur:
            cur.execute(sql, params)
            row = cur.fetchone()
            return int(row[0] or 0) if row else 0
    except Exception:
        logger.exception("fetch_active_calls_count failed")
        return 0


def fetch_call_history_page(
    legacy: dict[str, Any],
    *,
    limit: int,
    offset: int,
    start_date: str | None,
    end_date: str | None,
    agent_id: str | None,
    customer_number: str | None,
    campaign_id: str | None,
    conversation_id: str | None,
) -> tuple[list[dict[str, Any]], int]:
    bid = legacy.get("business_id")
    if not bid:
        return [], 0
    table = call_history_table_name(int(bid))
    if not cluster_table_exists(table):
        return [], 0

    where: list[str] = ["business_id = %s"]
    params: list[Any] = [int(bid)]

    role_agent_frag, role_agent_params = _agent_sql_fragment(legacy)
    if role_agent_frag:
        where.append(role_agent_frag)
        params.extend(role_agent_params)

    if start_date:
        where.append("call_start_time >= %s")
        params.append(start_date)
    if end_date:
        where.append("call_start_time < DATE_ADD(%s, INTERVAL 1 DAY)")
        params.append(end_date)
    if agent_id:
        where.append("(CAST(agent_id AS CHAR) = %s OR agent_name LIKE %s)")
        params.extend([str(agent_id), f"%{agent_id}%"])
    if customer_number:
        where.append(
            "(phone_number LIKE %s OR agent_phone_number LIKE %s)"
        )
        like = f"%{customer_number}%"
        params.extend([like, like])
    if campaign_id:
        where.append("campaign_id = %s")
        params.append(int(campaign_id) if str(campaign_id).isdigit() else campaign_id)
    if conversation_id:
        where.append("conversation_id = %s")
        params.append(conversation_id)

    where_sql = " AND ".join(where)

    count_sql = f"SELECT COUNT(*) FROM {_q_ident(table)} WHERE {where_sql}"
    list_sql = f"""
        SELECT
            id,
            conversation_id,
            agent_id,
            agent_name,
            phone_number,
            agent_phone_number,
            customer_name,
            call_start_time,
            call_end_time,
            call_duration_secs,
            call_successful,
            call_status,
            ongoing,
            transferred,
            sentiment,
            summary,
            recording_url,
            conversation_data,
            campaign_id
        FROM {_q_ident(table)}
        WHERE {where_sql}
        ORDER BY call_start_time DESC, id DESC
        LIMIT %s OFFSET %s
    """

    try:
        with connections["cluster"].cursor() as cur:
            cur.execute(count_sql, params)
            total_row = cur.fetchone()
            total = int(total_row[0] or 0) if total_row else 0
            cur.execute(list_sql, [*params, int(limit), int(offset)])
            cols = [c[0] for c in cur.description]
            conversations: list[dict[str, Any]] = []
            for row in cur.fetchall():
                d = dict(zip(cols, row))
                conversations.append(_serialize_call_row(d))
            return conversations, total
    except Exception:
        logger.exception("fetch_call_history_page failed")
        return [], 0


def _serialize_call_row(d: dict[str, Any]) -> dict[str, Any]:
    return {
        "id": d.get("id"),
        "conversation_id": d.get("conversation_id"),
        "agent_id": d.get("agent_id"),
        "agent_name": d.get("agent_name"),
        "phone_number": d.get("phone_number"),
        "agent_phone_number": d.get("agent_phone_number"),
        "customer_name": d.get("customer_name"),
        "call_start_time": d.get("call_start_time").isoformat()
        if d.get("call_start_time")
        else None,
        "call_end_time": d.get("call_end_time").isoformat()
        if d.get("call_end_time")
        else None,
        "call_duration_secs": d.get("call_duration_secs"),
        "call_successful": d.get("call_successful"),
        "call_status": d.get("call_status"),
        "transferred": bool(d.get("transferred")),
        "sentiment": d.get("sentiment"),
        "summary": d.get("summary"),
        "recording_url": d.get("recording_url"),
        "conversation_data": d.get("conversation_data"),
        "campaign_id": d.get("campaign_id"),
    }