/**
* An initial attempt at visualizing Admissions/Registration data
* This will connect to the DB, get some data about gender vs. enrolment and visualize
**/

// ADMIT_STATUS,START_TERM,FED_COHORT_GROUP,GENDER,STATE,ETHNIC_DESC,DENOM_DESC,BIRTH_DATE,CAREER_GOAL1,CAREER_GOAL2,CAREER_GOAL3,INTEREST1,INTEREST2,INTEREST3,SAT_BEST,ACT_BEST,STUDENT_TYPE,HS_COUNTY_DESC,HS_RANK_NUMERATOR,HS_RANK_DENOMINATOR,HS_EXT_CREDITS,HS_EXT_GPA

import de.bezier.data.sql.*;
import au.com.bytecode.opencsv.CSVReader;
import java.util.ArrayList;
import java.sql.*;


ArrayList startTerms;
ArrayList genderCounts;
PFont smallFont;
PFont bigFont;
Connection conn;
int bar_width; // width of each bar
int x_buff=50;
int y_buff=50; // x and y margins for the plot

// String semesters[] = {"JA", "SP", "FA", "S1", "S2", "S3"};

void setup() {
    size(1100, 700);
    // noStroke();
    smallFont = loadFont("CartoGothicStd-Bold-14.vlw");
    bigFont = loadFont("CartoGothicStd-Bold-18.vlw");
    smooth();
    frameRate(10);

    connectDB();
    getSomeData();
    bar_width = (width-2*x_buff)/(startTerms.size());
}


void draw() {
    background(200);
    textAlign(LEFT);
    textFont(bigFont, 18);
    fill(0);
    text("Admissions Data Viz", 20, 30);
    text("____________________", 20, 40);

    // drawHistograms();
    drawGraphs();
}

void drawGraphs() {
    textFont(smallFont, 7);

    beginShape();
    stroke(100, 200, 100);
    strokeWeight(2);
    for (int i = 0; i < startTerms.size(); i++) {
        float x1 = (i*bar_width - bar_width/2) + x_buff;
        float y1 = height - 2*getMaleHeight(i) - 2*y_buff;
        String x = (String)startTerms.get(i);
        textAlign(CENTER);
        fill(0);
        text(x, x1, height-y_buff);
        noFill();
        vertex(x1, y1);
        if(i != 0 || i == startTerms.size()-1)
            vertex(x1, y1);
    }
    endShape();
    strokeWeight(0.5);
    stroke(100, 100);
    fill(100, 200, 100);
    for (int i = 0; i < startTerms.size(); i++) {
        float x1 = (i*bar_width - bar_width/2) + x_buff;
        float y1 = height - 2*getMaleHeight(i) - 2*y_buff;
        fill(100, 200, 100, 255);
        ellipse(x1, y1, 8, 8);
        line(x1, height-2*y_buff, x1, 2*y_buff);
        textFont(smallFont, 12);
        fill(230);
        int[] a = (int[]) genderCounts.get(i);
        if(dist(x1, y1, mouseX, mouseY) < 5)
            text("Male: "+a[0], x1, y1+20);
    }

    beginShape();
    stroke(200, 100, 100);
    strokeWeight(2);
    noFill();
    for (int i = 0; i < startTerms.size(); i++) {
        float x1 = (i*bar_width - bar_width/2) + x_buff;
        float y1 = height - 2*getFemaleHeight(i) - 2*y_buff;
        vertex(x1, y1);
        if(i != 0 || i == startTerms.size()-1)
            vertex(x1, y1);
    }
    endShape();


    strokeWeight(0.5);

    for (int i = 0; i < startTerms.size(); i++) {
        float x1 = (i*bar_width - bar_width/2) + x_buff;
        float y1 = height - 2*getFemaleHeight(i) - 2*y_buff;
        fill(200, 100, 100);
        ellipse(x1, y1, 8, 8);
        textFont(smallFont, 12);
        fill(170);
        int[] a = (int[]) genderCounts.get(i);
        if(dist(x1, y1, mouseX, mouseY) < 5)
            text("Female: "+a[1], x1, y1-20);
    }
}

/**
 * Draws the data as histograms
 */
void drawHistograms() {
    for (int i = 0; i < startTerms.size(); i++) {
        float maleHeight = getMaleHeight(i);
        float femaleHeight = getFemaleHeight(i);

        fill(150);
        rect(i*bar_width, height-maleHeight, bar_width, maleHeight);

        fill(210);
        rect(i*bar_width, 70, bar_width, femaleHeight);
        rotate(3*PI/2.0);
        fill(40);
        textFont(smallFont, 12);
        String x = (String) startTerms.get(i);
        int a[] = (int[]) genderCounts.get(i);
        text(x, -height/2, i*bar_width+bar_width/2);
        rotate(PI/2.0);

        if(mouseX>i*bar_width && mouseX<(i*bar_width+bar_width)) {
            stroke(40, 70);
            line(mouseX, 0, mouseX, height);
            // && mouseY>(height-maleHeight-femaleHeight
            String printLabelStr = "Semester: " + x + "\nM: " + a[0] + "\nF: " + a[1];
            textFont(smallFont, 14);
            text(printLabelStr, 20, width/2);
        }
    }
}

float getMaleHeight(int i) {
    int[] a = (int[]) genderCounts.get(i);
    return map(a[0], 0, 60000, 0, (height)/2);
}

float getFemaleHeight(int i) {
    int[] a = (int[]) genderCounts.get(i);
    return map(a[1], 0, 60000, 0, (height)/2);
}


void getSomeData() {
    try {
        startTerms = new ArrayList();
        genderCounts = new ArrayList();
        Statement stmt = conn.createStatement();
        // only get Fall enrollments
        ResultSet rs = stmt.executeQuery("SELECT DISTINCT START_TERM FROM admissions_main WHERE START_TERM LIKE '%FA'" );
        while (rs.next()) {
            String x = rs.getString(1);
            startTerms.add(x);
        }
        Collections.sort(startTerms, new termsCompare());

        for (int i = 0; i < startTerms.size(); i++) {
            String x = (String)startTerms.get(i);
            Statement stmt_g = conn.createStatement();
            ResultSet rs_g = stmt_g.executeQuery("SELECT COUNT(GENDER) FROM admissions_main WHERE START_TERM='" +x+ "' AND GENDER='M'" );
            rs_g.next();
            int m = rs_g.getInt(1);
            rs_g = stmt_g.executeQuery("SELECT COUNT(GENDER) FROM admissions_main WHERE START_TERM='" +x+ "' AND GENDER='F'" );
            rs_g.next();
            int f = rs_g.getInt(1);
            int[] a = {m, f};
            // println(x+ ":males " + m + ", females: " +f);

            genderCounts.add(a);
            stmt_g.close();
            rs_g.close();
        }

    } catch (Exception e) {
        e.printStackTrace();
    }
}

/**
void testDB2() {
    try {
        genderCounts = new ArrayList();

        boolean done = false;
        int year_initls = 90;
        Statement stmt;
        ResultSet rs_m;
        while (!done) {
            for (int i = 0; i < semesters.length; i++) {
                String start_term = year_initls+"/"+semesters[i];
                stmt = conn.createStatement();
                rs_m = stmt.executeQuery("SELECT COUNT(START_TERM) FROM admissions_main WHERE GENDER='M' AND START_TERM='" + start_term +"'" );
                rs_m.next();
                int m = rs_m.getInt(1);
                stmt.close();
                rs_m.close();

                stmt = conn.createStatement();
                rs_m = stmt.executeQuery("SELECT COUNT(START_TERM) FROM admissions_main WHERE GENDER='F' AND START_TERM='" + start_term +"'" );
                rs_m.next();
                int f = rs_m.getInt(1);
                stmt.close();
                rs_m.close();
                println("start_term: " +start_term+ "-> f:" +f+ ", m:" +m);
            }

            year_initls++;
            if(year_initls==100)
                year_initls = 00;
            else if (year_initls==10)
                done=true;
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
}

*/

// helper methods
/**
 * Connect to the database
 */
void connectDB() {
    conn = null;
    try
    {
        String userName = "root";
        String password = "raheelms";
        String url = "jdbc:mysql://localhost:8889/admissionsviz";
        Class.forName ("com.mysql.jdbc.Driver").newInstance ();
        conn = DriverManager.getConnection (url, userName, password);
        System.out.println ("Database connection established");
    }
    catch (Exception e)
    {
        System.err.println ("Cannot connect to database server");
        e.printStackTrace();
    }

}

/**
 * For loading the CSV file. Abandoning this since will need data in DB for ease
 * of querying
 */

/*
void loadCSV() {
    int linenum=1;
    try {
        CSVReader reader = new CSVReader(new FileReader("/Users/rahmad/Documents/Programming/Processing/AdmissionsViz/data/data.txt"));
        String [] nextLine;
        students = new ArrayList();
        while ((nextLine = reader.readNext()) != null) {
            try {
                Student s = new Student();
                s.admit_status = nextLine[0];
                String[] stterm = split(nextLine[1], '/');
                s.startterm_year = int(stterm[0]);
                s.startterm_semester = stterm[1];
                s.gender = nextLine[3];
                s.state = nextLine[4];
                s.ethnic_desc = nextLine[5];
                s.denom_desc = nextLine[6];
                s.birth_date = nextLine[7];
                s.hs_ext_gpa = nextLine[21];
                //System.out.println("gpa: " + s.admit_status);
                students.add(s);
                linenum++;
            } catch (Exception e) {
                //System.out.println("got an exception while creating student, moving on");
            }

        }
    } catch (Exception e) {
        System.out.println("Exception while loading CSV: at line no. " + linenum );
        e.printStackTrace();
    }
    System.out.println("loaded " + students.size() + " students");
    for (int i = 0; i < students.size(); i++) {
        Student s = (Student) students.get(i);
        println("student " + i + "'s gender: " + s.gender);
    }
}
*/

/**
 * A class to sort the terms (stored in the form 99/FA, 00/SP)
 */
private class termsCompare implements java.util.Comparator {
    public int compare(Object a, Object b) {
        String[] aSplit = ((String) a).split("/");
        String[] bSplit = ((String) b).split("/");
        int aYear = Integer.parseInt(aSplit[0]);
        String aSem = aSplit[1];
        int bYear = Integer.parseInt(bSplit[0]);
        String bSem = bSplit[1];

        int sdif = 0;
        if(aYear > 80 && bYear <= 30)  // i.e., the earliest year in 20th century is 1980, and latest year in 21st is 2030
          sdif = -1;
        else if (bYear > 80 && aYear <= 20)
          sdif = 1;
        else {
          if(aYear < bYear)
            sdif = -1;
          else if(aYear > bYear)
            sdif = 1;
          else {
            sdif = compareSemesters(aSem, bSem);
          }
        }
        return sdif;
     }

    private int compareSemesters(String s1, String s2) {
        if(s1.equals(s2))
            return 0;
        if(s1.equals("JA"))
            return 1;
        else if(s1.equals("SP")) {
            if(s2.equals("JA"))
                return -1;
            else
                return -1;
        } else if(s1.equals("S1")) {
            if(s2.equals("JA") || s2.equals("SP"))
                return -1;
            else
                return 1;
        } else if(s1.equals("S2")) {
            if(s2.equals("JA") || s2.equals("SP") || s2.equals("S1"))
                return -1;
            else
                return 1;
        } else if(s1.equals("S3")) {
            if(s2.equals("JA") || s2.equals("SP") || s2.equals("S1") || s2.equals("S2"))
                return -1;
            else
                return 1;
        } else if(s1.equals("FA")) {
            return -1;
        }

        return 0;
    }

}