package de.unikn.bioml.mpi2.stuvove.implementation.db;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import de.unikn.bioml.mpi2.stuvove.definitions.core.IDeserializer;
import de.unikn.bioml.mpi2.stuvove.definitions.core.ILecture;
import de.unikn.bioml.mpi2.stuvove.definitions.core.ILecturer;
import de.unikn.bioml.mpi2.stuvove.definitions.core.IManager;
import de.unikn.bioml.mpi2.stuvove.definitions.core.IRoom;
import de.unikn.bioml.mpi2.stuvove.definitions.core.IStudyCourse;
import de.unikn.bioml.mpi2.stuvove.definitions.core.ITimeSlot;
import de.unikn.bioml.mpi2.stuvove.implementation.core.Exercise;
import de.unikn.bioml.mpi2.stuvove.implementation.core.Lecture;
import de.unikn.bioml.mpi2.stuvove.implementation.core.Lecturer;
import de.unikn.bioml.mpi2.stuvove.implementation.core.Room;
import de.unikn.bioml.mpi2.stuvove.implementation.core.Seminar;
import de.unikn.bioml.mpi2.stuvove.implementation.core.TimeSlot;

public class DBStudyCourseDeserializer implements IDeserializer<IStudyCourse> {

    String     driver     = null;

    private List<IStudyCourse> l_studyCourses = new ArrayList<IStudyCourse>();
    private IManager manage;

      // --------------------------------------------------------------------------

      String     host       = null;

      String     port       = null;

      String     database   = null;

      String     user       = null;

      String     password   = null;

      // --------------------------------------------------------------------------

      Connection connection = null;




      public DBStudyCourseDeserializer(String jdbcDriverClass, String dbUrl,
                String user, String pw, IManager manager)
            throws SQLException, ClassNotFoundException {

          manage=manager;
          driver = jdbcDriverClass;
          host = dbUrl;
          this.user = user;
          password=pw;
      }

      private void closeConnection ()
      {
        try
        {
          connection.close ();
        }
        catch (SQLException e)
        {
          e.printStackTrace ();
          System.exit (1);
        }

        System.out.println ("\nconnection closed");
      }

      private void loadJdbcDriver ()
      {
        try
        {
          Class.forName (driver);
        }
        catch (ClassNotFoundException e)
        {
          e.printStackTrace ();
          System.exit (1);
        }

        System.out.println ("driver loaded");
      }

      private void openConnection ()
      {
        try
        {
          connection = DriverManager.getConnection (host,
                                                    user,
                                                    password);
        }
        catch (SQLException e)
        {
          e.printStackTrace ();
          System.exit (1);
        }

        System.out.println ("connection opened");
      }

    @Override
    public List<IStudyCourse> deserialize() throws IOException, ParseException {

        loadJdbcDriver ();
        openConnection ();

        int id,lec,room,maxstuds,lecture,sc_id,ts_id;
        String type;
        String title;
        String description;

        try
        {
          Statement statement1 = connection.createStatement ();
          Statement statement2 = connection.createStatement ();

          String str = "SELECT * from studycourses";
          ResultSet resultSet = statement1.executeQuery (str);

          while (resultSet.next ())
          {
              String str1 = "SELECT * from study_times";
              ResultSet resultSetagain = statement2.executeQuery (str1);

              Set<ITimeSlot> slots = new HashSet<ITimeSlot>();
              id=resultSet.getInt(1);
              type= resultSet.getString(2).replaceAll(" ", "");
              title=resultSet.getString(3).replaceAll(" ", "");
              lec=resultSet.getInt(4);
              room=resultSet.getInt(5);
              maxstuds=resultSet.getInt(6);
              description=resultSet.getString(7).replaceAll(" ", "");

              ILecturer leccy = null;
              IRoom roomy = null;
              ILecture lsso = null;

              while(resultSetagain.next()){
                  sc_id=resultSetagain.getInt(1);
                  ts_id=resultSetagain.getInt(2);
                  if(id==sc_id){
                      List<ITimeSlot> timeSlots = manage.searchTimeSlots(new TimeSlot(ts_id, null, -1,-1));
                      slots.add(timeSlots.get(0));
                      timeSlots.clear();
                  }
              }
              List<ILecturer> lecturer=manage.searchLecturers(new Lecturer(lec,null,null));
              if(lecturer.size()>0){
              leccy=lecturer.get(0);
              lecturer.clear();
              }
              List<IRoom> rooms=manage.searchRooms(new Room(room,null,-1));
              if(rooms.size()>0){
              roomy=rooms.get(0);
              rooms.clear();
              }
              if("ILecture".equals(type)){
              l_studyCourses.add(new Lecture(id,title,leccy,slots,roomy,maxstuds,description));
              }
              if("ISeminar".equals(type)){
              l_studyCourses.add(new Seminar(id,title,leccy,slots,roomy,maxstuds,description));
              }
              if("IExercise".equals(type)){
                  lecture=resultSet.getInt(8);
                  for(IStudyCourse isc : l_studyCourses){
                      if(lecture==isc.getID()){
                          lsso = (Lecture)isc;
                          l_studyCourses.add(new Exercise(id,title,leccy,slots,roomy,maxstuds,description,lsso));
                          break;
                      }
                  }
              }
              resultSetagain.close ();
          }

          resultSet.close ();

          statement1.close ();
          statement2.close ();
        }
        catch (SQLException e)
        {
             e.printStackTrace ();
        }
        closeConnection();
        return l_studyCourses;
    }


}