daboard3

import streamlit as st
import pandas as pd
import json
from datetime import datetime
import plotly.express as px
import plotly.graph_objects as go
from plotly.subplots import make_subplots
import numpy as np
from typing import Dict, List, Any, Optional

# Configure page
st.set_page_config(
    page_title="Snowflake Query Optimizer",
    page_icon="❄️",
    layout="wide",
    initial_sidebar_state="collapsed"
)

# Custom CSS for beautiful UI
st.markdown("""
<style>
    /* Import Google Fonts */
    @import url('https://fonts.googleapis.com/css2?family=Inter:wght@300;400;500;600;700&display=swap');

    /* Global Styles */
    * {
        font-family: 'Inter', sans-serif;
    }

    .main {
        padding: 1rem 2rem;
        background: linear-gradient(135deg, #f5f7fa 0%, #c3cfe2 100%);
        min-height: 100vh;
    }

    /* Header Styles */
    .header-container {
        background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
        padding: 3rem 2rem;
        border-radius: 20px;
        margin-bottom: 2rem;
        color: white;
        text-align: center;
        box-shadow: 0 20px 60px rgba(102, 126, 234, 0.3);
        position: relative;
        overflow: hidden;
    }

    .header-container::before {
        content: '';
        position: absolute;
        top: -50%;
        left: -50%;
        width: 200%;
        height: 200%;
        background: radial-gradient(circle, rgba(255,255,255,0.1) 0%, transparent 70%);
        animation: rotate 20s linear infinite;
    }

    @keyframes rotate {
        0% { transform: rotate(0deg); }
        100% { transform: rotate(360deg); }
    }

    .header-title {
        font-size: 3rem;
        font-weight: 700;
        margin-bottom: 1rem;
        text-shadow: 2px 2px 4px rgba(0,0,0,0.3);
        position: relative;
        z-index: 1;
    }

    .header-subtitle {
        font-size: 1.3rem;
        opacity: 0.95;
        font-weight: 400;
        position: relative;
        z-index: 1;
    }

    /* Metrics Cards */
    .metrics-container {
        display: grid;
        grid-template-columns: repeat(auto-fit, minmax(250px, 1fr));
        gap: 1.5rem;
        margin-bottom: 2rem;
    }

    .metric-card {
        background: white;
        padding: 2rem;
        border-radius: 16px;
        box-shadow: 0 8px 32px rgba(0,0,0,0.1);
        border: 1px solid rgba(255,255,255,0.2);
        backdrop-filter: blur(10px);
        transition: all 0.3s ease;
        position: relative;
        overflow: hidden;
    }

    .metric-card::before {
        content: '';
        position: absolute;
        top: 0;
        left: 0;
        width: 100%;
        height: 4px;
        background: linear-gradient(90deg, #667eea, #764ba2);
    }

    .metric-card:hover {
        transform: translateY(-8px);
        box-shadow: 0 16px 48px rgba(0,0,0,0.15);
    }

    .metric-value {
        font-size: 2.5rem;
        font-weight: 700;
        color: #2c3e50;
        margin-bottom: 0.5rem;
        background: linear-gradient(135deg, #667eea, #764ba2);
        -webkit-background-clip: text;
        -webkit-text-fill-color: transparent;
        background-clip: text;
    }

    .metric-label {
        color: #7f8c8d;
        font-size: 1rem;
        text-transform: uppercase;
        letter-spacing: 1px;
        font-weight: 500;
    }

    /* Table Styles */
    .table-container {
        background: white;
        border-radius: 20px;
        overflow: hidden;
        box-shadow: 0 20px 60px rgba(0,0,0,0.1);
        margin: 2rem 0;
        border: 1px solid rgba(255,255,255,0.2);
    }

    .table-header {
        background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
        color: white;
        padding: 2rem;
        text-align: center;
        position: relative;
    }

    .table-title {
        font-size: 1.8rem;
        font-weight: 600;
        margin-bottom: 0.5rem;
    }

    .table-subtitle {
        font-size: 1rem;
        opacity: 0.9;
        font-weight: 400;
    }

    /* Streamlit Table Row Styles */
    .table-row {
        display: flex;
        align-items: center;
        padding: 0.5rem 0;
        border-bottom: 1px solid #f1f3f4;
        transition: all 0.3s ease;
    }

    .table-row:hover {
        background: linear-gradient(135deg, #f8f9ff 0%, #f0f4ff 100%);
        border-radius: 8px;
        margin: 0 0.5rem;
    }

    .table-header-row {
        background: linear-gradient(135deg, #f8f9fa 0%, #e9ecef 100%);
        font-weight: 600;
        color: #495057;
        border-bottom: 2px solid #dee2e6;
        font-size: 0.9rem;
        text-transform: uppercase;
        letter-spacing: 0.5px;
        padding: 1rem 0;
        margin-bottom: 0.5rem;
        border-radius: 8px;
    }

    /* User cell styling */
    .user-cell-style {
        background: linear-gradient(135deg, #667eea 0%, #764ba2 100%) !important;
        color: white !important;
        font-weight: 700 !important;
        border-radius: 12px !important;
        border: none !important;
        box-shadow: 0 4px 15px rgba(102, 126, 234, 0.3) !important;
        height: 50px !important;
    }

    /* Clickable metric button styles */
    .metric-button {
        background: linear-gradient(135deg, #e3f2fd 0%, #bbdefb 100%) !important;
        color: #1565c0 !important;
        font-weight: 600 !important;
        border-radius: 8px !important;
        border: 2px solid transparent !important;
        transition: all 0.3s ease !important;
        height: 45px !important;
    }

    .metric-button:hover {
        background: linear-gradient(135deg, #1976d2 0%, #1565c0 100%) !important;
        color: white !important;
        transform: scale(1.05) !important;
        box-shadow: 0 4px 15px rgba(25, 118, 210, 0.3) !important;
    }

    .zero-button {
        background: #f8f9fa !important;
        color: #6c757d !important;
        font-weight: 500 !important;
        border-radius: 8px !important;
        border: none !important;
        cursor: default !important;
        height: 45px !important;
    }

    .zero-button:hover {
        background: #f8f9fa !important;
        color: #6c757d !important;
        transform: none !important;
        box-shadow: none !important;
    }

    /* Status button styles */
    .status-high {
        background: linear-gradient(135deg, #ff5722 0%, #d32f2f 100%) !important;
        color: white !important;
        border-radius: 20px !important;
        font-weight: 600 !important;
        font-size: 0.85rem !important;
        box-shadow: 0 4px 15px rgba(255, 87, 34, 0.3) !important;
        border: none !important;
        height: 45px !important;
    }

    .status-normal {
        background: linear-gradient(135deg, #4caf50 0%, #388e3c 100%) !important;
        color: white !important;
        border-radius: 20px !important;
        font-weight: 600 !important;
        font-size: 0.85rem !important;
        box-shadow: 0 4px 15px rgba(76, 175, 80, 0.3) !important;
        border: none !important;
        height: 45px !important;
    }

    /* Query Details Styles */
    .query-details-container {
        background: white;
        border-radius: 20px;
        padding: 2rem;
        margin: 2rem 0;
        box-shadow: 0 20px 60px rgba(0,0,0,0.1);
        border: 1px solid rgba(255,255,255,0.2);
    }

    .query-details-header {
        background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
        color: white;
        padding: 2rem;
        border-radius: 16px;
        margin-bottom: 2rem;
        text-align: center;
        position: relative;
        overflow: hidden;
    }

    .query-details-title {
        font-size: 2rem;
        font-weight: 700;
        margin-bottom: 0.5rem;
    }

    .query-details-subtitle {
        font-size: 1.1rem;
        opacity: 0.9;
        font-weight: 400;
    }

    .query-box {
        background: linear-gradient(135deg, #f8f9fa 0%, #e9ecef 100%);
        border: 2px solid #dee2e6;
        border-radius: 12px;
        padding: 1.5rem;
        margin: 1rem 0;
        font-family: 'Monaco', 'Menlo', 'Ubuntu Mono', monospace;
        font-size: 0.9rem;
        line-height: 1.6;
        white-space: pre-wrap;
        max-height: 400px;
        overflow-y: auto;
        box-shadow: inset 0 2px 8px rgba(0,0,0,0.05);
        position: relative;
    }

    .query-box::before {
        content: 'SQL';
        position: absolute;
        top: 10px;
        right: 15px;
        background: #667eea;
        color: white;
        padding: 4px 8px;
        border-radius: 4px;
        font-size: 0.7rem;
        font-weight: 600;
    }

    /* Button Styles */
    .stButton > button {
        transition: all 0.3s ease !important;
    }

    /* Dropdown Styles */
    .stSelectbox > div > div {
        background: white;
        border: 2px solid #e9ecef;
        border-radius: 12px;
        box-shadow: 0 4px 15px rgba(0,0,0,0.1);
    }

    /* Metric Cards in Details */
    .detail-metrics {
        display: grid;
        grid-template-columns: repeat(auto-fit, minmax(200px, 1fr));
        gap: 1rem;
        margin: 2rem 0;
    }

    .detail-metric-card {
        background: linear-gradient(135deg, #f8f9fa 0%, #e9ecef 100%);
        padding: 1.5rem;
        border-radius: 12px;
        text-align: center;
        border: 1px solid #dee2e6;
        transition: all 0.3s ease;
    }

    .detail-metric-card:hover {
        transform: translateY(-4px);
        box-shadow: 0 8px 25px rgba(0,0,0,0.1);
    }

    .detail-metric-value {
        font-size: 1.8rem;
        font-weight: 700;
        color: #495057;
        margin-bottom: 0.5rem;
    }

    .detail-metric-label {
        color: #6c757d;
        font-size: 0.9rem;
        font-weight: 500;
        text-transform: uppercase;
        letter-spacing: 0.5px;
    }

    /* Animation Classes */
    .fade-in {
        animation: fadeIn 0.6s ease-out;
    }

    @keyframes fadeIn {
        from {
            opacity: 0;
            transform: translateY(20px);
        }
        to {
            opacity: 1;
            transform: translateY(0);
        }
    }

    .slide-in {
        animation: slideIn 0.8s ease-out;
    }

    @keyframes slideIn {
        from {
            opacity: 0;
            transform: translateX(-30px);
        }
        to {
            opacity: 1;
            transform: translateX(0);
        }
    }

    /* Scrollbar Styles */
    ::-webkit-scrollbar {
        width: 8px;
    }

    ::-webkit-scrollbar-track {
        background: #f1f1f1;
        border-radius: 4px;
    }

    ::-webkit-scrollbar-thumb {
        background: linear-gradient(135deg, #667eea, #764ba2);
        border-radius: 4px;
    }

    ::-webkit-scrollbar-thumb:hover {
        background: linear-gradient(135deg, #5a6fd8, #6a42a0);
    }

    /* Loading Animation */
    .loading {
        display: inline-block;
        width: 20px;
        height: 20px;
        border: 3px solid rgba(102, 126, 234, 0.3);
        border-radius: 50%;
        border-top-color: #667eea;
        animation: spin 1s ease-in-out infinite;
    }

    @keyframes spin {
        to { transform: rotate(360deg); }
    }

    /* Hide default streamlit button styling for table */
    .element-container:has(.user-cell-style) .stButton > button {
        background: linear-gradient(135deg, #667eea 0%, #764ba2 100%) !important;
        color: white !important;
        font-weight: 700 !important;
        border-radius: 12px !important;
        border: none !important;
        box-shadow: 0 4px 15px rgba(102, 126, 234, 0.3) !important;
    }
</style>
""", unsafe_allow_html=True)

# Initialize session state
def init_session_state():
    """Initialize session state variables"""
    if 'page' not in st.session_state:
        st.session_state.page = 'overview'
    if 'selected_user' not in st.session_state:
        st.session_state.selected_user = None
    if 'selected_metric' not in st.session_state:
        st.session_state.selected_metric = None
    if 'selected_count' not in st.session_state:
        st.session_state.selected_count = 0
    if 'selected_query_id' not in st.session_state:
        st.session_state.selected_query_id = None
    if 'sample_queries' not in st.session_state:
        st.session_state.sample_queries = []

# Sample data generator (replace with your actual data loading function)
@st.cache_data
def load_sample_data():
    """Load sample data that matches your Snowflake query output"""
    np.random.seed(42)

    users = ['john_doe', 'jane_smith', 'mike_wilson', 'sarah_jones', 'alex_brown', 
             'chris_taylor', 'emma_davis', 'ryan_clark', 'lisa_white', 'david_hall']

    data = []
    for user in users:
        # Generate realistic sample queries for each metric type
        sample_queries = {
            'spilled': [
                {
                    'query_id': f'Q{np.random.randint(1000, 9999)}',
                    'query_text': f'SELECT * FROM large_table_{user} WHERE date > \'2024-01-01\' ORDER BY timestamp, amount DESC',
                    'execution_time_ms': np.random.randint(30000, 120000),
                    'bytes_spilled_to_local_storage': np.random.randint(500000, 5000000),
                    'bytes_spilled_to_remote_storage': np.random.randint(0, 1000000),
                    'warehouse_size': np.random.choice(['MEDIUM', 'LARGE', 'X-LARGE']),
                    'start_time': '2024-01-15 14:30:00'
                } for _ in range(np.random.randint(0, 5))
            ],
            'over_provisioned': [
                {
                    'query_id': f'Q{np.random.randint(1000, 9999)}',
                    'query_text': f'SELECT COUNT(*) FROM small_table_{user}',
                    'execution_time_ms': np.random.randint(1000, 5000),
                    'bytes_scanned': np.random.randint(1000, 100000),
                    'warehouse_size': 'X-LARGE',
                    'start_time': '2024-01-15 10:15:00'
                } for _ in range(np.random.randint(0, 8))
            ],
            'peak_hour_long_running': [
                {
                    'query_id': f'Q{np.random.randint(1000, 9999)}',
                    'query_text': f'SELECT a.*, b.* FROM big_table_a_{user} a JOIN big_table_b_{user} b ON a.id = b.id WHERE a.status = \'active\'',
                    'execution_time_ms': np.random.randint(300000, 600000),
                    'bytes_scanned': np.random.randint(10000, 50000),
                    'warehouse_size': np.random.choice(['LARGE', 'X-LARGE', '2X-LARGE']),
                    'start_time': '2024-01-15 14:30:00'
                } for _ in range(np.random.randint(0, 6))
            ],
            'select_star': [
                {
                    'query_id': f'Q{np.random.randint(1000, 9999)}',
                    'query_text': f'SELECT * FROM user_data_{user} LIMIT 1000',
                    'execution_time_ms': np.random.randint(5000, 30000),
                    'bytes_scanned': np.random.randint(1000, 10000),
                    'warehouse_size': np.random.choice(['SMALL', 'MEDIUM', 'LARGE']),
                    'start_time': '2024-01-15 11:20:00'
                } for _ in range(np.random.randint(0, 10))
            ],
            'unpartitioned_scan': [
                {
                    'query_id': f'Q{np.random.randint(1000, 9999)}',
                    'query_text': f'SELECT * FROM events_{user} WHERE event_date BETWEEN \'2024-01-01\' AND \'2024-12-31\'',
                    'execution_time_ms': np.random.randint(60000, 180000),
                    'bytes_scanned': np.random.randint(5000, 30000),
                    'partitions_scanned': 365,
                    'partitions_total': 365,
                    'start_time': '2024-01-15 16:45:00'
                } for _ in range(np.random.randint(0, 7))
            ],
            'zero_result': [
                {
                    'query_id': f'Q{np.random.randint(1000, 9999)}',
                    'query_text': f'SELECT * FROM transactions_{user} WHERE amount < 0 AND status = \'invalid\'',
                    'execution_time_ms': np.random.randint(10000, 60000),
                    'bytes_scanned': np.random.randint(500, 5000),
                    'rows_produced': 0,
                    'start_time': '2024-01-15 09:30:00'
                } for _ in range(np.random.randint(0, 4))
            ],
            'slow': [
                {
                    'query_id': f'Q{np.random.randint(1000, 9999)}',
                    'query_text': f'SELECT customer_id, SUM(amount) FROM transactions_{user} GROUP BY customer_id HAVING SUM(amount) > 10000 ORDER BY SUM(amount) DESC',
                    'execution_time_ms': np.random.randint(120000, 300000),
                    'bytes_scanned': np.random.randint(10000, 50000),
                    'warehouse_size': np.random.choice(['MEDIUM', 'LARGE', 'X-LARGE']),
                    'start_time': '2024-01-15 13:15:00'
                } for _ in range(np.random.randint(0, 6))
            ],
            'cartesian_join': [
                {
                    'query_id': f'Q{np.random.randint(1000, 9999)}',
                    'query_text': f'SELECT * FROM table_a_{user} CROSS JOIN table_b_{user}',
                    'execution_time_ms': np.random.randint(180000, 600000),
                    'bytes_scanned': np.random.randint(20000, 100000),
                    'rows_produced': np.random.randint(100, 10000),
                    'start_time': '2024-01-15 15:20:00'
                } for _ in range(np.random.randint(0, 2))
            ]
        }

        user_data = {
            'USER_NAME': user,
            'TOTAL_QUERIES': np.random.randint(500, 3000),
            'WAREHOUSES_USED': np.random.randint(2, 8),
            'DATABASES_ACCESSED': np.random.randint(3, 10),
            'TOTAL_CREDITS': round(np.random.uniform(50.0, 500.0), 2),
            'AVG_EXECUTION_TIME_MS': np.random.randint(1000, 8000),
            'AVG_BYTES_PER_ROW': round(np.random.uniform(100.0, 2000.0), 2),
            'TOTAL_DATA_SCANNED_GB': round(np.random.uniform(100.0, 2000.0), 2),
            'FAILURE_CANCELLATION_RATE_PCT': round(np.random.uniform(0.5, 8.0), 2),
            'SPILLED_QUERIES': len(sample_queries['spilled']),
            'OVER_PROVISIONED_QUERIES': len(sample_queries['over_provisioned']),
            'PEAK_HOUR_LONG_RUNNING_QUERIES': len(sample_queries['peak_hour_long_running']),
            'SELECT_STAR_QUERIES': len(sample_queries['select_star']),
            'UNPARTITIONED_SCAN_QUERIES': len(sample_queries['unpartitioned_scan']),
            'REPEATED_QUERIES': np.random.randint(50, 500),
            'COMPLEX_JOIN_QUERIES': np.random.randint(10, 100),
            'ZERO_RESULT_QUERIES': len(sample_queries['zero_result']),
            'HIGH_COMPILE_QUERIES': np.random.randint(5, 80),
            'UNTAGGED_QUERIES': np.random.randint(20, 200),
            'UNLIMITED_ORDER_BY_QUERIES': np.random.randint(5, 50),
            'LARGE_GROUP_BY_QUERIES': np.random.randint(3, 30),
            'SLOW_QUERIES': len(sample_queries['slow']),
            'EXPENSIVE_DISTINCT_QUERIES': np.random.randint(2, 25),
            'INEFFICIENT_LIKE_QUERIES': np.random.randint(10, 80),
            'NO_RESULTS_WITH_SCAN_QUERIES': np.random.randint(5, 40),
            'CARTESIAN_JOIN_QUERIES': len(sample_queries['cartesian_join']),
            'HIGH_COMPILE_RATIO_QUERIES': np.random.randint(3, 30),
            'WEIGHTED_SCORE': round(np.random.uniform(500.0, 3000.0), 2),
            'COST_STATUS': np.random.choice(['Normal', 'High Cost'], p=[0.7, 0.3]),
            'QUERY_SAMPLES': sample_queries
        }
        data.append(user_data)

    return pd.DataFrame(data)

def render_header():
    """Render the beautiful header section"""
    st.markdown("""
    <div class="header-container fade-in">
        <div class="header-title">❄️ Snowflake Query Optimizer</div>
        <div class="header-subtitle">Advanced Query Performance Analysis & Optimization Dashboard</div>
    </div>
    """, unsafe_allow_html=True)

def render_overview_metrics(df):
    """Render overview metrics cards"""
    total_users = len(df)
    total_queries = df['TOTAL_QUERIES'].sum()
    total_credits = df['TOTAL_CREDITS'].sum()
    high_cost_users = len(df[df['COST_STATUS'] == 'High Cost'])

    metrics_html = f"""
    <div class="metrics-container fade-in">
        <div class="metric-card">
            <div class="metric-value">{total_users:,}</div>
            <div class="metric-label">Total Users</div>
        </div>
        <div class="metric-card">
            <div class="metric-value">{total_queries:,}</div>
            <div class="metric-label">Total Queries</div>
        </div>
        <div class="metric-card">
            <div class="metric-value">${total_credits:,.2f}</div>
            <div class="metric-label">Total Credits</div>
        </div>
        <div class="metric-card">
            <div class="metric-value">{high_cost_users}</div>
            <div class="metric-label">High Cost Users</div>
        </div>
    </div>
    """

    st.markdown(metrics_html, unsafe_allow_html=True)

def handle_cell_click(user_name: str, metric_name: str, count: int, sample_queries: List[Dict]):
    """Handle cell click and navigate to details page"""
    st.session_state.page = 'query_list'
    st.session_state.selected_user = user_name
    st.session_state.selected_metric = metric_name
    st.session_state.selected_count = count
    st.session_state.sample_queries = sample_queries
    st.session_state.selected_query_id = None

def render_interactive_table(df):
    """Render the beautiful interactive table using Streamlit components"""
    st.markdown("""
    <div class="table-container slide-in">
        <div class="table-header">
            <div class="table-title">Query Performance Analysis</div>
            <div class="table-subtitle">Click on any query count to view detailed analysis</div>
        </div>
    </div>
    """, unsafe_allow_html=True)

    # Define metrics to display (excluding recommendations and query samples)
    metrics_config = [
        ('SPILLED_QUERIES', 'Spilled', 'spilled'),
        ('OVER_PROVISIONED_QUERIES', 'Over Provisioned', 'over_provisioned'),
        ('PEAK_HOUR_LONG_RUNNING_QUERIES', 'Peak Long Running', 'peak_hour_long_running'),
        ('SELECT_STAR_QUERIES', 'Select *', 'select_star'),
        ('UNPARTITIONED_SCAN_QUERIES', 'Unpartitioned Scan', 'unpartitioned_scan'),
        ('ZERO_RESULT_QUERIES', 'Zero Results', 'zero_result'),
        ('SLOW_QUERIES', 'Slow Queries', 'slow'),
        ('CARTESIAN_JOIN_QUERIES', 'Cartesian Joins', 'cartesian_join')
    ]

    # Create table header using columns
    header_cols = st.columns([2] + [1.2] * len(metrics_config) + [1.5])

    # Style the header row
    st.markdown('<div class="table-header-row">', unsafe_allow_html=True)
    header_cols[0].markdown("**User**")
    for i, (_, label, _) in enumerate(metrics_config):
        header_cols[i+1].markdown(f"**{label}**")
    header_cols[-1].markdown("**Cost Status**")
    st.markdown('</div>', unsafe_allow_html=True)

    # Create table rows
    for idx, row in df.iterrows():
        cols = st.columns([2] + [1.2] * len(metrics_config) + [1.5])

        # User column - non-clickable display
        with cols[0]:
            st.button(
                row['USER_NAME'], 
                key=f"user_{idx}",
                disabled=True,
                help=f"User: {row['USER_NAME']}",
                use_container_width=True
            )
            # Apply custom styling
            st.markdown(f"""
            <style>
            div[data-testid="stButton"]:has(button[key="user_{idx}"]) button {{
                background: linear-gradient(135deg, #667eea 0%, #764ba2 100%) !important;
                color: white !important;
                font-weight: 700 !important;
                border-radius: 12px !important;
                border: none !important;
                box-shadow: 0 4px 15px rgba(102, 126, 234, 0.3) !important;
                cursor: default !important;
            }}
            </style>
            """, unsafe_allow_html=True)

        # Metric columns with clickable buttons
        for i, (col_name, _, sample_key) in enumerate(metrics_config):
            value = int(row[col_name])
            sample_queries = row['QUERY_SAMPLES'].get(sample_key, []) if isinstance(row['QUERY_SAMPLES'], dict) else []

            with cols[i+1]:
                if value == 0:
                    # Zero value - non-clickable
                    st.button(
                        str(value),
                        key=f"zero_{row['USER_NAME']}_{col_name}_{idx}",
                        disabled=True,
                        use_container_width=True
                    )
                    # Apply zero styling
                    st.markdown(f"""
                    <style>
                    div[data-testid="stButton"]:has(button[key="zero_{row['USER_NAME']}_{col_name}_{idx}"]) button {{
                        background: #f8f9fa !important;
                        color: #6c757d !important;
                        font-weight: 500 !important;
                        border-radius: 8px !important;
                        border: none !important;
                        cursor: default !important;
                    }}
                    </style>
                    """, unsafe_allow_html=True)
                else:
                    # Clickable metric button
                    if st.button(
                        str(value),
                        key=f"metric_{row['USER_NAME']}_{col_name}_{idx}",
                        help=f"View {value} {col_name.replace('_', ' ').lower()}",
                        use_container_width=True
                    ):
                        handle_cell_click(row['USER_NAME'], col_name, value, sample_queries)
                        st.rerun()

                    # Apply metric button styling
                    st.markdown(f"""
                    <style>
                    div[data-testid="stButton"]:has(button[key="metric_{row['USER_NAME']}_{col_name}_{idx}"]) button {{
                        background: linear-gradient(135deg, #e3f2fd 0%, #bbdefb 100%) !important;
                        color: #1565c0 !important;
                        font-weight: 600 !important;
                        border-radius: 8px !important;
                        border: 2px solid transparent !important;
                        transition: all 0.3s ease !important;
                    }}
                    div[data-testid="stButton"]:has(button[key="metric_{row['USER_NAME']}_{col_name}_{idx}"]) button:hover {{
                        background: linear-gradient(135deg, #1976d2 0%, #1565c0 100%) !important;
                        color: white !important;
                        transform: scale(1.05) !important;
                        box-shadow: 0 4px 15px rgba(25, 118, 210, 0.3) !important;
                    }}
                    </style>
                    """, unsafe_allow_html=True)

        # Cost status column
        with cols[-1]:
            status_text = row['COST_STATUS']
            st.button(
                status_text,
                key=f"status_{row['USER_NAME']}_{idx}",
                disabled=True,
                use_container_width=True
            )

            # Apply status styling
            status_class = "status-high" if status_text == 'High Cost' else "status-normal"
            bg_color = "linear-gradient(135deg, #ff5722 0%, #d32f2f 100%)" if status_text == 'High Cost' else "linear-gradient(135deg, #4caf50 0%, #388e3c 100%)"

            st.markdown(f"""
            <style>
            div[data-testid="stButton"]:has(button[key="status_{row['USER_NAME']}_{idx}"]) button {{
                background: {bg_color} !important;
                color: white !important;
                border-radius: 20px !important;
                font-weight: 600 !important;
                font-size: 0.85rem !important;
                box-shadow: 0 4px 15px rgba(255, 87, 34, 0.3) !important;
                border: none !important;
                cursor: default !important;
            }}
            </style>
            """, unsafe_allow_html=True)

def render_query_list_page():
    """Render the query list page with dropdown selection"""
    # Back button
    col1, col2 = st.columns([1, 4])
    with col1:
        if st.button("← Back to Overview", key="back_to_overview", help="Return to main table"):
            st.session_state.page = 'overview'
            st.rerun()

    # Page header
    metric_display = st.session_state.selected_metric.replace('_', ' ').title()
    st.markdown(f"""
    <div class="query-details-container fade-in">
        <div class="query-details-header">
            <div class="query-details-title">{st.session_state.selected_user}</div>
            <div class="query-details-subtitle">{metric_display} - {st.session_state.selected_count} queries found</div>
        </div>
    </div>
    """, unsafe_allow_html=True)

    # Show sample queries if available
    if st.session_state.sample_queries:
        st.markdown("### 📋 Sample Queries")

        # Create dropdown for query selection
        query_options = []
        for i, query in enumerate(st.session_state.sample_queries):
            query_preview = query.get('query_text', 'No query text')[:50] + "..."
            query_options.append(f"Query {i+1}: {query.get('query_id', 'Unknown')} - {query_preview}")

        selected_query_idx = st.selectbox(
            "Select a query to view details:",
            range(len(query_options)),
            format_func=lambda x: query_options[x],
            key="query_selector"
        )

        if st.button("View Query Details", key="view_details", type="primary"):
            st.session_state.selected_query_id = selected_query_idx
            st.session_state.page = 'query_details'
            st.rerun()

        # Show preview of selected query
        if selected_query_idx is not None:
            selected_query = st.session_state.sample_queries[selected_query_idx]

            col1, col2 = st.columns([2, 1])

            with col1:
                st.markdown("**Query Preview:**")
                query_text = selected_query.get('query_text', 'No query text available')
                st.markdown(f'<div class="query-box">{query_text}</div>', unsafe_allow_html=True)

            with col2:
                st.markdown("**Quick Metrics:**")

                metrics_html = '<div class="detail-metrics">'

                if 'execution_time_ms' in selected_query:
                    metrics_html += f'''
                    <div class="detail-metric-card">
                        <div class="detail-metric-value">{selected_query["execution_time_ms"]:,}ms</div>
                        <div class="detail-metric-label">Execution Time</div>
                    </div>
                    '''

                if 'bytes_scanned' in selected_query:
                    bytes_gb = selected_query['bytes_scanned'] / (1024**3)
                    metrics_html += f'''
                    <div class="detail-metric-card">
                        <div class="detail-metric-value">{bytes_gb:.2f}GB</div>
                        <div class="detail-metric-label">Data Scanned</div>
                    </div>
                    '''

                if 'warehouse_size' in selected_query:
                    metrics_html += f'''
                    <div class="detail-metric-card">
                        <div class="detail-metric-value">{selected_query["warehouse_size"]}</div>
                        <div class="detail-metric-label">Warehouse</div>
                    </div>
                    '''

                metrics_html += '</div>'
                st.markdown(metrics_html, unsafe_allow_html=True)

    else:
        st.info("No sample queries available for this metric.")

def render_query_details_page():
    """Render detailed view of a specific query"""
    # Back button
    col1, col2 = st.columns([1, 4])
    with col1:
        if st.button("← Back to Query List", key="back_to_list", help="Return to query list"):
            st.session_state.page = 'query_list'
            st.rerun()

    # Get selected query
    if (st.session_state.selected_query_id is not None and 
        st.session_state.selected_query_id < len(st.session_state.sample_queries)):

        query = st.session_state.sample_queries[st.session_state.selected_query_id]

        # Page header
        st.markdown(f"""
        <div class="query-details-container fade-in">
            <div class="query-details-header">
                <div class="query-details-title">Query Details: {query.get('query_id', 'Unknown')}</div>
                <div class="query-details-subtitle">{st.session_state.selected_user} - {st.session_state.selected_metric.replace('_', ' ').title()}</div>
            </div>
        </div>
        """, unsafe_allow_html=True)

        # Query metrics
        st.markdown("### 📊 Query Metrics")

        # Create metrics grid
        metrics_data = []
        if 'execution_time_ms' in query:
            metrics_data.append(("Execution Time", f"{query['execution_time_ms']:,} ms"))
        if 'bytes_scanned' in query:
            bytes_gb = query['bytes_scanned'] / (1024**3)
            metrics_data.append(("Data Scanned", f"{bytes_gb:.2f} GB"))
        if 'warehouse_size' in query:
            metrics_data.append(("Warehouse Size", query['warehouse_size']))
        if 'rows_produced' in query:
            metrics_data.append(("Rows Produced", f"{query['rows_produced']:,}"))
        if 'bytes_spilled_to_local_storage' in query:
            spilled_mb = query['bytes_spilled_to_local_storage'] / (1024**2)
            metrics_data.append(("Local Spill", f"{spilled_mb:.2f} MB"))
        if 'bytes_spilled_to_remote_storage' in query:
            spilled_mb = query['bytes_spilled_to_remote_storage'] / (1024**2)
            metrics_data.append(("Remote Spill", f"{spilled_mb:.2f} MB"))
        if 'partitions_scanned' in query and 'partitions_total' in query:
            metrics_data.append(("Partitions", f"{query['partitions_scanned']}/{query['partitions_total']}"))
        if 'start_time' in query:
            metrics_data.append(("Start Time", query['start_time']))

        # Display metrics in a grid
        cols = st.columns(min(4, len(metrics_data)))
        for i, (label, value) in enumerate(metrics_data):
            with cols[i % 4]:
                st.metric(label, value)

        # Query text
        st.markdown("### 📝 Query Text")
        query_text = query.get('query_text', 'No query text available')
        st.markdown(f'<div class="query-box">{query_text}</div>', unsafe_allow_html=True)

        # Performance analysis
        st.markdown("### 🔍 Performance Analysis")

        analysis_points = []

        # Add analysis based on query type and metrics
        if 'execution_time_ms' in query and query['execution_time_ms'] > 60000:
            analysis_points.append("⚠️ **Long execution time detected** - Consider optimizing query logic or adding appropriate indexes")

        if 'bytes_scanned' in query and query['bytes_scanned'] > 10000:  # > 1GB
            analysis_points.append("📊 **High data scan volume** - Consider using clustering keys or more selective WHERE clauses")

        if 'bytes_spilled_to_local_storage' in query and query['bytes_spilled_to_local_storage'] > 0:
            analysis_points.append("💾 **Memory spilling detected** - Consider increasing warehouse size or optimizing query complexity")

        if 'warehouse_size' in query and query['warehouse_size'] in ['X-LARGE', '2X-LARGE', '3X-LARGE', '4X-LARGE']:
            if 'execution_time_ms' in query and query['execution_time_ms'] < 10000:
                analysis_points.append("💰 **Potential over-provisioning** - Query completed quickly on large warehouse")

        if 'partitions_scanned' in query and 'partitions_total' in query:
            if query['partitions_scanned'] == query['partitions_total']:
                analysis_points.append("🔄 **Full table scan** - Consider adding partition pruning conditions")

        if query.get('rows_produced', 1) == 0:
            analysis_points.append("❌ **Zero results returned** - Review query logic and data availability")

        if analysis_points:
            for point in analysis_points:
                st.markdown(point)
        else:
            st.success("✅ No major performance issues detected for this query")

        # Recommendations
        st.markdown("### 💡 Optimization Recommendations")

        recommendations = []

        # Generate recommendations based on query characteristics
        if 'SELECT *' in query_text.upper():
            recommendations.append("🎯 **Specify columns explicitly** instead of using SELECT * to reduce data transfer")

        if 'ORDER BY' in query_text.upper() and 'LIMIT' not in query_text.upper():
            recommendations.append("📏 **Add LIMIT clause** to ORDER BY queries to prevent unnecessary sorting of large result sets")

        if 'JOIN' in query_text.upper() and 'ON' not in query_text.upper():
            recommendations.append("⚠️ **Review JOIN conditions** to ensure proper join predicates are specified")

        if 'GROUP BY' in query_text.upper():
            recommendations.append("📊 **Consider pre-aggregation** for frequently used GROUP BY operations")

        if 'DISTINCT' in query_text.upper():
            recommendations.append("🔄 **Evaluate DISTINCT usage** - consider if GROUP BY might be more efficient")

        if recommendations:
            for rec in recommendations:
                st.markdown(rec)
        else:
            st.info("No specific recommendations available for this query pattern")

    else:
        st.error("Query not found or invalid selection")

def render_analytics_charts(df):
    """Render analytics charts"""
    st.markdown("### 📊 Analytics Overview")

    col1, col2 = st.columns(2)

    with col1:
        # Top users by weighted score
        top_users = df.nlargest(10, 'WEIGHTED_SCORE')
        fig = px.bar(
            top_users,
            x='USER_NAME',
            y='WEIGHTED_SCORE',
            title='Top Users by Weighted Score',
            color='WEIGHTED_SCORE',
            color_continuous_scale='Viridis',
            template='plotly_white'
        )
        fig.update_layout(
            showlegend=False,
            title_font_size=16,
            title_font_color='#2c3e50'
        )
        st.plotly_chart(fig, use_container_width=True)

    with col2:
        # Cost status distribution
        cost_dist = df['COST_STATUS'].value_counts()
        fig = px.pie(
            values=cost_dist.values,
            names=cost_dist.index,
            title='Cost Status Distribution',
            color_discrete_map={'Normal': '#4caf50', 'High Cost': '#f44336'},
            template='plotly_white'
        )
        fig.update_layout(
            title_font_size=16,
            title_font_color='#2c3e50'
        )
        st.plotly_chart(fig, use_container_width=True)

def main():
    """Main application function"""
    # Initialize session state
    init_session_state()

    # Load data (replace this with your actual data loading function)
    df = load_sample_data()

    # Render header
    render_header()

    # Route to appropriate page
    if st.session_state.page == 'overview':
        render_overview_metrics(df)
        render_interactive_table(df)
        render_analytics_charts(df)

    elif st.session_state.page == 'query_list':
        render_query_list_page()

    elif st.session_state.page == 'query_details':
        render_query_details_page()

# Function to replace load_sample_data with your actual data
def load_your_data(df_from_snowflake):
    """
    Replace this function with your actual data loading logic

    Args:
        df_from_snowflake: DataFrame from your Snowflake query

    Returns:
        Processed DataFrame ready for the dashboard
    """
    # Your data processing logic here
    # Make sure the DataFrame has all required columns
    return df_from_snowflake

if __name__ == "__main__":
    main()