from typing import Optional
from db.connection import get_connection
from data.loader import _validate_table_name


TABLES_QUERY = "SHOW TABLES"
INSTRUMENTS_QUERY = "SELECT id, name, type FROM instruments ORDER BY name"
INSTRUMENTS_BY_TYPE_QUERY = "SELECT id, name FROM instruments WHERE type = %s ORDER BY name"
TABLE_EXISTS_QUERY = "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = %s AND table_name = %s"


def load_candles(ticker: str, timeframe: str, limit: Optional[int] = None) -> list[dict]:
    table = _validate_table_name(ticker, timeframe)
    query = "SELECT timestamp, `Date`, `Time`, Open, High, Low, Close, Volume FROM " + table + " ORDER BY timestamp"
    params = None
    if limit:
        query += " LIMIT %s"
        params = (int(limit),)
    with get_connection() as conn:
        cursor = conn.cursor(dictionary=True)
        cursor.execute(query, params)
        rows = cursor.fetchall()
        cursor.close()
        return rows


def get_instruments(instrument_type: Optional[str] = None) -> list[dict]:
    with get_connection() as conn:
        cursor = conn.cursor(dictionary=True)
        if instrument_type:
            cursor.execute(INSTRUMENTS_BY_TYPE_QUERY, (instrument_type,))
        else:
            cursor.execute(INSTRUMENTS_QUERY)
        rows = cursor.fetchall()
        cursor.close()
        return rows