-- @query tooltip_local_info
-- @db localContent 32.0
-- @param local_id TEXT
WITH RECURSIVE ancestors(fsid, parent_fsid, wrf_id, is_root, item_size, rel_path) AS (
    SELECT FileSystemID, ParentFSID, WatchedRootFolderID, IsRootFolder, Size,
           CASE WHEN IsRootFolder = 1 THEN '' ELSE Name END
    FROM localContent.ItemMetadata
    WHERE LocalID = ?local_id
    UNION ALL
    SELECT p.FileSystemID, p.ParentFSID, p.WatchedRootFolderID, p.IsRootFolder, a.item_size,
           CASE WHEN p.IsRootFolder = 1 THEN a.rel_path
                ELSE p.Name || '\' || a.rel_path END
    FROM localContent.ItemMetadata p
    JOIN ancestors a ON a.parent_fsid = p.FileSystemID
    WHERE a.is_root = 0
)
SELECT a.item_size AS Size,
       wrf.FullPath || CASE WHEN a.rel_path = '' THEN '' ELSE '\' || a.rel_path END AS ItemPath
FROM ancestors a
LEFT JOIN localContent.WatchedRootFolders wrf ON wrf.WatchedRootFolderID = a.wrf_id
WHERE a.is_root = 1
LIMIT 1