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


public class aka{
		 
	
	public static void main(String[]args) throws Exception{
	
	 	String primo = null;
	 	String secondo =null;
	 	String sec = null;
	 	String stri = null;
		String[] res;
		String[] arr=null;
		String[] arr2=null;
		String nome=null;
		String cognome=null;
		String akanome=null;
		String akacognome=null;
		int attoreid=0;
		
		FileWriter fi1 = new FileWriter("G:\\Documents and Settings\\cristiano\\Desktop\\tesina seminario\\tesina\\finalScripts\\akanames.sql");
		PrintWriter out1=new PrintWriter(fi1);
		FileWriter fi2 = new FileWriter("G:\\Documents and Settings\\cristiano\\Desktop\\tesina seminario\\tesina\\finalScripts\\actorsAka.sql");
		PrintWriter out2=new PrintWriter(fi2);
		
		
		
		FileReader f = new FileReader("G:\\Documents and Settings\\cristiano\\Desktop\\tesina seminario\\tesina\\file IMDB\\aka-names.txt");
        BufferedReader filebuf = new BufferedReader(f); 
        
        
        out1.println("INSERT IGNORE INTO akaname VALUES\n");
        out2.println("INSERT IGNORE INTO actorsAka 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)
           {
           	   e.printStackTrace();
               System.err.println ("Cannot connect to database server");
           }
          
           
    	        
        String nextStr;
        
        nextStr=filebuf.readLine();
       
       		
      	while (!nextStr.equals("AKA NAMES LIST")){
        	nextStr=filebuf.readLine();
       	}
        	
       nextStr=filebuf.readLine();
       nextStr=filebuf.readLine();
       nextStr=filebuf.readLine();
       
       
      	while (nextStr!=null){
      	
      		try{
      		
      		      			
      		nextStr = nextStr.replace("'","");
      		 	
      			
      			
      			if(nextStr.equals("")){
      				
      				attoreid++;
      				
      				nextStr=filebuf.readLine();
      			}
      			
      		
      			
      				if (!nextStr.startsWith("  ")) {
      				
      				primo=nextStr;
      				primo=primo.replace("'","");
      				primo=primo.replace("\t","");
      			
      				int inizio2 = primo.indexOf(",");
      				
      				if (primo.contains(",")){
      					
      					cognome = primo.substring(0,inizio2);
      					
      					nome = primo.substring(inizio2+2);
      					
      					
      				}
      				
					else {
						
						cognome=primo;
						nome=null;
						
						
					}   
					
					
				  //out3.append("('"+attoreid+"','"+nome+"','"+cognome+"',null),\n");	
 			   		
 			   		/*
 			   		System.out.println(cognome);
 	    			System.out.println(nome);
 	    		 	*/
 	    		 	
 	 		
      				nextStr=filebuf.readLine();
      				
      			}
      			
      			
      			
      			if (nextStr.startsWith("  ")){
      				
      				
      				
      				secondo=nextStr;
      				secondo=secondo.replace("'","");
      				secondo=secondo.replace("\t","");
      				int inizio=secondo.indexOf(",");
      				int fine=secondo.indexOf(")");      					
      				
      				if (secondo.contains(",")){
      				
      				
      				akacognome = secondo.substring(8,inizio);
      				akanome = secondo.substring(inizio+2,fine);
      				
      				}
      				
      				else {
      					akanome=null;
      					akacognome=secondo.substring(8,fine);	
      					
      				}
      				
      					
      			
     				}
      			
      	
 			 
 			 	  
 	  
 	  
 	 
 	  	
 	  	
 	 	
 		 	
		/*
 	  	System.out.println(akacognome);
 	    System.out.println(akanome);
 	    System.out.println("****************");
 	 	*/ 	  
 				
				
				try{
     	  		
				Statement s = conn.createStatement();
  				
  				rs=s.executeQuery("SELECT id FROM actor WHERE name LIKE'%"+nome+"%' and surname LIKE'%"+cognome+"'%");
         				
  			 	rs.last();
  			 	
  			 	
  			    attoreid =(Integer)rs.getObject(1);
  			 	}
  			 	
  			 	catch(Exception e){
  			 		
  			 	}
				
				
				out1.append("('"+akanome+"','"+akacognome+"'),\n");   
				out2.append("('"+attoreid+"','"+akanome+"','"+akacognome+"'),\n");   
		
				
 		
 		
 		
 		
 		
 				nextStr=filebuf.readLine();
 		}
 		catch(NullPointerException e){
 			break;
 			
 		}
	    	 
	   }
	   				System.out.println("fine parsing");
	    	 		filebuf.close();  // chiude il file	
	    	 		out1.close();
	    	 		out2.close();
	    	 	
	    	 		
	    	 		     
 		}
        
        
}