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

import static de.srsoftware.tools.Optionals.is0;
import static de.srsoftware.tools.Optionals.isSet;
import static de.srsoftware.tools.jdbc.Condition.*;
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.bookmarks.Constants.*;
import static de.srsoftware.umbrella.core.Errors.*;
import static de.srsoftware.umbrella.core.constants.Field.*;
import static de.srsoftware.umbrella.core.constants.Field.TAG;
import static de.srsoftware.umbrella.core.constants.Field.TAGS;
import static de.srsoftware.umbrella.core.constants.Module.BOOKMARK;
import static de.srsoftware.umbrella.core.constants.Text.TABLE_WITH_NAME;
import static de.srsoftware.umbrella.core.constants.Text.USER_WITH_ID;
import static de.srsoftware.umbrella.core.exceptions.UmbrellaException.*;
import static de.srsoftware.umbrella.core.model.Translatable.t;
import static de.srsoftware.umbrella.tags.Constants.*;
import static java.lang.System.Logger.Level.*;
import static java.text.MessageFormat.format;
import static java.time.ZoneOffset.UTC;

import de.srsoftware.tools.Tuple;
import de.srsoftware.tools.jdbc.Query;
import de.srsoftware.umbrella.bookmarks.BookmarkDb;
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.Translatable;
import de.srsoftware.umbrella.core.model.UmbrellaUser;
import java.sql.Connection;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.*;
import java.util.regex.Pattern;

public class SqliteDb extends BaseDb implements TagDB{
	private final BookmarkDb bookmarks;

	public SqliteDb(Connection tagDb, Connection bmDb) {
		super(tagDb);
		bookmarks = new de.srsoftware.umbrella.bookmarks.SqliteDb(bmDb);
		createTables();
	}

	@Override
	protected int createTables() {
		var version = createSettingsTable();
		if (bookmarks == null) return version;
		switch (version){
			case 0:
				createLegacyUrlsTable();
				createLegacyCommentsTable();
				createLegacyUrlCommentsTable();
				createLegacyTagsTable();
			case 1:
				createNewTagTable();
				splitContentToBookmarks();
				dropOldTables();
				renameNewTagTable();
		}
		version = setCurrentVersion(2);
		LOG.log(INFO,"Updated db to version {0}",version);
		return version;
	}

	private void splitContentToBookmarks() {
		var pattern = Pattern.compile("/([^/]+)/(\\d+)/view");
		try {
			LocalDateTime earliestDate = LocalDateTime.of(2000,1,1,0,0);
			var rs = select(ALL).from(TABLE_URLS).leftJoin(HASH,TABLE_URL_COMMENTS,URL_HASH).leftJoin(COMMENT_HASH,TABLE_COMMENTS, HASH).where(TIMESTAMP,moreThan(0)).limit(1).exec(db);
			if (rs.next()) earliestDate = LocalDateTime.ofEpochSecond(rs.getLong(1),0,UTC);
			rs.close();

			var sql = "CREATE TABLE IF NOT EXISTS tags_new (TAG)";
			db.prepareStatement(sql).execute();
			// IMPORT BOOKMARKS
			var commentedURLS = select(ALL).from(TABLE_URLS).leftJoin(HASH,TABLE_URL_COMMENTS,URL_HASH).leftJoin(COMMENT_HASH,TABLE_COMMENTS, HASH).exec(db);

			while (commentedURLS.next()){
				var userId = commentedURLS.getLong(USER_ID);
				var urlHash = commentedURLS.getString(URL_HASH);
				if (userId == 0) continue;
				var url = commentedURLS.getString(URL);
				LOG.log(DEBUG,url);
				var timestamp = commentedURLS.getLong(TIMESTAMP);
				var comment = commentedURLS.getString(COMMENT);
				if (!isSet(comment)) comment = "";
				var dt = is0(timestamp) ? earliestDate : LocalDateTime.ofEpochSecond(timestamp,0,UTC);
				var bm = bookmarks.save(url,comment,List.of(userId),dt.withNano(0));

				String module = null;
				long entityId = 0;
				var matcher = pattern.matcher(url);
				if (matcher.find()){
					module = matcher.group(1);
					entityId = Long.parseLong(matcher.group(2));
					LOG.log(DEBUG,"This module is referring to Umbrella`s {0} {1}",module,entityId);
				}

				var urlTags = select(ALL).from(TABLE_TAGS).where(URL_HASH,equal(urlHash)).where(USER_ID,equal(userId)).exec(db);
				var insertQuery = insertInto(TABLE_TAGS_NEW,TAG, MODULE, ENTITY_ID, USER_ID).ignoreDuplicates(SQLITE);
				while (urlTags.next()){
					var tag = urlTags.getString(TAG);
					insertQuery.values(tag, BOOKMARK,bm.urlId(),userId);
					if (module != null && entityId != 0) insertQuery.values(tag,module,entityId,userId);
				}
				insertQuery.execute(db).close();
				urlTags.close();
			}
			commentedURLS.close();
		} catch (SQLException e) {
			throw databaseException(FAILED_TO_UPDATE_OBJECT, OBJECT, Translatable.t(TABLE_WITH_NAME,NAME,TABLE_URLS)).causedBy(e);
		}
	}

	private void createLegacyCommentsTable() {
		var sql = "CREATE TABLE IF NOT EXISTS comments (hash VARCHAR(255) PRIMARY KEY, comment TEXT NOT NULL)";
		try {
			var stmt = db.prepareStatement(sql);
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw failedToCreateTable("Comments (legacy)").causedBy(e);
		}
	}

	private void createLegacyTagsTable() {
		var sql = "CREATE TABLE IF NOT EXISTS tags (tag VARCHAR(255) NOT NULL, url_hash VARCHAR(255) NOT NULL, user_id int NOT NULL, UNIQUE(tag, url_hash, user_id))";
		try {
			var stmt = db.prepareStatement(sql);
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw failedToCreateTable("Tags (legacy)").causedBy(e);
		}
	}

	private void createLegacyUrlsTable() {
		var sql = "CREATE TABLE IF NOT EXISTS urls (hash VARCHAR(255) PRIMARY KEY, url TEXT NOT NULL, timestamp INT NOT NULL DEFAULT 0)";
		try {
			var stmt = db.prepareStatement(sql);
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw failedToCreateTable("URLs (legacy)").causedBy(e);
		}
	}

	private void createLegacyUrlCommentsTable() {
		var sql = "CREATE TABLE IF NOT EXISTS url_comments (`url_hash` VARCHAR ( 255 ) NOT NULL, `comment_hash` VARCHAR ( 255 ) NOT NULL, `user_id` INT NOT NULL, UNIQUE(`url_hash`,`user_id`))";
		try {
			var stmt = db.prepareStatement(sql);
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw failedToCreateTable("URL_comments (legacy)").causedBy(e);
		}
	}


	private void createNewTagTable() {
		var createTable = """
CREATE TABLE IF NOT EXISTS {0} (
	{1} VARCHAR(255) NOT NULL,
	{2} VARCHAR(20) NOT NULL,
	{3} INTEGER NOT NULL,
	{4} INTEGER,
	PRIMARY KEY ({1}, {2}, {3}, {4})
)""";
		try {
			var stmt = db.prepareStatement(format(createTable,TABLE_TAGS_NEW, TAG, MODULE, ENTITY_ID, USER_ID));
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw failedToCreateTable(TABLE_TAGS_NEW).causedBy(e);
		}
	}

	private void dropOldTables(){
		var sql = "DROP TABLE IF EXISTS {0}";
		String table = null;
		for (var t : List.of(TABLE_TAGS,TABLE_COMMENTS,TABLE_TOKENS,TABLE_URL_COMMENTS,TABLE_URLS)) try {
			table = t;
			var stmt = db.prepareStatement(format(sql,table));
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw failedToDropObject(table).causedBy(e);
		}
	}

	private void renameNewTagTable() {
		var sql = "ALTER TABLE {0} RENAME TO {1}";
		try {
			var stmt = db.prepareStatement(format(sql,TABLE_TAGS_NEW, TABLE_TAGS));
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw failedToCreateTable(TABLE_TAGS).causedBy(e);

		}
	}

	@Override
	public String delete(long userId, String module, long entityId, String tag) {
		try {
			Query.delete().from(TABLE_TAGS)
					.where(TAG,iEqual(tag)).where(MODULE,iEqual(module)).where(ENTITY_ID,equal(entityId)).where(USER_ID,equal(userId))
					.execute(db);
			Query.delete().from(TABLE_TAGS)
					.where(TAG,iEqual(tag)).where(MODULE,iEqual(module)).where(ENTITY_ID,equal(entityId)).where(USER_ID,isNull())
					.execute(db);
			return tag;
		} catch (SQLException e){
			throw failedToDropObject(tag);
		}
	}

	@Override
	public void deleteEntity(String module, long entityId) {
		deleteEntity(module,entityId,-1);
	}

	@Override
	public void deleteEntity(String module, long entityId, long userId) {
		try {
			var query = Query.delete().from(TABLE_TAGS)
					.where(MODULE,iEqual(module)).where(ENTITY_ID,equal(entityId));
			if (userId>0) query.where(USER_ID,equal(userId));
			query.execute(db);
		} catch (SQLException e){
			throw failedToDropObject(Translatable.t("{module}.{id}", MODULE,module, ID,entityId)).causedBy(e);
		}
	}

	@Override
	public Map<String, List<Long>> getUses(String tag, long userId) {
		try {
			var rs = select(ALL).from(TABLE_TAGS).where(TAG,iEqual(tag)).where(USER_ID,equal(userId)).exec(db);
			var result = new HashMap<String,List<Long>>();
			while (rs.next()){
				var module = rs.getString(MODULE);
				var entityId = rs.getLong(ENTITY_ID);
				result.computeIfAbsent(module, k -> new ArrayList<>()).add(entityId);
			}
			rs.close();
			rs = select(ALL).from(TABLE_TAGS).where(TAG,iEqual(tag)).where(USER_ID,isNull()).exec(db);
			while (rs.next()){
				var module = rs.getString(MODULE);
				var entityId = rs.getLong(ENTITY_ID);
				result.computeIfAbsent(module, k -> new ArrayList<>()).add(entityId);
			}
			rs.close();
			return result;
		} catch (SQLException e){
			throw failedToDropObject(Translatable.t("uses of {tag}",TAG,tag)).causedBy(e);
		}
	}

	@Override
	public Set<String> list(long userId, String module, long entityId) {
		try {
			var tags = new HashSet<String>();

			// load tags assigned to user
			var rs = select(TAG).from(TABLE_TAGS).where(MODULE,iEqual(module)).where(ENTITY_ID,equal(entityId)).where(USER_ID,equal(userId)).exec(db);
			while (rs.next()) tags.add(rs.getString(1));
			rs.close();

			// load tags assigned to no user
			rs = select(TAG).from(TABLE_TAGS).where(MODULE,iEqual(module)).where(ENTITY_ID,equal(entityId)).where(USER_ID,isNull()).exec(db);
			while (rs.next()) tags.add(rs.getString(1));
			rs.close();
			return tags;
		} catch (SQLException e) {
			throw databaseException(FAILED_TO_LIST_ENTITIES, TYPE,TAGS).causedBy(e);
		}
	}

	@Override
	public Collection<Tuple<String,Long>> list(long userId) {
		try {
			var rs = select("tag, COUNT(tag) as count").from(TABLE_TAGS).where(USER_ID,equal(userId)).groupBy(TAG).sort(TAG).exec(db);
			var list = new ArrayList<Tuple<String,Long>>();
			while (rs.next()) {
				var tag = rs.getString(TAG);
				if (tag.isBlank()) continue;;
				var count = rs.getLong("count");
				list.add(Tuple.of(tag,count));
			}
			rs.close();
			return list;
		} catch (SQLException e) {

			throw databaseException(FAILED_TO_LOAD_ENTITIES_OF_OWNER, TYPE,t(Text.TAGS), OWNER, Translatable.t(USER_WITH_ID, ID,userId)).causedBy(e);
		}
	}

	@Override
	public Map<Long, ? extends Collection<String>> list(long userId, String module, Collection<Long> entityIds) {
		try {
			var tags = new HashMap<Long,HashSet<String>>();

			// load tags assigned to user
			var rs = select(ENTITY_ID,TAG).from(TABLE_TAGS).where(MODULE,iEqual(module)).where(ENTITY_ID,in(entityIds.toArray())).where(USER_ID,equal(userId)).exec(db);
			while (rs.next()) tags.computeIfAbsent(rs.getLong(ENTITY_ID), k -> new HashSet<>()).add(rs.getString(TAG));
			rs.close();

			// load tags assigned to no user
			rs = select(ENTITY_ID,TAG).from(TABLE_TAGS).where(MODULE,iEqual(module)).where(ENTITY_ID,in(entityIds.toArray())).where(USER_ID,isNull()).exec(db);
			while (rs.next()) tags.computeIfAbsent(rs.getLong(ENTITY_ID), k -> new HashSet<>()).add(rs.getString(TAG));
			rs.close();
			return tags;
		} catch (SQLException e) {
			throw databaseException(FAILED_TO_LIST_ENTITIES, TYPE,t(Text.TAGS)).causedBy(e);
		}
	}

	@Override
	public void save(Collection<Long> userIds, String module, long entityId, Collection<String> tags) {
		try {
			var query = replaceInto(TABLE_TAGS, USER_ID, MODULE, ENTITY_ID,TAG);
			for (var tag : tags)  {
				if (userIds == null) { // tags not assigned to a user are available to all users
					query.values(null, module, entityId, tag);
				} else for (var userId : userIds) {
					query.values(userId,module,entityId,tag);
				}
			}
			query.execute(db).close();
		} catch (SQLException e){
			throw failedToStoreObject(String.join(", ",tags)).causedBy(e);
		}
	}

	@Override
	public Collection<String> search(String key, UmbrellaUser user) {
		try {
			var tags = new HashSet<String>();
			var rs = select("DISTINCT tag").from(TABLE_TAGS).where(USER_ID,equal(user.id())).where(Field.TAG,like("%"+key+"%")).exec(db);
			while (rs.next()) tags.add(rs.getString(1));
			rs.close();
			return tags;
		} catch (SQLException s){
			throw failedToLoadObject(Text.TAGS);
		}
	}

	@Override
	public void updateId(String module, Object oldId, Object newId) {
		try {
			update(TABLE_TAGS).set(ENTITY_ID).where(MODULE,iEqual(module)).where(ENTITY_ID,equal(oldId)).prepare(db).apply(newId).close();
			LOG.log(DEBUG,"Updated tag @ {0}.{1} → {0}.{2}",module,oldId,newId);
		} catch (SQLException e) {
			throw databaseException(FAILED_TO_UPDATE_OBJECT,format("{0}.{1} → {0}.{2}",module,oldId,newId)).causedBy(e);
		}
	}
}
