// 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
PImage input;

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

ArrayList faces;

String user     = "bob";
String pass     = "password";

// name of the database to use
String database = "looking";
String table="face_images";

int i=0;
int f=0;

int face_id=0;
int video_id=3;

int yes_votes=0;
int no_votes=0;

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

void setup()
{
  // general graphics setup
  //size( 1280, 720 );
  //  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, 24);

  // 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() )
  {   
    // connection failed !
    println("connection failed!");
    exit();
  }

  faceIDs = new ArrayList();
  // build list of image files and shuffle
  //  msql.query("SELECT face_id FROM "+table+" WHERE yes_votes>0");
  //  msql.query("SELECT DISTINCT face_id FROM "+table);
  msql.query("SELECT DISTINCT face_id FROM "+table+" WHERE yes_votes=0 AND no_votes=0 AND video_id="+video_id);
  while(msql.next()) {
    face_id=msql.getInt("face_id");
    faceIDs.add(face_id);
  }
  println(faceIDs.size()+" faces");
  if(faceIDs.size()<=0) exit();

  repeatableRandom = new Random(1234);
  Collections.shuffle(faceIDs, repeatableRandom);
  loadNextFaceSequence();
  frameRate(24);
}


void loadNextFaceSequence() {
  i++;
  if(i>=faceIDs.size())i=0;
  loadFaceSequence();
}

void loadPreviousFaceSequence() {
  i--;
  if(i<0) i=faceIDs.size()-1;
  loadFaceSequence();
}

void loadFaceSequence() {
  faces = new ArrayList();
  face_id=(Integer)faceIDs.get(i);
  print(i+" loading images for face_id "+face_id);

  // query series of id, filenames for current faceID
  msql.query("SELECT * FROM "+table+" WHERE face_id="+face_id+" AND video_id="+video_id);

  while(msql.next()) {
    String file_name = msql.getString("file_name");
    String path=msql.getString("path");
    yes_votes=msql.getInt("yes_votes");
    no_votes=msql.getInt("no_votes");
    // open image  
    PImage newimage = loadImage(path+"/"+file_name);
    if(newimage!=null) {
      // add to faces array
      faces.add(newimage);
      print(".");
    } 
    else {
      println("  couldn't open image file "+file_name);
    }
  }
  println(faces.size()+" loaded");
  f=0;
}

void draw()
{
  if(!paused) {
    background(0);
    image((PImage)faces.get(f), 400, 200);//, 400, 400);
    fill(64, 64, 64);
    text("frame "+f+" of "+faces.size()+" for face "+i+" of "+faceIDs.size()+"\nface_id "+face_id+"\n"+yes_votes+" yes votes, "+no_votes+" no votes.", 400, 650);
    f++;
    //    if(f>=faces.size()) {
    //      loadNextFaceSequence();
    //    }
    if(f>=faces.size()) f=0;
  }
}

void voteUp() {
  println("  voting up face_id "+face_id);
  msql.execute("UPDATE face_images SET yes_votes=yes_votes+1 WHERE face_id="+face_id+" AND video_id="+video_id);
  yes_votes++;
  loadNextFaceSequence();
}

void voteDown() {
  println("  voting down face_id "+face_id);
  msql.execute("UPDATE face_images SET no_votes=no_votes+1 WHERE face_id="+face_id+" AND video_id="+video_id);
  no_votes++;
  loadNextFaceSequence();
}

void clearVotes() {
  println("  clearing votes for face_id "+face_id);
  msql.execute("UPDATE face_images SET no_votes=0, yes_votes=0 WHERE face_id="+face_id+" AND video_id="+video_id);
  no_votes=0;
  yes_votes=0;

}

void keyPressed() {
  if(key==' ')
    paused = (paused) ? false : true;
  if(key=='y')
    voteUp();
  if(key=='n')
    voteDown();
  if(keyCode==LEFT)
    loadPreviousFaceSequence();
  if(keyCode==RIGHT)
    loadNextFaceSequence();
  if(keyCode==DELETE)
    clearVotes();
}