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

ADOBE CONFIDENTIAL
------------------
Copyright 2015 Adobe Systems Incorporated
All Rights Reserved.

NOTICE: All information contained herein is, and remain the property of
Adobe Systems Incorporated and its suppliers, if any. The intellectual and
technical concepts contained herein are proprietary to Adobe Systems
Incorporated and its suppliers and may be covered by U.S. and Foreign
Patents, patents in process, and are protected by trade secret or copyright
law.  Dissemination of this information or reproduction of this material is
strictly forbidden unless prior written permission is obtained from
Adobe Systems Incorporated.

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

local WFPlatformInfo = require "WFPlatformInfo"
local WFLogger = require 'WFLogger'

--==============================================================================


local LrmAllPhotosQueryBuilder = {}

local logger = WFLogger 'LrmAllPhotosQueryBuilder'
 -- :setLevel 'debug'

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


function LrmAllPhotosQueryBuilder.getCollectionInfoFields (isSearch, isTrash)

	local fields

	if WFPlatformInfo.isAndroid then
	
		LrmAllPhotosQueryBuilder.purgeDateColumnId = 15 

		fields = [[croppedWidth, croppedHeight, captureDate,
				  fileName, modifiedDate, thumbnailMD5, importTimestamp as importDate, isMergedHDR, hasThumbnail, importedByUser, isVideo, duration]]

		if isTrash then
			fields = "docId as assetId, " .. fields .. ", originalFileSize, purgeDate"
		else
			fields = "coreInfo.docId as assetId, " .. fields
		end

	else
	
		fields = [[captureDate, contentType, duration, importTimestamp, flag,
				  croppedWidth, croppedHeight, fileName, width, height, apertureValue, isoSpeedRatings, 
				  exposureTime, cameraMaker, cameraModel, rating, modifiedDate, isVideo, localUrl, fnumber, isMergedHDR, 
				  hasThumbnail, importedByUser]]
				  
		if isTrash then
			fields = "docId as assetId, "	.. fields .. ", purgeDate"
		else
			fields = "coreInfo.docId as assetId, "	.. fields .. ", NULL as purgeDate"
		end
	
		fields = fields .. [[, 0 AS commentCount, 0 AS favoriteCount ]]
	end

	if isSearch then
		fields = fields .. ", TEMP_search.sortOrder"
	end
	
	return fields
	
end

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

local function isRatingPredicateRequired( ratingFilter, ratingComparisonOperator )

	if ratingComparisonOperator == ">=" and ratingFilter == 0 then
		return false
	elseif ratingComparisonOperator == "<=" and ratingFilter == 5 then
		return false
	end
	return true

end

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

local function addRatingPredicate( ratingFilter, ratingComparisonOperator, predicate, queryParams )

	predicate = predicate .. " rating " .. ratingComparisonOperator .. " ?"
	table.insert( queryParams, ratingFilter )
	
	return predicate, queryParams

end

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

local function addPersonPredicate( personId, predicate, queryParams )

	predicate = predicate .. [[ coreInfo.docId IN
		(SELECT faces.assetId FROM faces JOIN persons p ON p.docId = faces.personId
		WHERE p.docId = ? OR p.primaryId == ?)
			]]

	table.insert(queryParams, personId)
	table.insert(queryParams, personId)
	
	return predicate, queryParams

end

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

local function makeAllPhotoViewPredicate( assetFilter, assetTypeFilter, keyword, edited, ratingFilter, ratingComparisonOperator, personId )
	
	local queryParams		= {}
	local predicate 		= ""
	local predicateStart	= [[WHERE ]]
	
	if #assetFilter > 0 then
		
		predicate = predicateStart .. "("
	
		for i = 1, #assetFilter do
		
			if i > 1 then
				predicate = predicate .. " OR "
			end
			
			local filter = assetFilter[ i ]
			
			if filter == 'pick' or filter == 'reject' or filter == 'unflagged' then
				predicate = predicate .. "flag == ?"
				table.insert( queryParams, filter )
			elseif filter == 'favorite' then
				predicate = predicate .. "favoriteCount > 0"
			elseif filter == 'comment' then
				predicate = predicate .. "commentCount > 0"
			end
			
		end
		
		predicate = predicate .. ")"
	end
	
	if assetTypeFilter and #assetTypeFilter > 0 then
	
		if predicate == "" then
			predicate = predicateStart .. "("
		else
			predicate = predicate .. " AND ("
		end
		
		for i = 1, #assetTypeFilter do
		
			if i > 1 then
				predicate = predicate .. " OR "
			end
			
			local assetType = assetTypeFilter[ i ]
			
			if assetType == 'image' then
				predicate = predicate .. "isVideo == ?"
				table.insert( queryParams, 0 )
			elseif assetType == 'video' then
				predicate = predicate .. "isVideo == ?"
				table.insert( queryParams, 1 )
			end
			
		end
		
		predicate = predicate .. ")"
	
	end
	
	
	if keyword ~= nil and #keyword > 0 then
	
		if predicate == "" then
			predicate = predicateStart .. "("
		else
			predicate = predicate .. " AND ("
		end
		
		for i = 1, #keyword do
		
			if i > 1 then
				predicate = predicate .. " OR "
			end
			
			predicate = predicate .. "keyword == ? COLLATE NOCASE"
			table.insert( queryParams, keyword[ i ] )
			
		end
		
		predicate = predicate .. ")"
	
	end
	
	
	if edited ~= nil then
		if predicate == "" then
			predicate = predicateStart .. "("
		else
			predicate = predicate .. " AND ("
		end
	
		if edited == 1 then
			predicate = predicate .. "coreInfo.developUserUpdated IS NOT NULL"
		else
			predicate = predicate .. "coreInfo.developUserUpdated IS NULL"
		end
		predicate = predicate .. ")"
	end

	if isRatingPredicateRequired( ratingFilter, ratingComparisonOperator ) then
		if predicate == "" then
			predicate = predicateStart .. "("
		else
			predicate = predicate .. " AND ("
		end
		predicate, queryParams = addRatingPredicate( ratingFilter, ratingComparisonOperator, predicate, queryParams )
		predicate = predicate .. ")"
	end
	
	if personId then
		if predicate == "" then
			predicate = predicateStart .. "("
		else
			predicate = predicate .. " AND ("
		end
		predicate, queryParams = addPersonPredicate( personId, predicate, queryParams )
		predicate = predicate .. ")"
	end

	if predicate == "" then
		predicate = predicateStart .. "("
	else
		predicate = predicate .. " AND ("
	end

    predicate = predicate .. "spaceId is NULL )"

	
	return predicate, queryParams
end

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

function LrmAllPhotosQueryBuilder.buildCoreInfoQuery( activeAlbum, ascending, assetFilter, assetTypeFilter, keyword, edited, sortCriteria, 
														ratingFilter, ratingComparisonOperator, isSearch, personId, limit )

	local order = 'DESC'

	if ascending or isSearch then
		order = 'ASC'
	end

	local predicate = ""
	local queryParams = {}
	local selectSubqueries = ""
    local personAssetFilter = {}
    local personRatingFilter = 0
	
	if activeAlbum then

		if personId then
			predicate, queryParams = makeAllPhotoViewPredicate( personAssetFilter, assetTypeFilter, keyword, edited, personRatingFilter, ratingComparisonOperator, personId )
		else
			predicate, queryParams = makeAllPhotoViewPredicate( assetFilter, assetTypeFilter, keyword, edited, ratingFilter, ratingComparisonOperator, personId )
		end
	end
	
	-- INFO [pkamin 2013-12-11]: we map the filename criteria to the table column with lowercase names and apply the correct collation
	if sortCriteria == 'fileName' then
		sortCriteria = 'fileName COLLATE i18n_collate '
	end
	
	local secondOrder = 'coreInfo.captureDate'
	
	if isSearch then
		sortCriteria = 'TEMP_search.sortOrder'
	elseif sortCriteria == 'userDefined' then
		sortCriteria = 'sortOrder'
	elseif sortCriteria == 'captureDate' then
		secondOrder = 'coreInfo.docId'
	elseif sortCriteria == 'importDate' then
		sortCriteria = "importTimestamp"
	elseif sortCriteria == 'mostFavorited' then
		sortCriteria = "favoriteCount "
	end

	-- Build sub queries for comment count and favorite count
	
	if WFPlatformInfo.isAndroid then
		selectSubqueries = [[, 0 AS commentCount, 0 AS favoriteCount ]]
	end
	
	-- Build final SQL statement
	local collectionFields = LrmAllPhotosQueryBuilder.getCollectionInfoFields (isSearch)
	
	local sql = [[SELECT ]] .. collectionFields .. selectSubqueries .. [[ FROM coreInfo ]]

	if isSearch then
		sql = sql .. [[ INNER JOIN TEMP_search ON coreInfo.docId = TEMP_search.docId ]]
	end

	if keyword ~= nil and #keyword > 0 then
		sql = sql .. [[ INNER JOIN keywords ON coreInfo.docId = keywords.docId ]]
	end

	sql = sql .. predicate ..
		[[ ORDER BY ]] .. sortCriteria .. [[ ]] .. order .. [[, ]] .. secondOrder .. [[ ]] .. order
		
	if sortCriteria == 'modifiedDate' or ( sortCriteria == 'rating' and WFPlatformInfo.isAndroid ) then
		sql = sql .. [[, fileName COLLATE i18n_collate ]] .. order .. [[, coreInfo.docId ]] .. order
	end

	if activeAlbum == '10002000300040005000600070008008' and limit then
		sql = sql .. ' LIMIT ' .. limit
	end

	logger:debugf('sql is : %s , %s', sql, queryParams)

	-- Fire query
	
	return sql, queryParams
						
end

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

return LrmAllPhotosQueryBuilder

