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

public class crazyCredits {
	
	
	public static void main (String[] args) throws Exception
       {
       	
       	
       	String primo="";
       	String secondo="";
       	String[]res=null;
       	int inizio=0;
       	int id=1;
       	int movieid=0;
       	String year=null;
     
        FileWriter fi1 = new FileWriter("G:\\Documents and Settings\\cristiano\\Desktop\\tesina seminario\\tesina\\finalScripts\\crazy.sql");
		PrintWriter out1=new PrintWriter(fi1);
		FileWriter fi2 = new FileWriter("G:\\Documents and Settings\\cristiano\\Desktop\\tesina seminario\\tesina\\finalScripts\\crazy2movie.sql");
		PrintWriter out2=new PrintWriter(fi2);
		
	    
	    
       	FileReader f = new FileReader("G:\\Documents and Settings\\cristiano\\Desktop\\tesina seminario\\tesina\\file IMDB\\crazy-credits.txt");
        BufferedReader filebuf = new BufferedReader(f); 
        
        
        out1.println("INSERT IGNORE INTO crazycredit VALUES\n");
        out2.println("INSERT IGNORE INTO crazycredit2movie 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("CRAZY CREDITS")){
			nextStr = filebuf.readLine();
        }	
        
       	 nextStr = filebuf.readLine();
         nextStr = filebuf.readLine();
        
        if (nextStr.equals("-------------------------------------------------------------------------------")){
        	System.out.println("Crazy inseriti nel db");
        	System.exit(0);
        }
        
        
         while (nextStr!=null){
         nextStr = nextStr.replace("'","");
       		
       	
       		if (nextStr.startsWith("------------------------")){
       			System.out.println("fine parsing");
       			break;
       		}
       	try{
       	
 			
       	secondo="";
       		
       		
        
      	while (!nextStr.equals("")){
      	nextStr = nextStr.replace("'","");
       	
       		
       	   
       	   	
       	   		if (nextStr.startsWith("#")){
       	   			
       	   			//movieid++;
       	   			
       	   			primo=nextStr;
       	   			primo = primo.replace("#","");
       	   			
       	   			if (primo.contains("(")){
 						inizio = primo.indexOf("(");
 						int ultimo = primo.indexOf(")");
 				   		 year = primo.substring(inizio+1,ultimo);		
 					}
 			
 					else {
 						year = null;
 					}
 					
       	   			
       	   		}
       		
       	    	else {
       	    		
       	    		
       	    		secondo=secondo+nextStr;
       	    		
       	    		
       	    		
       	    		//secondo=secondo.replace("-","\n");
       	    		
       	    	
       	    		
       	    		
       	    		
       	    		
       	    	}
       	 
       			 if (primo.contains("(")){
    	
   					 primo=primo.substring(0,inizio);     
    			}
    
       			
       			
       			
       			
       	 		nextStr=filebuf.readLine();
       	 		
       	 		if (nextStr.startsWith("------------------------")){
       			System.out.println("fine parsing");
       			break;
       		}
       	 		
       	  
       	    	
       	}	
       	
       	
       	primo=primo.substring(1);
       	
       		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){
  			 		
  			 	}
      
       		
       				res= secondo.split("-");
       	    		
       	    		for (int i=0;i<res.length;i++){
       	    			
       	    			if(!res[i].equals("")){
       	    			
       	    			
       	    			//System.out.println(primo);
       	    			//System.out.println(year);
       	      			//System.out.println(res[i]);
       	      			//System.out.println(movieid+" "+id);
           				//System.out.println("******************");
           				
           			
           				
           				out1.append("('"+id+"','"+res[i]+"'),\n");
           				out2.append("('"+id+"','"+movieid+"'),\n");
           				
           				id++;
           				
           				
       					}
      			 	    			
       	    			
       	    		}
       	    	
      
       
       
		
       	
       	
       }
       	
    	catch(NullPointerException n){
       		n.printStackTrace();
       		
       	}
     
    
				
			
   
    	
      
	   
	   
	   nextStr=filebuf.readLine();
	   
	   if (nextStr.startsWith("------------------------")){
       			System.out.println("fine parsing");
       			break;
       		}
        	 
	   	 
	   }
	   
	    	 	
	    	 	filebuf.close();  // chiude il file	
 				out1.close();
 				out2.close();
 					
 			
 				
 		}
        
        
}