"""
SQL query building functions for employee workstation activity tracking.

This module contains functions for building SQL queries to calculate working durations.
"""
from flask import current_app

def calculate_duration_sql(time_period):
    """
    Generates the core SQL query to calculate working durations.
    Uses LEAD() window function to pair 'working' with the next event.
    Calculates duration in hours using PostgreSQL functions.
    
    Args:
        time_period (str): Time period to group by ('daily', 'weekly', or 'monthly')
        
    Returns:
        str: SQL query for calculating durations
    """
    # PostgreSQL date functions (already compatible with the database)
    period_grouping = {
        'daily': "DATE_TRUNC('day', start_time)",
        'weekly': "DATE_TRUNC('week', start_time)", # PostgreSQL week starts Monday
        'monthly': "DATE_TRUNC('month', start_time)"
    }.get(time_period, "DATE_TRUNC('day', start_time)") # Default to daily if invalid

    # Calculate duration using EXTRACT(EPOCH FROM interval) / 3600 for hours
    duration_calculation = "EXTRACT(EPOCH FROM (next_event_time - start_time)) / 3600.0"

    # Use public schema explicitly, ensure proper aggregation by user and period
    sql_query = f"""
        WITH EventPairs AS (
            SELECT
                "user",
                ts AS start_time,
                state,
                LEAD(ts) OVER (PARTITION BY "user" ORDER BY ts) AS next_event_time,
                LEAD(state) OVER (PARTITION BY "user" ORDER BY ts) AS next_event_state
            FROM public.work_events
            ORDER BY "user", ts
        ),
        CalculatedDurations AS (
            SELECT
                "user",
                {period_grouping} AS period_start,
                SUM(
                    CASE
                        WHEN state = 'working' AND next_event_time IS NOT NULL THEN
                            {duration_calculation}
                        ELSE 0 -- Ignore intervals starting with 'stopped' or without a following event
                    END
                ) AS total_hours,
                MIN(CASE WHEN state = 'working' THEN start_time END) AS first_login_time
            FROM EventPairs
            WHERE state = 'working' -- Only consider intervals that start with 'working'
            GROUP BY "user", period_start
        )
        -- Final aggregation to ensure one row per user per period
        SELECT 
            "user",
            period_start,
            SUM(total_hours) AS total_hours,
            MIN(first_login_time) AS first_login_time
        FROM CalculatedDurations
        GROUP BY "user", period_start
        ORDER BY "user", period_start DESC;
    """
    
    # Add debug logging to see the SQL query
    current_app.logger.info(f"Generated SQL query: {sql_query}")
    
    return sql_query

def filter_sql_by_user(base_sql, user):
    """
    Applies a user filter to the SQL query safely.
    
    Args:
        base_sql (str): The base SQL query to modify
        user (str): Username to filter by
        
    Returns:
        str: SQL query with user filter applied
    """
    # Find the position of GROUP BY to insert WHERE clause correctly
    group_by_pos = base_sql.find("GROUP BY")
    if group_by_pos != -1:
        # Make the user filter case-insensitive using LOWER()
        where_clause = "WHERE state = 'working' AND LOWER(\"user\") = LOWER(:user)\n        "
        # Replace the generic WHERE clause with the user-specific one
        filtered_sql = base_sql[:base_sql.find("WHERE")] + where_clause + base_sql[group_by_pos:]
        return filtered_sql
    else:
        # Should not happen with the current query structure, but handle defensively
        return base_sql # Return original if GROUP BY not found