-- @query folder_tree_local
-- Local folder tree with photo counts. Returns all local folders from
-- localContent.LocalFolders with the number of local-only photos in each.
-- Only includes folders under watched roots with SyncRootType = 0.
-- Used by: shell-folders.js (loadFolderTree, local branch)
-- @db localContent 32.0
--
-- @with account_scope
-- @db media 11.0
-- @param account_id TEXT           -- signed-in account CID (filters RegistrantAccountId)
-- @end-with

SELECT
    lf.FileSystemID AS fsid,
    lf.RelativePath AS path,
    COUNT(loc.LocalID) AS photoCount
FROM localContent.LocalFolders AS lf
LEFT JOIN (
    -- Partition 1: Items with no registrant (pure local, always included)
    SELECT im.LocalID, im.ParentFSID
    FROM localContent.ItemMetadata AS im
    INNER JOIN localContent.VisibleMediaMetadata AS mm
        ON im.FileSystemID = mm.FileSystemID AND mm.MediaType = 1
    WHERE im.Tombstoned = 0
      AND mm.RegistrantAccountId IS NULL

    -- @with account_scope
    UNION ALL

    -- Partition 2: Items registered to the signed-in account (dedup cloud)
    SELECT im.LocalID, im.ParentFSID
    FROM localContent.ItemMetadata AS im
    INNER JOIN localContent.VisibleMediaMetadata AS mm
        ON im.FileSystemID = mm.FileSystemID AND mm.MediaType = 1
    LEFT JOIN media.media_properties AS cloud ON im.DriveItemID = cloud.driveItemId
    WHERE im.Tombstoned = 0
      AND mm.RegistrantAccountId = ?account_id
      AND (im.DriveItemID IS NULL OR cloud.driveItemId IS NULL)
    -- @end-with
) AS loc ON loc.ParentFSID = lf.FileSystemID
WHERE lf.WatchedRootFolderID IN (
    SELECT WatchedRootFolderID FROM localContent.WatchedRootFolders WHERE SyncRootType = 0
)
GROUP BY lf.FileSystemID
ORDER BY lf.RelativePath