/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package poetassignment;

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.util.Scanner;
import java.sql.*;

/**
 *
 * @author samuel
 */
public class MethodClass {

    public void CreateAllTables(String host, String user, String pass) throws SQLException {

        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            System.out.println("Could not load the driver");
        }

        Connection conn = DriverManager.getConnection("jdbc:mysql://" + host + ":3306/poetdb", user, pass);
        Statement s = conn.createStatement();
        conn.setAutoCommit(false);

        //CREATE POET TABLE
        s.executeUpdate("create table IF NOT EXISTS POET( PoetName varchar(20) PRIMARY KEY NOT NULL,  DateOfBirth date NOT NULL, PlaceOfBirth varchar(20) NOT NULL, Nationality varchar(20) NOT NULL, Language varchar(20) NOT NULL, NumRecordings int NOT NULL)");
        System.out.println("Created table POET");

        //CREATE TRANSLATOR TABLE
        s.executeUpdate("create table IF NOT EXISTS TRANSLATOR( TranslatorName varchar(20) PRIMARY KEY NOT NULL,  DateOfBirth date NOT NULL, PlaceOfBirth varchar(20) NOT NULL, Nationality varchar(20) NOT NULL, Language varchar(20) NOT NULL)");
        System.out.println("Created table TRANSLATOR");

        //CREATE POEM TABLE
        s.executeUpdate("create table IF NOT EXISTS POEM( PoemTitle varchar(20) PRIMARY KEY NOT NULL,  DatePublished date NOT NULL, Language varchar(20) NOT NULL,  NumWords int NOT NULL, PoetName varchar(20) NOT NULL, FOREIGN KEY (PoetName) REFERENCES POET (PoetName))");
        System.out.println("Created table POEM");

        //CREATE TRANSLATION TABLE
        s.executeUpdate("create table IF NOT EXISTS TRANSLATION( TranslationID int PRIMARY KEY NOT NULL,  TranslatorName varchar(20) NOT NULL, PoemTitle varchar(20) NOT NULL, TranslationDate date NOT NULL, FOREIGN KEY (TranslatorName) REFERENCES TRANSLATOR (TranslatorName), FOREIGN KEY (PoemTitle) REFERENCES POEM (PoemTitle))");
        System.out.println("Created table TRANSLATION");

        //CREATE RECORDING TABLE
        s.executeUpdate("create table IF NOT EXISTS RECORDING( RecordingID int PRIMARY KEY NOT NULL,  RecordingDate date NOT NULL, Place varchar(20) NOT NULL, Duration time NOT NULL, Format varchar(10) NOT NULL, PoemTitle varchar(20) NOT NULL, PoetName varchar(20) NOT NULL, FOREIGN KEY (PoemTitle) REFERENCES POEM (PoemTitle), FOREIGN KEY (PoetName) REFERENCES POET(PoetName))");
        System.out.println("Created table RECORDING");
        conn.commit();
    }

    public void PopulateAllTables(String host, String user, String pass) throws SQLException {

        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            System.out.println("Could not load the driver");
        }

        Connection conn = DriverManager.getConnection("jdbc:mysql://" + host + ":3306/poetdb", user, pass);
        Statement s = conn.createStatement();
        conn.setAutoCommit(false);

        //POPULATE POET TABLE
        s.executeUpdate("load data local infile 'C://files/poets.txt' into table poet  LINES TERMINATED BY '\\r\\n'");

        //POPULATE TRANSLATOR TABLE
        s.executeUpdate("load data local infile 'C://files/translators.txt' into table translator  LINES TERMINATED BY '\\r\\n'");

        //POPULATE POEM TABLE
        s.executeUpdate("load data local infile 'C://files/poems.txt' into table poem  LINES TERMINATED BY '\\r\\n'");

        //POPULATE TRANSLATION TABLE
        s.executeUpdate("load data local infile 'C://files/translations.txt' into table translation  LINES TERMINATED BY '\\r\\n'");

        //POPULATE RECORDING TABLE
        s.executeUpdate("load data local infile 'C://files/recordings.txt' into table recording  LINES TERMINATED BY '\\r\\n'");
        conn.commit();
    }

    public void CreateProcedures(String host, String user, String pass) throws SQLException {

        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            System.out.println("Could not load the driver");
        }

        Connection conn = DriverManager.getConnection("jdbc:mysql://" + host + ":3306/poetdb", user, pass);
        Statement s = conn.createStatement();
        conn.setAutoCommit(false);

        //CREATING SIMPLE SELECT STORED PROCEDURE (PROC1)
        s.executeUpdate("CREATE PROCEDURE GetAllPoets() BEGIN SELECT * FROM POET; END");
        //CREATING SELECT WITH JOIN STORED PROCEDURE (PROC2)
        s.executeUpdate("CREATE PROCEDURE GetLanguage() BEGIN SELECT TRANSLATION.TranslationID, TRANSLATION.TranslatorName, TRANSLATION.PoemTitle, TRANSLATION.TranslationDate, TRANSLATOR.Language FROM TRANSLATION INNER JOIN TRANSLATOR ON TRANSLATION.TranslatorName=TRANSLATOR.TranslatorName; END");
        //CREATING SELECT WITH AN AGGREGATE FUNCTION (PROC3)
        s.executeUpdate("CREATE PROCEDURE MaxWords() BEGIN SELECT MAX(Poem.NumWords) FROM Poem; END");
        //CREATING SELECT WITH AGGREGATE FUNCTION AND GROUP BY (PROC4)
        s.executeUpdate("CREATE PROCEDURE NumOfTranslationsLang() BEGIN SELECT Translator.Language, COUNT(Translation.TranslatorName) AS NumberOfTranslations FROM Translation INNER JOIN TRANSLATOR ON TRANSLATION.TranslatorName=TRANSLATOR.TranslatorName GROUP BY Language; END");
        //CREATING SELECT WITH AGGREGATE FUNCTION, GROUP BY AND HAVING CLAUSE (PROC5)
        s.executeUpdate("CREATE PROCEDURE NumOfTranslationsLangOver2() BEGIN SELECT Translator.Language, COUNT(Translation.TranslatorName) AS NumberOfTranslations FROM Translation INNER JOIN TRANSLATOR ON TRANSLATION.TranslatorName=TRANSLATOR.TranslatorName GROUP BY Language HAVING NumberOfTranslations>2; END");
        //CREATING UNCORRELATED NESTED SELECT (PROC6)
        s.executeUpdate("CREATE PROCEDURE UncorrelatedSelect() BEGIN SELECT * FROM Translation WHERE TranslatorName in(SELECT TranslatorName FROM Translator WHERE year(DateOfBirth)='1989'); END");
        //CREATING CORRELATED SUBQUERY (PRO7)
        s.executeUpdate("CREATE PROCEDURE CorrelatedSubquery() BEGIN SELECT t.TranslatorName, tt.TranslationDate FROM Translator t JOIN Translation tt ON t.TranslatorName = tt.TranslatorName WHERE TranslationDate = (SELECT MAX(TranslationDate) FROM Translation ttt WHERE ttt.TranslatorName = t.TranslatorName); END");

        conn.commit();
    }

    public void CallProcedures(String host, String user, String pass) throws SQLException {

        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            System.out.println("Could not load the driver");
        }

        Connection conn = DriverManager.getConnection("jdbc:mysql://" + host + ":3306/poetdb", user, pass);
        Statement s = conn.createStatement();
        conn.setAutoCommit(false);

        ResultSet result;
        CallableStatement stmt;
        String proc1, proc2, proc3, proc4, proc5, proc6, proc7;

//PRINTING RESULTSET OF PROCEDURES
        proc1 = "{CALL poetdb.GetAllPoets()}";
        proc2 = "{CALL poetdb.GetLanguage()}";
        proc3 = "{CALL poetdb.MaxWords()}";
        proc4 = "{CALL poetdb.NumOfTranslationsLang()}";
        proc5 = "{CALL poetdb.NumOfTranslationsLangOver2()}";
        proc6 = "{CALL poetdb.UncorrelatedSelect()}";
        proc7 = "{CALL poetdb.CorrelatedSubquery()}";

        stmt = conn.prepareCall(proc1);
        result = stmt.executeQuery();

        System.out.println("Results: ");
        while (result.next()) {
            System.out.println(result.getString(1) + "  " + result.getString(2) + "  " + result.getString(3) + " " + result.getString(4) + " " + result.getString(5));
        }

        stmt = conn.prepareCall(proc2);
        result = stmt.executeQuery();
        System.out.println("Results: ");
        while (result.next()) {
            System.out.println(result.getString(1) + "  " + result.getString(2) + "  " + result.getString(3) + " " + result.getString(4) + " " + result.getString(5));
        }

        stmt = conn.prepareCall(proc3);
        result = stmt.executeQuery();
        System.out.println("Results: ");
        while (result.next()) {
            System.out.println(result.getString(1) + " words");
        }

        stmt = conn.prepareCall(proc4);
        result = stmt.executeQuery();
        System.out.println("Results: ");
        while (result.next()) {
            System.out.println(result.getString(1) + "  " + result.getString(2));
        }

        stmt = conn.prepareCall(proc5);
        result = stmt.executeQuery();
        System.out.println("Results: ");
        while (result.next()) {
            System.out.println(result.getString(1) + "  " + result.getString(2));
        }

        stmt = conn.prepareCall(proc6);
        result = stmt.executeQuery();
        System.out.println("Results: ");
        while (result.next()) {
            System.out.println(result.getString(1) + "  " + result.getString(2) + "  " + result.getString(3) + " " + result.getString(4));
        }

        stmt = conn.prepareCall(proc7);
        result = stmt.executeQuery();
        System.out.println("Results: ");
        while (result.next()) {
            System.out.println(result.getString(1) + "  " + result.getString(2));
        }

        conn.commit();
    }
}