-- @query albums_list_v2
-- @db media 29.0
-- @db thumbnailCache 9.0
-- @with album_type_personal
-- @end-with
-- @with album_type_shared
-- @end-with
-- @with album_scope
-- @param driveItemIds TEXT
-- @end-with
WITH
child_counts AS (
    SELECT parentDriveItemId, COUNT(*) AS child_count
    FROM media.media_properties
    WHERE isTombstoned = 0
        AND parentDriveItemId IN (
            SELECT mf_counts.driveItemId
            FROM media.media_folders mf_counts
            WHERE mf_counts.isMediaAlbum = 1
                AND mf_counts.isTombstoned = 0
                -- @with album_type_personal
                AND mf_counts.isSharedWithMe = FALSE
                -- @end-with
                -- @with album_type_shared
                AND mf_counts.isSharedWithMe = TRUE
                -- @end-with
                -- @with album_scope
                AND mf_counts.driveItemId IN (SELECT value FROM json_each(?driveItemIds))
                -- @end-with
        )
    GROUP BY parentDriveItemId
)
SELECT
    ma.driveItemId,
    ma.driveId,
    ma.name,
    ma.createdDateTimeUtc,
    ma.lastModifiedDateTimeUtc,
    ma.sharedDateTimeUtc,
    COALESCE(cc.child_count, 0) AS child_count,
    ma.coverImageDriveItemId AS cover_drive_item_id,
    (SELECT tm.id FROM thumbnailCache.thumbnail_metadata tm
     WHERE tm.driveItemId = COALESCE(
         (SELECT ta.sourceDriveItemId FROM thumbnailCache.thumbnail_aliases ta
          WHERE ta.aliasDriveItemId = ma.coverImageDriveItemId LIMIT 1),
         ma.coverImageDriveItemId
     ) LIMIT 1) AS cover_thumbnail_id,
    ma.isSharedWithMe,
    ma.sharedByDisplayName,
    ma.sharedWithPermissions
FROM media.media_albums ma
LEFT JOIN child_counts cc
    ON cc.parentDriveItemId = ma.driveItemId
WHERE 1=1
    -- @with album_type_personal
    AND ma.isSharedWithMe = FALSE
    -- @end-with
    -- @with album_type_shared
    AND ma.isSharedWithMe = TRUE
    -- @end-with
    -- @with album_scope
    AND ma.driveItemId IN (SELECT value FROM json_each(?driveItemIds))
    -- @end-with
-- @with album_type_personal
ORDER BY ma.createdDateTimeUtc DESC
-- @end-with
-- @with album_type_shared
ORDER BY ma.sharedDateTimeUtc DESC
-- @end-with