WITH RECURSIVE q AS (
    SELECT
        d.web_url AS drive_path,
        di.id AS item_id,
        di.ms365_id,
        di.scan_id,
        di.web_url,
        di.item_type,
        di.modify_time AS item_modify_time,
        di.create_time AS item_create_time,
        RIGHT(pp.web_url, length(pp.web_url) - length(d.web_url)) AS parent_path,
        di.name AS item_name,
        di.size AS item_size,
        udm.ms365_user_id
    FROM ms365.drives AS d
    JOIN ms365.drive_scans AS ds ON ds.drive_id = d.id AND ds.scan_state=1
    JOIN ms365.user_drives AS udm ON udm.ms365_drive_id = d.ms365_id
    JOIN ms365.drive_items AS pp ON pp.ms365_drive_id = d.ms365_id AND pp.ms365_parent_id = '' AND pp.scan_id = ds.id
    JOIN ms365.drive_items AS di ON di.ms365_parent_id = pp.ms365_id AND pp.scan_id = di.scan_id
    WHERE di.item_type = 2 AND di.name='Microsoft Teams Chat Files'
    
    UNION ALL
    
    SELECT
        d.web_url AS drive_path,
        di.id AS item_id,
        di.ms365_id,
        di.scan_id,
        di.web_url,
        di.item_type,
        di.modify_time AS item_modify_time,
        di.create_time AS item_create_time,
        RIGHT(q.web_url, length(q.web_url) - length(d.web_url)) AS parent_path,
        di.name AS item_name,
        di.size AS item_size,
        q.ms365_user_id
    FROM ms365.drives AS d
    JOIN ms365.drive_items AS di ON di.ms365_drive_id = d.ms365_id
    JOIN q ON di.ms365_parent_id = q.ms365_id AND di.scan_id = q.scan_id
),
p AS (
    SELECT
        q.item_id,
        q.drive_path,
        q.parent_path,
        q.item_name,
        q.item_size,
        q.item_modify_time,
        q.item_create_time,
        q.ms365_user_id,
        u.display_name AS user_display_name,
        u.upn AS user_principal_name,
        COUNT(*) OVER (PARTITION BY q.item_type, q.drive_path) AS items_count,
        SUM(q.item_size) OVER (PARTITION BY q.item_type, q.drive_path) AS items_size,
        ROW_NUMBER() OVER (PARTITION BY q.item_type, q.drive_path) AS items_rownum
    FROM q
    JOIN ms365.users AS u ON u.ms365_id = q.ms365_user_id
    WHERE q.item_type = 1
),
sh AS (
    SELECT
        q.item_id,
        count(*) AS sharing_link_count
    FROM q
    JOIN ms365.permissions AS p ON p.drive_item_id = q.item_id
    WHERE p.roles <> 'owner' AND p.roles <> ''
    GROUP BY q.item_id
),
r AS (
    SELECT
        p.*,
        DENSE_RANK() OVER (ORDER BY p.items_size DESC) AS items_size_rank,
        DENSE_RANK() OVER (ORDER BY p.items_count DESC) AS items_count_rank
    FROM p
),
s AS (
    SELECT
        ROW_NUMBER() OVER (ORDER BY r.items_rownum, r.items_size_rank ASC, r.drive_path) AS items_size_rank_rownum,
        ROW_NUMBER() OVER (ORDER BY r.items_rownum, r.items_count_rank ASC, r.drive_path) AS items_count_rank_rownum,
        r.*
    FROM r
)
SELECT
    s.*,
    COALESCE(sh.sharing_link_count, 0) AS share_count
FROM s
LEFT JOIN sh ON sh.item_id = s.item_id