-- Function to get the last activity for a specific user
-- Returns the most recent activity (store login, task fixed, etc.)
--
-- Parameters:
--   p_user_id: UUID of the user
--   p_customer_id: Customer ID to filter activities (optional, if NULL returns all)
--
-- Returns: TABLE with last activity information

CREATE OR REPLACE FUNCTION get_last_user_activity(
    p_user_id UUID,
    p_customer_id INTEGER DEFAULT NULL
)
RETURNS TABLE (
    activity_type TEXT,
    item_id TEXT,
    activity_description TEXT,
    created_at TIMESTAMPTZ,
    store_code TEXT,
    store_name TEXT,
    task_id INTEGER,
    task_name TEXT
)
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        ua.type AS activity_type,
        ua.item_id,
        CASE 
            WHEN ua.type = 'store' THEN 'Logged into store: ' || COALESCE(s.name, ua.item_id)
            WHEN ua.type = 'task' THEN 'Fixed task: ' || COALESCE(t.name, ua.item_id)
            ELSE 'Activity: ' || COALESCE(ua.item_id, 'Unknown')
        END AS activity_description,
        ua.created_at,
        CASE 
            WHEN ua.type = 'store' THEN ua.item_id
            WHEN ua.type = 'task' THEN t.store_code
            ELSE NULL
        END AS store_code,
        CASE 
            WHEN ua.type = 'store' THEN s.name
            WHEN ua.type = 'task' THEN t.store_name
            ELSE NULL
        END AS store_name,
        CASE 
            WHEN ua.type = 'task' THEN t.id
            ELSE NULL
        END AS task_id,
        CASE 
            WHEN ua.type = 'task' THEN t.name
            ELSE NULL
        END AS task_name
    FROM user_activity ua
    LEFT JOIN store s ON ua.type = 'store' AND s.store_code = ua.item_id
    LEFT JOIN task t ON ua.type = 'task' AND t.id::text = ua.item_id
    WHERE ua.user_id = p_user_id
    AND (p_customer_id IS NULL OR ua.customer_id = p_customer_id)
    ORDER BY ua.created_at DESC
    LIMIT 1;
END;
$$;

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

-- Comment
COMMENT ON FUNCTION get_last_user_activity IS 'Returns the last activity for a user (store login, task fixed, etc.). Can filter by customer_id';

