import logging
import pandas as pd
import mysql.connector
from config import Config

logger = logging.getLogger(__name__)


class DataFetcher:
    def __init__(self):
        self.config = Config()

    def get_connection(self):
        return mysql.connector.connect(**self.config.DB_CONFIG)

    def fetch_ohlcv(self, instrument: str) -> pd.DataFrame:
        table = self.config.TABLES[instrument]
        query = f"""
            SELECT timestamp, Open, High, Low, Close, Volume
            FROM `{table}`
            ORDER BY timestamp ASC
        """
        conn = self.get_connection()
        try:
            df = pd.read_sql(query, conn)
        finally:
            conn.close()

        if df.empty:
            return df

        ts = pd.to_numeric(df['timestamp'], errors='coerce')
        if ts.max() > 1e12:
            ts = ts / 1000
        df['timestamp'] = pd.to_datetime(ts, unit='s')
        df.set_index('timestamp', inplace=True)

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

        df.sort_index(inplace=True)
        return df

    def fetch_all(self) -> dict:
        result = {}
        for instr in self.config.INSTRUMENTS:
            df = self.fetch_ohlcv(instr)
            logger.info("%s: %d candles, %s → %s", instr, len(df), df.index[0] if not df.empty else 'N/A', df.index[-1] if not df.empty else 'N/A')
            result[instr] = df
        return result