import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.NumberFormat;
import java.util.Scanner;

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) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getMessage());
            System.out.println("VendorError: " + e.getErrorCode());
        }

        return connection;
    }

    static String appMenu() {
        String userSelection = "0";

            System.out.print("\n----------Stock Reporting Menu----------\n"
                    + "\nMENU OPTIONS:\n"
                    + "\n1. List all tickers\n"
                    + "2. Display most recent data for ticker\n"
                    + "3. Display high and current close price for ticker\n"
                    + "4. Display top five active tickers for date\n"
                    + "\nSelect number or type quit: ");

            Scanner input = new Scanner(System.in);
            userSelection = input.nextLine();

            return userSelection;
    }

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

        try {
            Statement statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(query);

            System.out.print("\n");

            while(rs.next()) {
                String ticker = rs.getString("Ticker");
                System.out.print(ticker);

                if(!rs.isLast()) {
                    System.out.print(", ");
                }
                else {
                    System.out.print("\n");
                }
            }

            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());
        }
    }

    static void getRecentData() {
        System.out.print("\nPlease enter valid ticker: ");
        Scanner input = new Scanner(System.in);
        String  getTicker = input.nextLine();

        Connection connection = getConnection();
        String query = "select * from TickerHistory where Ticker = '" + getTicker
                + "' and HistoryDate = '2017-03-10'";

            try {
                Statement statement = connection.createStatement();
                ResultSet rs = statement.executeQuery(query);

                System.out.print("\n");

                if(rs.next()) {
                    String ticker = rs.getString("Ticker");
                    String companyName = rs.getString("CompanyName");
                    String historyDate = rs.getString("HistoryDate");
                    int volume = rs.getInt("Volume");
                    String formattedVolume = NumberFormat.getInstance().format(volume);
                    double openPrice = rs.getDouble("OpenPrice");
                    double highPrice = rs.getDouble("HighPrice");
                    double lowPrice = rs.getDouble("LowPrice");
                    double closePrice = rs.getDouble("ClosePrice");

                    System.out.println(ticker + "   " + companyName +  "    Volume: " + formattedVolume);
                    System.out.println("    History Date:   " + historyDate);
                    System.out.println("    Open:   " + openPrice);
                    System.out.println("    High:   " + highPrice);
                    System.out.println("    Low:    " + lowPrice);
                    System.out.println("    Close:  " + closePrice);
                }
                else {
                    System.out.print("There is no data matching your criteria.");
                }

                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());
            }

    }

    static void highAndClose() {
        System.out.print("\nPlease enter valid ticker: ");
        Scanner input = new Scanner(System.in);
        String getTicker = input.nextLine();

        Connection connection = getConnection();
        String query1 = "select * from TickerHistory where Ticker = '" + getTicker
                + "' order by HighPrice desc limit 1";
        String query2 = "select * from TickerHistory where Ticker = '" + getTicker
                + "' and HistoryDate = '2017-03-10'";

        try {
            Statement statement = connection.createStatement();
            ResultSet rs1 = statement.executeQuery(query1);

            System.out.print("\n");

            if(rs1.next()) {
                String highDate = rs1.getString("HistoryDate");
                double highPrice = rs1.getDouble("HighPrice");

                System.out.println(highDate + " " + highPrice);
            }
            else {
                System.out.println("There is no data matching your criteria.");
            }

            ResultSet rs2 = statement.executeQuery(query2);

            while(rs2.next()) {
                String currentDate = rs2.getString("HistoryDate");
                double currentClose = rs2.getDouble("ClosePrice");

                System.out.println(currentDate + "  " + currentClose);

            }

            rs2.close();
            rs1.close();
            statement.close();
            connection.close();

            rs2 = null;
            rs1 = 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());
        }
    }

    static void getTopFive(){
        System.out.print("\nPlease enter valid date (yyyy-mm-dd): ");
        Scanner input = new Scanner(System.in);
        String getDate = input.nextLine();

        System.out.print("\n");

        Connection connection = getConnection();
        String query = "select * from TickerHistory where HistoryDate = '" + getDate +"' order by Volume desc limit 5";

        try {
            Statement statement = connection.createStatement();
            ResultSet rs1 = statement.executeQuery(query);

            while(rs1.next()) {
                String ticker = rs1.getString("Ticker");
                int volume = rs1.getInt("Volume");
                String formattedVolume = NumberFormat.getInstance().format(volume);

                System.out.println("Ticker: " + ticker + "  " + formattedVolume);
            }

            ResultSet rs2 = statement.executeQuery(query);

            if(!rs2.next()) {
                System.out.println("There is no data matching your criteria.");
            }

            rs2.close();
            rs1.close();
            statement.close();
            connection.close();

            rs2 = null;
            rs1 = 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());
        }
    }
    public static void main(String[] args) {

        String userSelection = appMenu();

        while(!userSelection.equals("quit")) {

            if(userSelection.equals("1")) {
                getAllTickers();
            }
            else if(userSelection.equals("2")) {
                getRecentData();
            }
            else if(userSelection.equals("3")) {
                highAndClose();
            }
            else if(userSelection.equals("4")) {
                getTopFive();
            }

            userSelection = appMenu();
        }

        System.out.print("\nYou have exited from the menu.");
    }
}