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

import static de.srsoftware.tools.jdbc.Condition.equal;
import static de.srsoftware.tools.jdbc.Query.SelectQuery.ALL;
import static de.srsoftware.tools.jdbc.Query.insertInto;
import static de.srsoftware.tools.jdbc.Query.select;
import static de.srsoftware.umbrella.contact.Constants.*;
import static de.srsoftware.umbrella.core.Errors.*;
import static de.srsoftware.umbrella.core.constants.Field.*;
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.Translatable.t;
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.Contact;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

public class SqliteDb extends BaseDb implements ContactDb{

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

	@Override
	protected int createTables() {
		int currentVersion = createSettingsTable();
		switch (currentVersion){
			case 0:
				createContactTable();
				createContactsUsersTable();
		}

		return setCurrentVersion(1);
	}

	private void createContactTable() {
		var sql = "CREATE TABLE IF NOT EXISTS {0} ({1} INTEGER PRIMARY KEY, {2} TEXT)";
		sql = format(sql, TABLE_CONTACTS, ID, DATA);
		try {
			db.prepareStatement(sql).execute();
		} catch (SQLException e) {
			throw failedToCreateTable(TABLE_CONTACTS).causedBy(e);
		}
	}

	private void createContactsUsersTable() {
		var sql = "CREATE TABLE IF NOT EXISTS {0} ({1} INT NOT NULL, {2} INT NOT NULL, {3} BOOLEAN DEFAULT 0, PRIMARY KEY({1}, {2}))";
		sql = format(sql, TABLE_CONTACTS_USERS, CONTACT_ID, USER_ID, ASSIGNED);
		try {
			db.prepareStatement(sql).execute();
		} catch (SQLException e) {
			throw failedToCreateTable(TABLE_CONTACTS_USERS).causedBy(e);
		}
	}

	@Override
	public void drop(Contact contact) {
		try {
			db.setAutoCommit(false);
			Query.delete().from(TABLE_CONTACTS).where(ID,equal(contact.id())).execute(db);
			Query.delete().from(TABLE_CONTACTS_USERS).where(CONTACT_ID,equal(contact.id())).execute(db);
			db.setAutoCommit(true);
		} catch (SQLException e){
			throw failedToDropObject(t(CONTACT_WITH_ID, ID,contact.id())).causedBy(e);
		}
	}

	@Override
	public Map<Long,Contact> listContactsOf(long userId) throws UmbrellaException{
		try {
			var rs = select(ALL).from(TABLE_CONTACTS).leftJoin(ID,TABLE_CONTACTS_USERS,CONTACT_ID).where(USER_ID,equal(userId)).exec(db);
			var contacts = new HashMap<Long,Contact>();
			while (rs.next()) {
				var contact = Contact.of(rs);
				contacts.put(contact.id(),contact);
			}
			rs.close();
			return contacts;
		} catch (SQLException e) {
			throw databaseException(FAILED_TO_LOAD_ENTITIES_OF_OWNER,
					TYPE, t(CONTACTS),
					OWNER, t(USER_WITH_ID, ID,userId)
			).causedBy(e);
		}
	}

	@Override
	public Contact load(long contactId, long userId) {
		try {
			var rs = select(ALL).from(TABLE_CONTACTS).leftJoin(ID,TABLE_CONTACTS_USERS,CONTACT_ID).where(USER_ID,equal(userId)).where(ID,equal(contactId)).exec(db);
			Contact contact = null;
			if (rs.next()) contact = Contact.of(rs);
			rs.close();
			if (contact != null) return contact;
			throw failedToLoadObject(t(CONTACT), contactId);
		} catch (SQLException e) {
			throw databaseException(FAILED_TO_LOAD_ENTITIES_OF_OWNER,
					TYPE, t(CONTACT),
					OWNER, t(USER_WITH_ID, ID,userId)
			);
		}
	}

	@Override
	public Contact save(Contact contact) {
		if (contact.id() == 0){ // new contact
			try {
				var rs = insertInto(TABLE_CONTACTS, DATA).values(contact.vcard()).execute(db).getGeneratedKeys();
				Long id = null;
				if (rs.next()) id = rs.getLong(1);
				rs.close();
				if (id != null) return new Contact(id,contact.vcard());
				throw failedToStoreObject(VCARD);
			} catch (SQLException e) {
				throw failedToStoreObject(VCARD).causedBy(e);
			}
		} else try { // update
			Query.update(TABLE_CONTACTS).set(DATA).where(ID,equal(contact.id())).prepare(db).apply(contact.vcard()).execute();
		} catch (SQLException e) {
			throw failedToStoreObject(VCARD).causedBy(e);
		}
		return contact;
	}

	@Override
	public void setOwner(long userId, Contact contact) {
		try {
			Query.replaceInto(TABLE_CONTACTS_USERS, USER_ID,CONTACT_ID,ASSIGNED).values(userId,contact.id(),false).execute(db).close();
		} catch (SQLException e) {
			throw databaseException(FAILED_TO_ASSIGN_A_TO_B,"a", t(CONTACT_WITH_ID, ID,contact.id()),"b", t(USER_WITH_ID, ID,userId)).causedBy(e);
		}
	}
}
