package eu.dnetlib.openaire.user.dao;

import eu.dnetlib.openaire.user.pojos.RoleVerification;
import eu.dnetlib.openaire.user.store.DataSourceConnector;
import eu.dnetlib.openaire.user.store.Statement;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import static eu.dnetlib.openaire.user.queries.RoleVerificationQueries.*;

@Component(value = "roleVerificationDAO")
public class RoleVerificationDAO {

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

    @Autowired
    private DataSourceConnector dataSourceConnector;

    public RoleVerificationDAO() {
    }

    public RoleVerification getManagerVerification(String email, String type, String entity) throws SQLException {
        List<RoleVerification> verifications =  executeQuery(FETCH_BY_EMAIL_TYPE_ENTITY_VERIFICATION_TYPE, new Statement.Initializer() {
            @Override
            public void prepare(PreparedStatement stmt) throws SQLException {
                stmt.setString(1, email);
                stmt.setString(2, type);
                stmt.setString(3, entity);
                stmt.setString(4, "manager");
            }
        });
        if(verifications.size() > 0) {
            return verifications.get(0);
        }
        return null;
    }

    public RoleVerification getMemberVerification(String email, String type, String entity) throws SQLException {
        List<RoleVerification> verifications =  executeQuery(FETCH_BY_EMAIL_TYPE_ENTITY_VERIFICATION_TYPE, new Statement.Initializer() {
            @Override
            public void prepare(PreparedStatement stmt) throws SQLException {
                stmt.setString(1, email);
                stmt.setString(2, type);
                stmt.setString(3, entity);
                stmt.setString(4, "member");
            }
        });
        if(verifications.size() > 0) {
            return verifications.get(0);
        }
        return null;
    }

    public List<String> getInvitedManagers(String type, String entity) throws SQLException {
        return getInvitedEmail(new Statement.Initializer() {
            @Override
            public void prepare(PreparedStatement stmt) throws SQLException {
                stmt.setString(1, type);
                stmt.setString(2, entity);
                stmt.setString(3, "manager");
            }
        });
    }

    public List<String> getInvitedMembers(String type, String entity) throws SQLException {
        return getInvitedEmail(new Statement.Initializer() {
            @Override
            public void prepare(PreparedStatement stmt) throws SQLException {
                stmt.setString(1, type);
                stmt.setString(2, entity);
                stmt.setString(3, "member");
            }
        });
    }

    public RoleVerification fetchById(final String id)
            throws SQLException {
        List<RoleVerification> userVerifications = executeQuery(FETCH_BY_ID, new Statement.Initializer() {
            @Override
            public void prepare(PreparedStatement stmt) throws SQLException {
                stmt.setString(1, id);
            }
        });

        if (userVerifications.isEmpty())
            return null;

        return userVerifications.get(0);
    }

    public long countAll() throws SQLException {
        return executeCount(COUNT_ALL);
    }

    public int insert(final RoleVerification roleVerification)
            throws SQLException {
        return executeUpdate(INSERT, new Statement.Initializer() {
            @Override
            public void prepare(PreparedStatement stmt)
                    throws SQLException {
                stmt.setString(1, roleVerification.getId());
                stmt.setString(2, roleVerification.getEmail());
                stmt.setString(3, roleVerification.getVerificationCode());
                stmt.setString(4, roleVerification.getVerificationType());
                stmt.setString(5, roleVerification.getType());
                stmt.setString(6, roleVerification.getEntity());
                stmt.setTimestamp(7, roleVerification.getDate());
            }
        });

    }

    public boolean delete(final String id)
            throws SQLException {
        return executeUpdate(DELETE, new Statement.Initializer() {
            @Override
            public void prepare(PreparedStatement stmt)
                    throws SQLException {
                stmt.setString(1, id);
            }
        }) > 0;
    }

    public boolean update(final RoleVerification roleVerification) throws SQLException {
        return executeUpdate(UPDATE, new Statement.Initializer() {
            @Override
            public void prepare(PreparedStatement stmt)
                    throws SQLException {
                stmt.setString(1, roleVerification.getId());
                stmt.setString(2, roleVerification.getEmail());
                stmt.setString(3, roleVerification.getVerificationCode());
                stmt.setString(4, roleVerification.getVerificationType());
                stmt.setString(5, roleVerification.getType());
                stmt.setString(6, roleVerification.getEntity());
                stmt.setTimestamp(7, roleVerification.getDate());
            }
        }) > 0;
    }

    protected RoleVerification fromResultSet(ResultSet set) throws SQLException {
        RoleVerification roleVerification = new RoleVerification();

        roleVerification.setId(set.getString("id"));
        roleVerification.setEmail(set.getString("email"));
        roleVerification.setVerificationCode(set.getString("verification_code"));
        roleVerification.setVerificationType(set.getString("verification_type"));
        roleVerification.setType(set.getString("type"));
        roleVerification.setEntity(set.getString("entity"));
        roleVerification.setDate(set.getTimestamp("date"));

        return roleVerification;
    }

    protected int executeUpdate(String sql, Statement.Initializer init)
            throws SQLException {
        Connection connection = dataSourceConnector.getDatasource().getConnection();
        try {
            PreparedStatement stmt = connection.prepareStatement(sql);
            init.prepare(stmt);
            return stmt.executeUpdate();
        } finally {
            connection.close();
        }
    }

    protected List<String> getInvitedEmail(Statement.Initializer init)
            throws SQLException {
        Connection connection = dataSourceConnector.getDatasource().getConnection();

        try {
            PreparedStatement stmt = connection.prepareStatement(FETCH_BY_TYPE_ENTITY_VERIFICATION_TYPE);
            init.prepare(stmt);

            ResultSet set = stmt.executeQuery();

            try {
                List<String> results = new ArrayList<>();
                while (set.next())
                    results.add(set.getString("email"));
                return results;
            } finally {
                set.close();
            }
        } finally {
            connection.close();
        }
    }

    protected List<RoleVerification> executeQuery(String sql, Statement.Initializer init)
            throws SQLException {
        Connection connection = dataSourceConnector.getDatasource().getConnection();

        try {
            PreparedStatement stmt = connection.prepareStatement(sql);
            init.prepare(stmt);

            ResultSet set = stmt.executeQuery();

            try {
                List<RoleVerification> results = new ArrayList<>();

                while (set.next())
                    results.add(fromResultSet(set));

                return results;
            } finally {
                set.close();
            }
        } finally {
            connection.close();
        }
    }

    protected List<RoleVerification> executeQuery(String sql)
            throws SQLException {
        return executeQuery(sql, Statement.emptyInitializer());
    }

    public long executeCount(String sql)
            throws SQLException {
        return executeCount(sql, Statement.emptyInitializer());
    }

    public long executeCount(String sql, Statement.Initializer init)
            throws SQLException {
        Connection connection = dataSourceConnector.getDatasource().getConnection();

        try {
            PreparedStatement stmt = connection.prepareStatement(sql);
            init.prepare(stmt);

            ResultSet set = stmt.executeQuery();

            try {
                if (set.next())
                    return set.getLong(1);
                throw new IllegalArgumentException(stmt.toString());
            } finally {
                set.close();
            }
        } finally {
            connection.close();
        }
    }


    public DataSourceConnector getDataSourceConnector() {
        return dataSourceConnector;
    }

    public void setDataSourceConnector(DataSourceConnector dataSourceConnector) {
        this.dataSourceConnector = dataSourceConnector;
    }

}