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.Vehicle;
import at.ac.tuwien.sepm.assignment.groupphase.missioncontrol.dto.Vehicle.ConstructionType;
import at.ac.tuwien.sepm.assignment.groupphase.missioncontrol.dto.Vehicle.Status;
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.sql.Statement;
import java.util.HashSet;
import java.util.Set;
import org.springframework.stereotype.Repository;

@Repository
public class VehicleDatabaseDAO implements VehicleDAO {

    private final JDBCConnectionManager jdbcConnectionManager;
    private RegistrationDatabaseDAO registrationDatabaseDao;

    public VehicleDatabaseDAO(
            JDBCConnectionManager j, RegistrationDatabaseDAO registrationDatabaseDao) {
        jdbcConnectionManager = j;
        this.registrationDatabaseDao = registrationDatabaseDao;
    }

    @Override
    public long add(Vehicle v) throws PersistenceException {
        String sql =
                "INSERT INTO VehicleVersion (name,hasNef,constructionType,type) VALUES (?,?,?,?)";
        String sql2 = "INSERT INTO Vehicle (version,status) VALUES (?,?)";
        String sql3 = "UPDATE VehicleVersion SET name=? WHERE id=?";

        try {
            Connection con = jdbcConnectionManager.getConnection();
            con.setAutoCommit(false);
            String name = "";
            long version, id;

            try (PreparedStatement pstmt =
                    con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
                pstmt.setString(1, name);
                pstmt.setBoolean(2, v.hasNef());
                pstmt.setInt(3, v.constructionType().ordinal());
                pstmt.setString(4, v.type().name());
                pstmt.executeUpdate();

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

                    version = rs.getLong(1);
                }
            }

            try (PreparedStatement pstmt =
                    con.prepareStatement(sql2, Statement.RETURN_GENERATED_KEYS)) {
                pstmt.setLong(1, version);
                pstmt.setInt(2, Status.ABGEMELDET.ordinal());
                pstmt.executeUpdate();

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

                    id = rs.getLong(1);
                }

                name = v.type().name() + "-" + id;
            }

            try (PreparedStatement pstmt = con.prepareStatement(sql3)) {
                pstmt.setString(1, name);
                pstmt.setLong(2, version);

                if (pstmt.executeUpdate() != 1) {
                    con.rollback();
                    throw new PersistenceException("Failed to update VehicleVersion");
                }
            }

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

    @Override
    public void update(Vehicle v) throws ElementNotFoundException, PersistenceException {
        String sql = "SELECT version FROM Vehicle WHERE id = ?";
        String sql2 =
                "MERGE INTO VehicleVersion(name, constructionType, type, hasNef)"
                        + " KEY(name, constructionType, type, hasNef) VALUES(?, ?, ?, ?)";
        String sql3 = "UPDATE Vehicle SET version = ?, status = ? WHERE id = ?";

        long versionId;

        try {
            Connection con = jdbcConnectionManager.getConnection();
            con.setAutoCommit(false);
            try (PreparedStatement pstmt = con.prepareStatement(sql)) {
                pstmt.setLong(1, v.id());

                try (ResultSet rs = pstmt.executeQuery()) {
                    if (!rs.next()) throw new ElementNotFoundException("No such vehicleId exists");

                    versionId = rs.getLong(1);
                }
            }

            try (PreparedStatement pstmt =
                    con.prepareStatement(sql2, Statement.RETURN_GENERATED_KEYS)) {
                pstmt.setString(1, v.type().name() + "-" + v.id());
                pstmt.setString(2, v.constructionType().name());
                pstmt.setString(3, v.type().name());
                pstmt.setBoolean(4, v.hasNef());
                pstmt.executeUpdate();

                try (ResultSet rs = pstmt.getGeneratedKeys()) {
                    if (rs.next()) {
                        // version changed, update it
                        versionId = rs.getLong(1);
                    }
                }
            }

            try (PreparedStatement pstmt = con.prepareStatement(sql3)) {
                pstmt.setLong(1, versionId);
                pstmt.setString(2, v.status().name());
                pstmt.setLong(3, v.id());
                pstmt.executeUpdate();
            }

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

    @Override
    public Set<Vehicle> list() throws PersistenceException {
        Set<Vehicle> result = new HashSet<>();

        String sql =
                "Select * from VehicleVersion, Vehicle where VehicleVersion.id=Vehicle.version";

        try (PreparedStatement pstmt =
                jdbcConnectionManager.getConnection().prepareStatement(sql)) {
            pstmt.executeQuery();
            try (ResultSet rs = pstmt.getResultSet()) {
                while (rs.next()) {
                    result.add(vehicleFromRS(rs));
                }
            }
        } catch (SQLException e) {
            throw new PersistenceException(e);
        }
        return result;
    }

    @Override
    public Vehicle get(long id) throws ElementNotFoundException, PersistenceException {
        String sql =
                "SELECT *"
                        + " FROM Vehicle a"
                        + " INNER JOIN VehicleVersion b"
                        + " ON version = b.id"
                        + " WHERE a.id = ?";

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

                try (ResultSet rs = pstmt.executeQuery()) {
                    if (!rs.first()) throw new ElementNotFoundException("No such vehicle exists");

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

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

    private Vehicle vehicleFromRS(ResultSet rs) throws SQLException, PersistenceException {
        return Vehicle.builder()
                .id(rs.getLong("Vehicle.id"))
                .name(rs.getString("name"))
                .constructionType(ConstructionType.values()[rs.getInt("constructionType")])
                .type(VehicleType.valueOf(rs.getString("type")))
                .status(Status.values()[rs.getInt("status")])
                .hasNef(rs.getBoolean("hasNef"))
                .registrations(registrationDatabaseDao.list(rs.getLong("Vehicle.version")))
                .build();
    }
}