#!/usr/bin/env python3
"""
yfinance Cache with SQLite - Persistent until disabled

Caches OHLCV data fetched from yfinance to avoid redundant API calls
when running multiple screener filters within short time periods.

Features:
- SQLite-based persistent cache
- Cache active as long as checkbox is enabled (no expiry)
- Manual clear to fetch fresh data
- Enable/disable toggle
- Per-ticker caching (not per batch)
- Thread-safe
- Clear cache function
- Cache statistics
"""

import sqlite3
import os
import pickle
import time
import threading
import pandas as pd
from typing import Optional, Dict, List
from datetime import datetime

# Cache configuration
CACHE_ENABLED = True  # Can be toggled - cache persists while enabled
CACHE_DB_PATH = os.path.join(os.path.dirname(__file__), "yfinance_cache.db")

# Thread lock for thread safety
_lock = threading.Lock()

# Database initialized flag
_db_initialized = False


def _ensure_db():
    """Ensure database table exists (call once)"""
    global _db_initialized
    if _db_initialized:
        return
    
    with _lock:
        if _db_initialized:
            return
        
        try:
            os.makedirs(os.path.dirname(CACHE_DB_PATH) or '.', exist_ok=True)
            conn = sqlite3.connect(CACHE_DB_PATH, timeout=30)
            conn.execute("""
                CREATE TABLE IF NOT EXISTS yf_cache (
                    ticker TEXT NOT NULL,
                    interval TEXT NOT NULL DEFAULT '1d',
                    period TEXT NOT NULL DEFAULT '2y',
                    data BLOB NOT NULL,
                    timestamp REAL NOT NULL,
                    PRIMARY KEY (ticker, interval, period)
                )
            """)
            conn.execute("""
                CREATE INDEX IF NOT EXISTS idx_yf_cache_timestamp 
                ON yf_cache(timestamp)
            """)
            conn.commit()
            conn.close()
            _db_initialized = True
        except Exception:
            pass


# Initialize database on module load
_ensure_db()


# ============================================================================
# CACHE CONFIGURATION (Can be changed at runtime)
# ============================================================================

def set_cache_enabled(enabled: bool):
    """Enable or disable caching"""
    global CACHE_ENABLED
    CACHE_ENABLED = enabled
    status = "ENABLED" if enabled else "DISABLED"
    print(f"[CACHE] Cache {status} - will {'use' if enabled else 'skip'} cache on next fetch")


def is_cache_enabled() -> bool:
    """Check if cache is enabled"""
    return CACHE_ENABLED


def get_cache_config() -> Dict:
    """Get current cache configuration"""
    return {
        'enabled': CACHE_ENABLED
    }


def get_cached_ticker(ticker: str, interval: str = '1d', period: str = '2y') -> Optional[pd.DataFrame]:
    """
    Get cached OHLCV data for a ticker (persistent while cache enabled)
    
    Args:
        ticker: Stock ticker symbol
        interval: Data interval ('1d', '5m', '15m', etc.)
        period: Data period ('2y', '1mo', '5d', etc.)
    
    Returns:
        DataFrame if cached, None if not found or disabled
    """
    # Skip if cache disabled
    if not CACHE_ENABLED:
        return None
    
    try:
        conn = sqlite3.connect(CACHE_DB_PATH, timeout=10)
        conn.execute("PRAGMA journal_mode=WAL")
        with _lock:
            cursor = conn.execute(
                "SELECT data FROM yf_cache WHERE ticker = ? AND interval = ? AND period = ?",
                (ticker, interval, period)
            )
            row = cursor.fetchone()
        conn.close()
        
        if row is None:
            return None
        
        # Deserialize and return
        data_blob = row[0]
        df = pickle.loads(data_blob)
        return df
        
    except Exception as e:
        print(f"[CACHE] Error reading cache for {ticker}: {e}")
        return None


def cache_ticker_data(ticker: str, df: pd.DataFrame, interval: str = '1d', period: str = '2y'):
    """
    Cache OHLCV data for a ticker (only if cache enabled)
    
    Args:
        ticker: Stock ticker symbol
        df: DataFrame with OHLCV data
        interval: Data interval
        period: Data period
    """
    # Skip if cache disabled
    if not CACHE_ENABLED:
        return
    
    if df is None or df.empty:
        return
    
    try:
        data_blob = pickle.dumps(df)
        timestamp = time.time()
        
        conn = sqlite3.connect(CACHE_DB_PATH, timeout=10)
        conn.execute("PRAGMA journal_mode=WAL")
        with _lock:
            conn.execute(
                """INSERT OR REPLACE INTO yf_cache (ticker, interval, period, data, timestamp)
                   VALUES (?, ?, ?, ?, ?)""",
                (ticker, interval, period, data_blob, timestamp)
            )
            conn.commit()
        conn.close()
            
    except Exception as e:
        import traceback
        print(f"[CACHE] ❌ Error caching data for {ticker}: {e}")
        traceback.print_exc()


def clear_cache():
    """Clear all cached data"""
    try:
        conn = sqlite3.connect(CACHE_DB_PATH, timeout=10)
        with _lock:
            cursor = conn.execute("DELETE FROM yf_cache")
            deleted = cursor.rowcount
            conn.commit()
        conn.close()
        print(f"[CACHE] Cleared {deleted} cached entries")
        return deleted
    except Exception as e:
        print(f"[CACHE] Error clearing cache: {e}")
        return 0


def clean_expired():
    """Remove expired entries from cache"""
    try:
        cutoff = time.time() - CACHE_TTL_SECONDS
        conn = _get_connection()
        with _lock:
            cursor = conn.execute("DELETE FROM yf_cache WHERE timestamp < ?", (cutoff,))
            deleted = cursor.rowcount
            conn.commit()
        if deleted > 0:
            print(f"[CACHE] Cleaned {deleted} expired entries")
        return deleted
    except Exception as e:
        print(f"[CACHE] Error cleaning expired entries: {e}")
        return 0


def get_cache_stats() -> Dict:
    """Get cache statistics"""
    try:
        conn = sqlite3.connect(CACHE_DB_PATH, timeout=10)
        cursor = conn.execute("SELECT COUNT(*) FROM yf_cache")
        total_entries = cursor.fetchone()[0]
        conn.close()
        
        return {
            'enabled': CACHE_ENABLED,
            'total_entries': total_entries,
            'note': 'Cache persists until manually cleared or disabled'
        }
    except Exception as e:
        print(f"[CACHE] Error getting stats: {e}")
        return {'enabled': CACHE_ENABLED, 'total_entries': 0}


def fetch_batch_with_cache(
    tickers: List[str],
    fetch_func,
    interval: str = '1d',
    period: str = '2y',
    progress_callback=None
) -> Dict[str, pd.DataFrame]:
    """
    Fetch batch of tickers with automatic caching
    
    Args:
        tickers: List of ticker symbols
        fetch_func: Function to fetch data from yfinance (ticker, interval, period) -> DataFrame
        interval: Data interval
        period: Data period
        progress_callback: Optional callback(current, total, symbol, success)
    
    Returns:
        Dictionary mapping ticker -> DataFrame
    """
    results = {}
    total = len(tickers)
    to_fetch = []
    
    # Step 1: Check cache for each ticker
    for i, ticker in enumerate(tickers):
        cached = get_cached_ticker(ticker, interval, period)
        if cached is not None:
            results[ticker] = cached
            if progress_callback:
                progress_callback(i + 1, total, ticker, True)
        else:
            to_fetch.append((i, ticker))
    
    if to_fetch:
        cache_action = len(results)
        if cache_action > 0:
            print(f"[CACHE] {cache_action}/{total} tickers from cache, fetching {len(to_fetch)}...")
        
        # Step 2: Fetch missing tickers
        for i, ticker in to_fetch:
            try:
                df = fetch_func(ticker, interval, period)
                if df is not None and not df.empty:
                    results[ticker] = df
                    cache_ticker_data(ticker, df, interval, period)
                    if progress_callback:
                        progress_callback(i + 1, total, ticker, True)
                else:
                    if progress_callback:
                        progress_callback(i + 1, total, ticker, False)
            except Exception as e:
                print(f"[ERROR] {ticker}: {e}")
                if progress_callback:
                    progress_callback(i + 1, total, ticker, False)
    
    return results


if __name__ == "__main__":
    # Test cache functionality
    print("=" * 60)
    print("yfinance Cache Test")
    print("=" * 60)
    
    # Show stats
    stats = get_cache_stats()
    print(f"\nCache Stats: {stats}")
    
    # Clear cache
    print("\nClearing cache...")
    clear_cache()
    
    stats = get_cache_stats()
    print(f"After clear: {stats}")
    
    print("\n✅ Cache module working correctly!")
