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.Types;
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 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"));
                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 = ("
                + 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)");
    }
}