CREATE OR REPLACE VIEW user_timesheet_status AS
WITH past_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, 
    CASE WHEN ts.id IS NOT NULL AND ts.is_saved = true THEN true ELSE false END AS is_submitted,
    CASE WHEN ts.id IS NOT NULL AND ts.is_saved = false THEN true ELSE false END AS is_drafted,
    CASE WHEN ts.id IS NULL THEN true ELSE false END AS is_not_created, 
    COALESCE(
        TO_CHAR(
            FLOOR(SUM(EXTRACT(EPOCH FROM tl.hours::INTERVAL))/3600), 'FM99'
        ) || 'h ' || 
        TO_CHAR(
            MOD(FLOOR(SUM(EXTRACT(EPOCH FROM tl.hours::INTERVAL)/60)), 60), 'FM00'
        ) || 'm',
        '0h 00m'
    ) AS total_hours,
    CASE
      WHEN ts.process_status is null THEN 'Not Submitted'
      WHEN ts.process_status = 'under_approval' THEN 'Submitted'
      WHEN ts.process_status = 'submitted' THEN 'Draft'
      ELSE ts.process_status::TEXT
    END AS approval_status
FROM 
    users u
LEFT JOIN 
    time_sheet ts ON u.id = ts.user_id AND ts.week_id = (SELECT id FROM past_week)
LEFT JOIN 
    time_logs tl ON tl.timesheet_id = ts.id
GROUP BY 
    u.id, ts.id, ts.is_saved;