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

import static de.srsoftware.tools.jdbc.Condition.equal;
import static de.srsoftware.tools.jdbc.Condition.in;
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.userService;
import static de.srsoftware.umbrella.core.constants.Constants.COUNT;
import static de.srsoftware.umbrella.core.constants.Field.*;
import static de.srsoftware.umbrella.core.exceptions.UmbrellaException.*;
import static de.srsoftware.umbrella.core.model.Translatable.t;
import static de.srsoftware.umbrella.message.Constants.*;
import static de.srsoftware.umbrella.message.model.Schedule.schedule;
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 de.srsoftware.umbrella.message.model.Instantly;
import de.srsoftware.umbrella.message.model.Settings;
import de.srsoftware.umbrella.message.model.Silent;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.stream.Stream;

public class SqliteMessageDb extends BaseDb implements MessageDb, MessageQueue<TranslatedMessage> {
	private static final System.Logger LOG = System.getLogger(SqliteMessageDb.class.getSimpleName());

	public SqliteMessageDb(Connection conn){
		super(conn);
	}

	private void createMessageTables() {
		var sql = """
	CREATE TABLE IF NOT EXISTS {0} (
		{1} INTEGER PRIMARY KEY,
		{2} LONG NOT NULL,
		{3} LONG NOT NULL,
		{4} TEXT,
		{5} TEXT
	);
	""";
		sql = format(sql,TABLE_MESSAGES, ID, TIMESTAMP, SENDER_USER_ID, SUBJECT, BODY);
		try {
			db.prepareStatement(sql).execute();
		} catch (SQLException e) {
			throw failedToCreateTable(TABLE_MESSAGES);
		}

		sql = """
		CREATE TABLE IF NOT EXISTS {0} (
			{1} INTEGER NOT NULL,
			{2} VARCHAR(255) NOT NULL,
			{3} VARCHAR(255),
			PRIMARY KEY ({1}, {2})
		);
		""";
		sql = format(sql,TABLE_RECEIVERS, MESSAGE_ID, EMAIL, NAME);
		try {
			db.prepareStatement(sql).execute();
		} catch (SQLException e) {
			throw failedToCreateTable(TABLE_MESSAGES);
		}
		sql = """
		CREATE TABLE IF NOT EXISTS {0} (
			{1} INTEGER NOT NULL,
			{2} VARCHAR(255) NOT NULL,
			{3} VARCHAR(100),
			{4} BLOB NOT NULL,
			PRIMARY KEY ({1}, {2})
		);
		""";
		sql = format(sql,TABLE_ATTACHMENTS, MESSAGE_ID, NAME, MIME, DATA);
		try {
			db.prepareStatement(sql).execute();
		} catch (SQLException e) {
			throw failedToCreateTable(TABLE_MESSAGES);
		}
	}

	private void createSubmissionTable() {
		var createTable = """
CREATE TABLE IF NOT EXISTS {0} ( {1} INTEGER PRIMARY KEY, {2} VARCHAR(255) NOT NULL);
""";
		try {
			var stmt = db.prepareStatement(format(createTable,TABLE_SUBMISSIONS, USER_ID, VALUE));
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw failedToCreateTable(TABLE_SUBMISSIONS).causedBy(e);
		}
	}

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

	@Override
	public Optional<Envelope<TranslatedMessage>> getEnvelope(long messageId) {
		try {
			var attachments = new ArrayList<Attachment>();
			var rs = select(ALL).from(TABLE_ATTACHMENTS).where(MESSAGE_ID,equal(messageId)).exec(db);
			while (rs.next()) attachments.add(new Attachment(rs.getString(NAME),rs.getString(MIME),rs.getBytes(DATA)));
			rs.close();

			var receivers = new ArrayList<User>();
			rs = select(ALL).from(TABLE_RECEIVERS).where(MESSAGE_ID,equal(messageId)).exec(db);
			while (rs.next()) receivers.add(new User(rs.getString(NAME),new EmailAddress(rs.getString(EMAIL)),null));
			rs.close();

			Envelope<TranslatedMessage> envelope = null;
			rs = select(ALL).from(TABLE_MESSAGES).where(ID,equal(messageId)).exec(db);
			if (rs.next()){
				var sender = userService().loadUser(rs.getLong(SENDER_USER_ID));
				var msg = new TranslatedMessage(sender,rs.getString(SUBJECT),rs.getString(BODY), attachments);
				msg.utcTime(rs.getLong(TIMESTAMP));
				envelope = new Envelope<>(messageId, msg, receivers);
			}
			rs.close();
			if (envelope != null) return Optional.of(envelope);
			return Optional.empty();
		} catch (SQLException e) {
			throw failedToLoadObject(Text.MESSAGE,messageId);
		}
	}

	@Override
	public List<Envelope<TranslatedMessage>> getEnvelopesFor(User user) {
		try {
			var messageIds = new ArrayList<Long>();
			var rs = select(MESSAGE_ID).from(TABLE_RECEIVERS).where(EMAIL,equal(user.email().toString())).where(NAME,equal(user.name())).exec(db);
			while (rs.next()) messageIds.add(rs.getLong(MESSAGE_ID));
			rs.close();

			var attachments = new HashMap<Long,Collection<Attachment>>();
			rs = select(ALL).from(TABLE_ATTACHMENTS).where(MESSAGE_ID,in(messageIds.toArray())).exec(db);
			while (rs.next()){
				var attachment = new Attachment(rs.getString(NAME),rs.getString(MIME),rs.getBytes(DATA));
				var messageId = rs.getLong(MESSAGE_ID);
				attachments.computeIfAbsent(messageId, k -> new ArrayList<>()).add(attachment);
			}
			rs.close();

			var envelopes = new ArrayList<Envelope<TranslatedMessage>>();
			rs = select(ALL).from(TABLE_MESSAGES).where(ID,in(messageIds.toArray())).exec(db);
			while (rs.next()){
				var messageId = rs.getLong(ID);
				var sender = userService().loadUser(rs.getLong(SENDER_USER_ID));
				var msg = new TranslatedMessage(sender,rs.getString(SUBJECT),rs.getString(BODY), attachments.get(messageId));
				msg.utcTime(rs.getLong(TIMESTAMP));
				var envelope = new Envelope<>(messageId, msg, user);
				envelopes.add(envelope);
			}
			rs.close();
			return envelopes;
		} catch (SQLException e) {
			throw failedToLoadObject(t(Text.EMAILS_FOR_RECEIVER,EMAIL,user.email()));
		}
	}

	@Override
	public Stream<User> getReceivers() {
		try {
			var receivers = new ArrayList<User>();
			var rs = select(format("DISTINCT {0}, {1}",EMAIL,NAME)).from(TABLE_RECEIVERS).exec(db);
			while (rs.next()){
				var email = new EmailAddress(rs.getString(EMAIL));
				receivers.add(new User(rs.getString(NAME),email,null));
			}
			rs.close();
			return receivers.stream();
		} catch (SQLException e) {
			throw failedToLoadObject(t(Text.RECEIVERS));
		}
	}

	@Override
	public Settings getSettings(UmbrellaUser user) throws UmbrellaException {
		try {
			Settings settings = null;
			var rs = select(VALUE).from(TABLE_SUBMISSIONS).where(USER_ID,equal(user.id())).exec(db);
			if (rs.next()) settings = toSettings(rs);
			rs.close();
			if (settings != null) return settings;
			throw new UmbrellaException(500,"No submission settings stored for {0}",user);
		} catch (SQLException e) {
			throw databaseException(FAILED_TO_LOAD_ENTITIES_OF_OWNER, TYPE,t(Text.SETTINGS), OWNER,user).causedBy(e);
		}
	}

	@Override
	public Optional<Envelope<TranslatedMessage>> markRead(long messageId, User user) {
		try {
			var envelope = getEnvelope(messageId);
			Query.delete().from(TABLE_RECEIVERS).where(MESSAGE_ID,equal(messageId)).where(EMAIL,equal(user.email().toString())).execute(db);
			var rs = select(COUNT).from(TABLE_RECEIVERS).where(MESSAGE_ID,equal(messageId)).exec(db);
			Integer count = null;
			if (rs.next()) count = rs.getInt(1);
			rs.close();
			if (count != null && count == 0){
				delete().from(TABLE_ATTACHMENTS).where(MESSAGE_ID,equal(messageId)).execute(db);
				delete().from(TABLE_MESSAGES).where(ID,equal(messageId)).execute(db);
			}

			return envelope;
		} catch (SQLException e) {
			throw failedToDropObject(Text.RECEIVER);
		}
	}

	@Override
	public void push(Envelope<TranslatedMessage> envelope) {
		var message   = envelope.message();
		var timestamp = message.utcTime();
		var sender    = message.sender().id();
		var subject   = message.subject();
		var body      = message.body();
		try {
			var rs = insertInto(TABLE_MESSAGES, TIMESTAMP, SENDER_USER_ID, SUBJECT, BODY).values(timestamp, sender, subject, body).execute(db).getGeneratedKeys();
			Long messageId = null;
			if (rs.next()) messageId = rs.getLong(1);
			rs.close();
			if (messageId == null) throw failedToStoreObject(envelope);

			for (var receiver : envelope.receivers()){
				insertInto(TABLE_RECEIVERS,MESSAGE_ID,EMAIL,NAME).values(messageId,receiver.email(),receiver.name()).execute(db).close();
			}
			if (envelope.message().attachments() != null) {
				for (var attachment : envelope.message().attachments()) {
					insertInto(TABLE_ATTACHMENTS, MESSAGE_ID, NAME, MIME, DATA).values(messageId, attachment.name(), attachment.mime(), attachment.content()).execute(db).close();
				}
			}
		} catch (SQLException e) {
			throw failedToStoreObject(envelope).causedBy(e);
		}
	}

	private Settings toSettings(ResultSet rs) throws SQLException {
		var submission = rs.getString(VALUE);
		if (submission.trim().equalsIgnoreCase(INSTANTLY)) return new Instantly();
		try {
			var times = Arrays.stream(submission.split(",")).map(Integer::parseInt).toList();
			return schedule(times);
		} catch (NumberFormatException nfe){
			return new Silent();
		}
	}

	@Override
	public Settings update(UmbrellaUser user, Settings settings) throws UmbrellaException {
		try {
			replaceInto(TABLE_SUBMISSIONS, USER_ID, VALUE).values(user.id(),settings.toString()).execute(db).close();
			return settings;
		} catch (SQLException e) {
			throw failedToStoreObject("submission data").causedBy(e);
		}
	}
}
