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

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.SelectQuery.ALL;
import static de.srsoftware.umbrella.core.Errors.*;
import static de.srsoftware.umbrella.core.constants.Field.*;
import static de.srsoftware.umbrella.core.constants.Text.NOTE;
import static de.srsoftware.umbrella.core.constants.Text.NOTE_WITH_ID;
import static de.srsoftware.umbrella.core.exceptions.UmbrellaException.*;
import static de.srsoftware.umbrella.core.model.Translatable.t;
import static de.srsoftware.umbrella.notes.Constants.*;
import static java.lang.System.Logger.Level.*;
import static java.text.MessageFormat.format;
import static java.time.ZoneOffset.UTC;

import de.srsoftware.tools.jdbc.Query;
import de.srsoftware.umbrella.core.BaseDb;
import de.srsoftware.umbrella.core.constants.Text;
import de.srsoftware.umbrella.core.model.Note;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class SqliteDb extends BaseDb implements NotesDb {
	private static final int INITIAL_DB_VERSION = 1;
	private static final System.Logger LOG = System.getLogger("NotesDB");

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

	protected int createTables() {
		int currentVersion = createSettingsTable();
		switch (currentVersion){
			case 0:
				createNotesTable();
			case 1:
				addModuleColumn();
				addEntityIdColumn();
				calcReferences();
				dropUriColumn();
		}

		return setCurrentVersion(2);
	}

	private void addEntityIdColumn() {
		var createTable = """
ALTER TABLE notes
ADD COLUMN entity_id VARCHAR(255);
""";
		try {
			var stmt = db.prepareStatement(createTable);
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw databaseException(FAILED_TO_UPDATE_COLUMN,"old","(not existing)","new", ENTITY_ID, TABLE,TABLE_NOTES).causedBy(e);
		}
	}

	private void addModuleColumn() {
		var createTable = """
ALTER TABLE notes
ADD COLUMN module VARCHAR(20);
""";
		try {
			var stmt = db.prepareStatement(createTable);
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw databaseException(FAILED_TO_UPDATE_COLUMN,"old","(not existing)","new", MODULE, TABLE,TABLE_NOTES).causedBy(e);
		}
	}

	private void calcReferences(){
		var createTable = """
UPDATE notes
SET module = SUBSTR(uri, 1, INSTR(uri, ':') - 1),
    entity_id = SUBSTR(uri, INSTR(uri, ':') + 1);
""";
		try {
			var stmt = db.prepareStatement(createTable);
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw databaseException("Failed to fill \"{a}\" and \"{b}\" columns","a", MODULE,"b", ENTITY_ID).causedBy(e);
		}
	}

	private void createNotesTable() {
		var createTable = """
CREATE TABLE IF NOT EXISTS notes (
	id INTEGER PRIMARY KEY,
	user_id INT NOT NULL,
	uri VARCHAR(255) NOT NULL,
	note TEXT NOT NULL,
	timestamp INT)""";
		try {
			var stmt = db.prepareStatement(createTable);
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw failedToCreateTable(TABLE_NOTES).causedBy(e);
		}
	}

	private void createNewNotesTable() {
		var createTable = """
CREATE TABLE IF NOT EXISTS "{0}" (
	{1} INTEGER NOT NULL PRIMARY KEY,
	{2} TEXT NOT NULL,
	{3} VARCHAR(20) NOT NULL,
	{4} VARCHAR(255) NOT NULL,
	{5} INTEGER NOT NULL,
	{6} DATETIME NOT NULL
)""";
		try {
			var stmt = db.prepareStatement(format(createTable,TABLE_NOTES, ID, NOTE, MODULE, ENTITY_ID, USER_ID, TIMESTAMP));
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw failedToCreateTable(TABLE_NOTES).causedBy(e);
		}
	}

	@Override
	public long delete(long noteId) {
		try {
			Query.delete().from(TABLE_NOTES)
					.where(ID,equal(noteId))
					.execute(db);
			return noteId;
		} catch (SQLException e){
			throw failedToDropObject(t(NOTE_WITH_ID, ID,noteId)).causedBy(e);
		}
	}

	@Override
	public void deleteEntity(String module, String entityId) {
		try {
			Query.delete().from(TABLE_NOTES)
					.where(MODULE,equal(module)).where(ENTITY_ID,equal(entityId))
					.execute(db);
		} catch (SQLException e){
			throw databaseException(FAILED_TO_DROP_NOTES,MODULE,module, ID,entityId).causedBy(e);
		}
	}

	private void dropUriColumn(){
		var createTable = "ALTER TABLE notes DROP COLUMN uri";
		try {
			var stmt = db.prepareStatement(createTable);
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw databaseException(FAILED_TO_DROP_OBJECT, t("uri column")).causedBy(e);
		}
	}

	@Override
	public Map<Long, Note> find(long userId, List<String> keys, boolean fulltext) {
		try {
			var notes = new HashMap<Long, Note>();
			var query = select(ALL).from(TABLE_NOTES).where(USER_ID, equal(userId));
			for (var key : keys) query.where(NOTE,like("%"+key+"%"));
			var rs = query.exec(db);
			while (rs.next()){
				var note = Note.of(rs);
				notes.put(note.id(),note);
			}
			rs.close();
			return notes;
		} catch (SQLException e) {
			throw failedToSearchDb(t(TABLE_NOTES)).causedBy(e);
		}
	}

	@Override
	public Map<Long, Note> list(long authorId, long offset, long limit) {
		try {
			var notes = new HashMap<Long, Note>();
			var rs = select(ALL).from(TABLE_NOTES).where(USER_ID,equal(authorId))
					.sort(format("{0} DESC", ID)).skip(offset).limit(limit).exec(db);
			while (rs.next()) {
				var note = Note.of(rs);
				notes.put(note.id(),note);
			}
			rs.close();
			return notes;
		} catch (SQLException e) {
			throw failedToLoadObject(TABLE_NOTES).causedBy(e);
		}
	}

	@Override
	public Map<Long, Note> list(String module, String entityId) {
		try {
			var notes = new HashMap<Long, Note>();
			var query = select(ALL).from(TABLE_NOTES).where(MODULE,equal(module));
			if (entityId != null) query.where(ENTITY_ID,equal(entityId));
			var rs = query.exec(db);
			while (rs.next()) {
				var note = Note.of(rs);
				notes.put(note.id(),note);
			}
			rs.close();
			return notes;
		} catch (SQLException e) {
			throw failedToLoadObject(TABLE_NOTES).causedBy(e);
		}
	}

	@Override
	public Note load(long noteId) {
		try {
			Note note = null;
			var rs = select(ALL).from(TABLE_NOTES).where(ID,equal(noteId)).exec(db);
			if (rs.next()) note = Note.of(rs);
			rs.close();
			return note;
		} catch (SQLException e) {
			throw failedToLoadObject(t(Text.NOTE),noteId).causedBy(e);
		}
	}

	@Override
	public Note save(Note note) {
		try {
			if (note.id() == 0) {
				var rs = insertInto(TABLE_NOTES, USER_ID, MODULE, ENTITY_ID, NOTE, TIMESTAMP)
						.values(note.authorId(), note.module(), note.entityId(), note.text(), note.timestamp().toEpochSecond(UTC))
						.execute(db)
						.getGeneratedKeys();
				long id = 0;
				if (rs.next()) id = rs.getLong(1);
				rs.close();
				if (id != 0) return new Note(id,note.module(),note.entityId(),note.authorId(),note.text(),note.timestamp());
			} else {
				replaceInto(TABLE_NOTES, ID, USER_ID, MODULE, ENTITY_ID, NOTE, TIMESTAMP)
						.values(note.id(), note.authorId(), note.module(), note.entityId(), note.text(), note.timestamp().toEpochSecond(UTC))
						.execute(db).close();

			}
			return note;
		} catch (SQLException e){
			throw failedToStoreObject(t(Text.NOTE)).causedBy(e);
		}
	}

	@Override
	public void updateId(String module, Object oldId, Object newId) {
		try {
			update(TABLE_NOTES).set(ENTITY_ID).where(MODULE,equal(module)).where(ENTITY_ID,equal(oldId)).prepare(db).apply(newId).close();
			LOG.log(DEBUG,"Updated note @ {0}.{1} → {0}.{2}",module,oldId,newId);
		} catch (SQLException e) {
			throw databaseException("Failed to update {module}.{a} → {module}.{b}", MODULE,module,"a",oldId,"b",newId).causedBy(e);
		}
	}
}
