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

import static de.srsoftware.tools.jdbc.Condition.*;
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.Constants.TABLE_SETTINGS;
import static de.srsoftware.umbrella.core.constants.Field.*;
import static de.srsoftware.umbrella.core.constants.Field.PROJECT;
import static de.srsoftware.umbrella.core.constants.Field.TYPE;
import static de.srsoftware.umbrella.core.constants.Text.*;
import static de.srsoftware.umbrella.core.exceptions.UmbrellaException.*;
import static de.srsoftware.umbrella.core.model.Status.COMPLETE;
import static de.srsoftware.umbrella.core.model.Status.OPEN;
import static de.srsoftware.umbrella.core.model.Translatable.t;
import static de.srsoftware.umbrella.project.Constants.*;
import static java.text.MessageFormat.format;

import de.srsoftware.umbrella.core.BaseDb;
import de.srsoftware.umbrella.core.constants.Field;
import de.srsoftware.umbrella.core.exceptions.UmbrellaException;
import de.srsoftware.umbrella.core.model.*;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashMap;
import java.util.Map;
import org.json.JSONObject;

public class SqliteDb extends BaseDb implements ProjectDb {

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

	private void createProjectTable() {
		var createTable = """
				CREATE TABLE IF NOT EXISTS {0} (
					`{1}` INTEGER PRIMARY KEY,
					`{2}` INTEGER,
					`{3}` VARCHAR(255) NOT NULL,
					`{4}` TEXT,
					`{5}` INT DEFAULT {6},
					`{7}` BOOLEAN DEFAULT 0
				)""";
		try {
			var stmt = db.prepareStatement(format(createTable, TABLE_PROJECTS, ID, COMPANY_ID, NAME, DESCRIPTION, STATUS, OPEN.code(), SHOW_CLOSED));
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw failedToCreateTable(TABLE_PROJECTS).causedBy(e);
		}
	}

	private void createStatesTable(){
		var sql = """
CREATE TABLE IF NOT EXISTS custom_states (
project_id LONG NOT NULL,
code INT NOT NULL,
name VARCHAR(64) NOT NULL,
PRIMARY KEY (project_id, code)
);
""";
		try {
			var stmt = db.prepareStatement(format(sql));
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw failedToCreateTable(TABLE_CUSTOM_STATES).causedBy(e);
		}

	}

	protected int createTables() {
		int currentVersion = createSettingsTable();
		switch (currentVersion){
			case 0:
				createProjectTable();
				createUsersTable();
			case 1:
				createStatesTable();
			case 2:
				swapStates(TABLE_PROJECTS);
		}
		return setCurrentVersion(3);
	}

	private void createUsersTable(){
		var createTable = """
CREATE TABLE IF NOT EXISTS {0} (
	{1} INT NOT NULL,
	{2} INT NOT NULL,
	{3} INT DEFAULT {3},
	PRIMARY KEY ({1}, {2})
)""";
		try {
			var stmt = db.prepareStatement(format(createTable,TABLE_PROJECT_USERS, PROJECT_ID, USER_ID, PERMISSIONS));
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw failedToCreateTable(TABLE_PROJECT_USERS).causedBy(e);
		}
	}


	@Override
	public void dropMember(long projectId, long userId) {
		try {
			delete().from(TABLE_PROJECT_USERS)
					.where(PROJECT_ID,equal(projectId))
					.where(USER_ID,equal(userId))
					.execute(db);
		} catch (SQLException e) {
			throw failedToDropObjectFromObject("member",userId,t(PROJECT),projectId).causedBy(e);
		}
	}

	@Override
	public Map<Long, Permission> getMembers(Project project) {
		try {
			var result = new HashMap<Long,Permission>();
			var rs = select(ALL).from(TABLE_PROJECT_USERS).where(PROJECT_ID,equal(project.id())).exec(db);
			while (rs.next()) result.put(rs.getLong(USER_ID),Permission.of(rs.getInt(PERMISSIONS)));
			rs.close();
			return result;
		} catch (SQLException e){
			throw failedToLoadMembers(t(PROJECT_WITH_ID,ID,project.name())).causedBy(e);
		}
	}

	@Override
	public Project load(long projectId) throws UmbrellaException {
		try {
			var rs = select(ALL).from(TABLE_PROJECTS).where(ID, equal(projectId)).exec(db);
			Project project = null;
			if (rs.next()) project = Project.of(rs);
			rs.close();

			if (project == null) throw notFound("No project for id {id}", ID,projectId);

			rs = select(ALL).from(TABLE_CUSTOM_STATES).where(PROJECT_ID,equal(projectId)).exec(db);
			var states = project.allowedStates();
			while (rs.next()) states.add(Status.of(rs));
			rs.close();

			rs = select(VALUE).from(TABLE_SETTINGS).where(KEY,equal(colorKey(projectId))).exec(db);
			if (rs.next()) {
				var map = project.tagColors();
				new JSONObject(rs.getString(VALUE)).toMap().forEach((k, v) -> map.put(k.toLowerCase(), v.toString()));
			}
			rs.close();
			return project;
		} catch (SQLException e) {
			throw failedToLoadObject(t(PROJECT),projectId).causedBy(e);
		}
	}

	private String colorKey(long projectId) {
		return "tag_colors:"+projectId;
	}

	@Override
	public Map<Long, Project> ofCompany(long companyId, boolean includeClosed) throws UmbrellaException {
		try {
			var projects = new HashMap<Long,Project>();
			var query = select(ALL).from(TABLE_PROJECTS).where(COMPANY_ID, equal(companyId));
			if (!includeClosed) query = query.where(STATUS,lessThan(COMPLETE.code()));
			var rs = query.exec(db);
			while (rs.next()){
				var project = Project.of(rs);
				projects.put(project.id(),project);
			}
			rs.close();
			return projects;
		} catch (SQLException e) {
			throw failedToLoadObject("items").causedBy(e);
		}
	}

	@Override
	public Map<Long, Project> find(long userId, Collection<String> keys, boolean fulltext) {
		try {
			var projects = new HashMap<Long,Project>();
			var query = select(ALL).from(TABLE_PROJECTS).leftJoin(ID,TABLE_PROJECT_USERS, PROJECT_ID).where(USER_ID, equal(userId));
			if (fulltext) {
				for (var key : keys) query.where(format("CONCAT({0},\" \",{1})", NAME, DESCRIPTION),like("%"+key+"%"));
			} else {
				for (var key : keys) query.where(NAME,like("%"+key+"%"));
			}
			var rs = query.exec(db);
			while (rs.next()){
				var project = Project.of(rs);
				projects.put(project.id(),project);
			}
			rs.close();
			return projects;
		} catch (SQLException e) {
			throw databaseException(FAILED_TO_LIST_ENTITIES, TYPE,t(ITEMS)).causedBy(e);
		}
	}

	@Override
	public Map<Long, Project> ofUser(long userId, boolean includeClosed) throws UmbrellaException {
		try {
			var projects = new HashMap<Long,Project>();
			var query = select(ALL).from(TABLE_PROJECTS).leftJoin(ID,TABLE_PROJECT_USERS, PROJECT_ID).where(USER_ID, equal(userId));
			if (!includeClosed) query = query.where(STATUS,lessThan(COMPLETE.code()));
			var rs = query.exec(db);
			while (rs.next()){
				var project = Project.of(rs);
				projects.put(project.id(),project);
			}
			rs.close();
			return projects;
		} catch (SQLException e) {
			throw databaseException(FAILED_TO_LIST_ENTITIES, t("projects")).causedBy(e);
		}
	}

	@Override
	public Project save(Project prj, UmbrellaUser user) throws UmbrellaException {
			if (prj.id() == 0) { // new
				try {
					var stmt = insertInto(TABLE_PROJECTS, NAME, DESCRIPTION, STATUS, COMPANY_ID, SHOW_CLOSED).values(prj.name(), prj.description(), prj.status(), prj.companyId().orElse(null), prj.showClosed()).execute(db);
					var rs = stmt.getGeneratedKeys();
					var id = rs.next() ? rs.getLong(1) : null;
					rs.close();
					if (id != null){
						if (!prj.members().isEmpty()) {
							var query = insertInto(TABLE_PROJECT_USERS, PROJECT_ID, USER_ID, PERMISSIONS);
							for (var member : prj.members().entrySet()) query.values(id, member.getKey(), member.getValue().permission().code());
							query.execute(db).close();
						}
						return new Project(id, prj.name(), prj.description(),prj.status(),prj.companyId().orElse(null),prj.showClosed(),prj.members(),prj.allowedStates());
					}
				} catch (SQLException e) {
					throw failedToStoreObject(prj).causedBy(e);
				}
			} else { // Update
				try {
					if (prj.isDirty(MEMBERS)){
						var query = replaceInto(TABLE_PROJECT_USERS, PROJECT_ID, USER_ID,PERMISSIONS);
						for (var member : prj.members().entrySet()) query.values(prj.id(),member.getKey(),member.getValue().permission().code());
						query.execute(db).close();
						prj.clean(MEMBERS);
					}
					if (prj.isDirty(TAG_COLORS)){
						replaceInto(TABLE_SETTINGS, KEY,VALUE).values(colorKey(prj.id()),new JSONObject(prj.tagColors()).toString()).execute(db).close();
						prj.clean(TAG_COLORS);
					}
					if (prj.isDirty()){
						update(TABLE_PROJECTS).set(NAME, DESCRIPTION, STATUS, COMPANY_ID, SHOW_CLOSED).where(ID,equal(prj.id())).prepare(db)
								.apply(prj.name(),prj.description(),prj.status(),prj.companyId().orElse(null),prj.showClosed())
								.execute();
						prj.clean();
					}
					return prj;
				} catch (SQLException e) {
					throw databaseException(FAILED_TO_UPDATE_OBJECT, t(PROJECT_WITH_ID, ID,prj.name())).causedBy(e);
				}
			}
			return null;
	}


	@Override
	public Status save(long projectId, Status newState) {
		try {
			replaceInto(TABLE_CUSTOM_STATES, PROJECT_ID, Field.CODE, NAME).values(projectId,newState.code(),newState.name()).execute(db).close();
			return newState;
		} catch (SQLException e) {
			throw databaseException(FAILED_TO_CREATE_STATE).causedBy(e);
		}
	}
}
