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.util.*;

@Repository
@Profile("!no-db")
public class JdbcRoleRepository implements RoleRepository {

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

    private final JdbcClient jdbc;

    public JdbcRoleRepository(JdbcClient jdbc) {
        this.jdbc = jdbc;
    }

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

    @Override
    public Result<RepositoryError, Optional<Role>> findByName(RoleName name) {
        try {
            var roleOpt = jdbc.sql("SELECT * FROM roles WHERE name = :name")
                    .param("name", name.name())
                    .query(this::mapRoleRow)
                    .optional();
            if (roleOpt.isEmpty()) {
                return Result.success(Optional.empty());
            }
            return Result.success(Optional.of(loadPermissions(roleOpt.get())));
        } catch (Exception e) {
            logger.trace("Database error in findByName", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

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

    @Override
    public Result<RepositoryError, Void> save(Role role) {
        try {
            int rows = jdbc.sql("""
                    UPDATE roles SET name = :name, description = :description
                    WHERE id = :id
                    """)
                    .param("id", role.id().value())
                    .param("name", role.name().name())
                    .param("description", role.description())
                    .update();

            if (rows == 0) {
                jdbc.sql("""
                        INSERT INTO roles (id, name, description)
                        VALUES (:id, :name, :description)
                        """)
                        .param("id", role.id().value())
                        .param("name", role.name().name())
                        .param("description", role.description())
                        .update();
            }

            savePermissions(role);

            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(Role role) {
        try {
            jdbc.sql("DELETE FROM roles WHERE id = :id")
                    .param("id", role.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> existsByName(RoleName name) {
        try {
            int count = jdbc.sql("SELECT COUNT(*) FROM roles WHERE name = :name")
                    .param("name", name.name())
                    .query(Integer.class)
                    .single();
            return Result.success(count > 0);
        } catch (Exception e) {
            logger.trace("Database error in existsByName", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    private void savePermissions(Role role) {
        jdbc.sql("DELETE FROM role_permissions WHERE role_id = :roleId")
                .param("roleId", role.id().value())
                .update();

        for (Permission permission : role.permissions()) {
            jdbc.sql("INSERT INTO role_permissions (role_id, permission) VALUES (:roleId, :permission)")
                    .param("roleId", role.id().value())
                    .param("permission", permission.name())
                    .update();
        }
    }

    private Role loadPermissions(Role role) {
        var permissions = jdbc.sql("SELECT permission FROM role_permissions WHERE role_id = :roleId")
                .param("roleId", role.id().value())
                .query((rs, rowNum) -> Permission.valueOf(rs.getString("permission")))
                .set();
        return Role.reconstitute(role.id(), role.name(), permissions, role.description());
    }

    private Role mapRoleRow(ResultSet rs, int rowNum) throws SQLException {
        return Role.reconstitute(
                RoleId.of(rs.getString("id")),
                RoleName.valueOf(rs.getString("name")),
                Set.of(),
                rs.getString("description")
        );
    }
}
