package eu.dnetlib.openaire.user.dao;

import eu.dnetlib.openaire.user.pojos.UserVerification;
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.*;
import java.util.ArrayList;
import java.util.List;

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

/**
 * Created by sofia on 3/10/2017.
 */
@Component(value = "userVertificationDao")
public class UserVerificationDAO {

    //private final DataSource ds;

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

    @Autowired
    private DataSourceConnector dataSourceConnector;

    public UserVerificationDAO() {
        //this.ds = null;
       // this.ds = dataSourceConnector.getDatasource();
    }

    // FETCH

    public List<UserVerification> fetchAll()
            throws SQLException
    {
        return (List<UserVerification>) executeQuery(FETCH_ALL);
    }

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

        if (userVerifications.isEmpty())
            return null;

        return userVerifications.get(0);
    }

    public  UserVerification fetchByUsername(final String username)
            throws SQLException
    {
        //logger.info("Datasource -> " + dataSourceConnector.getDatasource());
        List<UserVerification> userVerifications = executeQuery(FETCH_BY_USERNAME, new Statement.Initializer() {
            @Override
            public void prepare(PreparedStatement stmt) throws SQLException {
                stmt.setString(1, username);
            }
        });

        if (userVerifications.isEmpty())
            return null;

        return userVerifications.get(0);
    }

    public UserVerification fetchByVerificationCode(final String verificationCode)
            throws SQLException
    {
        List<UserVerification> userVerifications = executeQuery(FETCH_BY_VERIFICATION_CODE, new Statement.Initializer() {
            @Override
            public void prepare(PreparedStatement stmt) throws SQLException {
                stmt.setString(1, verificationCode);
            }
        });

        if (userVerifications.isEmpty())
            return null;

        return userVerifications.get(0);
    }


    public UserVerification fetchByDate(final String date)
            throws SQLException
    {
        List<UserVerification> userVerifications = executeQuery(FETCH_BY_DATE, new Statement.Initializer() {
            @Override
            public void prepare(PreparedStatement stmt) throws SQLException {
                stmt.setDate(1, Date.valueOf(date));
            }
        });

        if (userVerifications.isEmpty())
            return null;

        return userVerifications.get(0);
    }

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

    public boolean insert(final UserVerification userVerification)
            throws SQLException
    {
        return executeUpdate(INSERT, new Statement.Initializer() {
            @Override
            public void prepare(PreparedStatement stmt)
                    throws SQLException {
                stmt.setString(1, userVerification.getUsername());
                stmt.setString(2, userVerification.getVerificationCode());
                stmt.setTimestamp(3, userVerification.getDate());
            }
        }) > 0;

    }

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

    public boolean update(final UserVerification userVerification)
            throws SQLException
    {
        return executeUpdate(UPDATE, new Statement.Initializer() {
            @Override
            public void prepare(PreparedStatement stmt)
                    throws SQLException {
                stmt.setString(1, userVerification.getUsername());
                stmt.setString(2, userVerification.getVerificationCode());
                stmt.setTimestamp(3, userVerification.getDate());
                stmt.setInt(4, userVerification.getId());
            }
        }) > 0;
    }

    protected UserVerification fromResultSet(ResultSet set)
            throws SQLException
    {
        UserVerification userVerification = new UserVerification(set.getString("username"));

        userVerification.setId(set.getInt("id"));
        userVerification.setVerificationCode(set.getString("verification_code"));
        userVerification.setDate(set.getTimestamp("date"));

        return userVerification;
    }

    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<UserVerification> 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<UserVerification> results = new ArrayList<>();

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

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

    protected List<UserVerification> 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;
    }

}