import de.bezier.data.sql.*;

MySQL msql;
PFont font;

String table="script";
String jointable="words_script";
String column="line";

String query="painting";
//String query="art";
//String query="body";
//String query="space";

int minID;
int maxID;


// list of table IDs matching query
ArrayList lineIDs;
Random repeatableRandom;
int i=0;

// vars holding response most recent query
String data= "";
int id=0;


// timing
int last=0;

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

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

  int randomseed = 0;
  randomSeed(randomseed);

  // 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 name,id FROM characters");
//    while(msql.next()) {
//      data=msql.getString("name");
//      id=msql.getInt("id");
//      println(id+" "+data);
//    }

    msql.query("SELECT "+table+".id, "+table+".line FROM "+table+", "+jointable+", words WHERE "+table+".id="+jointable+".line_id AND "+jointable+".word_id=words.id AND words.word=\""+query+"\"");
    lineIDs = new ArrayList();

    while(msql.next()) {
      id=msql.getInt(table+".id");
      data=msql.getString(table+".line");
      println(id+" "+data);
      lineIDs.add(id);
    }
    println("  "+lineIDs.size()+" results.");
    repeatableRandom = new Random(1234);
    Collections.shuffle(lineIDs, repeatableRandom);
  }
  else
  {
    // connection failed !
    println("connection failed!");
    exit();
  }
  
  last=-5000;
}

void draw()
{
  background(0);

  // new query every 1000 ms
  if(millis()-last>5000) {

    // select data from that random row
    msql.query("SELECT * FROM "+table+" WHERE id="+lineIDs.get(i));
    while(msql.next()) {
      i++;
      if(i>=lineIDs.size())i=0;
      data = msql.getString("line");
      id = msql.getInt("id");
      println(i+" of "+lineIDs.size()+" "+id+" "+data);
      background(0);
    }
    last=millis();
  }

  // display result text
  fill(map(millis()-last, 0, 1000, 0, 192));
  textFont(font, 64);
  text(data, 0, 0, width, height);//width/2, 200);

  // display query text
//  fill(map(millis()-last, 0, 1000, 0, 128));
//  text(query, 0, +200, width, height);//width/2, 200);

  // display row id
//  fill(map(millis()-last, 0, 1000, 0, 128));
//  //textFont(font, 32);
//  text(id, width/2, height-100);
}