from __future__ import annotations
import logging
from typing import Any, Dict, List, Optional
from db.connection import get_connection

logger = logging.getLogger(__name__)

_CREATE_TABLE_SQL = """
CREATE TABLE IF NOT EXISTS `stt_pipeline_bid_config` (
    `bid`               VARCHAR(100) NOT NULL,
    `enabled`           TINYINT(1)   NOT NULL DEFAULT 0,
    `raw_calls_id_col`  VARCHAR(100) NOT NULL DEFAULT 'id',
    `raw_calls_url_col` VARCHAR(100) NOT NULL DEFAULT 'recording_url',
    `batch_size`        INT          NOT NULL DEFAULT 10,
    `poll_interval_s`   INT          NOT NULL DEFAULT 30,
    `notes`             TEXT         NULL,
    `created_at`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`bid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
"""


def ensure_table() -> None:
    with get_connection() as conn:
        with conn.cursor() as cur:
            cur.execute(_CREATE_TABLE_SQL)


def get_all_bid_configs() -> List[Dict[str, Any]]:
    with get_connection() as conn:
        with conn.cursor() as cur:
            cur.execute("SELECT * FROM stt_pipeline_bid_config ORDER BY bid")
            return cur.fetchall() or []


def get_enabled_bids() -> List[Dict[str, Any]]:
    with get_connection() as conn:
        with conn.cursor() as cur:
            cur.execute(
                "SELECT * FROM stt_pipeline_bid_config WHERE enabled = 1 ORDER BY bid"
            )
            return cur.fetchall() or []


def ensure_bid_registered(
    bid: str,
    id_col: str = "id",
    url_col: str = "recording_url",
) -> None:
    """Insert a disabled row for this bid if it doesn't already exist."""
    with get_connection() as conn:
        with conn.cursor() as cur:
            cur.execute(
                "INSERT IGNORE INTO stt_pipeline_bid_config "
                "(bid, enabled, raw_calls_id_col, raw_calls_url_col) "
                "VALUES (%s, 0, %s, %s)",
                (bid, id_col, url_col),
            )


def get_bid_config(bid: str) -> Optional[Dict[str, Any]]:
    with get_connection() as conn:
        with conn.cursor() as cur:
            cur.execute(
                "SELECT * FROM stt_pipeline_bid_config WHERE bid = %s", (bid,)
            )
            return cur.fetchone()