
-- Create User Report View
CREATE OR REPLACE VIEW user_reportees AS
WITH RECURSIVE reportees AS (
    -- Base case: only admins
    SELECT 
        u.id AS root_id,
        u.id,
        u.username,
        u.reporting_to,
        NULL::TEXT AS reporting_to_name,
        u.uuid,
        u.company_id,
        u.status_id,
        u.role
    FROM users u
    WHERE u.role = 'admin'

    UNION ALL

    -- Recursive step: only admins
    SELECT 
        r.root_id,
        u.id,
        u.username,
        u.reporting_to,
        m.username AS reporting_to_name,
        u.uuid,
        u.company_id,
        u.status_id,
        u.role
    FROM users u
    INNER JOIN reportees r ON u.reporting_to = r.id
    LEFT JOIN users m ON u.reporting_to = m.id
    WHERE u.role = 'admin'
),

-- Fetch special manager IDs by email
super_managers AS (
    SELECT id AS user_id FROM users 
    WHERE email IN ('venkatesha@techcedence.com', 'gobij@techcedence.com', 'krishnak@techcedence.com')
),

-- Add all admin users as reporting to each super manager (excluding self)
super_scope AS (
    SELECT 
        sm.user_id AS root_id,
        u.id,
        u.username,
        u.reporting_to,
        m.username AS reporting_to_name,
        u.uuid,
        u.company_id,
        u.status_id,
        u.role
    FROM super_managers sm
    JOIN users u ON u.id != sm.user_id
    LEFT JOIN users m ON u.reporting_to = m.id
    WHERE u.role = 'admin'
),

combined AS (
    SELECT * FROM reportees WHERE id != root_id
    UNION
    SELECT * FROM super_scope
)

SELECT DISTINCT ON (root_id, id) *
FROM combined;