package eu.dnetlib.openaire.user.registeredService;

import eu.dnetlib.openaire.user.pojos.RegisteredService;
import eu.dnetlib.openaire.user.store.DataSourceConnector;
import eu.dnetlib.openaire.user.store.Statement;
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;

@Component(value = "registeredServiceSQL")
public class RegisteredServiceSQL implements RegisteredServiceDao {

    @Autowired
    DataSourceConnector dataSourceConnector;

    private final static String INSERT = "INSERT INTO registered_service (" +
            "client_id, " +
            "owner, " +
            "name, " +
            "creation_date, " +
            "registration_access_token," +
            "key_type" +
            ") VALUES (?,?,?,?,?,?)";

    private final static String UPDATE = "UPDATE registered_service SET " +
            "name = ?, key_type = ? " +
            "WHERE client_id = ?";

    private final static String DELETE = "DELETE FROM registered_service WHERE id = ?";

    private final static String SELECT = "SELECT * FROM registered_service WHERE owner = ? ORDER BY creation_date DESC";

    private final static String COUNT = "SELECT COUNT(*) FROM registered_service WHERE owner = ?";
    @Override
    public int insertRegisteredService(final RegisteredService registeredService)
            throws SQLException {
        return executeUpdate(INSERT, new Statement.Initializer() {
            @Override
            public void prepare(PreparedStatement stmt)
                    throws SQLException {
                stmt.setString(1, registeredService.getClientId());
                stmt.setString(2, registeredService.getOwner());
                stmt.setString(3, registeredService.getName());
                stmt.setTimestamp(4, registeredService.getDate());
                stmt.setString(5, registeredService.getRegistrationAccessToken());
                stmt.setString(6, registeredService.getKeyType());
            }
        });
    }

    @Override
    public boolean update(final RegisteredService registeredService) throws SQLException {
        System.out.println("UPDATE registered_service SET " +
                        "name = " + registeredService.getName() +
                        ", key_type = " + registeredService.getKeyType() +
                        " WHERE client_id = " +  registeredService.getClientId());
        return executeUpdate(UPDATE, new Statement.Initializer() {

            @Override
            public void prepare(PreparedStatement stmt)
                    throws SQLException {
                stmt.setString(1, registeredService.getName());
                stmt.setString(2, registeredService.getKeyType());
                stmt.setString(3, registeredService.getClientId());
            }
        }) > 0;
    }

    @Override
    public boolean delete(final int id)
            throws SQLException {
        System.out.println("DELETE FROM registered_service WHERE id = " + id);

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

    @Override
    public RegisteredService fetchRegisteredServiceById(final int id) throws SQLException {
        System.out.println("SELECT * FROM registered_service WHERE id = " +id);

        List<RegisteredService> registeredServices = executeQuery("SELECT * FROM registered_service WHERE id = ? ", new Statement.Initializer() {
            @Override
            public void prepare(PreparedStatement stmt) throws SQLException {
                stmt.setInt(1, id);
            }
        });

        if (registeredServices.isEmpty()) {
            return null;
        }

        return registeredServices.get(0);
    }

    @Override
    public List<RegisteredService> fetchAllRegisteredServicesByOwner(final String owner) throws SQLException {
        List<RegisteredService> registeredServices = executeQuery(SELECT, new Statement.Initializer() {
            @Override
            public void prepare(PreparedStatement stmt) throws SQLException {
                stmt.setString(1, owner);
            }
        });
        return registeredServices;
    }


    protected List<RegisteredService> 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<RegisteredService> results = new ArrayList<>();

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

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

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

    protected RegisteredService fromResultSet(ResultSet set) throws SQLException {
        RegisteredService registeredService = new RegisteredService();

        registeredService.setId(set.getString("id"));
        registeredService.setClientId(set.getString("client_id"));
        registeredService.setOwner(set.getString("owner"));
        registeredService.setName(set.getString("name"));
        registeredService.setDate(set.getTimestamp("creation_date"));
        registeredService.setRegistrationAccessToken(set.getString("registration_access_token"));
        registeredService.setKeyType(set.getString("key_type"));

        return registeredService;
    }

    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();
        }
    }

    public long countRegisteredServices(String owner) throws SQLException {
        long numberOfRegisteredServices = executeCount(COUNT, new Statement.Initializer() {
            @Override
            public void prepare(PreparedStatement stmt) throws SQLException {
                stmt.setString(1, owner);
            }
        });

        return numberOfRegisteredServices;
    }

    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();
        }
    }
}