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

public class prodDesigners {
	
	
	public static void main (String[] args) throws Exception
       {
       	
       	
       	
       	String primo,secondo;
       	String nome=null;
       	String cognome=null;
       	String[]arr=null;
       	primo = null;
       	secondo = null;
       	String[]res;
       	String nomecognome=null;
       	int movieid=0;
       	String year=null;
       	
       FileWriter fi1 = new FileWriter("G:\\Documents and Settings\\cristiano\\Desktop\\tesina seminario\\tesina\\finalScripts\\prodesigner.sql");
		PrintWriter out1=new PrintWriter(fi1);
		FileWriter fi2 = new FileWriter("G:\\Documents and Settings\\cristiano\\Desktop\\tesina seminario\\tesina\\finalScripts\\proddesignerby.sql");
		PrintWriter out2=new PrintWriter(fi2);
		  
       	FileReader f = new FileReader("G:\\Documents and Settings\\cristiano\\Desktop\\tesina seminario\\tesina\\file IMDB\\production-designers.txt");
        BufferedReader filebuf = new BufferedReader(f); 
      
      	out1.println("INSERT IGNORE INTO proddesigner VALUES\n");
        out2.println("INSERT IGNORE INTO proddesignby 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("THE PRODUCTION DESIGNERS LIST")) {
 		 	nextStr=filebuf.readLine();
  		}
   
   		nextStr = filebuf.readLine();
        nextStr = filebuf.readLine();
        nextStr = filebuf.readLine();
        nextStr = filebuf.readLine();
        nextStr = filebuf.readLine();
        
         					
       
        
   
      while (nextStr!=null){
      	try{
      	
      	
      	 if (nextStr.equals("SUBMITTING UPDATES")){
        	System.out.println("Proddesigners inseriti nel db");
        	break;
        }
        
        	
    		 nextStr = nextStr.replace("'"," ");
        	
    			 if(nextStr.equals("")){
      					nextStr=filebuf.readLine();
      			}
      			
      
      			
      			if (nextStr.startsWith("\t")){
      				
      				
      				secondo=nextStr;
      				secondo=secondo.replace("'","");
      				secondo=secondo.replace("\t","");
      				
      				if (secondo.contains("(")){
      					
      					int aperta=secondo.indexOf("(");
      					int chiusa=secondo.indexOf(")");
      					
      					year = secondo.substring(aperta+1,chiusa);
      					
      				 }
      				 
      				 else {
      				 	year=null;
      				 }
      					
      			
      		}	
      	
      			else {
      				
      				primo=nextStr;
      				primo=primo.replace("'","");
      				int tab=primo.indexOf("\t");
      				
      				nomecognome = primo.substring(0,tab);
      				
      				if (nomecognome.contains(",")){
      					
      					int virgola=nomecognome.indexOf(",");
      					
      					cognome = nomecognome.substring(0,virgola);
      					
      					nome= nomecognome.substring(virgola+2,tab);
      					
      							
      					
      				}
      				
      				else {
      					
      					nome = null;
      					cognome = nomecognome;
      					
      					
      				}
      				
      				out1.append("('"+nome+"','"+cognome+"'),\n");   	
			
      				
      				secondo=primo.substring(tab);
      				secondo=secondo.replace("'","");
      				secondo=secondo.replace("\t","");
      				
      				if (secondo.contains("(")){
      					
      					int aperta=secondo.indexOf("(");
      					int chiusa=secondo.indexOf(")");
      					
      					year = secondo.substring(aperta+1,chiusa);
      					
      				 }
      				 
      				 else {
      				 	year=null;
      				 }
      					
      				
       	}
      	
      	
      	
      	if (secondo.contains("[")){
    		int quadra=secondo.indexOf("[");
    		secondo=secondo.substring(0,quadra);     
   		 }
    
    	if (secondo.contains("(")){
    		int tonda=secondo.indexOf("(");	
    		secondo=secondo.substring(0,tonda);     
   		 }
      	
      	
      
      	
      	/*
 		System.out.println(nome);
 		System.out.println(cognome);
 		System.out.println(secondo);
 		System.out.println(year);
 	//	System.out.println(as);
 	//	System.out.println(attoreid);
 	//	System.out.println(movieid);
 		System.out.println("****************");
 		*/
    	try{
     	  		
				Statement s = conn.createStatement();
  				
  				rs=s.executeQuery("SELECT id FROM movies WHERE title='"+secondo+"' and year='"+year+"'");
         				
  			 	rs.last();
  			 	
  			 	
  			    movieid =(Integer)rs.getObject(1);
  			 	}
  			 	
  			 	catch(Exception e){
  			 		
  			 	}
     				
				
				out2.append("('"+movieid+"','"+nome+"','"+cognome+"'),\n");   	
		
 		    
 
    
       nextStr = filebuf.readLine();// legge una riga del file 
       }
       catch(Exception e){
       	break;
       }
       }
       
       
       filebuf.close();  // chiude il file    
       out1.close();   
       out2.close();
      
       }
		
		
		
	}