new22

import json
from datetime import datetime

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

from core.query_executor import query_executor
from queries.QueryBuilder import QueryBuilder
from queries.filter import CommonUI
from queries.final_last import USER_360_QUERIES


def create_snowflake_dashboard(df):
    # Page configuration
    # st.set_page_config(
    #     page_title="Snowflake Query Analytics",
    #     page_icon="❄️",
    #     layout="wide",
    #     initial_sidebar_state="expanded",
    # )

    # Custom CSS for better styling
    st.markdown(
        """
    <style>
        .main-header {
            background: linear-gradient(90deg, #667eea 0%, #764ba2 100%);
            color: white;
            padding: 20px;
            border-radius: 10px;
            text-align: center;
            margin-bottom: 20px;
            box-shadow: 0 4px 6px rgba(0, 0, 0, 0.1);
        }

        .metric-card {
            background: white;
            padding: 20px;
            border-radius: 10px;
            box-shadow: 0 2px 4px rgba(0,0,0,0.1);
            border-left: 4px solid #667eea;
            margin: 10px 0;
            transition: transform 0.2s;
        }

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

        .metric-value {
            font-size: 2em;
            font-weight: bold;
            color: #2c3e50;
        }

        .metric-label {
            color: #7f8c8d;
            font-size: 0.9em;
            margin-top: 5px;
        }

        .status-normal {
            background: #d4edda;
            color: #155724;
            padding: 5px 10px;
            border-radius: 20px;
            font-weight: bold;
        }

        .status-warning {
            background: #fff3cd;
            color: #856404;
            padding: 5px 10px;
            border-radius: 20px;
            font-weight: bold;
        }

        .status-critical {
            background: #f8d7da;
            color: #721c24;
            padding: 5px 10px;
            border-radius: 20px;
            font-weight: bold;
        }

        .query-detail-card {
            background: #f8f9fa;
            padding: 15px;
            border-radius: 8px;
            margin: 10px 0;
            border-left: 4px solid #007bff;
        }

        .recommendation-card {
            background: #fff3cd;
            padding: 15px;
            border-radius: 8px;
            margin: 10px 0;
            border-left: 4px solid #ffc107;
        }

        .table-container {
            overflow-x: auto;
            background: white;
            padding: 20px;
            border-radius: 12px;
            box-shadow: 0 4px 8px rgba(0,0,0,0.1);
            margin-top: 20px;
            max-height: 600px;
            overflow-y: auto;
            scrollbar-width: thin;
            scrollbar-color: #667eea #f1f3f5;
        }

        .table-container::-webkit-scrollbar {
            height: 12px;
            width: 8px;
        }

        .table-container::-webkit-scrollbar-track {
            background: #f1f3f5;
            border-radius: 10px;
        }

        .table-container::-webkit-scrollbar-thumb {
            background: #667eea;
            border-radius: 10px;
        }

        .table-row {
            display: flex;
            align-items: center;
            padding: 12px ,20px ;
            border-bottom: 1px solid #e9ecef;
            transition: background-color 0.2s;
        }

        .table-row:nth-child(even) {
            background-color: #f8f9fa;
        }



        .table-cell {
            flex: 1;
            text-align: center;
            padding: 10px;
            font-size: 0.95em;
            color: #2c3e50;
        }

        .table-header {
            font-weight: 600;
            background: #667eea;
            color: white;
            padding: 12px;
            border-radius: 8px 8px 0 0;
            position: sticky;
            top: 0;
            z-index: 1;
        }

        .user-cell {
            flex: 2.5;
            text-align: left;
            font-weight: bold;
        }

        .status-cell {
            flex: 1.2;
        }

        .score-cell {
            flex: 1.2;
        }

        .stButton>button {
            border: none;
            background: none;
            color: #007bff;
            cursor: pointer;
            font-size: 0.95em;
            padding: 5px;
            width: 100%;
            text-align: center;
        }

        .stButton>button:hover {
            color: #ffffff
            background: #e3f2fd;
            border-radius: 4px;
        }
    </style>
    """,
        unsafe_allow_html=True,
    )

    # Initialize session state
    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

    # Header
    st.markdown(
        """
    <div class="main-header">
        <h1>❄️ Snowflake Query Analytics Dashboard</h1>
        <p>Monitor and optimize your Snowflake query performance</p>
    </div>
    """,
        unsafe_allow_html=True,
    )

    # Sidebar for data input
    with st.sidebar:
        st.header("📊 Data Management")
        uploaded_file = st.file_uploader("Upload JSON Data", type=["json"])
        if uploaded_file is not None:
            try:
                new_data = json.load(uploaded_file)
                if isinstance(new_data, list):
                    df = pd.DataFrame(new_data)
                    st.success("Data loaded successfully!")
                else:
                    df = pd.DataFrame([new_data])
                    st.success("Data loaded successfully!")
            except Exception as e:
                st.error(f"Error loading data: {e}")

        st.subheader("Or paste JSON data:")
        json_input = st.text_area(
            "JSON Data", height=200, placeholder="Paste your JSON data here..."
        )
        if st.button("Load JSON Data"):
            try:
                new_data = json.loads(json_input)
                if isinstance(new_data, list):
                    df = pd.DataFrame(new_data)
                else:
                    df = pd.DataFrame([new_data])
                st.success("Data loaded successfully!")
                st.rerun()
            except Exception as e:
                st.error(f"Error parsing JSON: {e}")

    # Main dashboard
    if not df.empty:
        # Overall metrics cards
        st.header("📈 Overall Metrics")
        col1, col2, col3, col4 = st.columns(4)

        with col1:
            total_users = len(df)
            st.markdown(
                f"""
            <div class="metric-card">
                <div class="metric-value">{total_users}</div>
                <div class="metric-label">Total Users</div>
            </div>
            """,
                unsafe_allow_html=True,
            )

        with col2:
            total_queries = df["TOTAL_QUERIES"].sum()
            st.markdown(
                f"""
            <div class="metric-card">
                <div class="metric-value">{total_queries:,}</div>
                <div class="metric-label">Total Queries</div>
            </div>
            """,
                unsafe_allow_html=True,
            )

        with col3:
            total_credits = df["TOTAL_CREDITS"].sum()
            st.markdown(
                f"""
            <div class="metric-card">
                <div class="metric-value">${total_credits:.2f}</div>
                <div class="metric-label">Total Credits</div>
            </div>
            """,
                unsafe_allow_html=True,
            )

        with col4:
            avg_score = df["WEIGHTED_SCORE"].mean()
            st.markdown(
                f"""
            <div class="metric-card">
                <div class="metric-value">{avg_score:.1f}</div>
                <div class="metric-label">Avg Weighted Score</div>
            </div>
            """,
                unsafe_allow_html=True,
            )

        col5, col6, col7, col8 = st.columns(4)

        with col5:
            total_spilled = df["SPILLED_QUERIES"].sum()
            st.markdown(
                f"""
            <div class="metric-card">
                <div class="metric-value">{total_spilled}</div>
                <div class="metric-label">Total Spilled Queries</div>
            </div>
            """,
                unsafe_allow_html=True,
            )

        with col6:
            total_slow = df["SLOW_QUERIES"].sum()
            st.markdown(
                f"""
            <div class="metric-card">
                <div class="metric-value">{total_slow}</div>
                <div class="metric-label">Total Slow Queries</div>
            </div>
            """,
                unsafe_allow_html=True,
            )

        with col7:
            total_select_star = df["SELECT_STAR_QUERIES"].sum()
            st.markdown(
                f"""
            <div class="metric-card">
                <div class="metric-value">{total_select_star}</div>
                <div class="metric-label">SELECT * Queries</div>
            </div>
            """,
                unsafe_allow_html=True,
            )

        with col8:
            avg_failure_rate = df["FAILURE_CANCELLATION_RATE_PCT"].mean()
            st.markdown(
                f"""
            <div class="metric-card">
                <div class="metric-value">{avg_failure_rate:.1f}%</div>
                <div class="metric-label">Avg Failure Rate</div>
            </div>
            """,
                unsafe_allow_html=True,
            )

        st.markdown("---")

        # Query Details Container


        import ast

        # Custom CSS for modern styling
        st.markdown(
            """
            <style>
            .query-detail-card, .recommendation-card {
                background: #ffffff;
                border-radius: 12px;
                padding: 20px;
                box-shadow: 0 4px 12px rgba(0, 0, 0, 0.1);
                margin-bottom: 20px;
                transition: transform 0.2s ease-in-out;
            }
            .query-detail-card:hover, .recommendation-card:hover {
                transform: translateY(-5px);
            }
            .metric-card {
                background: #f8f9fa;
                border-radius: 8px;
                padding: 10px;
                text-align: center;
            }
            .stButton > button {
                background: #0066cc;
                color: white;
                border-radius: 8px;
                padding: 10px 20px;
                border: none;
                transition: background 0.3s;
            }
            .stButton > button:hover {
                background: #0052a3;
            }
            .st-expander {
                border-radius: 8px;
                border: 1px solid #e0e0e0;
            }
            .header-container {
                position: sticky;
                top: 0;
                background: #f8f9fa;
                z-index: 100;
                padding: 10px 0;
            }
            h4 {
                font-size: 1.5rem;
                color: #333;
                margin-bottom: 10px;
            }
            .metric-label {
                font-weight: bold;
                color: #555;
            }
            .status-green {
                color: #28a745;
            }
            .status-red {
                color: #dc3545;
            }
            </style>
            """,
            unsafe_allow_html=True,
        )

        def normalize_metric_name(metric_name):
            return (
                metric_name.lower()
                .replace("_queries", "")
                .replace("_", " ")
                .replace(" ", "_")
            )

        # Main container
        query_container = st.container()
        with query_container:
            if st.session_state.get("selected_user") and st.session_state.get("selected_metric"):
                # Sticky header
                with st.container():
                    st.markdown(
                        f"""
                        <div class="header-container">
                            <h2>🔍 Query Details for {st.session_state.selected_user}</h2>
                            <h4>Metric: {st.session_state.selected_metric.replace('_', ' ').title()}</h4>
                        </div>
                        """,
                        unsafe_allow_html=True,
                    )

                user_data = df[df["USER_NAME"] == st.session_state.selected_user].iloc[0]
                metric_value = user_data[st.session_state.selected_metric]

                # Layout: 2-column grid
                col1, col2 = st.columns([3, 1])

                with col1:
                    st.markdown(
                        f"""
                        <div class="query-detail-card" role="region" aria-label="Query Details">
                            <h4>{st.session_state.selected_metric.replace('_', ' ').title()}: {metric_value}</h4>
                            <p><span class="metric-label">User:</span> {st.session_state.selected_user}</p>
                            <p><span class="metric-label">Total Queries:</span> {user_data['TOTAL_QUERIES']}</p>
                            <p><span class="metric-label">Weighted Score:</span> {user_data['WEIGHTED_SCORE']}</p>
                            <p><span class="metric-label">Cost Status:</span> 
                                <span class="{ 'status-green' if user_data['COST_STATUS'] == 'Optimal' else 'status-red' }">
                                    {user_data['COST_STATUS']}
                                </span>
                            </p>
                        </div>
                        """,
                        unsafe_allow_html=True,
                    )

                with col2:
                    if st.button("❌ Clear", help="Clear current selection"):
                        st.session_state.selected_user = None
                        st.session_state.selected_metric = None
                        st.rerun()

                # Sample Queries Section
                if "QUERY_SAMPLES" in user_data and user_data["QUERY_SAMPLES"]:
                    st.subheader("📋 Sample Queries")

                    query_samples = user_data["QUERY_SAMPLES"]
                    if isinstance(query_samples, str):
                        with st.spinner("Parsing query samples..."):
                            try:
                                query_samples = ast.literal_eval(query_samples)
                            except Exception as e:
                                st.error(f"Failed to parse QUERY_SAMPLES: {e}")
                                query_samples = {}

                    sample_key = normalize_metric_name(st.session_state.selected_metric)

                    if sample_key in query_samples:
                        samples = query_samples[sample_key]
                        for i, sample in enumerate(samples):
                            with st.expander(f"Query {i+1}: {sample.get('query_id', 'N/A')}", expanded=False):
                                st.code(
                                    sample.get("query_text", "No query text available"),
                                    language="sql",
                                )

                                # Metrics in a grid layout
                                col1, col2, col3 = st.columns(3)
                                with col1:
                                    execution_time = sample.get("execution_time_ms", 0)
                                    st.markdown(
                                        f"""
                                        <div class="metric-card">
                                            <p class="metric-label">Execution Time</p>
                                            <p class="{'status-green' if execution_time < 1000 else 'status-red'}">
                                                {execution_time:,}ms
                                            </p>
                                        </div>
                                        """,
                                        unsafe_allow_html=True,
                                    )
                                with col2:
                                    st.markdown(
                                        f"""
                                        <div class="metric-card">
                                            <p class="metric-label">Bytes Scanned</p>
                                            <p>{sample.get('bytes_scanned', 0):,}</p>
                                        </div>
                                        """,
                                        unsafe_allow_html=True,
                                    )
                                with col3:
                                    st.markdown(
                                        f"""
                                        <div class="metric-card">
                                            <p class="metric-label">Warehouse Size</p>
                                            <p>{sample.get('warehouse_size', 'N/A')}</p>
                                        </div>
                                        """,
                                        unsafe_allow_html=True,
                                    )

                                if "bytes_spilled_to_local_storage" in sample:
                                    col4, col5 = st.columns(2)
                                    with col4:
                                        st.markdown(
                                            f"""
                                            <div class="metric-card">
                                                <p class="metric-label">Local Spill</p>
                                                <p>{sample.get('bytes_spilled_to_local_storage', 0):,} bytes</p>
                                            </div>
                                            """,
                                            unsafe_allow_html=True,
                                        )
                                    with col5:
                                        st.markdown(
                                            f"""
                                            <div class="metric-card">
                                                <p class="metric-label">Remote Spill</p>
                                                <p>{sample.get('bytes_spilled_to_remote_storage', 0):,} bytes</p>
                                            </div>
                                            """,
                                            unsafe_allow_html=True,
                                        )

                                st.info(f"Started at: {sample.get('start_time', 'N/A')}")
                    else:
                        st.info("No sample queries available for this metric.")

                # Recommendations Section
                if "RECOMMENDATIONS" in user_data and user_data["RECOMMENDATIONS"]:
                    st.subheader("💡 Recommendations")
                    recommendations = user_data["RECOMMENDATIONS"]
                    if isinstance(recommendations, str):
                        with st.spinner("Parsing recommendations..."):
                            try:
                                recommendations = ast.literal_eval(recommendations)
                            except Exception as e:
                                st.error(f"Failed to parse recommendations: {e}")
                                recommendations = []

                    for rec in recommendations:
                        st.markdown(
                            f"""
                            <div class="recommendation-card" role="region" aria-label="Recommendation">
                                <strong>💡 {rec}</strong>
                            </div>
                            """,
                            unsafe_allow_html=True,
                        )









        # Interactive table
        st.markdown("---")
        st.header("👥 User Analytics Table")
        st.markdown("*Click on any metric cell to view detailed query information*")

        display_df = df.copy()
        display_df["TOTAL_CREDITS"] = display_df["TOTAL_CREDITS"].apply(
            lambda x: f"${x:.2f}"
        )
        display_df["AVG_EXECUTION_TIME_MS"] = display_df["AVG_EXECUTION_TIME_MS"].apply(
            lambda x: f"{x:.1f}ms"
        )
        display_df["TOTAL_DATA_SCANNED_GB"] = display_df["TOTAL_DATA_SCANNED_GB"].apply(
            lambda x: f"{x:.2f}GB"
        )
        display_df["FAILURE_CANCELLATION_RATE_PCT"] = display_df[
            "FAILURE_CANCELLATION_RATE_PCT"
        ].apply(lambda x: f"{x:.2f}%")

        clickable_columns = [
            "SPILLED_QUERIES",
            "OVER_PROVISIONED_QUERIES",
            "PEAK_HOUR_LONG_RUNNING_QUERIES",
            "SELECT_STAR_QUERIES",
            "UNPARTITIONED_SCAN_QUERIES",
            "REPEATED_QUERIES",
            "COMPLEX_JOIN_QUERIES",
            "ZERO_RESULT_QUERIES",
            "HIGH_COMPILE_QUERIES",
            "UNTAGGED_QUERIES",
            "UNLIMITED_ORDER_BY_QUERIES",
            "LARGE_GROUP_BY_QUERIES",
            "SLOW_QUERIES",
            "EXPENSIVE_DISTINCT_QUERIES",
            "INEFFICIENT_LIKE_QUERIES",
            "NO_RESULTS_WITH_SCAN_QUERIES",
            "CARTESIAN_JOIN_QUERIES",
            "HIGH_COMPILE_RATIO_QUERIES",
        ]

        # Table container
        with st.container():
            st.markdown('<div class="table-container">', unsafe_allow_html=True)

            # Header row
            st.markdown('<div class="table-header table-row">', unsafe_allow_html=True)
            header_cols = st.columns([2.5] + [1] * len(clickable_columns) + [1.2, 1.2])
            with header_cols[0]:
                st.markdown(
                    '<div class="table-cell user-cell">User Name</div>',
                    unsafe_allow_html=True,
                )
            for i, col in enumerate(clickable_columns):
                with header_cols[i + 1]:
                    st.markdown(
                        f'<div class="table-cell">{col.replace("_", " ").title()}</div>',
                        unsafe_allow_html=True,
                    )
            with header_cols[-2]:
                st.markdown(
                    '<div class="table-cell score-cell">Weighted Score</div>',
                    unsafe_allow_html=True,
                )
            with header_cols[-1]:
                st.markdown(
                    '<div class="table-cell status-cell">Cost Status</div>',
                    unsafe_allow_html=True,
                )
            st.markdown("</div>", unsafe_allow_html=True)

            # Data rows
            for idx, row in display_df.iterrows():
                st.markdown('<div class="table-row">', unsafe_allow_html=True)
                row_cols = st.columns([2.5] + [1] * len(clickable_columns) + [1.2, 1.2])

                with row_cols[0]:
                    st.markdown(
                        f'<div class="table-cell user-cell">{row["USER_NAME"]}</div>',
                        unsafe_allow_html=True,
                    )

                for i, col in enumerate(clickable_columns):
                    with row_cols[i + 1]:
                        if st.button(f"{row[col]}", key=f"{idx}_{col}"):
                            st.session_state.selected_user = row["USER_NAME"]
                            st.session_state.selected_metric = col
                            # Scroll to query details
                            st.markdown(
                                """
                                <script>
                                    document.querySelector('.query-detail-card').scrollIntoView({ 
                                        behavior: 'smooth', 
                                        block: 'start' 
                                    });
                                </script>
                                """,
                                unsafe_allow_html=True,
                            )
                            st.rerun()

                with row_cols[-2]:
                    st.markdown(
                        f'<div class="table-cell score-cell">{row["WEIGHTED_SCORE"]:.1f}</div>',
                        unsafe_allow_html=True,
                    )

                with row_cols[-1]:
                    status = row["COST_STATUS"]
                    color_class = {
                        "Normal": "status-normal",
                        "Warning": "status-warning",
                        "Critical": "status-critical",
                    }.get(status, "status-normal")
                    st.markdown(
                        f'<div class="table-cell status-cell"><span class="{color_class}">{status}</span></div>',
                        unsafe_allow_html=True,
                    )
                st.markdown("</div>", unsafe_allow_html=True)

            st.markdown("</div>", unsafe_allow_html=True)

        # Charts section
        st.markdown("---")
        st.header("📊 Analytics Charts")

        col1, col2 = st.columns(2)

        with col1:
            fig_scatter = px.scatter(
                df,
                x="TOTAL_QUERIES",
                y="TOTAL_CREDITS",
                hover_data=["USER_NAME", "WEIGHTED_SCORE"],
                title="Credits vs Total Queries",
                color="WEIGHTED_SCORE",
                size="TOTAL_DATA_SCANNED_GB",
            )
            fig_scatter.update_layout(height=400)
            st.plotly_chart(fig_scatter, use_container_width=True)

        with col2:
            status_counts = df["COST_STATUS"].value_counts()
            fig_pie = px.pie(
                values=status_counts.values,
                names=status_counts.index,
                title="Cost Status Distribution",
            )
            fig_pie.update_layout(height=400)
            st.plotly_chart(fig_pie, use_container_width=True)

    else:
        st.info(
            "No data available. Please upload JSON data using the sidebar to get started!"
        )
        st.markdown(
            """
        ### Expected JSON Format:
        Your JSON should contain user analytics data with the following structure:
        - USER_NAME
        - TOTAL_QUERIES
        - Various query metrics (SPILLED_QUERIES, SELECT_STAR_QUERIES, etc.)
        - QUERY_SAMPLES with detailed query information
        - RECOMMENDATIONS
        """
        )


# Sample data for testing

# Run the dashboard with sample data
if __name__ == "__main__":

    st.subheader("🔧 Filters")
    col1, col2 = st.columns(2)

    with col1:
        date_filter, custom_start, custom_end = CommonUI.render_date_filter()

    with col2:
        selected_user = CommonUI.render_object_filter("user", query_executor)

    # === PREPARE QUERY PARAMETERS ===
    # Get date range
    start_date, end_date = query_executor.get_date_range(
        date_filter, custom_start, custom_end
    )

    # Build object filter
    object_filter = QueryBuilder.build_object_filter("user", selected_user)
    print(f"this is the meeee {object_filter}")
    st.markdown("---")
    query_key = "user_all_quries"
    df = {}
    query = QueryBuilder.prepare_query(
        USER_360_QUERIES, query_key, start_date, end_date, object_filter
    )

    if not query:
        st.error(f"Failed to prepare query for {query_key}")
        # continue

    # Execute query
    try:
        data = query_executor._execute_single_query(
            query, {}, query_key  # Already formatted
        )
        df = data
    except Exception as e:
        st.error(f"Query execution failed for {query_key}: {str(e)}")

    # Analysis type selection
    s = len(df)
    # sample_data = load_sample_data()
    # df = pd.DataFrame(sample_data)


    create_snowflake_dashboard(df)