CREATE OR REPLACE VIEW users_without_timesheet 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
),
users_without_timesheets AS (
    SELECT COUNT(DISTINCT u.id) AS total_count
    FROM users u
    WHERE NOT EXISTS (
        SELECT 1
        FROM time_sheet ts
        WHERE ts.user_id = u.id
        AND ts.week_id = (SELECT id FROM past_week)) AND u.role = 'admin' and u.azure_id is not null
),
reportees_without_timesheets AS (
    SELECT 
        u.id AS manager_id,
        COUNT(DISTINCT r.id) AS reportee_count
    FROM 
        users u
    LEFT JOIN 
        users r ON u.id = r.reporting_to
    LEFT JOIN 
        time_sheet ts ON r.id = ts.user_id AND ts.week_id = (SELECT id FROM past_week)
    WHERE 
        ts.id IS NULL
    GROUP BY 
        u.id
)
SELECT 
    u.id AS user_id,
    u.status_id AS status_id,
    u.company_id AS company_id,
    CASE 
        WHEN u.email IN ('venkatesha@techcedence.com', 'gobij@techcedence.com', 'krishnak@techcedence.com') 
        THEN (SELECT total_count FROM users_without_timesheets)
        ELSE COALESCE(r.reportee_count, 0)
    END AS user_reportees_count
FROM 
    users u
LEFT JOIN 
    reportees_without_timesheets r ON u.id = r.manager_id
ORDER BY 
    u.id;