package sql_project;

import java.sql.*;
import java.util.ArrayList;
import java.util.Collections;

public class Sql_project {

    public static void main(String[] args) throws Exception
    {
      //Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

      String url = "jdbc:oracle:thin:@localhost:1521:ORCL";
      String user = "rembocoder";
      String password = "mokikmynewnik";
      Connection connection = DriverManager.getConnection(url , user , password);


      Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE , ResultSet.CONCUR_UPDATABLE);

      String Q = "SELECT BAND_ID FROM BAND WHERE BAND_NAME = \'" + args[0] + "\'";
      ResultSet qb_id = statement.executeQuery(Q);
      if (!qb_id.next()) {
          System.out.println("No such band");
          return;
      }
      int b_id = qb_id.getInt(1);

      statement.close();

      statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE , ResultSet.CONCUR_UPDATABLE);

      Q = "SELECT * FROM RELEASE WHERE BAND_ID = " + b_id;
      ResultSet emps = statement.executeQuery(Q);

      ArrayList out = new ArrayList();
      while (emps.next())
      {
        int id  = emps.getInt(1);
        Date date = emps.getDate(2);
        String name = emps.getString(3);
        int sum = emps.getInt(4);
//        int b_id = emps.getInt(5);
        int num = emps.getInt(6);
        int g_id = emps.getInt(7);

/*        Q = "SELECT BAND_NAME FROM BAND WHERE BAND_ID = " + b_id;
        Statement statement2 = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE , ResultSet.CONCUR_UPDATABLE);
        ResultSet qb_name = statement2.executeQuery(Q);
        qb_name.next();*/
        String b_name = args[0];//qb_name.getString(1);
        /*qb_name.close();
        statement2.close();*/

        Q = "SELECT GENRE_NAME FROM GENRES WHERE GENRE_ID = " + g_id;
        Statement statement2 = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE , ResultSet.CONCUR_UPDATABLE);
        ResultSet qg_name = statement2.executeQuery(Q);
        qg_name.next();
        String g_name = qg_name.getString(1);
        qg_name.close();
        statement2.close();

        CallableStatement cstmt = connection.prepareCall("CALL GET_RELEASE_RATING(" + id + ") INTO ?");
        cstmt.registerOutParameter(1, Types.INTEGER);
        cstmt.executeUpdate();
        float rating = cstmt.getFloat(1);

        //System.out.println(Q);
        //System.out.println(date + " " + b_name + " - " + name + " " + rating + " " + g_name);
        out.add(date + " " + b_name + " - " + name + " " + rating + " " + g_name);
      }
      emps.close();
      statement.close();
      connection.close();
      Collections.sort(out);
      for (int i = 0; i < out.size(); ++i) {
          System.out.println(out.get(i));
      }
    }

}