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

public class Database_Reader {

    private static String EnterSelection(Scanner scanner) {
        System.out.println("--------------Stock Reporting Menu--------------");
        System.out.println("MENU OPTIONS:");
        System.out.println("");
        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");
        System.out.println("");

        System.out.println("Select number or type quit: ");
        String userResponse = scanner.next();

        return userResponse;
    }

    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!&allowMultiQueries=true");
        } catch (InstantiationException | IllegalAccessException | ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        }

        return connection;
    }

    static Connection getConnectionII() {

        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!&allowMultiQueries=true");
        } catch (InstantiationException | IllegalAccessException | ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        }

        return connection;
    }

    static void getAllTickers() {
        Connection connection = getConnection();
        String query = "SELECT GROUP_CONCAT(DISTINCT Ticker SEPARATOR ', ') as Ticker FROM TickerHistory";

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

            while (rs.next()) {

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

            }
            System.out.println("");

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

    }

    public static void EnterTicker() {
        Scanner inputTicker = new Scanner(System.in);
        NumberFormat formatter = NumberFormat.getCurrencyInstance();
        NumberFormat formatterII = NumberFormat.getNumberInstance();

        System.out.println("Please enter a valid ticker: ");
        String userResponse = inputTicker.nextLine();

        Connection connection = getConnection();
        String query = "select Ticker, CompanyName, HistoryDate, OpenPrice, ClosePrice, HighPrice, LowPrice, Volume from TickerHistory where Ticker ='"
                + userResponse + "' order by HistoryDate desc limit 1";
        try {
            Statement statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(query);
            if (!rs.isBeforeFirst()) {
                System.out.println("Invalid Ticker Entered");
            } else {
                while (rs.next()) {
                    String ticker = rs.getString("Ticker");
                    String companyName = rs.getString("CompanyName");
                    Date historyDate = rs.getDate("HistoryDate");
                    double openPrice = rs.getDouble("OpenPrice");
                    double closePrice = rs.getDouble("ClosePrice");
                    double highPrice = rs.getDouble("HighPrice");
                    double lowPrice = rs.getDouble("LowPrice");
                    int volume = rs.getInt("Volume");

                    System.out.println(ticker + "    " + companyName + "  " + "Volume: " + (formatterII.format(volume))
                            + "\n      " + "History Date: " + historyDate + "\n      " + "Open:  "
                            + (formatter.format(openPrice)) + "\n      " + "High:  " + (formatter.format(highPrice))
                            + "\n      " + "Low:   " + (formatter.format(lowPrice)) + "\n      " + "Close: "
                            + (formatter.format(closePrice)));

                }
            }
            System.out.println("");
            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());
        }

    }

    public static void EnterTickerII() {
        Scanner inputTicker = new Scanner(System.in);
        NumberFormat formatter = NumberFormat.getCurrencyInstance();
        NumberFormat formatterII = NumberFormat.getPercentInstance();
        formatterII.setMinimumFractionDigits(0);

        System.out.println("Please enter ticker: ");
        String userResponse = inputTicker.nextLine();

        Connection connection = getConnection();
        String query = "SELECT HistoryDate as HistoryDateI, ClosePrice, ClosePrice FROM TickerHistory WHERE Ticker ='"
                + userResponse + "' order by ClosePrice desc limit 1";

        Connection connectionII = getConnectionII();
        String queryII = "SELECT HistoryDate as HistoryDateII, ClosePrice as ClosePriceII FROM TickerHistory WHERE Ticker ='"
                + userResponse + "' order by HistoryDate desc limit 1";

        try {
            Statement statement = connection.createStatement();
            Statement statementII = connectionII.createStatement();

            ResultSet rs = statement.executeQuery(query);
            ResultSet rsII = statementII.executeQuery(queryII);
            if (!rs.isBeforeFirst()) {
                System.out.println("Invalid Ticker Entered");
            } else {
                while (rs.next() && rsII.next()) {
                    Date historyDate = rs.getDate("HistoryDateI");
                    Date historyDateII = rsII.getDate("HistoryDateII");
                    double closePrice = rs.getDouble("ClosePrice");
                    double closePriceII = rsII.getDouble("ClosePriceII");
                    double decrease = (closePriceII - closePrice);
                    double differencePercentage = (decrease / closePrice) * 100;

                    System.out.println(historyDate + "  " + (formatter.format(closePrice)) + "\n" + historyDateII + " "
                            + (formatter.format(closePriceII)) + "\n" + "Perfect Difference is: "
                            + (formatterII.format(differencePercentage / 100)));
                }
            }
            System.out.println("");

            rs.close();
            rsII.close();
            statement.close();
            statementII.close();
            connection.close();
            connectionII.close();

            rs = null;
            statement = null;
            connection = null;

            rsII = null;
            statementII = null;
            connectionII = 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 EnterTickerIII() {
        Scanner inputTicker = new Scanner(System.in);
        NumberFormat formatter = NumberFormat.getNumberInstance();

        System.out.println("Please enter valid date (yyyy-mm-dd): ");
        String userDate = inputTicker.next();

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

        try {
            Statement statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(query);
            if (!rs.isBeforeFirst()) {
                System.out.println("Invalid date entered");
            } else {

                while (rs.next()) {
                    String ticker = rs.getString("Ticker");
                    int volume = rs.getInt("Volume");
                    System.out.println("Ticker:      " + ticker + "    " + (formatter.format(volume)));
                }
            }
            System.out.println("");
            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());
        }

    }

    public static void main(String[] args) {

        Scanner input = new Scanner(System.in);
        boolean rerun = false;

        do {
            System.out.println("");
            String selection = EnterSelection(input);

            switch (selection) {

            case "1":
                getAllTickers();
                rerun = true;
                break;
            case "2":
                EnterTicker();
                rerun = true;
                break;
            case "3":
                EnterTickerII();
                rerun = true;
                break;
            case "4":
                EnterTickerIII();
                rerun = true;
                break;
            case "quit":
                rerun = false;
                System.out.println("...Application Ended...");
                return;
            default:
                System.out.println("Invalid selection");
                rerun = true;
                break;
            }

        } while (rerun = true);
    }

}