package com.sqldbsamples;

import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.DriverManager;
import java.util.Scanner;

public class App {

 public static void main(String[] args) {

     // Connect to database
        String hostName = "vo6901-sql-server.database.windows.net";
        String dbName = "cs-dsa-4513-sql-db";
        String user = "vo6901";
        String password = "";
        String url = String.format("jdbc:sqlserver://%s:1433;database=%s;user=%s;password=%s;encrypt=true;hostNameInCertificate=*.database.windows.net;loginTimeout=30;", hostName, dbName, user, password);
        Connection connection = null;

        try {
                connection = DriverManager.getConnection(url);
                String schema = connection.getSchema();
                System.out.println("Successful connection - Schema: " + schema);

                System.out.println("Query data example:");
                System.out.println("=========================================");
                Scanner scan = new Scanner(System.in);
                Scanner scanString = new Scanner(System.in);
                String userInput;

                int pidInsert;
                String pnameInsert;
                int aidInsert;
                int maxScoreInsert;

                // Create and execute a SELECT SQL statement.
                String selectSql = "SELECT * FROM Problem";
                String authorSql = "SELECT * FROM Author";

                String pidSql = "SELECT pid FROM Problem";
                String pnameSql = "SELECT pname FROM Problem";
                String aidSql = "SELECT aid FROM Author";

                String insertSql;

                        System.out.println("OPTIONS:");
                        System.out.println("(insert) Insert the pid, pname, Max-score, and aid into the table Problem (aid would be the existing ids from Author table). Assume that the value of Max_score is not known and will be estimated using the information present in the tableas follows. The Max_score should be set to the rounded average of maximumof Max_score + 10% ofproblems by that author. If there are no problems by that author yet in that table, then Max_score should be set to the rounded average of all the problems present in the table.");
                        System.out.println("(aid) The compensation of authors should be increased as follows. Taking aid as input from user, if that author has the highest number of problems, he should get 20% raise in his compensation; If it is the 2nd highest, he should  get  15%  raise in  his  compensation; if it  is  the 3rd highest, he should get 10% raisein his compensation; otherwise, the default 5% raise should be applied.");
                        System.out.println("(display) Display the complete information of the Problem table and author table.");
                        System.out.println("(quit) Quit");

                        while(true)
                        {
                          System.out.println("Please select one of the four options by typing in their keyword:");
                          userInput = scanString.nextLine();

                          /*INSERT*****************************************************************/

                          if (userInput.equals("insert"))
                          {
                            System.out.println("Currently used pids:");
                            try (Statement statement = connection.createStatement();
                                ResultSet resultSet = statement.executeQuery(pidSql))
                                {
                                  while (resultSet.next())
                                  {
                                      System.out.println(resultSet.getString(1));
                                  }
                                }

                            System.out.println("Enter pid to insert: ");
                            pidInsert = scan.nextInt();

                            System.out.println("Currently used pnames:");
                            try (Statement statement = connection.createStatement();
                                ResultSet resultSet = statement.executeQuery(pnameSql))
                                {
                                  while (resultSet.next())
                                  {
                                      System.out.println(resultSet.getString(1));
                                  }
                                }

                            System.out.println("Enter pname to insert: ");
                            pnameInsert = scanString.nextLine();

                            System.out.println("Currently used aids:");
                            try (Statement statement = connection.createStatement();
                                ResultSet resultSet = statement.executeQuery(aidSql))
                                {
                                  while (resultSet.next())
                                  {
                                      System.out.println(resultSet.getString(1));
                                  }
                                }
                            System.out.println("Enter aid to insert: ");
                            aidInsert = scan.nextInt();

                            System.out.println(pidInsert + " " + pnameInsert + " " + aidInsert);

                            maxScoreInsert = 100;

                            //insertSql = "GO CREATE PROCEDURE sp_insert @pid INT @pname VARCHAR(64) @MAX_SCORE INT @aid INT AS BEGIN set nocount on INSERT INTO Problem (pid, pname, max_score, aid) VALUES (" + pidInsert + ", " + pnameInsert + ", " + maxScoreInsert + ", " + aidInsert + "); set nocount off END GO EXEC sp_insert @pid = " + pidInsert + ", @pname = " + pnameInsert + ", @MAX_SCORE = " + maxScoreInsert + ", @aid = " + aidInsert + ";";
                            insertSql = "INSERT INTO Problem (pid, pname, max_score, aid) VALUES (" + pidInsert + ", '" + pnameInsert + "', " + maxScoreInsert + ", " + aidInsert + ");";
                            try (Statement statement = connection.createStatement();
                                ResultSet resultSet = statement.executeQuery(insertSql))
                                {
                                  System.out.println("Successful insertion.");
                                }

                          }
                          /*AID*****************************************************************/
                          else if (userInput.equals("aid"))
                          {
                            System.out.println("BOOP");
                          }
                          /*DISPLAY*****************************************************************/
                          else if (userInput.equals("display"))
                          {
                            try (Statement statement = connection.createStatement();
                                ResultSet resultSet = statement.executeQuery(selectSql))
                                {
                            System.out.println("PID  PNAME  MAX_SCORE  AID");
                            while (resultSet.next())
                            {
                                System.out.println(resultSet.getString(1) + " "
                                    + resultSet.getString(2) + "  "
                                        + resultSet.getString(3)+ " "
                                            + resultSet.getString(4));
                            }
                           }

                           try (Statement statement = connection.createStatement();
                           ResultSet authorSet = statement.executeQuery(authorSql))
                           {
                             System.out.println("AID  ANAME  COMPENSATION");

                             while (authorSet.next())
                             {
                               System.out.println(authorSet.getString(1) + " "
                                   + authorSet.getString(2) + "  "
                                       + authorSet.getString(3));
                             }
                           }
                          }
                          /*QUIT*****************************************************************/
                          else if (userInput.equals("quit"))
                          {
                            connection.close();
                            break;
                          }
                          else
                          {
                            System.out.println("Invalid input.");
                          }
                        }
        }
        catch (Exception e) {
                e.printStackTrace();
        }
    }
}