CREATE OR REPLACE VIEW team_members_count AS
WITH total_team_count AS (
    SELECT COUNT(DISTINCT u.id) AS total_count
    FROM users u 
    WHERE u.azure_id is not null AND u.role = 'admin'
)
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 total_team_count)
        ELSE (SELECT count(id) FROM users us WHERE u.id = us.reporting_to)
    END AS user_reportees_count
FROM 
    users u
ORDER BY 
    u.id;