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


public class akatitle{
		 
	
	public static void main(String[]args) throws Exception{
	
		String primo=null;
		String secondo=null;
	 	String movie = null;
	 	String year =null;
	 	String akamovie = null;
	 	String stri = null;
		String[] res;
		int movieid=0;
	
		FileWriter fi2 = new FileWriter("G:\\Documents and Settings\\cristiano\\Desktop\\tesina seminario\\tesina\\finalScripts\\moviesaka.sql");
		PrintWriter out2=new PrintWriter(fi2);
		FileWriter fi3 = new FileWriter("G:\\Documents and Settings\\cristiano\\Desktop\\tesina seminario\\tesina\\finalScripts\\akatitles.sql");
		PrintWriter out3=new PrintWriter(fi3);
		
		
	
		FileReader f = new FileReader("G:\\Documents and Settings\\cristiano\\Desktop\\tesina seminario\\tesina\\file IMDB\\aka-titles.txt");
        BufferedReader filebuf = new BufferedReader(f); 
        
        
        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)
           
           {
           	   e.printStackTrace();
               System.err.println ("Cannot connect to database server");
           }
          
           
    
           
        
        out2.println("INSERT IGNORE INTO moviesAka VALUES\n");
        out3.println("INSERT IGNORE INTO akatitle VALUES\n");   
        
        
        String nextStr;
        
        nextStr=filebuf.readLine();
       
       		
      	while (!nextStr.equals("AKA TITLES LIST")){
        	nextStr=filebuf.readLine();
       		}
        nextStr=filebuf.readLine();
       	nextStr=filebuf.readLine();
       	nextStr=filebuf.readLine();
       	nextStr=filebuf.readLine();
       		
       
        
       
      	while (nextStr!=null){
      		try{
      		
      	
      		 if (nextStr.equals("-----------------------------------------------------------------------------")){
        	System.out.println("Akatitles inseriti nel db");
        	break;
        }
        
       
      		
      		nextStr = nextStr.replace("'","");
      		
      			if(nextStr.equals("")){
      				
      				movieid++;
      				
      				nextStr=filebuf.readLine();
      			}
      			
      			
      				if (!nextStr.startsWith("  ")) {
      				
      				primo=nextStr;
      				primo=primo.replace("'","");
      				primo=primo.replace("\t","");
      			
      				int inizio2 = primo.indexOf("(");
      				int fine2 = primo.indexOf(")");
      				
      				if (primo.contains("(")){
      					
      					movie = primo.substring(0,inizio2);
      					
      					year = primo.substring(inizio2+1,fine2);
      					
      					
      				}
      				
					else {
						
						movie=primo;
						year=null;
						
						
					}   
					
					
				 	
 			   		/*
 			   		System.out.println(movie);
 	    			System.out.println(year);
 	    		 	System.out.println("****************");
 	    		 	*/
 	 			nextStr=filebuf.readLine();
      				
      				
      			}
      			
      			
      			
      			if (nextStr.startsWith("  ")){
      				
      				
      				secondo=nextStr;
      				secondo=secondo.replace("'","");
      				secondo=secondo.replace("\t","");
      				secondo=secondo.substring(8);
      				
      				int inizio=secondo.indexOf("(");
      				int fine = secondo.indexOf(")");
      				
      				if (secondo.contains("(")){
      				
      				
      				akamovie = secondo.substring(0,inizio);
      				
      				}
      				
      				else {
      				
      				akamovie=secondo.substring(0,fine)	;
      				}
      				
      					
      			}
      			
      
      	
 			 		 	
 	  	/*
 	  	System.out.println(akamovie);
 	    System.out.println("****************");
 	 	*/
      
      		     	
      
      			try{
     	  		
				Statement s = conn.createStatement();
  				
  				rs=s.executeQuery("SELECT id FROM movies WHERE title='"+movie+"' and year='"+year+"'");
         				
  			 	rs.last();
  			 	
  			 	
  			    movieid =(Integer)rs.getObject(1);
  			 	}
  			 	
  			 	catch(Exception e){
  			 		
  			 	}
 	  
				
				out2.append("('"+akamovie+"','"+movieid+"'),\n");	
				out3.append("('"+akamovie+"'),\n");   	
 				
 			
 		
 		
 		
 		
 		 	
 		
 	 
	   nextStr=filebuf.readLine();
        }
        
        catch(NullPointerException e){
        	e.printStackTrace();
 			break;
 			
 		}	 
	    	 
	   }
	    	 	
	    	 	filebuf.close();  // chiude il file	
 				out2.close();
 				out3.close();
 				
 				     
 		}
        
        
}