import re
import time
from datetime import date, timedelta, datetime

import pandas as pd
from loguru import logger

from ..config import QUERY_PATHS, TABLES, SOURCES, COUNTRY_CODES, QUANTILES
from ..db import get_rdb
from ..db.queries import QueryLoader


def _to_sql_string(data: pd.DataFrame | list, column: str | None = None) -> str:
    if isinstance(data, pd.DataFrame):
        data_filtered = data[data[column].notna()]
        values = data_filtered[column].tolist()
    elif isinstance(data, list):
        values = [re.sub("'", "''", str(x)) for x in data]
    else:
        raise TypeError("Input data must be a pandas DataFrame or a list")

    return "(" + ",".join(["'" + x + "'" for x in values]) + ")"


def _get_dates(rdb, ql: QueryLoader, table_name: str) -> tuple[date, date]:
    data = rdb.query(ql.load(QUERY_PATHS.date, table_name=table_name))
    return data["base"][0], data["max_s"][0]


def _get_date_list(base: date, max_s: date) -> list[date]:
    delta = max_s - base
    return [base + timedelta(days=x) for x in range(1, delta.days + 1)]


def _run_discovery_query(
    rdb,
    ql: QueryLoader,
    source: str,
    d: date,
    mode: str,
    cc: list[str],
    pl: list[str],
) -> bool:
    query_path = getattr(QUERY_PATHS, f"{source}_discovery")
    query = ql.load(
        query_path,
        temp_table=TABLES[f"{source}_temp"],
        main_table=TABLES[f"{source}_main"],
        date=str(d),
        uri_str=_to_sql_string(pl) if pl else "()",
        quantile=QUANTILES.get(source, 0),
        cc_str=_to_sql_string(cc),
        mode=mode,
    )
    return rdb.execute(query)


def _update_source(source: str) -> str | None:
    rdb = get_rdb()
    ql = QueryLoader()

    base, max_s = _get_dates(rdb, ql, TABLES[f"{source}_main"])
    date_list = _get_date_list(base, max_s)

    if not date_list:
        logger.info(f"{source}: already up to date")
        return None

    logger.info(f"{source}: updating {len(date_list)} day(s)")

    status = False
    for d in date_list:
        start_time = time.time()
        status = _run_discovery_query(rdb, ql, source, d, "update", COUNTRY_CODES, [])
        elapsed = round(time.time() - start_time)
        if status:
            logger.info(f"{source}: {d} done ({elapsed}s)")
        else:
            logger.error(f"{source}: {d} failed")

    if status:
        return date_list[-1].strftime("%Y-%m-%d")
    return None


def run_update() -> dict[str, str | None]:
    logger.info("Starting discovery table update")
    statuses = {}
    for source in SOURCES:
        statuses[source] = _update_source(source)
    return statuses


def run_backfill_overrides(from_date: str | None = None) -> None:
    rdb = get_rdb()
    ql = QueryLoader()

    # Get override ISRCs
    override_table = TABLES["isrc_override"]
    overrides = rdb.query(f"SELECT DISTINCT isrc_cd FROM {override_table}")
    if overrides.empty:
        logger.info("No override ISRCs found")
        return

    isrc_list = overrides["isrc_cd"].tolist()
    isrc_str = _to_sql_string(isrc_list)
    logger.info(f"Found {len(isrc_list)} override ISRC(s)")

    cc_str = _to_sql_string(COUNTRY_CODES)

    for source in ("radio", "search"):
        query_path = getattr(QUERY_PATHS, f"{source}_backfill_override")
        main_table = TABLES[f"{source}_main"]
        temp_table = TABLES[f"{source}_temp"]

        # Determine date range
        _, max_s = _get_dates(rdb, ql, main_table)

        if from_date:
            try:
                base = datetime.strptime(from_date, "%Y-%m-%d").date()
            except ValueError:
                logger.error(f"Invalid date: {from_date} (must be YYYY-MM-DD)")
                raise SystemExit(1)
        else:
            earliest = rdb.query(f"SELECT MIN(report_date) AS d FROM {main_table}")
            base = earliest["d"][0] - timedelta(days=1)

        date_list = _get_date_list(base, max_s)
        if not date_list:
            logger.info(f"{source} overrides: nothing to backfill")
            continue

        logger.info(f"Backfilling {source} overrides: {len(date_list)} day(s) from {base + timedelta(days=1)}")

        for d in date_list:
            start_time = time.time()
            query = ql.load(
                query_path,
                temp_table=temp_table,
                main_table=main_table,
                date=str(d),
                cc_str=cc_str,
                isrc_str=isrc_str,
            )
            status = rdb.execute(query)
            elapsed = round(time.time() - start_time)
            if status:
                logger.info(f"{source} overrides: {d} done ({elapsed}s)")
            else:
                logger.error(f"{source} overrides: {d} failed")


def run_backfill(source: str, base_date: str, cc_or_uri: list[str]) -> None:
    source = source.lower()
    short_map = {"p": "playlist", "s": "search", "r": "radio"}
    source = short_map.get(source, source)

    if source not in SOURCES:
        logger.error(f"Invalid source: {source} (must be playlist, search, or radio)")
        raise SystemExit(1)

    try:
        base = datetime.strptime(base_date, "%Y-%m-%d").date()
    except ValueError:
        logger.error(f"Invalid date: {base_date} (must be YYYY-MM-DD)")
        raise SystemExit(1)

    if not cc_or_uri:
        label = "playlist URIs" if source == "playlist" else "country codes"
        logger.error(f"Must pass at least one {label}")
        raise SystemExit(1)

    rdb = get_rdb()
    ql = QueryLoader()

    _, max_s = _get_dates(rdb, ql, TABLES[f"{source}_main"])
    date_list = _get_date_list(base, max_s)

    if not date_list:
        logger.info(f"{source}: nothing to backfill")
        return

    cc = [c.upper() for c in cc_or_uri] if source != "playlist" else COUNTRY_CODES
    pl = cc_or_uri if source == "playlist" else []

    logger.info(f"Backfilling {source}: {len(date_list)} day(s) from {base}")

    for d in date_list:
        start_time = time.time()
        status = _run_discovery_query(rdb, ql, source, d, "backfill", cc, pl)
        elapsed = round(time.time() - start_time)
        if status:
            logger.info(f"{source}: {d} done ({elapsed}s)")
        else:
            logger.error(f"{source}: {d} failed")