import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class UpisStudenata2 {

   public static void main(String argv[]) {
      Connection connection = otvoriKonekciju();
      Statement stmt = null;

      try {


         while (true) {
            String jmbag = System.console().readLine().trim();
            stmt = connection.createStatement();

            //provjere
            ResultSet rs = stmt.executeQuery("select (select SUM(kapacitet) from labprof1.dbo.grupa ) - (select SUM(brojStud) from labprof1.dbo.grupa) ispis");
            rs.next();
            if(Integer.parseInt(rs.getString("ispis").trim()) <= 0){
                System.out.println("Sve grupe su već popunjene");
                break;
            }

            rs = stmt.executeQuery("select isnull((select COUNT(*) from labprof1.dbo.stud where jmbag = '" + jmbag + "'), 0) ispis");
            rs.next();
            if(Integer.parseInt(rs.getString("ispis").trim()) == 0) {
                System.out.println("Student ne postoji");
                continue;
            }

            rs = stmt.executeQuery("select isnull((select COUNT(*) from labprof1.dbo.studGrupa where jmbag =  '" + jmbag + "'), 0) ispis");
            rs.next();
            if(Integer.parseInt(rs.getString("ispis").trim()) >= 1) {
                System.out.println("Student je vec upisan u nastavnu grupu");
                continue;
            }

            //upis
            try{
                rs = stmt.executeQuery("select MIN(brojStud/CONVERT(decimal(4,2), kapacitet)), oznGrupa as oznGrupa " +
                                    "from labprof1.dbo.grupa group by oznGrupa " +
                                    "order by MIN(brojStud/CONVERT(decimal(4,2), kapacitet)), oznGrupa ");
                rs.next();
                String oznGrupa = rs.getString("oznGrupa").trim();
                stmt.execute("insert into labprof1.dbo.studGrupa values ('" + jmbag + "', '" + oznGrupa  + "')");
                stmt.execute("update labprof1.dbo.grupa "+
                            "set brojStud = brojStud + 1 "+
                            "where oznGrupa = '" + oznGrupa + "'");

                System.out.println("Student je uspjesno upisan");

            }
            catch (SQLException exception) {
                ispisiPogresku(exception);
                System.exit(-1);
             }


      }}
      catch (SQLException exception1) {
         ispisiPogresku(exception1);
         System.exit(-1);
         }
   }


   private static void ispisiPogresku(SQLException exception) {
      System.out.println(exception.getErrorCode() + "; "
            + exception.getMessage() + "; "
            + "State=" + exception.getSQLState());
   }

   private static Connection otvoriKonekciju () {
      // sastavljanje JDBC URL:
      String url =
              "jdbc:sqlserver://localhost:1433;"   // ovdje staviti svoje vrijednosti
            + "databaseName=labprof1;"
            + "user=sa;"
            + "password=lozinkazabazu;" ;           // staviti svoje vrijednosti

      // ucitavanje i registriranje SQL Server JDBC driver-a
      try {
         Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
         System.out.println("SQL Server JDBC driver je uèitan i registriran.");
      } catch (ClassNotFoundException exception) {
         System.out.println("Pogreška: nije uspjelo uèitavanje JDBC driver-a.");
         System.out.println(exception.getMessage());
         System.exit(-1);
      }

      // uspostavljanje konekcije
      Connection conn = null;
      try {
         conn = DriverManager.getConnection(url);
         System.out.println("Konekcija je uspostavljena.");
      } catch (SQLException exception) {
         System.out.println("Pogreška: nije uspjelo uspostavljanje konekcije.");
         System.out.println(exception.getErrorCode() + " " + exception.getMessage());
         System.exit(-1);
      }
      return conn;
   }
}