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

logger = logging.getLogger(__name__)

DEFAULT_RECORDING_URL_COLUMN = "recording_url"
DEFAULT_CALL_ID_COLUMN = "id"


def get_all_bids() -> List[str]:
    with get_connection() as conn:
        with conn.cursor() as cur:
            cur.execute(
                "SELECT TABLE_NAME FROM information_schema.TABLES "
                "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME LIKE %s",
                ("%_raw_calls",),
            )
            rows = cur.fetchall()
    return [row["TABLE_NAME"].replace("_raw_calls", "") for row in rows]


def get_new_calls(
    bid: str,
    already_seen_ids: List[str],
    limit: int = 100,
    id_col: str = DEFAULT_CALL_ID_COLUMN,
    url_col: str = DEFAULT_RECORDING_URL_COLUMN,
) -> List[Dict[str, Any]]:
    table = f"{bid}_raw_calls"
    with get_connection() as conn:
        with conn.cursor() as cur:
            if already_seen_ids:
                placeholders = ", ".join(["%s"] * len(already_seen_ids))
                cur.execute(
                    f"SELECT * FROM `{table}` "
                    f"WHERE `{id_col}` NOT IN ({placeholders}) "
                    f"AND `{url_col}` IS NOT NULL "
                    f"AND `{url_col}` != '' "
                    f"ORDER BY `{id_col}` DESC LIMIT %s",
                    (*already_seen_ids, limit),
                )
            else:
                cur.execute(
                    f"SELECT * FROM `{table}` "
                    f"WHERE `{url_col}` IS NOT NULL "
                    f"AND `{url_col}` != '' "
                    f"ORDER BY `{id_col}` DESC LIMIT %s",
                    (limit,),
                )
            rows = cur.fetchall()
    result = []
    for row in rows:
        normalised = dict(row)
        normalised["call_id"] = str(row[id_col])
        normalised["recording_url"] = row.get(url_col, "")
        result.append(normalised)
    return result