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

import static de.srsoftware.tools.Optionals.is0;
import static de.srsoftware.tools.jdbc.Condition.equal;
import static de.srsoftware.tools.jdbc.Query.*;
import static de.srsoftware.tools.jdbc.Query.SelectQuery.ALL;
import static de.srsoftware.umbrella.core.ModuleRegistry.userService;
import static de.srsoftware.umbrella.core.constants.Field.*;
import static de.srsoftware.umbrella.core.constants.Field.DESCRIPTION;
import static de.srsoftware.umbrella.core.exceptions.UmbrellaException.*;
import static de.srsoftware.umbrella.core.model.Permission.READ_ONLY;
import static de.srsoftware.umbrella.poll.Constants.*;
import static java.text.MessageFormat.format;

import de.srsoftware.tools.jdbc.Query;
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.exceptions.UmbrellaException;
import de.srsoftware.umbrella.core.model.Permission;
import de.srsoftware.umbrella.core.model.Poll;
import de.srsoftware.umbrella.core.model.UmbrellaUser;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.*;

public class SqliteDb extends BaseDb implements PollDb {

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

	@Override
	protected int createTables() {
		var version = createSettingsTable();
		switch (version){
			case 0:
				createPollsTable();
				createOptionsTable();
				createWeightsTable();
				createSelectionsTable();
			case 1:
				createSharesTable();
			case 2:
				updateSharesTable();
		}
		return setCurrentVersion(3);
	}

	private void createOptionsTable() {
		var sql = "CREATE TABLE IF NOT EXISTS {0} ({1} INT NOT NULL, {2} VARCHAR(255) NOT NULL REFERENCES {3}({1}), {4} VARCHAR(255) NOT NULL, {5} TEXT, {6} INT DEFAULT 0, PRIMARY KEY ({1}, {2}))";
		try {
			var stmt = db.prepareStatement(format(sql,TABLE_OPTIONS, Field.ID,Field.POLL_ID,TABLE_POLLS,Field.NAME, Field.DESCRIPTION, Field.STATUS));
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw failedToCreateTable(TABLE_OPTIONS).causedBy(e);
		}
	}

	private void createPollsTable() {
		var sql = "CREATE TABLE IF NOT EXISTS {0} ({1} VARCHAR(255) NOT NULL PRIMARY KEY, {2} INT NOT NULL, {3} VARCHAR(255) NOT NULL, {4} TEXT, {5} BOOLEAN)";
		try {
			var stmt = db.prepareStatement(format(sql,TABLE_POLLS, Field.ID,Field.USER_ID,Field.NAME,Field.DESCRIPTION, Field.PRIVATE));
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw failedToCreateTable(TABLE_POLLS).causedBy(e);
		}
	}

	private void createSelectionsTable() {
		var sql = """
		CREATE TABLE IF NOT EXISTS {0} (
			{1} VARCHAR(255) NOT NULL,
			{2} INT NOT NULL,
			{3} INT NOT NULL,
			{4} INT NOT NULL,
			FOREIGN KEY ({1})	 REFERENCES {5}({7}),
			FOREIGN KEY ({1},{2}) REFERENCES {6}({1}, {7}),
			FOREIGN KEY ({1}, {4}) REFERENCES {8}({1}, {4}),
			PRIMARY KEY ({1}, {2}, {3}))""";
		try {
			var stmt = db.prepareStatement(format(sql,
					TABLE_SELECTIONS,
					POLL_ID, OPTION_ID, USER_ID, Field.WEIGHT,
					TABLE_POLLS, TABLE_OPTIONS, ID, TABLE_WEIGHTS));
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw failedToCreateTable(TABLE_SELECTIONS).causedBy(e);
		}
	}

	private void createSharesTable() {
		var sql = "CREATE TABLE IF NOT EXISTS {0} ({1} VARCHAR(255) NOT NULL REFERENCES {2}({3}), {4} INT NOT NULL, {5} INT, PRIMARY KEY ({1}, {4}))";
		try {
			var stmt = db.prepareStatement(format(sql,TABLE_SHARES,POLL_ID,TABLE_POLLS,ID,USER_ID, PERMISSION));
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw failedToCreateTable(TABLE_SHARES).causedBy(e);
		}
	}

	private void createWeightsTable(){
		var sql = "CREATE TABLE IF NOT EXISTS {0} ( {1} INT NOT NULL, {2} VARCHAR(255) NOT NULL REFERENCES {3}({4}), {5} TEXT, PRIMARY KEY ({2}, {1}))";
		try {
			var stmt = db.prepareStatement(format(sql,TABLE_WEIGHTS, WEIGHT,POLL_ID,TABLE_POLLS, ID, DESCRIPTION));
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw failedToCreateTable(TABLE_WEIGHTS).causedBy(e);
		}
	}

	private void dropWeight(Poll poll, int weight){
		try {
			delete().from(TABLE_WEIGHTS).where(POLL_ID, equal(poll.id())).where(WEIGHT, equal(weight)).execute(db);
			poll.weights().remove(weight);
		} catch (SQLException e){
			throw failedToDropObject(Text.WEIGHT);
		}
	}

	@Override
	public Collection<Poll> listPolls(UmbrellaUser user) {
		try {
			var sql = "SELECT DISTINCT {0}.*, {5} FROM {0} LEFT JOIN {1} ON {0}.{2} = {1}.{4} WHERE {0}.{7} IS FALSE OR {0}.{3} = ? OR {1}.{3} = ? ORDER BY {6} ASC;";
			var ps = db.prepareStatement(format(sql,TABLE_POLLS,TABLE_SHARES, ID, USER_ID, POLL_ID, PERMISSION, NAME, PRIVATE));
			ps.setLong(1,user.id());
			ps.setLong(2, user.id());
			var rs = ps.executeQuery();
			var map = new HashMap<String,Poll>();
			while (rs.next()) {
				var pollId = rs.getString(ID);
				if (map.containsKey(pollId)) continue;
				var poll = Poll.of(rs);
				var perm = rs.getInt(PERMISSION);
				if (perm != 0) poll.permissions().put(user,Permission.of(perm));
				map.put(pollId,poll);
			}
			rs.close();
			return map.values().stream().sorted(Comparator.comparing(Poll::name)).toList();
		} catch (SQLException sqle){
			throw failedToLoadObject(TABLE_POLLS);
		}
	}

	@Override
	public Poll.Evaluation loadEvaluation(Poll poll) {
		try {
			var result = new Poll.Evaluation(poll);
			var rs = select(ALL).from(TABLE_SELECTIONS).where(POLL_ID,equal(poll.id())).exec(db);
			while (rs.next()) result.count(rs);
			rs.close();
			return result;
		} catch (SQLException e) {
			throw failedToLoadObject(Text.EVALUATION);
		}
	}

	@Override
	public Poll loadPoll(String id) {
		try {
			var rs    = select(ALL).from(TABLE_POLLS).where(ID,equal(id)).exec(db);
			Poll poll = null;
			if (rs.next()) poll = Poll.of(rs);
			rs.close();
			if (poll == null) throw failedToLoadObject(Text.POLL,id);

			rs = select(ALL).from(TABLE_OPTIONS).where(POLL_ID,equal(id)).exec(db);
			while (rs.next()) poll.options().add(Poll.Option.of(rs));
			rs.close();

			rs = select(ALL).from(TABLE_WEIGHTS).where(POLL_ID,equal(id)).sort(WEIGHT+" ASC").exec(db);
			while (rs.next()) {
				var weight = rs.getInt(WEIGHT);
				var descr  = rs.getString(DESCRIPTION);
				poll.weights().put(weight,descr);
			}
			rs.close();

			rs = select(ALL).from(TABLE_SHARES).where(POLL_ID,equal(id)).exec(db);
			var permissions = new HashMap<Long,Permission>();
			while (rs.next()) permissions.put(rs.getLong(USER_ID),Permission.of(rs.getInt(PERMISSION)));
			rs.close();
			if (!permissions.isEmpty()) {
				var users = userService().list(null, null, permissions.keySet());
				for (var entry : users.entrySet()) poll.permissions().put(entry.getValue(), permissions.get(entry.getKey()));
			}
			return poll;
		} catch (SQLException e) {
			throw failedToLoadObject(Text.POLL,id).causedBy(e);
		}
	}

	@Override
	public Map<Integer, Integer> loadSelections(Poll poll, UmbrellaUser user) {
		try {
			var map = new HashMap<Integer,Integer>();
			var rs = select(ALL).from(TABLE_SELECTIONS).where(POLL_ID, equal(poll.id())).where(USER, equal(user.id())).exec(db);
			while (rs.next()) map.put(rs.getInt(OPTION_ID),rs.getInt(WEIGHT));
			rs.close();
			return map;
		} catch (SQLException e) {
			throw failedToLoadObject(Text.SELECTIONS);
		}

	}

	@Override
	public Poll save(Poll poll) {
			return is0(poll.id()) ? saveNew(poll) : update(poll);
	}

	public Poll.Option saveOption(String pollId, Poll.Option option) {
		try {
			if (option.isNew())   return saveNew(pollId,option);
			if (option.isDirty()) return update(pollId, option);
			return option;
		} catch (SQLException e){
			throw failedToStoreObject(Text.OPTION);
		}
	}

	private Poll saveNew(Poll poll) {
		var uuid = UUID.randomUUID().toString();
		try {
			insertInto(TABLE_POLLS,Field.ID, USER_ID, NAME, DESCRIPTION, PRIVATE)
					.values(uuid,poll.owner().id(),poll.name(),poll.description(),poll.isPrivate())
					.execute(db).close();
		} catch (SQLException e) {
			throw failedToStoreObject(poll);
		}
		return new Poll(uuid,poll.owner(),poll.name(),poll.description(),poll.isPrivate(), List.of(), Map.of());
	}

	private Poll.Option saveNew(String pollId, Poll.Option option) throws SQLException {
		insertInto(TABLE_OPTIONS, ID, POLL_ID, NAME, DESCRIPTION, STATUS)
				.values(option.id(), pollId, option.name(), option.description(), option.status())
				.execute(db).close();
		return option;
	}

	@Override
	public void saveSelection(Poll poll, Map<Integer, Integer> optionsToWeights, String guestName) {
		saveSelection(poll.id(), optionsToWeights, guestName);
	}

	@Override
	public void saveSelection(Poll poll, Map<Integer, Integer> optionsToWeights, UmbrellaUser user) {
		saveSelection(poll.id(), optionsToWeights, user.id());
	}

	private void saveSelection(String pollId, Map<Integer, Integer> optionsToWeights, Object editor) {

		var query = replaceInto(TABLE_SELECTIONS,POLL_ID,OPTION_ID,USER,WEIGHT);
		for (var entry : optionsToWeights.entrySet()){
			var optionId = entry.getKey();
			var weight   = entry.getValue();
			query.values(pollId,optionId,editor,weight);
		}
		try {
			query.execute(db).close();
		} catch (SQLException sqle){
			throw UmbrellaException.failedToStoreObject(Text.SELECTIONS);
		}
	}

	@Override
	public void setPermission(String pollId, UmbrellaUser user, Permission permission) {
		try {
			if (permission == null){
				delete().from(TABLE_SHARES).where(POLL_ID,equal(pollId)).where(USER_ID,equal(user.id())).execute(db);
			} else replaceInto(TABLE_SHARES, POLL_ID, USER_ID, PERMISSION)
					.values(pollId, user.id(), permission.code()).execute(db).close();
		} catch (SQLException e) {
			throw failedToStoreObject(Text.PERMISSION);
		}
	}

	private Poll update(Poll poll) {
		if (poll.isDirty(NAME, DESCRIPTION,PRIVATE)) try {
			replaceInto(TABLE_POLLS,ID,Field.USER_ID,NAME,DESCRIPTION,PRIVATE).values(poll.id(),poll.owner().id(),poll.name(),poll.description(),poll.isPrivate()).execute(db).close();
		} catch (SQLException e){
			throw failedToStoreObject(poll);
		}
		if (poll.isDirty(WEIGHTS)) try {
			Map.copyOf(poll.weights())
					.entrySet().stream()
					.filter(entry -> entry.getValue().isBlank())
					.map(Map.Entry::getKey)
					.forEach(w -> dropWeight(poll,w));
			var query = replaceInto(TABLE_WEIGHTS, POLL_ID, WEIGHT, DESCRIPTION);
			for (var entry : poll.weights().entrySet()){
				query.values(poll.id(),entry.getKey(),entry.getValue());
			}
			query.execute(db).close();
		} catch (SQLException e) {
			throw failedToStoreObject(poll);
		}
		for (var option : poll.options()) saveOption(poll.id(),option);
		return poll;
	}

	private Poll.Option update(String pollId, Poll.Option option) throws SQLException {
		if (option.name().isBlank()){
			delete().from(TABLE_OPTIONS).where(POLL_ID,equal(pollId)).where(ID,equal(option.id())).execute(db);
			return null;
		}
		replaceInto(TABLE_OPTIONS,POLL_ID, ID, NAME, DESCRIPTION, STATUS)
				.values(pollId, option.id(), option.name(), option.description(), option.status()).execute(db).close();
		return option;
	}

	private void updateSharesTable() {
		try {
			var newPermissions = new HashMap<String, Map<Long, Permission>>(); // Map from PollId → (Map from UserId → Permission)
			var rs = select(ALL).from(TABLE_SHARES).exec(db);
			while (rs.next()) {
				var pollID = rs.getString(POLL_ID);
				var userId = rs.getLong(USER_ID);
				var perm = rs.getInt(PERMISSION);
				var userMap = newPermissions.computeIfAbsent(pollID, k -> new HashMap<>());
				switch (perm) {
					case 0:
						userMap.put(userId, READ_ONLY);
						break;
					case 1:
					case 2:
						userMap.put(userId, Permission.EDIT);
						break;
					default:
						userMap.put(userId, null);
				}
			}
			rs.close();
			for (var pollEntry : newPermissions.entrySet()){
				var pollId = pollEntry.getKey();
				for (var userEntry : pollEntry.getValue().entrySet()){
					var userId     = userEntry.getKey();
					var permission = userEntry.getValue();
					if (permission == null) {
						Query.delete().from(TABLE_SHARES)
								.where(USER_ID, equal(userId))
								.where(POLL_ID, equal(pollId))
								.execute(db);
					} else {
						Query.update(TABLE_SHARES)
								.where(USER_ID, equal(userId))
								.where(POLL_ID, equal(pollId))
								.set(PERMISSION).prepare(db)
								.apply(permission.code()).close();
					}
				}
			}
		} catch (SQLException ex){
			throw databaseException("Failed to update permissions in shares table!");
		}
	}
}
