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.Operation;
import at.ac.tuwien.sepm.assignment.groupphase.missioncontrol.dto.Operation.Severity;
import at.ac.tuwien.sepm.assignment.groupphase.missioncontrol.dto.Operation.Status;
import at.ac.tuwien.sepm.assignment.groupphase.missioncontrol.dto.Vehicle;
import at.ac.tuwien.sepm.assignment.groupphase.missioncontrol.dto.Vehicle.ConstructionType;
import at.ac.tuwien.sepm.assignment.groupphase.missioncontrol.dto.Vehicle.VehicleType;
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.time.OffsetDateTime;
import java.time.ZoneId;
import java.util.EnumSet;
import java.util.HashSet;
import java.util.Set;
import java.util.stream.Collectors;
import org.springframework.lang.NonNull;
import org.springframework.stereotype.Repository;

@Repository
public class OperationDatabaseDAO implements OperationDAO {

    private JDBCConnectionManager jdbcConnectionManager;
    private VehicleDAO vehicleDAO;
    private RegistrationDatabaseDAO registrationDAO;

    public OperationDatabaseDAO(
            JDBCConnectionManager jdbcConnectionManager,
            VehicleDAO vehicleDAO,
            RegistrationDatabaseDAO registrationDAO) {
        this.jdbcConnectionManager = jdbcConnectionManager;
        this.vehicleDAO = vehicleDAO;
        this.registrationDAO = registrationDAO;
    }

    @Override
    public long add(@NonNull Operation o) throws PersistenceException {
        String sql =
                "INSERT INTO Operation(opCode, severity, created, destination, additionalInfo,"
                        + "            status) VALUES (?, ?, ?, ?, ?, ?)";
        long operationId;

        try {
            Connection con = jdbcConnectionManager.getConnection();
            con.setAutoCommit(false);
            try (PreparedStatement pstmt = con.prepareStatement(sql)) {
                pstmt.setString(1, o.opCode());
                pstmt.setInt(2, o.severity().ordinal());
                pstmt.setObject(3, OffsetDateTime.ofInstant(o.created(), ZoneId.systemDefault()));
                pstmt.setString(4, o.destination());
                pstmt.setString(5, o.additionalInfo());
                pstmt.setInt(6, o.status().ordinal());
                pstmt.executeUpdate();

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

                    operationId = rs.getLong(1);
                }
            }

            createVehicleOperation(con, operationId, o.vehicles());
            con.commit();
            return operationId;
        } catch (SQLException e) {
            jdbcConnectionManager.rollbackConnection();
            throw new PersistenceException(e);
        }
    }

    @Override
    public void update(@NonNull Operation o) throws ElementNotFoundException, PersistenceException {
        // Note this will, by design, not update created
        String sql =
                "UPDATE Operation SET opCode = ?, severity = ?, destination = ?,"
                        + " additionalInfo = ?, status = ? WHERE id = ?";
        String sql2 = "DELETE FROM VehicleOperation WHERE operationId = ?";

        try {
            Connection con = jdbcConnectionManager.getConnection();
            con.setAutoCommit(false);
            try (PreparedStatement pstmt = con.prepareStatement(sql)) {
                pstmt.setString(1, o.opCode());
                pstmt.setInt(2, o.severity().ordinal());
                pstmt.setString(3, o.destination());
                pstmt.setString(4, o.additionalInfo());
                pstmt.setInt(5, o.status().ordinal());
                pstmt.setLong(6, o.id());

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

            try (PreparedStatement pstmt = con.prepareStatement(sql2)) {
                pstmt.setLong(1, o.id());
                pstmt.executeUpdate();
            }

            createVehicleOperation(con, o.id(), o.vehicles());
            con.commit();
        } catch (SQLException e) {
            jdbcConnectionManager.rollbackConnection();
            throw new PersistenceException(e);
        }
    }

    private void createVehicleOperation(Connection con, long operationId, Set<Vehicle> vehicles)
            throws SQLException {
        String sql =
                "INSERT INTO VehicleOperation(vehicleId, operationId)"
                        + " SELECT version, ? FROM Vehicle WHERE id = ?";
        String sqlUpdateVehicleStatus =
                "UPDATE Vehicle SET status = 'ZUM_BERUFUNGSORT' WHERE id = ?";

        try (PreparedStatement pstmt = con.prepareStatement(sql);
                PreparedStatement stmtUpdateVehicleStatus =
                        con.prepareStatement(sqlUpdateVehicleStatus)) {
            pstmt.setLong(1, operationId);

            for (long id : (Iterable<Long>) vehicles.stream().map(Vehicle::id)::iterator) {
                pstmt.setLong(2, id);
                stmtUpdateVehicleStatus.setLong(1, id);
                pstmt.addBatch();
                stmtUpdateVehicleStatus.addBatch();
            }

            pstmt.executeBatch();
            stmtUpdateVehicleStatus.executeBatch();
        }
    }

    @Override
    public Operation get(long operationId) throws ElementNotFoundException, PersistenceException {
        String sql = "Select * from operation where id = ?";

        try {
            Connection con = jdbcConnectionManager.getConnection();
            try (PreparedStatement pstmt = con.prepareStatement(sql)) {
                pstmt.setLong(1, operationId);
                pstmt.execute();

                try (ResultSet rs = pstmt.getResultSet()) {
                    if (!rs.next())
                        throw new ElementNotFoundException("No such element could be found");

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

    @Override
    public Set<Operation> list(EnumSet<Status> statuses) throws PersistenceException {
        // This hack exists because H2 currently has a bug that prevents IN (?) with an array of
        // ids, i.e. pstmt.setArray(1, con.createArrayOf("INT", intarray) from working. See
        // commented code below.

        // SELECT * FROM Operation WHERE status IN ('COMPLETED', 'CANCELLED') is BUGGED on H2!
        // for this reason we use the ordinal values instead
        String str =
                statuses.stream()
                        .map(e -> Integer.toString(e.ordinal()))
                        .collect(Collectors.joining(","));

        String sql = "SELECT * FROM Operation WHERE status IN (" + str + ")";
        Set<Operation> operations = new HashSet<>();

        try {
            Connection con = jdbcConnectionManager.getConnection();

            try (PreparedStatement pstmt = con.prepareStatement(sql)) {
                // Object[] arr = statuses.stream().map(Enum::ordinal).toArray();
                // pstmt.setArray(1, con.createArrayOf("INT", arr));

                try (ResultSet rs = pstmt.executeQuery()) {
                    while (rs.next()) operations.add(operationFromRS(rs));
                }
            }

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

    private Operation operationFromRS(ResultSet rs) throws PersistenceException, SQLException {
        Long operationId = rs.getLong("id");

        return Operation.builder()
                .id(operationId)
                .opCode(rs.getString("opCode"))
                .severity(Severity.valueOf(rs.getString("severity")))
                .status(Status.valueOf(rs.getString("status")))
                .vehicles(getVehiclesFromOperationId(operationId))
                .created((rs.getObject("created", OffsetDateTime.class)).toInstant())
                .destination(rs.getString("destination"))
                .additionalInfo(rs.getString("additionalInfo"))
                .build();
    }

    private Set<Vehicle> getVehiclesFromOperationId(long operationId) throws PersistenceException {
        /*String sql =
        "SELECT id FROM Vehicle WHERE version IN"
                + " (SELECT vehicleId FROM VehicleOperation WHERE operationId = ?)";*/
        String sql =
                "SELECT vv.* FROM VehicleOperation vo JOIN VehicleVersion vv ON vv.id = vo.vehicleId WHERE operationId = ?";

        Set<Vehicle> vehicles = new HashSet<>();

        try {
            Connection con = jdbcConnectionManager.getConnection();
            try (PreparedStatement pstmt = con.prepareStatement(sql)) {
                pstmt.setLong(1, operationId);
                pstmt.execute();

                try (ResultSet rs = pstmt.getResultSet()) {
                    while (rs.next()) {
                        vehicles.add(vehicleFromRS(rs));
                    }
                }
            }
        } catch (SQLException e) {
            throw new PersistenceException(e);
        } catch (ElementNotFoundException e) {
            throw new PersistenceException("VehicleOperation contained nonexistent vehicle", e);
        }

        return vehicles;
    }

    private Vehicle vehicleFromRS(ResultSet rs)
            throws SQLException, PersistenceException, ElementNotFoundException {
        String name = rs.getString("VehicleVersion.name");
        long vehicleId = Long.parseLong(name.split("-")[1]);
        return Vehicle.builder()
                .id(vehicleId)
                .name(rs.getString("VehicleVersion.name"))
                .constructionType(
                        ConstructionType.values()[rs.getInt("VehicleVersion.constructionType")])
                .type(VehicleType.valueOf(rs.getString("VehicleVersion.type")))
                .status(vehicleDAO.get(vehicleId).status())
                .hasNef(rs.getBoolean("VehicleVersion.hasNef"))
                .registrations(registrationDAO.list(rs.getLong("VehicleVersion.id")))
                .build();
    }
}