"""Heal stuck STT queue state across all pipeline-enabled BIDs."""

from __future__ import annotations

import logging
import os
from typing import Any, Dict, List

logger = logging.getLogger(__name__)


def _has_transcript_sql(alias: str = "s") -> str:
    return (
        f"{alias}.transcript IS NOT NULL AND TRIM({alias}.transcript) != ''"
    )


def heal_stuck_queued_calls(
    db_handler,
    *,
    stuck_minutes: int | None = None,
    limit_per_bid: int = 50,
    bids: List[str] | None = None,
) -> Dict[str, Any]:
    """
    Reset status=1 calls that have no transcript and exceeded the stuck threshold.

    Safe to run while RabbitMQ has backlog: workers skip calls that already have
    transcripts; orchestrator re-queues status=0 calls on the next cycle.
    """
    stuck_minutes = max(
        10,
        int(
            stuck_minutes
            if stuck_minutes is not None
            else os.getenv("STT_STUCK_QUEUED_MINUTES", "25")
        ),
    )
    limit_per_bid = max(1, min(int(limit_per_bid or 50), 500))
    if bids is None:
        db_handler.ensure_business_pipeline_config_table()
        bids = db_handler.get_enabled_pipeline_bids() or []

    stats: Dict[str, Any] = {
        "stuck_minutes": stuck_minutes,
        "bids_checked": 0,
        "reset_by_bid": {},
        "reset_total": 0,
    }

    with db_handler.get_connection() as conn:
        cursor = conn.cursor()
        for bid in bids:
            bid = str(bid).strip()
            if not bid:
                continue
            raw_table = f"{bid}_raw_calls"
            resp_table = f"{bid}_sarvamresponse"
            cursor.execute("SHOW TABLES LIKE %s", (raw_table,))
            if not cursor.fetchone():
                continue
            stats["bids_checked"] += 1
            has_tx = _has_transcript_sql("s")
            cursor.execute(
                f"""
                UPDATE `{raw_table}` r
                SET r.status = 0,
                    r.transcription_status = 'stuck_requeued',
                    r.transcription_requested = 0
                WHERE r.status = 1
                  AND COALESCE(r.transcription_status, '') != 'backlog_cleared'
                  AND r.fileurl IS NOT NULL AND TRIM(r.fileurl) != ''
                  AND NOT EXISTS (
                    SELECT 1 FROM `{resp_table}` s
                    WHERE s.callid = r.callid AND {has_tx}
                  )
                  AND (
                    r.updated_at IS NULL
                    OR r.updated_at < DATE_SUB(NOW(), INTERVAL %s MINUTE)
                    OR r.call_starttime < DATE_SUB(NOW(), INTERVAL %s MINUTE)
                  )
                LIMIT %s
                """,
                (stuck_minutes, stuck_minutes, limit_per_bid),
            )
            reset_n = int(cursor.rowcount or 0)
            if reset_n:
                stats["reset_by_bid"][bid] = reset_n
                stats["reset_total"] += reset_n
        conn.commit()

    if stats["reset_total"]:
        logger.info(
            "STT queue healer: reset %s stuck status=1 row(s) across %s BID(s) (>%sm)",
            stats["reset_total"],
            len(stats["reset_by_bid"]),
            stuck_minutes,
        )
    return stats