import de.bezier.data.sql.*;

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;

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

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

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

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();
  }
  phraseIDs=new ArrayList();

//  msql.query("SELECT DISTINCT phrase_id FROM "+ctable+" WHERE yes_votes=0 AND no_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();

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

  loadNextPhrase();
  
  last = -2500;
}

//void runUpUntil(int rununtil) {
//  while(count < rununtil) {
//    data="";
//    idStr="";
//    for(int i=0; i<4; i++) {
//      // get a number for a random row
//      int rand_row = int(random(minID, maxID));
//      if(rand_row==873)
//        rand_row=1;
//
//      //println(rand_row);
//
//      // 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);
//      }
//    }
//    println(count+idStr+" "+data);
//    count++;
//  }
//}

void draw()
{
  background(0);

  // new query every 1000 ms
  if(millis()-last>2500) {
    if(!repeating) {
      loadNextPhrase();
      count++;
    } 
    else {
      last=millis();
    }
    println(idStr+" "+data);

    //    if(count>3619)
    //      exit();
  }

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

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_id[i]=id;
    }
  }
  phraseIDs.add(count);
  no_votes=0;
  yes_votes=0;
  
  last=millis();
  addPhraseToDB();
}

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

void loadPreviousPhrase() {
  i--;
  if(i<0) {
    createNewPhrase();
  } else {
    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_id[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);

  // query series of id, filenames for current faceID
  msql.query("SELECT * FROM "+ctable+" WHERE phrase_id="+phrase_id);

  while(msql.next()) {
    word_id[0]=msql.getInt("word_id1");
    word_id[1]=msql.getInt("word_id2");
    word_id[2]=msql.getInt("word_id3");
    word_id[3]=msql.getInt("word_id4");
    yes_votes=msql.getInt("yes_votes"); 
    no_votes=msql.getInt("no_votes");
    idStr=str(phrase_id);//+" "+word_id[0]+" "+word_id[1]+" "+word_id[2]+" "+word_id[3];
    loadWords();
  }
  println(" loaded.");
  last=millis();
}

void addPhraseToDB() {
  print("  adding phrase to db");
  phrase_id=count;
  // query series of id, filenames for current faceID
  msql.execute("INSERT INTO "+ctable+" (phrase_id, word_id1, word_id2, word_id3, word_id4) VALUES ("+phrase_id+", "+word_id[0]+", "+word_id[1]+", "+word_id[2]+", "+word_id[3]+")");

  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 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)
    clearVotes();
}