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.util.Scanner;

public class Database_Reader {

    private static int EnterSelection(Scanner scanner) {

        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: ");
        int userResponse = scanner.nextInt();

        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 ticker: ");
        String userResponse = inputTicker.nextLine();

        Connection connection = getConnection();
        String query = "select CompanyName, HistoryDate, OpenPrice, ClosePrice, Volume from TickerHistory where Ticker ='"
                + userResponse + "' order by HistoryDate desc limit 1";
        try {
            Statement statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(query);

            while (rs.next()) {
                String companyName = rs.getString("CompanyName");
                Date historyDate = rs.getDate("HistoryDate");
                double openPrice = rs.getDouble("OpenPrice");
                double closePrice = rs.getDouble("ClosePrice");
                int volume = rs.getInt("Volume");

                System.out.println(companyName + "  " + historyDate + "  " + (formatter.format(openPrice)) + "  "
                        + (formatter.format(closePrice)) + " " + "  " + (formatterII.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 EnterTickerII() {
        Scanner inputTicker = new Scanner(System.in);
        NumberFormat formatter = NumberFormat.getCurrencyInstance();
        NumberFormat formatterII = NumberFormat.getNumberInstance();

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

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

        Connection connectionII = getConnectionII();
        String queryII = "SELECT 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);

            while (rs.next() && rsII.next()) {
                Date historyDate = rs.getDate("HistoryDate");
                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)) + " "
                        + (formatter.format(closePriceII)) + " " + differencePercentage);
            }
            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.getCurrencyInstance();

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

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

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

            while (rs.next()) {
                String companyName = rs.getString("CompanyName");
                int volume = rs.getInt("Volume");
                System.out.println(companyName + "  " + 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("");
            int 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;
            default:
                System.out.println("Invalid selection");
                rerun = true;
                break;
            }

        } while (rerun = true);
    }

}