backend/services/database.py

"""
BIST Predictor — SQLite Veritabanı Servisi
Tahminler, günlük fiyatlar ve güven puanları için CRUD operasyonları.
"""

import sqlite3
import json
import logging
from datetime import datetime, date
from pathlib import Path
from typing import Optional

from config import DB_PATH

logger = logging.getLogger(__name__)


def get_connection() -> sqlite3.Connection:
    """SQLite bağlantısı oluştur."""
    conn = sqlite3.connect(str(DB_PATH))
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute("PRAGMA foreign_keys=ON")
    return conn


def init_db():
    """Veritabanı tablolarını oluştur."""
    conn = get_connection()
    try:
        conn.executescript("""
            CREATE TABLE IF NOT EXISTS daily_prices (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                symbol TEXT NOT NULL,
                date TEXT NOT NULL,
                open REAL,
                high REAL,
                low REAL,
                close REAL,
                volume INTEGER,
                created_at TEXT DEFAULT (datetime('now')),
                UNIQUE(symbol, date)
            );

            CREATE TABLE IF NOT EXISTS predictions (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                symbol TEXT NOT NULL,
                prediction_date TEXT NOT NULL,
                target_date TEXT NOT NULL,
                horizon INTEGER NOT NULL,
                predicted_close REAL NOT NULL,
                actual_close REAL,
                quantile_p10 REAL,
                quantile_p20 REAL,
                quantile_p30 REAL,
                quantile_p40 REAL,
                quantile_p50 REAL,
                quantile_p60 REAL,
                quantile_p70 REAL,
                quantile_p80 REAL,
                quantile_p90 REAL,
                created_at TEXT DEFAULT (datetime('now')),
                UNIQUE(symbol, prediction_date, target_date, horizon)
            );

            CREATE TABLE IF NOT EXISTS confidence_scores (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                symbol TEXT NOT NULL,
                date TEXT NOT NULL,
                horizon INTEGER NOT NULL,
                direction_accuracy REAL DEFAULT 0,
                mape_score REAL DEFAULT 0,
                quantile_coverage REAL DEFAULT 0,
                volatility_match REAL DEFAULT 0,
                total_score REAL DEFAULT 0,
                details TEXT,
                created_at TEXT DEFAULT (datetime('now')),
                UNIQUE(symbol, date, horizon)
            );

            CREATE TABLE IF NOT EXISTS system_log (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                event_type TEXT NOT NULL,
                message TEXT,
                created_at TEXT DEFAULT (datetime('now'))
            );

            CREATE INDEX IF NOT EXISTS idx_prices_symbol_date ON daily_prices(symbol, date);
            CREATE INDEX IF NOT EXISTS idx_predictions_symbol ON predictions(symbol, prediction_date);
            CREATE INDEX IF NOT EXISTS idx_confidence_symbol ON confidence_scores(symbol, date);
        """)
        conn.commit()
        logger.info("Veritabanı tabloları oluşturuldu.")
    finally:
        conn.close()


# ─── Günlük Fiyat İşlemleri ─────────────────────────────────────────────────────

def save_daily_prices(symbol: str, prices_df):
    """DataFrame formatında günlük fiyat verilerini kaydet."""
    conn = get_connection()
    try:
        for idx, row in prices_df.iterrows():
            date_str = idx.strftime("%Y-%m-%d") if hasattr(idx, 'strftime') else str(idx)[:10]
            conn.execute("""
                INSERT OR REPLACE INTO daily_prices (symbol, date, open, high, low, close, volume)
                VALUES (?, ?, ?, ?, ?, ?, ?)
            """, (
                symbol, date_str,
                float(row.get("Open", 0)),
                float(row.get("High", 0)),
                float(row.get("Low", 0)),
                float(row.get("Close", 0)),
                int(row.get("Volume", 0)),
            ))
        conn.commit()
        logger.info(f"{symbol}: {len(prices_df)} günlük fiyat kaydedildi.")
    finally:
        conn.close()


def get_daily_prices(symbol: str, limit: int = 500) -> list[dict]:
    """Belirli hisse için günlük fiyatları getir."""
    conn = get_connection()
    try:
        rows = conn.execute("""
            SELECT * FROM daily_prices
            WHERE symbol = ?
            ORDER BY date DESC
            LIMIT ?
        """, (symbol, limit)).fetchall()
        return [dict(r) for r in rows]
    finally:
        conn.close()


def get_closing_prices_array(symbol: str, limit: int = 500) -> list[float]:
    """Belirli hisse için kapanış fiyatlarını kronolojik sırada dizi olarak getir."""
    conn = get_connection()
    try:
        rows = conn.execute("""
            SELECT close FROM daily_prices
            WHERE symbol = ?
            ORDER BY date ASC
            LIMIT ?
        """, (symbol, limit)).fetchall()
        return [float(r["close"]) for r in rows if r["close"] is not None]
    finally:
        conn.close()


def get_latest_price(symbol: str) -> Optional[dict]:
    """En son fiyat verisini getir."""
    conn = get_connection()
    try:
        row = conn.execute("""
            SELECT * FROM daily_prices
            WHERE symbol = ?
            ORDER BY date DESC
            LIMIT 1
        """, (symbol,)).fetchone()
        return dict(row) if row else None
    finally:
        conn.close()


# ─── Tahmin İşlemleri ────────────────────────────────────────────────────────────

def save_prediction(symbol: str, prediction_date: str, target_date: str,
                    horizon: int, predicted_close: float, quantiles: dict):
    """Tek bir tahmin kaydı kaydet."""
    conn = get_connection()
    try:
        conn.execute("""
            INSERT OR REPLACE INTO predictions
            (symbol, prediction_date, target_date, horizon, predicted_close,
             quantile_p10, quantile_p20, quantile_p30, quantile_p40, quantile_p50,
             quantile_p60, quantile_p70, quantile_p80, quantile_p90)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """, (
            symbol, prediction_date, target_date, horizon, predicted_close,
            quantiles.get("p10"), quantiles.get("p20"), quantiles.get("p30"),
            quantiles.get("p40"), quantiles.get("p50"), quantiles.get("p60"),
            quantiles.get("p70"), quantiles.get("p80"), quantiles.get("p90"),
        ))
        conn.commit()
    finally:
        conn.close()


def save_predictions_batch(predictions: list[dict]):
    """Toplu tahmin kaydı kaydet."""
    conn = get_connection()
    try:
        for p in predictions:
            q = p.get("quantiles", {})
            conn.execute("""
                INSERT OR REPLACE INTO predictions
                (symbol, prediction_date, target_date, horizon, predicted_close,
                 quantile_p10, quantile_p20, quantile_p30, quantile_p40, quantile_p50,
                 quantile_p60, quantile_p70, quantile_p80, quantile_p90)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """, (
                p["symbol"], p["prediction_date"], p["target_date"],
                p["horizon"], p["predicted_close"],
                q.get("p10"), q.get("p20"), q.get("p30"),
                q.get("p40"), q.get("p50"), q.get("p60"),
                q.get("p70"), q.get("p80"), q.get("p90"),
            ))
        conn.commit()
        logger.info(f"{len(predictions)} tahmin kaydedildi.")
    finally:
        conn.close()


def update_actual_price(symbol: str, target_date: str, actual_close: float):
    """Tahmine gerçek fiyatı ekle."""
    conn = get_connection()
    try:
        conn.execute("""
            UPDATE predictions
            SET actual_close = ?
            WHERE symbol = ? AND target_date = ?
        """, (actual_close, symbol, target_date))
        conn.commit()
    finally:
        conn.close()


def get_predictions(symbol: str, horizon: Optional[int] = None,
                    limit: int = 100) -> list[dict]:
    """Belirli hisse için tahminleri getir."""
    conn = get_connection()
    try:
        if horizon:
            rows = conn.execute("""
                SELECT * FROM predictions
                WHERE symbol = ? AND horizon = ?
                ORDER BY prediction_date DESC
                LIMIT ?
            """, (symbol, horizon, limit)).fetchall()
        else:
            rows = conn.execute("""
                SELECT * FROM predictions
                WHERE symbol = ?
                ORDER BY prediction_date DESC
                LIMIT ?
            """, (symbol, limit)).fetchall()
        return [dict(r) for r in rows]
    finally:
        conn.close()


def get_predictions_with_actuals(symbol: str, horizon: int = 10) -> list[dict]:
    """Gerçek fiyatı olan tahminleri getir (karşılaştırma için)."""
    conn = get_connection()
    try:
        rows = conn.execute("""
            SELECT * FROM predictions
            WHERE symbol = ? AND horizon = ? AND actual_close IS NOT NULL
            ORDER BY target_date DESC
            LIMIT 100
        """, (symbol, horizon)).fetchall()
        return [dict(r) for r in rows]
    finally:
        conn.close()


def get_pending_comparisons(target_date: str) -> list[dict]:
    """Gerçek fiyatı bekleyen tahminleri getir."""
    conn = get_connection()
    try:
        rows = conn.execute("""
            SELECT * FROM predictions
            WHERE target_date <= ? AND actual_close IS NULL
            ORDER BY target_date ASC
        """, (target_date,)).fetchall()
        return [dict(r) for r in rows]
    finally:
        conn.close()


# ─── Güven Puanı İşlemleri ───────────────────────────────────────────────────────

def save_confidence_score(symbol: str, score_date: str, horizon: int,
                          scores: dict):
    """Güven puanı kaydet."""
    conn = get_connection()
    try:
        conn.execute("""
            INSERT OR REPLACE INTO confidence_scores
            (symbol, date, horizon, direction_accuracy, mape_score,
             quantile_coverage, volatility_match, total_score, details)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        """, (
            symbol, score_date, horizon,
            scores.get("direction_accuracy", 0),
            scores.get("mape_score", 0),
            scores.get("quantile_coverage", 0),
            scores.get("volatility_match", 0),
            scores.get("total_score", 0),
            json.dumps(scores.get("details", {})),
        ))
        conn.commit()
    finally:
        conn.close()


def get_confidence_score(symbol: str, horizon: int = 10) -> Optional[dict]:
    """En son güven puanını getir."""
    conn = get_connection()
    try:
        row = conn.execute("""
            SELECT * FROM confidence_scores
            WHERE symbol = ? AND horizon = ?
            ORDER BY date DESC
            LIMIT 1
        """, (symbol, horizon)).fetchone()
        return dict(row) if row else None
    finally:
        conn.close()


def get_all_confidence_scores(horizon: int = 10) -> list[dict]:
    """Tüm hisselerin en son güven puanlarını getir."""
    conn = get_connection()
    try:
        rows = conn.execute("""
            SELECT cs.*, dp.close as latest_price
            FROM confidence_scores cs
            LEFT JOIN (
                SELECT symbol, close, MAX(date) as max_date
                FROM daily_prices
                GROUP BY symbol
            ) dp ON cs.symbol = dp.symbol
            WHERE cs.horizon = ?
            AND cs.date = (
                SELECT MAX(date) FROM confidence_scores
                WHERE symbol = cs.symbol AND horizon = cs.horizon
            )
            ORDER BY cs.total_score DESC
        """, (horizon,)).fetchall()
        return [dict(r) for r in rows]
    finally:
        conn.close()


def get_confidence_history(symbol: str, horizon: int = 10,
                           limit: int = 30) -> list[dict]:
    """Güven puanı tarihçesi."""
    conn = get_connection()
    try:
        rows = conn.execute("""
            SELECT * FROM confidence_scores
            WHERE symbol = ? AND horizon = ?
            ORDER BY date DESC
            LIMIT ?
        """, (symbol, horizon, limit)).fetchall()
        return [dict(r) for r in rows]
    finally:
        conn.close()


# ─── Sistem Log ──────────────────────────────────────────────────────────────────

def log_event(event_type: str, message: str):
    """Sistem olayı kaydet."""
    conn = get_connection()
    try:
        conn.execute(
            "INSERT INTO system_log (event_type, message) VALUES (?, ?)",
            (event_type, message)
        )
        conn.commit()
    finally:
        conn.close()


def get_system_logs(limit: int = 50) -> list[dict]:
    """Son sistem loglarını getir."""
    conn = get_connection()
    try:
        rows = conn.execute("""
            SELECT * FROM system_log ORDER BY created_at DESC LIMIT ?
        """, (limit,)).fetchall()
        return [dict(r) for r in rows]
    finally:
        conn.close()


# ─── Dashboard Özet Verileri ─────────────────────────────────────────────────────

def get_dashboard_summary(horizon: int = 10) -> dict:
    """Dashboard için özet istatistikler."""
    conn = get_connection()
    try:
        # Toplam tahmin sayısı
        total_predictions = conn.execute(
            "SELECT COUNT(*) as cnt FROM predictions WHERE horizon = ?",
            (horizon,)
        ).fetchone()["cnt"]

        # Doğrulanmış tahmin sayısı
        verified = conn.execute(
            "SELECT COUNT(*) as cnt FROM predictions WHERE horizon = ? AND actual_close IS NOT NULL",
            (horizon,)
        ).fetchone()["cnt"]

        # Ortalama güven puanı
        avg_score = conn.execute("""
            SELECT AVG(total_score) as avg_score
            FROM confidence_scores cs
            WHERE horizon = ?
            AND date = (SELECT MAX(date) FROM confidence_scores WHERE symbol = cs.symbol AND horizon = cs.horizon)
        """, (horizon,)).fetchone()["avg_score"]

        # En yüksek güvenli hisse
        top_stock = conn.execute("""
            SELECT symbol, total_score FROM confidence_scores
            WHERE horizon = ?
            ORDER BY total_score DESC, date DESC
            LIMIT 1
        """, (horizon,)).fetchone()

        # Takip edilen hisse sayısı
        tracked_count = conn.execute(
            "SELECT COUNT(DISTINCT symbol) as cnt FROM daily_prices"
        ).fetchone()["cnt"]

        return {
            "total_predictions": total_predictions,
            "verified_predictions": verified,
            "average_confidence": round(avg_score, 1) if avg_score else 0,
            "top_stock": dict(top_stock) if top_stock else None,
            "tracked_stocks": tracked_count,
        }
    finally:
        conn.close()