-- @query folder_ancestors
-- @db localContent 32.0
-- @param target_local_id INT
WITH RECURSIVE ancestors AS (
    SELECT m.LocalID, m.Name, m.ParentFSID, m.FileSystemID, 0 AS depth
    FROM localContent.ItemMetadata m
    WHERE m.LocalID = ?target_local_id
      AND m.Tombstoned = 0
    UNION ALL
    SELECT parent.LocalID, parent.Name, parent.ParentFSID, parent.FileSystemID, a.depth + 1
    FROM localContent.ItemMetadata parent
    INNER JOIN ancestors a ON parent.FileSystemID = a.ParentFSID
    WHERE parent.IsRootFolder = 0
      AND parent.Tombstoned = 0
)
SELECT LocalID AS localId, Name AS name, lower(hex(FileSystemID)) AS fileSystemId
FROM ancestors
ORDER BY depth DESC