package OpgA;

import java.awt.List;
import java.sql.*;
import java.util.ArrayList;

public class Service {

	private static Connection conn = null;
	private static int returId = -1;
	private static int returNr = -1;
	private static int delId = -1;
	private static int delIdNy = -1;

	public static Connection createConnection() {
		try {
			String userName = "root";
			String password = "whenever";
			String url = "jdbc:mysql://localhost/carletti1";
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection(url, userName, password);
			System.out.println("\tDatabase connection established");
		} catch (Exception e) {
			System.err.println("Cannot connect to database server: " + e);
			throw new RuntimeException(e.getMessage());
		}
		return conn;

	}

	public static void closeConnection() {
		if (conn == null)
			return;

		try {
			conn.close();
			System.out.println("\tDatabase connection closed");
		} catch (SQLException e) {
			System.out.println("Cann't close connection\n " + e.getMessage());
			throw new RuntimeException(e.getMessage());
		}
	}

	public static void startTransaction() {
		try {
			conn.setAutoCommit(false);
			conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
		} catch (SQLException e) {
			throw new RuntimeException(e.getMessage());
		}
	}

	public static void commit() {
		try {
			conn.commit();
		} catch (SQLException e) {
			System.out.println(e);
		}
	}

	public static void rollback() {
		try {
			conn.rollback();
		} catch (SQLException e) {
			throw new RuntimeException(e.getMessage());
		}
	}

    enum IsoLevel
    {
        READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE;
    }

    public static void settransactionIsolationLevel(IsoLevel level) {
        try {
            switch (level) {
            case READ_UNCOMMITTED:
                conn.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
                break;
            case READ_COMMITTED:
                conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
                break;
            case REPEATABLE_READ:
                conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
                break;
            case SERIALIZABLE:
                conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
                break;
            default:
                break; //not possible
            }
        }
        catch (SQLException e) {
            throw new RuntimeException(e.getMessage());
        }
    }

	public static String afslutDelbehandling(int mellemvare) {
		try {
			PreparedStatement p = conn
					.prepareStatement("SELECT retur_id, returnr, del_id FROM retur WHERE mellemvare_id = ? ORDER BY tidspunkt DESC LIMIT 1");
			p.setInt(1, mellemvare);
			ResultSet rs = p.executeQuery();
			rs.first();
			returId = rs.getInt("retur_id");
			returNr = rs.getInt("returnr");
			delId = rs.getInt("del_id");

			p = conn.prepareStatement("UPDATE retur SET afsluttet = 1 WHERE retur_id = ?");
			p.setInt(1, returId);

			rs.close();
			p.close();

		} catch (SQLException e) {
			throw new RuntimeException(e.getMessage());
		}
		return " ";
	}

	public static String begyndNyDelbehandling(int mellemvare, String date) {
		try {
			PreparedStatement p = conn
					.prepareStatement("SELECT del_id, raeknr FROM delbehandling  LEFT JOIN raekkefoelge ON delbehandling.del_id = raekkefoelge.delbehandling_id LEFT JOIN behandling ON behandling.beh_id = raekkefoelge.behandling_id LEFT JOIN produkttype ON produkttype.beh_id = behandling.beh_id LEFT JOIN mellemvare ON mellemvare.prod_id = produkttype.prod_id WHERE mellemvare.mel_id = ? AND raeknr > ? ORDER BY raekkefoelge.raeknr ASC LIMIT 1");
			p.setInt(1, mellemvare);
			p.setInt(2, returNr);
			ResultSet rs2 = p.executeQuery();
			rs2.first();
			delIdNy = rs2.getInt("del_id");

			p = conn.prepareStatement("INSERT INTO retur VALUES(0, ?, ?, 0, ?, ?)");
			p.setInt(1, returNr + 1);
			p.setString(2, date);
			p.setInt(3, delIdNy);
			p.setInt(4, mellemvare);

			p.executeUpdate();
			rs2.close();
			p.close();
		} catch (SQLException e) {
			throw new RuntimeException("Mellemvaren er færdigbehandlet og der kan derfor ikke tilføjes flere delbehandlinger");
		}
		return " ";
	}
}