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

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.TIME_ID;
import static de.srsoftware.umbrella.core.constants.Text.TIME_WITH_ID;
import static de.srsoftware.umbrella.core.exceptions.UmbrellaException.*;
import static de.srsoftware.umbrella.core.model.Time.State.Complete;
import static de.srsoftware.umbrella.core.model.Translatable.t;
import static de.srsoftware.umbrella.time.Constants.*;
import static java.text.MessageFormat.format;

import de.srsoftware.tools.jdbc.Query;
import de.srsoftware.umbrella.core.BaseDb;
import de.srsoftware.umbrella.core.exceptions.UmbrellaException;
import de.srsoftware.umbrella.core.model.Time;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.*;

public class SqliteDb extends BaseDb implements TimeDb {


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

    @Override
    protected int createTables() {
        int currentVersion = createSettingsTable();
        switch (currentVersion){
            case 0:
                createTimesTable();
                createTaskTimesTable();
        }
        return setCurrentVersion(1);
    }

    private void createTaskTimesTable() {
        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_TIMES,TASK_ID,TIME_ID);
        try {
            var stmt = db.prepareStatement(sql);
            stmt.execute();
            stmt.close();
        } catch (SQLException e) {
			throw failedToCreateTable(TABLE_TASK_TIMES).causedBy(e);
        }
    }

    private void createTimesTable() {
        var sql = """
CREATE TABLE IF NOT EXISTS {0} (
    {1} INTEGER PRIMARY KEY,
    {2} INTEGER NOT NULL,
    {3} VARCHAR(255) NOT NULL,
    {4} TEXT,
    {5} TIMESTAMP,
    {6} TIMESTAMP,
    {7} INT NOT NULL DEFAULT {8}
)""";
        sql = format(sql,TABLE_TIMES, ID, USER_ID, SUBJECT, DESCRIPTION,START_TIME, END_TIME, STATE, Time.State.Started.code());
        try {
            var stmt = db.prepareStatement(sql);
            stmt.execute();
            stmt.close();
        } catch (SQLException e) {
			throw failedToCreateTable(TABLE_TIMES).causedBy(e);
        }
    }

	@Override
	public Long delete(long timeId) {
		try {
			db.setAutoCommit(false);
			Query.delete().from(TABLE_TASK_TIMES).where(TIME_ID,equal(timeId)).execute(db);
			Query.delete().from(TABLE_TIMES).where(ID,equal(timeId)).execute(db);
			db.setAutoCommit(false);
			return timeId;
		} catch (SQLException e) {
			throw failedToDropObject(t(TIME_WITH_ID, ID,timeId)).causedBy(e);
		}
	}

	@Override
	public Map<Long, Time> find(long userId, List<String> keys, boolean fulltext) {
		try {
			var query = select(ALL).from(TABLE_TIMES).where(USER_ID,equal(userId));
			if (fulltext) {
				for (var key : keys) query.where(format("CONCAT({0},\" \",{1})", SUBJECT, DESCRIPTION),like("%"+key+"%"));
			} else {
				for (var key : keys) query.where(SUBJECT,like("%"+key+"%"));
			}
			var rs = query.exec(db);
			var times = new HashMap<Long,Time>();
			while (rs.next()) {
				var time = Time.of(rs);
				times.put(time.id(),time);
			}
			rs.close();
			return times;
		} catch (Exception e) {
			throw failedToSearchDb(t(TIMES)).causedBy(e);
		}
	}

	@Override
	public HashMap<Long,Time> listTimes(Collection<Long> taskIds, boolean showClosed) throws UmbrellaException {
		try {
			var rs = select(ALL).from(TABLE_TASK_TIMES).where(TASK_ID,in(taskIds.toArray())).exec(db);
			var mapFromTimesToTasks = new HashMap<Long,HashSet<Long>>();
			while (rs.next()){
				var timeId = rs.getLong(TIME_ID);
				var taskId = rs.getLong(TASK_ID);
				mapFromTimesToTasks.computeIfAbsent(timeId, k -> new HashSet<>()).add(taskId);
			}
			rs.close();
			var query = select(ALL).from(TABLE_TIMES).where(ID,in(mapFromTimesToTasks.keySet().toArray()));
			if (!showClosed) query.where(STATE,lessThan(Complete.code()));
			rs = query.exec(db);
			var times = new HashMap<Long,Time>();
			while (rs.next()) {
				var time = Time.of(rs);
				time.taskIds().addAll(mapFromTimesToTasks.get(time.id()));
				times.put(time.id(),time);
			}
			rs.close();
			return times;
		} catch (Exception e) {
			throw databaseException(FAILED_TO_LIST_ENTITIES, TYPE,TIMES).causedBy(e);
		}
	}

	@Override
	public HashMap<Long, Time> listUserTimes(long userId, boolean showClosed) {
		try {
			var query = select(ALL).from(TABLE_TIMES).where(USER_ID,equal(userId));
			if (!showClosed) query.where(STATE,lessThan(Complete.code()));
			var rs = query.exec(db);
			var times = new HashMap<Long,Time>();
			while (rs.next()) {
				var time = Time.of(rs);
				times.put(time.id(),time);
			}
			rs.close();
			rs = select(ALL).from(TABLE_TASK_TIMES).where(TIME_ID,in(times.keySet().toArray())).exec(db);
			while (rs.next()){
				var time = times.get(rs.getLong(TIME_ID));
				time.taskIds().add(rs.getLong(TASK_ID));
			}
			rs.close();
			return times;
		} catch (SQLException e) {
			throw databaseException(FAILED_TO_LIST_ENTITIES, TYPE,TIMES).causedBy(e);
		}
	}

    @Override
    public Time load(long timeId) {
        try {
            var query = select(ALL).from(TABLE_TIMES).where(ID,equal(timeId));
            var rs = query.exec(db);
            Time time = null;
            if (rs.next()) time = Time.of(rs);
            rs.close();
            rs = select(ALL).from(TABLE_TASK_TIMES).where(TIME_ID,equal(timeId)).exec(db);
            while (rs.next()) time.taskIds().add(rs.getLong(TASK_ID));
            rs.close();
            if (time == null) throw notFound("No time found with id = {id}", ID,timeId);
            return time;
        } catch (SQLException e) {
			throw databaseException(FAILED_TO_LIST_ENTITIES, TYPE,TIMES).causedBy(e);
        }
    }

    @Override
	public Time save(Time track) throws UmbrellaException {
		try {
			if (track.id() == 0) { // create new
				var rs = insertInto(TABLE_TIMES, USER_ID, SUBJECT, DESCRIPTION, START_TIME, END_TIME, STATE)
						.values(track.userId(), track.subject(), track.description(), track.start(), track.end(), track.state().code())
						.execute(db)
						.getGeneratedKeys();
				if (rs.next()) track.setId(rs.getLong(1));
				rs.close();
			} else { // update
				replaceInto(TABLE_TIMES, ID, USER_ID, SUBJECT, DESCRIPTION, START_TIME, END_TIME, STATE)
						.values(track.id(), track.userId(), track.subject(), track.description(), track.start(), track.end(), track.state().code())
						.execute(db).close();
			}
			var query = replaceInto(TABLE_TASK_TIMES,TIME_ID,TASK_ID);
			for (var taskId : track.taskIds()) query.values(track.id(),taskId);
			query.execute(db).close();
			return track;
		} catch (SQLException e){
			throw failedToStoreObject("time").causedBy(e);
		}
	}

	@Override
	public void updateStates(Collection<Long> timeIds, Time.State timeState) {
		try {
			update(TABLE_TIMES)
					.set(STATE)
					.where(ID,in(timeIds.toArray()))
					.prepare(db)
					.apply(timeState.code())
					.close();
		} catch (SQLException e) {
			throw databaseException(FAILED_TO_UPDATE_OBJECT, OBJECT,TIMES).causedBy(e);
		}
	}
}
