// to connect to remote sql db, use ssh tunneling / port forwarding:
// ssh -fNg -L 3306:localhost:3306 192.168.0.183

import de.bezier.data.sql.*;

import java.util.Collections;
import java.util.Random;

MySQL msql;
PFont font;

// what table and what column to query in db
// this determines what data is played back
String table    = "images";
String column    = "name";
PImage input;

// vars holding response most recent query
String data= "";
String path="";
String fname="";
String filetype="";

// list of table IDs matching query
ArrayList imageIDs;
Random repeatableRandom;
//int i=1900;
//int i=2163;
//int i=487;
int i=300;
int id=0;


// state variables
int last=0; // for timing
boolean paused=false;
boolean showCaption=false;

import oscP5.*;

OscP5 oscP5;
boolean doNext=false;

void setup()
{
  // general graphics setup
  size( 1280, 1024 );
  //size(screen.width, screen.height);
//  size(1680,1050);
  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);

  input = createImage(width-50, height-50, 50);

  // 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 COUNT(*) FROM " + table+" WHERE filetype=\"image\"");
    msql.next();
    println( "number of rows: " + msql.getInt(1) );
  }
  else
  {
    // connection failed !
    println("connection failed!");
    exit();
  }

  imageIDs = new ArrayList();
  // build list of image files and shuffle
  msql.query("SELECT * FROM "+table+" WHERE filetype=\"image\"");
  while(msql.next()) {
    data=msql.getString("name");
    id=msql.getInt("id");
    println(data+" "+id);
    imageIDs.add(id);
  }
  //repeatableRandom = new Random(1234);
  repeatableRandom = new Random();
  Collections.shuffle(imageIDs, repeatableRandom);
  
  last=-1000;
  
  /* start oscP5, listening for incoming messages at port 12000 */
  oscP5 = new OscP5(this, 12000);
}

void draw()
{
  if(!paused) {
    // new query every 1000 ms
//    if(millis()-last>1000) {
    if(doNext) {
      
      //      // get a number for a random row
      //      msql.query( "SELECT FLOOR (MAX(id)*RAND()) FROM "+table);
      //      int rand_row = 0;
      //      while(msql.next()) {
      //        rand_row = msql.getInt(1);
      //        //println(rand_row);
      //      }
      boolean done=false;
      while(!done) {
        // select data from that random row
        msql.query("SELECT * FROM "+table+" WHERE id= "+imageIDs.get(i));
        while(msql.next()) {
          i++;
          if(i>=imageIDs.size())i=0;
          data = msql.getString("name");
          id = msql.getInt("id");
          path = msql.getString("path");
          fname = msql.getString("fname");
          path = path.replace("/Volumes/Reservoir/Assets/Memory/", "/Volumes/Cistern/Memory/");
          PImage newimage = loadImage(path+"/"+fname);
          if(newimage!=null) {
            input=newimage;
            println("SHOWING: "+i+"/"+imageIDs.size()+" "+id+" "+path+"/"+fname);
            background(0);
            image(input, 0, 0, width, float(width)/float(input.width)*input.height);
            done=true;
          }
        }
        doNext=false;
      }
    }

    if(showCaption) {
      fill(255, 255, 255, map(millis()-last, 0, 1000, 0, 255));
      int fheight = 36;
      textFont(font, fheight);
      text(id+" "+data, width/2, height-fheight-20);//width/2, 200);
    }
//    fill(192, 192, 192, map(millis()-last, 0, 1000, 0, 255));
//    textFont(font, 16);
//    text(id, width/2, height*.6666);
  }
}

/* incoming osc message are forwarded to the oscEvent method. */
void oscEvent(OscMessage theOscMessage) {
  /* print the address pattern and the typetag of the received OscMessage */
  //  println("### received an osc message.");
  //  println(" addrpattern: "+theOscMessage.addrPattern());
  //  println(" typetag: "+theOscMessage.typetag());

  String addrpat = theOscMessage.addrPattern();
  String strval = theOscMessage.get(0).stringValue();  

  //println(theOscMessage.toString());
  //theOscMessage.print();
  //theOscMessage.printData();

  if (match(addrpat, "/shownext")!=null) {
    println("Received: "+strval);
    doNext=true;
  }
}


void keyPressed() {
  switch(key){
    case ' ':
      paused = (paused) ? false : true;
      break;
    case 'c':
      showCaption = (showCaption) ? false : true;
      break;
    default:
      doNext=true;
  }
}