package opgD;

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

public class Service {

	private static Connection conn = null;

	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());
		}
	}

	public static String produkttyper() {
		try {
			PreparedStatement p = conn.prepareStatement("SELECT navn FROM produkttype ORDER BY navn;");
			ResultSet rs = p.executeQuery();

			while(rs.next()) {
				System.out.println(rs.getString("navn"));
				}
			rs.close();
			p.close();
		} catch (SQLException e) {
			throw new RuntimeException(e.getMessage());
	}
		return "";
	}

	public static String mellemvarerAfProdukttype(String produkttype) {
		try {
			PreparedStatement p = conn.prepareStatement("SELECT DISTINCT M.mel_id, P.navn, RF.raeknr, D.navn FROM produkttype P INNER JOIN mellemvare M ON P.prod_id = M.prod_id INNER JOIN retur R ON M.mel_id = R.mellemvare_id INNER JOIN delbehandling D ON R.del_id = D.del_id INNER JOIN raekkefoelge RF ON D.del_id = RF.delbehandling_id WHERE R.afsluttet = 0 AND P.navn = ? ORDER BY M.mel_id;");
			p.setString(1, produkttype);
			ResultSet rs = p.executeQuery();

			while(rs.next()){
                System.out.println("Mellemvare id: " + rs.getString("M.mel_id"));
                System.out.println("Er nået til delbehandling: " +rs.getString("RF.raeknr"));
                System.out.println( "Navn på delbehandlingen: " + rs.getString("D.navn"));
                System.out.println("--------------------");
      }
			rs.close();
			p.close();
		} catch (SQLException e) {
			throw new RuntimeException(e.getMessage());
		}
		return " ";
	}
}