database_console.py

import streamlit as st
import pandas as pd
from database import DatabaseManager
from sqlalchemy import text
import plotly.express as px
import plotly.graph_objects as go
from datetime import datetime, timedelta

def database_console():
    """Database console interface for viewing and managing stock data"""
    st.title("📊 Database Console - Stock Trading Data")
    st.markdown("### SQLite Database Management")
    
    # Show database file location
    st.info("Database file: stock_trading.db (persistent storage)")
    
    # Initialize database
    try:
        if 'console_db' not in st.session_state:
            from database import DatabaseManager
            st.session_state.console_db = DatabaseManager()
        db = st.session_state.console_db
        st.success("Database connection established")
        
        # Show database file size if it exists
        import os
        if os.path.exists('stock_trading.db'):
            size = os.path.getsize('stock_trading.db')
            st.metric("Database Size", f"{size / 1024:.1f} KB")
            
    except Exception as e:
        st.error(f"Database connection failed: {str(e)}")
        return
    
    # Sidebar navigation
    st.sidebar.header("Database Navigation")
    section = st.sidebar.selectbox(
        "Select Section:",
        ["Overview", "Stock Data", "Technical Indicators", "Trading Signals", "Model Metrics", "SQL Query"]
    )
    
    if section == "Overview":
        show_database_overview(db)
    elif section == "Stock Data":
        show_stock_data(db)
    elif section == "Technical Indicators":
        show_technical_indicators(db)
    elif section == "Trading Signals":
        show_trading_signals(db)
    elif section == "Model Metrics":
        show_model_metrics(db)
    elif section == "SQL Query":
        show_sql_interface(db)

def show_database_overview(db):
    """Show database overview and statistics"""
    st.header("Database Overview")
    
    col1, col2, col3, col4 = st.columns(4)
    
    try:
        # Get table counts
        stock_count = db.session.execute(text("SELECT COUNT(*) FROM stock_data")).scalar()
        indicator_count = db.session.execute(text("SELECT COUNT(*) FROM technical_indicators")).scalar()
        signal_count = db.session.execute(text("SELECT COUNT(*) FROM trading_signals")).scalar()
        model_count = db.session.execute(text("SELECT COUNT(*) FROM model_metrics")).scalar()
        
        with col1:
            st.metric("Stock Records", stock_count)
        with col2:
            st.metric("Technical Indicators", indicator_count)
        with col3:
            st.metric("Trading Signals", signal_count)
        with col4:
            st.metric("Model Records", model_count)
            
    except Exception as e:
        st.warning(f"Error getting counts: {str(e)}")
        # Initialize tables if they don't exist
        if st.button("Initialize Database Tables"):
            initialize_database_tables(db)
    
    # Recent activity
    st.subheader("Recent Activity")
    try:
        recent_signals = pd.read_sql("""
            SELECT symbol, signal, confidence, live_price, created_at 
            FROM trading_signals 
            ORDER BY created_at DESC 
            LIMIT 10
        """, db.engine)
        
        if not recent_signals.empty:
            st.dataframe(recent_signals)
        else:
            st.info("No recent trading signals found")
            
    except Exception as e:
        st.info("No trading signals table found yet")

def show_stock_data(db):
    """Show stock OHLCV data"""
    st.header("Stock Data (OHLCV)")
    
    # Stock selection
    symbol = st.selectbox(
        "Select Stock Symbol:",
        ["RELIANCE.NS", "TCS.NS", "INFY.NS", "HDFCBANK.NS", "ICICIBANK.NS"]
    )
    
    try:
        # Get stock data
        query = """
            SELECT * FROM stock_data 
            WHERE symbol = :symbol 
            ORDER BY date DESC 
            LIMIT 100
        """
        stock_data = pd.read_sql(query, db.engine, params={"symbol": symbol})
        
        if not stock_data.empty:
            st.dataframe(stock_data)
            
            # Create price chart
            fig = go.Figure()
            fig.add_trace(go.Candlestick(
                x=stock_data['date'],
                open=stock_data['open_price'],
                high=stock_data['high_price'],
                low=stock_data['low_price'],
                close=stock_data['close_price'],
                name=symbol
            ))
            fig.update_layout(title=f"{symbol} - Price Chart", xaxis_title="Date", yaxis_title="Price (₹)")
            st.plotly_chart(fig, use_container_width=True)
            
        else:
            st.info(f"No stock data found for {symbol}")
            if st.button(f"Load {symbol} Data"):
                load_sample_data(db, symbol)
                
    except Exception as e:
        st.error(f"Error loading stock data: {str(e)}")

def show_technical_indicators(db):
    """Show technical indicators data"""
    st.header("Technical Indicators")
    
    symbol = st.selectbox(
        "Select Stock:",
        ["RELIANCE.NS", "TCS.NS", "INFY.NS", "HDFCBANK.NS", "ICICIBANK.NS"],
        key="tech_symbol"
    )
    
    try:
        query = """
            SELECT * FROM technical_indicators 
            WHERE symbol = :symbol 
            ORDER BY date DESC 
            LIMIT 50
        """
        tech_data = pd.read_sql(query, db.engine, params={"symbol": symbol})
        
        if not tech_data.empty:
            st.dataframe(tech_data)
            
            # Create indicators chart
            col1, col2 = st.columns(2)
            
            with col1:
                if 'rsi' in tech_data.columns:
                    fig_rsi = px.line(tech_data, x='date', y='rsi', title='RSI')
                    st.plotly_chart(fig_rsi, use_container_width=True)
            
            with col2:
                if 'macd' in tech_data.columns:
                    fig_macd = px.line(tech_data, x='date', y='macd', title='MACD')
                    st.plotly_chart(fig_macd, use_container_width=True)
                    
        else:
            st.info(f"No technical indicators found for {symbol}")
            
    except Exception as e:
        st.error(f"Error loading technical indicators: {str(e)}")

def show_trading_signals(db):
    """Show trading signals"""
    st.header("Trading Signals")
    
    try:
        # Get all signals
        signals_data = pd.read_sql("""
            SELECT * FROM trading_signals 
            ORDER BY signal_date DESC 
            LIMIT 100
        """, db.engine)
        
        if not signals_data.empty:
            st.dataframe(signals_data)
            
            # Signal distribution
            col1, col2 = st.columns(2)
            
            with col1:
                signal_counts = signals_data['signal'].value_counts()
                fig_pie = px.pie(values=signal_counts.values, names=signal_counts.index, 
                               title="Signal Distribution")
                st.plotly_chart(fig_pie, use_container_width=True)
            
            with col2:
                fig_conf = px.histogram(signals_data, x='confidence', title="Confidence Distribution")
                st.plotly_chart(fig_conf, use_container_width=True)
                
        else:
            st.info("No trading signals found")
            
    except Exception as e:
        st.error(f"Error loading trading signals: {str(e)}")

def show_model_metrics(db):
    """Show model performance metrics"""
    st.header("Model Performance Metrics")
    
    try:
        metrics_data = pd.read_sql("""
            SELECT * FROM model_metrics 
            ORDER BY training_date DESC
        """, db.engine)
        
        if not metrics_data.empty:
            st.dataframe(metrics_data)
            
            # Performance charts
            col1, col2 = st.columns(2)
            
            with col1:
                fig_acc = px.bar(metrics_data, x='symbol', y='accuracy', 
                               title="Model Accuracy by Symbol")
                st.plotly_chart(fig_acc, use_container_width=True)
            
            with col2:
                fig_prec = px.bar(metrics_data, x='symbol', y='precision_score', 
                                title="Model Precision by Symbol")
                st.plotly_chart(fig_prec, use_container_width=True)
                
        else:
            st.info("No model metrics found")
            
    except Exception as e:
        st.error(f"Error loading model metrics: {str(e)}")

def show_sql_interface(db):
    """SQL query interface"""
    st.header("SQL Query Interface")
    
    # Predefined queries
    st.subheader("Quick Queries")
    col1, col2, col3 = st.columns(3)
    
    with col1:
        if st.button("Show All Tables"):
            try:
                tables = db.engine.execute(text("SELECT name FROM sqlite_master WHERE type='table'")).fetchall()
                st.write("Tables:", [table[0] for table in tables])
            except Exception as e:
                st.error(f"Error: {str(e)}")
    
    with col2:
        if st.button("Latest Signals"):
            try:
                result = pd.read_sql("SELECT * FROM trading_signals ORDER BY created_at DESC LIMIT 5", db.engine)
                st.dataframe(result)
            except Exception as e:
                st.error(f"Error: {str(e)}")
    
    with col3:
        if st.button("Stock Summary"):
            try:
                result = pd.read_sql("""
                    SELECT symbol, COUNT(*) as records, 
                           MIN(date) as first_date, MAX(date) as last_date 
                    FROM stock_data 
                    GROUP BY symbol
                """, db.engine)
                st.dataframe(result)
            except Exception as e:
                st.error(f"Error: {str(e)}")
    
    # Custom SQL query
    st.subheader("Custom SQL Query")
    sql_query = st.text_area(
        "Enter SQL Query:",
        value="SELECT * FROM trading_signals LIMIT 10;",
        height=100
    )
    
    if st.button("Execute Query"):
        try:
            if sql_query.strip():
                result = pd.read_sql(sql_query, db.engine)
                st.dataframe(result)
                st.success(f"Query executed successfully. {len(result)} rows returned.")
        except Exception as e:
            st.error(f"Query error: {str(e)}")

def initialize_database_tables(db):
    """Initialize database tables with sample data"""
    try:
        from database import Base
        Base.metadata.create_all(db.engine)
        st.success("Database tables initialized successfully")
        st.rerun()
    except Exception as e:
        st.error(f"Error initializing tables: {str(e)}")

def load_sample_data(db, symbol):
    """Load sample data for testing"""
    try:
        import yfinance as yf
        from technical_indicators import TechnicalIndicators
        
        # Download stock data
        ticker = yf.Ticker(symbol)
        data = ticker.history(period="1mo")
        
        if not data.empty:
            # Save stock data
            db.save_stock_data(symbol, data)
            
            # Calculate and save technical indicators
            tech = TechnicalIndicators()
            data_with_indicators = tech.calculate_all_indicators(data)
            db.save_technical_indicators(symbol, data_with_indicators)
            
            st.success(f"Sample data loaded for {symbol}")
            st.rerun()
        else:
            st.error(f"Could not fetch data for {symbol}")
            
    except Exception as e:
        st.error(f"Error loading sample data: {str(e)}")

if __name__ == "__main__":
    database_console()