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

import static de.srsoftware.tools.NotImplemented.notImplemented;
import static de.srsoftware.tools.Optionals.nullable;
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.accounting.Constants.*;
import static de.srsoftware.umbrella.core.ModuleRegistry.userService;
import static de.srsoftware.umbrella.core.constants.Field.*;
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.tools.jdbc.Condition;
import de.srsoftware.tools.jdbc.Query;
import de.srsoftware.umbrella.core.BaseDb;
import de.srsoftware.umbrella.core.constants.Field;
import de.srsoftware.umbrella.core.constants.Text;
import de.srsoftware.umbrella.core.model.Account;
import de.srsoftware.umbrella.core.model.Transaction;
import de.srsoftware.umbrella.core.model.UmbrellaUser;
import java.sql.Connection;
import java.sql.SQLException;
import java.time.ZoneOffset;
import java.util.*;

public class SqliteDb extends BaseDb implements AccountDb {
	public SqliteDb(Connection connection) {
		super(connection);
	}

	@Override
	protected int createTables() {
		var version = createSettingsTable();
		switch (version){
			case 0:
				createAccountsTable();
				createTransactionsTable();
				createTagsTable();
				createTagsTransactionsTable();
		}
		return setCurrentVersion(1);
	}

	private void createAccountsTable() {
		var sql = """
			CREATE TABLE IF NOT EXISTS {0} (
				{1} INTEGER PRIMARY KEY,
				{2} VARCHAR(255) NOT NULL,
				{3} LONG NOT NULL,
				{4} CURRENCY VARCHAR(16)
			);""";

		try {
			sql = format(sql,TABLE_ACCOUNTS, ID, Field.NAME, Field.OWNER, Field.CURRENCY);
			var stmt = db.prepareStatement(sql);
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw failedToCreateTable(TABLE_ACCOUNTS).causedBy(e);
		}
	}

	private void createTagsTable(){
		var sql = """
				CREATE TABLE IF NOT EXISTS {0} (
					{1} INTEGER PRIMARY KEY,
					{2} VARCHAR(32) UNIQUE NOT NULL
				);
				""";
		try {
			sql = format(sql,TABLE_TAGS, ID,Field.TAG);
			var stmt = db.prepareStatement(sql);
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw failedToCreateTable(TABLE_TAGS).causedBy(e);
		}
	}

	private void createTagsTransactionsTable(){
		var sql = """
				CREATE TABLE IF NOT EXISTS {0} (
					{1} LONG NOT NULL,
					{2} LONG NOT NULL,
					PRIMARY KEY({1}, {2})
				);
				""";
		try {
			sql = format(sql,TABLE_TAGS_TRANSACTIONS,Field.TRANSACTION_ID,Field.TAG_ID);
			var stmt = db.prepareStatement(sql);
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw failedToCreateTable(TABLE_TAGS).causedBy(e);
		}
	}

	private void createTransactionsTable() {
		var sql = """
				CREATE TABLE IF NOT EXISTS {0} (
				    {7} INTEGER PRIMARY KEY,
					{1} LONG NOT NULL,
					{2} LONG NOT NULL,
					{3} VARCHAR(255) NOT NULL,
					{4} VARCHAR(255) NOT NULL,
					{5} DOUBLE NOT NULL,
					{6} TEXT
				);""";
		try {
			sql = format(sql,TABLE_TRANSACTIONS,Field.ACCOUNT,Field.TIMESTAMP,Field.SOURCE,Field.DESTINATION, Field.AMOUNT,Field.DESCRIPTION, ID);
			var stmt = db.prepareStatement(sql);
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw failedToCreateTable(TABLE_TRANSACTIONS).causedBy(e);
		}
	}

	public Transaction dropTransaction(Transaction transaction){
		try {
			db.setAutoCommit(false);
			Query.delete().from(TABLE_TAGS_TRANSACTIONS).where(TRANSACTION_ID,equal(transaction.id())).execute(db);
			Query.delete().from(TABLE_TRANSACTIONS).where(ID,equal(transaction.id())).execute(db);
			db.setAutoCommit(true);
			return transaction;
		} catch (SQLException e){
			try {
				db.rollback();
			} catch (SQLException ignored){};
			throw failedToDropObject(transaction);
		}
	}

	@Override
	public void dropTransactionTag(long transactionId, String tag) {
		try {
			var tagIds = new HashSet<Long>();
			var rs = select(ID).from(TABLE_TAGS).where(TAG, equal(tag)).exec(db);
			while (rs.next()) tagIds.add(rs.getLong(1));
			rs.close();

			delete().from(TABLE_TAGS_TRANSACTIONS).where(TRANSACTION_ID, equal(transactionId)).where(TAG_ID, in(tagIds.toArray())).execute(db);
		} catch (SQLException e) {
			throw failedToDropObject(tag);
		}
	}

	@Override
	public Collection<UmbrellaUser> getMembers(long accountId) {
		try {
			var userIds = new HashSet<Long>();
			var rs = select("DISTINCT "+ SOURCE).from(TABLE_TRANSACTIONS).where(ACCOUNT,equal(accountId)).exec(db);
			while (rs.next()) try {
				userIds.add(Long.parseLong(rs.getString(1)));
			} catch (NumberFormatException ignored) {}
			rs.close();
			rs = select("DISTINCT "+ DESTINATION).from(TABLE_TRANSACTIONS).where(ACCOUNT,equal(accountId)).exec(db);
			while (rs.next()) try {
				userIds.add(Long.parseLong(rs.getString(1)));
			} catch (NumberFormatException ignored) {}
			rs.close();
			var us = userService();
			return userIds.stream().map(us::loadUser).toList();
		} catch (SQLException e) {
			throw failedToLoadMembers(Text.ACCOUNT);
		}
	}

	@Override
	public Optional<Transaction> lastTransaction(long accountId, String source, String destination, Double amount) {
		try {
			var query = select(ALL).from(TABLE_TRANSACTIONS).where(ACCOUNT,equal(accountId));
			if (source != null)      query = query.where(SOURCE,equal(source));
			if (destination != null) query = query.where(DESTINATION,equal(destination));
			if (amount != null)      query = query.where(AMOUNT,equal(amount));
			var rs = query.sort(ID+" DESC").limit(1).exec(db);
			Transaction ta = null;
			if (rs.next()) ta = Transaction.of(rs);
			rs.close();

			if (ta == null && amount != null) { // try to search by amount, ignore source and dest
				rs = select(ALL).from(TABLE_TRANSACTIONS).where(ACCOUNT, equal(accountId)).where(AMOUNT, equal(amount))
						.sort(ID + " DESC").limit(1).exec(db);
				if (rs.next()) ta = Transaction.of(rs);
				rs.close();
			}

			if (ta == null && source != null && destination != null) { // try to search by amount, ignore source and dest
				rs = select(ALL).from(TABLE_TRANSACTIONS)
						.where(SOURCE,equal(source))
						.where(DESTINATION,equal(destination))
						.where(ACCOUNT, equal(accountId))
						.sort(ID + " DESC").limit(1).exec(db);
				if (rs.next()) ta = Transaction.of(rs);
				rs.close();
			}


			if (ta != null){
				var tags = ta.tags();
				rs = select(TAG).from(TABLE_TAGS_TRANSACTIONS).leftJoin(TAG_ID,TABLE_TAGS,ID).where(TRANSACTION_ID,equal(ta.id())).exec(db);
				while (rs.next()) tags.add(rs.getString(1));
				rs.close();
			}

			return nullable(ta);
		} catch (SQLException e) {
			throw failedToSearchDb(t(Text.ACCOUNTING));
		}
	}

	@Override
	public HashSet<Account> listAccounts(long userId) {
		try {
			var accountIds = new HashSet<Long>();
			var rs = select("DISTINCT " + Field.ACCOUNT).from(TABLE_TRANSACTIONS).where(Field.SOURCE, equal(userId)).exec(db);
			while (rs.next()) accountIds.add(rs.getLong(1));
			rs.close();
			rs = select("DISTINCT " + Field.ACCOUNT).from(TABLE_TRANSACTIONS).where(Field.DESTINATION, equal(userId)).exec(db);
			while (rs.next()) accountIds.add(rs.getLong(1));
			rs.close();
			var accounts = new HashSet<Account>();
			rs = select(ALL).from(TABLE_ACCOUNTS).where(ID, Condition.in(accountIds.toArray())).exec(db);
			while (rs.next()) accounts.add(Account.of(rs));
			rs.close();
			return accounts;
		} catch (SQLException e){
			throw failedToLoadObject(Text.ACCOUNTING).causedBy(e);
		}
	}

	@Override
	public Collection<String> listTags(long accountId, String source, String destination) {
		try {
			var rs = select(TRANSACTION_ID,Field.TAG)
					.from(TABLE_TRANSACTIONS)
					.leftJoin(ID,TABLE_TAGS_TRANSACTIONS, TRANSACTION_ID)
					.leftJoin(TAG_ID,TABLE_TAGS,ID)
					.where(ACCOUNT,equal(accountId))
					.where(SOURCE,equal(source))
					.where(DESTINATION,equal(destination))
					.sort(TRANSACTION_ID).exec(db);
			Set<String> set = null;
			Set<String> transactionTags = new TreeSet<String>(String.CASE_INSENSITIVE_ORDER);
			Long lastTransaction = null;
			while (rs.next()){
				var currentTransaction = rs.getLong(TRANSACTION_ID);
				if (lastTransaction == null) { // first row
					transactionTags.add(rs.getString(TAG));
					lastTransaction = currentTransaction;
				} else if (lastTransaction == currentTransaction) {
					transactionTags.add(rs.getString(TAG));
				} else {
					if (set == null) {
						set = transactionTags;
					} else {
						set.retainAll(transactionTags);
					}
					transactionTags = new HashSet<>();
					transactionTags.add(rs.getString(TAG));
					lastTransaction = currentTransaction;
				}
			}
			rs.close();
			return set == null ? transactionTags : set;
		} catch (SQLException e){
			throw failedToLoadMembers(accountId);
		}
	}

	@Override
	public Account loadAccount(long accountId) {
		try {
			var rs = select(ALL).from(TABLE_ACCOUNTS).where(ID,equal(accountId)).exec(db);
			Account account = null;
			if (rs.next()) account = Account.of(rs);
			rs.close();
			if (account==null) throw failedToLoadObject(Text.ACCOUNT,accountId);
			return account;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	@Override
	public Transaction loadTransaction(long transactionId) {
		try {
			Transaction transaction = null;
			var rs = select(ALL).from(TABLE_TRANSACTIONS).where(ID,equal(transactionId)).exec(db);
			if (rs.next()) transaction = Transaction.of(rs);
			rs.close();
			if (transaction != null) return transaction;
			throw failedToLoadObject(Text.TRANSACTION,transactionId);
		} catch (SQLException e) {
			throw failedToLoadObject(Text.TRANSACTION);
		}
	}

	@Override
	public List<Transaction> loadTransactions(Account account) {
		try {
			var transactions = new HashMap<Long,Transaction>();
			var rs = select(ALL).from(TABLE_TRANSACTIONS).where(Field.ACCOUNT,equal(account.id())).exec(db);
			while (rs.next()) {
				var transaction = Transaction.of(rs);
				transactions.put(transaction.id(),transaction);
			}
			rs.close();
			var transactionIds = transactions.keySet().toArray();
			rs = select(ALL).from(TABLE_TAGS_TRANSACTIONS).leftJoin(Field.TAG_ID,TABLE_TAGS, ID).where(Field.TRANSACTION_ID,in(transactionIds)).exec(db);
			while (rs.next()) {
				var transaction = transactions.get(rs.getLong(Field.TRANSACTION_ID));
				if (transaction != null) transaction.tags().add(rs.getString(Field.TAG));
			}
			rs.close();
			return transactions.values().stream().sorted(Comparator.comparing(Transaction::date)).toList();
		} catch (SQLException e) {
			throw failedToLoadMembers(account);
		}
	}

	@Override
	public Account save(Account account) {
		if (account.id() == 0) try { // new account
			var rs = Query.insertInto(TABLE_ACCOUNTS,Field.NAME, Field.CURRENCY, Field.OWNER).values(account.name(),account.currency(),account.ownerId()).execute(db).getGeneratedKeys();
			Long newId = null;
			if (rs.next()) newId = rs.getLong(1);
			rs.close();
			if (newId == null) throw failedToStoreObject(account);
			return account.withId(newId);
		} catch (SQLException e) {
			throw failedToStoreObject(account).causedBy(e);
		} else {
			throw notImplemented(this,"save(account)");
		}
	}

	@Override
	public Transaction save(Transaction transaction) {
		var timestamp = transaction.date().toEpochSecond(ZoneOffset.UTC);
		if (transaction.id() == 0) {
			try {
				var rs = insertInto(TABLE_TRANSACTIONS, Field.ACCOUNT, Field.TIMESTAMP, Field.SOURCE, Field.DESTINATION, Field.AMOUNT, Field.DESCRIPTION)
						.values(transaction.accountId(), timestamp, transaction.source().value(), transaction.destination().value(), transaction.amount(), transaction.purpose())
						.execute(db).getGeneratedKeys();
				if (rs.next()) transaction = transaction.withId(rs.getLong(1));
				rs.close();
			} catch (SQLException e) {
				throw failedToStoreObject(transaction);
			}
		} else if (transaction.isDirty()) {
			try {
				replaceInto(TABLE_TRANSACTIONS, Field.ID, Field.ACCOUNT, Field.TIMESTAMP, Field.SOURCE, Field.DESTINATION, Field.AMOUNT, Field.DESCRIPTION)
						.values(transaction.id(), transaction.accountId(), timestamp, transaction.source().value(), transaction.destination().value(), transaction.amount(), transaction.purpose())
						.execute(db).close();
				return transaction.clearDirtyState();
			} catch (SQLException e) {
				throw failedToStoreObject(transaction);
			}
		}
		return saveTags(transaction);
	}

	private Transaction saveTags(Transaction transaction) {
		var remaining = new HashSet<>(transaction.tags());
		var existingTags = new HashMap<String,Long>();
		try {
			var rs = select(ALL).from(TABLE_TAGS).where(Field.TAG,in(transaction.tags().toArray())).exec(db);
			while (rs.next()) existingTags.put(rs.getString(Field.TAG), rs.getLong(ID));
			rs.close();
		} catch (SQLException e){
			throw failedToLoadMembers(transaction);
		}
		remaining.removeAll(existingTags.keySet());
		for (var tag : remaining) {
			try {
				var rs = insertInto(TABLE_TAGS, Field.TAG).values(tag).execute(db).getGeneratedKeys();
				if (rs.next()) existingTags.put(tag,rs.getLong(1));
				rs.close();
			} catch (SQLException e){
				throw failedToStoreObject(tag);
			}
		}
		try {
			var query = replaceInto(TABLE_TAGS_TRANSACTIONS, Field.TRANSACTION_ID, Field.TAG_ID);
			for (var tag_id : existingTags.values()) query.values(transaction.id(), tag_id);
			query.execute(db).close();
		} catch (SQLException e){
			throw failedToStoreObject(transaction);
		}
		return transaction;
	}

	@Override
	public HashSet<String> searchField(long userId, String field , String key) {
		var accounts = listAccounts(userId);
		var accountIds = accounts.stream().map(Account::id).toArray();
		var destinations = new HashSet<String>();
		try {
			var rs = Query.select("DISTINCT "+field).from(TABLE_TRANSACTIONS).where(Field.ACCOUNT,in(accountIds)).where(field,like("%"+key+"%")).exec(db);
			while (rs.next()) destinations.add(rs.getString(1));
			rs.close();
			return destinations;
		} catch (SQLException e) {
			throw failedToReadFromTable(field,TABLE_TRANSACTIONS).causedBy(e);
		}
	}

	@Override
	public Set<String> searchTagsContaining(String key, long accountId) {
		try {
			var tags = new HashSet<String>();
			var rs = select(ALL).from(TABLE_TRANSACTIONS).leftJoin(ID,TABLE_TAGS_TRANSACTIONS,Field.TRANSACTION_ID).leftJoin(Field.TAG_ID,TABLE_TAGS, ID).where(Field.TAG,like(format("%{0}%",key))).exec(db);
			while (rs.next()) tags.add(rs.getString(Field.TAG));
			rs.close();
			return tags;
		} catch (SQLException e){
			throw failedToSearchDb(t(Text.TAGS));
		}
	}
}
