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

public class mpaa {
	
	
	public static void main (String[] args) throws Exception
       {
       	
       	
       	String primo="";
       	String secondo="";
       	String terzo="";
       	int movieid=0;
     
        FileWriter fi = new FileWriter("G:\\Documents and Settings\\cristiano\\Desktop\\tesina seminario\\tesina\\finalScripts\\mpaa.sql");
		PrintWriter out=new PrintWriter(fi);
	      
       	FileReader f = new FileReader("G:\\Documents and Settings\\cristiano\\Desktop\\tesina seminario\\tesina\\file IMDB\\mpaa-ratings-reasons.txt");
        
        BufferedReader filebuf = new BufferedReader(f); 
      
			Connection conn = null;
		ResultSet rs=null;
        ResultSet rs2=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("MPAA RATINGS REASONS LIST")){
			nextStr = filebuf.readLine();
        }	
        
       	 nextStr = filebuf.readLine();
         nextStr = filebuf.readLine();
         nextStr = filebuf.readLine();
         
          
        
       
        
        
         while (nextStr!=null){
         nextStr = nextStr.replace("'","");
       		
       	
       	try{
       	
 			
       	secondo="";
       		
       		
        
      	while (!nextStr.startsWith("--------")){
      	nextStr = nextStr.replace("'","");
       	
       		
       	   
       	   	
       	   		if (nextStr.startsWith("MV:")){
       	   			
       	   			primo=nextStr;
       	   			}
       		
       	    	else  {
       	    		
       	    		
       	    		secondo=secondo+nextStr;
       	    		secondo=secondo.replace("RE:","");	
       	    	
       	    	
       	    		
       	    		
       	    		
       	    		
       	    	}
       	 
       
       	 		nextStr=filebuf.readLine();
       	  
       	    	
       	}	
      
       	
      
       
       /*

       	System.out.println("movie:" +primo);
       	System.out.println("mpaa reason:" +secondo);
       	System.out.println("******************");
       	*/
       	
       }
       	
    	catch(NullPointerException n){
       		System.out.println("errore nullpointer\n");
       		
       	}
     
     
     	
 	  	if (primo.contains("(")){
 	  		int indice = primo.indexOf("(");
 	  		
 	  		primo = primo.substring(4,indice);
  	  	}
     
     
     
     	if (!primo.equals("") && !secondo.equals("") && !secondo.startsWith("----------------")){
    			
    			
    		
    		//System.out.println(primo);	
    
			          
           
    		try{
    
    
    			
    			    
  				Statement s = conn.createStatement ();
  				
  				rs=s.executeQuery("SELECT id from movies WHERE title='"+primo+"'");
         		
         		rs.last();
         		movieid= (Integer)rs.getObject(1); 	
         		 		 	
   				
        		
           }
           catch(Exception e){
           //	e.printStackTrace();
           }
    			
    			//System.out.println(primo);
    			//System.out.println(secondo);
    			//System.out.println(movieid);
    			//System.out.println("******");
    			
  			 	out.println("UPDATE movies SET mpaa='"+secondo+"' WHERE id='"+movieid+"';");
              
   				}	
      	

	   
	   nextStr=filebuf.readLine();
        	 
	   	 
	   }
	   
	    	 	
	    	 	filebuf.close();  // chiude il file	
 				out.close();
 		}
        
        
}