import de.bezier.data.sql.*;

// ---- MySQL stuff ---- ///
MySQL msql;

String user="bob";
String pass="password";
String database = "naming";
String table="words";
String ctable="constructions";
String column="word";

// for couting the number of distinct words
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;
ArrayList wordIDs;
ArrayList setIDs;
Random repeatableRandom;
int seed=1;
int j=0;

int phrase_id=0;
int[] word_ids = {
  0, 0, 0, 0
};
int currseed=0;

int yes_votes=0;
int no_votes=0;


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

// ---- graphics ---- ///
PFont font;


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);

  //  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.
  //


  // 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();
  setIDs=new ArrayList();
  
  readSetsFromDB();
  readPhrasesFromDB();

  //  fillPhrases(3204);  

  i=-1;
  loadNextPhrase();
  last = -2500;
}


void readSetsFromDB() {
  msql.query("SELECT DISTINCT seed FROM "+ctable);
  while(msql.next()) {
    int set_id=msql.getInt("seed");
    setIDs.add(set_id);
  }

  println(setIDs.size()+" distinct seeds");
  if(setIDs.size()<=0) exit();
}

void readPhrasesFromDB(int seed) {
  //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+" WHERE seed="+seed);
  while(msql.next()) {
    phrase_id=msql.getInt("phrase_id");
    phraseIDs.add(phrase_id);
  }

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

void readPhrasesFromDB() {
  readPhrasesFromDB(1234);
}

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(seed);
  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 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");
    currseed=msql.getInt("seed");
    idStr=str(phrase_id);//+" "+word_ids[0]+" "+word_ids[1]+" "+word_ids[2]+" "+word_ids[3];
    loadWords();
    idStr+=" "+currseed;
  }
  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, seed) VALUES ("+word_ids[0]+", "+word_ids[1]+", "+word_ids[2]+", "+word_ids[3]+", "+seed+")");
  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 loadSet() {
  phraseIDs = new ArrayList();
  readPhrasesFromDB((Integer)setIDs.get(j));
  i=-1;
  loadNextPhrase();
}

void loadPreviousSet() {
  j--;
  if(j<0) j=setIDs.size()-1;
  loadSet();
}

void loadNextSet() {
  j++;
  if(j>=setIDs.size()) j=0;
  loadSet();
}

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();
  if(keyCode==UP)
    loadPreviousSet();
  if(keyCode==DOWN)
    loadNextSet();
}


void drawYesQuestions() {
  stroke(0, 64, 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(64, 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()
{
  background(0);

  // auto-advance?
  if(!repeating) 
    if(millis()-last>2500) {

      loadNextPhrase();
      println(idStr+" "+data);
    }

  fill(192);
  textFont(font, 24);
  text(data, 0, 0, width, height);//width/2, 200);
  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();

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