import java.sql.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.io.File;
import java.io.FileNotFoundException;
import java.util.Scanner;
import java.util.Date;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;

public class Svoboda_Project_1 {

    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.getSQLState());
            System.out.println("VenderError: " + e.getErrorCode());
        }

        return connection;
    }

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

            while(rs.next()) {
                String ticker = rs.getString("Ticker");
                System.out.println(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());
        }

        displayMainMenu();
    }

    static void recentDataForTicker() {

        System.out.print("\nPlease enter a valid ticker: ");
        Scanner in = new Scanner(System.in);
        String ticker = in.nextLine();
        ticker.toLowerCase();

        switch(ticker){
        case "aapl" : break;
        case "amzn" : break;
        case "atvi" : break;
        case "auph" : break;
        case "catb" : break;
        case "csco" : break;
        case "dish" : break;
        case "fb" : break;
        case "fnsr": break;
        case "ftr" : break;
        case "gild" : break;
        case "grpn" : break;
        case "intc" : break;
        case "jblu" : break;
        case "lltc" : break;
        case "mu" : break;
        case "oclr" : break;
        case "sbux" : break;
        case "siri" : break;
        case "spls" : break;
        default: System.out.println("This is not a valid ticker. Please choose from the main menu and start again.\n");
        }

        Connection connection = getConnection();
        String query = "select * from TickerHistory where Ticker = '" + ticker + "' order by HistoryDate desc limit 1";

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

            while(rs.next()) {
                String tickerName = rs.getString("Ticker");
                int volume = rs.getInt("Volume");
                String companyName = rs.getString("CompanyName");
                String historyDate = rs.getString("HistoryDate");
                double open = rs.getDouble("OpenPrice");
                double high = rs.getDouble("HighPrice");
                double low = rs.getDouble("LowPrice");
                double close = rs.getDouble("ClosePrice");

                System.out.print(tickerName + "      " + companyName + "    " + "Volume: "); System.out.printf( "%,d\n", volume);
                System.out.println("          " + "History Date: " + historyDate);
                System.out.println("          " + "Open:   $" + open);
                System.out.println("          " + "High:   $" + high);
                System.out.println("          " + "Low:    $" + low);
                System.out.println("          " + "Close:  $" + close);
            }

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

        displayMainMenu();
    }

    static void closePriceForTicker() {

        System.out.print("\nPlease enter a valid ticker: ");
        Scanner in = new Scanner(System.in);
        String ticker = in.nextLine();

        switch(ticker){
        case "aapl" : break;
        case "amzn" : break;
        case "atvi" : break;
        case "auph" : break;
        case "catb" : break;
        case "csco" : break;
        case "dish" : break;
        case "fb" : break;
        case "fnsr": break;
        case "ftr" : break;
        case "gild" : break;
        case "grpn" : break;
        case "intc" : break;
        case "jblu" : break;
        case "lltc" : break;
        case "mu" : break;
        case "oclr" : break;
        case "sbux" : break;
        case "siri" : break;
        case "spls" : break;
        default: System.out.println("This is not a valid ticker. Please choose from the main menu and start again.\n");
        }

        Connection connection = getConnection();
        String query = "select * from TickerHistory where Ticker = '" + ticker + "' order by ClosePrice desc limit 1";
        String query2 = "select * from TickerHistory where Ticker = '" + ticker + "' order by HistoryDate desc limit 1";

        double highClosePrice=0;
        double currentClosePrice = 0;

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



            while(rs.next()) {


                highClosePrice = rs.getDouble("ClosePrice");
                String highHistoryDate = rs.getString("HistoryDate");


                System.out.println(highHistoryDate + "     $" + highClosePrice);
            }




        Statement statement2=connection.createStatement();
        ResultSet rs2=statement2.executeQuery(query2);

        while(rs2.next()) {

        currentClosePrice = rs2.getDouble("ClosePrice");
        String currentHistoryDate = rs2.getString("HistoryDate");
        System.out.println(currentHistoryDate + "     $" + currentClosePrice);

        }

        rs.close();
        statement.close();
        rs2.close();
        statement2.close();
        connection.close();

        rs2 = null;
        statement2 = null;
        connection = null;

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


        double percentDifference = ((currentClosePrice - highClosePrice) / highClosePrice) * 100.00;

        double roundPercentDifference = Math.round(percentDifference * 100.00) / 100.00;


        System.out.println("Percent Difference is " + roundPercentDifference);

        displayMainMenu();
        }



    static void topFiveTickers() {

        System.out.print("\nPlease enter a valid date (yyyy-mm-dd): ");
        Scanner in = new Scanner(System.in);
        String date = in.nextLine();

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

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

            while(rs.next()) {

                String ticker = rs.getString("Ticker");
                int volume = rs.getInt("Volume");

                System.out.print("Ticker: " + ticker + "     "); System.out.printf( "%,d\n", volume);

            }

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


        displayMainMenu();
    }

    public static void displayMainMenu() {

        System.out.println("\n---------- Stock Reporting Menu ----------");
        System.out.println("\nMENU OPTIONS:");
        System.out.println("\n  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 the top five active tickers for date");
        System.out.print("\nSelect number or type quit: ");

        Scanner in = new Scanner(System.in);
        String menu = in.nextLine();

        switch(menu){
        case "1":   getAllTickers();
                    break;
        case "2":   recentDataForTicker();
                    break;
        case "3":   closePriceForTicker();
                    break;
        case "4":   topFiveTickers();
                    break;
        case "quit": System.out.println("You have exited from the menu.");
                    break;
        default: displayMainMenu();
            }
    }


    public static void main(String[] args) {

        displayMainMenu();

}
}