package demo.io;

import demo.view.DemoBase;
import y.base.Node;
import y.base.NodeCursor;
import y.layout.BufferedLayouter;
import y.layout.hierarchic.IncrementalHierarchicLayouter;
import y.view.Graph2D;
import y.view.NodeRealizer;

import java.awt.Color;
import java.awt.EventQueue;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;

/**
 * Demonstrates how to query data from a database and create a graph from the queried data.
 * Note, that this demo will not run out of-the-box. Instead you'd need to adopt it to your use case,
 * e.g. by choosing the right database driver, connect to your database and query the data from your tables.
 *
 * It is meant to showcase the general approach on how to gather data and create graph elements from this data.
 */
public class DataBaseQueryDemo extends DemoBase {

  public DataBaseQueryDemo() {

    //check if database driver is available
    checkForDBDriver();

    //get a database connection
    Connection con = getDBConnection();

    //add nodes and edges to the view's graph
    fillGraph(con);

    //run a layout
    new BufferedLayouter(new IncrementalHierarchicLayouter()).doLayout(view.getGraph2D());

    //make sure the view will show the whole graph
    view.fitContent();
    view.updateView();
  }

  /** Checks if the database driver is available in the classpath. */
  private void checkForDBDriver() {
    try {
      //TODO: Select/add your database driver. Depending on your database you can for example use one of these drivers:

      // Let's say we are using CsvJDBC here (http://sourceforge.net/projects/csvjdbc/)
      //csvDir
      Class.forName("org.relique.jdbc.csv.CsvDriver");

//      //Oracle
//      Class.forName("oracle.jdbc.driver.OracleDriver");
//
//      //DB2
//      Class.forName("com.ibm.db2.jdbc.app.DB2Driver");
//
//      //Microsoft SQL
//      Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
//
//      //mysql
//      Class.forName("com.mysql.jdbc.Driver");
//
//      //Derby
//      Class.forName("org.apache.derby.jdbc.ClientDriver");
//
//      //PostgreSQL
//      Class.forName("org.postgresql.Driver");
//
//      //hSQLDb
//      Class.forName("org.hsqldb.jdbcDriver");


    } catch (ClassNotFoundException e) {
      throw new RuntimeException("Database driver not found!");
    }
  }

  /**
   * Connect to the database with username and password
   */
  private Connection getDBConnection() {
    Connection con = null;
    try {
      String csvDir = getClass().getResource("csvDir").getPath();
      con = DriverManager.getConnection("jdbc:relique:csv:" + csvDir);

//      //Oracle
//      con = DriverManager.getConnection("jdbc:oracle:thin:@<serverip>:<port>:<sid>", "<username>", "<password>");
//
//      //DB2
//      con = DriverManager.getConnection("jdbc:db2://<serverip>:<port>/<databaseName>", "<username>", "<password>");
//
//      //Microsoft SQL
//      con = DriverManager.getConnection("jdbc:microsoft:sqlserver://<serverip>:<port>/<databaseName>", "<username>","<password>");
//
//      //mysql
//      con = DriverManager.getConnection("jdbc:mysql://<serverip>:<port>/<databaseName>", "<username>", "<password>");
//
//      //derby
//      con = DriverManager.getConnection("jdbc:derby://<serverip>:<port>/<databaseName>", "<username>", "<password>");
//
//      //PostgreSQL
//      con = DriverManager.getConnection("jdbc:postgreesql://<serverip>:<port>/<databaseName>", "<username>", "<password>");
//
//      //hSQLDb
//      con = DriverManager.getConnection("jdbc:hsqldb:hsql://<serverip>:<port>/<databaseName>", "<username>", "<password>");

      return con;
    }
    catch (SQLException e) {
      throw (new IllegalStateException("Could not create connection to database: " + e.getMessage()));
    }
    finally {
      if (con != null) {
        try {
          con.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
      }
    }
  }

  /**
   * Fills the view's graph with node elements according to the queried data.
   * In this example we have two tables. One is called "department" and will have an id and a name for each department.
   * The other table is called "employee" and will have an id, a name and an department_id per employee. The
   * department_id will map the employee to the according department.
   *
   * Table Department:
   * id,name
   * 1,Research
   * 2,Development
   * 3,Sales
   *
   *
   * Table Employee:
   * id,name,department_id
   * 1,Jacob Miller,1
   * 2,Joseph Lewis,2
   * 3,Susan Reid,2
   * 4,Gregory Arnold,3
   *
   */
  private void fillGraph(Connection con) {
    try {
      Statement stmt = con.createStatement();

      //get graph from view, so we can fill it
      Graph2D graph = view.getGraph2D();

      //create some root node
      Node company = graph.createNode();
      NodeRealizer companyRealizer = graph.getRealizer(company);
      //set the label of this node
      companyRealizer.getLabel().setText("My Company");
      //some style customization
      companyRealizer.setFillColor(new Color(255, 102, 51));

      //query some data and create nodes and edges for the departments
      ResultSet rs = stmt.executeQuery("SELECT department_id, name FROM department");
      HashMap departmentID2Node = new HashMap();
      while (rs.next()) {
        //create a node for each department
        Node department = graph.createNode();
        NodeRealizer realizer = graph.getRealizer(department);
        //set the label of this node
        realizer.getLabel().setText(rs.getString("name"));
        //some style customization
        realizer.setFillColor(new Color(255, 204, 51));

        //create an edge from root to this node
        graph.createEdge(company, department);

        //store the node with its according ID for easy access when creating edges later
        departmentID2Node.put(rs.getString("ID"), department);
      }


      //query some more data and create nodes and edges for employees
      rs = stmt.executeQuery("SELECT name, department_id FROM employee");
      while (rs.next()) {
        //create a node for each employee
        Node employee = graph.createNode();
        NodeRealizer realizer = graph.getRealizer(employee);
        realizer.getLabel().setText(rs.getString("name"));
        realizer.setFillColor(new Color(51, 102, 255));

        //create an edge between the department and the employee
        Node department = (Node) departmentID2Node.get(rs.getString(("department_id")));
        if (department != null) {
          graph.createEdge(department, employee);
        }
      }
      rs.close();
      stmt.close();

      //simply enlarge all nodes, so the labels will fit. Of course there are some more sophisticated methods to do that
      for (NodeCursor nodeCursor = graph.nodes(); nodeCursor.ok(); nodeCursor.next()) {
        Node node = nodeCursor.node();
        graph.getRealizer(node).setSize(100, 30);
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
    finally {
      if (con != null) {
        try {
          con.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
      }
    }
  }

  /** Launches this demo. */
  public static void main(String args[]) {
    initLnF();
    EventQueue.invokeLater(new Runnable() {
      public void run() {
        initLnF();
        (new DataBaseQueryDemo()).start("Database Query Demo");
      }
    });
  }
}