/**
 * LoadActors puts actor data in IMDB format into a DBMS via JDBC
 * 
 * @author Rasmus Pagh 
 * @version 2008.09.28
 *
 * "Bugs": Non-existing exception handling
 */

import java.sql.*;
import java.util.regex.*;
import java.io.*;

public class LoadActors
{
    /* Variables to be set by user*/
    private static String dir = "/Users/pagh/teaching/dlf08/";
    private static String username = "root";
    private static String databasename = "IMDB";
    private static String password = "";
    private static String url = "jdbc:mysql://localhost/"+databasename;
    // private static String url = "jdbc:mysql://mysql.itu.dk/"+databasename;
    
    /* Other variables */
    private static String filename = "actor-Cruise-Duval.list";

    private static Connection conn = null;
    private static Statement stmt = null;
    private static ResultSet rs = null;
    private static BufferedReader inputfile = null;

    private static String s;
    private static String firstname = null;
    private static String lastname = null;
    private static String movie = null;
    private static String character = null;
    private static String year = null;
    private static String ddl = 
    "CREATE TABLE IF NOT EXISTS `actorInfo` ("+
    "`firstname` VARCHAR(100),"+
    "`lastname` VARCHAR(100),"+
    "`movie` VARCHAR(500),"+
    "`year` INT,"+
    "`character` VARCHAR(500)"+
    ")"+
    "engine=innoDB, "+
    "CHARACTER SET utf8;";
    private static String ddl2 = "DELETE FROM actorInfo";

    private static Pattern pName = Pattern.compile("^([^\t]*)\t");
    private static Pattern pMovie = Pattern.compile("\t([^(]*)");
    private static Pattern pYear = Pattern.compile("(\\([0-9][0-9][0-9][0-9]\\))");
    private static Pattern pChar = Pattern.compile("(\\[.*\\])");
    private static Matcher m;
    
    public LoadActors() { }

    /**
     * Loader
     * 
     * @param      None
     * @return     Void 
     */
    public static void main (String[] args) throws Exception {

        /* Establish database connection */
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection(url,username,password);
        System.out.println ("Database connection opened.");
        PreparedStatement insert = 
		  conn.prepareStatement("INSERT INTO actorInfo VALUES (?,?,?,?,?)");

	/* Create relation (ignore exception if it already exists) */
	try {
	   conn.createStatement().executeUpdate(ddl);
	   conn.createStatement().executeUpdate(ddl2);
	}
	catch (Exception e) {};

	/* Open file and go to data entries */
	inputfile = new BufferedReader(new FileReader(dir+filename));
	String line = inputfile.readLine();
	while (line!=null && (line.length()<5 || !line.substring(0,5).equals("----\t"))) 			line = inputfile.readLine();

	/** Main data reading loop **/
	line= inputfile.readLine();
	while (line != null) {
	    if(line.length()>0) {

	        /* Parse line using regular expressions defined above */
	        m = pName.matcher(line);
                if (m.find()) {
                    s=m.group();
                    s=s.substring(0,s.length()-1);
                    String[] ss = s.split(",");
                    if (ss.length>1) { // If there is a new name
                        firstname = ss[1].trim();
                        lastname = ss[0];
                    } 
                }

                m = pMovie.matcher(line);
                if (m.find()) movie = m.group().trim();
	   
                m = pYear.matcher(line);
                if (m.find()) year = m.group().substring(1,5);

                m = pChar.matcher(line);
                if (m.find()) {
                    s=m.group();
                    character = s.substring(1,s.length()-1);
                }    

                /* Fill parameters and execute prepared statement */
                insert.setString(1, firstname); 
                insert.setString(2, lastname); 
                insert.setString(3, movie); 
                insert.setInt(4, Integer.parseInt(year)); 
                insert.setString(5, character); 
                insert.executeUpdate();

		// System.out.println(firstname+";"+lastname+";"+movie+";"+year+";"+character);
            }
            line= inputfile.readLine();
        }

        stmt = conn.createStatement();
        rs = stmt.executeQuery("SELECT * FROM actorInfo WHERE lastname='Cruise'");

        // May do some testing here
        
        conn.close ();
        System.out.println ("Database connection terminated.");

    }
}