/*
    ***** BEGIN LICENSE BLOCK *****
    
    Copyright © 2009 Center for History and New Media
                     George Mason University, Fairfax, Virginia, USA
                     http://zotero.org
    
    This file is part of Zotero.
    
    Zotero is free software: you can redistribute it and/or modify
    it under the terms of the GNU Affero General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.
    
    Zotero is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU Affero General Public License for more details.
    
    You should have received a copy of the GNU Affero General Public License
    along with Zotero.  If not, see <http://www.gnu.org/licenses/>.
    
    ***** END LICENSE BLOCK *****
*/


/*
 * Primary interface for accessing Zotero items
 */
Zotero.Items = function () {
	this.constructor = null;
	
	this._ZDO_object = 'item';
	
	// This needs to wait until all Zotero components are loaded to initialize,
	// but otherwise it can be just a simple property
	Zotero.defineProperty(this, "_primaryDataSQLParts", {
		get: function () {
			var itemTypeAttachment = Zotero.ItemTypes.getID('attachment');
			var itemTypeNote = Zotero.ItemTypes.getID('note');
			var itemTypeAnnotation = Zotero.ItemTypes.getID('annotation');
			
			return {
				itemID: "O.itemID",
				itemTypeID: "O.itemTypeID",
				dateAdded: "O.dateAdded",
				dateModified: "O.dateModified",
				libraryID: "O.libraryID",
				key: "O.key",
				version: "O.version",
				synced: "O.synced",
				
				createdByUserID: "createdByUserID",
				lastModifiedByUserID: "lastModifiedByUserID",
				
				firstCreator: _getFirstCreatorSQL(),
				sortCreator: _getSortCreatorSQL(),
				
				deleted: "DI.itemID IS NOT NULL AS deleted",
				inPublications: "PI.itemID IS NOT NULL AS inPublications",
				
				parentID: `(CASE O.itemTypeID `
					+ `WHEN ${itemTypeAttachment} THEN IAP.itemID `
					+ `WHEN ${itemTypeNote} THEN INoP.itemID `
					+ `WHEN ${itemTypeAnnotation} THEN IAnP.itemID `
					+ `END) AS parentID`,
				parentKey: `(CASE O.itemTypeID `
					+ `WHEN ${itemTypeAttachment} THEN IAP.key `
					+ `WHEN ${itemTypeNote} THEN INoP.key `
					+ `WHEN ${itemTypeAnnotation} THEN IAnP.key `
					+ `END) AS parentKey`,
				
				attachmentCharset: "CS.charset AS attachmentCharset",
				attachmentLinkMode: "IA.linkMode AS attachmentLinkMode",
				attachmentContentType: "IA.contentType AS attachmentContentType",
				attachmentPath: "IA.path AS attachmentPath",
				attachmentSyncState: "IA.syncState AS attachmentSyncState",
				attachmentSyncedModificationTime: "IA.storageModTime AS attachmentSyncedModificationTime",
				attachmentSyncedHash: "IA.storageHash AS attachmentSyncedHash",
				attachmentLastProcessedModificationTime: "IA.lastProcessedModificationTime AS attachmentLastProcessedModificationTime",
				attachmentLastRead: "IA.lastRead AS attachmentLastRead",
			};
		}
	}, {lazy: true});
	
	
	this._primaryDataSQLFrom = "FROM items O "
		+ "LEFT JOIN itemAttachments IA USING (itemID) "
		+ "LEFT JOIN items IAP ON (IA.parentItemID=IAP.itemID) "
		+ "LEFT JOIN itemNotes INo ON (O.itemID=INo.itemID) "
		+ "LEFT JOIN items INoP ON (INo.parentItemID=INoP.itemID) "
		+ "LEFT JOIN itemAnnotations IAn ON (O.itemID=IAn.itemID) "
		+ "LEFT JOIN items IAnP ON (IAn.parentItemID=IAnP.itemID) "
		+ "LEFT JOIN deletedItems DI ON (O.itemID=DI.itemID) "
		+ "LEFT JOIN publicationsItems PI ON (O.itemID=PI.itemID) "
		+ "LEFT JOIN charsets CS ON (IA.charsetID=CS.charsetID)"
		+ "LEFT JOIN groupItems GI ON (O.itemID=GI.itemID)";
	
	this._relationsTable = "itemRelations";
	
	
	/**
	 * @param {Integer} libraryID
	 * @return {Promise<Boolean>} - True if library has items in trash, false otherwise
	 */
	this.hasDeleted = async function (libraryID) {
		var sql = "SELECT COUNT(*) > 0 FROM items JOIN deletedItems USING (itemID) WHERE libraryID=?";
		return !!((await Zotero.DB.valueQueryAsync(sql, [libraryID])));
	};
	
	
	/**
	 * Returns all items in a given library
	 *
	 * @param  {Integer}  libraryID
	 * @param  {Boolean}  [onlyTopLevel=false]   If true, don't include child items
	 * @param  {Boolean}  [includeDeleted=false] If true, include deleted items
	 * @param  {Boolean}  [asIDs=false] 		 If true, resolves only with IDs
	 * @return {Promise<Array<Zotero.Item|Integer>>}
	 */
	this.getAll = async function (libraryID, onlyTopLevel, includeDeleted, asIDs=false) {
		var sql = 'SELECT A.itemID FROM items A';
		if (onlyTopLevel) {
			sql += ' LEFT JOIN itemNotes B USING (itemID) '
			+ 'LEFT JOIN itemAttachments C ON (C.itemID=A.itemID) '
			+ 'WHERE B.parentItemID IS NULL AND C.parentItemID IS NULL';
		}
		else {
			sql += " WHERE 1";
		}
		if (!includeDeleted) {
			sql += " AND A.itemID NOT IN (SELECT itemID FROM deletedItems)";
		}
		sql += " AND libraryID=?";
		var ids = await Zotero.DB.columnQueryAsync(sql, libraryID);
		if (asIDs) {
			return ids;
		}
		return this.getAsync(ids);
	};
	
	this._lastReadCutoffs = new Map(); // libraryID -> cutoff timestamp
	
	/**
	 * Get items with recently read attachments in a library.
	 *
	 * "Recently read" means read within two weeks of the most recently read
	 * attachment in the library (cutoff fixed within each session).
	 *
	 * @param {Integer} libraryID
	 * @return {Promise<Integer[]>} Item IDs (parent items, or standalone attachments)
	 */
	this.getLastRead = async function (libraryID) {
		let twoWeeksInSeconds = 14 * 24 * 60 * 60;

		// Freeze the cutoff on first load so that reading something new
		// doesn't cause old items to vanish mid-session
		let cutoff;
		if (this._lastReadCutoffs.has(libraryID)) {
			cutoff = this._lastReadCutoffs.get(libraryID);
		}
		else {
			let maxSQL = "SELECT MAX(IA.lastRead) FROM itemAttachments IA "
				+ "JOIN items I ON (I.itemID = IA.itemID) "
				+ "WHERE I.libraryID = ? "
				+ "AND IA.lastRead IS NOT NULL "
				+ "AND IA.itemID NOT IN (SELECT itemID FROM deletedItems) "
				+ "AND COALESCE(IA.parentItemID, IA.itemID) NOT IN (SELECT itemID FROM deletedItems)";
			let maxLastRead = await Zotero.DB.valueQueryAsync(maxSQL, [libraryID]);
			if (maxLastRead) {
				cutoff = maxLastRead - twoWeeksInSeconds;
				this._lastReadCutoffs.set(libraryID, cutoff);
			}
			else {
				// No items with lastRead.
				// Don't persist this - we want to calculate a new cutoff if items come in later.
				cutoff = 0;
			}
		}

		let sql = "SELECT DISTINCT COALESCE(IA.parentItemID, IA.itemID) "
			+ "FROM itemAttachments IA "
			+ "JOIN items I ON (I.itemID = COALESCE(IA.parentItemID, IA.itemID)) "
			+ "WHERE IA.lastRead >= ? "
			+ "AND I.libraryID = ? "
			+ "AND IA.itemID NOT IN (SELECT itemID FROM deletedItems) "
			+ "AND COALESCE(IA.parentItemID, IA.itemID) NOT IN (SELECT itemID FROM deletedItems)";
		return Zotero.DB.columnQueryAsync(sql, [cutoff, libraryID]);
	};


	//
	// Bulk data loading functions
	//
	// These are called by Zotero.DataObjects.prototype._loadDataType().
	//
	this._loadItemData = async function (libraryID, ids, idSQL) {
		var missingItems = {};
		var itemFieldsCached = {};
		
		var sql = "SELECT itemID, fieldID, value FROM items "
			+ "JOIN itemData USING (itemID) "
			+ "JOIN itemDataValues USING (valueID) WHERE libraryID=? AND itemTypeID!=?" + idSQL;
		var params = [libraryID, Zotero.ItemTypes.getID('note')];
		await Zotero.DB.queryAsync(
			sql,
			params,
			{
				noCache: true,
				onRow: function (row) {
					let itemID = row.getResultByIndex(0);
					let fieldID = row.getResultByIndex(1);
					let value = row.getResultByIndex(2);
					
					//Zotero.debug('Setting field ' + fieldID + ' for item ' + itemID);
					if (this._objectCache[itemID]) {
						if (value === null) {
							value = false;
						}
						this._objectCache[itemID].setField(fieldID, value, true);
					}
					else {
						if (!missingItems[itemID]) {
							missingItems[itemID] = true;
							Zotero.logError("itemData row references nonexistent item " + itemID);
						}
					}
					if (!itemFieldsCached[itemID]) {
						itemFieldsCached[itemID] = {};
					}
					itemFieldsCached[itemID][fieldID] = true;
				}.bind(this)
			}
		);
		
		var sql = "SELECT itemID FROM items WHERE libraryID=?" + idSQL;
		var params = [libraryID];
		var allItemIDs = [];
		await Zotero.DB.queryAsync(
			sql,
			params,
			{
				noCache: true,
				onRow: function (row) {
					let itemID = row.getResultByIndex(0);
					let item = this._objectCache[itemID];
					
					// Set nonexistent fields in the cache list to false (instead of null)
					let fieldIDs = Zotero.ItemFields.getItemTypeFields(item.itemTypeID);
					for (let j=0; j<fieldIDs.length; j++) {
						let fieldID = fieldIDs[j];
						if (!itemFieldsCached[itemID] || !itemFieldsCached[itemID][fieldID]) {
							//Zotero.debug('Setting field ' + fieldID + ' to false for item ' + itemID);
							item.setField(fieldID, false, true);
						}
					}
					
					allItemIDs.push(itemID);
				}.bind(this)
			}
		);
		
		
		var titleFieldID = Zotero.ItemFields.getID('title');
		
		// Note titles
		var sql = "SELECT itemID, title FROM items JOIN itemNotes USING (itemID) "
			+ "WHERE libraryID=? AND itemID NOT IN (SELECT itemID FROM itemAttachments)" + idSQL;
		var params = [libraryID];
		
		await Zotero.DB.queryAsync(
			sql,
			params,
			{
				noCache: true,
				onRow: function (row) {
					let itemID = row.getResultByIndex(0);
					let title = row.getResultByIndex(1);
					
					//Zotero.debug('Setting title for note ' + row.itemID);
					if (this._objectCache[itemID]) {
						this._objectCache[itemID].setField(titleFieldID, title, true);
					}
					else {
						if (!missingItems[itemID]) {
							missingItems[itemID] = true;
							Zotero.logError("itemData row references nonexistent item " + itemID);
						}
					}
				}.bind(this)
			}
		);
		
		for (let i=0; i<allItemIDs.length; i++) {
			let itemID = allItemIDs[i];
			let item = this._objectCache[itemID];
			
			// Mark as loaded
			item._loaded.itemData = true;
			item._clearChanged('itemData');
			
			// Display titles
			try {
				item.updateDisplayTitle()
			}
			catch (e) {
				// A few item types need creators or tags to be loaded. Annotations need to be loaded
				// to be displayed in itemTree.
				// Instead of making updateDisplayTitle() async and loading conditionally, just catch the error
				// and load on demand
				if (e instanceof Zotero.Exception.UnloadedDataException) {
					Zotero.debug(`Unloaded data for item ${item.libraryKey} updating display title`);
					if (item.isRegularItem()) {
						await item.loadDataType('creators');
						await item.loadDataType('tags');
					}
					else if (item.isAnnotation()) {
						await item.loadDataType('annotation');
					}
					try {
						item.updateDisplayTitle();
					}
					catch (e2) {
						Zotero.logError(e2);
					}
				}
				else {
					throw e;
				}
			}
		}
	};
	
	
	this._loadCreators = async function (libraryID, ids, idSQL) {
		var sql = 'SELECT itemID, creatorID, creatorTypeID, orderIndex '
			+ 'FROM items LEFT JOIN itemCreators USING (itemID) '
			+ 'WHERE libraryID=?' + idSQL + " ORDER BY itemID, orderIndex";
		var params = [libraryID];
		var rows = await Zotero.DB.queryAsync(sql, params, { noCache: true });
		
		// Mark creator indexes above the number of creators as changed,
		// so that they're cleared if the item is saved
		var fixIncorrectIndexes = function (item, numCreators, maxOrderIndex) {
			Zotero.debug("Fixing incorrect creator indexes for item " + item.libraryKey
				+ " (" + numCreators + ", " + maxOrderIndex + ")", 2);
			var i = numCreators;
			if (!item._changed.creators) {
				item._changed.creators = {};
			}
			while (i <= maxOrderIndex) {
				item._changed.creators[i] = true;
				i++;
			}
		};
		
		var lastItemID;
		var item;
		var index = 0;
		var maxOrderIndex = -1;
		for (let i = 0; i < rows.length; i++) {
			let row = rows[i];
			let itemID = row.itemID;
			
			if (itemID != lastItemID) {
				if (!this._objectCache[itemID]) {
					throw new Error("Item " + itemID + " not loaded");
				}
				item = this._objectCache[itemID];
				
				item._creators = [];
				item._creatorIDs = [];
				item._loaded.creators = true;
				item._clearChanged('creators');
				
				if (!row.creatorID) {
					lastItemID = row.itemID;
					continue;
				}
				
				if (index <= maxOrderIndex) {
					fixIncorrectIndexes(item, index, maxOrderIndex);
				}
				
				index = 0;
				maxOrderIndex = -1;
			}
			
			lastItemID = row.itemID;
			
			if (row.orderIndex > maxOrderIndex) {
				maxOrderIndex = row.orderIndex;
			}
			
			let creatorData = Zotero.Creators.get(row.creatorID);
			creatorData.creatorTypeID = row.creatorTypeID;
			item._creators[index] = creatorData;
			item._creatorIDs[index] = row.creatorID;
			index++;
		}
		
		if (index <= maxOrderIndex) {
			fixIncorrectIndexes(item, index, maxOrderIndex);
		}
	};
	
	
	this._loadNotes = async function (libraryID, ids, idSQL) {
		var notesToUpdate = [];
		
		var sql = "SELECT itemID, note FROM items "
			+ "JOIN itemNotes USING (itemID) "
			+ "WHERE libraryID=?" + idSQL;
		var params = [libraryID];
		await Zotero.DB.queryAsync(
			sql,
			params,
			{
				noCache: true,
				onRow: function (row) {
					let itemID = row.getResultByIndex(0);
					let item = this._objectCache[itemID];
					if (!item) {
						throw new Error("Item " + itemID + " not found");
					}
					let note = row.getResultByIndex(1);
					
					// Convert non-HTML notes on-the-fly
					if (note !== "") {
						if (typeof note == 'number') {
							note = '' + note;
						}
						if (typeof note == 'string') {
							if (!note.substr(0, 36).match(/^<div class="zotero-note znv[0-9]+">/)) {
								note = Zotero.Utilities.htmlSpecialChars(note);
								note = Zotero.Notes.notePrefix + '<p>'
									+ note.replace(/\n/g, '</p><p>')
									.replace(/\t/g, '&nbsp;&nbsp;&nbsp;&nbsp;')
									.replace(/  /g, '&nbsp;&nbsp;')
									+ '</p>' + Zotero.Notes.noteSuffix;
								note = note.replace(/<p>\s*<\/p>/g, '<p>&nbsp;</p>');
								notesToUpdate.push([item.id, note]);
							}
							
							// Don't include <div> wrapper when returning value
							let startLen = note.substr(0, 36).match(/^<div class="zotero-note znv[0-9]+">/)[0].length;
							let endLen = 6; // "</div>".length
							note = note.substr(startLen, note.length - startLen - endLen);
						}
						// Clear null notes
						else {
							note = '';
							notesToUpdate.push([item.id, '']);
						}
					}
					
					item._noteText = note ? note : '';
					item._loaded.note = true;
					item._clearChanged('note');
				}.bind(this)
			}
		);
		
		if (notesToUpdate.length) {
			await Zotero.DB.executeTransaction(async function () {
				for (let i = 0; i < notesToUpdate.length; i++) {
					let row = notesToUpdate[i];
					let sql = "UPDATE itemNotes SET note=? WHERE itemID=?";
					await Zotero.DB.queryAsync(sql, [row[1], row[0]]);
				}
			}.bind(this));
		}
		
		// Mark notes and attachments without notes as loaded
		sql = "SELECT itemID FROM items WHERE libraryID=?" + idSQL
			+ " AND itemTypeID IN (?, ?) AND itemID NOT IN (SELECT itemID FROM itemNotes)";
		params = [libraryID, Zotero.ItemTypes.getID('note'), Zotero.ItemTypes.getID('attachment')];
		await Zotero.DB.queryAsync(
			sql,
			params,
			{
				noCache: true,
				onRow: function (row) {
					let itemID = row.getResultByIndex(0);
					let item = this._objectCache[itemID];
					if (!item) {
						throw new Error("Item " + itemID + " not loaded");
					}
					
					item._noteText = '';
					item._loaded.note = true;
					item._clearChanged('note');
				}.bind(this)
			}
		);
	};
	
	
	this._loadAnnotations = async function (libraryID, ids, idSQL) {
		var sql = "SELECT itemID, IA.parentItemID, IA.type, IA.authorName, IA.text, IA.comment, "
			+ "IA.color, IA.sortIndex, IA.isExternal "
			+ "FROM items JOIN itemAnnotations IA USING (itemID) "
			+ "WHERE libraryID=?" + idSQL;
		var params = [libraryID];
		
		// TEMP: Fix faulty upgrade from early 6.0 beta
		// https://github.com/zotero/zotero/issues/3013
		try {
			await Zotero.DB.queryAsync(
				sql,
				params,
				{
					noCache: true,
					onRow: function (row) {
						let itemID = row.getResultByIndex(0);
						
						let item = this._objectCache[itemID];
						if (!item) {
							throw new Error("Item " + itemID + " not found");
						}
						
						item._parentItemID = row.getResultByIndex(1);
						var typeID = row.getResultByIndex(2);
						var type;
						switch (typeID) {
							case Zotero.Annotations.ANNOTATION_TYPE_HIGHLIGHT:
								type = 'highlight';
								break;

							case Zotero.Annotations.ANNOTATION_TYPE_UNDERLINE:
								type = 'underline';
								break;
							
							case Zotero.Annotations.ANNOTATION_TYPE_NOTE:
								type = 'note';
								break;

							case Zotero.Annotations.ANNOTATION_TYPE_TEXT:
								type = 'text';
								break;
							
							case Zotero.Annotations.ANNOTATION_TYPE_IMAGE:
								type = 'image';
								break;
							
							case Zotero.Annotations.ANNOTATION_TYPE_INK:
								type = 'ink';
								break;
							
							default:
								throw new Error(`Unknown annotation type id ${typeID}`);
						}
						item._annotationType = type;
						item._annotationAuthorName = row.getResultByIndex(3);
						item._annotationText = row.getResultByIndex(4);
						item._annotationComment = row.getResultByIndex(5);
						item._annotationColor = row.getResultByIndex(6);
						item._annotationSortIndex = row.getResultByIndex(7);
						item._annotationIsExternal = !!row.getResultByIndex(8);
						
						item._loaded.annotation = true;
						item._clearChanged('annotation');
					}.bind(this)
				}
			);
		}
		catch (e) {
			if (e.message.includes('no such column: IA.authorName')
					&& (await Zotero.DB.valueQueryAsync("SELECT COUNT(*) FROM version WHERE schema='userdata' AND version IN (120, 121, 122)"))) {
				await Zotero.DB.queryAsync("UPDATE version SET version=119 WHERE schema='userdata'");
				Zotero.crash();
			}
			throw e;
		}
	};
	
	
	this._loadAnnotationsDeferred = async function (libraryID, ids, idSQL) {
		var sql = "SELECT itemID, IA.position, IA.pageLabel FROM items "
			+ "JOIN itemAnnotations IA USING (itemID) "
			+ "WHERE libraryID=?" + idSQL;
		var params = [libraryID];
		await Zotero.DB.queryAsync(
			sql,
			params,
			{
				noCache: true,
				onRow: function (row) {
					let itemID = row.getResultByIndex(0);
					
					let item = this._objectCache[itemID];
					if (!item) {
						throw new Error("Item " + itemID + " not found");
					}
					
					item._annotationPosition = row.getResultByIndex(1);
					item._annotationPageLabel = row.getResultByIndex(2);
					
					item._loaded.annotationDeferred = true;
					item._clearChanged('annotationDeferred');
				}.bind(this)
			}
		);
	};
	
	
	this._loadChildItems = async function (libraryID, ids, idSQL) {
		var params = [libraryID];
		var rows = [];
		var onRow = function (row, setFunc) {
			var itemID = row.getResultByIndex(0);
			
			// If we've finished a set of rows for an item, process them
			if (lastItemID && itemID !== lastItemID) {
				setFunc(lastItemID, rows);
				rows = [];
			}
			
			lastItemID = itemID;
			rows.push({
				itemID: row.getResultByIndex(1),
				title: row.getResultByIndex(2),
				trashed: row.getResultByIndex(3)
			});
		};
		
		//
		// Attachments
		//
		var titleFieldID = Zotero.ItemFields.getID('title');
		var sql = "SELECT parentItemID, A.itemID, value AS title, "
			+ "CASE WHEN DI.itemID IS NULL THEN 0 ELSE 1 END AS trashed "
			+ "FROM itemAttachments A "
			+ "JOIN items I ON (A.parentItemID=I.itemID) "
			+ `LEFT JOIN itemData ID ON (fieldID=${titleFieldID} AND A.itemID=ID.itemID) `
			+ "LEFT JOIN itemDataValues IDV USING (valueID) "
			+ "LEFT JOIN deletedItems DI USING (itemID) "
			+ "WHERE libraryID=?"
			+ (ids.length ? " AND parentItemID IN (" + ids.map(id => parseInt(id)).join(", ") + ")" : "")
			+ " ORDER BY parentItemID";
		// Since we do the sort here and cache these results, a restart will be required
		// if this pref (off by default) is turned on, but that's OK
		if (Zotero.Prefs.get('sortAttachmentsChronologically')) {
			sql +=  ", dateAdded";
		}
		var setAttachmentItem = function (itemID, rows) {
			var item = this._objectCache[itemID];
			if (!item) {
				throw new Error("Item " + itemID + " not loaded");
			}
			
			item._attachments = {
				rows,
				chronologicalWithTrashed: null,
				chronologicalWithoutTrashed: null,
				alphabeticalWithTrashed: null,
				alphabeticalWithoutTrashed: null
			};
		}.bind(this);
		var lastItemID = null;
		await Zotero.DB.queryAsync(
			sql,
			params,
			{
				noCache: true,
				onRow: function (row) {
					onRow(row, setAttachmentItem);
				}
			}
		);
		// Process unprocessed rows
		if (lastItemID) {
			setAttachmentItem(lastItemID, rows);
		}
		// Otherwise clear existing entries for passed items
		else if (ids.length) {
			ids.forEach(id => setAttachmentItem(id, []));
		}
		
		//
		// Notes
		//
		sql = "SELECT parentItemID, N.itemID, title, "
			+ "CASE WHEN DI.itemID IS NULL THEN 0 ELSE 1 END AS trashed "
			+ "FROM itemNotes N "
			+ "JOIN items I ON (N.parentItemID=I.itemID) "
			+ "LEFT JOIN deletedItems DI USING (itemID) "
			+ "WHERE libraryID=?"
			+ (ids.length ? " AND parentItemID IN (" + ids.map(id => parseInt(id)).join(", ") + ")" : "")
			+ " ORDER BY parentItemID";
		if (Zotero.Prefs.get('sortNotesChronologically')) {
			sql +=  ", dateAdded";
		}
		var setNoteItem = function (itemID, rows) {
			var item = this._objectCache[itemID];
			if (!item) {
				throw new Error("Item " + itemID + " not loaded");
			}
			
			item._notes = {
				rows,
				rowsEmbedded: null,
				chronologicalWithTrashed: null,
				chronologicalWithoutTrashed: null,
				alphabeticalWithTrashed: null,
				alphabeticalWithoutTrashed: null,
				numWithTrashed: null,
				numWithoutTrashed: null,
				numWithTrashedWithEmbedded: null,
				numWithoutTrashedWithoutEmbedded: null
			};
		}.bind(this);
		lastItemID = null;
		rows = [];
		await Zotero.DB.queryAsync(
			sql,
			params,
			{
				noCache: true,
				onRow: function (row) {
					onRow(row, setNoteItem);
				}
			}
		);
		// Process unprocessed rows
		if (lastItemID) {
			setNoteItem(lastItemID, rows);
		}
		// Otherwise clear existing entries for passed items
		else if (ids.length) {
			ids.forEach(id => setNoteItem(id, []));
		}
		
		//
		// Annotations
		//
		sql = "SELECT parentItemID, IAn.itemID, "
			+ "text || ' - ' || comment AS title, " // TODO: Make better
			+ "CASE WHEN DI.itemID IS NULL THEN 0 ELSE 1 END AS trashed "
			+ "FROM itemAnnotations IAn "
			+ "JOIN items I ON (IAn.parentItemID=I.itemID) "
			+ "LEFT JOIN deletedItems DI USING (itemID) "
			+ "WHERE libraryID=?"
			+ (ids.length ? " AND parentItemID IN (" + ids.map(id => parseInt(id)).join(", ") + ")" : "")
			+ " ORDER BY parentItemID, sortIndex";
		var setAnnotationItem = function (itemID, rows) {
			var item = this._objectCache[itemID];
			if (!item) {
				throw new Error("Item " + itemID + " not loaded");
			}
			rows.sort((a, b) => a.sortIndex - b.sortIndex);
			item._annotations = {
				rows,
				withTrashed: null,
				withoutTrashed: null
			};
		}.bind(this);
		lastItemID = null;
		rows = [];
		await Zotero.DB.queryAsync(
			sql,
			params,
			{
				noCache: true,
				onRow: function (row) {
					onRow(row, setAnnotationItem);
				}
			}
		);
		// Process unprocessed rows
		if (lastItemID) {
			setAnnotationItem(lastItemID, rows);
		}
		// Otherwise clear existing entries for passed items
		else if (ids.length) {
			ids.forEach(id => setAnnotationItem(id, []));
		}
		
		// Mark either all passed items or all items as having child items loaded
		sql = "SELECT itemID FROM items I WHERE libraryID=?";
		if (idSQL) {
			sql += idSQL;
		}
		await Zotero.DB.queryAsync(
			sql,
			params,
			{
				noCache: true,
				onRow: function (row) {
					var itemID = row.getResultByIndex(0);
					var item = this._objectCache[itemID];
					if (!item) {
						throw new Error("Item " + itemID + " not loaded");
					}
					item._loaded.childItems = true;
					item._clearChanged('childItems');
				}.bind(this)
			}
		);
	};
	
	
	this._loadTags = async function (libraryID, ids, idSQL) {
		var sql = "SELECT itemID, name, type FROM items "
			+ "LEFT JOIN itemTags USING (itemID) "
			+ "LEFT JOIN tags USING (tagID) WHERE libraryID=?" + idSQL;
		var params = [libraryID];
		
		var lastItemID;
		var rows = [];
		var setRows = function (itemID, rows) {
			var item = this._objectCache[itemID];
			if (!item) {
				throw new Error("Item " + itemID + " not found");
			}
			
			item._tags = [];
			for (let i = 0; i < rows.length; i++) {
				let row = rows[i];
				item._tags.push(Zotero.Tags.cleanData(row));
			}
			
			item._loaded.tags = true;
		}.bind(this);
		
		await Zotero.DB.queryAsync(
			sql,
			params,
			{
				noCache: true,
				onRow: function (row) {
					let itemID = row.getResultByIndex(0);
					
					if (lastItemID && itemID !== lastItemID) {
						setRows(lastItemID, rows);
						rows = [];
					}
					
					lastItemID = itemID;
					
					// Item has no tags
					let tag = row.getResultByIndex(1);
					if (tag === null) {
						return;
					}
					
					rows.push({
						tag: tag,
						type: row.getResultByIndex(2)
					});
				}.bind(this)
			}
		);
		if (lastItemID) {
			setRows(lastItemID, rows);
		}
	};
	
	
	this._loadCollections = async function (libraryID, ids, idSQL) {
		var sql = "SELECT itemID, collectionID FROM items "
			+ "LEFT JOIN collectionItems USING (itemID) "
			+ "WHERE libraryID=?" + idSQL;
		var params = [libraryID];
		
		var lastItemID;
		var rows = [];
		var setRows = function (itemID, rows) {
			var item = this._objectCache[itemID];
			if (!item) {
				throw new Error("Item " + itemID + " not found");
			}
			
			item._collections = rows;
			item._loaded.collections = true;
			item._clearChanged('collections');
		}.bind(this);
		
		await Zotero.DB.queryAsync(
			sql,
			params,
			{
				noCache: true,
				onRow: function (row) {
					let itemID = row.getResultByIndex(0);
					
					if (lastItemID && itemID !== lastItemID) {
						setRows(lastItemID, rows);
						rows = [];
					}
					
					lastItemID = itemID;
					let collectionID = row.getResultByIndex(1);
					// No collections
					if (collectionID === null) {
						return;
					}
					rows.push(collectionID);
				}.bind(this)
			}
		);
		if (lastItemID) {
			setRows(lastItemID, rows);
		}
	};
	
	
	/**
	 * Copy child items from one item to another (e.g., in another library)
	 *
	 * Requires a transaction
	 */
	this.copyChildItems = async function (fromItem, toItem) {
		Zotero.DB.requireTransaction();
		
		var fromGroup = fromItem.library.isGroup;
		
		// Annotations on files
		if (fromItem.isFileAttachment()) {
			let annotations = fromItem.getAnnotations();
			for (let annotation of annotations) {
				// Don't copy embedded PDF annotations
				if (annotation.annotationIsExternal) {
					continue;
				}
				let newAnnotation = annotation.clone(toItem.libraryID);
				newAnnotation.parentItemID = toItem.id;
				// If there's no explicit author and we're copying an annotation created by another
				// user from a group, set the author to the creating user
				if (fromGroup
						&& !annotation.annotationAuthorName
						&& annotation.createdByUserID != Zotero.Users.getCurrentUserID()) {
					newAnnotation.annotationAuthorName =
						Zotero.Users.getName(annotation.createdByUserID);
				}
				await newAnnotation.save();
			}
		}
		
		// TODO: Other things as necessary
	};
	
	
	/**
	 * Move child items from one item to another
	 *
	 * Requires a transaction
	 *
	 * @param {Zotero.Item} fromItem
	 * @param {Zotero.Item} toItem
	 * @param {Object} options
	 * @param {Boolean} [includeTrashed=false]
	 * @param {Boolean} [skipEditCheck=false]
	 * @return {Promise}
	 */
	this.moveChildItems = async function (fromItem, toItem, { includeTrashed = false, skipEditCheck = false } = {}) {
		Zotero.DB.requireTransaction();
		
		// Annotations on files
		if (fromItem.isFileAttachment()) {
			let annotations = fromItem.getAnnotations(includeTrashed);
			for (let annotation of annotations) {
				if (annotation.annotationIsExternal) {
					continue;
				}
				annotation.parentItemID = toItem.id;
				await annotation.save({ skipEditCheck });
			}
		}
		
		// TODO: Other things as necessary
	};
	
	
	this.merge = function (item, otherItems) {
		Zotero.debug("Zotero.Items.merge() is deprecated -- use mergeItems.mjs");
		
		let { mergeItems } = ChromeUtils.importESModule("chrome://zotero/content/mergeItems.mjs");
		return mergeItems(item, otherItems);
	};


	this.trash = async function (ids) {
		Zotero.DB.requireTransaction();
		
		var libraryIDs = new Set();
		ids = Zotero.flattenArguments(ids);
		var items = [];
		for (let id of ids) {
			let item = this.get(id);
			if (!item) {
				Zotero.debug('Item ' + id + ' does not exist in Items.trash()!', 1);
				Zotero.Notifier.queue('trash', 'item', id);
				continue;
			}
			
			if (!item.isEditable()) {
				throw new Error(item._ObjectType + " " + item.libraryKey + " is not editable");
			}
			
			if (!Zotero.Libraries.get(item.libraryID).hasTrash) {
				throw new Error(Zotero.Libraries.getName(item.libraryID) + " does not have a trash");
			}
			
			items.push(item);
			libraryIDs.add(item.libraryID);
		}
		
		var parentItemIDs = new Set();
		items.forEach(item => {
			item.setDeleted(true);
			item.synced = false;
			if (item.parentItemID) {
				parentItemIDs.add(item.parentItemID);
			}
		});
		let hasGroupLibrary = [...libraryIDs].some(
			id => Zotero.Libraries.get(id).libraryType === 'group'
		);
		let dateModified = Zotero.DB.transactionDateTime;
		await Zotero.Utilities.Internal.forEachChunkAsync(ids, 250, async function (chunk) {
			let idStr = chunk.map(id => parseInt(id)).join(", ");
			await Zotero.DB.queryAsync(
				"UPDATE items SET synced=0, clientDateModified=CURRENT_TIMESTAMP, "
					+ `dateModified=? WHERE itemID IN (${idStr})`,
				dateModified
			);
			await Zotero.DB.queryAsync(
				"INSERT OR IGNORE INTO deletedItems (itemID) VALUES "
					+ chunk.map(id => "(" + id + ")").join(", ")
			);
			// Update lastModifiedByUserID for group items
			if (hasGroupLibrary) {
				let currentUserID = Zotero.Users.getCurrentUserID();
				if (currentUserID) {
					await Zotero.DB.queryAsync(
						"UPDATE groupItems SET lastModifiedByUserID=? "
							+ `WHERE itemID IN (${idStr})`,
						currentUserID
					);
				}
			}
		}.bind(this));
		
		// Keep in sync with Zotero.Item::saveData()
		for (let parentItemID of parentItemIDs) {
			let parentItem = await Zotero.Items.getAsync(parentItemID);
			await parentItem.reload(['primaryData', 'childItems'], true);
		}
		Zotero.Notifier.queue('modify', 'item', ids);
		Zotero.Notifier.queue('trash', 'item', ids);
		Array.from(libraryIDs).forEach(libraryID => {
			Zotero.Notifier.queue('refresh', 'trash', libraryID);
		});
	};
	
	
	this.trashTx = function (ids) {
		return Zotero.DB.executeTransaction(async function () {
			return this.trash(ids);
		}.bind(this));
	}
	
	
	/**
	 * @param {Integer} libraryID - Library to delete from
	 * @param {Object} [options]
	 * @param {Function} [options.onProgress] - fn(progress, progressMax)
	 * @param {Integer} [options.days] - Only delete items deleted more than this many days ago
	 * @param {Integer} [options.limit] - Number of items to delete
	 */
	this.emptyTrash = async function (libraryID, options = {}) {
		if (arguments.length > 2 || typeof arguments[1] == 'number') {
			Zotero.warn("Zotero.Items.emptyTrash() has changed -- update your code");
			options.days = arguments[1];
			options.limit = arguments[2];
		}
		
		if (!libraryID) {
			throw new Error("Library ID not provided");
		}
		
		var t = new Date();
		
		var deleted = await this.getDeleted(libraryID, false, options.days);
		
		if (options.limit) {
			deleted = deleted.slice(0, options.limit);
		}
		
		var processed = 0;
		if (deleted.length) {
			let toDelete = {
				top: [],
				child: []
			};
			deleted.forEach((item) => {
				item.isTopLevelItem() ? toDelete.top.push(item.id) : toDelete.child.push(item.id)
			});
			
			// Show progress meter during deletions
			let eraseOptions = options.onProgress
				? {
					onProgress: function (progress, progressMax) {
						options.onProgress(processed + progress, deleted.length);
					}
				}
				: undefined;
			for (let x of ['top', 'child']) {
				await Zotero.Utilities.Internal.forEachChunkAsync(
					toDelete[x],
					1000,
					async function (chunk) {
						await this.erase(chunk, eraseOptions);
						processed += chunk.length;
					}.bind(this)
				);
			}
			Zotero.debug("Emptied " + deleted.length + " item(s) from trash in " + (new Date() - t) + " ms");
			Zotero.Notifier.trigger('refresh', 'trash', libraryID);
		}
		
		return deleted.length;
	};
	
	
	/**
	 * Start idle observer to delete trashed items older than a certain number of days
	 */
	this._emptyTrashIdleObserver = null;
	this._emptyTrashTimeoutID = null;
	this.startEmptyTrashTimer = function () {
		this._emptyTrashIdleObserver = {
			observe: (subject, topic, data) => {
				if (topic == 'idle' || topic == 'timer-callback') {
					var days = Zotero.Prefs.get('trashAutoEmptyDays');
					if (!days) {
						return;
					}
					
					// TODO: empty group trashes if permissions
					
					// Delete a few items a time
					//
					// TODO: increase number after dealing with slow
					// tag.getLinkedItems() call during deletes
					let num = 50;
					this.emptyTrash(
						Zotero.Libraries.userLibraryID,
						{
							days,
							limit: num
						}
					)
					.then((deleted) => {
						if (!deleted) {
							this._emptyTrashTimeoutID = null;
							return;
						}
						
						// Set a timer to do more every few seconds
						this._emptyTrashTimeoutID = setTimeout(() => {
							this._emptyTrashIdleObserver.observe(null, 'timer-callback', null);
						}, 2500);
					});
				}
				// When no longer idle, cancel timer
				else if (topic === 'active') {
					if (this._emptyTrashTimeoutID) {
						clearTimeout(this._emptyTrashTimeoutID);
						this._emptyTrashTimeoutID = null;
					}
				}
			}
		};
		
		var idleService = Components.classes["@mozilla.org/widget/useridleservice;1"].
							getService(Components.interfaces.nsIUserIdleService);
		idleService.addIdleObserver(this._emptyTrashIdleObserver, 305);
	}
	
	
	this.addToPublications = function (items, options = {}) {
		if (!items.length) return;
		
		return Zotero.DB.executeTransaction(async function () {
			var timestamp = Zotero.DB.transactionTimestamp;
			
			var allItems = [...items];
			
			if (options.license) {
				for (let item of items) {
					if (!options.keepRights || !item.getField('rights')) {
						item.setField('rights', options.licenseName);
					}
				}
			}
			
			if (options.childNotes) {
				for (let item of items) {
					item.getNotes().forEach(id => allItems.push(Zotero.Items.get(id)));
				}
			}
			
			if (options.childFileAttachments || options.childLinks) {
				for (let item of items) {
					item.getAttachments().forEach(id => {
						var attachment = Zotero.Items.get(id);
						var linkMode = attachment.attachmentLinkMode;
						
						if (linkMode == Zotero.Attachments.LINK_MODE_LINKED_FILE) {
							Zotero.debug("Skipping child linked file attachment on drag");
							return;
						}
						if (linkMode == Zotero.Attachments.LINK_MODE_LINKED_URL) {
							if (!options.childLinks) {
								Zotero.debug("Skipping child link attachment on drag");
								return;
							}
						}
						else if (!options.childFileAttachments) {
							Zotero.debug("Skipping child file attachment on drag");
							return;
						}
						allItems.push(attachment);
					});
				}
			}
			
			await Zotero.Utilities.Internal.forEachChunkAsync(allItems, 250, async function (chunk) {
				for (let item of chunk) {
					item.setPublications(true);
					item.synced = false;
				}
				let ids = chunk.map(item => item.id);
				await Zotero.DB.queryAsync(
					`UPDATE items SET synced=0, clientDateModified=? WHERE itemID IN (${ids.join(", ")})`,
					timestamp
				);
				await Zotero.DB.queryAsync(
					`INSERT OR IGNORE INTO publicationsItems VALUES (${ids.join("), (")})`
				);
			}.bind(this));
			Zotero.Notifier.queue('modify', 'item', allItems.map(item => item.id));
		}.bind(this));
	};
	
	
	this.removeFromPublications = function (items) {
		return Zotero.DB.executeTransaction(async function () {
			let allItems = [];
			for (let item of items) {
				if (!item.inPublications) {
					throw new Error(`Item ${item.libraryKey} is not in My Publications`);
				}
				
				// Remove all child items too
				if (item.isRegularItem()) {
					allItems.push(...this.get(item.getNotes(true).concat(item.getAttachments(true))));
				}
				
				allItems.push(item);
			}
			
			allItems.forEach(item => {
				item.setPublications(false);
				item.synced = false;
			});
			
			var timestamp = Zotero.DB.transactionTimestamp;
			await Zotero.Utilities.Internal.forEachChunkAsync(allItems, 250, async function (chunk) {
				let idStr = chunk.map(item => item.id).join(", ");
				await Zotero.DB.queryAsync(
					`UPDATE items SET synced=0, clientDateModified=? WHERE itemID IN (${idStr})`,
					timestamp
				);
				await Zotero.DB.queryAsync(`DELETE FROM publicationsItems WHERE itemID IN (${idStr})`);
			}.bind(this));
			Zotero.Notifier.queue('modify', 'item', items.map(item => item.id));
		}.bind(this));
	};
	
	
	/**
	 * Purge unused data values
	 */
	this.purge = async function () {
		if (!Zotero.Prefs.get('purge.items')) {
			return;
		}
		
		await Zotero.DB.executeTransaction(async function () {
			let sql = "DELETE FROM itemDataValues WHERE valueID NOT IN "
				+ "(SELECT valueID FROM itemData)";
			await Zotero.DB.queryAsync(sql, [], { ignoreDBLock: true });
		}, { disableForeignKeys: true });
		
		Zotero.Prefs.set('purge.items', false)
	};
	
	
	
	this.getFirstCreatorFromJSON = function (json) {
		Zotero.warn("Zotero.Items.getFirstCreatorFromJSON() is deprecated "
			+ "-- use Zotero.Utilities.Internal.getFirstCreatorFromItemJSON()");
		return Zotero.Utilities.Internal.getFirstCreatorFromItemJSON(json);
	};
	
	
	/**
	 * Return a firstCreator string from internal creators data (from Zotero.Item::getCreators()).
	 *
	 * Used in Zotero.Item::getField() for unsaved items
	 *
	 * @param {Integer} itemTypeID
	 * @param {Object} creatorData
	 * @param {Object} [options]
	 * @param {Boolean} [options.omitBidiIsolates]
	 * @return {String}
	 */
	this.getFirstCreatorFromData = function (itemTypeID, creatorsData, options) {
		if (!options) {
			options = {
				omitBidiIsolates: false
			};
		}
		
		if (creatorsData.length === 0) {
			return "";
		}
		
		var validCreatorTypes = [
			Zotero.CreatorTypes.getPrimaryIDForType(itemTypeID),
			Zotero.CreatorTypes.getID('editor'),
			// Director used to be the primary type for Video Recording before
			// Creator (mapped to Author) was added
			Zotero.CreatorTypes.getID('director'),
			Zotero.CreatorTypes.getID('contributor')
		];
	
		for (let creatorTypeID of validCreatorTypes) {
			let matches = creatorsData.filter(data => data.creatorTypeID == creatorTypeID)
			if (!matches.length) {
				continue;
			}
			if (matches.length === 1) {
				return matches[0].lastName;
			}
			if (matches.length === 2) {
				let a = matches[0];
				let b = matches[1];
				let args = options.omitBidiIsolates
					? [a.lastName, b.lastName]
					// \u2068 FIRST STRONG ISOLATE: Isolates the directionality of characters that follow
					// \u2069 POP DIRECTIONAL ISOLATE: Pops the above isolation
					: [`\u2068${a.lastName}\u2069`, `\u2068${b.lastName}\u2069`];
				return Zotero.getString('general.andJoiner', args);
			}
			if (matches.length >= 3) {
				return matches[0].lastName + " " + Zotero.getString('general.etAl');
			}
		}
		
		return "";
	};
	
	
	/**
	 * Get the top-level items of all passed items
	 *
	 * @param {Zotero.Item[]} items
	 * @return {Zotero.Item[]}
	 */
	this.getTopLevel = function (items) {
		return [...new Set(items.map(item => item.topLevelItem))];
	};
	
	
	/**
	 * Return an array of items with descendants of selected top-level items removed
	 *
	 * Non-top-level items that aren't descendents of selected items are kept.
	 *
	 * @param {Zotero.Item[]}
	 * @return {Zotero.Item[]}
	 */
	this.keepTopLevel = function (items) {
		var topLevelItems = new Set(
			items.filter(item => item.isTopLevelItem())
		);
		return items.filter((item) => {
			var topLevelItem = !item.isTopLevelItem() && item.topLevelItem;
			// Not a child item or not a child of one of the passed items
			return !topLevelItem || !topLevelItems.has(topLevelItem);
		});
	};
	
	
	this.keepParents = function (items) {
		Zotero.debug("Zotero.Items.keepParents() is deprecated -- use Zotero.Items.keepTopLevel() instead");
		return this.keepTopLevel(items);
	};
	
	
	/**
	 * Returns a rough count (0, 1, or 2) of the number of file attachments implied by the passed
	 * array of items (which can include both parent and child items) in order to display a menu
	 * label (e.g., "Show File" or "Show Files")
	 *
	 * @param {[Zotero.Item]} items
	 * @param {Function} filter - An additional filter function to run on file attachment items to
	 *     determine if they qualify
	 * @return {Integer} - 0, 1, or 2, where 2 means >1
	 */
	this.numDistinctFileAttachmentsForLabel = function (items, filter = item => item.isFileAttachment()) {
		const MAX_ITEMS = 2;
		var num = 0;
		var foundKey;
		for (let item of items) {
			if (item.isRegularItem()) {
				// Ideally we want to avoid counting a parent item and its primary attachment as
				// multiple files, but getBestAttachment() is asynchronous and we need to do this
				// synchronously, so try to use the cached best-attachment state
				let { key } = item.getBestAttachmentStateCached();
				let bestAttachment = key && Zotero.Items.getByLibraryAndKey(item.libraryID, key);
				if (bestAttachment && filter(bestAttachment)) {
					if (foundKey) {
						if (key == foundKey) {
							continue;
						}
						return MAX_ITEMS;
					}
					foundKey = key;
					num++;
				}
				// If we don't have a cached primary attachment, the best we can do is count the
				// parent item if it has any file attachments. Since we're not recording the actual
				// attachment being counted, this might result in returning MAX_ITEMS even if only
				// the parent item and primary attachment are selected.
				else if (item.getAttachments().map(itemID => Zotero.Items.get(itemID)).some(filter)) {
					foundKey = item.key;
					num++;
				}
			}
			else if (filter(item)) {
				if (foundKey) {
					if (item.key == foundKey) {
						continue;
					}
					return MAX_ITEMS;
				}
				foundKey = item.key;
				num++;
			}
			if (num >= MAX_ITEMS) {
				break;
			}
		}
		return num;
	};
	
	
	/*
	 * Generate SQL to retrieve firstCreator field
	 *
	 * Why do we do this entirely in SQL? Because we're crazy. Crazy like foxes.
	 */
	var _firstCreatorSQL = '';
	function _getFirstCreatorSQL() {
		if (_firstCreatorSQL) {
			return _firstCreatorSQL;
		}

		var localizedAnd = Zotero.getString('general.andJoiner').replace(/%S/g, '%s');
		var localizedEtAl = Zotero.getString('general.etAl');

		// Generate a CASE block that returns the firstCreator display string
		// for creators matching the given WHERE clause
		function caseBlock(where) {
			return "CASE ("
				+ `SELECT COUNT(*) FROM itemCreators IC ${where}`
				+ ") "
				+ "WHEN 0 THEN NULL "
				+ "WHEN 1 THEN ("
					+ `SELECT lastName FROM itemCreators IC NATURAL JOIN creators ${where}`
				+ ") "
				+ "WHEN 2 THEN ("
					+ "SELECT PRINTF("
						+ `'${localizedAnd}'`
						+ ", "
						// \u2068 FIRST STRONG ISOLATE / \u2069 POP DIRECTIONAL ISOLATE
						+ `(SELECT '\u2068' || lastName || '\u2069' FROM itemCreators IC NATURAL JOIN creators ${where} ORDER BY orderIndex LIMIT 1)`
						+ ", "
						+ `(SELECT '\u2068' || lastName || '\u2069' FROM itemCreators IC NATURAL JOIN creators ${where} ORDER BY orderIndex LIMIT 1,1) `
					+ ")"
				+ ") "
				+ "ELSE ("
					+ "SELECT "
					+ `(SELECT lastName FROM itemCreators IC NATURAL JOIN creators ${where} ORDER BY orderIndex LIMIT 1)`
					+ " || ' " + localizedEtAl + "' "
				+ ") "
				+ "END";
		}

		let primaryJoin = "LEFT JOIN itemTypeCreatorTypes ITCT "
			+ "ON (IC.creatorTypeID=ITCT.creatorTypeID AND ITCT.itemTypeID=O.itemTypeID) "
			+ "WHERE itemID=O.itemID AND primaryField=1";
		function creatorTypeWhere(typeName) {
			return `WHERE itemID=O.itemID AND creatorTypeID=${Zotero.CreatorTypes.getID(typeName)}`;
		}

		let sql = "COALESCE("
			+ caseBlock(primaryJoin) + ", "
			+ caseBlock(creatorTypeWhere('editor')) + ", "
			// Director used to be the primary type for Video Recording before
			// Creator (mapped to Author) was added
			+ caseBlock(creatorTypeWhere('director')) + ", "
			+ caseBlock(creatorTypeWhere('contributor'))
			+ ") AS firstCreator";

		_firstCreatorSQL = sql;
		return sql;
	}
	
	
	/*
	 * Generate SQL to retrieve sortCreator field
	 */
	var _sortCreatorSQL = '';
	function _getSortCreatorSQL() {
		if (_sortCreatorSQL) {
			return _sortCreatorSQL;
		}

		let nameSQL = "lastName || ' ' || firstName ";

		// Generate a CASE block that returns sortCreator strings (full names
		// space-concatenated) for creators matching the given WHERE clause
		function caseBlock(where) {
			return "CASE ("
				+ `SELECT COUNT(*) FROM itemCreators IC ${where}`
				+ ") "
				+ "WHEN 0 THEN NULL "
				+ "WHEN 1 THEN ("
					+ `SELECT ${nameSQL}FROM itemCreators IC NATURAL JOIN creators ${where}`
				+ ") "
				+ "WHEN 2 THEN ("
					+ "SELECT "
					+ `(SELECT ${nameSQL}FROM itemCreators IC NATURAL JOIN creators ${where} ORDER BY orderIndex LIMIT 1)`
					+ " || ' ' || "
					+ `(SELECT ${nameSQL}FROM itemCreators IC NATURAL JOIN creators ${where} ORDER BY orderIndex LIMIT 1,1)`
				+ ") "
				+ "ELSE ("
					+ "SELECT "
					+ `(SELECT ${nameSQL}FROM itemCreators IC NATURAL JOIN creators ${where} ORDER BY orderIndex LIMIT 1)`
					+ " || ' ' || "
					+ `(SELECT ${nameSQL}FROM itemCreators IC NATURAL JOIN creators ${where} ORDER BY orderIndex LIMIT 1,1)`
					+ " || ' ' || "
					+ `(SELECT ${nameSQL}FROM itemCreators IC NATURAL JOIN creators ${where} ORDER BY orderIndex LIMIT 2,1)`
				+ ") "
				+ "END";
		}

		let primaryJoin = "LEFT JOIN itemTypeCreatorTypes ITCT "
			+ "ON (IC.creatorTypeID=ITCT.creatorTypeID AND ITCT.itemTypeID=O.itemTypeID) "
			+ "WHERE itemID=O.itemID AND primaryField=1";
		function creatorTypeWhere(typeName) {
			return `WHERE itemID=O.itemID AND creatorTypeID=${Zotero.CreatorTypes.getID(typeName)}`;
		}

		let sql = "COALESCE("
			+ caseBlock(primaryJoin) + ", "
			+ caseBlock(creatorTypeWhere('editor')) + ", "
			// Director used to be the primary type for Video Recording before
			// Creator (mapped to Author) was added
			+ caseBlock(creatorTypeWhere('director')) + ", "
			+ caseBlock(creatorTypeWhere('contributor'))
			+ ") AS sortCreator";

		_sortCreatorSQL = sql;
		return sql;
	}


	let _stripFromSortTitle = [
		'</?i>',
		'</?b>',
		'</?sub>',
		'</?sup>',
		'<span style="font-variant:small-caps;">',
		'<span class="nocase">',
		'</span>',
		// Any punctuation at the beginning of the string, repeated any number
		// of times, and any opening punctuation that follows
		'^\\s*([^\\P{P}@#*])\\1*[\\p{Ps}"\']*',
	].map(re => Zotero.Utilities.XRegExp(re, 'g'));
	
	
	this.getSortTitle = function (title) {
		if (!title) {
			return '';
		}

		if (typeof title == 'number') {
			return title.toString();
		}

		for (let re of _stripFromSortTitle) {
			title = title.replace(re, '');
		}
		return title.trim();
	};


	/**
	 * Find attachment items whose paths begin with the passed `pathPrefix` and don't exist on disk
	 *
	 * @param {Number} libraryID
	 * @param {String} pathPrefix
	 * @return {Zotero.Item[]}
	 */
	this.findMissingLinkedFiles = async function (libraryID, pathPrefix) {
		let sql = "SELECT itemID FROM items JOIN itemAttachments USING (itemID) "
			+ "WHERE itemID NOT IN (SELECT itemID FROM deletedItems) "
			+ `AND linkMode=${Zotero.Attachments.LINK_MODE_LINKED_FILE} `
			+ "AND path LIKE ? ESCAPE '\\' "
			+ "AND libraryID=?";
		let ids = await Zotero.DB.columnQueryAsync(sql, [Zotero.DB.escapeSQLExpression(pathPrefix) + '%', libraryID]);
		let items = await this.getAsync(ids);
		let missingItems = await Promise.all(
			items.map(async item => ((await item.fileExists()) ? false : item))
		);
		return missingItems.filter(Boolean);
	};
	
	
	Zotero.DataObjects.call(this);
	
	return this;
}.bind(Object.create(Zotero.DataObjects.prototype))();
