CREATE OR REPLACE VIEW previous_week_timesheet_status AS
WITH previous_week AS (
    SELECT id 
    FROM yearly_week 
    WHERE is_current_year = true AND EXTRACT(WEEK FROM start_date) = EXTRACT(WEEK FROM CURRENT_DATE - interval '1 week') -- This gets the past week
    ORDER BY start_date DESC 
    LIMIT 1
)

SELECT 
    u.id AS user_id, -- User ID
    u.reporting_to AS rep_id, -- Reporting manager ID
    -- Check if the user has a timesheet (is_not_created)
    CASE 
        WHEN ts.id IS NULL THEN true 
        ELSE false 
    END AS is_not_created,
    -- Check if the timesheet is unsubmitted (is_not_submitted)
    CASE 
        WHEN ts.id IS NOT NULL AND ts.is_saved = false THEN true
        ELSE false 
    END AS is_not_submitted,
    -- Check if the timesheet is submitted (is_submitted)
    CASE 
        WHEN ts.id IS NOT NULL AND ts.is_saved = true THEN true
        ELSE false 
    END AS is_submitted
FROM 
    users u
LEFT JOIN 
    time_sheet ts ON u.id = ts.user_id 
    AND ts.week_id = (SELECT id FROM previous_week)
ORDER BY 
    u.id;