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

import static de.srsoftware.tools.jdbc.Condition.equal;
import static de.srsoftware.tools.jdbc.Condition.like;
import static de.srsoftware.tools.jdbc.Query.*;
import static de.srsoftware.tools.jdbc.Query.Dialect.SQLITE;
import static de.srsoftware.tools.jdbc.Query.SelectQuery.ALL;
import static de.srsoftware.umbrella.company.Constants.*;
import static de.srsoftware.umbrella.core.Errors.*;
import static de.srsoftware.umbrella.core.constants.Field.*;
import static de.srsoftware.umbrella.core.constants.Field.COMPANY;
import static de.srsoftware.umbrella.core.constants.Field.NUMBER;
import static de.srsoftware.umbrella.core.constants.Field.TYPE;
import static de.srsoftware.umbrella.core.constants.Text.*;
import static de.srsoftware.umbrella.core.exceptions.UmbrellaException.*;
import static de.srsoftware.umbrella.core.model.Translatable.t;
import static java.text.MessageFormat.format;

import de.srsoftware.umbrella.company.api.CompanyDb;
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.Company;
import de.srsoftware.umbrella.core.model.UmbrellaUser;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.*;
import java.util.regex.Pattern;
import org.json.JSONObject;

public class SqliteDb extends BaseDb implements CompanyDb {

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

	protected int createTables() {
		int currentVersion = createSettingsTable();
		switch (currentVersion){
			case 0:
				createCompanyTable();
				createCompaniesUsersTable();
		}

		return setCurrentVersion(1);
	}

	private void createCompanyTable() {
		var sql = """
CREATE TABLE IF NOT EXISTS "companies" (
 	id INTEGER PRIMARY KEY,
 	name VARCHAR(255) NOT NULL,
 	address TEXT,
 	email VARCHAR(255),
 	phone VARCHAR(255),
 	bank_account TEXT,
 	court TEXT,
 	currency VARCHAR(10) DEFAULT "€",
 	logo TEXT,
 	tax_number VARCHAR(255),
 	decimals INT NOT NULL DEFAULT "2",
 	decimal_separator VARCHAR(10) DEFAULT ",",
 	thousands_separator VARCHAR(10) DEFAULT ".",
 	last_customer_number INT DEFAULT NULL,
 	customer_number_prefix VARCHAR(255))""";
		try {
			var stmt = db.prepareStatement(sql);
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw failedToCreateTable(TABLE_COMPANIES).causedBy(e);
		}
	}

	private void createCompaniesUsersTable() {
		var sql = "CREATE TABLE IF NOT EXISTS companies_users (company_id INT NOT NULL, user_id INT NOT NULL)";
		try {
			var stmt = db.prepareStatement(sql);
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw failedToCreateTable(TABLE_COMPANIES_USERS).causedBy(e);
		}
	}

	@Override
	public void addUser(long company_id, long user_id) {
		try {
			insertInto(TABLE_COMPANIES_USERS,COMPANY_ID, USER_ID)
					.values(company_id, user_id)
					.ignoreDuplicates(SQLITE)
					.execute(db)
					.close();
		} catch (SQLException e) {
			throw databaseException(FAILED_TO_ASSIGN_A_TO_B,"a", t(USER_WITH_ID, ID,user_id),"b", t(COMPANY_WITH_ID, ID,company_id)).causedBy(e);
		}
	}

	@Override
	public long drop(long companyId) {
		try {
			delete().from(TABLE_COMPANIES_USERS).where(COMPANY_ID,equal(companyId)).execute(db);
			delete().from(TABLE_COMPANIES).where(ID,equal(companyId)).execute(db);
			return companyId;
		} catch (SQLException e) {
			throw failedToDropObject("company "+ companyId).causedBy(e);
		}
	}

	@Override
	public void dropUser(long companyId, long userId) {
		try {
			delete().from(TABLE_COMPANIES_USERS).where(COMPANY_ID,equal(companyId)).where(USER_ID,equal(userId)).execute(db);
		} catch (SQLException e) {
			throw failedToDropObjectFromObject("user",userId,"company",companyId).causedBy(e);
		}
	}

	@Override
	public Collection<Long> getMembers(long companyId) throws UmbrellaException {
		try {
			var rs  = select(ALL).from(TABLE_COMPANIES_USERS).where(COMPANY_ID, equal(companyId)).exec(db);
			var ids = new HashSet<Long>();
			while (rs.next()) ids.add(rs.getLong(USER_ID));
			rs.close();
			return ids;
		} catch (SQLException e) {
			throw failedToLoadMembers(t("company {company}", COMPANY,companyId)).causedBy(e);
		}
	}

	@Override
	public String getNextCustomerNumber(long companyId) {
		try {
			var rs = select(LAST_CUSTOMER_NUMBER, CUSTOMER_NUMBER_PREFIX).from(TABLE_COMPANIES).where(ID,equal(companyId)).exec(db);
			var last = 0L;
			String prefix = null;
			if (rs.next()){
				last   = rs.getLong(LAST_CUSTOMER_NUMBER);
				prefix = rs.getString(CUSTOMER_NUMBER_PREFIX);
			}
			rs.close();
			var next = last+1;
			return prefix+next; // TODO: currently not taking growing number lengths into account, this should be resolved!
		} catch (SQLException e) {
			throw failedToLoadObject(t("customer number settings for {company_id}",COMPANY_ID,companyId)).causedBy(e);
		}
	}

	@Override
	public HashMap<Long, Company> find(UmbrellaUser user, Collection<String> keys) {
		try {
			var query = select(DISTINCT).from(TABLE_COMPANIES).leftJoin(ID,TABLE_COMPANIES_USERS,COMPANY_ID)
					.where(USER_ID,equal(user.id()));
			for (var key : keys){
				query.where(format("CONCAT({0},\" \",{1},\" \",{2},\" \",{3},\" \",{4})", NAME, ADDRESS, EMAIL,PHONE,BANK_ACCOUNT),like("%"+key+"%"));
			}
			var rs = query.exec(db);
			var companies = new HashMap<Long,Company>();
			while (rs.next()) {
				var company = Company.of(rs);
				companies.put(company.id(),company);
			}
			rs.close();
			return companies;
		} catch (SQLException e){
			throw failedToSearchDb(t(Text.COMPANY)).causedBy(e);
			//throw databaseException(FAILED_TO_SEARCH_DB, ModuleRegistry.translator().translate(user.language(),COMPANY)).causedBy(e);
		}
	}

	@Override
	public Map<Long,Company> listCompaniesOf(long userId) throws UmbrellaException {
		try {
			var rs = select(ALL).from(TABLE_COMPANIES).leftJoin(ID,TABLE_COMPANIES_USERS,COMPANY_ID).where(USER_ID,equal(userId)).exec(db);
			var companies = new HashMap<Long,Company>();
			while (rs.next()) {
				var company = Company.of(rs);
				companies.put(company.id(),company);
			}
			rs.close();
			return companies;
		} catch (SQLException e) {
			throw databaseException(FAILED_TO_LOAD_ENTITIES_OF_OWNER,TYPE,COMPANIES, OWNER, t(USER_WITH_ID, ID,userId));
		}
	}

	@Override
	public Company load(long companyId) throws UmbrellaException {
		try {
			var rs = select(ALL).from(TABLE_COMPANIES).where(ID, equal(companyId)).exec(db);
			Company company = null;
			if (rs.next()) company = Company.of(rs);
			rs.close();
			if (company == null) throw notFound(FAILED_TO_LOAD_OBJECT_BY_ID, OBJECT, t(Text.COMPANY), ID,companyId);
			return company;
		} catch (SQLException e){
			throw failedToLoadObject(t(Text.COMPANY),companyId).causedBy(e);
		}
	}

	@Override
	public Company save(Company company) {
		try {
			if (company.id() == 0){  // new
				long id = 0;
				var rs = insertInto(TABLE_COMPANIES, NAME, ADDRESS, EMAIL, PHONE, BANK_ACCOUNT, COURT, CURRENCY, TAX_NUMBER, DECIMALS, DECIMAL_SEPARATOR, THOUSANDS_SEPARATOR, LAST_CUSTOMER_NUMBER, CUSTOMER_NUMBER_PREFIX)
						.values(company.name(),company.address(),company.email(),company.phone(),company.bankAccount(),company.court(),company.currency(),company.taxId(),company.decimals(),company.decimalSeparator(),company.thousandsSeparator(),0,company.customerNumberPrefix())
						.execute(db)
						.getGeneratedKeys();
				if (rs.next()) id = rs.getLong(1);
				rs.close();
				if (id != 0){
					var json = new JSONObject(company.toMap());
					json.put(ID,id);
					return Company.of(json);
				}
				return company;
			} else { // update
				if (company.isDirty()) {
					update(TABLE_COMPANIES)
							.set(NAME, ADDRESS, EMAIL, PHONE, BANK_ACCOUNT, COURT, CURRENCY, TAX_NUMBER, DECIMALS, DECIMAL_SEPARATOR, THOUSANDS_SEPARATOR, LAST_CUSTOMER_NUMBER, CUSTOMER_NUMBER_PREFIX)
							.where(ID,equal(company.id())).prepare(db)
							.apply(company.name(), company.address(), company.email(), company.phone(), company.bankAccount(), company.court(), company.currency(), company.taxId(), company.decimals(), company.decimalSeparator(), company.thousandsSeparator(), company.lastCustomerNumber(), company.customerNumberPrefix())
							.close();
				}
				return company;
			}
		} catch (SQLException e){
			throw failedToStoreObject(company.name()).causedBy(e);
		}
	}

	@Override
	public void saveNewCustomer(long companyId, String id) {
		var p = Pattern.compile("(?s)(.*?)(\\d+)$");
		var m = p.matcher(id);
		if (!m.matches()) throw unprocessable("{number} is not a valid customer id: it does not end with a number!", NUMBER,id);
		String prefix = m.group(1);  // Prefix before last number
		long number = Long.parseLong(m.group(2));  // The last numeric part
		try {
			update(TABLE_COMPANIES).set(LAST_CUSTOMER_NUMBER, CUSTOMER_NUMBER_PREFIX).where(ID,equal(companyId)).prepare(db).apply(number,prefix).close();
		} catch (SQLException e) {
			throw failedToStoreObject(LAST_CUSTOMER_NUMBER).causedBy(e);
		}
	}
}
