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

WFCatImportManifestQueries.lua

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

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

NOTICE: All information contained herein is, and remains 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 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 Systems Incorporated.

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

local WFCatImportManifestQueriesUtils = require 'WFCatImportManifestQueriesUtils'

local inputField = WFCatImportManifestQueriesUtils.inputField

--------------------------------------------------------------------------------
-- Table creation
	
local createQueries = {

		variablesTable = "CREATE TABLE variables (" ..
							" name TEXT PRIMARY KEY," ..
							" value TEXT NOT NULL" ..
						" )",

		assetsTable = "CREATE TABLE assets (" ..
						" local_id INTEGER PRIMARY KEY," ..
						" global_id TEXT NOT NULL UNIQUE," ..
						" subType TEXT NOT NULL," ..
						" filePath TEXT NOT NULL," ..
						" fileWidth INTEGER NOT NULL," ..
						" fileHeight INTEGER NOT NULL," ..
						" touchTime TEXT," ..
						" ratingTime TEXT," ..
						" reviewTime TEXT," ..
						" devSettingTime TEXT," ..
						" xmpCameraRaw TEXT," ..
						" cameraProfileDigest TEXT," ..
						" lensProfileDigest TEXT," ..
						" croppedWidth INTEGER," ..
						" croppedHeight INTEGER," ..
						" xmp TEXT," ..
						" masterImage INTEGER," ..
						" ozAssetId TEXT," ..
						" duration TEXT," ..
						" frameRate TEXT," ..
						" country TEXT," ..
						" state TEXT," ..
						" city TEXT" ..
					" )",

		albumsTable = "CREATE TABLE albums (" ..
						" local_id INTEGER PRIMARY KEY," ..
						" subType TEXT NOT NULL," ..
						" name TEXT NOT NULL," ..
						" parent INTEGER," ..
						" cover INTEGER," ..
						" ozAlbumId TEXT," ..
						" FOREIGN KEY (cover) REFERENCES assets(local_id)" ..
					" )",

		album_asset = "CREATE TABLE album_asset (" ..
						" local_id INTEGER PRIMARY KEY," ..
						" album INTEGER, " ..
						" asset INTEGER," ..
						" ozAlbumAssetId TEXT," ..
						" position REAL," ..
						" UNIQUE (album,asset)," ..
						" FOREIGN KEY (album) REFERENCES albums(local_id)," ..
						" FOREIGN KEY (asset) REFERENCES assets(local_id)" ..
					" )",

		personTable = "CREATE TABLE persons (" ..
						" local_id INTEGER PRIMARY KEY," ..
						" name TEXT NOT NULL " ..
					" )",

		facesTable = "CREATE TABLE faces (" ..
						" local_id INTEGER PRIMARY KEY," ..
						" asset INTEGER NOT NULL, " ..
						" x REAL NOT NULL," ..
						" y REAL NOT NULL," ..
						" w REAL," ..
						" h REAL," ..
						" d REAL," ..
						" confidence INTEGER," ..
						" personId INTEGER," ..
						" FOREIGN KEY (asset) REFERENCES assets(local_id)" ..
						" FOREIGN KEY (personId) REFERENCES persons(local_id)" ..
					" )",
		
		stacksTable = "CREATE TABLE stacks (" ..
						" local_id INTEGER PRIMARY KEY," ..
						" cover INTEGER," ..
						" FOREIGN KEY (cover) REFERENCES assets(local_id)" ..
					" )",
	
		stacksAlbumTable = "CREATE TABLE stack_album (" ..
						" local_id INTEGER PRIMARY KEY," ..
						" stack INTEGER, " ..
						" album INTEGER," ..
						" UNIQUE (stack,album)," ..
						" FOREIGN KEY (stack) REFERENCES stacks(local_id)," ..
						" FOREIGN KEY (album) REFERENCES albums(local_id)" ..
					" )",
		
		stack_asset = "CREATE TABLE stack_asset (" ..
						" local_id INTEGER PRIMARY KEY," ..
						" stack INTEGER, " ..
						" asset INTEGER," ..
						" UNIQUE (stack,asset)," ..
						" FOREIGN KEY (stack) REFERENCES stacks(local_id)," ..
						" FOREIGN KEY (asset) REFERENCES assets(local_id)" ..
					" )",

}

--------------------------------------------------------------------------------
-- Note: Our preferred way was to make the table definition more declarative and use the common
-- information across manifast and Lrdata processing. 
-- As per Brian's suggestion, to keep it simple we are hardcoding the sql querries

local writeOperations = {
	
	addVariable = {
		sql = "INSERT INTO variables( name, value ) values( ?, ? )",
		inputFieldsInOrder = {
			inputField( "name", "text", true ),
			inputField( "value", "text", true ),
		},
		tableName = "variables",
	},
	
	addAsset = {
		sql = "INSERT INTO assets( local_id, global_id, subType, filePath, fileWidth, fileHeight, touchTime, ratingTime, reviewTime, devSettingTime, masterImage, ozAssetId, duration, frameRate, country, state, city ) values( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )",
		batchSize = 1000,
		inputFieldsInOrder = {
			inputField( "local_id", "integer", true ),
			inputField( "global_id", "text", true ),
			inputField( "subType", "text", true ),
			inputField( "filePath", "text", true ),
			inputField( "fileWidth", "integer", true ),
			inputField( "fileHeight", "integer", true ),
			inputField( "touchTime", "text", true ),
			inputField( "ratingTime", "text", false ),
			inputField( "reviewTime", "text", false ),
			inputField( "devSettingTime", "text", false ),
			inputField( "masterImage", "integer", false ),
			inputField( "ozAssetId", "uuid_or_empty", false ),
			inputField( "duration", "text", false ),
			inputField( "frameRate", "text", false ),
			inputField( "country", "text", false ),
			inputField( "state", "text", false ),
			inputField( "city", "text", false ),
		},
		tableName = "assets",
	},
	
	addAlbum = {
		sql = "INSERT INTO albums( local_id, subType, name, parent, cover, ozAlbumId ) values( ?, ?, ?, ?, ?, ? )",
		batchSize = 1000,
		inputFieldsInOrder = {
			inputField( "local_id", "integer", true ),
			inputField( "subType", "text", true ),
			inputField( "name", "text", true ),
			inputField( "parent", "integer", false ),
			inputField( "cover", "integer", false ),
			inputField( "ozAlbumId", "uuid_or_empty", false ),
		},
		tableName = "albums",
	},
	
	addAlbumAsset = {
		sql = "INSERT INTO album_asset( local_id, album, asset, ozAlbumAssetId, position ) values( ?, ?, ?, ?, ? )",
		batchSize = 1000,
		inputFieldsInOrder = {
			inputField( "local_id", "integer", true ),
			inputField( "album", "integer", true ),
			inputField( "asset", "integer", true ),
			inputField( "ozAlbumAssetId", "uuid_or_empty", false ),
			inputField( "position", "real", false ),
		},
		tableName = "album_asset",
	},
	
	addStack = {
		sql = "INSERT INTO stacks( local_id, cover ) values( ?, ? )",
		batchSize = 1000,
		inputFieldsInOrder = {
			inputField( "local_id", "integer", true ),
			inputField( "cover", "integer", false ),
		},
		tableName = "stacks",
	},
	
	addStackAsset = {
		sql = "INSERT INTO stack_asset( local_id, stack, asset ) values( ?, ?, ? )",
		batchSize = 1000,
		inputFieldsInOrder = {
			inputField( "local_id",  "integer", true ),
			inputField( "stack", "integer", true ), 
			inputField( "asset", "integer", true ),
		},
		tableName = "stack_asset",
	},
	
	addStackAlbum = {
		sql = "INSERT INTO stack_album( local_id, stack, album ) values( ?, ?, ? )",
		batchSize = 1000,
		inputFieldsInOrder = {
			inputField( "local_id", "integer", true ),
			inputField( "stack", "integer", true ),
			inputField( "album", "integer", true ),
		},
		tableName = "stack_album",
	},
	
	addFace = {
		sql = "INSERT INTO faces( local_id, asset, x, y, w, h, d, confidence, personId ) values( ?, ?, ?, ?, ?, ?, ?, ?, ? )",
		batchSize = 1000,
		inputFieldsInOrder = {
			inputField( "local_id", "integer", true ),
			inputField( "asset", "integer", true ),
			inputField( "x", "real", true ),
			inputField( "y", "real", true ),
			inputField( "w", "real", false ),
			inputField( "h", "real", false ),
			inputField( "d", "real", false ),
			inputField( "confidence", "integer", false ),
			inputField( "personId", "integer", false ),
		},
		tableName = "faces",
	},
	
	addPerson = {
		sql = "INSERT INTO persons( local_id, name ) values( ?, ? )",
		batchSize = 1000,
		inputFieldsInOrder = {
			inputField( "local_id", "integer", true ),
			inputField( "name", "text", true ),
		},
		tableName = "persons",
	},
	
	setAssetXMP = {
		sql = "UPDATE assets SET xmp = ? WHERE local_id = ?",
		batchSize = 1000,
		inputFieldsInOrder = {
			inputField( "xmp", "text", true ),
			inputField( "asset_local_id", "integer", true ),
		},
		tableName = "assets",
	},
	
	setAssetDevSettings = {
		sql = "UPDATE assets SET xmpCameraRaw = ?, croppedWidth = ?, croppedHeight = ?, cameraProfileDigest = ?, lensProfileDigest = ?  WHERE local_id = ?",
		batchSize = 500,
		inputFieldsInOrder = {
			inputField( "xmpCameraRaw", "text", false ),
			inputField( "croppedWidth", "integer", false ),
			inputField( "croppedHeight", "integer", false ),
			inputField( "cameraProfileDigest", "text", false ),
			inputField( "lensProfileDigest", "text", false ),
			inputField( "asset_local_id", "integer", true ),
		},
		tableName = "assets",
	},
	
	-- In Photos, we can add xmp and assets at the same time
	addAssetWithXMP = {
		sql = "INSERT INTO assets( local_id, global_id, subType, filePath, fileWidth, fileHeight, touchTime, ratingTime, reviewTime, devSettingTime, xmp, masterImage, ozAssetId, duration, frameRate, country, state, city ) values( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )",
		batchSize = 1000,
		inputFieldsInOrder = {
			inputField( "local_id", "integer", true ),
			inputField( "global_id", "text", true ),
			inputField( "subType", "text", true ),
			inputField( "filePath", "text", true ),
			inputField( "fileWidth", "integer", true ),
			inputField( "fileHeight", "integer", true ),
			inputField( "touchTime", "text", true ),
			inputField( "ratingTime", "text", false ),
			inputField( "reviewTime", "text", false ),
			inputField( "devSettingTime", "text", false ),
			inputField( "xmp", "text", true ),
			inputField( "masterImage", "integer", false ),
			inputField( "ozAssetId", "uuid_or_empty", false ),
			inputField( "duration", "text", false ),
			inputField( "frameRate", "text", false ),
			inputField( "country", "text", false ),
			inputField( "state", "text", false ),
			inputField( "city", "text", false ),
		},
		tableName = "assets",
	},
	
}

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

local deleteOperations = {
	deleteAllVariables = {
		sql = "DELETE FROM variables",
		tableName = "variables",
	},
	
	deleteAllAssets = {
		sql = "DELETE FROM assets",
		tableName = "assets",
	},
	
	deleteAllAlbums = {
		sql = "DELETE FROM albums",
		tableName = "albums",
	},
	
	deleteAllAlbumAssets = {
		sql = "DELETE FROM album_asset",
		tableName = "album_asset",
	},
	
	deleteAllStacks = {
		sql = "DELETE FROM stacks",
		tableName = "stacks",
	},
	
	deleteAllStackAssets = {
		sql = "DELETE FROM stack_asset",
		tableName = "stack_asset",
	},
	
	deleteAllStackAlbums = {
		sql = "DELETE FROM stack_album",
		tableName = "stack_album",
	},
	
	deleteAllFaces = {
		sql = "DELETE FROM faces",
		tableName = "faces",
	},
	
	deleteAllPersons = {
		sql = "DELETE FROM persons",
		tableName = "persons",
	},
	
}

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

local readOperations = {

	getVariable = {
		sql = "SELECT value FROM variables WHERE name = ?",
		outputFieldsInOrder = { "value" },
		conditionFieldsInOrder = {
			inputField( "name", "text", true )
		}
	},
	
	countAssets = {
		sql = "SELECT COUNT( * ) FROM assets",
		outputFieldsInOrder = { "count" },
	},
	
	minAssetId = {
		sql = "SELECT min( local_id ) as min_id from assets",
		outputFieldsInOrder = { "min_id" },
	},
	
	selectAssets = {
		sql = "SELECT local_id, global_id, subType, filePath, fileWidth, fileHeight, touchTime, ratingTime, reviewTime, devSettingTime, xmpCameraRaw, croppedWidth, croppedHeight, cameraProfileDigest, lensProfileDigest, xmp, masterImage, ozAssetId, duration, frameRate, country, state, city FROM assets WHERE local_id > ? ORDER BY local_id ASC LIMIT ?",
		-- batchSize = 100, -- Not using the infrastructure to batch. LIMIT is part of the query.
		conditionFieldsInOrder = {
			inputField( "min_id", "integer", true ),
			inputField( "limit", "integer", true ),
		},
		outputFieldsInOrder = {  "local_id", "global_id", "subType", 
								"filePath", "fileWidth","fileHeight", "touchTime",
								"ratingTime", "reviewTime", "devSettingTime",
								"xmpCameraRaw", "croppedWidth", "croppedHeight", 
								"cameraProfileDigest", "lensProfileDigest", "xmp", 
								"masterImage", "ozAssetId", "duration", "frameRate",
								"country", "state", "city" },
	},
	
	selectAlbums = {
		sql = "SELECT local_id, subType, name, parent, cover, ozAlbumId FROM albums",
		-- Not batching this query since we need all rows at once
		outputFieldsInOrder = { "local_id", "subType", "name", "parent", "cover", "ozAlbumId" },
	},
	
	countAlbumAssets = {
		sql = "SELECT COUNT( * ) FROM album_asset",
		outputFieldsInOrder = { "count" },
	},
	
	selectAlbumAssets = {
		sql = "SELECT local_id, album, asset, ozAlbumAssetId, position FROM album_asset ORDER BY album, position",
		batchSize = 100,
		outputFieldsInOrder = { "local_id", "album", "asset", "ozAlbumAssetId", "position" },
	},
	
	countCopies = {
		sql = "SELECT COUNT( * ) FROM assets WHERE masterImage IS NOT NULL",
		outputFieldsInOrder = { "count" },
	},
	
	selectCopies = {
		sql = "SELECT local_id, masterImage FROM assets WHERE masterImage IS NOT NULL ORDER BY masterImage",
		outputFieldsInOrder = { "local_id", "masterImage" },
		batchSize = 100,
	},
	
	countPersons = {
		sql = "SELECT count( * ) FROM persons",
		outputFieldsInOrder = { "count" },
	},
	
	selectPersons = {
		sql = "SELECT local_id, name FROM persons",
		outputFieldsInOrder = { "local_id", "name" },
		batchSize = 100,
	},
	
	countFaces = {
		sql = "SELECT count( * ) FROM faces",
		outputFieldsInOrder = { "count" },
	},
	
	selectFaces = {
		sql = "SELECT local_id, asset, x, y, w, h, d, confidence, personId FROM faces ORDER BY asset",
		batchSize = 100,
		outputFieldsInOrder = { "local_id", "asset", "x", "y", "w", "h", "d", "confidence", "personId" },
	},
	
	countStacks = {
		sql = "SELECT count( * ) FROM stacks",
		outputFieldsInOrder = { "count" },
		
	},
	
	selectStacks = {
		sql = "SELECT stacks.local_id as stack_id, cover, asset, stack_asset.local_id as stack_asset_id FROM stacks, stack_asset WHERE stacks.local_id = stack_asset.stack ORDER BY stacks.local_id",
		batchSize = 100,
		outputFieldsInOrder = { "stack_id", "cover", "asset", "stack_asset_id" },
	},
	
	countStackAlbums = {
		sql = "SELECT count( * ) FROM stack_album",
		outputFieldsInOrder = { "count" },
	},
	
	selectStackAlbums = {
		sql = "SELECT local_id, stack, album FROM stack_album ORDER BY album",
		batchSize = 100,
		outputFieldsInOrder = { "local_id", "stack", "album" },
	},
	
	selectStackAssets = {
	
		-- Though technically, ordering by assets and then updating the
		-- asset document once might be beneficial than ordering by
		-- stack and then updating each affected asset. Here we are
		-- expecting that the chances of assets being part of multiple
		-- stacks is less than a stack containing many assets and hence
		-- we are batching based on stacks, since it is much cleaner.
		
		sql = "SELECT stack, asset FROM stack_asset ORDER BY stack",
		batchSize = 100,
		outputFieldsInOrder = { "stack", "asset" },
	},
	
	selectStackAssetsForStack = {
		sql = "SELECT local_id from stack_asset WHERE stack = ?",
		outputFieldsInOrder = { "local_id" },
		conditionFieldsInOrder = {
			inputField( 'stack', 'integer', true ),
		},
	},
}

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

local function getCreateQueries()
	return createQueries
end

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

local function getWriteQuery( queryName )
	return writeOperations[ queryName ]
end

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

local function getReadQuery( queryName )
	return readOperations[ queryName ]
end

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

local function getDeleteQuery( queryName )
	return deleteOperations[ queryName ]
end

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

local function getWriteOperations()
	return writeOperations
end

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

local function getReadOperations()
	return readOperations
end

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

local function getDeleteOperations()
	return deleteOperations
end

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

return {
	getCreateQueries = getCreateQueries,
	getWriteQuery = getWriteQuery,
	getReadQuery = getReadQuery,
	getDeleteQuery = getDeleteQuery,
	getWriteOperations = getWriteOperations,
	getReadOperations = getReadOperations,
	getDeleteOperations = getDeleteOperations,
}
