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

import static de.srsoftware.tools.jdbc.Condition.equal;
import static de.srsoftware.tools.jdbc.Query.replaceInto;
import static de.srsoftware.tools.jdbc.Query.update;
import static de.srsoftware.umbrella.core.constants.Constants.TABLE_SETTINGS;
import static de.srsoftware.umbrella.core.constants.Field.*;
import static de.srsoftware.umbrella.core.exceptions.UmbrellaException.failedToCreateTable;
import static de.srsoftware.umbrella.core.exceptions.UmbrellaException.failedToReadFromTable;
import static java.lang.System.Logger.Level.INFO;
import static java.text.MessageFormat.format;

import de.srsoftware.tools.jdbc.Query;
import java.sql.Connection;
import java.sql.SQLException;

public abstract class BaseDb {
	protected final System.Logger LOG = System.getLogger(getClass().getInterfaces()[0].getSimpleName());

	protected final Connection db;

	public BaseDb(Connection connection) {
		db = connection;
		var version = createTables();
		LOG.log(INFO,"Updated db to version {0}",version);

	}

	protected abstract int createTables();

	protected int createSettingsTable() {
		var createTable = """
CREATE TABLE IF NOT EXISTS {0} ( {1} VARCHAR(255) PRIMARY KEY, {2} VARCHAR(255) NOT NULL);
""";
		try {
			var stmt = db.prepareStatement(format(createTable,TABLE_SETTINGS, KEY, VALUE));
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw failedToCreateTable(TABLE_SETTINGS).causedBy(e);
		}

		var version = 0;
		try {
			var rs = Query.select(VALUE).from(TABLE_SETTINGS).where(KEY, equal(DB_VERSION)).exec(db);
			if (rs.next()) version = rs.getInt(VALUE);
			rs.close();

			return version;
		} catch (SQLException e) {
			throw failedToReadFromTable(DB_VERSION,TABLE_SETTINGS).causedBy(e);
		}
	}

	protected int setCurrentVersion(int version)  {
		try {
			replaceInto(TABLE_SETTINGS, KEY, VALUE).values(DB_VERSION,version).execute(db).close();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		return version;
	}

	public void swapStates(String table) {
		try {
			db.setAutoCommit(false);
			update(table).set(STATUS).where(STATUS,equal(40)).prepare(db).apply(0).execute();
			update(table).set(STATUS).where(STATUS,equal(20)).prepare(db).apply(40).execute();
			update(table).set(STATUS).where(STATUS,equal(10)).prepare(db).apply(20).execute();
			update(table).set(STATUS).where(STATUS,equal(0)).prepare(db).apply(10).execute();
			db.setAutoCommit(true);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
}
