import de.bezier.data.sql.*;
import processing.serial.*;

MySQL msql;
PFont font;

// what table and what column to query in db
// this determines what data is played back
//String table    = "script";
//String column    = "line";

String table="words";
String ctable="constructions";
String column="word";
int minID;
int maxID;

// vars holding response most recent query
String data= "";
String idStr;
int id=0;
int i=0, w=0;

// list of table IDs matching query
ArrayList phraseIDs, wordIDs;
Random repeatableRandom;

int phrase_id=0;
int[] word_ids = {
  0, 0, 0, 0
};
int yes_votes=0;
int no_votes=0;

// timing
int last=0;
int count=0;
boolean paused=false;
boolean repeating=true;

Serial port;

void setupSerial() {
    // Print a list in case COM1 doesn't work out
  println("Available serial ports:");
  println(Serial.list());

  //port = new Serial(this, "COM1", 9600);
  // Uses the first available port
  port = new Serial(this, Serial.list()[2], 9600);
};

void setup()
{
  // general graphics setup
  size( 1280, 720 );
  background(0);
  smooth();

  // Font and text drawing setup
  font = loadFont("HelveticaNeue-48.vlw");
  textAlign(CENTER);
  textAlign(CENTER,CENTER);
  rectMode(CORNER);
  fill(255);
  textFont(font, 64);

  int randseed=0;
  randomSeed(randseed);
  println("randomSeed: "+randseed);

  // this example assumes that you are running the 
  // mysql server locally (on "localhost").
  //
  // replace --username--, --password-- with your mysql-account.
  //
  String user     = "bob";
  String pass     = "password";

  // name of the database to use
  String database = "naming";

  // connect to database of server "localhost"
  msql = new MySQL( this, "localhost", database, user, pass );

  if ( msql.connect() )
  {   
    msql.query( "SELECT MIN(id) FROM " + table );
    msql.next();
    minID=msql.getInt(1);
    println( "min row ID: " + minID );

    msql.query( "SELECT MAX(id) FROM " + table );
    msql.next();
    maxID=msql.getInt(1);
    println( "max row ID: " + maxID );
  }
  else
  {
    // connection failed !
    println("connection failed!");
    exit();
  }
  println("distinct words: "+ (maxID-minID));
  println("4 word pairs: "+((maxID-minID)/4));

  phraseIDs=new ArrayList();
  wordIDs = new ArrayList();
  readPhrasesFromDB();

  // randomize order of phrase presentation
  //  repeatableRandom = new Random(1234);
  //  Collections.shuffle(phraseIDs, repeatableRandom);

  //  fillPhrases(3204);  
  i=-1;
  loadNextPhrase();
  last = -2500;
  
  setupSerial();
}

void readPhrasesFromDB() {
  //msql.query("SELECT DISTINCT phrase_id FROM "+ctable+" WHERE yes_votes=0 AND no_votes=0");
  //  msql.query("SELECT DISTINCT phrase_id FROM "+ctable+" WHERE yes_votes>0");
  msql.query("SELECT DISTINCT phrase_id FROM "+ctable);
  while(msql.next()) {
    phrase_id=msql.getInt("phrase_id");
    phraseIDs.add(phrase_id);
  }

  println(phraseIDs.size()+" phrases");
  if(phraseIDs.size()<=0) exit();
}

void fillPhrases(int num) {
  int word_id=0;
  msql.query("SELECT DISTINCT id FROM "+table);
  //  msql.query("SELECT DISTINCT phrase_id FROM "+ctable+" WHERE yes_votes>0");
  while(msql.next()) {
    word_id=msql.getInt("id");
    wordIDs.add(word_id);
  }

  for(int i=0;i<num;i++)
    print((Integer)wordIDs.get(i)+" ");

  repeatableRandom = new Random(1234);
  Collections.shuffle(wordIDs, repeatableRandom);

  for(int i=0;i<num; i++) {
    createNextPhrase();  
    println(count+" "+idStr+" "+data);
    //    count++;
  }
}

void createNextPhrase() {
  data="";
  count++;
  int word_id=0;
  idStr=str(count);

  //  for(int i=0;i<maxID-minID;i++)
  //    print((Integer)wordIDs.get(i)+" ");

  for(int i=0; i<4; i++) {
    word_id=(Integer)wordIDs.get(w);
    // select data from that random row
    msql.query("SELECT * FROM "+table+" WHERE id="+word_id);
    while(msql.next()) {
      data += " "+msql.getString("word");
      //      id = msql.getInt("id");
      idStr+= " "+word_id;
      //      println(data + " " + id);
      word_ids[i]=word_id;
    }
    w++;
    if(w>=wordIDs.size()) {
      w=0;
      println("RAN OUT OF WORDS");
    }
  }
  phraseIDs.add(count);
  no_votes=0;
  yes_votes=0;

  last=millis();
  addPhraseToDB();
}

//void createNewPhrase() {
//  data="";
//  count++;
//  idStr=str(count);
//  for(int i=0; i<4; i++) {
//    // get a number for a random row
//    int rand_row = int(random(minID, maxID));
//
//    // select data from that random row
//    msql.query("SELECT * FROM "+table+" WHERE id>= "+rand_row+" ORDER BY id LIMIT 1");
//    while(msql.next()) {
//      data += " "+msql.getString(column);
//      id = msql.getInt("id");
//      idStr+= " "+id;
//      //println(data + " " + id);
//      word_ids[i]=id;
//    }
//  }
//  phraseIDs.add(count);
//  no_votes=0;
//  yes_votes=0;
//
//  last=millis();
//  addPhraseToDB();
//}

void loadNextPhrase() {
  i++;
  if(i>=phraseIDs.size())i=0;
  loadPhrase();
  //  if(i>=phraseIDs.size()) {
  //    createNewPhrase();
  //  } 
  //  else {
  //    loadPhrase();
  //  };
}

void loadPreviousPhrase() {
  i--;
  //  if(i<0) {
  //    createNewPhrase();
  //  } 
  //  else {
  //    loadPhrase();
  //  };
  if(i<0)i=phraseIDs.size()-1;
  loadPhrase();
}

void loadWords() {
  data="";
  for(int i=0; i<4; i++) {
    // select data from that random row
    msql.query("SELECT * FROM "+table+" WHERE id= "+word_ids[i]);
    while(msql.next()) {
      data += " "+msql.getString("word");
      idStr+=" "+msql.getString("id");
    }
  }
}

void loadPhrase() {
  phrase_id=(Integer)phraseIDs.get(i);
  print(i+" loading words for phrase_id "+phrase_id);
  
  // get word_ids and votes for current phrase_id
  msql.query("SELECT * FROM "+ctable+" WHERE phrase_id="+phrase_id);
  while(msql.next()) {
    word_ids[0]=msql.getInt("word_id1");
    word_ids[1]=msql.getInt("word_id2");
    word_ids[2]=msql.getInt("word_id3");
    word_ids[3]=msql.getInt("word_id4");
    yes_votes=msql.getInt("yes_votes"); 
    no_votes=msql.getInt("no_votes");
    idStr=str(phrase_id);//+" "+word_ids[0]+" "+word_ids[1]+" "+word_ids[2]+" "+word_ids[3];
    loadWords();
  }
  println(" loaded.");
  last=millis();
}

void addPhraseToDB() {
  print("  adding phrase to db");
  // query series of id, filenames for current faceID
  msql.execute("INSERT INTO "+ctable+" (word_id1, word_id2, word_id3, word_id4) VALUES ("+word_ids[0]+", "+word_ids[1]+", "+word_ids[2]+", "+word_ids[3]+")");
  msql.query("SELECT LAST_INSERT_ID()");
  while(msql.next()) {
    phrase_id=msql.getInt("LAST_INSERT_ID()");
  }
  println(" added.");
}

void voteUp() {
  println("  voting up phrase_id "+phrase_id);
  msql.execute("UPDATE constructions SET yes_votes=yes_votes+1 WHERE phrase_id="+phrase_id);
  yes_votes++;
  //  if(!repeating)
  loadNextPhrase();
}

void voteDown() {
  println("  voting down phrase_id "+phrase_id);
  msql.execute("UPDATE constructions SET no_votes=no_votes+1 WHERE phrase_id="+phrase_id);
  no_votes++;
  //  if(!repeating)
  loadNextPhrase();
}

void clearVotes() {
  println("  clearing votes for phrase_id "+phrase_id);
  msql.execute("UPDATE constructions SET no_votes=0, yes_votes=0 WHERE phrase_id="+phrase_id);
  no_votes=0;
  yes_votes=0;
}

void keyPressed() {
  if(key==' ')
    paused = (paused) ? false : true;
  if(key=='y')
    voteUp();
  if(key=='n')
    voteDown();
  if(key=='r') {
    repeating = (repeating) ? false: true;
    println("repeating is "+str(repeating));
  }
  if(keyCode==LEFT)
    loadPreviousPhrase();
  if(keyCode==RIGHT)
    loadNextPhrase();
  if((keyCode==DELETE) || (keyCode==BACKSPACE))
    clearVotes();
}

void drawYesQuestions() {
    stroke(0, 32, 0);
  msql.query("SELECT phrase_id FROM "+ctable+" WHERE yes_votes>0");
  //  msql.query("SELECT DISTINCT phrase_id FROM "+ctable+" WHERE yes_votes>0");
  while(msql.next()) {
    int tmp_id=msql.getInt("phrase_id");
    int j=phraseIDs.indexOf(tmp_id);//phrase_id);

    int linepos=round(map(j, 0, phraseIDs.size(), 0, width));
    line(linepos, height-50, linepos, height);
  }
}
void drawNoQuestions() {
    stroke(32, 0, 0);
  msql.query("SELECT phrase_id FROM "+ctable+" WHERE no_votes>0");
  //  msql.query("SELECT DISTINCT phrase_id FROM "+ctable+" WHERE yes_votes>0");
  while(msql.next()) {
    int tmp_id=msql.getInt("phrase_id");
    int j=phraseIDs.indexOf(tmp_id);//phrase_id);

    int linepos=round(map(j, 0, phraseIDs.size(), 0, width));
    line(linepos, height-50, linepos, height);
  }
}
void drawAnsweredQuestions() 
{
  drawNoQuestions();
  drawYesQuestions();
}

void draw()
{
  while (port.available() > 0) {
    serialEvent(port.read());
  }
  background(0);

  // new query every 1000 ms
  if(!repeating) 
    if(millis()-last>2500) {

      loadNextPhrase();
      println(idStr+" "+data);
      //    if(count>3619)
      //      exit();
    }

  //fill(map(millis()-last, 0, 1000, 0, 192));
  fill(192);
  textFont(font, 24);
  text(data, 0, 0, width, height);//width/2, 200);
  //  fill(map(millis()-last, 0, 1000, 0, 128));
  fill(128);
  textFont(font, 16);
  text(idStr+"\n"+(i)+" of "+str(phraseIDs.size())+"\n"+"yes votes: "+yes_votes+"  no votes: "+no_votes, width/2, height*.6666);

  drawAnsweredQuestions();
  
  // draw current position
  int linepos=round(map(i, 0, phraseIDs.size(), 0, width));
  stroke(128);
  line(linepos, height-50, linepos, height);


}

void mouseClicked() {
  i=round(map(mouseX, 0, width, 0, phraseIDs.size()));
  loadPhrase();
}

void serialEvent(int serial) 
{ 
  if(serial=='y')
    voteUp();
  if(serial=='n')
    voteDown();
}