#!/usr/bin/env python3
"""
Analysis Database Inspector
Check what indicators are actually populated
"""

import sqlite3
import json


def load_config():
    with open("config.json", "r") as f:
        return json.load(f)


def inspect_database(db_path):
    """Inspect analysis database schema and data"""
    print(f"\n📊 Inspecting: {db_path}")
    print("=" * 70)
    
    try:
        conn = sqlite3.connect(f"file:{db_path}?mode=ro", uri=True)
        cursor = conn.cursor()
        
        # Get table schema
        cursor.execute("PRAGMA table_info(analysis)")
        columns = cursor.fetchall()
        
        print("\n📋 TABLE SCHEMA:")
        print(f"{'Column Name':<20} {'Type':<15} {'Not Null':<10}")
        print("-" * 50)
        for col in columns:
            print(f"{col[1]:<20} {col[2]:<15} {'YES' if col[3] else 'NO':<10}")
        
        # Get row count
        cursor.execute("SELECT COUNT(*) FROM analysis")
        total_rows = cursor.fetchone()[0]
        print(f"\n📊 Total rows: {total_rows}")
        
        # Check data availability per timeframe
        cursor.execute("SELECT DISTINCT timeframe FROM analysis ORDER BY timeframe")
        timeframes = [row[0] for row in cursor.fetchall()]
        
        print("\n⏱️  DATA BY TIMEFRAME:")
        for tf in timeframes:
            cursor.execute(f"SELECT COUNT(*) FROM analysis WHERE timeframe = ?", (tf,))
            count = cursor.fetchone()[0]
            print(f"  {tf}: {count} rows")
        
        # Check for NULL values in key indicators
        print("\n🔍 NULL VALUE CHECK (latest 10 rows per timeframe):")
        
        indicator_cols = [
            'ema_9', 'ema_21', 'sma_50', 'sma_200',
            'rsi_14', 'macd', 'macd_signal', 'macd_hist',
            'bb_upper', 'bb_middle', 'bb_lower', 'bb_squeeze',
            'volume_ma_20'
        ]
        
        for tf in timeframes:
            print(f"\n  Timeframe: {tf}")
            
            # Get latest row
            cursor.execute(f"""
                SELECT * FROM analysis 
                WHERE timeframe = ? 
                ORDER BY timestamp DESC 
                LIMIT 1
            """, (tf,))
            
            row = cursor.fetchone()
            col_names = [desc[0] for desc in cursor.description]
            
            if row:
                row_dict = dict(zip(col_names, row))
                
                null_indicators = []
                present_indicators = []
                
                for ind in indicator_cols:
                    if ind in row_dict:
                        if row_dict[ind] is None:
                            null_indicators.append(ind)
                        else:
                            present_indicators.append(ind)
                    else:
                        null_indicators.append(f"{ind} (MISSING COLUMN)")
                
                if present_indicators:
                    print(f"    ✓ Present: {', '.join(present_indicators[:5])}")
                    if len(present_indicators) > 5:
                        print(f"               {', '.join(present_indicators[5:])}")
                
                if null_indicators:
                    print(f"    ❌ NULL/Missing: {', '.join(null_indicators)}")
                
                # Show sample values
                print(f"\n    Sample values from latest row:")
                print(f"      Timestamp: {row_dict.get('timestamp')}")
                for ind in ['ema_9', 'ema_21', 'rsi_14', 'bb_upper']:
                    if ind in row_dict:
                        val = row_dict[ind]
                        if val is not None:
                            print(f"      {ind}: {val}")
                        else:
                            print(f"      {ind}: NULL")
            else:
                print(f"    ❌ No data found")
        
        # Check if buy_volume exists in candles
        print("\n\n📊 Checking candles table for buy_volume...")
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='candles'")
        if cursor.fetchone():
            cursor.execute("PRAGMA table_info(candles)")
            candles_cols = [col[1] for col in cursor.fetchall()]
            
            if 'buy_volume' in candles_cols:
                print("  ✓ buy_volume column exists in candles table")
                
                # Check if it has data
                cursor.execute("SELECT COUNT(*) FROM candles WHERE buy_volume IS NOT NULL")
                count = cursor.fetchone()[0]
                print(f"  ✓ {count} rows with buy_volume data")
            else:
                print("  ❌ buy_volume column MISSING from candles table")
                print("     Available columns:", ', '.join(candles_cols))
        
        conn.close()
        
    except sqlite3.OperationalError as e:
        print(f"  ❌ Database error: {e}")
    except Exception as e:
        print(f"  ❌ Error: {e}")


def main():
    config = load_config()
    
    print("🔍 ANALYSIS DATABASE INSPECTOR")
    print("=" * 70)
    
    inspect_database(config["analysis_db"])
    
    print("\n\n💡 NEXT STEPS:")
    print("=" * 70)
    print("If indicators are missing:")
    print("  1. Check your analysis pipeline is running")
    print("  2. Verify the analysis script calculates these indicators:")
    print("     - rsi_14, bb_upper, bb_lower, bb_middle, bb_squeeze")
    print("  3. Re-run analysis on existing candle data")
    print("\nIf buy_volume is missing:")
    print("  1. Update your candles table schema")
    print("  2. Modify your data ingestion to capture buy_volume")
    print("  3. Or set buy_volume = volume/2 as approximation")


if __name__ == "__main__":
    main()