/* © SRSoftware 2025 */
package de.srsoftware.umbrella.stock;

import static de.srsoftware.tools.Optionals.is0;
import static de.srsoftware.tools.Optionals.nullIfEmpty;
import static de.srsoftware.tools.jdbc.Condition.*;
import static de.srsoftware.tools.jdbc.Condition.like;
import static de.srsoftware.tools.jdbc.Query.*;
import static de.srsoftware.tools.jdbc.Query.SelectQuery.ALL;
import static de.srsoftware.umbrella.core.Errors.*;
import static de.srsoftware.umbrella.core.ModuleRegistry.noteService;
import static de.srsoftware.umbrella.core.constants.Field.*;
import static de.srsoftware.umbrella.core.constants.Field.LOCATIONS;
import static de.srsoftware.umbrella.core.constants.Field.TYPE;
import static de.srsoftware.umbrella.core.constants.Field.UNIT;
import static de.srsoftware.umbrella.core.constants.Text.*;
import static de.srsoftware.umbrella.core.constants.Text.ITEMS;
import static de.srsoftware.umbrella.core.constants.Text.LOCATION;
import static de.srsoftware.umbrella.core.constants.Text.PROPERTY;
import static de.srsoftware.umbrella.core.exceptions.UmbrellaException.*;
import static de.srsoftware.umbrella.core.model.Translatable.t;
import static de.srsoftware.umbrella.stock.Constants.*;
import static java.lang.System.Logger.Level.*;
import static java.text.MessageFormat.format;

import de.srsoftware.tools.jdbc.Query;
import de.srsoftware.umbrella.core.BaseDb;
import de.srsoftware.umbrella.core.api.Owner;
import de.srsoftware.umbrella.core.constants.Field;
import de.srsoftware.umbrella.core.constants.Text;
import de.srsoftware.umbrella.core.model.*;
import de.srsoftware.umbrella.core.model.Location;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

public class SqliteDb extends BaseDb implements StockDb {

	private record LegacyLocation(String id, String parent, String name, String description){
		public static LegacyLocation of(ResultSet rs) throws SQLException {
			return new LegacyLocation(rs.getString(ID), rs.getString(LOCATION_ID), rs.getString(NAME), rs.getString(DESCRIPTION));
		}

		public String owner() {
			var parts = id.split(":");
			if (parts.length != 3) throw databaseException("Expected legacy location id to be of the form ss:dd:ss, encountered {ID}!", ID,id);
			return String.join(":", parts[0], parts[1]);
		}
	};

	public SqliteDb(Connection connection) {
		super(connection);
	}

	@Override
	public Property addNewProperty(long itemId, String name, Object value, String unit) {
		try {
			db.setAutoCommit(false);
			var rs = insertInto(TABLE_PROPERTIES, NAME, TYPE,UNIT).values(name,0,unit).execute(db).getGeneratedKeys();
			Long propertyId = null;
			if (rs.next()) propertyId = rs.getLong(1);
			rs.close();
			if (propertyId == null || propertyId == 0) throw failedToStoreObject(t(PROPERTY));
			insertInto(TABLE_ITEM_PROPERTIES,ITEM_ID,PROPERTY_ID,VALUE).values(itemId,propertyId,value).execute(db).close();
			db.setAutoCommit(true);
			return new Property(propertyId,name,value,unit);
		} catch (SQLException e) {
			throw failedToStoreObject(t(PROPERTY)).causedBy(e);
		}
	}

	private void createDescriptionColumn(){
		try {
			var sql = "ALTER TABLE {0} ADD COLUMN {1} TEXT";
			sql = format(sql,TABLE_ITEMS, DESCRIPTION);
			db.prepareStatement(sql).execute();
		} catch (SQLException e) {
			throw databaseException(FAILED_TO_ADD_COLUMN, NAME, DESCRIPTION, TABLE,TABLE_ITEMS).causedBy(e);
		}
	}

	/**
	 * id, owner, owner_id, code, name, location_id
	 * @throws SQLException
	 */
	private void createIntermediateItemsTable() throws SQLException { // create intermediate table
		var sql = """
				CREATE TABLE IF NOT EXISTS items_temp (
					{0} INTEGER PRIMARY KEY,
					{1} VARCHAR(50) NOT NULL,
					{2} LONG NOT NULL,
					{3} VARCHAR(255),
					{4} VARCHAR(255) NOT NULL,
					{5} LONG NOT NULL)""";
		sql = format(sql, ID, OWNER, OWNER_NUMBER, Field.CODE, NAME, LOCATION_ID);
		db.prepareStatement(sql).execute();
	}

	/**
	 * id, owner, parent_location_id, name, description
	 * @throws SQLException
	 */
	private void createIntermediateLocationTable() throws SQLException { // create intermediate table
		var sql = """
				CREATE TABLE IF NOT EXISTS locations_temp (
					{0} INTEGER PRIMARY KEY,
					{1} VARCHAR(50) NOT NULL,
					{2} LONG DEFAULT NULL,
					{3} VARCHAR(255) NOT NULL,
					{4} TEXT)""";
		sql = format(sql, ID, OWNER, PARENT_LOCATION_ID, NAME, DESCRIPTION);
		db.prepareStatement(sql).execute();
	}

	/**
	 * item_id, prop_id	, value
	 * @throws SQLException
	 */
	private void createIntermediatePropsTable() throws SQLException { // create intermediate table
		var sql = "CREATE TABLE IF NOT EXISTS item_props_temp ( {0} LONG NOT NULL, {1} LONG NOT NULL, {2} LONG NOT NULL, PRIMARY KEY({0}, {1}))";
		sql = format(sql, ITEM_ID, PROPERTY_ID, VALUE);
		db.prepareStatement(sql).execute();
	}

	private void createItemsTable() {
		try {
			var sql = "CREATE TABLE IF NOT EXISTS {0} ( {1} VARCHAR(255) PRIMARY KEY, {2} VARCHAR(255) NOT NULL, {3} TEXT, {4} VARCHAR(255))";
			sql = format(sql, TABLE_ITEMS, ID, Field.CODE, NAME, LOCATION_ID);
			db.prepareStatement(sql).execute();
		} catch (SQLException e) {
			throw failedToCreateTable(TABLE_ITEMS).causedBy(e);
		}
	}

	private void createItemPropsTable() {
		try {
			var sql = "CREATE TABLE IF NOT EXISTS {0} ( {1} INT NOT NULL, {2} INT NOT NULL, {3} VARCHAR(255) NOT NULL, PRIMARY KEY({1}, {2}))";
			sql = format(sql, TABLE_ITEM_PROPERTIES, ITEM_ID, PROPERTY_ID,VALUE);
			db.prepareStatement(sql).execute();
		} catch (SQLException e) {
			throw failedToCreateTable(TABLE_ITEM_PROPERTIES).causedBy(e);
		}
	}

	private void createLocationsTable() {
		try {
			var sql = "CREATE TABLE IF NOT EXISTS {0} ( {1} VARCHAR(255) PRIMARY KEY, {2} VARCHAR(255) DEFAULT NULL, {3} VARCHAR(255) NOT NULL, {4} TEXT)";
			sql = format(sql, TABLE_LOCATIONS, ID, LOCATION_ID, NAME, DESCRIPTION);
			db.prepareStatement(sql).execute();
		} catch (SQLException e) {
			throw failedToCreateTable(TABLE_LOCATIONS).causedBy(e);
		}
	}

	private void createPropertiesTable() {
		try {
			var sql = "CREATE TABLE IF NOT EXISTS {0} ( {1} INTEGER PRIMARY KEY, {2} VARCHAR(255) NOT NULL, {3} INT NOT NULL, {4} VARCHAR(255))";
			sql = format(sql, TABLE_PROPERTIES, ID, NAME, TYPE, UNIT);
			db.prepareStatement(sql).execute();
		} catch (SQLException e) {
			throw failedToCreateTable(TABLE_PROPERTIES).causedBy(e);
		}
	}

	@Override
	protected int createTables() {
		int currentVersion = createSettingsTable();
		switch (currentVersion){
			case 0:
				createLocationsTable();
				createItemsTable();
				createPropertiesTable();
				createItemPropsTable();
			case 1:
				dropTokenTable();
			case 2:
				transformTables();
			case 3:
				createDescriptionColumn();
		}
		return setCurrentVersion(4);
	}

	@Override
	public Location delete(DbLocation location) {
		try {
			Query.delete().from(TABLE_LOCATIONS).where(ID,equal(location.id())).execute(db);
			return location;
		} catch (SQLException e){
			throw failedToDropObject(location.name()).causedBy(e);
		}
	}

	private void dropTokenTable() {
		try {
			db.prepareStatement("DROP TABLE IF EXISTS tokens").execute();
		} catch (SQLException e) {
			throw failedToDropObject("table tokens").causedBy(e);
		}
	}

	@Override
	public Map<Long, Item> find(Collection<Owner> owners, Collection<String> keys, boolean fulltext) {
		try {
			var items = new HashMap<Long,Item>();
			var ownerCodes = owners.stream().map(Owner::dbCode).toArray();
			var query = select(ALL).from(TABLE_ITEMS).where(OWNER, in(ownerCodes));
			if (fulltext) {
				query.leftJoin(ID,TABLE_ITEM_PROPERTIES,ITEM_ID);
				for (var key : keys) query.where(format("CONCAT({0},\" \",{1},\" \",{2})", NAME, DESCRIPTION,VALUE),like("%"+key+"%"));
			} else {
				for (var key : keys) query.where(NAME,like("%"+key+"%"));
			}
			var rs = query.exec(db);
			while (rs.next()){
				var item = Item.of(rs);
				items.put(item.id(),item);
			}
			rs.close();
			return items;
		} catch (SQLException e) {
			throw databaseException(FAILED_TO_LIST_ENTITIES, TYPE,t(ITEMS)).causedBy(e);
		}
	}

	@Override
	public Collection<DbLocation> listChildLocations(long parentId) {
		try {
			var rs = select(ALL).from(TABLE_LOCATIONS).where(PARENT_LOCATION_ID,equal(parentId)).exec(db);
			var list = new ArrayList<DbLocation>();
			while (rs.next()) list.add(DbLocation.of(rs));
			rs.close();
			return list;
		} catch (SQLException e){
			throw databaseException(FAILED_TO_LOAD_CHILD_LOCATIONS,PARENT,parentId).causedBy(e);
		}
	}

	@Override
	public Collection<DbLocation> listCompanyLocations(Company company) {
		try {
			var rs = select(ALL).from(TABLE_LOCATIONS).where(OWNER,equal(company.dbCode())).where(PARENT_LOCATION_ID,isNull()).exec(db);
			var list = new ArrayList<DbLocation>();
			while (rs.next()) list.add(DbLocation.of(rs));
			rs.close();
			return list;
		} catch (SQLException e){
			throw databaseException(FAILED_TO_LOAD_ENTITIES_OF_OWNER, TYPE,t(Text.LOCATIONS), OWNER,company.name()).causedBy(e);
		}
	}

	@Override
	public Collection<Item> listItemsAt(Location location) {
		try {
			var rs = select(ALL).from(TABLE_ITEMS).where(LOCATION_ID,equal(location.id())).exec(db);
			var list = new ArrayList<Item>();
			while (rs.next()) list.add(Item.of(rs));
			rs.close();

			for (var item : list) loadProperties(item);
			return list;
		} catch (SQLException e){
			throw databaseException(FAILED_TO_LOAD_ENTITIES_OF_OWNER, TYPE,ITEMS, OWNER,location).causedBy(e);
		}
	}

	@Override
	public Collection<Item> listItemsOf(Company company) {
		try {
			var owner = company.dbCode();
			var rs = select(ALL).from(TABLE_ITEMS).where(OWNER,equal(owner)).exec(db);
			var list = new ArrayList<Item>();
			while (rs.next()) list.add(Item.of(rs));
			rs.close();

			for (var item : list) loadProperties(item);
			return list;
		} catch (SQLException e){
			throw databaseException(FAILED_TO_LOAD_ENTITIES_OF_OWNER, TYPE,ITEMS, OWNER,company).causedBy(e);
		}
	}

	@Override
	public Item loadProperties(Item item){
		try {
			var rs = select(ALL).from(TABLE_ITEM_PROPERTIES).leftJoin(PROPERTY_ID, TABLE_PROPERTIES, ID).where(ITEM_ID, equal(item.id())).exec(db);
			while (rs.next()) item.properties().add(Property.of(rs));
			rs.close();
			return item;
		} catch (SQLException e){
			throw databaseException(FAILED_TO_LOAD_ENTITIES_OF_OWNER, TYPE,t(Text.PROPERTIES), OWNER,item.name()).causedBy(e);
		}
	}

	@Override
	public Item loadItem(long id) {
		var query = select(ALL).from(TABLE_ITEMS).where(ID,equal(id));
		return loadItem(query);
	}

	@Override
	public Item loadItem(String owner, long itemNumber) {
		var query = select(ALL).from(TABLE_ITEMS).where(OWNER,equal(owner)).where(OWNER_NUMBER,equal(itemNumber));
		return loadItem(query);
	}

	private Item loadItem(SelectQuery query){
		try {
			var rs = query.exec(db);
			Item result = null;
			if (rs.next()) result = Item.of(rs);
			rs.close();
			if (result != null) return result;
			throw notFound(FAILED_TO_LOAD_OBJECT, OBJECT,t(Text.ITEM));
		} catch (SQLException e) {
			throw failedToLoadObject(t(Text.ITEM)).causedBy(e);
		}
	}

	public DbLocation loadLocation(long locationId) {
		try {
			var rs = select(ALL).from(TABLE_LOCATIONS).where(ID,equal(locationId)).exec(db);
			DbLocation loc = null;
			if (rs.next()) loc = DbLocation.of(rs);
			rs.close();
			if (loc != null) return loc;
			throw failedToLoadObject(t(LOCATION),locationId);
		} catch (SQLException e){
			throw failedToLoadObject(t(LOCATION),locationId).causedBy(e);
		}
	}

	@Override
	public Collection<Property> listProperties() {
		try {
			var rs = select(ALL).from(TABLE_PROPERTIES).exec(db);
			var list = new ArrayList<Property>();
			while (rs.next()) list.add(Property.of(rs));
			rs.close();
			return list;
		} catch (SQLException e){
			throw failedToLoadObject(t(Text.PROPERTIES)).causedBy(e);
		}
	}

	@Override
	public Collection<DbLocation> listUserLocations(UmbrellaUser user) {
		try {
			var rs = select(ALL).from(TABLE_LOCATIONS).where(OWNER,equal(user.dbCode())).where(PARENT_LOCATION_ID,isNull()).exec(db);
			var list = new ArrayList<DbLocation>();
			while (rs.next()) list.add(DbLocation.of(rs));
			rs.close();
			return list;
		} catch (SQLException e){
			throw databaseException(FAILED_TO_LOAD_ENTITIES_OF_OWNER, TYPE,t(Text.LOCATIONS), OWNER,user.name()).causedBy(e);
		}
	}

	@Override
	public long nextItemNumberFor(Owner owner) {
		try {
			var rs = select("max(owner_number)").from(TABLE_ITEMS).where(OWNER,equal(owner.dbCode())).exec(db);
			long number = rs.next() ? rs.getLong(1) : 0;
			rs.close();
			return number +1L;
		} catch (SQLException e) {
			throw databaseException(FAILED_TO_LOAD_ENTITIES_OF_OWNER, TYPE,"last item number", OWNER,owner).causedBy(e);
		}
	}

	@Override
	public Map<String, Object> pathToLocation(Location target) {
		var root = new HashMap<String,Object>();
		var location = loadLocation(target.id());
		root.put(NAME,location.name());
		root.put(ID,location.id());
		try {
			while (!is0(location.parent())) {
				var rs = select(ALL).from(TABLE_LOCATIONS).where(ID, equal(location.parent())).exec(db);
				var parent = DbLocation.of(rs);
				rs.close();
				var current = new HashMap<String,Object>();
				current.put(NAME,parent.name());
				current.put(ID,parent.id());
				current.put(LOCATIONS,List.of(root));
				root = current;
				location = parent;
			}
		} catch (SQLException e){
			throw databaseException(FAILED_TO_LOAD_ENTITIES_OF_OWNER, TYPE,t(Text.PATH), OWNER,target).causedBy(e);
		}
		return root;
	}

	private void replaceItemsTable() throws SQLException {
		db.prepareStatement(format("DROP TABLE {0}",TABLE_ITEMS)).execute();
		db.prepareStatement(format("ALTER TABLE {0} RENAME TO {1}","items_temp",TABLE_ITEMS)).execute();
	}

	private void replaceItemPropsTable() throws SQLException {
		db.prepareStatement(format("DROP TABLE {0}",TABLE_ITEM_PROPERTIES)).execute();
		db.prepareStatement(format("ALTER TABLE {0} RENAME TO {1}","item_props_temp",TABLE_ITEM_PROPERTIES)).execute();
	}

	private void replaceLocationsTable() throws SQLException {
		db.prepareStatement(format("DROP TABLE {0}",TABLE_LOCATIONS)).execute();
		db.prepareStatement(format("ALTER TABLE {0} RENAME TO {1}","locations_temp",TABLE_LOCATIONS)).execute();
	}

	@Override
	public DbLocation save(DbLocation location) {
		var parentId = is0(location.parent()) ? null : location.parent();
		if (location.id() == 0) {  // new location
			try {
				var rs = insertInto(TABLE_LOCATIONS, OWNER, PARENT_LOCATION_ID, NAME, DESCRIPTION)
						.values(location.owner().dbCode(),parentId,location.name(),location.description())
						.execute(db).getGeneratedKeys();
				long id = 0;
				if (rs.next()) id = rs.getLong(1);
				rs.close();
				if (id == 0) throw failedToStoreObject(location.name());
				return location.id(id);
			} catch (SQLException e){
				throw failedToStoreObject(location.name()).causedBy(e);
			}
		} else {
			try {
				update(TABLE_LOCATIONS)
						.set(OWNER, PARENT_LOCATION_ID, NAME, DESCRIPTION)
						.where(ID,equal(location.id()))
						.prepare(db)
						.apply(location.owner().dbCode(), parentId, location.name(), location.description())
						.close();
				return location.clear();
			} catch (SQLException e){
				throw databaseException(FAILED_TO_UPDATE_OBJECT, OBJECT,location.name()).causedBy(e);
			}
		}
	}

	@Override
	public Item save(Item item) {
		if (item.id() == 0){
			var number = nextItemNumberFor(item.location().resolve().owner());
			try {
				var rs = insertInto(TABLE_ITEMS, OWNER, OWNER_NUMBER, Field.CODE, NAME, DESCRIPTION, LOCATION_ID)
						.values(item.owner().dbCode(), number, item.code(), item.name(), item.description(), item.location().id())
						.execute(db).getGeneratedKeys();
				if (rs.next()) item.id(rs.getLong(1)).ownerNumber(number);
				rs.close();
			} catch (SQLException e) {
				throw failedToStoreObject(item.name()).causedBy(e);
			}
		} else if (item.isDirty()) {
			try {
				var location = item.location();
				var query = update(TABLE_ITEMS).where(ID, equal(item.id()));
				if (location == null) {
					query.set(Field.CODE, NAME, DESCRIPTION);
				} else {
					query.set(Field.CODE, NAME, DESCRIPTION, LOCATION_ID);
				}
				var pq = query.prepare(db);
				if (location == null) {
					pq.apply(item.code(),item.name(),item.description()).close();
				} else {
					pq.apply(item.code(),item.name(),item.description(),item.location().id()).close();
				}
				item.clear();
			} catch (SQLException e){
				throw databaseException(FAILED_TO_UPDATE_OBJECT, OBJECT,item.name()).causedBy(e);
			}
		}
		saveProperties(item);

		return item;
	}

	private void saveProperties(Item item){
		var saved = new ArrayList<Property>();
		for (var property : item.properties())  saved.add(saveProperty(item, property));
		item.properties().clear();
		item.properties().addAll(saved);
	}

	private Property saveProperty(Item item, Property property) {
		Long propId = property.id();
		if (is0(propId)) {
			try {
				var rs = select(ID).from(TABLE_PROPERTIES).where(NAME,equal(property.name())).where(UNIT,equal(property.unit())).exec(db);
				if (rs.next()) propId = rs.getLong(1);
				rs.close();
			} catch (SQLException e) {
				throw failedToLoadObject(property.name()).causedBy(e);
			}
		}
		if (is0(propId)) return addNewProperty(item.id(), property.name(), property.value(), property.unit());
		return setProperty(item.id(),propId,property.value());
	}

	@Override
	public Property setProperty(long itemId, long existingPropId, Object value) {
		try {
			Property prop = null;
			var rs = select(ALL).from(TABLE_PROPERTIES).where(ID,equal(existingPropId)).exec(db);
			if (rs.next()) prop = Property.of(rs);
			rs.close();
			if (prop == null) throw databaseException(FAILED_TO_LOAD_ENTITIES_OF_OWNER, TYPE,"property "+existingPropId, OWNER,"item "+itemId);
			if ("".equals(value)){
				Query.delete().from(TABLE_ITEM_PROPERTIES).where(ITEM_ID,equal(itemId)).where(PROPERTY_ID,equal(existingPropId)).execute(db);
			} else {
				replaceInto(TABLE_ITEM_PROPERTIES,ITEM_ID,PROPERTY_ID,VALUE).values(itemId,existingPropId,value).execute(db).close();
			}
			return prop.value(value);
		} catch (SQLException e) {
			throw databaseException(FAILED_TO_ADD_PROPERTY_TO_ITEM, OBJECT,itemId).causedBy(e);
		}
	}

	private HashMap<String, Long> transformItems(Map<String, Long> oldLocationIdsToNew) throws SQLException {
		var rs = select(ALL).from(TABLE_ITEMS).exec(db);
		var insert = insertInto("items_temp", OWNER, OWNER_NUMBER, LOCATION_ID, Field.CODE, NAME);
		var oldToNew = new HashMap<String,Long>(); // maps from old item ids to new ones
		while (rs.next()){
			var oldId = rs.getString(ID);
			var parts = oldId.split(":");
			if (parts.length != 3) throw databaseException(UNEXPECTED_ITEM_ID_FORMAT,OBJECT,oldId);
			var owner = String.join(":",parts[0], parts[1]);
			long ownerNumber;
			try {
				ownerNumber = Long.parseLong(parts[2]);
			} catch (NumberFormatException e) {
				throw databaseException(UNEXPECTED_ITEM_ID_FORMAT,OBJECT,oldId).causedBy(e);
			}

			var oldLocationId = rs.getString(LOCATION_ID);
			var locationId = oldLocationIdsToNew.get(oldLocationId);
			if (locationId == null) throw databaseException(UNKNOWN_ITEM_LOCATION, OBJECT,oldId, OWNER,parts[0]+" "+parts[1], Field.LOCATION,oldLocationId);
			var rs2 = insert.values(owner, ownerNumber, locationId, rs.getString(Field.CODE), rs.getString(NAME)).execute(db).getGeneratedKeys();
			oldToNew.put(oldId,rs2.getLong(1));
			rs2.close();
		}
		rs.close();
		insert.execute(db);
		return oldToNew;
	}

	private Map<String, Long> transformLocations() throws SQLException {
		var locations = new ArrayList<LegacyLocation>();
		var oldToNew = new HashMap<String,Long>();
		var rs = select(ALL).from(TABLE_LOCATIONS).exec(db);
		while (rs.next()) locations.add(LegacyLocation.of(rs));
		rs.close();

		var query = insertInto("locations_temp", OWNER, PARENT_LOCATION_ID, NAME, DESCRIPTION);
		while (!locations.isEmpty()){
			var legacyLocation = locations.removeFirst();
			var parentRef = nullIfEmpty(legacyLocation.parent());
			Long parentId = null;
			if (parentRef != null) {
				parentId = oldToNew.get(parentRef);
				if (parentId == null) { // parent not processed, re-add to end of queue
					LOG.log(WARNING,"Postponing {0}, as {1} is not present…",legacyLocation.id,legacyLocation.parent);
					locations.add(legacyLocation);
					continue;
				}
			}
			var owner = legacyLocation.owner();

			rs = query.values(owner, parentId, legacyLocation.name, legacyLocation.description).execute(db).getGeneratedKeys();
			oldToNew.put(legacyLocation.id(),rs.getLong(1));
			rs.close();
		}
		return oldToNew;
	}

	private void transformProperties(HashMap<String, Long> oldItemIdsToNew) throws SQLException {
		var rs = select(ALL).from(TABLE_ITEM_PROPERTIES).exec(db);
		var insert = insertInto("item_props_temp",ITEM_ID, PROPERTY_ID, VALUE);
		while (rs.next()){
			var oldItemId = rs.getString(ITEM_ID);
			var itemId    = oldItemIdsToNew.get(oldItemId);
			if (itemId == null) throw databaseException(MISSING_NEW_ITEM_ID, ID,oldItemId);
			insert.values(itemId, rs.getLong(PROPERTY_ID), rs.getString(VALUE));
		}
		rs.close();
		insert.execute(db).close();
	}

	private void transformTables(){
		try {
			db.setAutoCommit(false);
			createIntermediateLocationTable();
			createIntermediateItemsTable();
			createIntermediatePropsTable();
			var oldLocationIdsToNew = transformLocations();
			var oldItemIdsToNew = transformItems(oldLocationIdsToNew);
			updateNotes(oldItemIdsToNew);
			transformProperties(oldItemIdsToNew);
			replaceLocationsTable();
			replaceItemsTable();
			replaceItemPropsTable();
			db.setAutoCommit(true);
		} catch (Exception e) {
			try {
				db.rollback();
			} catch (SQLException ignored) {
			}
			throw databaseException(FAILED_TO_UPDATE_OBJECT, OBJECT, t(TABLE_WITH_NAME,NAME,TABLE_LOCATIONS)).causedBy(e);
		}
	}

	private void updateNotes(HashMap<String, Long> oldItemIdsToNew) {
		var noteService = noteService();
		Map<Long, Note> notes = noteService.getNotes("stock", null);
		for (var entry : notes.entrySet()){
			var note = entry.getValue();
			var oldEntityId = note.entityId();
			var newEntityId = oldItemIdsToNew.get(oldEntityId);
			if (newEntityId != null) noteService.save(note.entityId(""+newEntityId));
		}
	}
}
