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"


# ── signal_log: журнал всех сигналов monitor.py (audit 2026-08-03) ──────────
# Создаётся автоматически при первом вызове insert_signal_log.
SIGNAL_LOG_CREATE_SQL = """
CREATE TABLE IF NOT EXISTS signal_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ts INT NOT NULL,
    ticker VARCHAR(20) NOT NULL,
    `signal` VARCHAR(10) NOT NULL DEFAULT 'NEUTRAL',
    p_long DECIMAL(6,4) DEFAULT 0,
    p_short DECIMAL(6,4) DEFAULT 0,
    threshold_long DECIMAL(4,2) DEFAULT 0,
    threshold_short DECIMAL(4,2) DEFAULT 0,
    confidence DECIMAL(6,4) DEFAULT 0,
    flat_filter VARCHAR(50) DEFAULT NULL,
    close_price DECIMAL(20,8) DEFAULT 0,
    model_type VARCHAR(20) DEFAULT 'moe_v12',
    INDEX idx_ticker_ts (ticker, ts),
    INDEX idx_ts (ts)
) CHARACTER SET utf8mb4;
"""


def insert_signal_log(ts: int, ticker: str, signal: str,
                       p_long: float = 0, p_short: float = 0,
                       threshold_long: float = 0, threshold_short: float = 0,
                       confidence: float = 0, flat_filter: str = None,
                       close_price: float = 0, model_type: str = 'moe_v12') -> None:
    """Записывает сигнал в signal_log таблицу (audit 2026-08-03).

    Используется для post-hoc анализа качества модели и debugging.
    Таблица создаётся автоматически при первом вызове.
    """
    with get_connection() as conn:
        cur = conn.cursor()
        # Создаём таблицу если её нет (idempotent)
        cur.execute(SIGNAL_LOG_CREATE_SQL)
        cur.execute(
            "INSERT INTO signal_log "
            "(ts, ticker, `signal`, p_long, p_short, threshold_long, threshold_short, "
            " confidence, flat_filter, close_price, model_type) "
            "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
            (int(ts), ticker, signal,
             float(p_long), float(p_short),
             float(threshold_long), float(threshold_short),
             float(confidence), flat_filter,
             float(close_price), model_type)
        )
        conn.commit()
        cur.close()


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