package at.ac.tuwien.sepm.assignment.groupphase.demeter.dao.ImplementationsDAO;

import at.ac.tuwien.sepm.assignment.groupphase.demeter.dao.InterfacesDAO.IStaffDAO;
import at.ac.tuwien.sepm.assignment.groupphase.demeter.dto.Staff;
import at.ac.tuwien.sepm.assignment.groupphase.demeter.dto.StaffSearch;
import at.ac.tuwien.sepm.assignment.groupphase.exception.PersistenceException;
import at.ac.tuwien.sepm.assignment.groupphase.util.JDBCConnectionManager;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.lang.invoke.MethodHandles;
import java.sql.*;
import java.time.LocalDate;
import java.util.ArrayList;

@Repository
public class StaffDAO implements IStaffDAO {

    private Connection connection;

    private static final Logger LOG = LoggerFactory.getLogger(MethodHandles.lookup().lookupClass());
    private static final String GET_STAFF_DATA = "SELECT * FROM staff WHERE username = ?";
    private static final String GET_ALL_STAFF_QUERY = "SELECT * FROM staff";
    private static final String HIRE_STAFF_QUERY = "INSERT INTO staff (name, username, password, svnr, iban, owner, hire_date, fire_date) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
    private static final String UPDATE_STAFF_QUERY_WITH_PW = "UPDATE staff SET name = ?, username = ?, password = ?, svnr = ?, iban = ?, owner = ?, hire_date = ? WHERE id = ?";
    private static final String UPDATE_STAFF_QUERY_WITHOUT_PW = "UPDATE staff SET name = ?, username = ?, svnr = ?, iban = ?, owner = ?, hire_date = ? WHERE id = ?";
    private static final String FIRE_STAFF_QUERY = "UPDATE staff SET fire_date = ? WHERE id = ?";
    private static final String SEARCH_HIRED_STAFF = "SELECT * FROM STAFF WHERE NAME ILIKE ? AND USERNAME ILIKE ? AND HIRE_DATE >= ? AND FIRE_DATE IS ?";
    private static final String SEARCH_FIRED_STAFF = "SELECT * FROM STAFF WHERE NAME ILIKE ? AND USERNAME ILIKE ? AND HIRE_DATE >= ? AND FIRE_DATE IS NOT ?";
    private static final String SEARCH_STAFF_ALL = "SELECT * FROM STAFF WHERE NAME ILIKE ? AND USERNAME ILIKE ? AND HIRE_DATE >= ?";


    @Autowired
    public StaffDAO(JDBCConnectionManager jdbcConnectionManager) throws PersistenceException{
        try{
            connection = jdbcConnectionManager.getConnection();
        } catch (SQLException e){
            LOG.error(e.getMessage());
            throw new PersistenceException(e);
        }
    }


    @Override
    public Staff getStaff(String username) throws PersistenceException {
        LOG.info("Retrieving data from database.");
        try {
            var getStaffStatement = connection.prepareStatement(GET_STAFF_DATA);

            getStaffStatement.setString(1, username);
            var resultSet = getStaffStatement.executeQuery();
            if (resultSet.next()){
                var staff = new Staff();
                staff.setId(resultSet.getLong(1));
                staff.setName(resultSet.getString(2));
                staff.setUsername(resultSet.getString(3));
                staff.setSvnr(resultSet.getString(5));
                staff.setIban(resultSet.getString(6));
                staff.setOwner(resultSet.getBoolean(7));
                staff.setHireDate(resultSet.getDate(8).toLocalDate());
                staff.setFireDate(null);
                staff.setStatus(false);
                return staff;
            }else {
                throw new PersistenceException("No user with this username");
            }

        } catch (SQLException e) {
            LOG.error(e.getMessage());
            throw new PersistenceException(e);
        }
    }

    @Override
    public ArrayList<Staff> getAllStaff() throws PersistenceException {
        ArrayList<Staff> searchResult = new ArrayList<>();
        LOG.info("Retrieving all staff from database.");
        try {
            var getAllStaffStatement = connection.prepareStatement(GET_ALL_STAFF_QUERY);

            var resultSet = getAllStaffStatement.executeQuery();
            while (resultSet.next()){
                var staff = new Staff();
                setAllStaff(staff, resultSet);
                searchResult.add(staff);
            }
            resultSet.close();
            getAllStaffStatement.close();
        }catch (SQLException e){
            LOG.error(e.getMessage());
            throw new PersistenceException(e);
        }
        return searchResult;
    }

    @Override
    public void hire(Staff staffToHire) throws PersistenceException {
        try {
            LOG.info("Inserting");

            var hireStaffStatement = connection.prepareStatement(HIRE_STAFF_QUERY);

            hireStaffStatement.setString(1, staffToHire.getName());
            hireStaffStatement.setString(2, staffToHire.getUsername());
            hireStaffStatement.setString(3, staffToHire.getPassword());
            hireStaffStatement.setString(4, staffToHire.getSvnr());
            hireStaffStatement.setString(5, staffToHire.getIban());
            hireStaffStatement.setBoolean(6, staffToHire.getOwner());
            hireStaffStatement.setDate(7, Date.valueOf(staffToHire.getHireDate()));
            hireStaffStatement.setDate(8, staffToHire.getFireDate() == null ? null : Date.valueOf(staffToHire.getFireDate()));
            hireStaffStatement.executeUpdate();
            LOG.info("Saved successfully");
            hireStaffStatement.close();
        }catch (SQLException e){
            throw new PersistenceException(e);
        }
    }

    @Override
    public void updateStaff(Staff staffToUpdate) throws PersistenceException {
        LOG.info("Updating staff with id = " + staffToUpdate.getId());
        try {
            PreparedStatement updateStaffStatement;
            if (staffToUpdate.getPassword().isEmpty()){
                updateStaffStatement = connection.prepareStatement(UPDATE_STAFF_QUERY_WITHOUT_PW);
            }else {
                updateStaffStatement = connection.prepareStatement(UPDATE_STAFF_QUERY_WITH_PW);
            }
            var counter = 1;
            updateStaffStatement.setString(counter++, staffToUpdate.getName());
            updateStaffStatement.setString(counter++, staffToUpdate.getUsername());
            if (!staffToUpdate.getPassword().isEmpty()) {
                updateStaffStatement.setString(counter++, staffToUpdate.getPassword());
            }
            updateStaffStatement.setString(counter++, staffToUpdate.getSvnr());
            updateStaffStatement.setString(counter++, staffToUpdate.getIban());
            updateStaffStatement.setBoolean(counter++, staffToUpdate.getOwner());
            updateStaffStatement.setDate(counter++, Date.valueOf(staffToUpdate.getHireDate()));
            updateStaffStatement.setLong(counter, staffToUpdate.getId());
            updateStaffStatement.executeUpdate();
            LOG.info("Update successful");
            updateStaffStatement.close();
        } catch (SQLException e) {
            LOG.error(e.getMessage());
            throw new PersistenceException(e);
        }
    }

    @Override
    public void fire(Staff staffToFire) throws PersistenceException {
        LOG.info("Firing staff with id = " + staffToFire.getId());
        try {

            var fireStaffStatement = connection.prepareStatement(FIRE_STAFF_QUERY);

            fireStaffStatement.setDate(1, Date.valueOf(LocalDate.now()));
            fireStaffStatement.setLong(2, staffToFire.getId());
            fireStaffStatement.executeUpdate();
            LOG.info("Fire successful");
            fireStaffStatement.close();
        } catch (SQLException e) {
            LOG.error(e.getMessage());
            throw new PersistenceException(e);
        }
    }

    @Override
    public ArrayList<Staff> getSearchedStaff(StaffSearch staffSearch) throws PersistenceException {
        LOG.info("Retrieving all searched Staff!");
        ArrayList<Staff> searchResult = new ArrayList<>();
        PreparedStatement getSearchedStaff;
        try{

            if(staffSearch.getStatus().equals("all")){
                getSearchedStaff = connection.prepareStatement(SEARCH_STAFF_ALL);
            }else if (staffSearch.getStatus().equals("hired")){
                getSearchedStaff = connection.prepareStatement(SEARCH_HIRED_STAFF);
            }else {
                getSearchedStaff = connection.prepareStatement(SEARCH_FIRED_STAFF);
            }

            if(!staffSearch.getName().isEmpty()){
                getSearchedStaff.setString(1, "%"+staffSearch.getName()+"%");
            }else{
                getSearchedStaff.setString(1, "%");
            }

            if(!staffSearch.getUsername().isEmpty()){
                getSearchedStaff.setString(2, "%"+staffSearch.getUsername()+"%");
            }else{
                getSearchedStaff.setString(2, "%");
            }

            if(staffSearch.getHireDate() != null) {
                getSearchedStaff.setDate(3, Date.valueOf(staffSearch.getHireDate()));
            }else{
                getSearchedStaff.setDate(3, Date.valueOf("1000-11-11"));
            }

            switch (staffSearch.getStatus()){
                case "hired":
                    getSearchedStaff.setDate(4, null);
                    break;
                case "fired":
                    getSearchedStaff.setDate(4, null);
                    break;
            }
            var resultSet = getSearchedStaff.executeQuery();
            while (resultSet.next()){
                var staff = new Staff();
                setAllStaff(staff, resultSet);
                searchResult.add(staff);
            }
            resultSet.close();
            getSearchedStaff.close();

        }catch (SQLException e) {
            LOG.error(e.getMessage());
            throw new PersistenceException(e);
        }
        return searchResult;
    }

    private void setAllStaff(Staff staff, ResultSet resultSet) throws SQLException {
        staff.setId(resultSet.getLong(1));
        staff.setName(resultSet.getString(2));
        staff.setUsername(resultSet.getString(3));
        staff.setSvnr(resultSet.getString(5));
        staff.setIban(resultSet.getString(6));
        staff.setOwner(resultSet.getBoolean(7));
        staff.setHireDate(resultSet.getDate(8).toLocalDate());
        staff.setFireDate(resultSet.getDate(9) == null ? null : resultSet.getDate(9).toLocalDate());
        staff.setStatus(staff.getFireDate() == null);
    }
}