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


import static de.srsoftware.tools.Optionals.is0;
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.Field.*;
import static de.srsoftware.umbrella.core.constants.Field.TASK;
import static de.srsoftware.umbrella.core.constants.Field.TYPE;
import static de.srsoftware.umbrella.core.constants.Text.*;
import static de.srsoftware.umbrella.core.constants.Text.TASKS;
import static de.srsoftware.umbrella.core.exceptions.UmbrellaException.*;
import static de.srsoftware.umbrella.core.exceptions.UmbrellaException.failedToCreateTable;
import static de.srsoftware.umbrella.core.model.Status.*;
import static de.srsoftware.umbrella.core.model.Translatable.t;
import static de.srsoftware.umbrella.project.Constants.*;
import static de.srsoftware.umbrella.task.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.Text;
import de.srsoftware.umbrella.core.exceptions.UmbrellaException;
import de.srsoftware.umbrella.core.model.*;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.*;


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

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

	protected int createTables() {
		int currentVersion = createSettingsTable();
		switch (currentVersion){
			case 0:
				createTaskTable();
				createTasksUsersTable();
			case 1:
				swapStates(TABLE_TASKS);
			case 2:
				createDependencyTable();
			case 3:
				createPriorityColumn();
		}

		return setCurrentVersion(4);
	}

	private void createDependencyTable() {
		var sql = "CREATE TABLE IF NOT EXISTS {0} ({1} INT NOT NULL, {2} INT NOT NULL, PRIMARY KEY({1}, {2}))";
		sql = format(sql,TABLE_TASK_DEPENDENCIES,TASK_ID,REQUIRED_TASK_ID);
		try {
			var stmt = db.prepareStatement(sql);
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw failedToCreateTable(TABLE_TASK_DEPENDENCIES).causedBy(e);
		}
	}

	private void createPriorityColumn() {
		var sql = format("ALTER TABLE {0} ADD {1} INT NOT NULL DEFAULT 0",TABLE_TASKS, PRIORITY);
		try {
			db.prepareStatement(sql).execute();
		} catch (SQLException e) {
			throw databaseException(FAILED_TO_ADD_COLUMN, NAME, PRIORITY, TABLE,TABLE_TASKS).causedBy(e);
		}
	}

	private void createTaskTable() {
		var createTable = """
				CREATE TABLE IF NOT EXISTS "{0}" (
					{1} INTEGER PRIMARY KEY,
					{2} INTEGER NOT NULL,
					{3} INTEGER DEFAULT NULL,
					{4} VARCHAR(255) NOT NULL,
					{5} TEXT,
					{6} INT DEFAULT {7},
					{8} DOUBLE DEFAULT NULL,
					{9} DATE,
					{10} DATE,
					{11} BOOLEAN DEFAULT FALSE,
					{12} BOOLEAN DEFAULT FALSE
				)""";
		try {
			var stmt = db.prepareStatement(format(createTable, TABLE_TASKS, ID, PROJECT_ID, PARENT_TASK_ID, NAME, DESCRIPTION, STATUS, OPEN.code(), EST_TIME, START_DATE, DUE_DATE, SHOW_CLOSED, NO_INDEX));
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw failedToCreateTable(TABLE_PROJECTS).causedBy(e);
		}
	}

	private void createTasksUsersTable(){
		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_TASKS_USERS, TASK_ID, USER_ID, PERMISSIONS));
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw failedToCreateTable(TABLE_PROJECT_USERS).causedBy(e);
		}
	}

	@Override
	public void delete(Task task) throws UmbrellaException {
		try {
			var rs = select("count(*)").from(TABLE_TASKS).where(PARENT_TASK_ID,equal(task.id())).exec(db);
			long count = 0;
			if (rs.next()) count = rs.getLong(1);
			rs.close();
			if (count>0) throw unprocessable("Task \"{task}\" has child tasks and thus cannot be deleted!", TASK,task.name());
			Query.delete().from(TABLE_TASKS).where(ID,equal(task.id())).execute(db);
			Query.delete().from(TABLE_TASKS_USERS).where(TASK_ID,equal(task.id())).execute(db);
		} catch (SQLException e) {
			throw failedToDropObject(task).causedBy(e);
		}
	}

	@Override
	public void dropMember(long taskId, long userId) {
		try {
			Query.delete().from(TABLE_TASKS_USERS)
					.where(TASK_ID,equal(taskId))
					.where(USER_ID,equal(userId))
					.execute(db);
		} catch (SQLException e) {
			throw failedToDropObjectFromObject(USER_ID,userId,t(Text.TASK),taskId).causedBy(e);
		}
	}

	@Override
	public HashMap<Long, Task> find(long userId, List<String> keys, boolean fulltext) {
		try {
			var tasks = new HashMap<Long,Task>();
			var query = select(ALL).from(TABLE_TASKS).leftJoin(ID,TABLE_TASKS_USERS,TASK_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 task = Task.of(rs);
				tasks.put(task.id(),task);
			}
			rs.close();
			return tasks;
		} catch (SQLException e){
			throw databaseException(FAILED_TO_LOAD_ENTITIES_OF_OWNER, TYPE,t(TASKS), OWNER,t(Text.USER)).causedBy(e);
		}
	}

	@Override
	public Map<Long, Permission> getMembers(Task task) {
		try {
			var result = new HashMap<Long,Permission>();
			var rs = select(ALL).from(TABLE_TASKS_USERS).where(TASK_ID,equal(task.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 failedToLoadObject("task members").causedBy(e);
		}
	}

	public Map<Long, Task> listTasks(Collection<Long> projectIds) throws UmbrellaException {
		try {
			var tasks = new HashMap<Long,Task>();
			var rs = select(ALL).from(TABLE_TASKS).where(PROJECT_ID, in(projectIds.toArray())).exec(db);
			while (rs.next()){
				var task = Task.of(rs);
				tasks.put(task.id(),task);
			}
			rs.close();
			return loadDependencies(tasks);
		} catch (SQLException e) {
			throw databaseException(FAILED_TO_LOAD_ENTITIES_OF_OWNER, TYPE,t(TASKS), OWNER,"project_ids").causedBy(e);
		}
	}

	public Map<Long, Task> listRootTasks(Long projectId, UmbrellaUser user, boolean showClosed) {
		try {
			var tasks = new HashMap<Long,Task>();
			var query = select(ALL).from(TABLE_TASKS).leftJoin(ID,TABLE_TASKS_USERS,TASK_ID)
					.where(PROJECT_ID,equal(projectId))
					.where(USER_ID,equal(user.id()))
					.where(PARENT_TASK_ID,isNull());
			if (!showClosed) query.where(STATUS,lessThan(COMPLETE.code()));
			var rs = query.exec(db);
			while (rs.next()){
				var task = Task.of(rs);
				tasks.put(task.id(),task);
			}
			rs.close();
			return loadDependencies(tasks);
		} catch (SQLException e){
			throw databaseException(FAILED_TO_LOAD_ENTITIES_OF_OWNER, TYPE,t(TASKS), OWNER, t(PROJECT_WITH_ID, ID,projectId)).causedBy(e);
		}
	}

	public Map<Long, Task> listChildrenOf(Long parentTaskId, UmbrellaUser user, boolean showClosed) {
		try {
			var tasks = new HashMap<Long,Task>();
			var query = select(ALL).from(TABLE_TASKS).leftJoin(ID,TABLE_TASKS_USERS,TASK_ID)
					.where(PARENT_TASK_ID,equal(parentTaskId))
					.where(USER_ID,equal(user.id()));
			if (!showClosed) query.where(STATUS,lessThan(COMPLETE.code()));
			var rs = query.exec(db);
			while (rs.next()){
				var task = Task.of(rs);
				tasks.put(task.id(),task);
			}
			rs.close();
			return loadDependencies(tasks);
		} catch (SQLException e){
			throw databaseException(FAILED_TO_LOAD_ENTITIES_OF_OWNER, TYPE,"child tasks", OWNER,parentTaskId).causedBy(e);
		}
	}

	@Override
	public Map<Long, Task> listProjectTasks(Long projectId, Long parentTaskId, boolean noIndex) throws UmbrellaException {
		try {
			var query = select(ALL).from(TABLE_TASKS).where(PROJECT_ID,equal(projectId));
			if (!is0(parentTaskId)) query.where(PARENT_TASK_ID,equal(parentTaskId));
			if (!noIndex) query.where(NO_INDEX,notIn(1));
			var tasks = new HashMap<Long,Task>();
			var rs = query.exec(db);
			while (rs.next()){
				var task = Task.of(rs);
				tasks.put(task.id(),task);
			}
			rs.close();
			return loadDependencies(tasks);
		} catch (SQLException e){
			throw databaseException(FAILED_TO_LOAD_ENTITIES_OF_OWNER, TYPE,t(TASKS), OWNER, t(PROJECT_WITH_ID, ID,projectId)).causedBy(e);
		}
	}

	@Override
	public List<Task> listUserTasks(long userId, Long limit, long offset, boolean showClosed) {
		try {
			var query = select(ALL).from(TABLE_TASKS).leftJoin(ID,TABLE_TASKS_USERS,TASK_ID).where(USER_ID,equal(userId));
			if (!showClosed) query.where(STATUS,lessThan(COMPLETE.code()));
			var rs = query.sort("(CASE due_date WHEN \"\" THEN '9999-99-99' ELSE IFNULL(due_date,'9999-99-99') END), status COLLATE NOCASE").limit(limit).skip(offset).exec(db);
			var map = new ArrayList<Task>();
			while (rs.next()) map.add(Task.of(rs));
			rs.close();
			return map;
		} catch (SQLException e) {
			throw databaseException(FAILED_TO_LOAD_ENTITIES_OF_OWNER, TYPE,t(TASKS), OWNER, t(USER_WITH_ID, ID,userId)).causedBy(e);
		}
	}

	@Override
	public Task load(long taskId) throws UmbrellaException {
		var map = load(List.of(taskId));
		var task = map.get(taskId);
		if (task == null) throw notFound(FAILED_TO_LOAD_OBJECT_BY_ID, OBJECT,t(Text.TASK), ID,taskId);
		return task;
	}

	@Override
	public Map<Long, Task> load(Collection<Long> taskIds) throws UmbrellaException {
		try {
			var rs = select(ALL).from(TABLE_TASKS).where(ID, in(taskIds.toArray())).exec(db);
			var map = new HashMap<Long, Task>();
			while (rs.next()) {
				var task = Task.of(rs);
				map.put(task.id(),task);
			}
			rs.close();
			return loadDependencies(map);
		} catch (SQLException e) {
			throw failedToLoadObject(t(TASKS)).causedBy(e);
		}
	}

	private Map<Long, Task> loadDependencies(Map<Long, Task> tasks) {
		try {
			var rs = select(ALL).from(TABLE_TASK_DEPENDENCIES).where(TASK_ID,in(tasks.keySet().toArray())).exec(db);
			while (rs.next()){
				var taskId         = rs.getLong(TASK_ID);
				var requiredTaskId = rs.getLong(REQUIRED_TASK_ID);
				tasks.get(taskId).requiredTasksIds().add(requiredTaskId);
			}
			rs.close();
			return tasks;
		} catch (SQLException e) {
			throw failedToLoadObject("task dependencies").causedBy(e);
		}
	}

	@Override
	public Task save(Task task) {
		try {
			if (task.id() == 0){ // new task
				var rs = insertInto(TABLE_TASKS, PROJECT_ID, PARENT_TASK_ID, NAME, DESCRIPTION, STATUS, EST_TIME, START_DATE, DUE_DATE, SHOW_CLOSED, NO_INDEX, PRIORITY)
						.values(task.projectId(),task.parentTaskId(),task.name(),task.description(),task.status(),task.estimatedTime(),task.start(),task.dueDate(),task.showClosed(),task.noIndex(),task.priority())
						.execute(db)
						.getGeneratedKeys();
				Long taskId = null;
				if (rs.next()) taskId=rs.getLong(1);
				rs.close();
				if (taskId == null) throw failedToStoreObject(task.name());
				return new Task(taskId,task.projectId(),task.parentTaskId(),task.name(),task.description(),task.status(),task.estimatedTime(),task.start(),task.dueDate(),task.showClosed(),task.noIndex(),task.members(), task.priority());
			}
			if (task.isDirty(MEMBERS)){
				var query = replaceInto(TABLE_TASKS_USERS,TASK_ID, USER_ID,PERMISSIONS);
				for (var member : task.members().entrySet()) query.values(task.id(),member.getKey(),member.getValue().permission().code());
				query.execute(db).close();
				task.clean(MEMBERS);
			}
			if (task.isDirty(REQUIRED_TASKS_IDS)) {
				var dropQuery = Query.delete().from(TABLE_TASK_DEPENDENCIES).where(TASK_ID,equal(task.id()));
				if (!task.requiredTasksIds().isEmpty()){
					dropQuery.where(REQUIRED_TASK_ID,notIn(task.requiredTasksIds().toArray()));
					db.setAutoCommit(false);
				}
				dropQuery.execute(db);
				if (!task.requiredTasksIds().isEmpty()) {
					var addQuery = replaceInto(TABLE_TASK_DEPENDENCIES, TASK_ID, REQUIRED_TASK_ID);
					for (var reqId : task.requiredTasksIds()) addQuery.values(task.id(), reqId);
					addQuery.execute(db).close();
					db.setAutoCommit(true);
				}

				task.clean(REQUIRED_TASKS_IDS);
			}
			if (task.isDirty()) {
				update(TABLE_TASKS).set(PROJECT_ID, PARENT_TASK_ID, NAME, DESCRIPTION, STATUS, EST_TIME, START_DATE, DUE_DATE, SHOW_CLOSED, NO_INDEX, PRIORITY)
						.where(ID,equal(task.id())).prepare(db)
						.apply(task.projectId(),task.parentTaskId(),task.name(),task.description(),task.status(),task.estimatedTime(),task.start(),task.dueDate(),task.showClosed(),task.noIndex(),task.priority())
						.close();
				task.clean();
			}
			return task;
		} catch (SQLException e){
			throw failedToStoreObject(task.name()).causedBy(e);
		}
	}

	@Override
	public void setMember(long taskId, long userId, Permission permission) {
		try {
			replaceInto(TABLE_TASKS_USERS,TASK_ID, USER_ID,PERMISSIONS).values(taskId,userId,permission.code()).execute(db).close();
		} catch (SQLException e) {
			throw failedToStoreObject(PERMISSIONS).causedBy(e);
		}
	}
}
