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

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.ModuleRegistry.*;
import static de.srsoftware.umbrella.core.constants.Field.*;
import static de.srsoftware.umbrella.core.constants.Text.WIKI_PAGE;
import static de.srsoftware.umbrella.core.constants.Text.WIKI_PAGES;
import static de.srsoftware.umbrella.core.exceptions.UmbrellaException.*;
import static de.srsoftware.umbrella.core.model.Permission.EDIT;
import static de.srsoftware.umbrella.core.model.Permission.READ_ONLY;
import static de.srsoftware.umbrella.core.model.Translatable.t;
import static de.srsoftware.umbrella.wiki.Constants.*;
import static java.lang.System.Logger.Level.*;
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.model.Permission;
import de.srsoftware.umbrella.core.model.WikiPage;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.*;
import java.util.regex.Pattern;

public class SqliteDb extends BaseDb implements WikiDb {
	private static final System.Logger LOG = System.getLogger("WikiDb");

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

	@Override
	protected int createTables() {
		int currentVersion = createSettingsTable();
		switch (currentVersion){
			case 0:
				createPagesTable();
				createPagesUsersTable();
			case 1:
				/*
				The old structure was:
				Pages
				| id (String) | version (int) | content (String) |

				Page_Users
				| page_id (String) | user_id (long) | permission (long) |

				This poses a problem whenever a page is renamed:
				On that occasion, all corresponding entries in the Page_Users table (and tags and notes) need to be updated!

				So the transformation went to the following structure:
				Pages
				| id (long) | title (String) | version (int) | content (String) |

				Page_Users
				| page_id (long) | user_id (long) | permission (long) |

				With this structure we have stable ids even when a page is renamed!
				 */
				var newPages = TABLE_PAGES+"_new";
				var newUsers = TABLE_PAGES_USERS+"_new";
				createNewTables(newPages, newUsers);
				transferValues(newPages, newUsers);
				dropOldTables();
				moveNewTables(newPages, newUsers);
		}

		return setCurrentVersion(2);
	}

	private void createNewTables(String newPages, String newUsers) {
		var pages = "CREATE TABLE IF NOT EXISTS {0} ( {1} INT NOT NULL, {2} INT NOT NULL, {3} VARCHAR(255) NOT NULL, {4} TEXT NOT NULL, PRIMARY KEY({1},{2}))";
		var users = "CREATE TABLE IF NOT EXISTS {0} ( {1} INT NOT NULL, {2} INT NOT NULL, {3} INT NOT NULL, PRIMARY KEY({1}, {2}))";

		try {
			var stmt = db.prepareStatement(format(pages, newPages, ID, VERSION, TITLE, CONTENT));
			stmt.execute();
			stmt.close();

			stmt = db.prepareStatement(format(users, newUsers, PAGE_ID, USER_ID, PERMISSIONS));
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw failedToCreateTable(newPages).causedBy(e);
		}
	}

	private void createPagesTable() {
		var sql = "CREATE TABLE IF NOT EXISTS {0} ( {1} VARCHAR(255) NOT NULL, {2} INT NOT NULL, {3} TEXT NOT NULL, PRIMARY KEY({1},{2}))";
		try {
			var stmt = db.prepareStatement(format(sql, TABLE_PAGES, ID, VERSION, CONTENT));
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw failedToCreateTable(TABLE_PAGES).causedBy(e);
		}
	}

	private void createPagesUsersTable() {
		var sql = "CREATE TABLE IF NOT EXISTS {0} ( {1} VARCHAR(255) NOT NULL, {2} INT NOT NULL, {3} INT NOT NULL, PRIMARY KEY({1}, {2}))";
		try {
			var stmt = db.prepareStatement(format(sql, 	TABLE_PAGES_USERS, PAGE_ID, USER_ID, PERMISSIONS));
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw failedToCreateTable(TABLE_PAGES_USERS).causedBy(e);
		}
	}

	@Override
	public WikiPage drop(WikiPage page) {
		try {
			delete().from(TABLE_PAGES).where(ID,equal(page.id())).where(VERSION,equal(page.version())).execute(db);
			return page;
		} catch (SQLException e) {
			throw failedToDropObject("page version").causedBy(e);
		}
	}

	private void dropOldTables() {
		try {
			db.prepareStatement(format("DROP TABLE {0}",TABLE_PAGES)).execute();
		} catch (SQLException e) {
			throw failedToDropObject(TABLE_PAGES).causedBy(e);
		}
		try {
			db.prepareStatement(format("DROP TABLE {0}",TABLE_PAGES_USERS)).execute();
		} catch (SQLException e) {
			throw failedToDropObject(TABLE_PAGES_USERS).causedBy(e);
		}
	}

    @Override
    public long getNextId() {
        try {
            var id = 0L;
            var rs = select("MAX(ID)").from(TABLE_PAGES).exec(db);
            if (rs.next()) id = rs.getLong(1);
            rs.close();
            return id+1;
        } catch (SQLException e) {
			throw databaseException(FAILED_TO_GET_FREE_ID).causedBy(e);
        }
    }

	@Override
	public Map<Long, WikiPage> find(long userId, List<String> keys, boolean fulltext) {
		try {
			var map = new HashMap<Long,WikiPage>();
			{
				var query = select(ALL).from(TABLE_PAGES).leftJoin(ID, TABLE_PAGES_USERS, PAGE_ID).where(USER_ID, equal(userId));
				for (var key : keys) query.where(TITLE, like("%" + key.replaceAll("[ÄäÖöÜüß]", "%") + "%"));
				var rs = query.exec(db);
				while (rs.next()) {
					var page = WikiPage.of(rs);
					map.put(page.id(), page);
				}
				rs.close();
			}
			if (fulltext) {
				var query = select(ALL).from(TABLE_PAGES).leftJoin(ID, TABLE_PAGES_USERS, PAGE_ID).where(USER_ID, equal(userId));
				for (var key : keys) query.where(CONTENT, like("%" + key.replaceAll("[ÄäÖöÜüß]", "%") + "%"));
				var rs = query.exec(db);
				while (rs.next()) {
					var page = WikiPage.of(rs);
					map.put(page.id(), page);
				}
				rs.close();
			}
			return map;
		} catch (SQLException e) {
			throw failedToSearchDb(t(WIKI_PAGES)).causedBy(e);
		}
	}

	@Override
    public boolean isAvailable(String title) {
        if (title==null||title.isBlank())return false;
        try {
            var count = 1;
            var rs = select("COUNT(ID)").from(TABLE_PAGES).where(TITLE,equal(title)).exec(db);
            if (rs.next()) count = rs.getInt(1);
            rs.close();
            return count < 1;
        } catch (SQLException e) {
			throw databaseException(FAILED_TO_CHECK_ENTITY_AVAILABLE, OBJECT,title).causedBy(e);
        }
    }

    @Override
	public List<String> listUserPages(long userId) {
		try {
			var rs = select(TITLE,"MAX(version) AS version").from(TABLE_PAGES).leftJoin(ID,TABLE_PAGES_USERS,PAGE_ID).where(USER_ID, equal(userId)).groupBy(TITLE).sort("TITLE COLLATE NOCASE ASC").exec(db);
			var set = new ArrayList<String>();
			while (rs.next()) set.add(rs.getString(TITLE));
			rs.close();
			return set;
		} catch (SQLException e) {
			throw databaseException(FAILED_TO_LIST_ENTITIES, TYPE,WIKI_PAGES).causedBy(e);
		}
	}

	@Override
	public WikiPage load(String title, Integer version) {
		WikiPage page = null;
		try { // Try to load by id
			long id = Long.parseLong(title);
			var query = select(ALL).from(TABLE_PAGES).where(ID, equal(id));
			if (version == null) {
				query.sort(VERSION+" DESC").limit(1);
			} else {
				query.where(VERSION, equal(version));
			}
			var rs = query.exec(db);
			if (rs.next()) page = WikiPage.of(rs);
			rs.close();
		} catch (NumberFormatException ignored){
			// title is not an id, go on…
		} catch (SQLException e) {
			throw failedToLoadObject("wiki page",title).causedBy(e);
		}
		if (page == null) try { // page was not loaded by ID
			var query = select(ALL).from(TABLE_PAGES).where(TITLE, equal(title));
			if (version == null) {
				query.sort(VERSION+" DESC").limit(1);
			} else {
				query.where(VERSION, equal(version));
			}
			var rs = query.exec(db);
			if (rs.next()) page = WikiPage.of(rs);
			rs.close();
		} catch (SQLException e) {
			throw failedToLoadObject("wiki page",title).causedBy(e);
		}
		if (page == null) throw notFound(FAILED_TO_LOAD_OBJECT_BY_ID, OBJECT,WIKI_PAGE, ID,title);
		try {
			var rs = select(VERSION).from(TABLE_PAGES).where(ID, equal(page.id())).sort(VERSION).exec(db);
			var versions = page.versions();
			while (rs.next()) versions.add(rs.getInt(VERSION));
			rs.close();
			return page;
		} catch (SQLException e) {
			throw failedToLoadObject("wiki page",title).causedBy(e);
		}
	}

	@Override
	public Map<Long, Permission> loadMembers(WikiPage page) {
		try {
			var map = new HashMap<Long, Permission>();
			var rs = select(ALL).from(TABLE_PAGES_USERS).where(PAGE_ID, equal(page.id())).exec(db);
			while (rs.next()){
				var permission = wikiPermission(rs.getInt(PERMISSIONS));
				if (permission != null) map.put(rs.getLong(USER_ID),permission);
			}
			rs.close();
			return map;
		} catch (SQLException e) {
			throw failedToLoadMembers(t("wiki page {title}",TITLE,page.title())).causedBy(e);
		}
	}

	private void moveNewTables(String newPages, String newUsers) {
		try {
			db.prepareStatement(format("ALTER TABLE {0} RENAME TO {1}",newPages,TABLE_PAGES)).execute();
		} catch (SQLException e) {
			throw databaseException(FAILED_TO_UPDATE_OBJECT, OBJECT,format("{0} → {1}", newPages, TABLE_PAGES)).causedBy(e);
		}
		try {
			db.prepareStatement(format("ALTER TABLE {0} RENAME TO {1}",newUsers,TABLE_PAGES_USERS)).execute();
		} catch (SQLException e) {
			throw databaseException(FAILED_TO_UPDATE_OBJECT, OBJECT,format("{0} → {1}", newPages, TABLE_PAGES_USERS)).causedBy(e);
		}
	}

	@Override
	public WikiPage save(WikiPage page) {
		try {
			if (page.isDirty(CONTENT) || page.isDirty(ID) || page.isDirty(TITLE)) {
				insertInto(TABLE_PAGES, ID,VERSION,TITLE, CONTENT).values(page.id(),page.version(),page.title(),page.content()).execute(db).close();
				page.versions().add(page.version());
			}
			if (page.isDirty(MEMBERS)){
				Query.delete().from(TABLE_PAGES_USERS).where(PAGE_ID, equal(page.id())).where(USER_ID,Condition.notIn(page.members().keySet().toArray())).execute(db);
				var query = replaceInto(TABLE_PAGES_USERS,PAGE_ID, USER_ID,PERMISSIONS);
				for (var member : page.members().entrySet()) query.values(page.id(),member.getKey(),wikiPermissionCode(member.getValue().permission()));
				query.execute(db).close();
			}
			if (page.isDirty(GUEST_ALLOWED)){
				if (page.guestAllowed()) {
					insertInto(TABLE_PAGES_USERS,PAGE_ID, USER_ID,PERMISSIONS).values(page.id(),0, wikiPermissionCode(READ_ONLY)).execute(db).close();
				} else {
					Query.delete().from(TABLE_PAGES_USERS).where(PAGE_ID,equal(page.id())).where(USER_ID,equal(0)).execute(db);
				}
			}
			return page;
		} catch (SQLException e) {
			throw failedToStoreObject(page.title()).causedBy(e);
		}
	}

	private void transferValues(String newTable, String newUsers) {
		var pageMap = new HashMap<String,Long>(); // map from old IDs to new IDs
		try {
			Long id = 0L;
			String lastTitle = null;
			var rs = select("MAX(ID)").from(newTable).limit(1).exec(db);
			if (rs.next()) id = rs.getLong(1);
			rs.close();

			rs = select(ALL).from(TABLE_PAGES).sort(ID).exec(db);

			while (rs.next()){
				var title   = rs.getString(ID);
				var version = rs.getInt(VERSION);
				var content = rs.getString(CONTENT);
				if (!title.equals(lastTitle)) {
					pageMap.put(lastTitle = title,++id);
					LOG.log(DEBUG,"Transferring \"{0}\" to new table with id = {1}",title,id);
				}
				insertInto(newTable, ID,VERSION,TITLE, CONTENT).values(id,version,title,content).execute(db).close();
			}

			rs.close();

			rs = select(ALL).from(TABLE_PAGES_USERS).sort(PAGE_ID).exec(db);
			while (rs.next()){
				var userId = rs.getLong(USER_ID);
				var title = rs.getString(PAGE_ID);
				var perm   = rs.getInt(PERMISSIONS);
				id = pageMap.get(title);
				if (id == null){
					LOG.log(WARNING,"Found reference to non-existing page ({0}) in {1} – skipping transfer of permission",title,TABLE_PAGES_USERS);
					continue;
				}
				insertInto(newUsers,PAGE_ID, USER_ID,PERMISSIONS).values(id,userId,perm).execute(db).close();
			}
			rs.close();
		} catch (SQLException e) {
			throw databaseException(FAILED_TO_MOVE,"old",TABLE_PAGES,"new",TABLE_PAGES+"_new").causedBy(e);
		}
		var notes = noteService();
		var tags  = tagService();
		for (var entry : pageMap.entrySet()){
			var oldId = entry.getKey();
			var newId = entry.getValue();
			notes.updateId("wiki",oldId,newId);
		}

		var urls = bookmarkService().find("%/wiki/%/view");
		var pattern = Pattern.compile("/wiki/([^/]+)/view");

		var users = userService().list(null,null,null);
		for (var entry : urls.entrySet()){
			var urlId = entry.getKey();
			var url = entry.getValue();
			var match = pattern.matcher(url);
			if (match.find()){
				var title = match.group(1);
				var pageId = pageMap.get(title);
				if (pageId == null) continue;
				for (var user : users.values()){
					var tagList = tags.getTags("bookmark",urlId,user);
					if (!tagList.isEmpty()){
						LOG.log(DEBUG,"{0} has tags for page {1}: {2}",user.name(),title,tagList);
						tags.save("wiki",pageId,List.of(user.id()),tagList);
					}
				}
			}
		}
	}

	private int wikiPermissionCode(Permission perm){
		return switch (perm){
			case READ_ONLY -> 1;
			case ASSIGNEE, EDIT, OWNER -> 2;
		};
	}

	private Permission wikiPermission(int code){
		return switch (code){
			case 1 -> READ_ONLY;
			case 2,3 -> EDIT;
			default -> null;
		};
	}
}