# src/data/loader.py
import logging
import re
import urllib.parse
from typing import TYPE_CHECKING

import pandas as pd
from sqlalchemy import create_engine, text

import config
from config import VALID_INSTRUMENTS, VALID_TIMEFRAMES

if TYPE_CHECKING:
    from sqlalchemy import create_engine as _create_engine

logger = logging.getLogger(__name__)

_TABLE_RE = re.compile(r'^[A-Z][A-Z0-9_]{0,63}$')


def validate_table_name(name: str) -> str:
    """Return *name* if it is a safe MySQL table identifier, else raise ValueError."""
    if not _TABLE_RE.match(name):
        raise ValueError(f"Invalid table name: {name!r}")
    return name


def get_db_engine() -> 'create_engine':  # noqa: F821
    """Create a SQLAlchemy engine with safe password encoding."""
    if not config.DB_CONFIG['password']:
        raise ValueError("DB_PASSWORD is not set in .env")
    safe_pass = urllib.parse.quote_plus(config.DB_CONFIG['password'])
    db_uri = (
        f"mysql+pymysql://{config.DB_CONFIG['user']}:{safe_pass}@"
        f"{config.DB_CONFIG['host']}:{config.DB_CONFIG['port']}/{config.DB_CONFIG['database']}"
    )
    return create_engine(db_uri, pool_pre_ping=True, pool_size=2, max_overflow=0)


class DataLoadError(Exception):
    """Raised on data loading errors."""
    pass


def fetch_ohlcv(instrument: str, timeframe: str) -> pd.DataFrame:
    """Fetch OHLCV from MySQL, coerce types, drop zero-volume / NaN rows."""
    instrument = instrument.upper()
    timeframe = timeframe.upper()

    if instrument not in VALID_INSTRUMENTS:
        raise ValueError(f"Unsupported instrument: {instrument}. Choices: {sorted(VALID_INSTRUMENTS)}")
    if timeframe not in VALID_TIMEFRAMES:
        raise ValueError(f"Unsupported timeframe: {timeframe}. Choices: {sorted(VALID_TIMEFRAMES)}")

    table_name = validate_table_name(f"{instrument}_{timeframe}")
    engine = get_db_engine()

    try:
        query = text(f"SELECT timestamp, Open, High, Low, Close, Volume FROM {table_name} ORDER BY timestamp ASC")
        with engine.connect() as conn:
            df = pd.read_sql(query, conn)

        numeric_cols = ['Open', 'High', 'Low', 'Close', 'Volume']
        for col in numeric_cols:
            df[col] = pd.to_numeric(df[col], errors='coerce')

        df = df[df['Volume'] > 0].reset_index(drop=True)
        return df.dropna().reset_index(drop=True)

    except Exception as exc:
        logger.error("Data load error %s: %s", table_name, exc)
        raise DataLoadError(f"Cannot load data for {table_name}: {exc}") from exc