new2

# user360.py

import streamlit as st
import pandas as pd
from datetime import date, timedelta
from typing import Dict, Any, List, Tuple, Optional
import numpy as np # For numerical operations like nansum

# Import the FinOps Dashboard API (assuming your framework is updated as per previous instructions)
from finops_framework.api import FinOpsDashboard
from finops_framework.exceptions import DataFetchError

# --- 1. Global Configurations (Reused for consistency) ---
# Ensure your finops_framework/ui/visual_renderers.py supports 'color_col' and 'sort_desc'
MY_APP_THEME = {
    "primary_color": "#00BFFF", # Deep sky blue
    "background_color": "#1C2833", # Dark slate gray
    "secondary_background_color": "#283747",
    "text_color": "#EAECEE",
    "font": "Segoe UI",
    "chart_height": 340, # Slightly taller charts
    "chart_colors": ["#00BFFF", "#FFD700", "#FF6347", "#3CB371", "#BA55D3", "#FFA07A", "#20B2AA", "#7B68EE", "#FF69B4", "#ADFF2F"]
}

MY_DATE_RANGE_OPTIONS = {
    "Last 7 Days": 7,
    "Last 30 Days": 30,
    "Last 90 Days": 90, # Default for initial load to get more data
    "Last 1 Year": 365,
    "Custom Range": None
}

# --- 2. SQL Query Definitions (User-Specific and Comprehensive) ---
# All user-specific queries will include a :selected_user_name parameter.
USER_360_SQL_QUERIES = {
    "LIST_ACTIVE_USERS_BY_COST": """
        SELECT
            USER_NAME,
            SUM(CREDITS_USED * 2) AS ESTIMATED_COST_USD
        FROM
            SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
        WHERE
            START_TIME >= DATEADD(day, -90, CURRENT_DATE()) -- Users active in last 90 days
        GROUP BY
            USER_NAME
        ORDER BY
            ESTIMATED_COST_USD DESC;
    """,
    "USER_360_SUMMARY_METRICS": """
        SELECT
            SUM(CASE WHEN START_TIME BETWEEN :start_date AND :end_date THEN CREDITS_USED ELSE 0 END) AS TOTAL_CREDITS,
            SUM(CASE WHEN START_TIME BETWEEN :start_date AND :end_date THEN CREDITS_USED * 2 ELSE 0 END) AS ESTIMATED_COST_USD,
            SUM(CASE WHEN START_TIME BETWEEN :prev_start_date AND :prev_end_date THEN CREDITS_USED ELSE 0 END) AS PREV_TOTAL_CREDITS,
            SUM(CASE WHEN START_TIME BETWEEN :prev_start_date AND :prev_end_date THEN CREDITS_USED * 2 ELSE 0 END) AS PREV_ESTIMATED_COST_USD,
            AVG(CASE WHEN START_TIME BETWEEN :start_date AND :end_date THEN TOTAL_ELAPSED_TIME ELSE NULL END) / 1000 AS AVG_QUERY_DURATION_SEC,
            AVG(CASE WHEN START_TIME BETWEEN :start_date AND :end_date THEN CREDITS_USED ELSE NULL END) AS AVG_CREDITS_PER_QUERY,
            COUNT(CASE WHEN START_TIME BETWEEN :start_date AND :end_date THEN QUERY_ID ELSE NULL END) AS TOTAL_QUERY_COUNT,
            COUNT(CASE WHEN START_TIME BETWEEN :start_date AND :end_date AND ERROR_MESSAGE IS NOT NULL THEN QUERY_ID ELSE NULL END) AS FAILED_QUERY_COUNT,
            SUM(CASE WHEN START_TIME BETWEEN :start_date AND :end_date THEN BYTES_SCANNED ELSE 0 END) AS TOTAL_BYTES_SCANNED,
            SUM(CASE WHEN START_TIME BETWEEN :start_date AND :end_date THEN COMPILATION_TIME ELSE 0 END) / 1000 AS TOTAL_COMPILATION_TIME_SEC
        FROM
            SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
        WHERE
            USER_NAME = :selected_user_name;
    """,
    "USER_DAILY_CREDIT_CONSUMPTION": """
        SELECT
            TO_DATE(START_TIME) AS USAGE_DAY,
            SUM(CREDITS_USED) AS DAILY_CREDITS_USED,
            SUM(CREDITS_USED * 2) AS DAILY_ESTIMATED_COST_USD
        FROM
            SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
        WHERE
            USER_NAME = :selected_user_name AND START_TIME BETWEEN :start_date AND :end_date
        GROUP BY
            1
        ORDER BY
            1;
    """,
    "USER_WAREHOUSE_COST_BREAKDOWN": """
        SELECT
            WAREHOUSE_NAME,
            SUM(CREDITS_USED * 2) AS ESTIMATED_COST_USD,
            SUM(CREDITS_USED) AS TOTAL_CREDITS
        FROM
            SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
        WHERE
            USER_NAME = :selected_user_name AND START_TIME BETWEEN :start_date AND :end_date
        GROUP BY
            WAREHOUSE_NAME
        ORDER BY
            ESTIMATED_COST_USD DESC
        LIMIT 10;
    """,
    "USER_ROLE_COST_BREAKDOWN": """
        SELECT
            ROLE_NAME,
            SUM(CREDITS_USED * 2) AS ESTIMATED_COST_USD,
            SUM(CREDITS_USED) AS TOTAL_CREDITS
        FROM
            SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
        WHERE
            USER_NAME = :selected_user_name AND START_TIME BETWEEN :start_date AND :end_date
        GROUP BY
            ROLE_NAME
        ORDER BY
            ESTIMATED_COST_USD DESC
        LIMIT 10;
    """,
    "USER_QUERY_TYPE_DISTRIBUTION": """
        SELECT
            QUERY_TYPE,
            COUNT(*) AS QUERY_COUNT,
            SUM(CREDITS_USED) AS TOTAL_CREDITS,
            SUM(CREDITS_USED * 2) AS ESTIMATED_COST_USD
        FROM
            SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
        WHERE
            USER_NAME = :selected_user_name AND START_TIME BETWEEN :start_date AND :end_date
        GROUP BY
            QUERY_TYPE
        ORDER BY
            ESTIMATED_COST_USD DESC
        LIMIT 10;
    """,
    "USER_TOP_EXPENSIVE_QUERIES": """
        SELECT
            QUERY_ID,
            QUERY_TEXT,
            WAREHOUSE_NAME,
            ROLE_NAME,
            TOTAL_ELAPSED_TIME / 1000 AS TOTAL_ELAPSED_TIME_SEC,
            CREDITS_USED,
            CREDITS_USED * 2 AS ESTIMATED_COST_USD,
            BYTES_SCANNED / POW(1024,3) AS BYTES_SCANNED_GB, -- Convert to GB
            BYTES_SPILLED_TO_LOCAL_STORAGE / POW(1024,3) AS LOCAL_SPILL_GB,
            BYTES_SPILLED_TO_REMOTE_STORAGE / POW(1024,3) AS REMOTE_SPILL_GB,
            START_TIME
        FROM
            SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
        WHERE
            USER_NAME = :selected_user_name AND START_TIME BETWEEN :start_date AND :end_date
            AND CREDITS_USED IS NOT NULL
        ORDER BY
            CREDITS_USED DESC
        LIMIT 20; -- Increased to 20 for more detailed review
    """,
    "USER_TOP_LONG_RUNNING_QUERIES": """
        SELECT
            QUERY_ID,
            QUERY_TEXT,
            WAREHOUSE_NAME,
            ROLE_NAME,
            TOTAL_ELAPSED_TIME / 1000 AS TOTAL_ELAPSED_TIME_SEC,
            EXECUTION_TIME / 1000 AS EXECUTION_TIME_SEC,
            QUEUED_OVERLOAD_TIME / 1000 AS QUEUED_OVERLOAD_TIME_SEC,
            COMPILATION_TIME / 1000 AS COMPILATION_TIME_SEC,
            CREDITS_USED,
            CREDITS_USED * 2 AS ESTIMATED_COST_USD,
            START_TIME
        FROM
            SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
        WHERE
            USER_NAME = :selected_user_name AND START_TIME BETWEEN :start_date AND :end_date
            AND TOTAL_ELAPSED_TIME IS NOT NULL
        ORDER BY
            TOTAL_ELAPSED_TIME DESC
        LIMIT 20; -- Increased to 20
    """,
    "USER_WAREHOUSE_UTILIZATION_OVERVIEW": """
        SELECT
            WH.WAREHOUSE_NAME,
            WH.SIZE,
            WH.AUTO_SUSPEND,
            SUM(WMH.CREDITS_USED) AS TOTAL_CREDITS,
            -- Calculate approximate idle time (if no credits used, but warehouse was on for this user)
            -- This is tricky for user-specific. Let's simplify: check if ANY of user's queries ran,
            -- and if the warehouse was generally idle by ACCOUNT_USAGE
            SUM(CASE WHEN WMH.CREDITS_USED = 0 AND WMH.WAREHOUSE_ID IN (SELECT DISTINCT WAREHOUSE_ID FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY WHERE USER_NAME = :selected_user_name AND START_TIME BETWEEN :start_date AND :end_date) THEN 1 ELSE 0 END) AS USER_INCURRED_IDLE_MINUTES, -- If user used it, and it was idle
            COUNT(WMH.WAREHOUSE_ID) AS TOTAL_MINUTES_ON_FOR_WAREHOUSE,
            (USER_INCURRED_IDLE_MINUTES * 100.0 / NULLIF(TOTAL_MINUTES_ON_FOR_WAREHOUSE, 0)) AS USER_INCURRED_IDLE_PERCENTAGE
        FROM
            SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSES WH
        JOIN
            SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY WMH
            ON WH.WAREHOUSE_ID = WMH.WAREHOUSE_ID
        WHERE
            WMH.START_TIME BETWEEN :start_date AND :end_date
            AND WMH.WAREHOUSE_ID IN (SELECT DISTINCT WAREHOUSE_ID FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY WHERE USER_NAME = :selected_user_name AND START_TIME BETWEEN :start_date AND :end_date) -- Only warehouses used by this user
        GROUP BY
            WH.WAREHOUSE_NAME, WH.SIZE, WH.AUTO_SUSPEND
        HAVING USER_INCURRED_IDLE_PERCENTAGE IS NOT NULL AND USER_INCURRED_IDLE_PERCENTAGE > 5 -- Only show warehouses with some user-related idle time
        ORDER BY USER_INCURRED_IDLE_PERCENTAGE DESC
        LIMIT 5;
    """
}

# --- 3. Metric Card Configurations (User-Specific and Comprehensive) ---
USER_360_METRIC_CARDS_CONFIG = [
    {
        "label": "Total Credits (User)",
        "query_id": "USER_360_SUMMARY_METRICS",
        "value_col": "TOTAL_CREDITS",
        "delta_col": "PREV_TOTAL_CREDITS",
        "format_value": "{:,.0f}",
        "format_delta": "{:+.2%}",
        "help_text": "Total Snowflake credits consumed by this user in the selected period."
    },
    {
        "label": "Estimated Cost (User)",
        "query_id": "USER_360_SUMMARY_METRICS",
        "value_col": "ESTIMATED_COST_USD",
        "delta_col": "PREV_ESTIMATED_COST_USD",
        "format_value": "${:,.2f}",
        "format_delta": "{:+.2%}",
        "help_text": "Estimated cost for this user (assuming $2/credit)."
    },
    {
        "label": "Avg. Query Duration",
        "query_id": "USER_360_SUMMARY_METRICS",
        "value_col": "AVG_QUERY_DURATION_SEC",
        "delta_col": None,
        "format_value": "{:,.1f} s",
        "help_text": "Average execution time of queries run by this user."
    },
    {
        "label": "Avg. Credits/Query",
        "query_id": "USER_360_SUMMARY_METRICS",
        "value_col": "AVG_CREDITS_PER_QUERY",
        "delta_col": None,
        "format_value": "{:,.3f}",
        "help_text": "Average credits consumed per query by this user. High value might indicate inefficient queries."
    },
    {
        "label": "Total Query Count",
        "query_id": "USER_360_SUMMARY_METRICS",
        "value_col": "TOTAL_QUERY_COUNT",
        "delta_col": None, # Could add prev_total_query_count if query supports it
        "format_value": "{:,.0f}",
        "help_text": "Total number of queries executed by this user."
    },
    {
        "label": "Failed Query Rate",
        "query_id": "USER_360_SUMMARY_METRICS",
        "value_col": "FAILED_QUERY_RATE", # Calculated in Python
        "delta_col": None,
        "format_value": "{:.1%}",
        "help_text": "Percentage of queries that failed for this user. High rate indicates issues."
    },
    {
        "label": "Total Data Scanned",
        "query_id": "USER_360_SUMMARY_METRICS",
        "value_col": "TOTAL_BYTES_SCANNED",
        "delta_col": None,
        "format_value": "{:,.2f} GB", # Will convert bytes to GB in Python
        "help_text": "Total data scanned by user's queries. High volume can lead to high cost."
    }
]

# --- 4. Chart Configurations (User-Specific and Organized by Tab) ---
USER_360_CHART_CONFIGS_BY_TAB = {
    "Cost & Usage Overview": [
        {
            "title": "User's Daily Credit Consumption Trend",
            "description": "Visualizes daily credit consumption by the selected user to identify personal trends.",
            "query_id": "USER_DAILY_CREDIT_CONSUMPTION",
            "chart_type": "line",
            "x_col": "USAGE_DAY",
            "y_col": "DAILY_CREDITS_USED",
            "x_axis_title": "Date",
            "y_axis_title": "Credits Used"
        },
        {
            "title": "User's Estimated Cost by Query Type",
            "description": "Breakdown of estimated costs by the types of queries run by this user (e.g., SELECT, DML, DDL).",
            "query_id": "USER_QUERY_TYPE_DISTRIBUTION",
            "chart_type": "bar",
            "x_col": "QUERY_TYPE",
            "y_col": "ESTIMATED_COST_USD",
            "x_axis_title": "Query Type",
            "y_axis_title": "Estimated Cost (USD)",
            "sort_desc": True # Sort by Y-axis descending
        }
    ],
    "Resource Utilization & Roles": [
        {
            "title": "User's Top 10 Warehouses by Estimated Cost",
            "description": "Warehouses where the user incurred the highest estimated costs. Consider if the right warehouse size/type is being used.",
            "query_id": "USER_WAREHOUSE_COST_BREAKDOWN",
            "chart_type": "bar",
            "x_col": "WAREHOUSE_NAME",
            "y_col": "ESTIMATED_COST_USD",
            "x_axis_title": "Warehouse Name",
            "y_axis_title": "Estimated Cost (USD)",
            "sort_desc": True
        },
        {
            "title": "User's Top 10 Roles by Estimated Cost",
            "description": "Snowflake roles the user frequently assumed that incurred the highest estimated costs. Review role privileges and usage patterns.",
            "query_id": "USER_ROLE_COST_BREAKDOWN",
            "chart_type": "bar",
            "x_col": "ROLE_NAME",
            "y_col": "ESTIMATED_COST_USD",
            "x_axis_title": "Role Name",
            "y_axis_title": "Estimated Cost (USD)",
            "sort_desc": True
        },
        {
            "title": "Underutilized Warehouses (Used by User)",
            "description": "Warehouses used by this user that exhibit significant idle time. Opportunities to adjust AUTO_SUSPEND.",
            "query_id": "USER_WAREHOUSE_UTILIZATION_OVERVIEW",
            "chart_type": "table" # Display as table for detailed review
        }
    ],
    "Query Performance Deep Dive": [
        {
            "title": "User's Top 20 Most Expensive Queries",
            "description": "The user's queries that consumed the most credits. Prioritize these for optimization, examining `QUERY_TEXT`.",
            "query_id": "USER_TOP_EXPENSIVE_QUERIES",
            "chart_type": "table"
        },
        {
            "title": "User's Top 20 Longest Running Queries",
            "description": "The user's queries with the longest execution times. Analyze `QUEUED_OVERLOAD_TIME` and `COMPILATION_TIME` for bottlenecks.",
            "query_id": "USER_TOP_LONG_RUNNING_QUERIES",
            "chart_type": "table"
        }
    ]
}


# --- 5. User-Specific Recommendation Logic (Enhanced) ---
def display_user_360_recommendations(dashboard_instance: FinOpsDashboard,
                                     sql_queries: Dict[str, str],
                                     start_date: date, end_date: date,
                                     selected_user_name: str,
                                     summary_data: pd.DataFrame): # Pass summary data for efficiency
    """Generates and displays comprehensive, actionable recommendations for the selected user."""
    st.markdown(f"### Actionable Insights for **`{selected_user_name}`**")
    st.markdown("---")

    recommendations_count = 0

    # Parameters for all user-specific queries
    params = {
        "start_date": start_date.isoformat(),
        "end_date": end_date.isoformat(),
        "selected_user_name": selected_user_name
    }

    # --- Recommendation 1: High Cost/Inefficient Queries ---
    try:
        expensive_queries_data = dashboard_instance._data_fetcher.execute_query(
            sql_queries["USER_TOP_EXPENSIVE_QUERIES"], params
        )
        if not expensive_queries_data.empty:
            top_cost = expensive_queries_data["ESTIMATED_COST_USD"].max()
            if top_cost > 5: # Threshold for a single "highly expensive" query (adjust as needed)
                recommendations_count += 1
                top_query = expensive_queries_data.iloc[0]
                st.markdown(f"<span style='color:#FF6347; font-weight:bold;'>&#9888; CRITICAL: Highly Expensive Query Detected!</span>", unsafe_allow_html=True)
                st.markdown(f"**Why it matters:** A single query (ID `{top_query['QUERY_ID']}`) cost **${top_query['ESTIMATED_COST_USD']:.2f}**, contributing significantly to your overall spend.")
                st.markdown(f"**Recommended Action:** Review the `QUERY_TEXT` for inefficiencies. Look for: large `BYTES_SCANNED_GB`, high `LOCAL_SPILL_GB`/`REMOTE_SPILL_GB`. Consider: ")
                st.markdown(f"- **Optimizing joins/filters.**")
                st.markdown(f"- **Materialized Views/Clustering:** If querying frequently used large datasets.")
                st.markdown(f"- **Warehouse Size:** Is the warehouse (`{top_query['WAREHOUSE_NAME']}`) appropriate for this workload?")
                st.info(f"👉 **Potential Savings:** Up to {top_query['ESTIMATED_COST_USD']:.2f} if this query is optimized or runs less frequently.")
                st.info(f"📍 See **'Query Performance Deep Dive'** tab for 'User's Top 20 Most Expensive Queries'.")
                st.markdown("---")
            elif top_cost > 1: # Moderate
                 recommendations_count += 1
                 top_query = expensive_queries_data.iloc[0]
                 st.markdown(f"<span style='color:#FFD700; font-weight:bold;'>&#9889; HIGH: Moderately Expensive Query</span>", unsafe_allow_html=True)
                 st.markdown(f"**Why it matters:** Query ID `{top_query['QUERY_ID']}` cost **${top_query['ESTIMATED_COST_USD']:.2f}**. Repeated execution can add up.")
                 st.markdown(f"**Recommended Action:** Review query plan, check for unnecessary scans. Consider minor optimizations or scheduled runs during off-peak hours.")
                 st.info(f"📍 See **'Query Performance Deep Dive'** tab for 'User's Top 20 Most Expensive Queries'.")
                 st.markdown("---")

    except DataFetchError as e:
        st.error(f"Error fetching data for expensive query recommendation: {e}")

    # --- Recommendation 2: High Failed Query Rate ---
    if not summary_data.empty:
        total_queries = summary_data["TOTAL_QUERY_COUNT"].iloc[0]
        failed_queries = summary_data["FAILED_QUERY_COUNT"].iloc[0]
        failed_rate = (failed_queries / total_queries) if total_queries > 0 else 0

        if failed_rate > 0.15 and total_queries > 10: # More than 15% failed, and at least 10 queries
            recommendations_count += 1
            st.markdown(f"<span style='color:#FF6347; font-weight:bold;'>&#9888; CRITICAL: High Failed Query Rate ({failed_rate:.1%})</span>", unsafe_allow_html=True)
            st.markdown(f"**Why it matters:** A high failure rate indicates consistent issues with queries, potentially wasting compute resources or indicating data pipeline problems.")
            st.markdown(f"**Recommended Action:** Investigate the specific queries that are failing. Common reasons include: syntax errors, permission issues, missing data, or timeouts. Review your recent query history for error messages.")
            st.info(f"📍 See Snowflake's `QUERY_HISTORY` for detailed error messages associated with failed queries.")
            st.markdown("---")

    # --- Recommendation 3: Underutilized Warehouses (Used by User) ---
    try:
        underutilized_wh_data = dashboard_instance._data_fetcher.execute_query(
            sql_queries["USER_WAREHOUSE_UTILIZATION_OVERVIEW"], params
        )
        if not underutilized_wh_data.empty:
            for index, row in underutilized_wh_data.iterrows():
                idle_percent = row['USER_INCURRED_IDLE_PERCENTAGE']
                wh_name = row['WAREHOUSE_NAME']
                current_suspend = row['AUTO_SUSPEND']
                user_incurred_credits = row['TOTAL_CREDITS'] # Credits incurred by this user on this warehouse

                if idle_percent > 20 and user_incurred_credits > 0: # Significant idle time for user-related activity
                    recommendations_count += 1
                    st.markdown(f"<span style='color:#FFD700; font-weight:bold;'>&#9889; HIGH: Underutilized Warehouse ({wh_name})</span>", unsafe_allow_html=True)
                    st.markdown(f"**Why it matters:** You've used warehouse `{wh_name}`, which shows **{idle_percent:.1f}%** idle time (based on your activity). This means it's running but not doing work, costing credits.")
                    st.markdown(f"**Recommended Action:** Consider adjusting its `AUTO_SUSPEND` parameter. Current: `{current_suspend}` seconds. A lower value (e.g., 60 or 120 seconds) for this warehouse could save credits. If you don't control this warehouse, discuss with the administrator.")
                    # Rough potential savings (very simplified)
                    estimated_idle_cost = (user_incurred_credits * 2) * (idle_percent / 100)
                    st.info(f"👉 **Potential Savings:** Estimated ${estimated_idle_cost:.2f} over the period by reducing idle time.")
                    st.info(f"📍 See **'Resource Utilization & Roles'** tab for 'Underutilized Warehouses (Used by User)' table.")
                    st.markdown("---")
    except DataFetchError as e:
        st.error(f"Error fetching data for underutilized warehouse recommendation: {e}")


    # --- Recommendation 4: Queries with High Disk Spilling ---
    try:
        expensive_queries_data = dashboard_instance._data_fetcher.execute_query( # Re-using this query
            sql_queries["USER_TOP_EXPENSIVE_QUERIES"], params
        )
        spill_queries = expensive_queries_data[(expensive_queries_data['LOCAL_SPILL_GB'] > 0.1) | (expensive_queries_data['REMOTE_SPILL_GB'] > 0.1)] # Threshold > 0.1GB spill

        if not spill_queries.empty:
            recommendations_count += 1
            st.markdown(f"<span style='color:#3CB371; font-weight:bold;'>&#128161; MEDIUM: Queries with Data Spilling</span>", unsafe_allow_html=True)
            st.markdown(f"**Why it matters:** When queries spill data to local or remote disk, it means the warehouse didn't have enough memory, slowing down queries and potentially increasing costs.")
            st.markdown(f"**Recommended Action:** Review the top {len(spill_queries)} queries with spilling (e.g., query ID `{spill_queries.iloc[0]['QUERY_ID']}`). Consider:")
            st.markdown(f"- **Optimizing query logic:** Reduce data returned, avoid full table scans, use more selective filters.")
            st.markdown(f"- **Temporary Warehouse Upsizing:** For very large or complex queries, consider running them on a larger warehouse size if permitted.")
            st.info(f"📍 See **'Query Performance Deep Dive'** tab for 'User's Top 20 Most Expensive Queries' (check spill columns).")
            st.markdown("---")
    except DataFetchError as e:
        st.error(f"Error fetching data for spill recommendation: {e}")

    # --- Recommendation 5: Consistently Long Running Queries (not necessarily expensive) ---
    try:
        long_running_queries_data = dashboard_instance._data_fetcher.execute_query(
            sql_queries["USER_TOP_LONG_RUNNING_QUERIES"], params
        )
        if not long_running_queries_data.empty and long_running_queries_data["TOTAL_ELAPSED_TIME_SEC"].max() > 120: # Threshold for long-running > 2 minutes
            recommendations_count += 1
            top_long_query = long_running_queries_data.iloc[0]
            st.markdown(f"<span style='color:#3CB371; font-weight:bold;'>&#128161; MEDIUM: Long-Running Query Identified</span>", unsafe_allow_html=True)
            st.markdown(f"**Why it matters:** Query ID `{top_long_query['QUERY_ID']}` ran for **{top_long_query['TOTAL_ELAPSED_TIME_SEC']:.1f} seconds**. While not always highest cost, long-running queries can indicate performance bottlenecks and user frustration.")
            st.markdown(f"**Recommended Action:** Analyze the query profile. Pay attention to: ")
            st.markdown(f"- `QUEUED_OVERLOAD_TIME_SEC`: Indicates warehouse concurrency issues. Consider a larger warehouse or splitting workload.")
            st.markdown(f"- `COMPILATION_TIME_SEC`: If high, the query itself is complex or metadata is slow. Optimize logic or review schema.")
            st.info(f"📍 See **'Query Performance Deep Dive'** tab for 'User's Top 20 Longest Running Queries'.")
            st.markdown("---")
    except DataFetchError as e:
        st.error(f"Error fetching data for long-running query recommendation: {e}")

    # Fallback if no specific recommendations found
    if recommendations_count == 0:
        st.success("🎉 **Great job!** No immediate high-priority optimization opportunities detected for this user in the selected period. Keep up the efficient work!")
        st.markdown("*(Recommendations are based on specific thresholds and available data. For deeper insights, explore the other tabs.)*")
    st.markdown("---")


# --- 6. Main User Dashboard Run Function ---
def run_user_360_dashboard():
    # Initialize the dashboard framework
    dashboard = FinOpsDashboard(
        app_title="Snowflake User 360 FinOps View",
        app_subtitle="Deep Dive into User Consumption, Performance, and Optimization",
        theme_config=MY_APP_THEME
    )

    # --- Sidebar Controls ---
    st.sidebar.header("Filter & User Selection")

    # 1. User Selection (Most important for this page)
    selected_user_name: Optional[str] = None
    try:
        # Fetch list of active users, ordered by cost for a better default selection
        active_users_df = dashboard._data_fetcher.execute_query(USER_360_SQL_QUERIES["LIST_ACTIVE_USERS_BY_COST"], {})

        if active_users_df.empty:
            st.warning("No active users found in the last 90 days. Please ensure your Snowflake ACCOUNT_USAGE is populated and user activity exists.")
            st.stop() # Halts execution for no users

        user_names = active_users_df["USER_NAME"].tolist()

        # Default to the user with the highest cost
        default_user_index = 0 
        if not active_users_df.empty:
            highest_cost_user = active_users_df.iloc[0]["USER_NAME"]
            if highest_cost_user in user_names:
                default_user_index = user_names.index(highest_cost_user)

        selected_user_name = st.sidebar.selectbox(
            "Select User:",
            user_names,
            index=default_user_index,
            help="Choose a Snowflake user to view their FinOps insights. Users ordered by estimated cost."
        )

    except DataFetchError as e:
        st.error(f"Error fetching user list: {e}. Please check Snowflake connection and query permissions.")
        st.stop() # Halts execution on data fetch error for user list
    except Exception as e:
        st.error(f"An unexpected error occurred while setting up user selection: {e}")
        st.exception(e)
        st.stop()

    if not selected_user_name: # Defensive check
        st.warning("No user selected or available.")
        st.stop()


    # 2. Date Range Selector (in sidebar)
    start_date, end_date = dashboard.render_date_filter(
        MY_DATE_RANGE_OPTIONS, default_key="Last 90 Days" # Default to 90 days for more data
    )

    if start_date is None or end_date is None:
        st.warning("Please select a valid date range to load data.")
        st.stop() # Halts execution if dates are invalid

    st.markdown(f"## User 360 View: **`{selected_user_name}`**")
    st.markdown("---")

    # --- Main Tabbed Layout ---
    tab_titles = ["FinOps Action Center", "Cost & Usage Overview",
                  "Resource Utilization & Roles", "Query Performance Deep Dive"]

    selected_tab_idx = st.tabs(tab_titles)

    # Prepare base parameters for all user-specific queries
    base_params = {
        "start_date": start_date.isoformat(),
        "end_date": end_date.isoformat(),
        "selected_user_name": selected_user_name
    }

    # Fetch summary data once and pass to relevant sections for efficiency
    summary_data_params = base_params.copy()
    prev_start_date, prev_end_date = dashboard._data_fetcher.calculate_prev_period_dates(start_date, end_date)
    summary_data_params["prev_start_date"] = prev_start_date.isoformat()
    summary_data_params["prev_end_date"] = prev_end_date.isoformat()

    user_summary_df: pd.DataFrame = pd.DataFrame()
    try:
        user_summary_df = dashboard._data_fetcher.execute_query(
            USER_360_SQL_QUERIES["USER_360_SUMMARY_METRICS"], summary_data_params
        )
    except DataFetchError as e:
        st.error(f"Error fetching user summary metrics: {e}")
        st.info("Some sections may not display correctly due to missing summary data.")
    except Exception as e:
        st.error(f"An unexpected error occurred fetching user summary metrics: {e}")
        st.exception(e)


    with selected_tab_idx[0]: # FinOps Action Center Tab
        display_user_360_recommendations(dashboard, USER_360_SQL_QUERIES, start_date, end_date, selected_user_name, user_summary_df)

    with selected_tab_idx[1]: # Cost & Usage Overview Tab
        dashboard.display_section_header("User's Performance & Cost Summary", "Key metrics and trends for the selected user's activity.")

        # --- Metric Cards for User Summary ---
        updated_user_metrics = USER_360_METRIC_CARDS_CONFIG.copy()
        if not user_summary_df.empty:
            summary_row = user_summary_df.iloc[0]
            for config in updated_user_metrics:
                current_val = summary_row.get(config["value_col"])
                config["value"] = current_val

                if config["label"] == "Total Data Scanned": # Convert bytes to GB
                    config["value"] = (current_val / (1024**3)) if current_val is not None else 0
                elif config["label"] == "Failed Query Rate":
                    total_q = summary_row.get("TOTAL_QUERY_COUNT", 0)
                    failed_q = summary_row.get("FAILED_QUERY_COUNT", 0)
                    config["value"] = (failed_q / total_q) if total_q > 0 else 0
                else: # For other metrics, handle delta
                    if config.get("delta_col") and summary_row.get(config["delta_col"]) is not None:
                        prev_val = summary_row[config["delta_col"]]
                        if prev_val is not None and prev_val != 0:
                            config["delta"] = ((current_val - prev_val) / prev_val) * 100
                        else:
                            config["delta"] = None if current_val == 0 else "N/A"
                    else:
                        config["delta"] = None
        else: # If no data for user, set all to 0/N/A
            for config in updated_user_metrics:
                config["value"] = 0
                config["delta"] = None
                config["help_text"] = "No data for this user in selected period."

        # Render metrics
        cols = st.columns(len(updated_user_metrics))
        for i, config in enumerate(updated_user_metrics):
            with cols[i]:
                dashboard._visual_renderer.render_metric_card(
                    config,
                    config.get("value", "N/A"),
                    config.get("delta")
                )

        # --- Charts for User Overview ---
        dashboard.display_charts_section(
            section_title="", # Empty, as header is above
            chart_configs=USER_360_CHART_CONFIGS_BY_TAB["Cost & Usage Overview"],
            sql_queries=USER_360_SQL_QUERIES, # Use user-specific queries
            current_start_date=start_date,
            current_end_date=end_date,
            extra_query_params={"selected_user_name": selected_user_name} # Pass the user parameter
        )

    with selected_tab_idx[2]: # Resource Utilization & Roles Tab
        dashboard.display_section_header("User's Resource & Role Utilization", "Insights into which warehouses and roles drive the user's costs.")
        dashboard.display_charts_section(
            section_title="",
            chart_configs=USER_360_CHART_CONFIGS_BY_TAB["Resource Utilization & Roles"],
            sql_queries=USER_360_SQL_QUERIES,
            current_start_date=start_date,
            current_end_date=end_date,
            extra_query_params={"selected_user_name": selected_user_name}
        )

    with selected_tab_idx[3]: # Query Performance Deep Dive Tab
        dashboard.display_section_header("User's Query Performance & Optimization Details", "Detailed analysis of expensive and slow queries for targeted tuning.")
        dashboard.display_charts_section(
            section_title="",
            chart_configs=USER_360_CHART_CONFIGS_BY_TAB["Query Performance Deep Dive"],
            sql_queries=USER_360_SQL_QUERIES,
            current_start_date=start_date,
            current_end_date=end_date,
            extra_query_params={"selected_user_name": selected_user_name}
        )


if __name__ == "__main__":
    run_user_360_dashboard()