/**
 * 
 */
package de.fhswf.in.inf.se.projektthemenvergabe;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.HashMap;

import de.fhswf.in.inf.se.projektthemenvergabe.model.Ansprechpartner;
import de.fhswf.in.inf.se.projektthemenvergabe.model.Organisation;
import de.fhswf.in.inf.se.projektthemenvergabe.model.Projekt;
import de.fhswf.in.inf.se.projektthemenvergabe.model.Student;
import de.fhswf.in.inf.se.projektthemenvergabe.view.AnsprechpartnerListeController;
import de.fhswf.in.inf.se.projektthemenvergabe.view.ProjektHinzufuegenController;
import de.fhswf.in.inf.se.projektthemenvergabe.view.ProjektthemenverwaltungsController;
import de.fhswf.in.inf.se.projektthemenvergabe.view.StudentenverwaltungsController;
import javafx.application.Application;
import javafx.application.Platform;
import javafx.beans.value.ChangeListener;
import javafx.collections.FXCollections;
import javafx.collections.ListChangeListener;
import javafx.collections.ObservableList;
import javafx.fxml.FXMLLoader;
import javafx.scene.Scene;
import javafx.scene.layout.BorderPane;
import javafx.stage.Modality;
import javafx.stage.Stage;

/**
 * Handles the stages and the data.
 *
 * @author Dina-Marie Hanxleden & Stefan Suhren
 * @version 1.0
 */
public class Main extends Application
{
   private ObservableList<Ansprechpartner> ansprechpartner = FXCollections
         .observableArrayList(Ansprechpartner.extractor());

   private ObservableList<Organisation> organisation = FXCollections
         .observableArrayList(Organisation.extractor());

   private ObservableList<Projekt> projekte = FXCollections
         .observableArrayList(Projekt.extractor());

   private ObservableList<Student> studenten = FXCollections
         .observableArrayList(Student.extractor());

   private Stage primaryStage;

   private Stage studentenverwaltungsStage;

   private Stage ansprechpartnerverwaltungsStage;

   private Stage projekthinzufuegenStage;

   private Connection dbConnection;

   /*
    * (non-Javadoc)
    * 
    * @see javafx.application.Application#start(javafx.stage.Stage)
    */
   @Override
   public void start(Stage primaryStage) throws Exception
   {
      loadData();
      registerSaveHandler();

      this.primaryStage = primaryStage;

      try
      {
         FXMLLoader loader = new FXMLLoader(
               getClass().getResource("view/Projektthemenverwaltung.fxml"));
         BorderPane root = (BorderPane) loader.load();

         Scene scene = new Scene(root);
         primaryStage.setScene(scene);
         primaryStage.setTitle("Projektthemenverwaltung");

         ProjektthemenverwaltungsController controller = loader
               .getController();
         controller.setMain(this);

         primaryStage.show();
      }
      catch (IOException e)
      {
         e.printStackTrace();
      }
   }

   /**
    * Show the Studentenverwaltung stage with the corresponding FXML.
    *
    */
   public void showStudentenverwaltung()
   {
      try
      {
         FXMLLoader loader = new FXMLLoader(
               getClass().getResource("view/Studentenverwaltung.fxml"));
         BorderPane root = (BorderPane) loader.load();

         Scene scene = new Scene(root);
         Stage stage = new Stage();

         studentenverwaltungsStage = stage;

         stage.setScene(scene);
         stage.setTitle("Studentenverwaltung");
         stage.initOwner(primaryStage);
         stage.initModality(Modality.WINDOW_MODAL);

         StudentenverwaltungsController controller = loader.getController();
         controller.setMain(this);

         stage.showAndWait();
      }
      catch (IOException e)
      {
         e.printStackTrace();
      }
   }

   /**
    * Show the Ansprechpartnerverwaltung stage with the corresponding FXML.
    *
    */
   public void showAnsprechpartnerverwaltungsStage()
   {
      try
      {
         FXMLLoader loader = new FXMLLoader(
               getClass().getResource("view/AnsprechpartnerListe.fxml"));
         BorderPane root = (BorderPane) loader.load();

         Scene scene = new Scene(root);
         Stage stage = new Stage();

         ansprechpartnerverwaltungsStage = stage;

         stage.setScene(scene);
         stage.setTitle("Ansprechpartnerverwaltung");
         stage.initOwner(primaryStage);
         stage.initModality(Modality.WINDOW_MODAL);

         AnsprechpartnerListeController controller = loader.getController();
         controller.setMain(this);

         stage.showAndWait();
      }
      catch (IOException e)
      {
         e.printStackTrace();
      }
   }

   /**
    * Show the Projekthinzufuegen stage with the corresponding FXML.
    *
    */
   public Projekt showProjekthinzufuegenStage(Projekt projekt)
   {
      try
      {
         FXMLLoader loader = new FXMLLoader(
               getClass().getResource("view/ProjektHinzufuegen.fxml"));
         BorderPane root = (BorderPane) loader.load();

         Scene scene = new Scene(root);
         Stage stage = new Stage();

         projekthinzufuegenStage = stage;

         stage.setScene(scene);
         stage.setTitle("Projekt");
         stage.initOwner(primaryStage);
         stage.initModality(Modality.WINDOW_MODAL);

         ProjektHinzufuegenController controller = loader.getController();
         controller.setMain(this, projekt);

         stage.showAndWait();

         return controller.getProjekt();
      }
      catch (IOException e)
      {
         e.printStackTrace();
      }
      return null;
   }

   /**
    * Get the primary {@link Stage} for this application. Can for example be
    * used to set modal.
    *
    * @return The primary stage of the application.
    */
   public Stage getPrimaryStage()
   {
      return primaryStage;
   }

   /**
    * Getter for the StudentenverwaltungsStage.
    *
    * @return studentenverwaltungsStage
    */
   public Stage getStudentenverwaltungsStage()
   {
      return studentenverwaltungsStage;
   }

   /**
    * Getter for the AnsprechpartnerverwaltungsStage.
    *
    * @return ansprechpartnerverwaltungsStage
    */
   public Stage getAnsprechpartnerverwaltungsStage()
   {
      return ansprechpartnerverwaltungsStage;
   }

   /**
    * Getter for the ProjekthinzufuegenStage.
    *
    * @return projekthinzufuegenStage
    */
   public Stage getProjekthinzufuegenStage()
   {
      return projekthinzufuegenStage;
   }

   /**
    * Getter for property ansprechpartner.
    * 
    * @return Returns the ansprechpartner.
    */
   public ObservableList<Ansprechpartner> getAnsprechpartner()
   {
      return ansprechpartner;
   }

   /**
    * Getter for property organisation.
    * 
    * @return Returns the organisation.
    */
   public ObservableList<Organisation> getOrganisation()
   {
      return organisation;
   }

   /**
    * Getter for property projekte.
    * 
    * @return Returns the projekte.
    */
   public ObservableList<Projekt> getProjekte()
   {
      return projekte;
   }

   /**
    * Getter for property studenten.
    * 
    * @return Returns the studenten.
    */
   public ObservableList<Student> getStudenten()
   {
      return studenten;
   }

   /**
    * Launches the JavaFX application.
    *
    * @param args
    *           Command line arguments.
    */
   public static void main(String[] args)
   {
      launch(args);
   }

   /**
    * Load Data on launch from database.
    *
    */
   private void loadData()
   {
      try
      {
         Class.forName("com.mysql.jdbc.Driver").newInstance();
         dbConnection = DriverManager
               .getConnection("jdbc:mysql://localhost/projekte", "root", "");

         ansprechpartner.clear();
         organisation.clear();

         HashMap<String, Organisation> orgaHash = new HashMap<>();
         HashMap<String, HashMap<String, Ansprechpartner>> anspHash = new HashMap<>();
         HashMap<Integer, Student> studHash = new HashMap<>();

         ResultSet rs = dbConnection.createStatement()
               .executeQuery("SELECT * from organisation");
         while (rs.next())
         {
            Organisation organisation = new Organisation(
                  rs.getString("name"));

            orgaHash.put(organisation.getName(), organisation);
         }

         rs = dbConnection.createStatement()
               .executeQuery("SELECT * from ansprechpartner");
         while (rs.next())
         {
            Organisation organisation = orgaHash
                  .get(rs.getString("organisation"));

            Ansprechpartner ansprechpartner = new Ansprechpartner(
                  organisation, rs.getString("vorname"),
                  rs.getString("nachname"));

            HashMap<String, Ansprechpartner> tmpHashMap = new HashMap<>();

            tmpHashMap.put(ansprechpartner.getNachname(), ansprechpartner);

            tmpHashMap = anspHash.putIfAbsent(ansprechpartner.getVorname(),
                  tmpHashMap);
            if (tmpHashMap != null)
            {
               tmpHashMap.put(ansprechpartner.getNachname(), ansprechpartner);
            }
         }

         rs = dbConnection.createStatement()
               .executeQuery("SELECT * from student");
         while (rs.next())
         {
            Student student = new Student(rs.getInt("matrikelnummer"));

            student.setVorname(rs.getString("vorname"));
            student.setNachname(rs.getString("nachname"));

            studHash.put(student.getMatrikelnummer(), student);
         }

         rs = dbConnection.createStatement()
               .executeQuery("SELECT * from projekt");
         while (rs.next())
         {
            Ansprechpartner ansprechpartner = null;

            if (rs.getString("ansprechpartnerVorname") != null
                  && rs.getString("ansprechpartnerNachname") != null)
            {
               ansprechpartner = anspHash
                     .get(rs.getString("ansprechpartnerVorname"))
                     .get(rs.getString("ansprechpartnerNachname"));
            }

            Student student1 = studHash.get(rs.getInt("student1"));
            Student student2 = studHash.get(rs.getInt("student2"));
            Student student3 = studHash.get(rs.getInt("student3"));

            Projekt projekt = new Projekt(rs.getString("projektthema"),
                  student1, student2, student3, ansprechpartner);

            projekt.setSkizze(rs.getString("skizze"));
            projekt.setProjektbeschreibung(
                  rs.getString("projektbeschreibung"));
            projekt.setProjekteinhalte(rs.getString("projektinhalte"));
            projekt.setStatus(
                  Projekt.StatusTypes.valueOf(rs.getString("status")));
            projekt.setDozentenkommentar(rs.getString("dozentenkommentar"));

            this.projekte.add(projekt);
         }

         organisation.addAll(orgaHash.values());
         for (HashMap<String, Ansprechpartner> hashs : anspHash.values())
         {
            ansprechpartner.addAll(hashs.values());
         }
         studenten.addAll(studHash.values());
      }
      catch (SQLException | InstantiationException | IllegalAccessException |

      ClassNotFoundException e)

      {
         e.printStackTrace();
         Platform.exit();
      }

   }

   /**
    * Registers {@link ChangeListener} on the {@link ObservableList}s and
    * persists the changes to the database.
    *
    */
   private void registerSaveHandler()
   {
      try
      {
         PreparedStatement deleteOrganisation = dbConnection
               .prepareStatement("DELETE FROM organisation WHERE name=?");
         PreparedStatement addOrganisation = dbConnection.prepareStatement(
               "INSERT INTO organisation (name) VALUES (?)");
         PreparedStatement updateOrganisation = dbConnection.prepareStatement(
               "UPDATE organisation SET name=? WHERE name=?");

         organisation.addListener((
               ListChangeListener.Change<? extends Organisation> listener) -> {
            while (listener.next())
            {
               if (listener.wasUpdated())
               {
                  for (int i = listener.getFrom(); i < listener.getTo(); i++)
                  {
                     try
                     {
                        Organisation tmpOrg = listener.getList().get(i);

                        updateOrganisation.setString(1, tmpOrg.getName());
                        updateOrganisation.setString(2, tmpOrg.getOldName());
                        updateOrganisation.execute();
                     }
                     catch (SQLException e)
                     {
                        e.printStackTrace();
                     }
                  }
               }
               else
               {
                  for (Organisation removed : listener.getRemoved())
                  {
                     try
                     {
                        deleteOrganisation.setString(1, removed.getName());
                        deleteOrganisation.execute();
                     }
                     catch (SQLException e)
                     {
                        e.printStackTrace();
                     }
                  }
                  for (Organisation added : listener.getAddedSubList())
                  {
                     try
                     {
                        addOrganisation.setString(1, added.getName());
                        addOrganisation.execute();
                     }
                     catch (SQLException e)
                     {
                        e.printStackTrace();
                     }
                  }
               }
            }
         });

         PreparedStatement deleteAnsprechpartner = dbConnection
               .prepareStatement(
                     "DELETE FROM ansprechpartner WHERE vorname=? and nachname=?");
         PreparedStatement addAnsprechpartner = dbConnection.prepareStatement(
               "INSERT INTO ansprechpartner (vorname, nachname, organisation) VALUES (?, ?, ?)");
         PreparedStatement updateAnsprechpartner = dbConnection
               .prepareStatement(
                     "UPDATE ansprechpartner SET vorname=?,nachname=?,organisation=? WHERE vorname=? and nachname=?");

         ansprechpartner.addListener((
               ListChangeListener.Change<? extends Ansprechpartner> listener) -> {
            while (listener.next())
            {
               if (listener.wasUpdated())
               {
                  for (int i = listener.getFrom(); i < listener.getTo(); i++)
                  {
                     try
                     {
                        Ansprechpartner tmpAnsprechpartner = listener
                              .getList().get(i);

                        updateAnsprechpartner.setString(1,
                              tmpAnsprechpartner.getVorname());
                        updateAnsprechpartner.setString(2,
                              tmpAnsprechpartner.getNachname());

                        if (tmpAnsprechpartner.getOrganisation() != null)
                        {
                           updateAnsprechpartner.setString(3,
                                 tmpAnsprechpartner.getOrganisation()
                                       .getName());
                        }
                        else
                        {
                           updateAnsprechpartner.setString(3, null);
                        }

                        updateAnsprechpartner.setString(4,
                              tmpAnsprechpartner.getOldVorname());
                        updateAnsprechpartner.setString(5,
                              tmpAnsprechpartner.getOldNachname());

                        updateAnsprechpartner.execute();
                     }
                     catch (SQLException e)
                     {
                        e.printStackTrace();
                     }
                  }
               }
               else
               {
                  for (Ansprechpartner removed : listener.getRemoved())
                  {
                     try
                     {
                        deleteAnsprechpartner.setString(1,
                              removed.getVorname());
                        deleteAnsprechpartner.setString(2,
                              removed.getNachname());

                        deleteAnsprechpartner.execute();
                     }
                     catch (SQLException e)
                     {
                        e.printStackTrace();
                     }
                  }
                  for (Ansprechpartner added : listener.getAddedSubList())
                  {
                     try
                     {
                        addAnsprechpartner.setString(1, added.getVorname());
                        addAnsprechpartner.setString(2, added.getNachname());

                        if (added.getOrganisation() != null)
                        {
                           addAnsprechpartner.setString(3,
                                 added.getOrganisation().getName());
                        }
                        else
                        {
                           addAnsprechpartner.setString(3, null);
                        }

                        addAnsprechpartner.execute();
                     }
                     catch (SQLException e)
                     {
                        e.printStackTrace();
                     }
                  }
               }
            }
         });

         PreparedStatement deleteStudent = dbConnection.prepareStatement(
               "DELETE FROM student WHERE matrikelnummer=?");
         PreparedStatement addStudent = dbConnection.prepareStatement(
               "INSERT INTO student (matrikelnummer, vorname, nachname) VALUES (?, ?, ?)");
         PreparedStatement updateStudent = dbConnection.prepareStatement(
               "UPDATE student SET matrikelnummer=?,vorname=?,nachname=? WHERE matrikelnummer=?");

         studenten.addListener(
               (ListChangeListener.Change<? extends Student> listener) -> {
                  while (listener.next())
                  {
                     if (listener.wasUpdated())
                     {
                        for (int i = listener.getFrom(); i < listener
                              .getTo(); i++)
                        {
                           try
                           {
                              Student tmpStudent = listener.getList().get(i);

                              updateStudent.setInt(1,
                                    tmpStudent.getMatrikelnummer());
                              updateStudent.setString(2,
                                    tmpStudent.getVorname());
                              updateStudent.setString(3,
                                    tmpStudent.getNachname());

                              updateStudent.setInt(4,
                                    tmpStudent.getOldMatrikelnummer());

                              updateStudent.execute();
                           }
                           catch (SQLException e)
                           {
                              e.printStackTrace();
                           }
                        }
                     }
                     else
                     {
                        for (Student removed : listener.getRemoved())
                        {
                           try
                           {
                              deleteStudent.setInt(1,
                                    removed.getMatrikelnummer());

                              deleteStudent.execute();
                           }
                           catch (SQLException e)
                           {
                              e.printStackTrace();
                           }
                        }
                        for (Student added : listener.getAddedSubList())
                        {
                           try
                           {
                              addStudent.setInt(1, added.getMatrikelnummer());
                              addStudent.setString(2, added.getVorname());
                              addStudent.setString(3, added.getNachname());

                              addStudent.execute();
                           }
                           catch (SQLException e)
                           {
                              e.printStackTrace();
                           }
                        }
                     }
                  }
               });

         PreparedStatement deleteProjekt = dbConnection
               .prepareStatement("DELETE FROM projekt WHERE projektthema=?");
         PreparedStatement addProjekt = dbConnection.prepareStatement(
               "INSERT INTO projekt (projektthema, skizze, projektbeschreibung, projektinhalte, student1, student2, student3, ansprechpartnerVorname, ansprechpartnerNachname, status, dozentenkommentar) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
         PreparedStatement updateProjekt = dbConnection.prepareStatement(
               "UPDATE projekt SET projektthema=?, skizze=?, projektbeschreibung=?, projektinhalte=?, student1=?, student2=?, student3=?, ansprechpartnerVorname=?, ansprechpartnerNachname=?, status=?, dozentenkommentar=? WHERE projektthema=?");

         projekte.addListener(
               (ListChangeListener.Change<? extends Projekt> listener) -> {
                  while (listener.next())
                  {
                     if (listener.wasUpdated())
                     {
                        for (int i = listener.getFrom(); i < listener
                              .getTo(); i++)
                        {
                           try
                           {
                              Projekt tmpProjekt = listener.getList().get(i);

                              updateProjekt.setString(1,
                                    tmpProjekt.getProjektthema());
                              updateProjekt.setString(2,
                                    tmpProjekt.getSkizze());
                              updateProjekt.setString(3,
                                    tmpProjekt.getProjektbeschreibung());
                              updateProjekt.setString(4,
                                    tmpProjekt.getProjekteinhalte());
                              if (tmpProjekt.getStudent1() != null)
                              {
                                 updateProjekt.setInt(5, tmpProjekt
                                       .getStudent1().getMatrikelnummer());
                              }
                              else
                              {
                                 updateProjekt.setNull(5, Types.INTEGER);
                              }
                              if (tmpProjekt.getStudent2() != null)
                              {
                                 updateProjekt.setInt(6, tmpProjekt
                                       .getStudent2().getMatrikelnummer());
                              }
                              else
                              {
                                 updateProjekt.setNull(6, Types.INTEGER);
                              }
                              if (tmpProjekt.getStudent3() != null)
                              {
                                 updateProjekt.setInt(7, tmpProjekt
                                       .getStudent3().getMatrikelnummer());
                              }
                              else
                              {
                                 updateProjekt.setNull(7, Types.INTEGER);
                              }
                              if (tmpProjekt.getAnsprechpartner() != null)
                              {
                                 updateProjekt.setString(8, tmpProjekt
                                       .getAnsprechpartner().getVorname());
                                 updateProjekt.setString(9, tmpProjekt
                                       .getAnsprechpartner().getNachname());
                              }
                              else
                              {
                                 updateProjekt.setString(8, null);
                                 updateProjekt.setString(9, null);
                              }

                              updateProjekt.setString(10,
                                    tmpProjekt.getStatus().name());
                              updateProjekt.setString(11,
                                    tmpProjekt.getDozentenkommentar());

                              updateProjekt.setString(12,
                                    tmpProjekt.getOldProjektthema());

                              updateProjekt.execute();
                           }
                           catch (SQLException e)
                           {
                              e.printStackTrace();
                           }
                        }
                     }
                     else
                     {
                        for (Projekt removed : listener.getRemoved())
                        {
                           try
                           {
                              deleteProjekt.setString(1,
                                    removed.getProjektthema());

                              deleteProjekt.execute();
                           }
                           catch (SQLException e)
                           {
                              e.printStackTrace();
                           }
                        }
                        for (Projekt added : listener.getAddedSubList())
                        {
                           try
                           {
                              addProjekt.setString(1,
                                    added.getProjektthema());
                              addProjekt.setString(2, added.getSkizze());
                              addProjekt.setString(3,
                                    added.getProjektbeschreibung());
                              addProjekt.setString(4,
                                    added.getProjekteinhalte());
                              if (added.getStudent1() != null)
                              {
                                 addProjekt.setInt(5, added.getStudent1()
                                       .getMatrikelnummer());
                              }
                              else
                              {
                                 addProjekt.setNull(5, Types.INTEGER);
                              }
                              if (added.getStudent2() != null)
                              {
                                 addProjekt.setInt(6, added.getStudent2()
                                       .getMatrikelnummer());
                              }
                              else
                              {
                                 addProjekt.setNull(6, Types.INTEGER);
                              }
                              if (added.getStudent3() != null)
                              {
                                 addProjekt.setInt(7, added.getStudent3()
                                       .getMatrikelnummer());
                              }
                              else
                              {
                                 addProjekt.setNull(7, Types.INTEGER);
                              }
                              if (added.getAnsprechpartner() != null)
                              {
                                 addProjekt.setString(8, added
                                       .getAnsprechpartner().getVorname());
                                 addProjekt.setString(9, added
                                       .getAnsprechpartner().getNachname());
                              }
                              else
                              {
                                 addProjekt.setString(8, null);
                                 addProjekt.setString(9, null);
                              }

                              addProjekt.setString(10,
                                    added.getStatus().name());
                              addProjekt.setString(11,
                                    added.getDozentenkommentar());

                              addProjekt.execute();
                           }
                           catch (SQLException e)
                           {
                              e.printStackTrace();
                           }
                        }
                     }
                  }
               });
      }
      catch (SQLException e)
      {
         e.printStackTrace();
      }
   }
}