#!/usr/bin/env python3
"""
Quick database statistics checker
Shows oldest date, newest date, and count for each interval
"""

import asyncio
import asyncpg
import os
from datetime import datetime

async def check_database_stats():
    # Database connection (uses same env vars as your app)
    conn = await asyncpg.connect(
        host=os.getenv('DB_HOST', 'localhost'),
        port=int(os.getenv('DB_PORT', 5432)),
        database=os.getenv('DB_NAME', 'btc_data'),
        user=os.getenv('DB_USER', 'btc_bot'),
        password=os.getenv('DB_PASSWORD', '')
    )
    
    try:
        print("=" * 70)
        print("DATABASE STATISTICS")
        print("=" * 70)
        print()
        
        # Check for each interval
        intervals = ['1m', '3m', '5m', '15m', '30m', '37m', '1h', '2h', '4h', '8h', '12h', '1d']
        
        for interval in intervals:
            stats = await conn.fetchrow("""
                SELECT 
                    COUNT(*) as count,
                    MIN(time) as oldest,
                    MAX(time) as newest
                FROM candles 
                WHERE symbol = 'BTC' AND interval = $1
            """, interval)
            
            if stats['count'] > 0:
                oldest = stats['oldest'].strftime('%Y-%m-%d %H:%M') if stats['oldest'] else 'N/A'
                newest = stats['newest'].strftime('%Y-%m-%d %H:%M') if stats['newest'] else 'N/A'
                count = stats['count']
                
                # Calculate days of data
                if stats['oldest'] and stats['newest']:
                    days = (stats['newest'] - stats['oldest']).days
                    print(f"{interval:6} | {count:>8,} candles | {days:>4} days | {oldest} to {newest}")
        
        print()
        print("=" * 70)
        
        # Check indicators
        print("\nINDICATORS AVAILABLE:")
        indicators = await conn.fetch("""
            SELECT DISTINCT indicator_name, interval, COUNT(*) as count
            FROM indicators
            WHERE symbol = 'BTC'
            GROUP BY indicator_name, interval
            ORDER BY interval, indicator_name
        """)
        
        if indicators:
            for ind in indicators:
                print(f"  {ind['indicator_name']:10} on {ind['interval']:6} | {ind['count']:>8,} values")
        else:
            print("  No indicators found in database")
        
        print()
        print("=" * 70)
        
        # Check 1m specifically with more detail
        print("\n1-MINUTE DATA DETAIL:")
        one_min_stats = await conn.fetchrow("""
            SELECT 
                COUNT(*) as count,
                MIN(time) as oldest,
                MAX(time) as newest,
                COUNT(*) FILTER (WHERE time > NOW() - INTERVAL '24 hours') as last_24h
            FROM candles 
            WHERE symbol = 'BTC' AND interval = '1m'
        """)
        
        if one_min_stats['count'] > 0:
            total_days = (one_min_stats['newest'] - one_min_stats['oldest']).days
            expected_candles = total_days * 24 * 60  # 1 candle per minute
            actual_candles = one_min_stats['count']
            coverage = (actual_candles / expected_candles) * 100 if expected_candles > 0 else 0
            
            print(f"  Total candles:     {actual_candles:,}")
            print(f"  Date range:        {one_min_stats['oldest'].strftime('%Y-%m-%d')} to {one_min_stats['newest'].strftime('%Y-%m-%d')}")
            print(f"  Total days:        {total_days}")
            print(f"  Expected candles:  {expected_candles:,} (if complete)")
            print(f"  Coverage:          {coverage:.1f}%")
            print(f"  Last 24 hours:     {one_min_stats['last_24h']:,} candles")
        else:
            print("  No 1m data found")
        
        print()
        print("=" * 70)
        
    finally:
        await conn.close()

if __name__ == "__main__":
    asyncio.run(check_database_stats())