package at.ac.tuwien.sepm.assignment.groupphase.missioncontrol.dao;

import at.ac.tuwien.sepm.assignment.groupphase.exception.ElementNotFoundException;
import at.ac.tuwien.sepm.assignment.groupphase.exception.PersistenceException;
import at.ac.tuwien.sepm.assignment.groupphase.missioncontrol.dto.Employee;
import at.ac.tuwien.sepm.assignment.groupphase.missioncontrol.dto.Employee.EducationLevel;
import at.ac.tuwien.sepm.assignment.groupphase.util.JDBCConnectionManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.LocalDate;
import java.util.HashSet;
import java.util.Set;
import org.springframework.stereotype.Repository;

@Repository
public class EmployeeDatabaseDAO implements EmployeeDAO {

    private JDBCConnectionManager jdbcConnectionManager;

    public EmployeeDatabaseDAO(JDBCConnectionManager jdbcConnectionManager) {
        this.jdbcConnectionManager = jdbcConnectionManager;
    }

    private long createEmployeeVersion(Connection con, Employee e)
            throws PersistenceException, SQLException {
        String sql =
                "INSERT INTO EmployeeVersion(name, birthday, educationLevel, isDriver, isPilot) "
                        + "VALUES(?, ?, ?, ?, ?)";

        try (PreparedStatement pstmt = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            pstmt.setString(1, e.name());
            pstmt.setObject(2, e.birthday());
            pstmt.setInt(3, e.educationLevel().ordinal());
            pstmt.setBoolean(4, e.isDriver());
            pstmt.setBoolean(5, e.isPilot());
            pstmt.executeUpdate();

            try (ResultSet rs = pstmt.getGeneratedKeys()) {
                if (!rs.next()) throw new PersistenceException("Failed to insert EmployeeVersion");

                return rs.getLong(1);
            }
        }
    }

    @Override
    public long add(Employee employee) throws PersistenceException {
        String sql = "INSERT INTO Employee(version) VALUES(?)";

        try {
            Connection con = jdbcConnectionManager.getConnection();
            con.setAutoCommit(false);

            long versionId = createEmployeeVersion(con, employee);

            try (PreparedStatement pstmt =
                    con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
                pstmt.setLong(1, versionId);
                pstmt.executeUpdate();

                try (ResultSet rs = pstmt.getGeneratedKeys()) {
                    if (!rs.next()) {
                        con.rollback();
                        throw new PersistenceException("Failed to insert Employee");
                    }

                    con.commit();
                    return rs.getLong(1);
                }
            }
        } catch (SQLException e) {
            jdbcConnectionManager.rollbackConnection();
            throw new PersistenceException(e);
        }
    }

    @Override
    public void update(Employee employee) throws ElementNotFoundException, PersistenceException {
        String sql = "UPDATE Employee SET version = ? WHERE id = ?";

        try {
            Connection con = jdbcConnectionManager.getConnection();
            con.setAutoCommit(false);

            long versionId = createEmployeeVersion(con, employee);

            try (PreparedStatement pstmt = con.prepareStatement(sql)) {
                pstmt.setLong(1, versionId);
                pstmt.setLong(2, employee.id());

                if (pstmt.executeUpdate() != 1) {
                    con.rollback();
                    throw new ElementNotFoundException("No such employeeId exists");
                }
            }

            con.commit();
        } catch (SQLException e) {
            jdbcConnectionManager.rollbackConnection();
            throw new PersistenceException(e);
        }
    }

    @Override
    public Set<Employee> list() throws PersistenceException {
        String sql =
                "SELECT emp.id, v.name, v.birthday, v.educationLevel, v.isDriver, v.isPilot "
                        + "FROM employee emp "
                        + "JOIN EmployeeVersion v ON v.id = emp.version";

        try {
            Connection con = jdbcConnectionManager.getConnection();
            Set<Employee> employees = new HashSet<>();

            try (PreparedStatement pstmt = con.prepareStatement(sql)) {
                try (ResultSet rs = pstmt.executeQuery()) {
                    while (rs.next()) {
                        employees.add(
                                Employee.builder()
                                        .id(rs.getLong(1))
                                        .name(rs.getString(2))
                                        .birthday(rs.getObject(3, LocalDate.class))
                                        .educationLevel(EducationLevel.valueOf(rs.getString(4)))
                                        .isDriver(rs.getBoolean(5))
                                        .isPilot(rs.getBoolean(6))
                                        .build());
                    }
                }
            }

            return employees;
        } catch (SQLException e) {
            throw new PersistenceException(e);
        }
    }

    @Override
    public void remove(long id) throws ElementNotFoundException, PersistenceException {
        throw new UnsupportedOperationException();
    }
}