/**
 * Created by Bailey Sostek on 9/27/18.
 */

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.*;

public class p3 implements Runnable {

    private static String USERID = "";
    private static String PASSWORD = "";

    private static Thread thisThread;

    private static String[] launchArgs;

    private static boolean running = false;

    @Override
    public void run() {
        //If this should keep running
        this.running = true;

        //Derrive input username and password
        if (launchArgs.length >= 2) {
            USERID   = launchArgs[0];
            PASSWORD = launchArgs[1];
        }

        //Input Stream

        //Run loop
        loop:while(running) {
            //Not enough input args
            if (launchArgs.length == 0) {
                System.out.println("Please execute this command with your username and password passed in as additional arguments.");
                System.out.println("java -jar p3 <username> <password>");
                stop();
                break;
            }
            //No additional input args
            if (launchArgs.length == 2) {
                System.out.println("1 – Report Wine Information");
                System.out.println("2 – Report Company Rep Information");
                System.out.println("3 – Report Wine Label Form Information");
                System.out.println("4 – Update Phone Number");
                System.out.println("5 – Exit Program");
                stop();
                break;
            }
            //No additional input args
            if (launchArgs.length == 3) {
                int option = 0;
                try{
                    option = Integer.parseInt(launchArgs[2]);
                }catch(Exception e){
                    System.out.println("The argument input was invalid.");
                    stop();
                    break;
                }

                Connection connection = logIn();
                if(connection == null){
                    System.out.println("No connection could be estableshed with the provided login information.");
                }

                String input = "";
                switch (option){
                    case 1:
                        // enter "Report Wine Information" Mode
                        // print out "Enter Wine ID:"
                        System.out.println("Enter Wine ID:");
                        //await user input
                        input = getInput();
                        // upon user input, query the wines table and print out info (in specific format)
                        query1(connection, input);
                        // then terminate
                        stop();
                        break loop;
                    case 2:
                        // enter "Report Company Rep Information" Mode
                        // print out "Enter Company Rep Login Name:"
                        System.out.println("Enter Company Rep Login Name:");
                        //await user input
                        input = getInput();

                        query2(connection, input);
                        // then terminate
                        stop();
                        break loop;
                    case 3:
                        // enter "Report Wine Label Form Information" Mode
                        System.out.println("Enter Wine Label Form ID:");
                        //await user input
                        input = getInput();
                        //Execute Query
                        query3(connection, input);
                        // then terminate
                        stop();
                        break loop;
                    case 4:
                        // enter "Update Phone Number" Mode
                        System.out.println("Enter Company Rep login Name:");
                        //await user input
                        input = getInput();
                        System.out.println("Enter the Updated Phone Number:");
                        //await users phone number input
                        String phoneNumber = getInput();
                        query4(connection, input, phoneNumber);
                        // then terminate
                        stop();
                        break loop;
                    case 5:
                        // exit program
                        stop();
                        break loop;
                    default:
                        // output "The argument inputted was invalid"
                        System.out.println("The argument input was invalid");
                        // then terminate
                        stop();
                        break loop;
                }
            }
        }
    }

    public String getInput(){
        //Wait for command
        try {
            BufferedReader reader = new BufferedReader(new InputStreamReader(System.in));
            return reader.readLine();
        } catch (IOException e) {
            e.printStackTrace();
            return "";
        }
    }

    public static void stop(){
        running = false;
    }

    public Connection logIn(){
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");

        } catch (ClassNotFoundException e){
            System.out.println("Where is your Oracle JDBC Driver?");
            e.printStackTrace();
            return null;
        }

        Connection connection = null;

        try {
            connection = DriverManager.getConnection(
                    "jdbc:oracle:thin:@oracle.wpi.edu:1521:orcl", USERID, PASSWORD);
        } catch (SQLException e) {
            System.out.println("Connection Failed! Check output console");
            e.printStackTrace();
            return null;
        }

        return connection;
    }

    public void query1(Connection connection, String input){
        // Performing the query
        try {
            Statement stmt = connection.createStatement();
            String str = "SELECT * FROM WINES WHERE WINEID = "+input;
            ResultSet rset = stmt.executeQuery(str);

            int wineID = 0;
            String brandName = "";
            String classType = "";
            float percent = 0.0f;
            String appellation = "";
            float netContent = 0.0f;
            String bottler = "";

            boolean shouldContinue = rset.next();
            if(!shouldContinue){
                System.out.println("No Wine Records exist with the WineID:"+input);
            }

            // Process the results
            while (shouldContinue) {
                wineID = rset.getInt("wineID");
                brandName = rset.getString("brand");
                classType = rset.getString("classType");
                percent = rset.getFloat("alcohol");
                appellation = rset.getString("appellation");
                netContent = rset.getFloat("netContent");
                bottler = rset.getString("bottlerName");
                System.out.println("Wines Information");
                System.out.println("Wine ID: " + wineID + "\nBrand Name: " + brandName + "\nClass/Type: " + classType + "\nAlchohol: " + percent + "%\nAppellation: " + appellation + "\nNet Content: " + netContent + "\nBottler: " + bottler);
                shouldContinue = rset.next();
            } // end while

            rset.close();
            stmt.close();
            connection.close();
        } catch (SQLException e) {
            System.out.println("Get Data Failed! Check output console");
            e.printStackTrace();
        }
    }

    public void query2(Connection connection, String input){
        // Performing the query
        try {
            Statement stmt = connection.createStatement();
            String str = "SELECT * FROM ACCOUNTS JOIN REPS ON reps.loginname = accounts.loginname WHERE reps.loginname = '"+input+"'";
            ResultSet rset = stmt.executeQuery(str);

            String logInName = "";
            int repID = 0;
            String name = "";
            String phone = "";
            String email = "";
            String companyName = "";

            boolean shouldContinue = rset.next();
            if(!shouldContinue){
                System.out.println("No Representative with the username:"+input+" exists.");
            }

            // Process the results
            while (shouldContinue) {
                logInName = rset.getString("logInName");
                repID = rset.getInt("repID");
                name = rset.getString("name");
                phone = rset.getString("phone");
                email = rset.getString("email");
                companyName = rset.getString("companyName");
                System.out.println("Company Representative Information");
                System.out.println(
                        "Login Name: " + logInName + "\n" +
                        "RepID: " + repID + "\n" +
                        "Full Name: " + name + "\n" +
                        "Phone: " + phone + "\n" +
                        "Email Address: " + email + "\n" +
                        "Company Name: " + companyName);
                shouldContinue = rset.next();
            } // end while

            rset.close();
            stmt.close();
            connection.close();
        } catch (SQLException e) {
            System.out.println("Get Data Failed! Check output console");
            e.printStackTrace();
        }
    }

    public void query3(Connection connection, String input){
        // Performing the query
        try {
            Statement stmt = connection.createStatement();
            String str = "SELECT FORMID, STATUS, BRAND, VINTAGE, repName, agentName\n" +
                    "FROM (SELECT FORMID, STATUS, BRAND, VINTAGE, REPID, CURRENTREVIEWERID FROM FORMS JOIN WINES ON wines.wineid = forms.wineid)winestuff\n" +
                    "JOIN (SELECT repID, NAME AS repName FROM REPS JOIN ACCOUNTS ON REPS.loginName = ACCOUNTS.loginName)repstuff\n" +
                    "ON winestuff.repid = repstuff.repid\n" +
                    "JOIN (SELECT ttbID, NAME  AS agentName FROM AGENTS JOIN ACCOUNTS ON AGENTS.loginName = ACCOUNTS.loginName)agentstuff\n" +
                    "ON winestuff.currentreviewerID = agentstuff.ttbID WHERE FORMID = "+input+"";
            ResultSet rset = stmt.executeQuery(str);

            int formID = 0;
            String status = "";
            String brand = "";
            int vintage = 0;
            String repName = "";
            String agentName = "";

            boolean shouldContinue = rset.next();
            if(!shouldContinue){
                System.out.println("No Forms with the FormID:"+input+" exists.");
            }

            // Process the results
            while (shouldContinue) {
                formID = rset.getInt("formID");
                status = rset.getString("status");
                brand = rset.getString("brand");
                vintage = rset.getInt("vintage");
                repName = rset.getString("repName");
                agentName = rset.getString("agentName");

                System.out.println("Company Representative Information");
                System.out.println(
                        "Form ID: " + formID + "\n" +
                        "Status: " + status + "\n" +
                        "Wine Brand: " + brand + "\n" +
                        "Vintage: " + vintage + "\n" +
                        "Company Rep Full Name: " + repName + "\n" +
                        "Agent Full Names: " + agentName);
                shouldContinue = rset.next();
            } // end while

            rset.close();
            stmt.close();
            connection.close();
        } catch (SQLException e) {
            System.out.println("Get Data Failed! Check output console");
            e.printStackTrace();
        }
    }

    public void query4(Connection connection, String input, String phone){
        // Performing the query
        try {
            Statement stmt = connection.createStatement();
            String str = "UPDATE ACCOUNTS SET PHONE = '"+phone+"' WHERE LOGINNAME = '"+input+"'";
            stmt.executeUpdate(str);
            stmt.close();
            connection.close();
        } catch (SQLException e) {
            System.out.println("Get Data Failed! Check output console");
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        thisThread = new Thread(new p3());
        launchArgs = args;
        thisThread.start();
    }
}