-- @query folder_subfolders
-- @db localContent 32.0
-- @param local_id INT
-- @param last_name TEXT
WITH parent_fsid AS (
    SELECT FileSystemID FROM localContent.ItemMetadata
    WHERE LocalID = ?local_id
    LIMIT 1
),
folder_stats AS (
    SELECT c.ParentFSID,
           COUNT(*) AS childCount,
           SUM(CASE WHEN mm.MediaType = 1 THEN 1 ELSE 0 END) AS photoCount,
           SUM(CASE WHEN mm.MediaType = 2 THEN 1 ELSE 0 END) AS videoCount
    FROM localContent.ItemMetadata c
    INNER JOIN localContent.VisibleMediaMetadata mm ON c.FileSystemID = mm.FileSystemID
    WHERE c.Tombstoned = 0
      AND c.ParentFSID IN (
          SELECT f.FileSystemID
          FROM localContent.ItemMetadata f
          WHERE f.ParentFSID = (SELECT FileSystemID FROM parent_fsid)
            AND f.ItemType = 2 AND f.Tombstoned = 0
      )
    GROUP BY c.ParentFSID
)
SELECT
    COALESCE(local.DriveItemID, 'FSID_' || lower(hex(local.FileSystemID))) AS id,
    local.LocalID AS localId,
    local.Name AS name,
    2 AS itemType,
    lower(hex(local.FileSystemID)) AS fileSystemId,
    'LocalId_' || cover.CoverImageID AS coverImageId,
    lower(hex(cover.CoverImageFSID)) AS coverImageFsid,
    COALESCE(fs.childCount, 0) AS childCount,
    COALESCE(fs.photoCount, 0) AS photoCount,
    COALESCE(fs.videoCount, 0) AS videoCount,
    local.ModTime AS modTimeSec
FROM localContent.ItemMetadata AS local
LEFT JOIN (
    SELECT FolderFSID, CoverImageID, CoverImageFSID FROM localContent.LatestMediaByFolderView
) AS cover ON cover.FolderFSID = local.FileSystemID
LEFT JOIN folder_stats fs ON fs.ParentFSID = local.FileSystemID
WHERE local.ParentFSID = (SELECT FileSystemID FROM parent_fsid)
  AND local.ItemType = 2
  AND local.Tombstoned = 0
  AND (?last_name IS NULL OR local.Name > ?last_name COLLATE NOCASE)
ORDER BY name COLLATE NOCASE ASC
LIMIT 201