-- @query folder_subfolders_v2
-- @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
)
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_' || lf.AutoCoverLocalID AS coverImageId,
    lower(hex(lf.AutoCoverFSID)) AS coverImageFsid,
    COALESCE(lf.PhotoCount + lf.VideoCount, 0) AS childCount,
    COALESCE(lf.PhotoCount, 0) AS photoCount,
    COALESCE(lf.VideoCount, 0) AS videoCount,
    local.ModTime AS modTimeSec
FROM localContent.ItemMetadata AS local
LEFT JOIN localContent.LocalFolders AS lf ON lf.FileSystemID = 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)
ORDER BY name ASC
LIMIT 201
