InvitedRegistrationDAO.java

package it.inaf.ia2.gms.persistence;

import it.inaf.ia2.gms.model.Permission;
import it.inaf.ia2.gms.persistence.model.InvitedRegistration;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.stream.Collectors;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

@Component
public class InvitedRegistrationDAO {

    private final JdbcTemplate jdbcTemplate;

    @Autowired
    public InvitedRegistrationDAO(DataSource dataSource) {
        jdbcTemplate = new JdbcTemplate(dataSource);
    }

    @Transactional
    public void addInvitedRegistration(InvitedRegistration invitedRegistration) {

        String sqlReq = "INSERT INTO invited_registration_request (id, token_hash, email) VALUES (?, ?, ?)";

        jdbcTemplate.update(conn -> {
            PreparedStatement ps = conn.prepareStatement(sqlReq);
            ps.setString(1, invitedRegistration.getId());
            ps.setString(2, invitedRegistration.getTokenHash());
            ps.setObject(3, invitedRegistration.getEmail());
            return ps;
        });

        for (Map.Entry<String, Permission> entry : invitedRegistration.getGroupsPermissions().entrySet()) {

            String sqlReqGroup = "INSERT INTO invited_registration_request_group (request_id, group_id, permission) VALUES (?, ?, ?)";

            jdbcTemplate.update(conn -> {
                PreparedStatement ps = conn.prepareStatement(sqlReqGroup);
                ps.setString(1, invitedRegistration.getId());
                ps.setString(2, entry.getKey());
                ps.setObject(3, entry.getValue().toString(), Types.OTHER);
                return ps;
            });
        }
    }

    public Optional<InvitedRegistration> getInvitedRegistrationFromToken(String tokenHash) {

        String sqlReq = "SELECT id, email, creation_time FROM invited_registration_request WHERE token_hash = ? AND done IS NOT true";

        InvitedRegistration registration = jdbcTemplate.query(conn -> {
            PreparedStatement ps = conn.prepareStatement(sqlReq);
            ps.setString(1, tokenHash);
            return ps;
        }, resultSet -> {
            if (resultSet.next()) {
                InvitedRegistration reg = new InvitedRegistration();
                reg.setId(resultSet.getString("id"));
                reg.setEmail(resultSet.getString("email"));
                reg.setCreationTime(new Date(resultSet.getDate("creation_time").getTime()));
                return reg;
            }
            return null;
        });

        if (registration != null) {

            String sqlReqGroup = "SELECT group_id, permission FROM invited_registration_request_group WHERE request_id = ?";

            Map<String, Permission> groupsPermissions = jdbcTemplate.query(conn -> {
                PreparedStatement ps = conn.prepareStatement(sqlReqGroup);
                ps.setString(1, registration.getId());
                return ps;
            }, resultSet -> {
                Map<String, Permission> map = new HashMap<>();
                while (resultSet.next()) {
                    String groupId = resultSet.getString("group_id");
                    Permission permission = Permission.valueOf(resultSet.getString("permission"));
                    map.put(groupId, permission);
                }
                return map;
            });

            registration.setGroupsPermissions(groupsPermissions);
        }

        return Optional.ofNullable(registration);
    }

    public void setRegistrationUser(InvitedRegistration invitedRegistration) {

        String sql = "UPDATE invited_registration_request SET \"user\" = ? WHERE id = ?";

        jdbcTemplate.update(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, invitedRegistration.getUserId());
            ps.setString(2, invitedRegistration.getId());
            return ps;
        });
    }

    public void setRegistrationDone(InvitedRegistration invitedRegistration) {

        String sql = "UPDATE invited_registration_request SET done = true WHERE id = ?";

        jdbcTemplate.update(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, invitedRegistration.getId());
            return ps;
        });
    }

    /**
     * Called before deleting a group.
     */
    public void deleteAllGroupsInvitedRegistrations(List<String> groupIds) {

        if (groupIds.isEmpty()) {
            return;
        }

        String sql = "DELETE FROM invited_registration_request_group WHERE group_id IN ("
                + String.join(",", groupIds.stream().map(g -> "?").collect(Collectors.toList()))
                + ")";

        jdbcTemplate.update(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            int i = 0;
            for (String groupId : groupIds) {
                ps.setString(++i, groupId);
            }
            return ps;
        });

        // Cleanup orphan invited requests
        jdbcTemplate.update("DELETE FROM invited_registration_request WHERE id NOT IN "
                + "(SELECT request_id FROM invited_registration_request_group)");
    }

    public List<InvitedRegistration> getPendingInvitedRegistrationsForGroup(String groupId) {

        String sql = "SELECT id, email, creation_time, group_id, permission\n"
                + "FROM invited_registration_request r\n"
                + "JOIN invited_registration_request_group rg ON r.id = rg.request_id\n"
                + "WHERE done IS NOT TRUE AND rg.group_id = ?";

        return jdbcTemplate.query(sql,
                ps -> {
                    ps.setString(1, groupId);
                },
                rs -> {
                    List<InvitedRegistration> registrations = getInvitedRegistrationsFromResultSet(rs);
                    Collections.sort(registrations, (reg1, reg2) -> reg1.getEmail().compareToIgnoreCase(reg2.getEmail()));
                    return registrations;
                });
    }

    public List<InvitedRegistration> getInvitedRegistrationsFromEmailAddresses(List<String> addresses) {

        if (addresses.isEmpty()) {
            throw new IllegalArgumentException("List of email addresses is empty");
        }

        String sql = "SELECT id, email, creation_time, group_id, permission\n"
                + "FROM invited_registration_request r\n"
                + "JOIN invited_registration_request_group rg ON r.id = rg.request_id\n"
                + "WHERE done IS NOT TRUE AND ("
                + String.join(" OR ", Collections.nCopies(addresses.size(), "email ILIKE ?"))
                + ")";

        return jdbcTemplate.query(sql,
                ps -> {
                    for (int i = 0; i < addresses.size(); i++) {
                        ps.setString(i + 1, addresses.get(i));
                    }
                },
                rs -> {
                    return getInvitedRegistrationsFromResultSet(rs);
                });
    }

    private List<InvitedRegistration> getInvitedRegistrationsFromResultSet(ResultSet rs) throws SQLException {

        // key: invited registration id
        Map<String, InvitedRegistration> map = new HashMap<>();

        while (rs.next()) {

            String id = rs.getString("id");

            InvitedRegistration reg = map.get(id);
            if (reg == null) {
                String email = rs.getString("email");
                Date creationTime = new Date(rs.getDate("creation_time").getTime());
                reg = new InvitedRegistration()
                        .setId(id)
                        .setEmail(email)
                        .setCreationTime(creationTime);
                map.put(id, reg);
            }

            if (reg.getGroupsPermissions() == null) {
                reg.setGroupsPermissions(new HashMap<>());
            }

            Permission permission = Permission.valueOf(rs.getString("permission"));

            String groupId = rs.getString("group_id");
            reg.getGroupsPermissions().put(groupId, permission);
        }

        return new ArrayList<>(map.values());
    }

    public void deleteInvitedRegistrationRequest(String requestId, String groupId) {

        String sql = "DELETE FROM invited_registration_request_group\n"
                + "WHERE request_id = ? AND group_id = ?";

        jdbcTemplate.update(sql, ps -> {
            ps.setString(1, requestId);
            ps.setString(2, groupId);
        });

        // Cleanup orphan invited requests
        jdbcTemplate.update("DELETE FROM invited_registration_request WHERE id NOT IN "
                + "(SELECT request_id FROM invited_registration_request_group)");
    }
}