/* © SRSoftware 2025 */
package de.srsoftware.umbrella.user.sqlite;

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.SETTINGS;
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.USERS;
import static de.srsoftware.umbrella.core.exceptions.UmbrellaException.*;
import static de.srsoftware.umbrella.core.exceptions.UmbrellaException.failedToLoadObject;
import static de.srsoftware.umbrella.core.model.Translatable.t;
import static de.srsoftware.umbrella.user.Constants.*;
import static de.srsoftware.umbrella.user.model.DbUser.ADMIN_PERMISSIONS;
import static java.lang.System.Logger.Level.*;
import static java.text.MessageFormat.format;
import static java.time.ZoneOffset.UTC;

import de.srsoftware.tools.PasswordHasher;
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.user.BadHasher;
import de.srsoftware.umbrella.user.api.LoginServiceDb;
import de.srsoftware.umbrella.user.api.UserDb;
import de.srsoftware.umbrella.user.model.*;
import de.srsoftware.umbrella.user.model.DbUser;
import de.srsoftware.umbrella.user.model.Password;
import java.security.NoSuchAlgorithmException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.Duration;
import java.time.Instant;
import java.time.LocalDateTime;
import java.util.*;

public class SqliteDB extends BaseDb implements LoginServiceDb, UserDb {
	private static final System.Logger LOG = System.getLogger(SqliteDB.class.getSimpleName());
	private static Duration sessionDuration = DEFAULT_SESSION_DURATION;

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

	private void createLoginServiceTables() {
		var createTable = """
CREATE TABLE IF NOT EXISTS {0} (
	{1} VARCHAR(255),
	{2} TEXT,
	{3} VARCHAR(255),
	{4} VARCHAR(255),
	{5} VARCHAR(255),
	PRIMARY KEY ({1})
)""";
		try {
			var stmt = db.prepareStatement(format(createTable,TABLE_LOGIN_SERVICES, NAME, URL,CLIENT_ID,CLIENT_SECRET, USER_INFO_FIELD));
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw failedToCreateTable(TABLE_LOGIN_SERVICES).causedBy(e);
		}

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

		createTable = """
CREATE TABLE IF NOT EXISTS {0} (
	{1} VARCHAR(255) NOT NULL,
	{2} TEXT NOT NULL
)""";
		try {
			var stmt = db.prepareStatement(format(createTable,TABLE_TOKEN_USES, TOKEN, DOMAIN));
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw failedToCreateTable(TABLE_TOKEN_USES).causedBy(e);
		}
	}

	protected int createTables() {
		int currentVersion = createSettingsTable();
		switch (currentVersion){
			case 0:
			case 1:
			case 2:
			case 3:
				createUserTables();
				createLoginServiceTables();
            case 4:
                createLanguageColumn();
		}

		return setCurrentVersion(5);

	}

    private void createLanguageColumn() {
        var sql = "ALTER TABLE {0} ADD COLUMN {1} VARCHAR(10)";
        try {
            var stmt = db.prepareStatement(format(sql,TABLE_USERS, LANGUAGE));
            stmt.execute();
            stmt.close();
        } catch (SQLException e) {
			throw databaseException(FAILED_TO_ADD_COLUMN, NAME, LANGUAGE, TABLE,TABLE_USERS).causedBy(e);
        }
    }


    private void createUserTables() {
		PasswordHasher<String> hasher;
		try {
			hasher = new BadHasher();
		} catch (NoSuchAlgorithmException e) {
			throw new RuntimeException(e);
		}

		var createTable = """
CREATE TABLE IF NOT EXISTS {0} (
	`{1}` INTEGER,
	`{2}` VARCHAR(255) NOT NULL,
	`{3}` VARCHAR(255) NOT NULL,
	`{4}` VARCHAR(50),
	`{5}` VARCHAR(255) UNIQUE,
	`{6}` VARCHAR(100) DEFAULT "DELIVER INSTANTLY",
	`{7}` INT DEFAULT NULL,
	`{8}` TEXT,
	PRIMARY KEY({1})
)""";
		try {
			var stmt = db.prepareStatement(format(createTable,TABLE_USERS, ID, LOGIN, PASS, THEME, EMAIL, MESSAGE_DELIVERY, LAST_LOGOFF, SETTINGS));
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw failedToCreateTable(TABLE_USERS);
		}

		long count = 0L;
		try {
			ResultSet rs = select("COUNT(*)").from(TABLE_USERS).exec(db);
			if (rs.next()) count = rs.getLong(1);
			rs.close();
		} catch (SQLException ignored) {
			// go on with table creation
		}

		try {
			if (count<1) insertInto(TABLE_USERS, LOGIN, PASS,THEME, SETTINGS).values("admin", hasher.hash("admin",null),"default",null).execute(db);
		} catch (SQLException e) {
			throw failedToStoreObject(t("first user")).causedBy(e);
		}

		createTable = """
CREATE TABLE IF NOT EXISTS {0} (
	{1} INT NOT NULL PRIMARY KEY,
	{2} VARCHAR(255),
	{3} INTEGER NOT NULL
)""";
		try {
			var stmt = db.prepareStatement(format(createTable,TABLE_TOKENS, USER_ID, TOKEN, EXPIRATION));
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw failedToCreateTable(TABLE_USERS).causedBy(e);
		}
	}

	@Override
	public Long delete(Long userId) throws UmbrellaException {
		try {
			Query.delete().from(TABLE_USERS).where(ID,equal(userId)).execute(db);
		} catch (SQLException e) {
			throw failedToDropObject(t(USER_WITH_ID, ID,userId)).causedBy(e);
		}
		return userId;
	}

	@Override
	public String delete(String serviceName) throws UmbrellaException {
		try {
			Query.delete().from(TABLE_LOGIN_SERVICES).where(NAME,equal(serviceName)).execute(db);
			Query.delete().from(TABLE_SERVICE_IDS_USERS).where(SERVICE_ID,like(serviceName+":%")).execute(db);
		} catch (SQLException e) {
			throw failedToDropObject(t(SERVICE_WITH_ID,ID,serviceName)).causedBy(e);
		}
		return serviceName;
	}

	private void dropExpiredSessions() {
		LOG.log(DEBUG,"Dropping expired sessions.");
		try {
			var tokens = new HashSet<String>();
			var rs = select(TOKEN).from(TABLE_TOKENS).where(EXPIRATION,lessThan(now())).exec(db);
			while (rs.next()) tokens.add(rs.getString(TOKEN));
			rs.close();
			Query.delete().from(TABLE_TOKEN_USES).where(TOKEN,in(tokens.toArray())).execute(db);
			Query.delete().from(TABLE_TOKENS).where(TOKEN,in(tokens.toArray())).execute(db);
		} catch (SQLException sqle){
			LOG.log(WARNING,"Failed to drop expired sessions!",sqle);
		}
	}

	@Override
	public Boolean dropSession(Token token) throws UmbrellaException {
		LOG.log(DEBUG,"Dropping session associated with {0}.",token);
		try {
			return Query.delete().from(TABLE_TOKENS).where(TOKEN, equal(token)).execute(db);
		} catch (SQLException e){
			throw failedToDropObject(t("session token")).causedBy(e);
		}
	}

	@Override
	public Session extend(Session session) throws UmbrellaException {
		try {
			Instant newExpiration = then();
			update(TABLE_TOKENS).set(EXPIRATION).where(TOKEN, equal(session.token())).prepare(db).apply(newExpiration.getEpochSecond());
			LOG.log(DEBUG,"Extended session of user {0} until {1}",session.user().name(),then());
			return session.extended(newExpiration);
		} catch (SQLException e) {
			throw databaseException(FAILED_TO_UPDATE_OBJECT, OBJECT,session.token()).causedBy(e);
		}
	}

	@Override
	public Session getSession(UmbrellaUser user) throws UmbrellaException {
		if (user == null) throw new UmbrellaException(400,"User must not be null");
		dropExpiredSessions();
		LOG.log(DEBUG,"Trying to get session for \"{0}\"",user.name());
		Session session = null;
		try {
			var rs = select(ALL).from(TABLE_TOKENS).where(USER_ID,equal(user.id())).exec(db);
			if (rs.next()) session = new Session(user,Token.of(rs.getString(TOKEN)),Instant.ofEpochSecond(rs.getLong(EXPIRATION)));
			rs.close();
			if (session != null) return session;
			session = new Session(user,new Token(),then());
			insertInto(TABLE_TOKENS, USER_ID, TOKEN, EXPIRATION).values(session.user().id(),session.token(),session.expiration().getEpochSecond()).execute(db);
			return session;
		} catch (SQLException e) {
			throw databaseException(FAILED_TO_LOAD_ENTITIES_OF_OWNER, TYPE,t(SESSION), OWNER,user.name()).causedBy(e);
		}
	}

	@Override
	public long getUserId(String loginService, String foreignUserId) throws UmbrellaException {
		Long userId = null;
		try {
			var rs = select(USER_ID).from(TABLE_SERVICE_IDS_USERS).where(SERVICE_ID,equal(loginService+":"+foreignUserId)).exec(db);
			if (rs.next()) userId = rs.getLong(USER_ID);
			rs.close();
		} catch (SQLException sqle){
			LOG.log(WARNING,"Failed to load user_id for '{0}' @ '{1}'!",foreignUserId,loginService);
		}
		if (userId == null) throw databaseException(FAILED_TO_LOAD_ENTITIES_OF_OWNER, TYPE, t(USER_WITH_ID, ID,foreignUserId), OWNER,loginService);
		return userId;
	}

	@Override
	public Map<Long, UmbrellaUser> list(Integer start, Integer limit, Collection<Long> ids) throws UmbrellaException {
		var list = new HashMap<Long, UmbrellaUser>();
		try {
			var query = select(ALL).from(TABLE_USERS);
			if (start != null && start > 0) query.skip(start);
			if (limit != null && limit > 0) query.limit(limit);
			if (ids != null && !ids.isEmpty()) query.where(ID,in(ids.toArray()));
			var rs = query.exec(db);
			while (rs.next()) {
				var user = toUser(rs);
				list.put(user.id(),user);
			}
			rs.close();
		} catch (SQLException e) {
			throw databaseException(FAILED_TO_LIST_ENTITIES,t(USERS)).causedBy(e);
		}
		return list;
	}

	@Override
	public List<ForeignLogin> listAssignments(long userId) throws UmbrellaException {
		try {
			var list = new ArrayList<ForeignLogin>();
			var rs = select(ALL).from(TABLE_SERVICE_IDS_USERS).where(USER_ID,equal(userId)).exec(db);
			while (rs.next()) list.add(toForeignLogin(rs));
			rs.close();
			return list;
		} catch (SQLException e) {
			throw databaseException(FAILED_TO_LOAD_ENTITIES_OF_OWNER, TYPE,"service ids", OWNER, t(USER_WITH_ID, ID,userId)).causedBy(e);
		}
	}

	@Override
	 public List<LoginService> listLoginServices() throws UmbrellaException {
		var list = new ArrayList<LoginService>();
		try {
			var rs = select(ALL).from(TABLE_LOGIN_SERVICES).exec(db);
			while (rs.next()) list.add(toLoginService(rs));
			rs.close();
		} catch (SQLException e){
			throw databaseException(FAILED_TO_LIST_ENTITIES, TYPE,TABLE_LOGIN_SERVICES).causedBy(e);
		}
		return list;
	 }

	@Override
	public LoginService loadLoginService(String name) throws UmbrellaException {
		LoginService loginService = null;
		try {
			var rs = select(ALL).from(TABLE_LOGIN_SERVICES).where(NAME,equal(name)).exec(db);
			if (rs.next()) loginService = toLoginService(rs);
			rs.close();
		} catch (SQLException e) {
			throw databaseException(FAILED_TO_LOAD_OBJECT, OBJECT,t(LOGIN_SERVICE)).causedBy(e);
		}
		if (loginService == null) throw new UmbrellaException(500,"Failed to load login service \"{0}\"!",name);
		return loginService;
	}

	@Override
	public UmbrellaUser load(EmailAddress email) throws UmbrellaException {
		if (email == null) throw new UmbrellaException(400,"Email must not be null!");
		UmbrellaUser user = null;
		try {
			var rs = select(ALL).from(TABLE_USERS).where(EMAIL,equal(email.toString())).exec(db);
			if (rs.next()) user = toUser(rs);
			rs.close();
		} catch (SQLException e) {
			LOG.log(WARNING,"Failed to load user for \"{0}\"!",email);
		}
		if (user == null) throw failedToLoadObject(Text.USER,email);
		return user;
	}

	@Override
	public UmbrellaUser load(Long id) throws UmbrellaException {
		if (id == null) throw new UmbrellaException(400,"Id must not be null!");
		UmbrellaUser user = null;
		try {
			var rs = select(ALL).from(TABLE_USERS).where(ID,equal(id)).exec(db);
			if (rs.next()) user = toUser(rs);
			rs.close();
		} catch (SQLException e) {
			LOG.log(WARNING,"Failed to load user \"{0}\"!",id);
		}
		if (user == null) throw failedToLoadObject(t(Text.USER),id);
		return user;
	}

	@Override
	public UmbrellaUser load(Session session) throws UmbrellaException {
		if (session == null) throw new UmbrellaException(500,"Session must not be null!");
		UmbrellaUser user = null;
		try {
			var rs = select(ALL).from(TABLE_USERS).where(ID,equal(session.user().id())).exec(db);
			if (rs.next()) user = toUser(rs);
			rs.close();
		} catch (SQLException e) {
			LOG.log(WARNING,"Failed to load user for session {0}!",session.token());
		}
		if (user == null) throw failedToLoadObject(t(Text.USER),session.token());
		return user;
	}


	@Override
	public UmbrellaUser load(String key, Password password) throws UmbrellaException {
		UmbrellaUser user = null;
		try {
			var rs = select(ALL).from(TABLE_USERS).where(PASS,equal(password)).exec(db);
			while (rs.next()){
				var email = rs.getString(EMAIL);
				if (key.equalsIgnoreCase(email)) {
					user = toUser(rs);
					break;
				}
				var login = rs.getString(LOGIN);
				if (key.equals(login)) {
					user = toUser(rs);
					break;
				}
			}
			rs.close();
		} catch (SQLException e) {
			LOG.log(WARNING,"Failed to load user \"{0}\"!",key,e);
		}
		if (user == null) throw unauthorized(FAILED_TO_LOAD_OBJECT_BY_ID, OBJECT,t(Text.USER), ID,key);
		return user;
	}

	@Override
	public Session load(Token token) throws UmbrellaException {
		dropExpiredSessions();
		Session session = null;
		try {
			var rs = select(ALL).from(TABLE_TOKENS).leftJoin(USER_ID,TABLE_USERS, ID).where(TOKEN,equal(token)).exec(db);
			if (rs.next()) session = new Session(toUser(rs),token,Instant.ofEpochSecond(rs.getLong(EXPIRATION)));
			rs.close();
		} catch (SQLException e) {
			LOG.log(WARNING,"Failed to request session ({0}) from database",token);
			throw new UmbrellaException(500,"Failed to request session ({0}) from database",token);
		}
		if (session == null) throw failedToLoadObject("session",token);
		return session;
	}

	public long now() {
		return LocalDateTime.now().toEpochSecond(UTC);
	}

	@Override
	public ForeignLogin save(ForeignLogin assignment) throws UmbrellaException {
		try {
			insertInto(TABLE_SERVICE_IDS_USERS, SERVICE_ID, USER_ID).values(assignment.loginService()+":"+assignment.foreingId(),assignment.userId()).execute(db).close();
		} catch (SQLException e) {
			throw failedToStoreObject("user assignment").causedBy(e);
		}
		return assignment;
	}


	@Override
	public LoginService save(LoginService service) throws UmbrellaException {
		try {
			replaceInto(TABLE_LOGIN_SERVICES, NAME, URL,CLIENT_ID,CLIENT_SECRET)
					.values(service.name(),service.url(),service.clientId(),service.clientSecret())
					.execute(db)
					.close();
		} catch (SQLException e){
			throw failedToStoreObject("login service data").causedBy(e);
		}
		return service;
	}

	@Override
	public UmbrellaUser save(DbUser user) throws UmbrellaException {
		try {
			Long id = user.id();
            var email = user.email() == null ? null : user.email().toString();
			if (id<1){
				insertInto(TABLE_USERS, LOGIN, PASS, THEME, EMAIL, LANGUAGE)
						.values(user.name(), user.hashedPassword(), user.theme(), email, user.language())
						.execute(db)
						.close();
			} else {
				replaceInto(TABLE_USERS, ID, LOGIN, PASS, THEME, EMAIL, LAST_LOGOFF, LANGUAGE)
						.values(id, user.name(), user.hashedPassword(), user.theme(), email, user.lastLogoff(), user.language())
						.execute(db)
						.close();
			}
		} catch (SQLException e) {
			throw failedToStoreObject("user data").causedBy(e);
		}
		return user;
	}

	@Override
	public Map<Long, DbUser> search(String key) {
		try {
			var rs = select(ALL).from(TABLE_USERS).where(LOGIN,like("%"+key+"%")).exec(db);
			var users = new HashMap<Long,DbUser>();
			while (rs.next()){
				var user = toUser(rs);
				users.put(user.id(),user);
			}
			rs.close();
			return users;
		} catch (SQLException e){
			throw failedToSearchDb(t(USERS)).causedBy(e);
		}
	}

	@Override
	public UserDb setSessionDuration(Number minutes) {
		LOG.log(INFO,"Session duration set to {} minutes",minutes);
		sessionDuration = Duration.ofMinutes(minutes.longValue());
		return this;
	}

	public Instant then(){
		return LocalDateTime.now().plus(sessionDuration).toInstant(UTC);
	}

	private ForeignLogin toForeignLogin(ResultSet rs) throws SQLException {
		var serviceId = rs.getString(SERVICE_ID);
		var userId = rs.getLong(USER_ID);
		var parts = serviceId.split(":",2);
		var serviceName = parts[0];
		var externalId = parts[1];
		return ForeignLogin.of(serviceName,externalId,userId);
	}

	private LoginService toLoginService(ResultSet rs) throws SQLException {
		return new LoginService(
				rs.getString(NAME),
				rs.getString(URL),
				rs.getString(CLIENT_ID),
				rs.getString(CLIENT_SECRET),
				rs.getString(USER_INFO_FIELD)
		);
	}

	private DbUser toUser(ResultSet rs) throws SQLException, UmbrellaException {
		long id = rs.getLong(ID);
		Set<DbUser.Permission> perms = id == 1 ? ADMIN_PERMISSIONS : Set.of();
		return new DbUser(
				id,
				rs.getString(LOGIN),
				rs.getString(EMAIL) instanceof String addr && !addr.isBlank() ? new EmailAddress(addr) : null,
				Password.of(rs.getString(PASS)),
				rs.getString(THEME),
                rs.getString(LANGUAGE),
				perms,
				rs.getLong(LAST_LOGOFF)
		);
	}

	@Override
	public ForeignLogin unlink(ForeignLogin assignment) throws UmbrellaException {
		try {
			Query.delete().from(TABLE_SERVICE_IDS_USERS)
					.where(SERVICE_ID,equal(assignment.loginService()+":"+assignment.foreingId()))
					.where(USER_ID,equal(assignment.userId()))
					.execute(db);
			return assignment;
		} catch (SQLException e) {
			throw failedToDropObjectFromObject("foreign id",assignment.foreingId(),"service",assignment.loginService()).causedBy(e);
		}
	}
}
