import java.io.File;
import java.io.FileNotFoundException;
import java.sql.*;
import java.util.HashMap;
import java.util.Map;
import java.util.Scanner;



public class DataLoader {

    //manages the connection to the marketData database
    public void connectDBandLoad(){
         Connection conn = null;

         try
         {
             String userName = "root";
             String password = "root";
             String url = "jdbc:mysql://localhost:8889/marketData";
             Class.forName("com.mysql.jdbc.Driver").newInstance();
             conn = DriverManager.getConnection(url, userName, password);
             System.out.println ("Database connection established");
             loadTextFilesToDB(conn);

         }
         catch (Exception e)
         {
             System.err.println ("Cannot connect to database server");
         }
         finally
         {
             if (conn != null)
             {
                 try
                 {
                     conn.close ();
                     System.out.println ("Database connection terminated");
                 }
                 catch (Exception e) {}
             }
         }
    }

    //reads through the text files and creates data lines
    private void loadTextFilesToDB(Connection conn) throws SQLException {

        String currentDate = "20070101";

        //for each file
        while(!currentDate.equals("20080101")){

            Map<String, Object> dataLine = new HashMap<String, Object>();
            File file = new File("Resources/TSXData/TSX_" + currentDate + ".txt");

            try {
                Scanner scanner = new Scanner(file);
                scanner.useDelimiter(",|\r");
                scanner.nextLine();

                //for each line
                while(scanner.hasNextLine()){

                    dataLine.put("SYMBOL", scanner.next());
                    dataLine.put("DATE", scanner.next());
                    dataLine.put("OPEN", scanner.nextFloat());
                    dataLine.put("HIGH", scanner.nextFloat());
                    dataLine.put("LOW", scanner.nextFloat());
                    dataLine.put("CLOSE", scanner.nextFloat());
                    dataLine.put("VOLUME", scanner.nextInt());
                    scanner.nextLine();

                storeLineInDB(dataLine, conn);
                }

                scanner.close();

            } catch (FileNotFoundException e) {
                System.out.println("No market data for " + currentDate);
            }

            currentDate = incrementCurrentDate(currentDate);
        }

    }

    //Returns a string that is an incremented version of the given date string
    private String incrementCurrentDate(String currentDate) {

        Integer day = Integer.valueOf(currentDate.substring(6));
        Integer month = Integer.valueOf(currentDate.substring(4,6));
        Integer year = Integer.valueOf(currentDate.substring(0,4));

        if(++day > 31){ day = 1; month++; }
        if(month > 12){ month = 1; year++; }

        return year + String.format("%02d", month) + String.format("%02d", day);
    }

    //Stores the info found in the data line in the appropriate tables
    private void storeLineInDB(Map<String, Object> _dataLine, Connection _conn) {

        System.out.println(_dataLine);
        String symbol = (String)_dataLine.get("SYMBOL");
        String date = (String)_dataLine.get("DATE");
        Float open = (Float)_dataLine.get("OPEN");
        Float high = (Float)_dataLine.get("HIGH");
        Float low = (Float)_dataLine.get("LOW");
        Float close = (Float)_dataLine.get("CLOSE");
        Integer volume = (Integer)_dataLine.get("VOLUME");

        try{
            Statement stmt = _conn.createStatement();
            String query = "SELECT * FROM commodity WHERE symbol = '" + symbol + "'";
            ResultSet rs = stmt.executeQuery(query);

            //if symbol exists in commodity table, do nothing, else create new table
            if(!rs.next())
                createNewCommodityEntry(symbol, _conn);

            //add data to table
            String insert = "INSERT INTO `marketData`.`" + symbol + "` " +
                    "(date, open, high, low, close, volume) VALUES (" +
                    "'" + date + "', " +
                    open + ", " +
                    high + ", " +
                    low + ", " +
                    close + ", " +
                    volume + ")";

            stmt = _conn.createStatement();
            Integer result = stmt.executeUpdate(insert);

            // close the result set and the statement
            rs.close();
            stmt.close();
        } catch(SQLException s){ s.printStackTrace(); }

    }

    //creates a new entry in the commodity table, and also creates a table with the symbols name
    private void createNewCommodityEntry(String _symbol, Connection _conn) {
        try{
            //insert the commodity name into the commodity table
            Statement stmt = _conn.createStatement();
            String insert = "INSERT INTO commodity VALUES('" + _symbol + "', NULL)";
            stmt.executeUpdate(insert);

            //create a new table with the commodity name
            stmt = _conn.createStatement();
            String update = "CREATE TABLE  `" + _symbol + "` (" +
                    "`date` DATE NOT NULL ," +
                    "`open` FLOAT NOT NULL ," +
                    "`high` FLOAT NOT NULL ," +
                    "`low` FLOAT NOT NULL ," +
                    "`close` FLOAT NOT NULL ," +
                    "`volume` INT NOT NULL ," +
                    "PRIMARY KEY (  `date` ))";
            stmt.executeUpdate(update);

            // close the result set and the statement

            stmt.close();
        } catch(SQLException s){ s.printStackTrace(); }

    }

    //main method for data loader
    public static void main(String[] args) {
        DataLoader loader = new DataLoader();
        loader.connectDBandLoad();

    }

}