"""Shared usage allocation checks for ingest, STT queue, and API responses."""

from __future__ import annotations

from typing import Any, Dict, Optional

DEFAULT_WARN_THRESHOLD_PERCENT = 90.0


def _table_exists(cursor, table_name: str) -> bool:
    cursor.execute(
        """
        SELECT 1
        FROM information_schema.tables
        WHERE table_schema = DATABASE() AND table_name = %s
        LIMIT 1
        """,
        (table_name,),
    )
    return cursor.fetchone() is not None


def get_monthly_minute_limit(cursor, bid: str) -> int:
    if not _table_exists(cursor, "pca_business_allocations"):
        return 0
    cursor.execute(
        """
        SELECT monthly_minute_limit, monthly_call_limit
        FROM pca_business_allocations
        WHERE bid = %s
        LIMIT 1
        """,
        (str(bid),),
    )
    row = cursor.fetchone() or {}
    return int(
        row.get("monthly_minute_limit")
        or row.get("monthly_call_limit")
        or 0
    )


def get_validity_months(cursor, bid: str) -> int:
    if not _table_exists(cursor, "pca_business_allocations"):
        return 0
    cursor.execute(
        """
        SELECT validity_months
        FROM pca_business_allocations
        WHERE bid = %s
        LIMIT 1
        """,
        (str(bid),),
    )
    row = cursor.fetchone() or {}
    return int(row.get("validity_months") or 0)


def pick_onboarding_date(business_created_at, pipeline_created_at):
    return business_created_at or pipeline_created_at


def resolve_onboarding_date(cursor, bid: str):
    business_created_at = None
    if _table_exists(cursor, "businesses"):
        cursor.execute(
            "SELECT created_at FROM businesses WHERE bid = %s LIMIT 1",
            (str(bid),),
        )
        row = cursor.fetchone() or {}
        business_created_at = row.get("created_at")

    pipeline_created_at = None
    if _table_exists(cursor, "business_pipeline_config"):
        cursor.execute(
            "SELECT created_at FROM business_pipeline_config WHERE bid = %s LIMIT 1",
            (str(bid),),
        )
        row = cursor.fetchone() or {}
        pipeline_created_at = row.get("created_at")

    return pick_onboarding_date(business_created_at, pipeline_created_at)


def _compute_validity_status(cursor, onboarding_date, validity_months: int):
    if not onboarding_date or not validity_months or validity_months <= 0:
        return None, False
    cursor.execute(
        """
        SELECT
            DATE_ADD(%s, INTERVAL %s MONTH) AS expiry_date,
            DATE_ADD(%s, INTERVAL %s MONTH) < NOW() AS expired
        """,
        (onboarding_date, validity_months, onboarding_date, validity_months),
    )
    row = cursor.fetchone() or {}
    return row.get("expiry_date"), bool(row.get("expired"))


def get_transcribed_minutes(cursor, bid: str) -> int:
    raw_table = f"{bid}_raw_calls"
    sarvam_table = f"{bid}_sarvamresponse"
    if not _table_exists(cursor, raw_table):
        return 0
    if not _table_exists(cursor, sarvam_table):
        return 0

    cursor.execute(
        f"""
        SELECT COALESCE(
            SUM(
                CASE
                    WHEN s.transcript IS NOT NULL AND s.transcript != ''
                    THEN COALESCE(s.duration, TIMESTAMPDIFF(SECOND, r.call_starttime, r.call_endtime))
                    ELSE 0
                END
            ),
            0
        ) AS transcribed_seconds
        FROM `{raw_table}` r
        LEFT JOIN `{sarvam_table}` s ON r.callid = s.callid
        """
    )
    seconds = float((cursor.fetchone() or {}).get("transcribed_seconds") or 0)
    return int(seconds / 60)


def evaluate_usage_allocation(
    cursor,
    bid: str,
    *,
    warn_threshold_percent: float = DEFAULT_WARN_THRESHOLD_PERCENT,
    onboarding_date=None,
    validity_months: Optional[int] = None,
) -> Dict[str, Any]:
    used_minutes = get_transcribed_minutes(cursor, bid)
    monthly_limit = get_monthly_minute_limit(cursor, bid)
    unlimited = monthly_limit <= 0
    usage_percent: Optional[float] = None
    if not unlimited:
        usage_percent = min(round((used_minutes / monthly_limit) * 100, 2), 100.0)

    limit_exhausted = bool(not unlimited and used_minutes >= monthly_limit)
    near_limit = bool(
        not unlimited
        and not limit_exhausted
        and usage_percent is not None
        and usage_percent >= warn_threshold_percent
    )

    if validity_months is None:
        validity_months = get_validity_months(cursor, bid)
    if onboarding_date is None:
        onboarding_date = resolve_onboarding_date(cursor, bid)
    validity_expiry_date, validity_expired = _compute_validity_status(
        cursor, onboarding_date, validity_months
    )

    return {
        "used_minutes": used_minutes,
        "monthly_minute_limit": monthly_limit,
        "usage_percent": usage_percent,
        "unlimited": unlimited,
        "limit_exhausted": limit_exhausted,
        "near_limit": near_limit,
        "validity_months": validity_months,
        "onboarding_date": onboarding_date,
        "validity_expiry_date": validity_expiry_date,
        "validity_expired": validity_expired,
        "blocked": limit_exhausted or validity_expired,
        "blocked_reasons": [
            reason
            for reason, flag in (
                ("usage_limit", limit_exhausted),
                ("validity_expired", validity_expired),
            )
            if flag
        ],
    }


def is_usage_blocked(cursor, bid: str) -> bool:
    return bool(evaluate_usage_allocation(cursor, bid)["blocked"])