-- Function to get currently active users from both app_user and user tables
-- Returns users who are:
--   1. Currently logged in (have active Supabase auth sessions)
--   2. Were active within the last 15 minutes
--   3. Linked to the specified customer_id (if provided)
--
-- Parameters:
--   p_active_minutes: Number of minutes to consider as "active" (default: 15)
--   p_customer_id: Customer ID to filter users (optional, if NULL returns all)
--
-- Returns: TABLE with active user information

CREATE OR REPLACE FUNCTION get_active_users(
    p_active_minutes INTEGER DEFAULT 15,
    p_customer_id INTEGER DEFAULT NULL
)
RETURNS TABLE (
    user_id UUID,
    full_name TEXT,
    email TEXT,
    role TEXT,
    platform TEXT,
    last_active TIMESTAMP,
    profile_image_url TEXT,
    is_currently_logged_in BOOLEAN
)
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
    RETURN QUERY
    WITH active_sessions AS (
        -- Get all users with active Supabase auth sessions
        -- Sessions are active if not_after is NULL (no expiration) or not_after is in the future
        SELECT DISTINCT s.user_id
        FROM auth.sessions s
        WHERE (s.not_after IS NULL OR s.not_after > NOW())
    ),
    app_users_active AS (
        -- Get active app users (mobile app)
        SELECT 
            au.id AS user_id,
            COALESCE(au.full_name, CONCAT(au.first_name, ' ', au.last_name)) AS full_name,
            au.email,
            au.role,
            'app'::TEXT AS platform,
            au.active_last::TIMESTAMP AS last_active,
            au.profile_image_url,
            EXISTS(SELECT 1 FROM active_sessions WHERE active_sessions.user_id = au.id) AS is_currently_logged_in
        FROM app_user au
        WHERE au.archived = 0
        AND (
            -- User has active session OR was active within last X minutes
            EXISTS(SELECT 1 FROM active_sessions WHERE active_sessions.user_id = au.id)
            OR (au.active_last IS NOT NULL AND au.active_last > NOW() - (p_active_minutes || ' minutes')::INTERVAL)
        )
        AND (
            -- Filter by customer_id if provided
            p_customer_id IS NULL 
            OR EXISTS(
                SELECT 1 FROM customer_user cu 
                WHERE cu.user_id = au.id 
                AND cu.customer_id = p_customer_id
            )
        )
    ),
    web_users_active AS (
        -- Get active web portal users
        SELECT 
            u.id AS user_id,
            COALESCE(u.full_name, CONCAT(u.first_name, ' ', u.last_name)) AS full_name,
            u.email,
            u.role,
            'web'::TEXT AS platform,
            u.updated_at::TIMESTAMP AS last_active,
            u.profile_img AS profile_image_url,
            EXISTS(SELECT 1 FROM active_sessions WHERE active_sessions.user_id = u.id) AS is_currently_logged_in
        FROM "user" u
        WHERE u.active IS NOT NULL
        AND (
            -- User has active session OR was active within last X minutes
            EXISTS(SELECT 1 FROM active_sessions WHERE active_sessions.user_id = u.id)
            OR (u.updated_at IS NOT NULL AND u.updated_at > NOW() - (p_active_minutes || ' minutes')::INTERVAL)
        )
        AND (
            -- Filter by customer_id if provided
            p_customer_id IS NULL 
            OR EXISTS(
                SELECT 1 FROM customer_user cu 
                WHERE cu.user_id = u.id 
                AND cu.customer_id = p_customer_id
            )
        )
    )
    -- Combine results from both tables
    SELECT 
        COALESCE(au.user_id, wu.user_id) AS user_id,
        COALESCE(au.full_name, wu.full_name) AS full_name,
        COALESCE(au.email, wu.email) AS email,
        COALESCE(au.role, wu.role) AS role,
        COALESCE(au.platform, wu.platform) AS platform,
        COALESCE(au.last_active, wu.last_active) AS last_active,
        COALESCE(au.profile_image_url, wu.profile_image_url) AS profile_image_url,
        COALESCE(au.is_currently_logged_in, wu.is_currently_logged_in) AS is_currently_logged_in
    FROM app_users_active au
    FULL OUTER JOIN web_users_active wu ON au.user_id = wu.user_id
    ORDER BY 
        COALESCE(au.is_currently_logged_in, wu.is_currently_logged_in) DESC,  -- Currently logged in users first
        COALESCE(au.last_active, wu.last_active) DESC;              -- Then by most recently active
END;
$$;

-- Grant execute permission to authenticated users
GRANT EXECUTE ON FUNCTION get_active_users(INTEGER, INTEGER) TO authenticated;

-- Comment
COMMENT ON FUNCTION get_active_users IS 'Returns list of active users from both app_user (mobile) and user (web portal) tables. Includes users with active sessions or activity within specified minutes. Can filter by customer_id';

