--[[----------------------------------------------------------------------------

WFCatVolumeAgentQueryHelper.lua

--------------------------------------------------------------------------------

ADOBE CONFIDENTIAL
------------------
Copyright 2021 Adobe
All Rights Reserved.

NOTICE: All information contained herein is, and remains
the property of Adobe and its suppliers, if any. The intellectual
and technical concepts contained herein are proprietary to Adobe
and its suppliers and are protected by all applicable intellectual
property laws, including trade secret and copyright laws.
Dissemination of this information or reproduction of this material
is strictly forbidden unless prior written permission is obtained
from Adobe.

------------------------------------------------------------------------------]]

	-- RxLua
local Rx = require 'Rx'
local Observable = Rx.Observable

	-- WF.core.utils
local KSDate = require 'KSDate'
local KSTableUtils = require 'KSTableUtils'
local WFArguments = require 'WFArguments'
local WFLogger = require 'WFLogger'
local WFPlatformInfo = require 'WFPlatformInfo'
local WFTableMerge = require 'WFTableMerge'

	-- WF.oz_client.managed_catalog
local WFCatAssetUtils = require 'WFCatAssetUtils'
local WFCatFileSpec = require 'WFCatFileSpec'
local WFCatIndexUtils = require 'WFCatIndexUtils'
local WFCatAssetAux = require 'WFCatAssetAux'

	-- WF.lrmobile
local LrmConfig

if WFPlatformInfo.isMobile() then
	LrmConfig = require 'LrmConfig'
end

--------------------------------------------------------------------------------

local check = assert( WFArguments.check )

local WFCatIndexUtils_isValidAssetDocument = assert( WFCatIndexUtils.isValidAssetDocument )
local WFCatIndexUtils_isValidVersionDocument = assert( WFCatIndexUtils.isValidVersionDocument )
local KSTableUtils_derefOpt = assert( KSTableUtils.derefOpt )

local logger = WFLogger "WFCatVolumeAgentQueryHelper"
	
local ACCESS_QUERY_DEFAULT_LIMIT = 10000

local isDesktop = WFPlatformInfo.isDesktop

--------------------------------------------------------------------------------

local kInactiveThresholdInDays = 7

if WFPlatformInfo.isMobile() then
	kInactiveThresholdInDays = 30
		-- WF-1938: Proactive Purge on Mobile is intentionally less aggressive.
end

-- These constants are only used when the file size associated with a binary
-- is not available in the document. This should be very rare. These are the same
-- defaults used by the cacheReferences index store table.

local kDefaultFileSizeForBinaryTypes = {
	original = 20000000,
	xmp_develop = 16384,
	aux = 16384,
}

--------------------------------------------------------------------------------

local WFCatVolumeAgentQueryHelper = {}

--------------------------------------------------------------------------------

local function boolToInt( maybeBool )

	return maybeBool and 1 or 0

end

--------------------------------------------------------------------------------

local imageProxySizes, videoProxySizes

if isDesktop then

	imageProxySizes = {
		'2560',
		'panorama',
		'pano', -- deprecated
		'fullsize'
	}
	
	-- For LrD, video proxies can exist in the following sizes: 360p, 720p (LrD-only), 1080p
	-- The 720p proxy is technically a rendition, but LrD considered it a proxy before
	-- real video proxies were introduced into the ecosystem.

	videoProxySizes = {
		'1080p',
		'1080p709',
		'720p',
		'360p'
	}

else

	imageProxySizes = {
		'2560'
	}

	videoProxySizes = {
		'1080p',
		'360p'
	}

end

--------------------------------------------------------------------------------

-- @mark Index Store Table Definition

--------------------------------------------------------------------------------

function WFCatVolumeAgentQueryHelper.generateIndexSchema( indexStoreSchema )

	indexStoreSchema:addTable {

		sql = [[CREATE TABLE purgeCandidates(
						localId,
						assetId,
						hasLocalFile,
						isPurgeable,
						binaryType,
						binarySize,
						volumeGuidUsedByFile,
						fileSize,
						dateLastUpdatedInSeconds,
						contentIdOrSha256,
						isMarkedForOfflineUse,
						ozHasBinary,
						isVideo )]],

		relevantTypeSubtypeTuples = {
			{ 'asset', 'image' },
			{ 'asset', 'video' },
			{ 'asset', 'deleted_image' },
			{ 'asset', 'deleted_video' },
			{ 'version', 'edit' },
		},
		
		map = function( docValue, emit )

			if WFCatIndexUtils_isValidAssetDocument( docValue ) or WFCatIndexUtils_isValidVersionDocument( docValue ) then
			
				-- General Properties
				
				local localId = docValue._localId
				
				local assetId
				
				-- assetId is either the doc id, or the asset id referred to by a version document.
				
				if docValue.type == 'asset' then
					assetId = docValue._id
				else
					assetId = KSTableUtils.derefOpt( docValue, 'asset', 'id' )
				end
				
				local dateLastUpdated = KSTableUtils_derefOpt( docValue, 'userUpdated' )
						or KSTableUtils_derefOpt( docValue, '_serverProvidedMetadata', 'updated' )
						or KSTableUtils_derefOpt( docValue, 'importSource', 'importTimestamp' )

				local dateLastUpdatedInSeconds = dateLastUpdated
						and KSDate.iso8601ToTimeIntervalSinceReference( dateLastUpdated )
						or 0

				local isMarkedForOfflineUse = WFCatFileSpec.getMarkedForOfflineUseFromDocValue( docValue )
					-- DESKTOP ONLY

				-- If Oz does not have renditions, we need the proxy locally to generate renditions.
				-- If Oz does not have renditions and we have no local proxy, then we need to keep
				-- the local original to generate renditions.
				-- If Oz does not have renditions, then we also need the develop settings and
				-- associated masks on the device in order to generate valid renditions.
				
				local sufficientRenditionFoundOnOz = WFCatFileSpec.checkOzForNonThumbnailRendition( docValue )

				local subtype = docValue.subtype
				local isVideo = subtype == 'video' or subtype == 'deleted_video'

				if docValue.type == 'asset' then

					-- Original
					
					local ozHasOriginal = WFCatFileSpec.getOriginalLinkFromDocValue( docValue )
					
					local isOzOriginalValid = WFCatAssetUtils.isValidOriginalOnOz( docValue )
					
					local hasLocalOriginal = WFCatAssetUtils.hasLocalOriginal( docValue )
					
					local originalFileSize = WFCatAssetUtils.getOriginalSizeFromDocValue( docValue )
							or kDefaultFileSizeForBinaryTypes[ 'original' ]
					
					local hasAnyLocalProxy = WFCatAssetUtils.hasLocalProxy( docValue )
					
					local volumeGuidUsedByOriginal = hasLocalOriginal 
							and WFCatFileSpec.getOriginalVolumeGuidFromDocValue( docValue )
						-- DESKTOP ONLY
						
					local hasLocalSidecar = WFCatFileSpec.getXmpSidecarFileSizeFromDocValue( docValue )
						-- DESKTOP ONLY
						
					local xmpSidecarSize = hasLocalSidecar 
							and WFCatFileSpec.getXmpSidecarFileSizeFromDocValue( docValue ) or 0
						-- DESKTOP ONLY
					
					local isOriginalPurgeable = ozHasOriginal
							and isOzOriginalValid
							and not isMarkedForOfflineUse
							and ( sufficientRenditionFoundOnOz or hasAnyLocalProxy )

					local purgeableFileSize = originalFileSize + xmpSidecarSize

					if hasLocalOriginal or ozHasOriginal or WFPlatformInfo.isMobile() then
						emit( localId, assetId, boolToInt( hasLocalOriginal ), boolToInt( isOriginalPurgeable ),
								'original', nil, volumeGuidUsedByOriginal, purgeableFileSize,
								dateLastUpdatedInSeconds, nil, boolToInt( isMarkedForOfflineUse ),
								boolToInt( not not ozHasOriginal ), boolToInt( isVideo ) )
					end
					
					-- Proxies

					local proxySizes = isVideo and videoProxySizes or imageProxySizes
					local foundLocalProxy = false

					for _, proxySize in ipairs( proxySizes ) do
					
						local hasLocalProxy = WFCatAssetUtils.hasLocalProxy( docValue, proxySize )

						if hasLocalProxy then

							foundLocalProxy = true

							local ozHasProxy = WFCatFileSpec.getProxyLinkFromDocValue( docValue, proxySize )
							
							local proxyFileSize = WFCatAssetUtils.getProxySizeFromDocValue( docValue, proxySize )
								
							local volumeGuidUsedByProxy = WFCatFileSpec.getProxyVolumeGuidFromDocValue( docValue, proxySize )
								-- DESKTOP ONLY
								
							local isProxyPurgeable = ozHasProxy
									and not isMarkedForOfflineUse
									and sufficientRenditionFoundOnOz
							
							emit( localId, assetId, boolToInt( hasLocalProxy ), boolToInt( isProxyPurgeable ),
									'proxy', proxySize, volumeGuidUsedByProxy, proxyFileSize,
									dateLastUpdatedInSeconds, nil, boolToInt( isMarkedForOfflineUse ),
									boolToInt( not not ozHasProxy ), boolToInt( isVideo ) )
									
						end
							
					end

					if not foundLocalProxy then

						-- SPAM [jandrew 2022-02-10]: We must emit the default proxy,
						-- even if it's not local, so that the cache size logic works
						-- correctly. Luckily, this has no affect on the other purge logic.

						-- See if there is a proxy on oz and what type it is as indicated by proxySize.
						-- Note: proxySize will be set to default proxy size if a link
						-- doesn't exist (2560 or 1080p).
						local ozHasProxy, proxySize = WFCatFileSpec.getProxyLinkFromDocValue( docValue )
						if proxySize == 'panorama' then
							-- We don't actually download panorama proxies, switch to '2560'
							proxySize = '2560'
						end

						local proxyFileSize = WFCatAssetUtils.estimateProxySize( docValue, proxySize )

						local hasLocalProxy = false
						local volumeGuidUsedByProxy = nil

						local isProxyPurgeable = ozHasProxy
								and not isMarkedForOfflineUse
								and sufficientRenditionFoundOnOz

						if ozHasProxy or WFPlatformInfo.isMobile() then
							emit( localId, assetId, boolToInt( hasLocalProxy ), boolToInt( isProxyPurgeable ),
									'proxy', proxySize, volumeGuidUsedByProxy, proxyFileSize,
									dateLastUpdatedInSeconds, nil, boolToInt( isMarkedForOfflineUse ),
									boolToInt( not not ozHasProxy ), boolToInt( isVideo ) )
						end

					end

					-- Aux Objects (Masks, filters, c2pa etc.)

					-- Version aux objects are referenced by their parent asset documents, so we only need
					-- to consider them for asset docs.

					local auxObjects = KSTableUtils_derefOpt( docValue, 'aux' ) or {}
				
					for auxId, auxObject in pairs( auxObjects ) do

						local ozHasAuxObject = WFCatAssetAux.isAuxObjectOnOz( docValue, auxId )
						
						local auxObjectFingerprint = auxObject.digest
						
						local hasLocalAuxObject = not not WFCatAssetUtils.getLocalAuxObjectPath( docValue, auxObjectFingerprint )

						local auxObjectSize = auxObject.fileSize
								or kDefaultFileSizeForBinaryTypes[ 'aux' ]
							
						local isAuxObjectPurgeable = ozHasAuxObject
								and not isMarkedForOfflineUse
								and sufficientRenditionFoundOnOz
						
						if hasLocalAuxObject or ozHasAuxObject then
							emit( localId, assetId, boolToInt( hasLocalAuxObject ), boolToInt( isAuxObjectPurgeable ),
									'aux', nil, nil, auxObjectSize, dateLastUpdatedInSeconds,
									auxObjectFingerprint, boolToInt( isMarkedForOfflineUse ),
									boolToInt( not not ozHasAuxObject ), boolToInt( isVideo ) )
						end

						-- resampled aux from original instance
						-- Don't emit more than one aux for the same group

						local filterGroupDigests = {}
						local maybeGroupDigest = WFCatAssetAux.maybeGetGroupDigestForDigest( docValue, auxObjectFingerprint )

						if maybeGroupDigest and not filterGroupDigests[ maybeGroupDigest ] then

							filterGroupDigests[ maybeGroupDigest ] = true

							local resampledAuxObjectFingerprint, resampledAuxInfo = WFCatAssetAux.maybeResampledDigestAndAuxInfoLocally( docValue, maybeGroupDigest )
							if resampledAuxObjectFingerprint and resampledAuxInfo then

								-- For resampled aux, it shares Oz related values with original instance

								local hasLocalResampledAuxObject = true
								local resampledAuxObjectSize = resampledAuxInfo.fileSize

								emit( localId, assetId, boolToInt( hasLocalResampledAuxObject ), boolToInt( isAuxObjectPurgeable ),
										'aux', nil, nil, resampledAuxObjectSize, dateLastUpdatedInSeconds,
										resampledAuxObjectFingerprint, boolToInt( isMarkedForOfflineUse ),
										boolToInt( not not ozHasAuxObject ), boolToInt( isVideo ) )

							end
							
						end
					end
				
				end
				
				-- Develop Settings

				local ozHasDevelopSettings = WFCatFileSpec.getXmpDevelopLinkAndSha256FromDocValue( docValue )
					-- ozHasDevelopSettings is false if the settings on Oz are invalid.
					
				local hasLocalDevelopSettings = not not WFCatAssetUtils.getLocalExternalDevelopSettingsPath( docValue )
				
				local developSettingsFileSize = WFCatAssetUtils.getLocalExternalDevelopSettingsSize( docValue )
						or kDefaultFileSizeForBinaryTypes[ 'xmp_develop' ]
						
				local developSettingsSha256 = WFCatAssetUtils.getLocalExternalDevelopSettingsContentId( docValue )

				local areDevelopSettingsPurgeable = ozHasDevelopSettings and not isMarkedForOfflineUse
				
				if docValue.type == 'asset' then
					areDevelopSettingsPurgeable = areDevelopSettingsPurgeable and sufficientRenditionFoundOnOz
				end
				
				if hasLocalDevelopSettings or ozHasDevelopSettings then
					emit( localId, assetId, boolToInt( hasLocalDevelopSettings ), boolToInt( areDevelopSettingsPurgeable ),
							'xmp_develop', nil, nil, developSettingsFileSize, dateLastUpdatedInSeconds,
							developSettingsSha256, boolToInt( isMarkedForOfflineUse ),
							boolToInt( not not ozHasDevelopSettings ), boolToInt( isVideo ) )
				end

			end

		end

	}
	
	-- Add some indexes
		
	if isDesktop then
		
		-- volumeGuidUsedByFile & isMarkedForOfflineUse are only used in
		-- LrD queries, hence the platform differences.
		
		indexStoreSchema:addIndex
				[[CREATE INDEX purgeCandidates_searchIndex
					ON purgeCandidates(
							binaryType,
							hasLocalFile,
							isPurgeable,
							dateLastUpdatedInSeconds,
							volumeGuidUsedByFile )]]

		indexStoreSchema:addIndex
			[[CREATE INDEX purgeCandidates_contentIdOrSha256
				ON purgeCandidates(
						binaryType,
						contentIdOrSha256,
						fileSize,
						isMarkedForOfflineUse )]]

	else
	
		indexStoreSchema:addIndex
				[[CREATE INDEX purgeCandidates_searchIndex
					ON purgeCandidates(
							binaryType,
							hasLocalFile,
							dateLastUpdatedInSeconds,
							isPurgeable )]]
							
		indexStoreSchema:addIndex
			[[CREATE INDEX purgeCandidates_contentIdOrSha256
				ON purgeCandidates(
						binaryType,
						contentIdOrSha256,
						fileSize )]]
	
	end

	indexStoreSchema:addIndex
			[[CREATE INDEX purgeCandidates_assetId
				ON purgeCandidates( assetId )]]

	----------------------------------------------------------------------------

	--[[

		The following table is used by what's effectively the garbage collector
		for aux binaries that are shared between documents. This table must consider
		both non-deleted and deleted doc values.

		This table currently only supports auxiliary object files, as they are
		shared between all documents. Develop Settings files are also shared, but
		this approach doesn't work for develop settings because the local only
		file annotations do not always persist in the document value (they can
		be overwritten or deleted when the develop settings are updated).

	]]--

	indexStoreSchema:addTable {

		sql = [[ CREATE TABLE auxReferenceTracker(
						localId,
						fingerprint,
						hasDocReference ) ]],

		relevantTypeSubtypeTuples = {
			{ 'asset', 'image' },
			{ 'asset', 'video' },
			{ 'asset', 'deleted_image' },
			{ 'asset', 'deleted_video' },
			{ 'version', 'edit' },
		},

		map = function( docValue, emit )

			local localId = docValue._localId

			local referencedAuxObjects = {}

			if not docValue._deleted then

				-- Since the payload 'aux' section of the documents are additive, and no entries
				-- are ever removed, we intentionally only count references to the aux objects
				-- from the develop section.

				local auxObjects = WFCatAssetAux.extractDevelopAuxDigestsFromDocValue( docValue )

				-- need to also track c2pa 
				auxObjects = WFTableMerge.applyOverlay( auxObjects, WFCatAssetAux.extractC2paAuxDigestFromDocValue( docValue ) )

				for _, digest in pairs( auxObjects ) do

					local fingerprint = digest

					-- Aux Objects with doc references

					if fingerprint then

						referencedAuxObjects[ fingerprint ] = true

						local hasAuxPath = not not WFCatAssetUtils.getLocalAuxObjectPath( docValue, fingerprint )

						if hasAuxPath then
							emit( localId, fingerprint, 1 )
						end

					end
					
				end
			
			end

			local localOnlyAuxObjects = KSTableUtils_derefOpt( docValue, '_localOnly', 'files', 'aux' ) or {}

			for fingerprint, auxInfo in pairs( localOnlyAuxObjects ) do

				local hasAuxPath = not not auxInfo.relativePath
			
				if not referencedAuxObjects[ fingerprint ] and hasAuxPath then

					-- If aux is a resampled filter, check if it belongs to a current develop filter
				
					local maybeGroupDigest = auxInfo.groupDigest

					if maybeGroupDigest and KSTableUtils_derefOpt( docValue, 'develop', 'filters', maybeGroupDigest ) then

						-- Aux Objects without doc references, but is resampled aux from current filter
						emit( localId, fingerprint, 1 )

					else

						-- Aux objects not from resample API with no doc references
						emit( localId, fingerprint, 0 )

					end
					
				end

			end

		end

	}

	indexStoreSchema:addIndex
			[[CREATE INDEX auxReferenceTracker_fingerprint
				ON auxReferenceTracker( fingerprint )]]

	----------------------------------------------------------------------------

	--[[

		The following table is used solely for space-related garbage collection.
		When a user's role is downgraded, or when the user is removed from a
		space, we must remove the auxiliary binaries associated with that space
		from the the device, *UNLESS* those binaries are shared with documents
		that exist outside the space.

	]]--

	indexStoreSchema:addTable {

		sql = [[ CREATE TABLE localAuxTracker(
						spaceId,
						digest ) ]],

		relevantTypeSubtypeTuples = {
			{ 'asset', 'image' },
			{ 'asset', 'video' },
			{ 'asset', 'deleted_image' },
			{ 'asset', 'deleted_video' },
			{ 'version', 'edit' },
		},

		map = function( docValue, emit )

			local spaceId = docValue._spaceId

			local localOnlyAuxObjects = KSTableUtils_derefOpt( docValue, '_localOnly', 'files', 'aux' ) or {}

			for digest, auxInfo in pairs( localOnlyAuxObjects ) do

				local hasAuxPath = not not auxInfo.relativePath

				if hasAuxPath then

					emit( spaceId, digest )

				end

			end

		end

	}

	indexStoreSchema:addIndex
			[[CREATE INDEX localAuxTracker_digest
				ON localAuxTracker( digest )]]

end

--------------------------------------------------------------------------------

-- @mark Helper Functions

--------------------------------------------------------------------------------

local function groupResultsByDoc( rows )

	-- This function only works with rows of the specified format:
	-- { <local Id>, <doc id>, <size>, <binarySize> }

	-- Iterate over the result set and group together the results by the doc Id.
	-- For example, the following code turns this:
	--
	-- {
	--     { <localId 1>, <docId 1>, 5, 'a' },
	--     { <localId 1>, <docId 1>, 10, 'b' },
	--     { <localId 2>, <docId 2>, 50, 'c' },
	--     { <localId 2>, <docId 2>, 100, 'd' },
	-- }
	--
	-- Into this:
	--
	-- {
	--     { <localId 1>, <docId 1>, 15, { 'a', 'b' } },
	--     { <localId 2>, <docId 2>, 150, { 'c', 'd' } },
	-- }
	
	local results = {}
	local resultsIndexLookupByDocId = {}
	
	for _, row in ipairs( rows ) do
	
		local localId = row[ 1 ]
		local docId = row[ 2 ]
		local fileSize = row[ 3 ] or 0
		local binarySize = row[ 4 ]
		
		local resultsIndex = resultsIndexLookupByDocId[ docId ]
		
		local entry = resultsIndex and results[ resultsIndex ]
		
		if entry then
		
			entry[ 3 ] = entry[ 3 ] + fileSize
			
			local binarySizeArray = entry[ 4 ]
			binarySizeArray[ #binarySizeArray + 1 ] = binarySize
			
		else
		
			entry = {
				localId,
				docId,
				fileSize,
				{ binarySize }
			}
			
			resultsIndex = #results + 1
		
			results[ resultsIndex ] = entry
			resultsIndexLookupByDocId[ docId ] = resultsIndex
		
		end
		
	end
	
	return results

end

--------------------------------------------------------------------------------

local function getAlbumFilter( albumId )

	if WFPlatformInfo.isMobile() then

		return string.format(
				[[ INNER JOIN album_asset ON album_asset.albumId == '%s'
					AND album_asset.assetId == purgeCandidates.assetId ]], albumId )

	else

		return string.format(
				[[ INNER JOIN album_asset_v2 ON album_asset_v2.albumId == '%s'
					AND album_asset_v2.assetId == purgeCandidates.assetId ]], albumId )

	end

end

--------------------------------------------------------------------------------

local function queryForSizeOfBinariesWithContentIdsOrSha256s( 
		indexStore, binaryType, contentIdsOrSha256s, optOnlyInAlbumId )

	if not next( contentIdsOrSha256s ) then
		return Observable.just( { 0 } )
	end

	local maybeAlbumFilter = optOnlyInAlbumId and getAlbumFilter( optOnlyInAlbumId ) or ' '

	local queryStr =
			string.format( [[ SELECT DISTINCT contentIdOrSha256, fileSize
					FROM purgeCandidates
					%s
					WHERE binaryType == ?
						AND contentIdOrSha256 IN CARRAY(?, ?, 'char*') ]],
			maybeAlbumFilter )

	return indexStore:queryOnce {
			sql = queryStr,
			quiesceUntilUpToDate = true,
			binaryType,
			contentIdsOrSha256s,
			#contentIdsOrSha256s,
		}
		
		:map( function( rows )
							
			local totalSize = 0
			
			for _, row in ipairs( rows ) do
			
				local size = row[ 2 ]
				
				totalSize = totalSize + size
			
			end
						
			return { totalSize }
			
		end )
		
end 

--------------------------------------------------------------------------------

local daysToSeconds = function( days )
	return days * 24 * 60 * 60
end

--------------------------------------------------------------------------------

local function getOnlyValueFromRows( rows )
	return rows and rows[ 1 ] or 0
end

--------------------------------------------------------------------------------

local function getInactiveFilter()

	local currentTimeInSeconds = KSDate.timeNow()

	local nDaysAgoInSeconds = currentTimeInSeconds - daysToSeconds( kInactiveThresholdInDays )

	return " AND dateLastUpdatedInSeconds < ? ", nDaysAgoInSeconds
	
end

--------------------------------------------------------------------------------

local function getIsMarkedForOfflineUseFilter( orOrAnd )

	return string.format( 
			[[ %s purgeCandidates.assetId IN
				( SELECT album_asset.assetId
					FROM album_asset
					INNER JOIN albums ON album_asset.albumId == albums.docId
					WHERE albums.useOffline == 1 )
			]], orOrAnd )

end

--------------------------------------------------------------------------------

local function getIsNotMarkedForOfflineUseFilter( orOrAnd )

	return string.format( 
			[[ AND purgeCandidates.assetId NOT IN
				( SELECT album_asset.assetId
					FROM album_asset
					INNER JOIN albums ON album_asset.albumId == albums.docId
					WHERE albums.useOffline == 1 )
			]], orOrAnd )

end

--------------------------------------------------------------------------------

local function getDevelopSettingsPurgeCandidates( inactiveAssetsOnly )

	local inactiveFilter = ' '
	local inactiveThresholdParam
	
	if inactiveAssetsOnly then

		inactiveFilter, inactiveThresholdParam = getInactiveFilter()
		
	end
	
	local offlineUseFilter = WFPlatformInfo.isMobile() and getIsMarkedForOfflineUseFilter( 'OR' ) or " "

	local developSettingsPurgeCandidates = string.format(
			[[ SELECT DISTINCT contentIdOrSha256
					FROM purgeCandidates
					WHERE binaryType == 'xmp_develop'
						AND hasLocalFile == 1
						AND isPurgeable == 1
						%s
				EXCEPT
				SELECT contentIdOrSha256
					FROM purgeCandidates
					WHERE binaryType == 'xmp_develop'
						AND ( isPurgeable == 0 %s ) ]],
					inactiveFilter, offlineUseFilter )

	return developSettingsPurgeCandidates, inactiveThresholdParam

end

--------------------------------------------------------------------------------

local function getUnsyncedAuxOrDevelopSettings( binaryType )

	local offlineUseFilter = WFPlatformInfo.isMobile() 
			and getIsMarkedForOfflineUseFilter( 'AND' )
			or ' AND isMarkedForOfflineUse == 1 '

	local unsyncedDevelopSettingsSha256s = string.format(
			[[ SELECT DISTINCT contentIdOrSha256
					FROM purgeCandidates
					WHERE binaryType == '%s'
						AND hasLocalFile == 1
						AND ozHasBinary == 0
				EXCEPT
				SELECT contentIdOrSha256
					FROM purgeCandidates
					WHERE binaryType == '%s'
						%s ]],
					binaryType,
					binaryType,
					offlineUseFilter )

	return unsyncedDevelopSettingsSha256s

end

--------------------------------------------------------------------------------

local function getBatchOfDevelopSettings(
		indexStore, inactiveAssetsOnly, limit, offset )

	-- First query for all the develop settings purge candidates. Settings are
	-- considered to be purgeable only if they not referenced by any asset that
	-- we must keep on the device.

	local developSettingsPurgeCandidates, maybeInactiveThresholdParam 
			= getDevelopSettingsPurgeCandidates( inactiveAssetsOnly )

	logger:infof( "Desktop purge candidate query: %s", developSettingsPurgeCandidates )

	return indexStore:queryOnce {
			sql = developSettingsPurgeCandidates,
			quiesceUntilUpToDate = true,
			singleColumnQuery = true,
			maybeInactiveThresholdParam
		}
		
		:flatMap( function( developSettingsSha256s )
		
			if not next( developSettingsSha256s ) then
				return Observable.just( {} )
			end
		
			-- Finally, fetch info about all the documents that reference the purgeable settings.
		
			local developSettingsPurgeCandidates =
					[[ SELECT localId, docId, fileSize
							FROM purgeCandidates
							WHERE binaryType == 'xmp_develop'
								AND contentIdOrSha256 IN CARRAY(?, ?, 'char*')
						ORDER BY dateLastUpdatedInSeconds ASC, fileSize DESC
						LIMIT ? OFFSET ? ]]

			logger:infof( "Desktop purge candidate query: %s", developSettingsPurgeCandidates )

			return indexStore:queryOnce {
					sql = developSettingsPurgeCandidates,
					quiesceUntilUpToDate = true,
					developSettingsSha256s,
					#developSettingsSha256s,
					limit,
					offset
				}
		
		end )

end

--------------------------------------------------------------------------------

local function getAuxPurgeCandidates( inactiveAssetsOnly, onlyInAlbumId )

	local inactiveFilter = ' '
	local inactiveThresholdParam
	
	if inactiveAssetsOnly then

		inactiveFilter, inactiveThresholdParam = getInactiveFilter()
		
	end

	local albumFilter = ' '

	if onlyInAlbumId then

		albumFilter = getAlbumFilter( onlyInAlbumId )

	end

	local offlineUseFilter = WFPlatformInfo.isMobile() and getIsMarkedForOfflineUseFilter( 'OR' ) or " "

	local auxPurgeCandidates = string.format(
			[[ SELECT DISTINCT contentIdOrSha256
					FROM purgeCandidates
					%s
					WHERE binaryType == 'aux'
						AND hasLocalFile == 1
						AND isPurgeable == 1
						%s
				EXCEPT
				SELECT contentIdOrSha256
					FROM purgeCandidates
					WHERE binaryType == 'aux'
						AND ( isPurgeable == 0 %s ) ]],
			albumFilter, inactiveFilter, offlineUseFilter )

	return auxPurgeCandidates, inactiveThresholdParam

end

--------------------------------------------------------------------------------

local function getBatchOfAuxObjects(
		indexStore, inactiveAssetsOnly, limit, offset )
	
	-- First query for all the aux purge candidates. A aux is considered to
	-- be a purge candidate only if it is not referenced by any asset that
	-- we must keep on the device.
	
	local auxPurgeCandidates, maybeInactiveThresholdParam 
			= getAuxPurgeCandidates( inactiveAssetsOnly )
	
	logger:infof( "Desktop purge candidate query: %s", auxPurgeCandidates )

	 return indexStore:queryOnce {
			sql = auxPurgeCandidates,
			quiesceUntilUpToDate = true,
			singleColumnQuery = true,
			maybeInactiveThresholdParam
		}
		
		:flatMap( function( purgeCandidateFingerprints )
			
			if not next( purgeCandidateFingerprints ) then
				return Observable.just( {} )
			end
			
			local developSettingsPurgeCandidates =
					[[ SELECT localId, docId, fileSize, contentIdOrSha256
							FROM purgeCandidates
							WHERE binaryType == 'aux'
								AND contentIdOrSha256 IN CARRAY(?, ?, 'char*')
						ORDER BY dateLastUpdatedInSeconds ASC, fileSize DESC
						LIMIT ? OFFSET ? ]]

			logger:infof( "Desktop purge candidate query: %s", developSettingsPurgeCandidates )

			return indexStore:queryOnce {
					sql = developSettingsPurgeCandidates,
					quiesceUntilUpToDate = true,
					purgeCandidateFingerprints,
					#purgeCandidateFingerprints,
					limit,
					offset
				}
				
		end )
		
		:map( groupResultsByDoc )
	
end

--------------------------------------------------------------------------------

-- @mark Mobile Helper Functions

--------------------------------------------------------------------------------

local function getBatchOfOriginalsOrProxies_Mobile(
		indexStore, binaryType, inactiveAssetsOnly, limit, offset )

	local filterParams = {}
	
	filterParams[ #filterParams + 1 ] = binaryType
	
	local inactiveFilter = ' '
	local inactiveThresholdParam
	
	if inactiveAssetsOnly then

		inactiveFilter, inactiveThresholdParam = getInactiveFilter()
		filterParams[ #filterParams + 1 ] = inactiveThresholdParam
		
	end
	
	filterParams[ #filterParams + 1 ] = limit
	filterParams[ #filterParams + 1 ] = offset

	local queryStr = string.format(
				[[ SELECT localId, docId, fileSize, binarySize
					FROM purgeCandidates
					WHERE binaryType == ?
						AND hasLocalFile == 1
						AND isPurgeable == 1
						%s
						%s
					ORDER BY dateLastUpdatedInSeconds ASC, fileSize DESC
					LIMIT ? OFFSET ? ]],
					inactiveFilter,
					getIsNotMarkedForOfflineUseFilter( 'AND' ) )

	 return indexStore:queryOnce {
			sql = queryStr,
			quiesceUntilUpToDate = true,
			unpack( filterParams )	
		}
	 
		:map( groupResultsByDoc )
	
end

--------------------------------------------------------------------------------

local function getBatchOfRenditions_Mobile(
		indexStore, binaryType, inactiveAssetsOnly, limit, offset )
	
	local filterParams = {}
	
	filterParams[ #filterParams + 1 ] = binaryType
	
	local inactiveFilter = ' '
	local inactiveThresholdParam
	
	if inactiveAssetsOnly then

		inactiveFilter, inactiveThresholdParam = getInactiveFilter()
		filterParams[ #filterParams + 1 ] = inactiveThresholdParam
		
	end
	
	filterParams[ #filterParams + 1 ] = limit
	filterParams[ #filterParams + 1 ] = offset

	local queryStr = string.format(
			[[ SELECT DISTINCT purgeCandidates.localId, purgeCandidates.docId, size
				FROM cacheReferences
				INNER JOIN purgeCandidates ON purgeCandidates.docId == cacheReferences.docId
				WHERE cacheReferences.renditionName == ?
					AND cacheReferences.hasUploaded == 1
					%s
					%s
				ORDER BY dateLastUpdatedInSeconds ASC, fileSize DESC
				LIMIT ? OFFSET ? ]],
				inactiveFilter,
				getIsNotMarkedForOfflineUseFilter( 'AND' ) )

	 return indexStore:queryOnce {
			sql = queryStr,
			quiesceUntilUpToDate = true,
			unpack( filterParams )	
		}
		
end

--------------------------------------------------------------------------------

local function getBatchOfPurgeableBinaries_Mobile(
		indexStore, binaryType, inactiveAssetsOnly, limit, offset )

	offset = offset or 0

	limit = limit or ACCESS_QUERY_DEFAULT_LIMIT

	local query

	if binaryType == 'original' or binaryType == 'proxy' then
		
		query = getBatchOfOriginalsOrProxies_Mobile(
				indexStore, binaryType, inactiveAssetsOnly, limit, offset )

	elseif binaryType == 'xmp_develop' then
		
		query = getBatchOfDevelopSettings(
				indexStore, inactiveAssetsOnly, limit, offset )
	
	elseif binaryType == 'aux' then
	
		query = getBatchOfAuxObjects(
				indexStore, inactiveAssetsOnly, limit, offset )
	
	else

		query = getBatchOfRenditions_Mobile(
				indexStore, binaryType, inactiveAssetsOnly, limit, offset )

	end

	local nextBatchOffset = offset + limit

	return query
		
		:map( function( rows )

			local function _getNextBatchFn()
			
				return getBatchOfPurgeableBinaries_Mobile(
						indexStore, binaryType, inactiveAssetsOnly, 
						limit, nextBatchOffset )
			
			end

			return rows, _getNextBatchFn
		
		end )
		
end

--------------------------------------------------------------------------------

-- @mark Desktop Helper Functions

--------------------------------------------------------------------------------

local function getBatchOfOriginalsOrProxies_Desktop(
		indexStore, binaryType, maybeVolumeGuid, inactiveAssetsOnly, limit, offset )
	
	local filterParams = {}

	filterParams[ #filterParams + 1 ] = binaryType
	
	-- Maybe add a Volume Guid filter
	
	local volumeGuidFilter = " "
	
	if maybeVolumeGuid then

		volumeGuidFilter = " AND volumeGuidUsedByFile == ? "

		filterParams[ #filterParams + 1 ] = maybeVolumeGuid

	end

	-- Maybe add an inactive asset filter
	
	local inactiveFilter = ' '
	local inactiveThresholdParam
	
	if inactiveAssetsOnly then

		inactiveFilter, inactiveThresholdParam = getInactiveFilter()
		filterParams[ #filterParams + 1 ] = inactiveThresholdParam
		
	end
	
	filterParams[ #filterParams + 1 ] = limit
	filterParams[ #filterParams + 1 ] = offset

	local queryStr = string.format(
				[[ SELECT localId, docId, fileSize, binarySize
					FROM purgeCandidates
					WHERE binaryType == ?
						AND hasLocalFile == 1
						AND isPurgeable == 1
						%s
						%s
					ORDER BY dateLastUpdatedInSeconds ASC, fileSize DESC
					LIMIT ? OFFSET ? ]],
					volumeGuidFilter, inactiveFilter )

	logger:infof( "Desktop purge candidate query: %s", queryStr )

	return indexStore:queryOnce {
			sql = queryStr,
			quiesceUntilUpToDate = true,
			unpack( filterParams )			
		}
	
		:map( groupResultsByDoc )
	
end

--------------------------------------------------------------------------------

local function getBatchOfPurgeableBinaries_Desktop(
		indexStore, binaryType, maybeVolumeGuid, inactiveAssetsOnly, limit, offset )

	offset = offset or 0

	limit = limit or ACCESS_QUERY_DEFAULT_LIMIT

	local query

	if binaryType == 'original' or binaryType == 'proxy' then
		
		query = getBatchOfOriginalsOrProxies_Desktop(
				indexStore, binaryType, maybeVolumeGuid,
				inactiveAssetsOnly, limit, offset )

	elseif binaryType == 'xmp_develop' then
		
		query = getBatchOfDevelopSettings(
				indexStore, inactiveAssetsOnly, limit, offset )
	
	elseif binaryType == 'aux' then
	
		query = getBatchOfAuxObjects(
				indexStore, inactiveAssetsOnly, limit, offset )
	
	else

		return Observable.just( {} )

	end

	local nextBatchOffset = offset + limit

	return query
		
		:map( function( rows )

			local function _getNextBatchFn()
			
				return getBatchOfPurgeableBinaries_Desktop(
						indexStore, binaryType, maybeVolumeGuid, 
						inactiveAssetsOnly, limit, nextBatchOffset )
			
			end

			return rows, _getNextBatchFn
		
		end )
		
end

--------------------------------------------------------------------------------

-- @mark Get Binaries APIs

--------------------------------------------------------------------------------

local function getBatchOfPurgeableBinaries(
		indexStore, binaryType, maybeVolumeGuid, inactiveAssetsOnly, limit, offset )
		
	if WFPlatformInfo.isMobile() then
	
		return getBatchOfPurgeableBinaries_Mobile(
				indexStore, binaryType, inactiveAssetsOnly, limit, offset )
	
	else
	
		return getBatchOfPurgeableBinaries_Desktop(
				indexStore, binaryType, maybeVolumeGuid, inactiveAssetsOnly, limit, offset )
	
	end
		
end

--------------------------------------------------------------------------------

--[[!

	\brief Retrieve the current list of purge candidates from the db, sorted
	by importance and then by when they were last updated.

	\param wfCatalog (WFCatalog)

	\param binaryType (string) Only return purgeable binaries of the specified type:
		
		"original"
		"proxy"
		"xmp_develop"
		"aux" (this includes masks, filters and c2pa)
		"fullsize"
		"preview"
		"thumbnail2x"

	\param volumeGuid (optional string) Only return binaries that live on this specific volume.
	\param limit (optional number) Limit the number of binaries to search for.
	\param offset (optional number) Used to fetch the next batch of binaries.
	
	\return <code>Observable< array ></code> An array of purge candidates, along
		with a function for getting the next batch of candidates. Example:
	
		local batch, getNextBatchFunc = WFCatVolumeAgentQueryHelper.getBatchOfPurgeableBinaries( ... )
		...
		batch, getNextBatchFunc = getNextBatchFunc()
		...
		batch, getNextBatchFunc = getNextBatchFunc()
		...
		batch, getNextBatchFunc = getNextBatchFunc()
		...
		and so on.
		
		Each entry in the resulting batch array is of the following format:
		
		{ <local Id>, <doc Id>, <file size>, <optional table of binary sizes> }

		Example batch:

		{
			{ 1, '11111111111111111111111111111111', 1000, { '2560' } },
			{ 2, '22222222222222222222222222222222', 3000, { '2560', 'panorama' } },
			{ 3, '33333333333333333333333333333333', 2000, { '2560', 'fullsize' } },
		}

--]]

function WFCatVolumeAgentQueryHelper.getBatchOfPurgeableBinaries(
				wfCatalog, binaryType, volumeGuid, limit, offset )

	check( 'WFCatalog', 'string', '?string', '?number', '?number' )

	local indexStore = wfCatalog:indexStore()

	local inactiveAssetsOnly = false
	
	return getBatchOfPurgeableBinaries(
			indexStore, binaryType, volumeGuid, inactiveAssetsOnly, limit, offset )

end

--------------------------------------------------------------------------------

--[[!

	\brief Retrieve the current list of inactive (unimportant) purge candidates
	from the db, sorted by when they were last updated.

	\param wfCatalog (WFCatalog)

	\param binaryType (string) Only return purgeable binaries of the specified type:
		
		"original"
		"proxy"
		"xmp_develop"
		"aux" (this includes masks)
		"fullsize"
		"preview"
		"thumbnail2x"

	\param volumeGuid (optional string) Only return binaries that live on this specific volume.
	\param limit (optional number) Limit the number of binaries to search for.
	\param offset (optional number) Used to fetch the next batch of binaries.	
	
	\return <code>Observable< array ></code> An array of purge candidates, along
		with a function for getting the next batch of candidates. Example:
	
		local batch, getNextBatchFunc = WFCatVolumeAgentQueryHelper.getBatchOfInactiveBinaries( ... )
		...
		batch, getNextBatchFunc = getNextBatchFunc()
		...
		batch, getNextBatchFunc = getNextBatchFunc()
		...
		batch, getNextBatchFunc = getNextBatchFunc()
		...
		and so on.
	
		Each entry in the resulting batch array is of the following format:
		
		{ <local Id>, <doc Id>, <file size>, <optional table of binary sizes> }

		Example batch:

		{
			{ 1, '11111111111111111111111111111111', 1000, { '2560' } },
			{ 2, '22222222222222222222222222222222', 3000, { '2560', 'panorama' } },
			{ 3, '33333333333333333333333333333333', 2000, { '2560', 'fullsize' } },
		}

--]]

function WFCatVolumeAgentQueryHelper.getBatchOfInactiveBinaries(
				wfCatalog, binaryType, volumeGuid, limit, offset )

	check( 'WFCatalog', 'string', '?string', '?number', '?number' )

	local indexStore = wfCatalog:indexStore()

	local inactiveAssetsOnly = true
	
	return getBatchOfPurgeableBinaries(
			indexStore, binaryType, volumeGuid, inactiveAssetsOnly, limit, offset )

end

--------------------------------------------------------------------------------

-- @mark Size APIs

--------------------------------------------------------------------------------

--[[!

	\brief Get the size of all binaries of a specified type that are purgeable.
		Purgeable here means that the binaries are synced with Oz, and not marked
		for local storage.

	\param indexStore (WFRxIndexStore)
	\param binaryType (string) Only return purgeable binaries of the specified type:
		"original"
		"proxy"
		"xmp_develop"
		"aux"
		"fullsize"
		"preview"
		"thumbnail2x"
	\param maybeVolumeGuid (optional string) If set, only binaries living on the specified
		volume will be counted.
	\param optBinaryStrategy (optional string) LrM-Only: The download strategy.
	
	\return <code>Observable< number ></code> Size of the binaries.

--]]

function WFCatVolumeAgentQueryHelper.getSizeOfPurgeableBinariesForType( indexStore, binaryType, maybeVolumeGuid, optBinaryStrategy )

	local sizeStream = Observable.just( { 0 } )

	local offlineUseFilter = " "
	local volumeGuidFilter = " "
	
	if WFPlatformInfo.isMobile() then
	
		offlineUseFilter = getIsNotMarkedForOfflineUseFilter( 'AND' )
	
	elseif maybeVolumeGuid then
	
		volumeGuidFilter = " AND volumeGuidUsedByFile == ? "
	
	end

	if binaryType == 'original' or binaryType == 'proxy' then
	
		-- All Originals/Proxies not marked for local use.
		
		local queryStr = string.format(
			[[ SELECT SUM( fileSize )
					FROM purgeCandidates
					WHERE binaryType == ?
					AND hasLocalFile == 1
					AND isPurgeable == 1
					%s
					%s ]], 
			offlineUseFilter,
			volumeGuidFilter )

		sizeStream = indexStore:queryOnce {
				sql = queryStr,
				quiesceUntilUpToDate = true,
				singleColumnQuery = true,
				binaryType,
				maybeVolumeGuid
			}
	
		if WFPlatformInfo.isMobile() and optBinaryStrategy then
		
			local preferredBinaryType
		
			if binaryType == 'original' and optBinaryStrategy== LrmConfig.StrategyPreferProxy then

				-- In StrategyPreferProxy mode, if an album is marked for local use, originals
				-- associated with assets in that album are considered purgeable - and therefore
				-- a part of the photo cache - as long as they have an associated proxy
				-- (either locally, or on Oz).

				preferredBinaryType = 'proxy'

			elseif binaryType == 'proxy' and optBinaryStrategy == LrmConfig.StrategyPreferMaster then

				-- In StrategyPreferMaster mode, if an album is marked for local use, proxies
				-- associated with assets in that album are considered purgeable - and therefore
				-- a part of the photo cache - as long as they have an associated original
				-- (either locally, or on Oz).

				preferredBinaryType = 'original'

			end

			if preferredBinaryType then

				local preferredQueryStr = string.format(
					[[ SELECT SUM( fileSize )
							FROM purgeCandidates
							WHERE binaryType == ?
							AND hasLocalFile == 1
							AND isPurgeable == 1
							AND docId IN (
								SELECT docId
									FROM purgeCandidates
									WHERE binaryType == ?
									AND ( hasLocalFile == 1
										OR ozHasBinary == 1 ) )
							%s ]], 
					getIsMarkedForOfflineUseFilter( 'AND' ) )

				local preferredSizeStream = indexStore:queryOnce {
						sql = preferredQueryStr,
						quiesceUntilUpToDate = true,
						singleColumnQuery = true,
						binaryType,
						preferredBinaryType
					}

				sizeStream = Observable.combineLatest( sizeStream, preferredSizeStream )

					:map( function( rows, preferredRows )

						local size = rows and rows[ 1 ] or 0
						local preferredSize = preferredRows and preferredRows[ 1 ] or 0

						return { size + preferredSize }

					end )

			end
		
		end

	elseif binaryType == 'xmp_develop' then
	
		local queryStr, maybeInactiveThresholdParam = getDevelopSettingsPurgeCandidates()

		sizeStream = indexStore:queryOnce {
				sql = queryStr,
				quiesceUntilUpToDate = true,
				singleColumnQuery = true,
				maybeInactiveThresholdParam
			}
			
			:flatMap( function( developSettingsSha256s )
			
				return queryForSizeOfBinariesWithContentIdsOrSha256s( 
						indexStore, binaryType, developSettingsSha256s )
			
			end )

	elseif binaryType == 'aux' then
	
		local queryStr, maybeInactiveThresholdParam = getAuxPurgeCandidates()

		sizeStream = indexStore:queryOnce {
				sql = queryStr,
				quiesceUntilUpToDate = true,
				singleColumnQuery = true,
				maybeInactiveThresholdParam
			}
			
			:flatMap( function( purgeCandidateFingerprints )
			
				return queryForSizeOfBinariesWithContentIdsOrSha256s( 
						indexStore, binaryType, purgeCandidateFingerprints )
			
			end )
		
	elseif WFPlatformInfo.isMobile() then

		-- Renditions

		local renditionsQueryStr = string.format(
				[[ SELECT DISTINCT localId, size
					FROM cacheReferences
					INNER JOIN purgeCandidates ON purgeCandidates.docId == cacheReferences.docId
					WHERE cacheReferences.renditionName == ?
						AND cacheReferences.hasUploaded == 1
						%s ]],
					offlineUseFilter )
		
		sizeStream = indexStore:queryOnce {
				sql = renditionsQueryStr,
				quiesceUntilUpToDate = true,
				binaryType
			}
			
			:map( function( rows )
							
				local totalSize = 0
				
				for _, row in ipairs( rows ) do
				
					local size = row[ 2 ]
					
					totalSize = totalSize + size
				
				end
							
				return { totalSize }
				
			end )

	end

	return sizeStream
	
		:map( function( rows )
			return rows and rows[ 1 ] or 0
		end  )

end

--------------------------------------------------------------------------------

--[[!

	\brief Get the size of all auxiliaries objects on the device that are purgeable
		in the specified album. Purgeable here means that the binaries are synced with Oz,
		and not marked for local storage.

	\param indexStore (WFRxIndexStore)
	\param albumId (string) The album document Id.

	\return <code>Observable< number ></code> Size of the binaries.

--]]

function WFCatVolumeAgentQueryHelper.getSizeOfPurgeableAuxInAlbum( indexStore, albumId )

	local queryStr, maybeInactiveThresholdParam = getAuxPurgeCandidates( false, albumId )

	return indexStore:queryOnce {
			sql = queryStr,
			quiesceUntilUpToDate = true,
			singleColumnQuery = true,
			maybeInactiveThresholdParam
		}

		:flatMap( function( purgeCandidateFingerprints )

			return queryForSizeOfBinariesWithContentIdsOrSha256s(
					indexStore, 'aux', purgeCandidateFingerprints, albumId )

		end )

		:map( function( rows )
			return rows and rows[ 1 ] or 0
		end  )

end

--------------------------------------------------------------------------------

--[[!

	\brief Get the size of all binaries of a specified type that are not synced and
		not marked for local use.

	\param indexStore (WFRxIndexStore)
	\param binaryType (string) Only return purgeable binaries of the specified type:
		"original"
		"proxy"
		"xmp_develop"
		"aux"
		Note that renditions are intentionally not supported here.
	\param maybeVolumeGuid (optional string) If set, only binaries living on the specified
		volume will be counted.
	
	\return <code>Observable< number ></code> Size of the binaries.

--]]

function WFCatVolumeAgentQueryHelper.getSizeOfUnsyncedBinariesForType( indexStore, binaryType, maybeVolumeGuid )

	local sizeStream = Observable.just( { 0 } )

	local offlineUseFilter = " "
	local volumeGuidFilter = " "
	
	if WFPlatformInfo.isMobile() then
	
		offlineUseFilter = getIsNotMarkedForOfflineUseFilter( 'AND' )
	
	elseif maybeVolumeGuid then
	
		offlineUseFilter = " AND isMarkedForOfflineUse == 0 "
		volumeGuidFilter = " AND volumeGuidUsedByFile == ? "

	end

	if binaryType == 'original' or binaryType == 'proxy' then
		
		local queryStr = string.format(
			[[ SELECT SUM( fileSize )
					FROM purgeCandidates
					WHERE binaryType == ?
					AND hasLocalFile == 1
					AND ozHasBinary == 0
					%s
					%s ]], 
			offlineUseFilter,
			volumeGuidFilter )

		sizeStream = indexStore:queryOnce {
				sql = queryStr,
				quiesceUntilUpToDate = true,
				singleColumnQuery = true,
				binaryType,
				maybeVolumeGuid
			}

	elseif binaryType == 'xmp_develop' or binaryType == 'aux' then
	
		local queryStr = getUnsyncedAuxOrDevelopSettings( binaryType )

		sizeStream = indexStore:queryOnce {
				sql = queryStr,
				quiesceUntilUpToDate = true,
				singleColumnQuery = true
			}

			:flatMap( function( sha256s )

				return queryForSizeOfBinariesWithContentIdsOrSha256s( 
						indexStore, binaryType, sha256s )

			end )
	
	end

	return sizeStream
	
		:map( function( rows )
			return rows and rows[ 1 ] or 0
		end  )

end

--------------------------------------------------------------------------------

--[[!

	\brief Get the size of all binaries of a specified type that are marked for
		offline use through a parent album.

	\param indexStore (WFRxIndexStore)
	\param binaryType (string) Only return purgeable binaries of the specified type:
		"original"
		"proxy"
		"xmp_develop"
		"aux"
		"fullsize"
		"preview"
		"thumbnail2x"
	\param onlyCountLocalBinaries (optional boolean )
	\param optBinaryStrategy (optional string) LrM-Only: The download strategy.
	\param optOnlyInAlbumId (optional string) Only count binaries within this album, if specified.

	\return <code>Observable< number ></code> Size of the binaries.

--]]

function WFCatVolumeAgentQueryHelper.getSizeOfStoredLocalBinariesForType(
		indexStore, binaryType, onlyCountLocalBinaries, optBinaryStrategy, optOnlyInAlbumId )

	local sizeStream = Observable.just( { 0 } )

	local offlineUseFilter = WFPlatformInfo.isMobile()
			and getIsMarkedForOfflineUseFilter( 'AND' )
			or 'AND isMarkedForOfflineUse == 1'

	local maybeAlbumFilter = optOnlyInAlbumId and getAlbumFilter( optOnlyInAlbumId ) or ' '

	-- [jandrew 2022-05-25]: On Mobile clients, we only count what we have actually
	-- downloaded as part of the locally stored bucket. On LrD, we count what we have
	-- downloaded and what still needs to be downloaded; this matches the old behavior
	-- in LrD.
	--
	-- By only counting what has been downloaded for the mobile clients, there's no chance
	-- we'll accidentally count something that isn't going to be downloaded.

	local hasLocalFileFilter = ( onlyCountLocalBinaries or WFPlatformInfo.isMobile() )
			and ' AND hasLocalFile == 1 '
			or ' '
			
	local isNotVideoFilter = WFPlatformInfo.isMobile()
		and ' AND isVideo == 0 '
		or ' '

	if binaryType == 'original' then
		
		local queryStr
		
		if WFPlatformInfo.isMobile() and optBinaryStrategy == LrmConfig.StrategyPreferProxy then

			-- In StrategyPreferProxy mode, if an album is marked for local use, originals
			-- associated with assets in that album are considered purgeable - and therefore
			-- a part of the photo cache - as long as they have an associated proxy
			-- (either locally, or on Oz).

			queryStr = string.format(
				[[ SELECT SUM( fileSize )
					FROM purgeCandidates
					%s
					WHERE binaryType == ?
					AND purgeCandidates.docId IN (
						SELECT docId
							FROM purgeCandidates
							WHERE binaryType == 'proxy'
							AND hasLocalFile == 0
							AND ozHasBinary == 0 )
					%s
					%s
					%s ]],
				maybeAlbumFilter,
				hasLocalFileFilter,
				offlineUseFilter,
				isNotVideoFilter )

		else

			queryStr = string.format(
				[[ SELECT SUM( fileSize )
					FROM purgeCandidates
					%s
					WHERE binaryType == ?
					%s
					%s
					%s ]],
				maybeAlbumFilter,
				hasLocalFileFilter,
				offlineUseFilter,
				isNotVideoFilter )

		end
		
		sizeStream = indexStore:queryOnce {
				sql = queryStr,
				quiesceUntilUpToDate = true,
				singleColumnQuery = true,
				binaryType
			}

	elseif binaryType == 'proxy' then

		local queryStr

		if WFPlatformInfo.isMobile() and optBinaryStrategy == LrmConfig.StrategyPreferMaster then

			-- In StrategyPreferMaster mode, if an album is marked for local use, proxies
			-- associated with assets in that album are considered purgeable - and therefore
			-- a part of the photo cache - as long as they have an associated original
			-- (either locally, or on Oz).

			-- See ozBinaryDownloaderMethods:startDownloading in
			-- LrmOzBinaryDownloader for more context.

			queryStr = string.format(
				[[ SELECT SUM( fileSize )
					FROM purgeCandidates
					%s
					WHERE binaryType == ?
					AND purgeCandidates.docId IN (
						SELECT docId
							FROM purgeCandidates
							WHERE binaryType == 'original'
							AND hasLocalFile == 0
							AND ozHasBinary == 0 )
					%s
					%s
					%s ]],
				maybeAlbumFilter,
				hasLocalFileFilter,
				offlineUseFilter,
				isNotVideoFilter )

		else

			queryStr = string.format(
				[[ SELECT SUM( fileSize )
					FROM purgeCandidates
					%s
					WHERE binaryType == ?
					%s
					%s
					%s ]],
				maybeAlbumFilter,
				hasLocalFileFilter,
				offlineUseFilter,
				isNotVideoFilter )

		end

		sizeStream = indexStore:queryOnce {
				sql = queryStr,
				quiesceUntilUpToDate = true,
				singleColumnQuery = true,
				binaryType
			}

	elseif binaryType == 'xmp_develop' or binaryType == 'aux' then
	
		local queryStr = string.format(
			[[ SELECT contentIdOrSha256
				FROM purgeCandidates
				%s
				WHERE binaryType == ?
				%s
				%s
				%s ]],
			maybeAlbumFilter,
			hasLocalFileFilter,
			offlineUseFilter,
			isNotVideoFilter )

		sizeStream = indexStore:queryOnce {
				sql = queryStr,
				quiesceUntilUpToDate = true,
				singleColumnQuery = true,
				binaryType
			}
			
			:flatMap( function( contentIdsOrSha256s )
			
				return queryForSizeOfBinariesWithContentIdsOrSha256s( 
						indexStore, binaryType, contentIdsOrSha256s, optOnlyInAlbumId )
			
			end )
			
	elseif WFPlatformInfo.isMobile() then

		-- Renditions

		local renditionsQueryStr = string.format(
				[[ SELECT DISTINCT localId, size
					FROM cacheReferences
					INNER JOIN purgeCandidates ON purgeCandidates.docId == cacheReferences.docId
					%s
					WHERE cacheReferences.renditionName == ?
						%s
						%s ]],
				maybeAlbumFilter,
				offlineUseFilter,
				isNotVideoFilter )

		sizeStream = indexStore:queryOnce {
				sql = renditionsQueryStr,
				quiesceUntilUpToDate = true,
				binaryType
			}
			
			:map( function( rows )
							
				local totalSize = 0
				
				for _, row in ipairs( rows ) do
				
					local size = row[ 2 ]
					
					totalSize = totalSize + size
				
				end
							
				return { totalSize }
				
			end )

	end

	return sizeStream
	
		:map( function( rows )
			return rows and rows[ 1 ] or 0
		end  )

end

--------------------------------------------------------------------------------

--[[!

	\brief Get the size of all binaries of a specified type. This function should
		only be used when keepAllProxies or keepAllOriginals is used for fetching
		the total size of the binary regardless of their local or Oz sync status.

		The keepAll options will both cause the xmp_develop and aux files to be
		kept as local Storage (prevented from purge).

		For example, it can be used to calculate how much space is required to
		store all originals on a device.

	\param indexStore (WFRxIndexStore)
	\param binaryType (string) Only return purgeable binaries of the specified type:
		"original"
		"proxy"	
	
	\return <code>Observable< number ></code> Size of the binaries.

--]]

function WFCatVolumeAgentQueryHelper.getSizeOfKeepAllBinariesForType( indexStore, binaryType, onlyCountLocalBinaries )

	local hasLocalFileFilter = onlyCountLocalBinaries
			and ' AND hasLocalFile == 1 '
			or ''

	local queryStr

	if binaryType == 'original' or binaryType == 'proxy' then

		queryStr = string.format(
				[[ SELECT SUM( fileSize )
					FROM purgeCandidates
					WHERE binaryType == ?
					%s ]], 
				hasLocalFileFilter )


	elseif binaryType == 'xmp_develop' or binaryType == 'aux' then

		queryStr = string.format(
				[[ SELECT SUM( fileSize )
					FROM (
						SELECT DISTINCT contentIdOrSha256, fileSize
						FROM purgeCandidates
						WHERE binaryType == ?
						%s
					) ]],
				hasLocalFileFilter )
	end

	if queryStr then

		return indexStore:queryOnce {
				sql = queryStr,
				quiesceUntilUpToDate = true,
				singleColumnQuery = true,
				binaryType
			}
			
			:map( function( rows )
				return rows and rows[ 1 ] or 0
			end )

	else

		logger:warnf( "getSizeOfKeepAllBinariesForType only works for originals, proxies, xmp_develop and aux, not %s", binaryType )

		return Observable.just( 0 )
	
	end

end

--------------------------------------------------------------------------------

-- @mark Aux Reference Counting API

--------------------------------------------------------------------------------

--[[!

	\brief Periodically checks for aux binariers that no longer have any develop
		references, if they are on Oz, then adds them to a purge list table. 
		This purge list can then be passed to WFCatPurgeHelper.deleteFilesByPurgeList 
		to delete the aforementioned binaries.

	\param wfCatalog (WFCatalog)
	
	\return <code>Observable< table ></code> The purge list.

--]]

function WFCatVolumeAgentQueryHelper.untrackedBinariesPurgeListStream( wfCatalog )

	local indexStore = wfCatalog:indexStore()

	-- The first query identifies all fingerprints that have localOnly paths, but
	-- are not referenced by the develop settings.

	-- For example, imagine you have two documents A and B that refer to the same 
	-- aux object. Both of these documents have references to the aux object in their
	-- develop section, and both have a localOnly annotation that points to the same
	-- file on disk.

	-- Now if we delete document A, then document A will no longer have a reference to
	-- the aux object in the develop session. However, it will still have a reference
	-- to the file in the localOnly section. Document B still has both a develop 
	-- reference and a localOnly reference.

	-- In this state, the first SELECT will return the fingerprint of the aux object 
	-- because of document A. The second SELECT will return the aux object fingerprint
	-- because of document B. The net result is that the query won't identify any
	-- untracked aux object fingerprints (which is correct).

	-- If we then then delete document B, it will lose its develop reference. The first
	-- SELECT will then return the aux object fingerprint because of both documents A
	-- and B, and the second SELECT will return nothing. The net result is that the
	-- query will identify one untracked auxiliary binary.

	return indexStore:query{
			sql = [[ SELECT DISTINCT fingerprint
						FROM auxReferenceTracker
						WHERE hasDocReference = 0
					EXCEPT
					SELECT fingerprint
						FROM (
							SELECT DISTINCT fingerprint
								FROM auxReferenceTracker
								WHERE hasDocReference = 1
							UNION
							SELECT DISTINCT contentIdOrSha256 AS fingerprint
								FROM purgeCandidates
								WHERE binaryType == 'aux'
								AND isPurgeable = 0 ) ]],
			quiesceUntilUpToDate = true,
			singleColumnQuery = true,
			calm = WFPlatformInfo.isUnitTest() and 0.01 or 60
		}
			
		:flatMapLatest( function( fingerprints )
		
			if not next( fingerprints ) then
				return Observable.just( {} )
			end
		
			-- The second query finds all documents with localOnly references to
			-- the fingerprints identified in the above query.
		
			return indexStore:query{
					sql = [[ SELECT localId, fingerprint
								FROM auxReferenceTracker
								WHERE fingerprint IN CARRAY( ?, ?, 'char*' ) ]],
					quiesceUntilUpToDate = true,
					calm = WFPlatformInfo.isUnitTest() and 0.01 or 60,
					fingerprints,
					#fingerprints
				}
			
				:map( function( rows )
				
					-- Build the purge list
					
					local purgeList = {}
				
					for _, row in ipairs( rows ) do
					
						local localId = row[ 1 ]
						local fingerprint = row[ 2 ]
						
						local fingerprintsForLocalId = purgeList[ localId ] or {}
						fingerprintsForLocalId[ #fingerprintsForLocalId + 1 ] = fingerprint
						purgeList[ localId ] = fingerprintsForLocalId
					
					end
					
					if next( purgeList ) then
					
						-- Restructure purgeList into the format understood
						-- by WFCatPurgeHelper
						
						purgeList.binaryType = 'aux'
						purgeList = { purgeList }
						
					end
					
					return purgeList
				
				end )
		
		end )

end

--------------------------------------------------------------------------------

--[[!

	\brief Generates a purge list that can be used to purge all the auxiliary
		binaries that referenced in the provided documents (docIds) that, and
		are NOT referenced outside of the provided space (spaceId).
		
		Note that this API intentionally ignores metadata references and is
		concerned with documents that have _localOnly file path references
		to the same auxiliary files.

	\param wfCatalog (WFCatalog)
	\param spaceId (string) The id of the space that is being deleted/downgraded.
	\param docIds (table) A table of document Ids
	
	\return <code>Observable< table ></code> Returns a purge list that can be
		passed to the WFCatPurgeHelper.deleteFilesByPurgeList API. Format:
		
		{
			binaryType = 'aux'
			localId = { aux1_fingerprint, aux2_fingerprint, ... },
			localId = { aux1_fingerprint, aux2_fingerprint, ... },
			...
		}

--]]

function WFCatVolumeAgentQueryHelper.getAuxPurgeListForDeletedOrDowngradedSpace( wfCatalog, spaceId, docIds )

	local indexStore = wfCatalog:indexStore()

	-- FIXME [jandrew 2022-08-05]: The following queries, and likely some
	-- other similar code in this file, could be optimized by performing
	-- the filtering within Lua. A query such as:
	--
	--[[ SELECT docId, digest
		   FROM localAuxTracker
				WHERE docId IN CARRAY(?, ?, 'char*')
				AND spaceId != ?
				GROUP BY digest ]]
	--
	-- ... may work in this specific case.

	return indexStore:queryOnce {
			sql = [[ SELECT DISTINCT digest
						FROM localAuxTracker
							WHERE docId IN CARRAY( ?, ?, 'char*' )
					EXCEPT
					SELECT DISTINCT digest
						FROM localAuxTracker
							WHERE spaceId != ? OR spaceId IS NULL ]],
			quiesceUntilUpToDate = true,
			singleColumnQuery = true,
			docIds,
			#docIds,
			spaceId
		}
			
		:flatMap( function( digestsOfUnsharedAuxFiles )
		
			return indexStore:queryOnce {
				sql = [[ SELECT docId, digest
							FROM localAuxTracker
								WHERE digest IN CARRAY( ?, ?, 'char*' ) ]],
				quiesceUntilUpToDate = true,
				digestsOfUnsharedAuxFiles,
				#digestsOfUnsharedAuxFiles
			} 
		
		end )
		
		:map( function( docsWithUnsharedAuxFiles )
		
			local auxPurgeList = {}

			for _, row in ipairs( docsWithUnsharedAuxFiles ) do

				auxPurgeList.binaryType = 'aux'

				local docId = row[ 1 ]
				local digest = row[ 2 ]

				local entry = auxPurgeList[ docId ] or {}
				entry[ #entry + 1 ] = digest
				auxPurgeList[ docId ] = entry

			end

			return auxPurgeList

		end )

end

--------------------------------------------------------------------------------

-- @mark Develop Settings Reference Checking API

--------------------------------------------------------------------------------

--[[!

	\brief Check if there are other docs that reference the same develop settings file
		that we wishes to purge.

	\param wfCatalog (WFCatalog)
	\param docId (string) The id of the doc that requests to purge the develop settings.
	\param developSettingsSha256 (string) The sha256 of the develop settings.

	
	\return <code>Observable< boolean ></code> Returns true if the develop settings can be
		purged, false otherwise.
--]]

function WFCatVolumeAgentQueryHelper.isDevelopSettingsPurgeable( wfCatalog, docId, developSettingsSha256 )

	local sql = [[SELECT COUNT(*)
					FROM purgeCandidates
					WHERE docId != ?
						AND binaryType == 'xmp_develop'
						AND contentIdOrSha256 == ?
						AND isPurgeable == 0]]
	
	return wfCatalog:indexStore():queryOnce
		{
			sql = sql,
			quiesceUntilUpToDate = true,
			singleColumnQuery = true,
			docId,
			developSettingsSha256
		}
		
		:map( function( rows ) 
		
			local count = getOnlyValueFromRows( rows )
			return count == 0
			
		end )

end

--------------------------------------------------------------------------------

-- @mark DEBUG APIs

--------------------------------------------------------------------------------

function WFCatVolumeAgentQueryHelper._DEBUG_groupResultsByDoc( rows )
	return groupResultsByDoc( rows )
end

--------------------------------------------------------------------------------

function WFCatVolumeAgentQueryHelper._DEBUG_numberOfLocalBinariesOfType( wfCatalog, binaryType )

	local indexStore = wfCatalog:indexStore()

	if binaryType == 'original' or binaryType == 'proxy' then

		return indexStore:queryOnce {
				sql = [[ SELECT COUNT( * )
							FROM purgeCandidates
							WHERE binaryType == ?
								AND hasLocalFile == 1 ]],
				quiesceUntilUpToDate = true,
				singleColumnQuery = true,
				binaryType
			}
			
			:map( getOnlyValueFromRows )

	elseif binaryType == 'xmp_develop' or binaryType == 'aux' then
	
		return indexStore:queryOnce {
				sql = [[ SELECT COUNT( DISTINCT contentIdOrSha256 )
							FROM purgeCandidates
							WHERE binaryType == ?
								AND hasLocalFile == 1 ]],
				quiesceUntilUpToDate = true,
				singleColumnQuery = true,
				binaryType
			}
			
			:map( getOnlyValueFromRows )
	
	elseif WFPlatformInfo.isMobile() then

		-- Renditions

		return indexStore:queryOnce {
				sql = [[ SELECT COUNT( DISTINCT docId )
							FROM cacheReferences
							WHERE cacheReferences.renditionName == ? ]],
				quiesceUntilUpToDate = true,
				singleColumnQuery = true,
				binaryType
			}
			
			:map( getOnlyValueFromRows )
	
	end

end

--------------------------------------------------------------------------------

function WFCatVolumeAgentQueryHelper._DEBUG_sizeOfLocalBinariesOfType( wfCatalog, binaryType )

	local indexStore = wfCatalog:indexStore()

	if binaryType == 'original' or binaryType == 'proxy' then

		return indexStore:queryOnce {
				sql = [[ SELECT SUM( fileSize )
							FROM purgeCandidates
							WHERE binaryType == ?
								AND hasLocalFile == 1 ]],
				quiesceUntilUpToDate = true,
				singleColumnQuery = true,
				binaryType
			}
			
			:map( getOnlyValueFromRows )

	elseif binaryType == 'xmp_develop' or binaryType == 'aux' then
	
		return indexStore:queryOnce {
				sql = [[ SELECT DISTINCT contentIdOrSha256, fileSize
							FROM purgeCandidates
							WHERE binaryType == ?
								AND hasLocalFile == 1 ]],
				quiesceUntilUpToDate = true,
				binaryType
			}
			
			:map( function( rows )
				
				local totalSize = 0
				
				for _, row in ipairs( rows ) do
				
					local size = row[ 2 ] or 0
				
					totalSize = totalSize + size
				
				end
				
				return totalSize
	
			end )
	
	elseif WFPlatformInfo.isMobile() then

		-- Renditions

		return indexStore:queryOnce {
				sql = [[ SELECT SUM( size )
							FROM cacheReferences
							WHERE cacheReferences.renditionName == ? ]],
				quiesceUntilUpToDate = true,
				singleColumnQuery = true,
				binaryType
			}
			
			:map( getOnlyValueFromRows )
	
	end

end

--------------------------------------------------------------------------------

function WFCatVolumeAgentQueryHelper._DEBUG_dumpPurgeCandidatesContentsForType( indexStore, binaryType )

	return indexStore:queryOnce {
			sql = [[ SELECT * FROM purgeCandidates
						WHERE binaryType == ?
						ORDER BY localId ]],
			quiesceUntilUpToDate = true,
			binaryType
		}

end

--------------------------------------------------------------------------------

return WFCatVolumeAgentQueryHelper
