import java.sql.*;
import java.net.*;
import java.io.*;
import java.lang.*;
import java.util.*;

public class plot {
	
	
	public static void main (String[] args) throws Exception
       {
       	
       	
       	String primo="";
       	String secondo="";
       	String terzo="";
     	String year=null;
     	int inizio=0;
     	int id=1;
     	int movieid=0;
        
        FileWriter fi1 = new FileWriter("G:\\Documents and Settings\\cristiano\\Desktop\\tesina seminario\\tesina\\finalScripts\\plot.sql");
		PrintWriter out1=new PrintWriter(fi1);
		FileWriter fi2 = new FileWriter("G:\\Documents and Settings\\cristiano\\Desktop\\tesina seminario\\tesina\\finalScripts\\plot2movie.sql");
		PrintWriter out2=new PrintWriter(fi2);
		
          
       	FileReader f = new FileReader("G:\\Documents and Settings\\cristiano\\Desktop\\tesina seminario\\tesina\\file IMDB\\plot.txt");
        BufferedReader filebuf = new BufferedReader(f); 
      
		out1.println("INSERT IGNORE INTO plot VALUES\n");
        out2.println("INSERT IGNORE INTO plot2movie VALUES\n");
            
      
		Connection conn = null;
		ResultSet rs=null;
        
           try
           {
               String userName = "root";
               String password = "041524";
               String url = "jdbc:mysql://localhost/imdb";
               Class.forName ("com.mysql.jdbc.Driver").newInstance ();
               conn = DriverManager.getConnection (url, userName, password);
               //System.out.println ("Database connection established");
           }
           catch (Exception e)
           {
               System.err.println ("Cannot connect to database server");
           }
          
           
    	
        String nextStr; 
        nextStr = filebuf.readLine();
        
        while (!nextStr.equals("PLOT SUMMARIES LIST")){
			nextStr = filebuf.readLine();
        }	
        
       	 nextStr = filebuf.readLine();
         nextStr = filebuf.readLine();
         nextStr = filebuf.readLine();
         
          
        
        
        
        
         while (nextStr!=null){
        	nextStr = nextStr.replace("'","");
       		
       		try{
       		
       		secondo="";
       	    while (!nextStr.startsWith("BY")){
       	    	
       	    	nextStr = nextStr.replace("'"," ");
       		
       	    		
       	    	if (nextStr==null) break;
       			
       			if (nextStr.startsWith("MV")){
       				
       				
       				primo = nextStr;
       				primo = primo.replace("MV:","");
       				primo=primo.substring(1);
       				if (primo.contains("(")){
       					int aperta=primo.indexOf("(");
       					int chiusa=primo.indexOf(")");
       					
       					year = primo.substring(aperta+1,chiusa);
       					primo=primo.substring(0,aperta);
       				}
       				else{
       					year=null;
       				}
       					
       			//	movieid++;
       			}
       			
       			if (nextStr.startsWith("PL")){
       				secondo = secondo+nextStr;
       				secondo = secondo.replace("PL:","");
       			}	
       				
       				
       		
       		nextStr = filebuf.readLine();
       		
       		nextStr.replace("'","");
       		
       			
       		}	
       		
		terzo = nextStr;
		terzo=terzo.replace("'","");
		terzo=terzo.replace("BY:","");
		
		/*
       	System.out.println("movie:" +primo);
       	System.out.println("year:" +year);
       	System.out.println("plot:" +secondo);
       	System.out.println("author:" +terzo);
       	System.out.println("******************");
       	*/
       	
       	
       	
       	
       	try{
     	  		
				Statement s = conn.createStatement();
  				
  				rs=s.executeQuery("SELECT id FROM movies WHERE title='"+primo+"' and year='"+year+"'");
         				
  			 	rs.last();
  			 	
  			 	
  			    movieid =(Integer)rs.getObject(1);
  			 	}
  			 	
  			 	catch(Exception e){
  			 		
  			 	}
       					out1.append("('"+id+"','"+secondo+"','"+terzo+"'),\n");
           				out2.append("('"+id+"','"+movieid+"'),\n");
           			
           				id++;
           				
       	
       	}
       	
       	catch(NullPointerException n){
       	break;
       	}
       	
       	
       	
       	
 	  
    
    
	   nextStr=filebuf.readLine();
        	 
	   	 
	   }
	   
	    	 	
	    	 	filebuf.close();  // chiude il file	
 				out1.close();
 				out2.close();

 		}
        
        
}