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

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.bookmarks.Constants.*;
import static de.srsoftware.umbrella.core.Errors.*;
import static de.srsoftware.umbrella.core.ModuleRegistry.tagService;
import static de.srsoftware.umbrella.core.constants.Field.*;
import static de.srsoftware.umbrella.core.constants.Text.BOOKMARK;
import static de.srsoftware.umbrella.core.exceptions.UmbrellaException.*;
import static de.srsoftware.umbrella.messagebus.MessageBus.messageBus;
import static java.text.MessageFormat.format;
import static java.time.ZoneOffset.UTC;

import de.srsoftware.umbrella.core.BaseDb;
import de.srsoftware.umbrella.core.constants.Module;
import de.srsoftware.umbrella.core.model.Bookmark;
import de.srsoftware.umbrella.core.model.Translatable;
import de.srsoftware.umbrella.core.model.UmbrellaUser;
import de.srsoftware.umbrella.messagebus.events.BookmarkEvent;
import de.srsoftware.umbrella.messagebus.events.Event;
import java.sql.Connection;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.Collection;
import java.util.HashMap;
import java.util.Map;

public class SqliteDb extends BaseDb implements BookmarkDb {
	public SqliteDb(Connection conn) {
		super(conn);
	}

	@Override
	protected int createTables() {
		var version = createSettingsTable();
		switch (version){
			case 0:
				createUrlsTable();
				createUrlCommentsTable();
		}
		return setCurrentVersion(1);
	}

	private void createUrlCommentsTable() {
		var sql = """
CREATE TABLE IF NOT EXISTS {0} (
	`{1}` INTEGER NOT NULL,
	`{2}` INTEGER NOT NULL,
	`{3}` TEXT NOT NULL,
	`{4}` DATETIME NOT NULL,
	PRIMARY KEY (`{1}`,`{2}`)
)""";
		try {
			var stmt = db.prepareStatement(format(sql,TABLE_URL_COMMENTS,URL_ID, USER_ID, COMMENT,TIMESTAMP));
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw databaseException(FAILED_TO_CREATE_TABLE,TABLE_URL_COMMENTS).causedBy(e);
		}
	}

	private void createUrlsTable() {
		var sql = "CREATE TABLE IF NOT EXISTS urls (ID INTEGER PRIMARY KEY, url TEXT UNIQUE NOT NULL)";
		try {
			var stmt = db.prepareStatement(sql);
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw failedToCreateTable(TABLE_URLS).causedBy(e);
		}
	}

	@Override
	public Map<Long, String> findUrls(String key) {
		try {
			var map = new HashMap<Long,String>();
			var rs = select(ALL).from(TABLE_URLS).where(URL, like(key)).exec(db);
			while (rs.next()) map.put(rs.getLong(ID),rs.getString(URL));
			rs.close();;
			return map;
		} catch (SQLException e) {
			throw failedToLoadObject("bookmark list").causedBy(e);
		}
	}

	@Override
	public Map<Long, Bookmark> findUrls(long userId, Collection<String> keys) {
		try {
			var map = new HashMap<Long,Bookmark>();
			var query = select(ALL).from(TABLE_URL_COMMENTS).leftJoin(URL_ID,TABLE_URLS, ID)
					.where(USER_ID, equal(userId));
					for (var key : keys) query.where(COMMENT,like("%"+key+"%"));
			var rs = query.sort(format("{0} DESC",TIMESTAMP)).exec(db);
			while (rs.next()){
				var bookmark = Bookmark.of(rs);
				map.put(bookmark.urlId(),bookmark);
			}
			rs.close();;
			return map;
		} catch (SQLException e) {
			throw failedToDropObject("bookmark list").causedBy(e);
		}
	}

	@Override
	public Map<Long, Bookmark> list(long userId, Long offset, Long limit) {
		try {
			var map = new HashMap<Long,Bookmark>();
			var rs = select(ALL).from(TABLE_URL_COMMENTS).leftJoin(URL_ID,TABLE_URLS, ID).where(USER_ID, equal(userId)).sort(format("{0} DESC",TIMESTAMP)).skip(offset).limit(limit).exec(db);
			while (rs.next()){
				var bookmark = Bookmark.of(rs);
				map.put(bookmark.urlId(),bookmark);
			}
			rs.close();;
			return map;
		} catch (SQLException e) {
			throw failedToLoadObject("bookmark list").causedBy(e);
		}
	}

	@Override
	public Bookmark load(long urlId, long userId) {
		try {
			Bookmark result = null;
			var rs = select(ALL).from(TABLE_URLS).leftJoin(ID,TABLE_URL_COMMENTS,URL_ID).where(ID,equal(urlId)).where(USER_ID,equal(userId)).exec(db);
			if (rs.next()) result = Bookmark.of(rs);
			rs.close();
			if (result != null) return result;
			throw failedToLoadObject(Translatable.t(BOOKMARK),urlId);
		} catch (SQLException e) {
			throw failedToLoadObject(Translatable.t(BOOKMARK),urlId).causedBy(e);
		}
	}

	@Override
	public void remove(UmbrellaUser user, Bookmark bookmark) {
		try {
			var urlId = bookmark.urlId();
			var userId = user.id();
			delete().from(TABLE_URL_COMMENTS).where(USER_ID, equal(userId)).where(URL_ID, equal(urlId)).execute(db);
			messageBus().dispatch(new BookmarkEvent(user,bookmark, Event.EventType.DELETE));
			tagService().deleteEntity(Module.BOOKMARK,urlId,userId);
		} catch (SQLException e){
			throw failedToDropObject(BOOKMARK);
		}
	}

	@Override
	public Bookmark save(String url, String comment, Collection<Long> userIds, LocalDateTime timestamp) {
		try {
			var rs = select(ID).from(TABLE_URLS).where(URL, equal(url)).exec(db);
			var urlId = 0L;
			if (rs.next()) urlId = rs.getLong(ID);
			rs.close();
			if (urlId == 0) {
				var stmt = insertInto(TABLE_URLS, URL).values(url).execute(db);
				rs = stmt.getGeneratedKeys();
				if (rs.next()) urlId = rs.getLong(1);
				rs.close();
				stmt.close();
			}
			var query = replaceInto(TABLE_URL_COMMENTS,URL_ID, USER_ID, COMMENT, TIMESTAMP);
			for (long userId : userIds) query.values(urlId,userId,comment,timestamp.toEpochSecond(UTC));
			query.execute(db).close();
			return Bookmark.of(urlId,url,comment,timestamp);
		} catch (SQLException e) {
			throw failedToStoreObject(this).causedBy(e);
		}
	}
}
