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.missioncontrol.dto.Registration;
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.Instant;
import java.time.LocalDate;
import java.time.OffsetDateTime;
import java.time.ZoneId;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

@Repository
public class RegistrationDatabaseDAO implements RegistrationDAO {

    private JDBCConnectionManager jdbcConnectionManager;
    private EmployeeDAO employeePersistence;

    @Autowired
    public RegistrationDatabaseDAO(
            JDBCConnectionManager jdbcConnectionManager, EmployeeDAO employeePersistence) {
        this.jdbcConnectionManager = jdbcConnectionManager;
        this.employeePersistence = employeePersistence;
    }

    private long getVehicleVersionId(long vehicleId) throws PersistenceException {
        String sqlGetVehicleVersionId = "SELECT * FROM vehicle WHERE id = ?";
        try (PreparedStatement stmt =
                jdbcConnectionManager.getConnection().prepareStatement(sqlGetVehicleVersionId)) {
            stmt.setLong(1, vehicleId);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getLong("version");
                } else {
                    throw new PersistenceException("vehicle id not found");
                }
            }
        } catch (SQLException e) {
            throw new PersistenceException(e);
        }
    }

    private long getEmployeeVersionId(long employeeId) throws PersistenceException {
        String sqlGetEmployeeVersionId = "SELECT * FROM employee WHERE id = ?";
        try (PreparedStatement stmt =
                jdbcConnectionManager.getConnection().prepareStatement(sqlGetEmployeeVersionId)) {
            stmt.setLong(1, employeeId);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getLong("version");
                } else {
                    throw new PersistenceException("employee id not found");
                }
            }
        } catch (SQLException e) {
            throw new PersistenceException(e);
        }
    }

    @Override
    public Set<Long> add(long vehicleId, Set<Registration> registrations)
            throws PersistenceException {
        String sql =
                "INSERT INTO Registration (vehicleId, employeeId, start, end, active) VALUES (?,?,?,?,?)";
        String sql2 = "UPDATE Vehicle SET status = 'FREI_WACHE' WHERE id = ?;";

        Set<Long> vehicleIds = new HashSet<>();

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

            try (PreparedStatement pstmt =
                    con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {

                // vehicleId is a Vehicle.id as it comes from GUI => fetch VehicleVersion.id
                pstmt.setLong(1, getVehicleVersionId(vehicleId));

                for (Registration r : registrations) {
                    pstmt.setLong(2, getEmployeeVersionId(r.employee().id()));
                    pstmt.setObject(3, OffsetDateTime.ofInstant(r.start(), ZoneId.systemDefault()));
                    pstmt.setObject(4, OffsetDateTime.ofInstant(r.end(), ZoneId.systemDefault()));
                    pstmt.setBoolean(5, true);
                    pstmt.addBatch();
                }

                pstmt.executeBatch();

                try (ResultSet rs = pstmt.getGeneratedKeys()) {
                    while (rs.next()) vehicleIds.add(rs.getLong(1));
                }
            }

            try (PreparedStatement pstmt = con.prepareStatement(sql2)) {
                pstmt.setLong(1, vehicleId);
                if (pstmt.executeUpdate() != 1) {
                    con.rollback();
                    throw new PersistenceException("Failed to persist registration");
                }
            }

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

    @Override
    public void remove(long id) throws ElementNotFoundException, PersistenceException {
        String sql = "UPDATE Registration SET active = 0, end = ? WHERE id = ?";

        try {
            Connection con = jdbcConnectionManager.getConnection();

            try (PreparedStatement pstmt = con.prepareStatement(sql)) {
                pstmt.setObject(1, OffsetDateTime.ofInstant(Instant.now(), ZoneId.systemDefault()));
                pstmt.setLong(2, id);

                if (pstmt.executeUpdate() != 1)
                    throw new ElementNotFoundException("No such registrationId exists");
            }

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

    protected List<Registration> list(long vehicleId) throws PersistenceException {

        String sql =
                "SELECT * FROM Registration r "
                        + "JOIN EmployeeVersion ev ON ev.id = r.employeeId "
                        + "JOIN VehicleVersion vv ON vv.id = r.vehicleId "
                        + "WHERE r.vehicleId = ?";

        try (PreparedStatement stmt = jdbcConnectionManager.getConnection().prepareStatement(sql)) {

            List<Registration> registrationList = new ArrayList<>();
            stmt.setLong(1, vehicleId); // is vehicle version id!
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {

                Employee employee =
                        Employee.builder()
                                .id(rs.getLong("EmployeeVersion.id"))
                                .name(rs.getString("EmployeeVersion.name"))
                                .birthday(rs.getObject("EmployeeVersion.birthday", LocalDate.class))
                                .educationLevel(
                                        EducationLevel.valueOf(
                                                rs.getString("EmployeeVersion.educationLevel")))
                                .isDriver(rs.getBoolean("EmployeeVersion.isDriver"))
                                .isPilot(rs.getBoolean("EmployeeVersion.isPilot"))
                                .build();

                Registration registration =
                        Registration.builder()
                                .id(rs.getLong("Registration.id"))
                                .start(
                                        (rs.getObject("Registration.start", OffsetDateTime.class))
                                                .toInstant())
                                .end(
                                        (rs.getObject("Registration.end", OffsetDateTime.class))
                                                .toInstant())
                                .employee(employee)
                                .build();

                registrationList.add(registration);
            }

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