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


import static de.srsoftware.tools.jdbc.Condition.*;
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.translator;
import static de.srsoftware.umbrella.core.constants.Constants.FALLBACK_LANG;
import static de.srsoftware.umbrella.core.constants.Field.*;
import static de.srsoftware.umbrella.core.constants.Field.AMOUNT;
import static de.srsoftware.umbrella.core.constants.Field.COMPANY;
import static de.srsoftware.umbrella.core.constants.Field.CUSTOMER;
import static de.srsoftware.umbrella.core.constants.Field.NUMBER;
import static de.srsoftware.umbrella.core.constants.Field.SENDER;
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.Field.UNIT_PRICE;
import static de.srsoftware.umbrella.core.constants.Text.*;
import static de.srsoftware.umbrella.core.exceptions.UmbrellaException.*;
import static de.srsoftware.umbrella.core.model.Document.DEFAULT_THOUSANDS_SEPARATOR;
import static de.srsoftware.umbrella.core.model.Document.State;
import static de.srsoftware.umbrella.core.model.Translatable.t;
import static de.srsoftware.umbrella.documents.Constants.*;
import static java.text.MessageFormat.format;
import static java.time.ZoneOffset.UTC;

import de.srsoftware.tools.Pair;
import de.srsoftware.tools.jdbc.Query;
import de.srsoftware.umbrella.core.BaseDb;
import de.srsoftware.umbrella.core.constants.Text;
import de.srsoftware.umbrella.core.exceptions.UmbrellaException;
import de.srsoftware.umbrella.core.model.*;
import de.srsoftware.umbrella.documents.model.*;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.Instant;
import java.util.*;
import java.util.regex.Pattern;

public class SqliteDb extends BaseDb implements DocumentDb{
	private static final System.Logger LOG = System.getLogger(SqliteDb.class.getSimpleName());
	private static final String DB_VERSION = "message_db_version";
	private static final int	INITIAL_DB_VERSION = 1;
	private static final Pattern NUMBER_PATTERN = Pattern.compile("(\\D*)(\\d+)(.*)");

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

	private void addTemplateColumn() {
		try {
			var sql = format("ALTER TABLE {0} ADD COLUMN {1} VARCHAR(255)",TABLE_DOCUMENTS,TEMPLATE);
			db.prepareStatement(sql).execute();
		} catch (SQLException e) {
			throw databaseException(FAILED_TO_UPDATE_COLUMN,"old",TEMPLATE_ID,"new",TEMPLATE, TABLE,TABLE_DOCUMENTS).causedBy(e);
		}
	}

	protected int createTables() {
		int currentVersion = createSettingsTable();
		switch (currentVersion) {
			case 0:
				createTableDocumentTypes();
				createTableTemplates();
				createTableDocuments();
				createTablePositions();
				createTableCustomerPrices();
				createTableCustomerSettings();
			case 1:
				addTemplateColumn();
				moveTemplateNames();
				dropTemplateTable();
				dropTemplateIdColumn();
		}
		return setCurrentVersion(2);
	}

	private void createTableCustomerPrices() {
		var sql = "CREATE TABLE IF NOT EXISTS {0} ({1} INT NOT NULL, {2} VARCHAR(255), {3} VARCHAR(50), {4} INTEGER)";
		try {
			var stmt = db.prepareStatement(format(sql,TABLE_PRICES, COMPANY_ID,CUSTOMER_NUMBER, ITEM_CODE,PRICE));
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw failedToCreateTable(TABLE_PRICES).causedBy(e);
		}
	}

	private void createTableCustomerSettings() {
		var sql = "CREATE TABLE IF NOT EXISTS {0} ({1} INT NOT NULL, {2} INT NOT NULL, {3} VARCHAR(255), {4} TEXT, {5} TEXT, {6} TEXT, PRIMARY KEY ({1}, {2}, {3}))";
		try {
			var stmt = db.prepareStatement(format(sql,TABLE_CUSTOMER_SETTINGS, COMPANY_ID,DOC_TYPE_ID,CUSTOMER_NUMBER,DEFAULT_HEADER,DEFAULT_FOOTER,DEFAULT_MAIL));
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw failedToCreateTable(TABLE_CUSTOMER_SETTINGS).causedBy(e);
		}
	}


	private void createTableDocuments() {
		var createTable = """
CREATE TABLE IF NOT EXISTS {0} (
	{1} INTEGER PRIMARY KEY,
	{2} INT NOT NULL,
	{3} INT NOT NULL,
	{4} TEXT NOT NULL,
	{5} TIMESTAMP NOT NULL,
	{6} INT NOT NULL,
	{7} INT,
	{8} VARCHAR(100),
	{9} TEXT,
	{10} TEXT,
	{11} VARCHAR(10) NOT NULL,
	{12} TEXT NOT NULL,
	{13} VARCHAR(255),
	{14} TEXT,
	{15} TEXT,
	{16} TEXT,
	{17} VARCHAR(255),
	{18} VARCHAR(255),
	{19} VARCHAR(255)
)""";
		createTable = format(createTable,TABLE_DOCUMENTS, ID, TYPE_ID, COMPANY_ID, NUMBER, DATE, STATE, TEMPLATE_ID, DELIVERY_DATE,HEAD,FOOTER,CURRENCY, SENDER,TAX_NUMBER,BANK_ACCOUNT,COURT, CUSTOMER,CUSTOMER_NUMBER,CUSTOMER_TAX_NUMBER,CUSTOMER_EMAIL);
		try {
			var stmt = db.prepareStatement(createTable);
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw failedToCreateTable(TABLE_DOCUMENTS).causedBy(e);
		}
	}

	private void createTableDocumentTypes() {
		var createTable = "CREATE TABLE IF NOT EXISTS {0} ({1} INTEGER PRIMARY KEY, {2} INT, {3} VARCHAR(255) NOT NULL)";
		try {
			var stmt = db.prepareStatement(format(createTable,TABLE_DOCUMENT_TYPES, ID,NEXT_TYPE, NAME));
			stmt.execute();
			stmt.close();
			insertInto(TABLE_DOCUMENT_TYPES, ID,NEXT_TYPE, NAME)
					.values(1,2,TYPE_OFFER)
					.values(2,3,TYPE_CONFIRMATION)
					.values(3,4,TYPE_INVOICE)
					.values(4,4,TYPE_REMINDER)
					.execute(db);
		} catch (SQLException e) {
			throw failedToCreateTable(TABLE_DOCUMENT_TYPES).causedBy(e);
		}
	}

	private void createTablePositions() {
		var sql = """
CREATE TABLE IF NOT EXISTS {0} (
	{1} INTEGER NOT NULL,
	{2} INTEGER NOT NULL,
	{3} VARCHAR(50),
	{4} INTEGER NOT NULL NULL,
	{5} VARCHAR(12),
	{6} VARCHAR(255),
	{7} TEXT,
	{8} INTEGER,
	{9} INTEGER,
	{10} INTEGER,
	{11} BOOLEAN DEFAULT 0
)
""";
		sql = format(sql,TABLE_POSITIONS,DOCUMENT_ID,POS, ITEM_CODE,AMOUNT,UNIT, TITLE, DESCRIPTION,PRICE,TAX,TIME_ID, OPTIONAL);
		try {
			var stmt = db.prepareStatement(sql);
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw failedToCreateTable(TABLE_POSITIONS).causedBy(e);
		}
	}

	private void createTableTemplates() {
		var createTable = "CREATE TABLE IF NOT EXISTS {0} ({1} INTEGER PRIMARY KEY, {2} INT NOT NULL, {3} VARCHAR(255) NOT NULL, {4} BLOB)";
		try {
			var stmt = db.prepareStatement(format(createTable,TABLE_TEMPLATES, ID, COMPANY_ID, NAME, TEMPLATE));
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw failedToCreateTable(TABLE_TEMPLATES).causedBy(e);
		}
	}

	@Override
	public String deleteDoc(Long docId) throws UmbrellaException {
		try {
			db.setAutoCommit(false);
			var rs = select(NUMBER).from(TABLE_DOCUMENTS).where(ID,equal(docId)).exec(db);
			String number = null;
			if (rs.next()) number = rs.getString(NUMBER);
			rs.close();
			delete().from(TABLE_POSITIONS).where(DOCUMENT_ID,equal(docId)).execute(db);
			delete().from(TABLE_DOCUMENTS).where(ID,equal(docId)).execute(db);
			db.setAutoCommit(true);
			if (number != null) return number;
			throw failedToDropObject(t(DOCUMENT_WITH_ID, ID,docId));
		} catch (SQLException e){
			throw failedToDropObject(t(DOCUMENT_WITH_ID, ID,docId)).causedBy(e);
		}
	}

	@Override
	public Long dropPosition(long docId, long pos) throws UmbrellaException {
		try {
			db.setAutoCommit(false);
			delete().from(TABLE_POSITIONS).where(DOCUMENT_ID,equal(docId)).where(POS,equal(pos)).execute(db);
			var sql = format("UPDATE {0} SET {1} = {1}-1 WHERE {2} = ? AND {1} > ?",TABLE_POSITIONS,POS,DOCUMENT_ID);
			var stmt = db.prepareStatement(sql);
			stmt.setLong(1,docId);
			stmt.setLong(2,pos);
			stmt.execute();
			stmt.close();
			db.setAutoCommit(true);
			return pos;
		} catch (SQLException e) {
			throw failedToDropObjectFromObject(POSITION,pos,t(Text.DOCUMENT),docId).causedBy(e);
		}

	}

	private void dropTemplateIdColumn() {
		try {
			var sql = format("ALTER TABLE {0} DROP COLUMN {1}",TABLE_DOCUMENTS,TEMPLATE_ID);
			db.prepareStatement(sql).execute();
		} catch (SQLException e) {
			throw databaseException(FAILED_TO_UPDATE_COLUMN,"old",TEMPLATE_ID,"new",TEMPLATE, TABLE,TABLE_DOCUMENTS).causedBy(e);
		}
	}

	private void dropTemplateTable() {
		try {
			var sql = format("DROP TABLE IF EXISTS {0};",TABLE_TEMPLATES);
			db.prepareStatement(sql).execute();
		} catch (SQLException e) {
			throw failedToDropObject(t(TABLE_WITH_NAME,NAME,TABLE_TEMPLATES)).causedBy(e);
		}
	}

	@Override
	public Long getCustomerPrice(long company, String customer, String itemCode) throws UmbrellaException {
		try {
			var rs = select(PRICE).from(TABLE_PRICES).where(COMPANY_ID,equal(company)).where(CUSTOMER_NUMBER,equal(customer)).where(ITEM_CODE,equal(itemCode)).exec(db);
			Long price = null;
			if (rs.next()) price = rs.getLong(PRICE);
			rs.close();
			if (price != null) return price;
			throw notFound(FAILED_TO_LOAD_CUSTOMER_PRICE,COMPANY,company, CUSTOMER,customer,t(Text.ITEM),itemCode);
		} catch (SQLException e) {
			throw databaseException(FAILED_TO_LOAD_CUSTOMER_PRICE,COMPANY,company, CUSTOMER,customer,t(Text.ITEM),itemCode).causedBy(e);
		}
	}

	@Override
	public CustomerSettings getCustomerSettings(long companyId, Type docType, String customerId) throws UmbrellaException {
		try {
			var rs = select(ALL).from(TABLE_CUSTOMER_SETTINGS).where(COMPANY_ID,equal(companyId)).where(DOC_TYPE_ID,equal(docType.id())).where(CUSTOMER_NUMBER,equal(customerId)).exec(db);
			CustomerSettings settings = null;
			if (rs.next()) settings = CustomerSettings.of(rs);
			rs.close();
			return settings;
		} catch (SQLException e) {
			throw databaseException(FAILED_TO_LOAD_CUSTOMER_SETTINGS,COMPANY,companyId,TYPE, docType.name()).causedBy(e);
		}
	}

	@Override
	public Type getType(int typeId) throws UmbrellaException {
		try {
			var rs = select(ALL).from(TABLE_DOCUMENT_TYPES).where(ID,equal(typeId)).exec(db);
			Type type = null;
			if (rs.next()) type = toType(rs);
			rs.close();
			if (type != null) return type;
			throw notFound(FAILED_TO_LOAD_OBJECT_BY_ID, OBJECT, Text.TYPE, ID,typeId);
		} catch (SQLException e) {
			throw databaseException(FAILED_TO_LOAD_OBJECT_BY_ID, OBJECT, Text.TYPE, ID,typeId).causedBy(e);
		}

	}
	
	@Override
	public Map<Long, Map<Long, String>> docReferencedByTimes(Set<Long> timeIds) throws UmbrellaException {
		try {
			var map = new HashMap<Long, Map<Long, String>>(); // Map ( timeId → Map ( docId → name ))
			var rs = select(TIME_ID,DOCUMENT_ID, NUMBER).from(TABLE_POSITIONS).leftJoin(DOCUMENT_ID,TABLE_DOCUMENTS, ID).where(TIME_ID,in(timeIds.toArray())).exec(db);
			while (rs.next()) {
				var timeId = rs.getLong(TIME_ID);
				var docId  = rs.getLong(DOCUMENT_ID);
				var number = rs.getString(NUMBER);
				map.computeIfAbsent(timeId,k -> new HashMap<>()).put(docId,number);
			}
			rs.close();
			return map;
		} catch (SQLException e) {
			throw databaseException(FAILED_TO_LIST_ENTITIES,TYPE, t(Text.DOCUMENTS)).causedBy(e);
		}
	}

	@Override
	public Map<Long, Document> find(Collection<Long> companyIds, List<String> keys, boolean fulltext) {
		try {
			var rs = Query.select(ALL).from(TABLE_DOCUMENT_TYPES).exec(db);
			var types = new HashMap<Integer,Type>();
			while (rs.next()) types.put(rs.getInt(ID),toType(rs));
			rs.close();

			var query = Query.select(ALL).from(TABLE_DOCUMENTS).where(COMPANY_ID,in(companyIds.toArray()));
			if (fulltext) {
				for (var key : keys) query.where(format("CONCAT({0},\" \",{1},\" \",{2},\" \",{3},\" \",{4},\" \",{5},\" \",{6},\" \",{7},\" \",{8},\" \",{9})", NUMBER,HEAD,FOOTER, SENDER,TAX_NUMBER,BANK_ACCOUNT, CUSTOMER,CUSTOMER_NUMBER,CUSTOMER_TAX_NUMBER,CUSTOMER_EMAIL),like("%"+key+"%"));
			} else {
				for (var key : keys) query.where(format("CONCAT({0},\" \",{1},\" \",{2},\" \",{3},\" \",{4})", NUMBER,HEAD,FOOTER, SENDER, CUSTOMER),like("%"+key+"%"));
			}
			rs = query.exec(db);
			var map = new HashMap<Long,Document>();
			while (rs.next()) map.put(rs.getLong(ID),toDoc(rs,types));
			rs.close();

			if (fulltext){
				var additionalDocIds = new HashSet<Long>();
				query = select(DOCUMENT_ID).from(TABLE_POSITIONS).leftJoin(DOCUMENT_ID,TABLE_DOCUMENTS, ID).where(COMPANY_ID,in(companyIds.toArray()));
				for (var key : keys) query.where(format("CONCAT({0},\" \",{1},\" \",{2},\" \",{3})",ITEM_CODE,UNIT,TITLE, DESCRIPTION),like("%"+key+"%"));
				rs = query.exec(db);
				while (rs.next()) additionalDocIds.add(rs.getLong(DOCUMENT_ID));
				rs.close();

				additionalDocIds.removeAll(map.keySet());

				query = select(ALL).from(TABLE_DOCUMENTS).where(ID,in(additionalDocIds.toArray()));
				rs = query.exec(db);
				while (rs.next()) map.put(rs.getLong(ID),toDoc(rs,types));
				rs.close();
			}

			rs = Query.select(ALL).from(TABLE_POSITIONS).where(DOCUMENT_ID,in(map.keySet().toArray())).exec(db);
			while (rs.next()){
				var docId = rs.getLong(DOCUMENT_ID);
				var position = toPosition(rs);
				map.get(docId).positions().add(position);
				position.clean();
			}
			rs.close();
			return map;
		} catch (SQLException e) {
			throw failedToSearchDb(t(Text.DOCUMENTS)).causedBy(e);
		}
	}

	@Override
	public Map<Long, Document> listDocs(long companyId) throws UmbrellaException {
		try {
			var rs = Query.select(ALL).from(TABLE_DOCUMENT_TYPES).exec(db);
			var types = new HashMap<Integer,Type>();
			while (rs.next()) types.put(rs.getInt(ID),toType(rs));
			rs.close();

			rs = Query.select(ALL).from(TABLE_DOCUMENTS).where(COMPANY_ID,equal(companyId)).exec(db);
			var map = new HashMap<Long,Document>();
			while (rs.next()) map.put(rs.getLong(ID),toDoc(rs,types));
			rs.close();

			rs = Query.select(ALL).from(TABLE_POSITIONS).where(DOCUMENT_ID,in(map.keySet().toArray())).exec(db);
			while (rs.next()){
				var docId = rs.getLong(DOCUMENT_ID);
				var position = toPosition(rs);
				map.get(docId).positions().add(position);
				position.clean();
			}
			rs.close();
			return map;
		} catch (SQLException e) {
			throw databaseException(FAILED_TO_LIST_ENTITIES,TYPE,t(Text.DOCUMENTS)).causedBy(e);
		}
	}

	@Override
	public HashMap<Integer, Type> listTypes() throws UmbrellaException {
		try {
			var rs = Query.select(ALL).from(TABLE_DOCUMENT_TYPES).exec(db);
			var types = new HashMap<Integer,Type>();
			while (rs.next()) types.put(rs.getInt(ID),toType(rs));
			rs.close();
			return types;
		} catch (SQLException e) {
			throw databaseException(FAILED_TO_LIST_ENTITIES,TYPE,"document types").causedBy(e);
		}
	}

	@Override
	public Document loadDoc(long docId) throws UmbrellaException {
		try {
			var rs = Query.select(ALL).from(TABLE_DOCUMENT_TYPES).exec(db);
			var types = new HashMap<Integer,Type>();
			while (rs.next()) types.put(rs.getInt(ID),toType(rs));
			rs.close();

			rs = Query.select(ALL).from(TABLE_DOCUMENTS).where(TABLE_DOCUMENTS+"."+ ID,equal(docId)).exec(db);
			Document doc = null;
			while (rs.next()) doc = toDoc(rs,types);
			rs.close();

			if (doc != null) {
				var positions = doc.positions();
				rs = Query.select(ALL).from(TABLE_POSITIONS).where(DOCUMENT_ID, equal(docId)).exec(db);
				while (rs.next()) {
					var position = toPosition(rs);
					positions.add(position);
					position.clean();
				}
				rs.close();
				return doc;
			}
			throw notFound(FAILED_TO_LOAD_OBJECT_BY_ID, OBJECT,t(Text.DOCUMENT), ID,docId);
		} catch (SQLException e) {
			throw failedToLoadObject(t(Text.DOCUMENT),docId).causedBy(e);
		}

	}

	private void moveTemplateNames() {
		try {
			var sql = format("UPDATE {0} SET template = (SELECT name FROM templates WHERE templates.id = documents.template_id);",TABLE_DOCUMENTS);
			db.prepareStatement(sql).execute();
		} catch (SQLException e) {
			throw databaseException(FAILED_TO_MOVE,"old","template.names","new","document.templates").causedBy(e);
		}
	}

	@Override
	public String nextDocId(String language, long companyId, Type type) {
		try {
			var rs = select(NUMBER).from(TABLE_DOCUMENTS).where(COMPANY_ID,equal(companyId)).where(TYPE_ID,equal(type.id())).sort(ID +" DESC").limit(1).exec(db);
			String lastId = null;
			if (rs.next()) lastId = rs.getString(1);
			rs.close();
			if (lastId == null) return translator().translate(language,type.name())+"-0001";
			var numeric = NUMBER_PATTERN.matcher(lastId);
			if (numeric.find()){
				var prefix = numeric.group(1);
				var digits = numeric.group(2);
				var suffix = numeric.group(3);
				var len = digits.length();
				while (digits.startsWith("0")) digits = digits.substring(1);
				var lid = Long.parseLong(digits)+1;
				digits = ""+lid;
				while (digits.length()<len) digits = "0"+digits;
				return prefix+digits+suffix;
			}
			return lastId;
		} catch (SQLException e) {
			throw databaseException(FAILED_TO_READ_LAST_DOCID).causedBy(e);
		}
	}

	@Override
	public CustomerSettings save(CustomerSettings settings, Document doc) throws UmbrellaException {
		var companyId = doc.companyId();
		var typeId = doc.type().id();
		var customerId = doc.customer().id();
		try{
		replaceInto(TABLE_CUSTOMER_SETTINGS, COMPANY_ID, DOC_TYPE_ID, CUSTOMER_NUMBER, DEFAULT_HEADER, DEFAULT_FOOTER, DEFAULT_MAIL)
				.values(companyId, typeId, customerId, settings.header(), settings.footer(), settings.mailText())
				.execute(db);
			return settings;
		} catch (SQLException e){
			throw databaseException(FAILED_TO_UPDATE_OBJECT, OBJECT,t(CUSTOMER_SETTINGS)).causedBy(e);
		}
	}

	@Override
	public Document save(Document doc) throws UmbrellaException {
		if (doc.isNew()) try {
			var timestamp = doc.date().atStartOfDay(UTC).toInstant().getEpochSecond();
			var sender = doc.sender();
			var custom = doc.customer();
			var stmt = insertInto(TABLE_DOCUMENTS,TYPE_ID,COMPANY_ID, DATE, DELIVERY_DATE,FOOTER,HEAD, NUMBER, STATE, SENDER,TAX_NUMBER,BANK_ACCOUNT,COURT, CUSTOMER,CUSTOMER_EMAIL,CUSTOMER_NUMBER,CUSTOMER_TAX_NUMBER,TEMPLATE,CURRENCY)
					.values(doc.type().id(),doc.companyId(),timestamp,doc.delivery(),doc.footer(),doc.head(),doc.number(),doc.state().code(),sender.name(),sender.taxNumber(),sender.bankAccount(),sender.court(),custom.name(),custom.email(),custom.id(),custom.taxNumber(),doc.template(), doc.currency())
				.execute(db);
			var rs = stmt.getGeneratedKeys();
			Long newId = null;
			if (rs.next()) newId = rs.getLong(1);
			rs.close();
			stmt.close();

			sender.clean();
			custom.clean();
			doc.clean();
			if (newId == null) throw failedToStoreObject(doc);
			return loadDoc(newId);
		} catch (Exception e) {
			throw databaseException(FAILED_TO_UPDATE_OBJECT, OBJECT, t(DOCUMENT_WITH_ID,ID,doc.number())).causedBy(e);
		}

		if (doc.isDirty()) try {
			var timestamp = doc.date().atStartOfDay(UTC).toInstant().getEpochSecond();
			var sender = doc.sender();
			var custom = doc.customer();
			update(TABLE_DOCUMENTS)
					.set(DATE, DELIVERY_DATE,FOOTER,HEAD, NUMBER, STATE, SENDER,TAX_NUMBER,BANK_ACCOUNT,COURT, CUSTOMER,CUSTOMER_EMAIL,CUSTOMER_NUMBER,CUSTOMER_TAX_NUMBER,TEMPLATE)
					.where(ID,equal(doc.id()))
					.prepare(db)
					.apply(timestamp,doc.delivery(),doc.footer(),doc.head(),doc.number(),doc.state().code(),sender.name(),sender.taxNumber(),sender.bankAccount(),sender.court(),custom.name(),custom.email(),custom.id(),custom.taxNumber(),doc.template())
					.close();
			sender.clean();
			custom.clean();
			doc.clean();
		} catch (Exception e) {
			throw databaseException(FAILED_TO_UPDATE_OBJECT,doc).causedBy(e);
		}

		if (doc.positions().isDirty()) try {
			for (var entry : doc.positions().entrySet()){
				var pos = entry.getValue();
				if (pos.docId() != doc.id()) pos.setDocId(doc.id());
				if (pos.isNew()){
					insertInto(TABLE_POSITIONS,DOCUMENT_ID,POS, ITEM_CODE,AMOUNT, DESCRIPTION, TITLE,UNIT,PRICE,TAX,TIME_ID, OPTIONAL)
							.values(pos.docId(),pos.num(),pos.itemCode(),pos.amount(),pos.description(),pos.title(),pos.unit(),pos.unitPrice(),pos.tax(),pos.timeId(),pos.optional())
							.execute(db)
							.close();
					pos.clean();
				}
				if (pos.isDirty()){
					update(TABLE_POSITIONS)
							.set(ITEM_CODE,AMOUNT, DESCRIPTION, TITLE,UNIT,PRICE,TAX,TIME_ID, OPTIONAL)
							.where(DOCUMENT_ID,equal(doc.id()))
							.where(POS,equal(pos.num())).prepare(db)
							.apply(pos.itemCode(),pos.amount(),pos.description(),pos.title(),pos.unit(),pos.unitPrice(),pos.tax(),pos.timeId(),pos.optional())
							.close();
					if (pos.isDirty(UNIT_PRICE)) saveCustomerPrice(doc.companyId(),doc.customer().id(),pos.itemCode(),pos.unitPrice());
					pos.clean();

				}
			}
		} catch (Exception e) {
			throw failedToStoreObject(t("positions of document")).causedBy(e);
		}
		return doc;
	}

	@Override
	public CustomerSettings save(long companyId, Type docType, String customerId, CustomerSettings settings) throws UmbrellaException {
		if (!settings.isDirty()) return settings;
		try {
			replaceInto(TABLE_CUSTOMER_SETTINGS,COMPANY_ID,DOC_TYPE_ID,CUSTOMER_NUMBER,DEFAULT_HEADER,DEFAULT_FOOTER,DEFAULT_MAIL)
					.values(companyId,docType.id(),customerId,settings.header(),settings.footer(),settings.mailText())
					.execute(db)
					.close();
			return settings;
		} catch (SQLException e){
			throw databaseException(FAILED_TO_UPDATE_OBJECT, OBJECT, t(CUSTOMER_SETTINGS)).causedBy(e);
		}
	}

	private void saveCustomerPrice(long company, String customer, String item, long price) {
		try {
			var rs = select(PRICE).from(TABLE_PRICES).where(COMPANY_ID,equal(company)).where(CUSTOMER_NUMBER,equal(customer)).where(ITEM_CODE,equal(item)).exec(db);
			Long oldPrice = null;
			if (rs.next()) oldPrice = rs.getLong(PRICE);
			rs.close();

			if (oldPrice == null) {
				insertInto(TABLE_PRICES, COMPANY_ID, CUSTOMER_NUMBER, ITEM_CODE, PRICE).values(company, customer, item, price).execute(db).close();
			} else {
				update(TABLE_PRICES).where(COMPANY_ID,equal(company)).where(CUSTOMER_NUMBER,equal(customer)).where(ITEM_CODE,equal(item))
						.set(PRICE).prepare(db).apply(price).close();
			}
		} catch (SQLException e) {
			throw failedToStoreObject(t("customer price")).causedBy(e);
		}
	}

	@Override
	public Pair<Integer> switchPositions(long docId, Pair<Integer> pair) throws UmbrellaException {
		try {
			db.setAutoCommit(false);
			update(TABLE_POSITIONS).set(POS).where(DOCUMENT_ID,equal(docId)).where(POS,equal(pair.left())).prepare(db).apply(-pair.right()).close();
			update(TABLE_POSITIONS).set(POS).where(DOCUMENT_ID,equal(docId)).where(POS,equal(pair.right())).prepare(db).apply(pair.left()).close();
			update(TABLE_POSITIONS).set(POS).where(DOCUMENT_ID,equal(docId)).where(POS,equal(-pair.right())).prepare(db).apply(pair.right()).close();
			db.setAutoCommit(true);
		} catch (SQLException e) {
			throw databaseException(FAILED_TO_SWITCH_POSITIONS,"a",pair.left(),"b",pair.right(),docId).causedBy(e);
		}
		return pair;
	}

	private Document toDoc(ResultSet rs, HashMap<Integer, Type> types) throws SQLException {
		var id = rs.getLong(ID);
		var typeId = rs.getInt(TYPE_ID);
		var type = types.get(typeId);
		var company = rs.getLong(COMPANY_ID);
		var number = rs.getString(NUMBER);
		var timestamp = rs.getLong(DATE);
		var date = timestamp > 0 ? Instant.ofEpochSecond(timestamp).atOffset(UTC).toLocalDate() : null;
		var state = rs.getInt(STATE);

		var delivery = rs.getString(DELIVERY_DATE);
		var head = rs.getString(HEAD);
		var footer = rs.getString(FOOTER);
		var currency = rs.getString(CURRENCY);
		var senderName = rs.getString(SENDER);
		var taxNumber = rs.getString(TAX_NUMBER);
		var bankAccount = rs.getString(BANK_ACCOUNT);
		var court = rs.getString(COURT);
		var customerName = rs.getString(CUSTOMER);
		var customerId = rs.getString(CUSTOMER_NUMBER);
		var customerTaxNumber = rs.getString(CUSTOMER_TAX_NUMBER);
		var customerEmail = rs.getString(CUSTOMER_EMAIL);
		var customer = new Customer(customerId, customerName, customerEmail, customerTaxNumber, FALLBACK_LANG);
		var sender = new Sender(senderName,bankAccount,taxNumber,court);
		var template = rs.getString(TEMPLATE);
		return new Document(id,company,number,type,date, Document.State.of(state).orElse(State.ERROR),template,delivery,head,footer,currency, DEFAULT_THOUSANDS_SEPARATOR,sender,customer,new PositionList());
	}

	private Position toPosition(ResultSet rs) throws SQLException {
		var num = rs.getInt(POS);
		var itemCode = rs.getString(ITEM_CODE);
		var amount = rs.getDouble(AMOUNT);
		var unit = rs.getString(UNIT);
		var title = rs.getString(TITLE);
		var description = rs.getString(DESCRIPTION);
		var unitPrice = rs.getLong(PRICE);
		var tax = rs.getInt(TAX);
		var timeId = rs.getLong(TIME_ID);
		var optional = rs.getBoolean(OPTIONAL);
		return new Position(num,itemCode,amount,unit,title,description,unitPrice,tax,timeId,optional);
	}

	private Type toType(ResultSet rs) throws SQLException {
		return new Type(rs.getInt(ID),rs.getInt(NEXT_TYPE),rs.getString(NAME));
	}
}
