package Main;

import java.io.File;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Scanner;

import com.mysql.jdbc.Statement;


public class Database {

    private String user = "";
    private String password = "";
    private String table = "";
    private String url = "jdbc:mysql://localhost/stockanalysis";

    private Connection conn = null;

    public Database()
    {

    }

    public Database(String inputUser, String inputPassword)
    {
        user = inputUser;
        password = inputPassword;
    }

    public Database(String inputUser, String inputPassword, String inputTable)
    {
        user = inputUser;
        password = inputPassword;
        table = inputTable;
    }

    public void setUser(String inputUser)
    {
        user = inputUser;
    }

    public void setPassword(String inputPassword)
    {
        password = inputPassword;
    }

    public void setTable(String inputTable)
    {
        table = inputTable;
    }

    public void establishConnection() {
        try{
            try{
                Class.forName("com.mysql.jdbc.Driver").newInstance();
            }catch(Exception e){
                System.err.println("Can't connect to Driver");
                System.err.println(e.getMessage());
            }
            conn = DriverManager.getConnection(url, user, password);
            System.out.println("Connected");

        }catch(Exception e){
            System.err.println("Could not Connect");
            System.err.println(e.getMessage());
        }
    }

    public void disconnect(boolean close) throws SQLException
    {
        if(close)
        {
            conn.close();
            System.out.println("Disconnected");
        }
    }

    public void fileToDatabase(File newFile, String tableName) throws FileNotFoundException, SQLException, ParseException
    {
        Scanner file = new Scanner(newFile);
        file.nextLine();
        int totalUpdates = 0;

        while(file.hasNext())
        {
            String temp = file.nextLine();
            String[] line = temp.split(",");



            java.sql.Date date = java.sql.Date.valueOf(line[0]);
            double open = Double.parseDouble(line[1]);
            double high = Double.parseDouble(line[2]);
            double low = Double.parseDouble(line[3]);
            double close = Double.parseDouble(line[4]);
            double volume = Double.parseDouble(line[5]);

            double amountChange = close - open;
            double percentChange = amountChange/open*100;

            PreparedStatement pStmt = null;

            DatabaseMetaData dbm = conn.getMetaData();

            ResultSet RS = dbm.getTables(null, null, tableName, null);
            if(!RS.next())
            {
                System.out.println("Table not found, creating new table.");
                String makeTable = "CREATE TABLE " + tableName + "(date DATE, PRIMARY KEY(date), open FLOAT, high FLOAT, low FLOAT, close FLOAT, volume INT, amountChange FLOAT, percentChange FLOAT )";
                pStmt = conn.prepareStatement(makeTable);
                pStmt.execute();
                System.out.println("Table created!");
            }

            try{
                String insert = "INSERT INTO " + tableName + "(date, open, high, low, close, volume, amountChange, percentChange) VALUES(?, ?, ?, ?, ?, ?, ?, ?)";
                pStmt = conn.prepareStatement(insert);
                pStmt.setDate(1, date);
                pStmt.setDouble(2, open);
                pStmt.setDouble(3, high);
                pStmt.setDouble(4, low);
                pStmt.setDouble(5, close);
                pStmt.setDouble(6, volume);
                pStmt.setDouble(7, amountChange);
                pStmt.setDouble(8, percentChange);
                pStmt.executeUpdate();
                totalUpdates += pStmt.getUpdateCount();
            }catch(Exception e){
                String x = e.getMessage();
                if(x.contains("Duplicate entry"))
                {
                    continue;
                }
                else
                    e.printStackTrace();
            }
        }

        System.out.println("Made " + totalUpdates + " updates to the database.");

        file.close();
    }

    public ResultSet execute(String sqlQuery) throws SQLException
    {
        PreparedStatement pStmt1 = conn.prepareStatement(sqlQuery);
        pStmt1.execute();

        ResultSet RS = pStmt1.getResultSet();

        return RS;
    }

    public void deleteData() throws SQLException
    {
        PreparedStatement pStmt = conn.prepareStatement("TRUNCATE TABLE  analysisA");
        pStmt.execute();
    }

    public void insertIntoResultTable(String ticker, double nextDayIncrease, double nextDayIncreasePercent, double averageIncreasePercent, double nextDayDecrease, double nextDayDecreasePercent, double averageDecreasePercent) throws SQLException
    {
        double buyValue = nextDayIncreasePercent * averageIncreasePercent;
        double sellValue = nextDayDecreasePercent * averageDecreasePercent;


        PreparedStatement pStmt1 = conn.prepareStatement("INSERT INTO stockanalysis.analysisA SET ticker= '" + ticker + "', daysInc='" + nextDayIncrease + "', pctOfDaysInc='" + nextDayIncreasePercent + "', avgIncPct='" + averageIncreasePercent +
                "', daysDec='" + nextDayDecrease + "',pctOfDaysDec='" + nextDayDecreasePercent + "',avgDecPct='" + averageDecreasePercent + "',buyValue='" + buyValue + "',sellValue='" + sellValue + "';");
        pStmt1.execute();

        pStmt1.close();
    }

    public Date[] getDateData(String ticker) throws SQLException
    {
        PreparedStatement pStmt = conn.prepareStatement("SELECT date FROM " + ticker);
        pStmt.executeQuery();
        ResultSet RS = pStmt.getResultSet();
        ArrayList<Date> dates = new ArrayList<Date>();
        while(RS.next())
        {
            dates.add(RS.getDate(1));
        }
        Date[] returnDates = new Date[dates.size()];

        for(int x = 0; x < returnDates.length; x++)
            returnDates[x] = dates.get(x);

        RS.close();

        return returnDates;
    }

    public double[] getCloseValue(String ticker) throws SQLException
    {
        PreparedStatement pStmt = conn.prepareStatement("SELECT close FROM " + ticker);
        pStmt.executeQuery();
        ResultSet RS = pStmt.getResultSet();

        ArrayList<Double> close = new ArrayList<Double>();
        while(RS.next())
            close.add(RS.getDouble(1));
        double[] returnClose = new double[close.size()];

        for(int x = 0; x < returnClose.length; x++)
            returnClose[x] = close.get(x);

        RS.close();

        return returnClose;
    }


    public double[] getChanges(Date date, String tickerMaster) throws SQLException
    {
        PreparedStatement pStmt = conn.prepareStatement("SELECT percentChange FROM " + tickerMaster + " WHERE date > '" + date + "' ORDER BY date ASC");
        ResultSet RS = pStmt.executeQuery();
        RS.first();
        ArrayList<Double> doubleList = new ArrayList<Double>();
        while(RS.next())
            doubleList.add(RS.getDouble("percentChange"));

        double[] newChange = new double[doubleList.size()];


        for(int x = 0; x < newChange.length; x++)
        {
            newChange[x] = doubleList.get(x);
        }

        return newChange;
    }

    public Date[] getDate(Date date, String tickerMaster) throws SQLException {
        PreparedStatement pStmt = conn.prepareStatement("SELECT date FROM " + tickerMaster + " WHERE date > '" + date + "' ORDER BY date ASC");
        ResultSet RS = pStmt.executeQuery();
        RS.first();
        ArrayList<Date> dateList = new ArrayList<Date>();
        while(RS.next())
            dateList.add(RS.getDate("date"));

        Date[] newDate = new Date[dateList.size()];

        for(int x = 0; x < newDate.length; x++)
        {
            newDate[x] = dateList.get(x);
        }
        return newDate;

    }

}