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

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
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.view.AnsprechpartnerListeController;
import javafx.application.Application;
import javafx.application.Platform;
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.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 Stage primaryStage;

   private Connection dbConnection;

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

      connectToDatabase();
      loadData();

      ansprechpartner.addListener((
            ListChangeListener.Change<? extends Ansprechpartner> listener) -> {
         saveData();
      });

      organisation.addListener(
            (ListChangeListener.Change<? extends Organisation> listener) -> {
               saveData();
            });

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

         Scene scene = new Scene(root);
         primaryStage.setScene(scene);

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

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

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

   /**
    * 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 property organisation.
    * 
    * @return Returns the organisation.
    */
   public ObservableList<Organisation> getOrganisation()
   {
      return organisation;
   }

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

   /**
    * TODO Add method comment here
    *
    */
   private void connectToDatabase()
   {
      try
      {
         Class.forName("com.mysql.jdbc.Driver").newInstance();
         dbConnection = DriverManager
               .getConnection("jdbc:mysql://localhost/projekte", "root", "");
      }
      catch (ClassNotFoundException | InstantiationException
            | IllegalAccessException | SQLException e)
      {
         e.printStackTrace();
         Platform.exit();
      }
   }

   /**
    * TODO Add method comment here
    *
    */
   private void loadData()
   {
      try
      {
         ansprechpartner.clear();
         organisation.clear();

         HashMap<String, Organisation> orga = new HashMap<>();

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

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

         rs = dbConnection.createStatement()
               .executeQuery("SELECT * from ansprechpartner");
         while (rs.next())
         {
            Organisation organisation = orga
                  .get(rs.getString("organisation"));
            if (organisation != null)
            {
               Ansprechpartner ansprechpartner = new Ansprechpartner(
                     organisation);
               ansprechpartner.setVorname(rs.getString("vorname"));
               ansprechpartner.setNachname(rs.getString("nachname"));

               this.ansprechpartner.add(ansprechpartner);
            }
         }

         organisation.addAll(orga.values());
      }
      catch (SQLException e)
      {
         e.printStackTrace();
         Platform.exit();
      }
   }

   private void saveData()
   {
      try
      {
         dbConnection.createStatement()
               .execute("SET FOREIGN_KEY_CHECKS = 0;");
         dbConnection.createStatement()
               .execute("TRUNCATE TABLE ansprechpartner");
         dbConnection.createStatement()
               .execute("TRUNCATE TABLE organisation");
         dbConnection.createStatement()
               .execute("SET FOREIGN_KEY_CHECKS = 1;");

         PreparedStatement ps = dbConnection.prepareStatement(
               "INSERT into organisation (name) VALUES (?)");
         for (Organisation org : organisation)
         {
            ps.setString(1, org.getName());

            ps.execute();
         }

         ps = dbConnection.prepareStatement(
               "INSERT into ansprechpartner (vorname, nachname, organisation) VALUES (?, ?, ?)");
         for (Ansprechpartner ans : ansprechpartner)
         {
            ps.setString(1, ans.getVorname());
            ps.setString(2, ans.getNachname());
            ps.setString(3, ans.getOrganisation().getName());

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