SELECT
    volume.id AS "id",
    volume.name AS "vol",
    ROUND("total capacity" / POWER(1024.0, 4), 2) AS "capacity_TB",
    ROUND("volume occupied space" / POWER(1024.0, 4), 2) AS "used_physical_TB",
    ROUND("volume occupied space logical" / POWER(1024.0, 4), 2) AS "used_logical_TB",
    ROUND("volume free space" / POWER(1024.0, 4), 2) AS "free_TB",
    "volume occupied space %" AS "used_percent",
    "regular files" AS "regular_files",
    "median file size" AS "median_file_size_bytes",
    ROUND("average file size")::BIGINT AS "average_file_size_bytes"
FROM sf_reports.stats_current
    LEFT JOIN sf_volumes.volume AS volume ON stats_current."volume name" = volume.name

/* Return aggregate size of directories that correspond to storage allocations. */
SELECT 
    dirs.volume_id, 
    vol.name AS vol_name, 
    dirs.gid, 
    vol_group.name AS group_name, 
    dirs.uid, 
    vol_user.name AS user_name, 
    dirs.path AS path, 
    (dirs.rec_aggrs->>'size')::numeric AS total_size, 
    dirs.depth
FROM 
    sf.dir_current dirs
LEFT JOIN 
    sf_volumes.volume vol ON dirs.volume_id = vol.id
LEFT JOIN 
    sf.uid_mapping vol_user ON dirs.volume_id = vol_user.volume_id AND dirs.uid = vol_user.uid
LEFT JOIN 
    sf.gid_mapping vol_group ON dirs.volume_id = vol_group.volume_id AND dirs.gid = vol_group.gid
WHERE
    /* change these criteria to correspond to storage allocations' root directories */
    LENGTH(vol_group.name) > 1
    AND dirs.path SIMILAR TO 'LABS/%'
    AND dirs.depth = 2
ORDER BY 
    volume_id, path;

/*
Calculate usage per user per allocation directory.

Collect user, size, volume, and allocation path of any files in dirs that contain
an allocation path in its path value, then aggregate by user+path+volume.
*/

SELECT 
    user_usage.size_sum,
    user_usage.lab_path,
    vol.name AS vol_name,
    vol_user.name AS user_name
FROM (
    SELECT 
        SUM(size) AS size_sum,
        lab_path,
        volume_id,
        uid
    FROM (
        SELECT 
            dirs.path, 
            substring(dirs.path FROM '(LABS/[^/]+)') AS lab_path,
            files.size,
            files.volume_id,
            files.gid, 
            files.uid
        FROM sf.dir_current dirs
        JOIN sf.file_current files ON dirs.id = files.parent_id
        WHERE
            NOT files.size = 0 
            AND (dirs.path LIKE 'LABS/%' AND dirs.depth > 2)
    ) userfiles
    -- group by user, volume, lab_dirs path
    GROUP BY uid, lab_path, volume_id) user_usage
LEFT JOIN sf_volumes.volume vol ON user_usage.volume_id = vol.id
LEFT JOIN sf.uid_mapping vol_user ON user_usage.volume_id = vol_user.volume_id AND user_usage.uid = vol_user.uid