package it.inaf.ia2.gms.persistence;

import it.inaf.ia2.gms.persistence.model.InvitedRegistration;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;
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 (String groupId : invitedRegistration.getGroupIds()) {
            String sqlReqGroup = "INSERT INTO invited_registration_request_group (request_id, group_id) VALUES (?, ?)";

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

    public Optional<InvitedRegistration> getInvitedRegistrationFromToken(String tokenHash) {

        String sqlReq = "SELECT id, email FROM invited_registration_request WHERE token_hash = ? AND !done";

        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 FROM invited_registration_request_group WHERE request_id = ?";

            List<String> groupIds = jdbcTemplate.query(conn -> {
                PreparedStatement ps = conn.prepareStatement(sqlReqGroup);
                ps.setString(1, registration.getId());
                return ps;
            }, resultSet -> {
                List<String> groups = new ArrayList<>();
                while (resultSet.next()) {
                    groups.add(resultSet.getString("group_id"));
                }
                return groups;
            });

            registration.setGroupIds(groupIds);
        }

        return Optional.ofNullable(registration);
    }

    public void setRegistrationDone(String tokenHash) {

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

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