import java.nio.DoubleBuffer;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.util.Scanner;
import javax.management.Descriptor;

public class TableReader {
    static Connection getConnection() {
        Connection connection = null;
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            connection = DriverManager.getConnection(
                    "jdbc:mysql://184.154.73.113/davidlno_StockHistory?" + "user=davidlno_student&password=ez24get!");
        } catch (InstantiationException | IllegalAccessException | ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        }
        return connection;
    }

    public static void reportMenu() {
        System.out.println("\n----------Stock Reporting Menu----------");
        System.out.println("MENU OPTIONS\n");
        System.out.println("   1. List all tickers");
        System.out.println("   2. Display most recent data for ticker");
        System.out.println("   3. Display high and current close price for ticker");
        System.out.println("   4. Display top five active tickers for date\n");
        System.out.println("select number or type quit: ");
    }

    static void getAllTickers() {
        String query = "select Ticker from TickerHistory group by Ticker order by Ticker";

        try {
            Connection connection = getConnection();
            Statement statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(query);
            rs = statement.executeQuery(query);
            while (rs.next()) {
                String ticker = rs.getString("Ticker");
                System.out.print(ticker + ", ");
            }
            rs.close();
            statement.close();
            connection.close();
            rs = null;
            statement = null;
            connection = null;
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } catch (NumberFormatException e) {
        }
    }

    static void getOption2(Scanner keyboard) {
        System.out.println("Please enter a valid Ticker");
        String tickerInput = keyboard.nextLine();
        String query = "select * from TickerHistory where HistoryDate = '2017-03-10' and Ticker = '" + tickerInput
                + "'";

        try {
            Connection connection = getConnection();
            Statement statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(query);
            rs = statement.executeQuery(query);
            while (rs.next()) {
                String ticker = rs.getString("Ticker");
                String companyName = rs.getString("companyName");
                int volum = rs.getInt("Volume");
                String historyDate = rs.getString("HistoryDate");
                double openPrice = rs.getDouble("OpenPrice");
                double highPrice = rs.getDouble("HighPrice");
                double lowPrice = rs.getDouble("LowPrice");
                double closePrice = rs.getDouble("ClosePrice");
                DecimalFormat dff = new DecimalFormat("#,###");
                System.out.println(ticker + "\t" + companyName + "\t" + "Volume: " + dff.format(volum));
                System.out.println("\tHistory Date: " + historyDate);
                System.out.println("\tOpen:   " + openPrice);
                System.out.println("\tHigh:   " + highPrice);
                System.out.println("\tLow:    " + lowPrice);
                System.out.println("\tClose:  " + closePrice);
            }
            rs.close();
            statement.close();
            connection.close();
            rs = null;
            statement = null;
            connection = null;
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } catch (NumberFormatException e) {
        }
    }

    static void getOption3(Scanner keyboard) {
        System.out.println("Please enter a valid Ticker");
        String tickerInput = keyboard.nextLine();

        try {
            Connection connection = getConnection();
            Statement statement = connection.createStatement();
            String query = "select * from TickerHistory where Ticker = '" + tickerInput + "'"
                    + " order by ClosePrice desc limit 1";
            ResultSet rs = statement.executeQuery(query);
            double result1 = 0, result2 = 0;
            while (rs.next()) {
                Double closePrice = rs.getDouble("ClosePrice");
                String historyDate = rs.getString("HistoryDate");
                result1 = closePrice;
                System.out.println(historyDate + "\t" + closePrice);
            }
            query = "select * from TickerHistory where Ticker = '" + tickerInput + "'"
                    + " order by HistoryDate desc limit 1 ";
            rs = statement.executeQuery(query);

            while (rs.next()) {
                Double closePrice = rs.getDouble("ClosePrice");
                result2 = closePrice;
                Double differenceA = ((result2 - result1) / result1) * 100;
                String historyDate = rs.getString("HistoryDate");
                System.out.println(historyDate + "\t" + closePrice);
                System.out.printf("Perfect Difference is %.2f ", differenceA);
            }

            rs.close();
            statement.close();
            connection.close();
            rs = null;
            statement = null;
            connection = null;
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } catch (NumberFormatException e) {
        }
    }

    static void getOption4(Scanner keyboard) {
        System.out.print("Please enter a valid date (yyyy-mm-dd): ");
        String tickerInput = keyboard.nextLine();
        try {
            Connection connection = getConnection();
            Statement statement = connection.createStatement();
            String query = "select * from TickerHistory where HistoryDate = '" + tickerInput + "'"
                    + " order by Volume desc limit 5";
            ResultSet rSet = statement.executeQuery(query);
            while (rSet.next()) {
                Double volume = rSet.getDouble("Volume");
                String ticker = rSet.getString("Ticker");
                DecimalFormat dff = new DecimalFormat("#,###");

                System.out.println("Ticker: " + ticker + "\t" + dff.format(volume));
            }
            rSet.close();
            statement.close();
            connection.close();
            rSet = null;
            statement = null;
            connection = null;

        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } catch (NumberFormatException e) {
        }
    }

    public static void main(String[] args) {
        boolean isFound = true;

        while (isFound) {
            reportMenu();
            Scanner keyboard = new Scanner(System.in);
            String input = keyboard.nextLine();
            switch (input) {
            case "1":
                getAllTickers();
                break;
            case "2":
                getOption2(keyboard);
                break;
            case "3":
                getOption3(keyboard);
                break;
            case "4":
                getOption4(keyboard);
                break;

            case "quit":
                System.out.println("Application is quiting");
                isFound = false;
                break;
            default:
                System.out.println("\nYou entered an invalid option\n");
                break;
            }


        }

    }
}