package de.effigenix.infrastructure.usermanagement.persistence;

import de.effigenix.domain.usermanagement.*;
import de.effigenix.shared.common.RepositoryError;
import de.effigenix.shared.common.Result;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.context.annotation.Profile;
import org.springframework.jdbc.core.simple.JdbcClient;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.OffsetDateTime;
import java.util.*;

@Repository
@Profile("!no-db")
public class JdbcUserRepository implements UserRepository {

    private static final Logger logger = LoggerFactory.getLogger(JdbcUserRepository.class);

    private final JdbcClient jdbc;
    private final RoleRepository roleRepository;

    public JdbcUserRepository(JdbcClient jdbc, RoleRepository roleRepository) {
        this.jdbc = jdbc;
        this.roleRepository = roleRepository;
    }

    @Override
    public Result<RepositoryError, Optional<User>> findById(UserId id) {
        try {
            var userOpt = jdbc.sql("SELECT * FROM users WHERE id = :id")
                    .param("id", id.value())
                    .query(this::mapUserRow)
                    .optional();
            if (userOpt.isEmpty()) {
                return Result.success(Optional.empty());
            }
            return Result.success(Optional.of(loadRoles(userOpt.get())));
        } catch (Exception e) {
            logger.trace("Database error in findById", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, Optional<User>> findByUsername(String username) {
        try {
            var userOpt = jdbc.sql("SELECT * FROM users WHERE username = :username")
                    .param("username", username)
                    .query(this::mapUserRow)
                    .optional();
            if (userOpt.isEmpty()) {
                return Result.success(Optional.empty());
            }
            return Result.success(Optional.of(loadRoles(userOpt.get())));
        } catch (Exception e) {
            logger.trace("Database error in findByUsername", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, Optional<User>> findByEmail(String email) {
        try {
            var userOpt = jdbc.sql("SELECT * FROM users WHERE email = :email")
                    .param("email", email)
                    .query(this::mapUserRow)
                    .optional();
            if (userOpt.isEmpty()) {
                return Result.success(Optional.empty());
            }
            return Result.success(Optional.of(loadRoles(userOpt.get())));
        } catch (Exception e) {
            logger.trace("Database error in findByEmail", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, List<User>> findByBranchId(String branchId) {
        try {
            var users = jdbc.sql("SELECT * FROM users WHERE branch_id = :branchId")
                    .param("branchId", branchId)
                    .query(this::mapUserRow)
                    .list()
                    .stream()
                    .map(this::loadRoles)
                    .toList();
            return Result.success(users);
        } catch (Exception e) {
            logger.trace("Database error in findByBranchId", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, List<User>> findByStatus(UserStatus status) {
        try {
            var users = jdbc.sql("SELECT * FROM users WHERE status = :status")
                    .param("status", status.name())
                    .query(this::mapUserRow)
                    .list()
                    .stream()
                    .map(this::loadRoles)
                    .toList();
            return Result.success(users);
        } catch (Exception e) {
            logger.trace("Database error in findByStatus", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, List<User>> findAll() {
        try {
            var users = jdbc.sql("SELECT * FROM users ORDER BY username")
                    .query(this::mapUserRow)
                    .list()
                    .stream()
                    .map(this::loadRoles)
                    .toList();
            return Result.success(users);
        } catch (Exception e) {
            logger.trace("Database error in findAll", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, Void> save(User user) {
        try {
            int rows = jdbc.sql("""
                    UPDATE users
                    SET username = :username, email = :email, password_hash = :passwordHash,
                        branch_id = :branchId, status = :status, last_login = :lastLogin
                    WHERE id = :id
                    """)
                    .param("id", user.id().value())
                    .param("username", user.username())
                    .param("email", user.email())
                    .param("passwordHash", user.passwordHash().value())
                    .param("branchId", user.branchId())
                    .param("status", user.status().name())
                    .param("lastLogin", user.lastLogin())
                    .update();

            if (rows == 0) {
                jdbc.sql("""
                        INSERT INTO users (id, username, email, password_hash, branch_id, status, created_at, last_login)
                        VALUES (:id, :username, :email, :passwordHash, :branchId, :status, :createdAt, :lastLogin)
                        """)
                        .param("id", user.id().value())
                        .param("username", user.username())
                        .param("email", user.email())
                        .param("passwordHash", user.passwordHash().value())
                        .param("branchId", user.branchId())
                        .param("status", user.status().name())
                        .param("createdAt", user.createdAt())
                        .param("lastLogin", user.lastLogin())
                        .update();
            }

            saveUserRoles(user);

            return Result.success(null);
        } catch (Exception e) {
            logger.trace("Database error in save", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, Void> delete(User user) {
        try {
            jdbc.sql("DELETE FROM users WHERE id = :id")
                    .param("id", user.id().value())
                    .update();
            return Result.success(null);
        } catch (Exception e) {
            logger.trace("Database error in delete", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, Boolean> existsByUsername(String username) {
        try {
            int count = jdbc.sql("SELECT COUNT(*) FROM users WHERE username = :username")
                    .param("username", username)
                    .query(Integer.class)
                    .single();
            return Result.success(count > 0);
        } catch (Exception e) {
            logger.trace("Database error in existsByUsername", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, Boolean> existsByEmail(String email) {
        try {
            int count = jdbc.sql("SELECT COUNT(*) FROM users WHERE email = :email")
                    .param("email", email)
                    .query(Integer.class)
                    .single();
            return Result.success(count > 0);
        } catch (Exception e) {
            logger.trace("Database error in existsByEmail", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    private void saveUserRoles(User user) {
        jdbc.sql("DELETE FROM user_roles WHERE user_id = :userId")
                .param("userId", user.id().value())
                .update();

        for (Role role : user.roles()) {
            jdbc.sql("INSERT INTO user_roles (user_id, role_id) VALUES (:userId, :roleId)")
                    .param("userId", user.id().value())
                    .param("roleId", role.id().value())
                    .update();
        }
    }

    private User loadRoles(User user) {
        var roleIds = jdbc.sql("SELECT role_id FROM user_roles WHERE user_id = :userId")
                .param("userId", user.id().value())
                .query((rs, rowNum) -> rs.getString("role_id"))
                .list();

        Set<Role> roles = new HashSet<>();
        for (String roleId : roleIds) {
            switch (roleRepository.findById(RoleId.of(roleId))) {
                case Result.Success(var opt) -> opt.ifPresent(roles::add);
                case Result.Failure(var err) ->
                    logger.trace("Failed to load role {}: {}", roleId, err.message());
            }
        }

        return User.reconstitute(
                user.id(), user.username(), user.email(), user.passwordHash(),
                roles, user.branchId(), user.status(), user.createdAt(), user.lastLogin()
        );
    }

    private User mapUserRow(ResultSet rs, int rowNum) throws SQLException {
        return User.reconstitute(
                UserId.of(rs.getString("id")),
                rs.getString("username"),
                rs.getString("email"),
                PasswordHash.of(rs.getString("password_hash")),
                Set.of(),
                rs.getString("branch_id"),
                UserStatus.valueOf(rs.getString("status")),
                rs.getObject("created_at", OffsetDateTime.class),
                rs.getObject("last_login", OffsetDateTime.class)
        );
    }
}
