#temp mariaDB error

import java.util.*;
import java.io.*;
import java.sql.*;

public class RawData {
    private int numberOfLocs = -1;
    private int numberOfRows = -1;

    private String tablename = " Agnes1 ";
//      private String tablename = " Data ";
//      private String tablename = " Visitors ";


    //ling      public String[] getNodeNames(String restriction, String classNumber, String dateRestriction) {
    public String[] getNodeNames(String restriction, int classNumber, String dateRestriction) {
        Connection C = getConnection();
        String[] names = null;


        try {
                    Statement Stmt = C.createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_UPDATABLE
            );

//              Stmt.executeUpdate("CREATE TEMPORARY TABLE temp (loc VARCHAR(30))");
//ling
            Stmt.executeUpdate("CREATE TABLE IF NOT EXISTS #temp (loc VARCHAR(30))");
//end ling

            String s = "WHERE class='" + classNumber + "'";
            if (!restriction.equals("")) {
                s += " AND student like '" + restriction + "'";
            }
            if (!dateRestriction.equals("")) {
                s += " AND timedate='" + dateRestriction + "'";
            }

            Stmt.executeUpdate("INSERT INTO #temp SELECT DISTINCT fromLoc FROM Data " + s);
//              Stmt.executeUpdate(" SELECT DISTINCE loc FROM Data " + s);
            Stmt.executeUpdate("INSERT INTO #temp SELECT DISTINCT toLoc FROM   Data " + s);

            ResultSet RS = Stmt.executeQuery("SELECT DISTINCT loc FROM #temp ORDER BY loc");

            RS.last();

            int n = RS.getRow();
            RS.beforeFirst();

            if (n < 1) return null;

            names = new String[n];
            int i = 0;
            while (RS.next()) {
                names[i++] = RS.getString("loc");
            }
        }
        catch (SQLException E) {
            System.out.println("SQLException: " + E.getMessage());
            System.out.println("SQLState:     " + E.getSQLState());
            System.out.println("VendorError:  " + E.getErrorCode());
        }

        return names;
    }

    public String[] getStudentNames(String restriction, int classNumber, String dateRestriction) {
        Connection C = getConnection();
        String[] names = null;

        try {
            Statement Stmt = C.createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_UPDATABLE
            );

            Stmt.executeUpdate("CREATE TABLE IF NOT EXISTS #temp (student VARCHAR(30))");

            String s = "WHERE class='" + classNumber + "'";
            if (!restriction.equals("")) {
                s += " AND student like '" + restriction + "'";
            }
            if (!dateRestriction.equals("")) {
                s += " AND timedate='" + dateRestriction + "'";
            }

            Stmt.executeUpdate("INSERT INTO #temp SELECT DISTINCT student FROM Data " + s);

            ResultSet RS2 = Stmt.executeQuery("SELECT DISTINCT student FROM #temp ORDER BY student");

//                ResultSet RS2 = Stmt.executeQuery("SELECT distinct student FROM Data ");
//              ResultSet RS2 = Stmt.executeQuery("SELECT distinct Student FROM data " + s);

            RS2.last();
            int n = RS2.getRow();
            System.out.print("n has " + n + " students");

            RS2.beforeFirst();

            if (n < 1) return null;

            names = new String[n];
            int i = 0;
            while (RS2.next()) {
                names[i++] = RS2.getString("student");
            }

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

        return names;
    }

    public double[][] getTransitionMatrix(int classNumber, String dateRestriction, String studentName, Hashtable invNodeNames) {
        int n = invNodeNames.size();
        double[][] matrix = new double[n][n];

        Connection C = getConnection();

        try {
            Statement Stmt = C.createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_UPDATABLE
            );

            String s;
            s =  "SELECT fromLoc,toLoc ";
            s += "FROM data ";
            s += "WHERE class='" + classNumber + "' ";
            s += "  AND student like '" + studentName + "' ";
            s += "  AND fromLoc != 'none' ";
            s += "  AND toLoc != 'none' ";

            if (!dateRestriction.equals("")) {
                s += " AND timedate='" + dateRestriction + "'";
            }

            int fromLoc, toLoc;
            ResultSet RS = Stmt.executeQuery(s);
            while (RS.next()) {
                fromLoc = ((Integer)invNodeNames.get(RS.getString("fromLoc").toLowerCase())).intValue();
                toLoc =   ((Integer)invNodeNames.get(RS.getString("toLoc").toLowerCase())).intValue();
                matrix[fromLoc][toLoc]++;
            }

            for (int i = 0; i < n; i++) {
                for (int j = 0; j < n; j++) {
                    if (matrix[i][j] < .5) matrix[i][j] = -1;
                }
            }


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

        return matrix;
    }



    public ResultSet query(String q) {
        ResultSet RS = null;

        Connection C = getConnection();

        try {
            Statement Stmt = C.createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_UPDATABLE
            );

            String s;
            s =  "CREATE TABLE IF NOT EXISTS #temp (";
            s += "id       INT, ";
            s += "class    INT, ";
            s += "student  VARCHAR(30), ";
            s += "purpose  VARCHAR(50), ";
            s += "fromLoc  VARCHAR(30), ";
            s += "toLoc    VARCHAR(30)";
            s += ")";

            Stmt.executeUpdate(s);

            Stmt.executeUpdate("INSERT INTO #temp " + q);

            Stmt.executeUpdate("CREATE TABLE IF NOT EXISTS #temp1 (loc VARCHAR(30))");

            Stmt.executeUpdate("INSERT INTO #temp1 SELECT DISTINCT fromLoc FROM #temp");
            Stmt.executeUpdate("INSERT INTO #temp1 SELECT DISTINCT toLoc FROM #temp");

            RS = Stmt.executeQuery("SELECT COUNT(DISTINCT loc) AS n FROM #temp1");
            if (RS.next()) {
                numberOfLocs = RS.getInt("n");
            } else {
                numberOfLocs = -1;
            }

            RS = Stmt.executeQuery(q);

            RS.last();
            numberOfRows = RS.getRow();
            RS.first();
        }
        catch (SQLException E) {
            System.out.println("SQLException: " + E.getMessage());
            System.out.println("SQLState:     " + E.getSQLState());
            System.out.println("VendorError:  " + E.getErrorCode());
        }

        return RS;
    }


    private Connection getConnection() {
        Connection C = null;
        try {

        } catch (Exception E) {
            System.err.println("Unable to load driver.");
            E.printStackTrace();
        }
        String dbname =   "pathfinderdata";
        String username = "root";
        String password = "newpass";

        try {

            //mona C = DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433", "path", "path");
            String DB_URL = "jdbc:mysql://localhost:3306/pathfinderdata";
            C = DriverManager.getConnection(DB_URL, username, password);

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

//          System.out.println("LingWang:  End");
        return C;
    }


    public int getNumberOfLocs() { return numberOfLocs; }

    public int getNumberOfRows() { return numberOfRows; }
}