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

public class certificates {
	
	
	public static void main (String[] args) throws Exception
       {
       	
       	
       	
       	String primo=null;
       	String secondo = null;
		String terzo=null;
		String quarto=null;
       	String res=null;
       	String year=null;
       	String[]arr=null;
       	int movieid=0;
       	String country=null;
       	String movie=null;
       	String type2=null;
       	String type=null;
       	int aperta=0;
       	int chiusa=0;

   	
		FileWriter fi1 = new FileWriter("G:\\Documents and Settings\\cristiano\\Desktop\\tesina seminario\\tesina\\finalScripts\\cert.sql");
		PrintWriter out1=new PrintWriter(fi1);
		FileWriter fi2 = new FileWriter("G:\\Documents and Settings\\cristiano\\Desktop\\tesina seminario\\tesina\\finalScripts\\countryfromcert.sql");
		PrintWriter out2=new PrintWriter(fi2);
		
       	FileReader f = new FileReader("G:\\Documents and Settings\\cristiano\\Desktop\\tesina seminario\\tesina\\file IMDB\\certificates.txt");
        BufferedReader filebuf = new BufferedReader(f); 
      
      
      
      	out1.println("INSERT IGNORE INTO certificate VALUES\n");
      	out2.println("INSERT IGNORE INTO country 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("CERTIFICATES LIST")){
		nextStr = filebuf.readLine();
        }	
		
		
		nextStr = filebuf.readLine();
        nextStr = filebuf.readLine();
        
        
       
			
     	while (nextStr!=null){
     		
     		 if (nextStr.equals("--------------------------------------------------------------------------------")){
        	System.out.println("Certs inseriti nel db");
       		break;
          }
        
        	nextStr = nextStr.replace("'","");
        
        
        	res=nextStr;
        	
        	int tab=res.indexOf("\t");
        	
        	primo=res.substring(0,tab);
        	secondo=res.substring(tab);
        	
        	      
            if (primo.startsWith("(")){
        		int tonda=primo.indexOf(")");
        		primo=primo.substring(tonda+2);
        	
       		 }
       		 
       		 
        	
        
			 if(primo.contains("(")){
			  
        	
       		  int inizio = primo.indexOf("(");
       		  int fine = primo.indexOf(")");
        	
        	movie=primo.substring(0,inizio);
        	year=primo.substring(inizio+1,fine);
        	
     		   }
        	
        	else {
        	
        	movie=primo;
        	year=null;
        	
        }
        
        int duepunti=secondo.indexOf(":");
        
         
        country = secondo.substring(0,duepunti);
        
        country=country.replace("\t","");
        
        type2=secondo.substring(duepunti+1);
        
        
        if (type2.contains("\t")){
        
        int tab2 = type2.indexOf("\t");
        
        type = type2.substring(0,tab2);
        }
        
        else {
        	
        	type=type2;
        }
        
        /*
        System.out.println(movie);
        System.out.println(year);
        System.out.println(country);
      	System.out.println(type);
        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("('"+country+"'),\n");
      		out1.append("('"+country+"','"+movieid+"','"+type+"'),\n");
      	
      	
      
       
       nextStr = filebuf.readLine();// legge una riga del file 
    
           }
           
       
       filebuf.close();  // chiude il file    
        out1.close();  
        out2.close();  
        
         
            
           
       }
		
		
		
	}