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

public class soundtracks {
	
	
	public static void main (String[] args) throws Exception
       {
       	
       	
       	String primo="";
       	String secondo="";
     String year=null;
int movieid=0;
        String writer=null;
        String composer=null;
        String musician=null; 
           	
         FileWriter fi1 = new FileWriter("G:\\Documents and Settings\\cristiano\\Desktop\\tesina seminario\\tesina\\finalScripts\\soundtrack.sql");
		PrintWriter out1=new PrintWriter(fi1);
		FileWriter fi2 = new FileWriter("G:\\Documents and Settings\\cristiano\\Desktop\\tesina seminario\\tesina\\finalScripts\\tracklist.sql");
		PrintWriter out2=new PrintWriter(fi2);
	
         
       	FileReader f = new FileReader("G:\\Documents and Settings\\cristiano\\Desktop\\tesina seminario\\tesina\\file IMDB\\soundtracks.txt");
        BufferedReader filebuf = new BufferedReader(f); 
     
      	out1.println("INSERT IGNORE INTO soundtrack VALUES\n");
        out2.println("INSERT IGNORE INTO tracklist 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("SOUNDTRACKS LIST")){
			nextStr = filebuf.readLine();
        }	
        
       	 nextStr = filebuf.readLine();
         nextStr = filebuf.readLine();
         nextStr = filebuf.readLine();
         nextStr = filebuf.readLine();
         nextStr = filebuf.readLine();
         
          
        
        
         while (nextStr!=null){
        	nextStr = nextStr.replace("'","");
       		
       		if (nextStr.startsWith("#")){
       			
       			primo=nextStr;
       			primo = primo.replace("#","");
       				primo=primo.substring(1);
       
       			nextStr = filebuf.readLine();
       			nextStr = nextStr.replace("'","");
       		
       		}
       		
       		
       		
       		if (nextStr.startsWith("-")){
       				secondo=nextStr;
       				secondo=secondo.substring(1);
       				secondo=secondo.replace("#","");
       				secondo=secondo.substring(1);     
       				nextStr = filebuf.readLine();
       				nextStr = nextStr.replace("'","");
       		
       		}
       		
       		if (nextStr.startsWith("  Written by")){
       			
       			writer=nextStr;
       			writer = writer.substring(13);
       			writer=writer.replace("'","");
       			nextStr = filebuf.readLine();
       			nextStr = nextStr.replace("'","");
       		
       		}
       		
       		
       		if (nextStr.startsWith("  Composed by")){
       			
       			composer=nextStr;
       			composer = composer.substring(14);
       			composer=composer.replace("'","");
       			nextStr = filebuf.readLine();
       			nextStr = nextStr.replace("'","");
       		
       		}	
       		
       		if (nextStr.startsWith("  by")){
       			
       			musician=nextStr;
       			musician = musician.substring(5);
       			musician=musician.replace("'","");
       			nextStr = filebuf.readLine();
       			nextStr = nextStr.replace("'","");
       		
       		}
       	
       		
       		
     	if (primo.contains("(")){
        	int chiusa=primo.indexOf(")");
 	  		int indice = primo.indexOf("(");
 	  		
 	  		year=primo.substring(indice+1,chiusa);
 	  		
 	  		primo = primo.substring(0,indice);
 	  		}
 	  	
 	  	else {
 	  		year=null;
 	  	}
 	  		
       		
       	/*
       	System.out.println(primo);	
       	System.out.println(writer);	
       	System.out.println(composer);	
       	System.out.println(musician);	
       	*/
        //System.out.println(secondo);	
       	//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("('"+secondo+"','"+writer+"','"+composer+"','"+musician+"'),\n");
    			out2.append("('"+secondo+"','"+movieid+"'),\n");
    			
    			
    			
  		  
	   nextStr=filebuf.readLine();
        	 
	    	 
	   }
	    	 	
	    	 	filebuf.close();  // chiude il file	
 					out1.close();
 					out2.close();
 				
 		}
        
        
}