package de.hdm_stuttgart.mi.sda1.exam.jdbcexport;

import javax.xml.transform.Result;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


/**
 * Exporting company data. Execution requires importing corresponding
 * schema and optionally sample data from Sql/sample.sql
 */
public class EmployeeInfo {

   /**
    * @param args Unused
    * @throws SQLException Diagnosing database problems.
    */
   public static void main( String[] args ) throws SQLException {

      try (final Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/sda", "root", "123")) {

         final Statement stmt = conn.createStatement();
         final ResultSet employees = stmt.executeQuery("SELECT * FROM Employee");

         while (employees.next()) {
            final Statement stmt2 = conn.createStatement();
            ResultSet departments = stmt2.executeQuery(
                    "SELECT * FROM Department WHERE shortName = '" + employees.getString("department") + "'"
            );
            departments.next();
            String departmentName = departments.getString("name");
            String parent = departments.getString("parent");

            boolean test = false;
            while (parent != null) {
               departments = stmt2.executeQuery(
                       "SELECT * FROM Department WHERE shortName = '" + parent + "'"
               );
               if (!departments.isBeforeFirst()) break;
               departments.next();
               parent = departments.getString("parent");
               test = true;
            }


            String topLevel = null;
            if (test) topLevel = departments.getString("name");


            System.out.println(
                    employees.getString("commonName")
                      + ", department " + departmentName
                      + (topLevel != null ? ", Top level department " + topLevel : "")
            );
         }
      }
   }
}