import streamlit as st
import pandas as pd
import sqlite3
import plotly.express as px
import plotly.graph_objects as go
import os

def main():
    st.set_page_config(page_title="Stock Trading Database Console", layout="wide")
    
    st.title("📊 Stock Trading Database Console")
    st.markdown("### SQLite Database Management & Analytics")
    
    # Database file info
    db_file = "stock_trading.db"
    if os.path.exists(db_file):
        size = os.path.getsize(db_file)
        st.success(f"Database: {db_file} ({size/1024:.1f} KB)")
    else:
        st.warning(f"Database file {db_file} not found. Data will be created when you use the main app.")
        return
    
    # Connect to database
    try:
        conn = sqlite3.connect(db_file)
        st.success("Database connection established")
    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", "Tables", "Stock Data", "Trading Signals", "Model Metrics", "SQL Query"]
    )
    
    if section == "Overview":
        show_overview(conn)
    elif section == "Tables":
        show_tables(conn)
    elif section == "Stock Data":
        show_stock_data(conn)
    elif section == "Trading Signals":
        show_trading_signals(conn)
    elif section == "Model Metrics":
        show_model_metrics(conn)
    elif section == "SQL Query":
        show_sql_interface(conn)
    
    conn.close()

def show_overview(conn):
    """Show database overview"""
    st.header("Database Overview")
    
    # Get table information
    tables_query = "SELECT name FROM sqlite_master WHERE type='table'"
    tables = pd.read_sql(tables_query, conn)
    
    if not tables.empty:
        st.subheader("Available Tables")
        st.dataframe(tables)
        
        # Table statistics
        col1, col2, col3, col4 = st.columns(4)
        
        try:
            stock_count = pd.read_sql("SELECT COUNT(*) as count FROM stock_data", conn).iloc[0]['count']
            with col1:
                st.metric("Stock Records", stock_count)
        except:
            with col1:
                st.metric("Stock Records", 0)
        
        try:
            signals_count = pd.read_sql("SELECT COUNT(*) as count FROM trading_signals", conn).iloc[0]['count']
            with col2:
                st.metric("Trading Signals", signals_count)
        except:
            with col2:
                st.metric("Trading Signals", 0)
        
        try:
            indicators_count = pd.read_sql("SELECT COUNT(*) as count FROM technical_indicators", conn).iloc[0]['count']
            with col3:
                st.metric("Technical Indicators", indicators_count)
        except:
            with col3:
                st.metric("Technical Indicators", 0)
        
        try:
            models_count = pd.read_sql("SELECT COUNT(*) as count FROM model_metrics", conn).iloc[0]['count']
            with col4:
                st.metric("Model Metrics", models_count)
        except:
            with col4:
                st.metric("Model Metrics", 0)
    else:
        st.info("No tables found. Use the main app to generate data.")

def show_tables(conn):
    """Show table schemas"""
    st.header("Table Schemas")
    
    # Get all tables
    tables_query = "SELECT name FROM sqlite_master WHERE type='table'"
    tables = pd.read_sql(tables_query, conn)
    
    for table_name in tables['name']:
        st.subheader(f"Table: {table_name}")
        
        # Get table schema
        schema_query = f"PRAGMA table_info({table_name})"
        schema = pd.read_sql(schema_query, conn)
        st.dataframe(schema)
        
        # Get sample data
        try:
            sample_query = f"SELECT * FROM {table_name} LIMIT 3"
            sample = pd.read_sql(sample_query, conn)
            if not sample.empty:
                st.write("Sample Data:")
                st.dataframe(sample)
        except:
            st.write("No sample data available")
        
        st.markdown("---")

def show_stock_data(conn):
    """Show stock OHLCV data"""
    st.header("Stock Data Analysis")
    
    try:
        # Get unique symbols
        symbols_query = "SELECT DISTINCT symbol FROM stock_data ORDER BY symbol"
        symbols = pd.read_sql(symbols_query, conn)
        
        if not symbols.empty:
            selected_symbol = st.selectbox("Select Stock Symbol:", symbols['symbol'].tolist())
            
            # Get data for selected symbol
            data_query = f"""
                SELECT * FROM stock_data 
                WHERE symbol = '{selected_symbol}' 
                ORDER BY date DESC 
                LIMIT 100
            """
            stock_data = pd.read_sql(data_query, conn)
            
            if not stock_data.empty:
                st.dataframe(stock_data)
                
                # Create candlestick chart
                fig = go.Figure(data=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']
                ))
                fig.update_layout(title=f"{selected_symbol} - Candlestick Chart")
                st.plotly_chart(fig, use_container_width=True)
                
                # Volume chart
                fig_vol = px.bar(stock_data, x='date', y='volume', title=f"{selected_symbol} - Volume")
                st.plotly_chart(fig_vol, use_container_width=True)
            else:
                st.info(f"No data found for {selected_symbol}")
        else:
            st.info("No stock data available. Train a model in the main app to generate data.")
    except Exception as e:
        st.error(f"Error loading stock data: {str(e)}")

def show_trading_signals(conn):
    """Show trading signals"""
    st.header("Trading Signals Analysis")
    
    try:
        signals_query = "SELECT * FROM trading_signals ORDER BY signal_date DESC LIMIT 100"
        signals = pd.read_sql(signals_query, conn)
        
        if not signals.empty:
            st.dataframe(signals)
            
            # Signal distribution
            col1, col2 = st.columns(2)
            
            with col1:
                signal_counts = signals['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, x='confidence', title="Confidence Distribution")
                st.plotly_chart(fig_conf, use_container_width=True)
            
            # Signals by symbol
            if 'symbol' in signals.columns:
                symbol_signals = signals.groupby('symbol')['signal'].count().reset_index()
                fig_bar = px.bar(symbol_signals, x='symbol', y='signal', title="Signals by Symbol")
                st.plotly_chart(fig_bar, use_container_width=True)
        else:
            st.info("No trading signals found. Generate signals in the main app.")
    except Exception as e:
        st.error(f"Error loading trading signals: {str(e)}")

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

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

if __name__ == "__main__":
    main()