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

public class prodCompanies {
	
	
	public static void main (String[] args) throws Exception
       {
       	
       	
       	
       	String primo,secondo;
		secondo = null;
		String country=null;
       	String[]res;
       	String year=null;
       	int movieid=0;

       	FileWriter fi1 = new FileWriter("G:\\Documents and Settings\\cristiano\\Desktop\\tesina seminario\\tesina\\finalScripts\\countryfromprodcomp.sql");
		PrintWriter out1=new PrintWriter(fi1);
		FileWriter fi2 = new FileWriter("G:\\Documents and Settings\\cristiano\\Desktop\\tesina seminario\\tesina\\finalScripts\\prodcompany.sql");
		PrintWriter out2=new PrintWriter(fi2);
		FileWriter fi4 = new FileWriter("G:\\Documents and Settings\\cristiano\\Desktop\\tesina seminario\\tesina\\finalScripts\\prodcomp2movie.sql");
		PrintWriter out4=new PrintWriter(fi4);
		FileWriter fi5 = new FileWriter("G:\\Documents and Settings\\cristiano\\Desktop\\tesina seminario\\tesina\\finalScripts\\prodcomp2country.sql");
		PrintWriter out5=new PrintWriter(fi5);
		
       	
       	   
       	FileReader f = new FileReader("G:\\Documents and Settings\\cristiano\\Desktop\\tesina seminario\\tesina\\file IMDB\\production-companies.txt");
        BufferedReader filebuf = new BufferedReader(f); 
      	
      	out1.println("INSERT IGNORE INTO country VALUES\n");
        out2.println("INSERT IGNORE INTO prodcompany VALUES\n");
       	out4.println("INSERT IGNORE INTO prodcompany2movie VALUES\n");   
       	out5.println("INSERT IGNORE INTO prodcompany2country 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("PRODUCTION COMPANIES LIST")){
		nextStr = filebuf.readLine();
        }	
		
		
		nextStr = filebuf.readLine();
        nextStr = filebuf.readLine();
        nextStr = filebuf.readLine();
        
       
        	
			
     	while (nextStr!=null){
     		
     		 if (nextStr.equals("--------------------------------------------------------------------------------")){
        	System.out.println("Prodcompanies inseriti nel db");
        	break;
        }
        
        	
         nextStr = nextStr.replace("'","");
        	
         res=nextStr.split("\t\t");
        
          for (int x=1; x<res.length; x++){
         
          if (!res[x].equals("\t")){
          	secondo= res[x];
          	secondo = secondo.replace("\t","");
          	
          	if (secondo.contains("[")){
          		
          		int inizio =secondo.indexOf("[");
          		int fine=secondo.indexOf("]");
          		country=secondo.substring(inizio+1,fine);
          		
          		secondo = secondo.substring(0,inizio);
          		
          	}
          	else {
          		
          		country=null;
          		
          	}
          	
          	
          }
        
      	   }
      	   
        primo=res[0];
          if (primo.contains("(")){
          	int fine2=primo.indexOf(")");
    		int inizio2=primo.indexOf("(");
    		year = primo.substring(inizio2+1,fine2);
    		primo=primo.substring(0,inizio2);     
    	}
    	
    	else {
    		
    		year=null;
    	}
        
        /*
        System.out.println(primo);
      	System.out.println(secondo);
      	System.out.println(country);
      	System.out.println(year);
      	
      	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("('"+country+"'),\n");
              	out2.append("('"+secondo+"'),\n");
              	out4.append("('"+secondo+"','"+movieid+"'),\n");
              	out5.append("('"+secondo+"','"+country+"'),\n");
              	
   				
        
       
       nextStr = filebuf.readLine();// legge una riga del file 
    
           }
           
       
       filebuf.close();  // chiude il file    
       out1.close();    
       out2.close();
       out4.close();
       out5.close();
            
       }
		
		
		
	}