"""Database repository for scanner — encapsulates all SQL queries."""

from __future__ import annotations

import re
from datetime import datetime
from typing import Dict, List, Optional
from zoneinfo import ZoneInfo

from loguru import logger

_MS = ZoneInfo("Europe/Moscow")

_VALID_TICKER_RE = re.compile(r"^[A-Z][A-Z0-9_]{0,19}$")
_VALID_TF = {"D1", "H1", "H4", "M5", "M15", "M30"}


def _validate_ticker(ticker: str) -> str:
    safe = ticker.upper().strip()
    if not _VALID_TICKER_RE.match(safe):
        raise ValueError(f"Invalid ticker: {ticker}")
    return safe


class InstrumentRepository:
    def __init__(self):
        self._db = None

    @property
    def db(self):
        if self._db is None:
            from db import get_db_manager
            self._db = get_db_manager()
        return self._db

    def get_all_instruments(self) -> List[str]:
        from sqlalchemy import text

        try:
            with self.db.engine.connect() as conn:
                result = conn.execute(text("SHOW TABLES"))
                tables = [row[0] for row in result.fetchall()]
            
            instruments = set()
            for table in tables:
                parts = table.upper().split("_")
                if len(parts) >= 2:
                    tf = parts[-1]
                    if tf in _VALID_TF:
                        ticker = "_".join(parts[:-1])
                        instruments.add(ticker)
            
            instruments = sorted(list(instruments))
            logger.debug(f"Найдено {len(instruments)} инструментов: {instruments}")
            return instruments
        except Exception as e:
            logger.error(f"Ошибка получения инструментов: {e}")
            return []

    def get_latest_timestamp(self, ticker: str) -> Optional[int]:
        from sqlalchemy import text

        safe = _validate_ticker(ticker)
        table_name = f"{safe}_H1"

        try:
            with self.db.engine.connect() as conn:
                result = conn.execute(
                    text(f"SELECT MAX(timestamp) as max_ts FROM {table_name}")
                )
                row = result.fetchone()
                ts = row[0] if row else None
                return int(ts) if ts is not None else None
        except Exception as e:
            logger.debug(f"Error getting latest timestamp for {ticker}: {e}")
            return None

    def get_latest_price(self, ticker: str) -> Optional[float]:
        from sqlalchemy import text

        safe = _validate_ticker(ticker)
        table_name = f"{safe}_H1"

        try:
            with self.db.engine.connect() as conn:
                result = conn.execute(
                    text(f"SELECT Close FROM {table_name} ORDER BY timestamp DESC LIMIT 1")
                )
                row = result.fetchone()
                return float(row[0]) if row else None
        except Exception as e:
            logger.debug(f"Error getting latest price for {ticker}: {e}")
            return None

    def get_latest_candle_state(self, ticker: str) -> Optional[Dict]:
        """Get latest timestamp AND close price for change detection.

        Returns:
            Dict with 'timestamp' and 'close' keys, or None on error
        """
        from sqlalchemy import text

        safe = _validate_ticker(ticker)
        table_name = f"{safe}_H1"

        try:
            with self.db.engine.connect() as conn:
                result = conn.execute(
                    text(f"SELECT timestamp, Close FROM {table_name} ORDER BY timestamp DESC LIMIT 1")
                )
                row = result.fetchone()
                if row and row[0] is not None:
                    return {"timestamp": int(row[0]), "close": float(row[1])}
                return None
        except Exception as e:
            logger.debug(f"Error getting latest candle state for {ticker}: {e}")
            return None