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


public class movieLinks{
		 
	
	public static void main(String[]args) throws Exception{
	
	 	String primo = null;
	 	String secondo =null;
	 	String featured_in =null;
	 	String referenced_in =null;
	 	String spin =null;
	 	String spoofed =null;
	 	String version =null;
	 	String followed =null;
	 	String features =null;
	 	String remake =null;
	 	String follows =null;
	 	String references=null;
	 	String year=null;
	 	int id=1;
	 	int movieid=0;
	 	
	 	
	 	
	 	
	 	
	 	String sec = null;
	 	String stri = null;
		String[] res;
	
			FileWriter fi1 = new FileWriter("G:\\Documents and Settings\\cristiano\\Desktop\\tesina seminario\\tesina\\finalScripts\\movieLinks.sql");
		PrintWriter out1=new PrintWriter(fi1);
		FileWriter fi2 = new FileWriter("G:\\Documents and Settings\\cristiano\\Desktop\\tesina seminario\\tesina\\finalScripts\\link.sql");
		PrintWriter out2=new PrintWriter(fi2);
		
	    
		FileReader f = new FileReader("G:\\Documents and Settings\\cristiano\\Desktop\\tesina seminario\\tesina\\file IMDB\\movie-links.txt");
        BufferedReader filebuf = new BufferedReader(f); 
        
        out1.println("INSERT IGNORE INTO link VALUES\n");
        out2.println("INSERT IGNORE INTO movieLinks 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("MOVIE LINKS LIST")){
        	nextStr=filebuf.readLine();
       		}
        	
       nextStr=filebuf.readLine();
       nextStr=filebuf.readLine();
       nextStr=filebuf.readLine();
       
        
        
        
       
       
      	while (nextStr!=null){
      	
      		try{
      		
      	
      		nextStr = nextStr.replace("'","");
      
      		while (!nextStr.equals("")){
   
      
      		if (nextStr.startsWith(" ")){
      	
        	
        		if (nextStr.contains("featured in")){
        			
						secondo+=featured_in+"***";      			
        			
        				res = nextStr.split("featured in");
        				secondo +=res[1];
        				
        				secondo=secondo.replace("null","");
 			
 						sec = secondo.substring(0,secondo.length()-1);
 						
 						
 						
						featured_in=sec.replace("'","");
						
						if (featured_in.contains("(")){
							
							int aperta=featured_in.indexOf("(");
							
							featured_in=featured_in.substring(0,aperta);				
							
							
						}
						
						featured_in=featured_in.replace("***","");						
 			
 			       
          			
        			
        			
        		}
        	
        	
	        		if (nextStr.contains("referenced in")){
	        			
	        			secondo+=referenced_in+"***";
        			
        				res = nextStr.split("referenced in");
        				secondo +=res[1];
        				
        				secondo=secondo.replace("null","");
 			
 						sec = secondo.substring(0,secondo.length()-1);
 			
						referenced_in=sec.replace("'","");
						
						if (referenced_in.contains("(")){
							
							int aperta2=referenced_in.indexOf("(");
							
							referenced_in=referenced_in.substring(0,aperta2);				
							
							
						}
						
						referenced_in=referenced_in.replace("***","");
 			
 			       
          		//		System.out.println("Referenced in: "+referenced_in);
        			
        			
        		}
        		
        		if (nextStr.contains("spin off")){
        			
        				secondo+=spin+"***";
        				res = nextStr.split("spin off");
        			secondo +=res[1];
        				
        				secondo=secondo.replace("null","");
 						sec = secondo.substring(0,secondo.length()-1);
 			
						spin=sec.replace("'","");
						
						if (spin.contains("(")){
							
							int aperta3=spin.indexOf("(");
							
							spin=spin.substring(0,aperta3);				
							
							
						}
						
 						spin=spin.replace("***","");
 			       
          	//			System.out.println("Spin off: "+spin);
        			
        			
        		}
        		
        		if (nextStr.contains("spoofed in")){
        			
        				secondo+=spoofed+"***";
        				res = nextStr.split("spoofed in");
        				secondo +=res[1];
        				
        				secondo=secondo.replace("null","");
 			
 						sec = secondo.substring(0,secondo.length()-1);
 			
						spoofed=sec.replace("'","");
						
						if (spoofed.contains("(")){
							
							int aperta4=spoofed.indexOf("(");
							
							spoofed=spoofed.substring(0,aperta4);				
							
							
						}
						
 						spoofed=spoofed.replace("***","");
 			       
          		//		System.out.println("Spoofed in: "+spoofed);
        			
        			
        		}
 			
 				if (nextStr.contains("version of")){
        				
        				secondo+=version+"***";
        				res = nextStr.split("version of");
        				secondo +=res[1];
        				
        				secondo=secondo.replace("null","");
 			
 						sec = secondo.substring(0,secondo.length()-1);
 			
						version=sec.replace("'","");
						
						if (version.contains("(")){
							
							int aperta5=version.indexOf("(");
							
							version=version.substring(0,aperta5);				
							
							
						}
						
						version=version.replace("***","");
						
 			
 			       
          		//		System.out.println("Version of: "+version);
        			
        			
        		}
 		    
 		    	if (nextStr.contains("followed by")){
        			
        				secondo+=followed+"***";
        				res = nextStr.split("followed by");
        				secondo +=res[1];
        				
        				secondo=secondo.replace("null","");
 			
 						sec = secondo.substring(0,secondo.length()-1);
 			
						followed=sec.replace("'","");
						
						if (followed.contains("(")){
							
							int aperta6=followed.indexOf("(");
							
							followed=followed.substring(0,aperta6);				
							
							
						}
						
						followed=followed.replace("***","");
						
 			
 			       
          		//		System.out.println("Followed by: "+followed);
        			
        			
        		}
 				
 		    	if (nextStr.contains("features")){
        				
        				secondo+=features+"***";
        				res = nextStr.split("features");
        				secondo +=res[1];
        				
        				secondo=secondo.replace("null","");
 			
 						sec = secondo.substring(0,secondo.length()-1);
 			
						features=sec.replace("'","");
						
						if (features.contains("(")){
							
							int aperta7=features.indexOf("(");
							
							features=features.substring(0,aperta7);				
							
							
						}
						
						features=features.replace("***","");
 			
 			       
          		//		System.out.println("Features: "+features);
        			
        			
        		}
      		 		 
      		 	if (nextStr.contains("remake of")){
        				
        				secondo+=remake+"***";
        				res = nextStr.split("remake of");
        				secondo +=res[1];
        				
        				secondo=secondo.replace("null","");
 			
 						sec = secondo.substring(0,secondo.length()-1);
 			
						remake=sec.replace("'","");
						
						if (remake.contains("(")){
							
							int aperta8=remake.indexOf("(");
							
							remake=remake.substring(0,aperta8);				
							
							
						}
						
						remake=remake.replace("***","");
						
 			
 			       
          		//		System.out.println("Remake of: "+remake);
        			
        			
        		}	 
      		 	
      			if (nextStr.contains("follows")){
        			
        				secondo+=follows+"***";
        				res = nextStr.split("follows");
        				secondo +=res[1];
        				
        				secondo=secondo.replace("null","");
 			
 						sec = secondo.substring(0,secondo.length()-1);
 			
						follows=sec.replace("'","");
 						
 						if (follows.contains("(")){
							
							int aperta9=follows.indexOf("(");
							
							follows=follows.substring(0,aperta9);				
							
							
						}
						
						follows=follows.replace("***","");
						
 			       
          			//	System.out.println("Follows: "+follows);
        			
        			
        		}	 
      		 		 	
      			if (nextStr.contains("references")){
        				
        				secondo+=references+"***";
        				res = nextStr.split("references");
        				secondo +=res[1];
        				
        				secondo=secondo.replace("null","");
 			
 						sec = secondo.substring(0,secondo.length()-1);
 			
						references=sec.replace("'","");
						
						if (references.contains("(")){
							
							int aperta10=references.indexOf("(");
							
							references=references.substring(0,aperta10);
											
							
							
						}
						
 						references=references.replace("***","");
 			       
          			//	System.out.println("References: "+references);
        			
        			
        			}	 
        			
        	secondo="";
        	/*		
        	else {
        		 nextStr=filebuf.readLine();
        		
        	}		
      	*/	 		 		 	
      		 
 		}
 	  
 	  
 	  
 	  	else  {
 	  	
 	  	primo= nextStr;
 	  	
 	  	if (primo.contains("(")){
				int aperta11=primo.indexOf("(");
				int chiusa=primo.indexOf(")");
				
				year=primo.substring(aperta11+1,chiusa);
				
				primo=primo.substring(0,aperta11);				
							
							
		}
		else {
			
			year =null;
		}
						
 	  	//System.out.println(primo);
 	  	
 	  	
 	 	}	
 		nextStr=filebuf.readLine(); 	
 		
 		}
 		
 	  
 	
 	
 		}
 	catch(NullPointerException n){
       		System.out.println("errore nullpointer\n");
       		
       	}
     
     	/*
     	System.out.println("*******************");
 	 	System.out.println(primo);
 		System.out.println(featured_in);
 		System.out.println(referenced_in);
 		System.out.println(spin);
 		System.out.println(spoofed);
 		System.out.println(version);
 		System.out.println(followed);
 		System.out.println(features);
 		System.out.println(remake);
 		System.out.println(follows);
 		System.out.println(references); 	
 		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+"','"+featured_in+"','"+referenced_in+"','"+spin+"','"+spoofed+"','"+version+"','"+followed+"','"+follows+"','"+features+"','"+remake+"','"+references+"'),\n");
        out2.append("('"+id+"','"+movieid+"'),\n");
              
   		
   		id++;
   
 		
	    primo = null;
	 	secondo =null;
	 	featured_in =null;
	    referenced_in =null;
	 	spin =null;
	 	spoofed =null;
	 	version =null;
	 	followed =null;
	 	features =null;
	 	remake =null;
	    follows =null;
	 	references=null; 	 
	 	
	   nextStr=filebuf.readLine();
        	 
	    	 
	   }
	    	 	
	    	 	filebuf.close();  // chiude il file	
 				out1.close();
 				out2.close();
 			
 		}
        
        
}