
//===========================================================================
 /*
    This file is part of the Cinema Web Interface Code.
    Copyright (C) by Los Alamos National Lab.
    Author:    Uzma Shaikh
 //===========================================================================*/

package com.cinemawebservice;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.Iterator;
import java.util.Properties;

import org.json.simple.JSONArray;
import org.json.simple.JSONObject;
import org.json.simple.parser.JSONParser;

public class LoadJSON 
{
	 public static void parseJSON()
	  {
	    Connection c = null;
	    try 
	    {
		    Class.forName("org.sqlite.JDBC");
		
			Properties prop = new Properties();
		    prop.load(SearchCinemaDAO.class.getResourceAsStream("/appProperties.properties"));
		    c = DriverManager.getConnection(prop.getProperty("dbname"));
		      
	      
	    } catch ( Exception e ) {
	      System.err.println( e.getClass().getName() + ": " + e.getMessage() );
	      System.exit(0);
	    }
	   
	    System.out.println("Opened database successfully");
	    createTables(c);
	    loadJSONData(c);

	  }
	 public static void main(String args[])
	 {
		 parseJSON();
	 }
	  
	  
	  public static void createTables(Connection c)
	  {

	    Statement stmt = null;
	    try 
	    {
	     
	     stmt = c.createStatement();
	      
	     String sql = "DROP TABLE IF EXISTS JSON_INFO";
	      
	     stmt.execute(sql);
	     sql = "DROP TABLE IF EXISTS ARGUMENT";
	      
	     stmt.execute(sql);
	     sql = "DROP TABLE IF EXISTS ARGUMENT_VALUE";
	      
	      stmt.execute(sql);
	      
	      sql = "CREATE TABLE IF NOT EXISTS JSON_INFO " +
	                   "(JSON_ID INTEGER PRIMARY KEY AUTOINCREMENT," +
	                   " ROOT_LOCATION     TEXT    NOT NULL, " + 
	                   " NAME_PATTERN  TEXT     NOT NULL, " + 
	                   " METADATA   TEXT); "; 
	      stmt.executeUpdate(sql);
	      
	      sql = "CREATE TABLE IF NOT EXISTS ARGUMENT " +
	              "(ARGUMENT_ID INTEGER PRIMARY KEY AUTOINCREMENT,"
	              + "ARGUMENT_NAME TEXT NOT NULL," +
	    		  "DEFAULT_VALUE INTEGER,"+
	    		  "JSON_ID INTEGER,"+
	              "FOREIGN KEY(JSON_ID) REFERENCES JSON_INFO(JSON_ID)) ;"; 
	      stmt.executeUpdate(sql);
	      
	      sql = "CREATE TABLE IF NOT EXISTS ARGUMENT_VALUE " +
	              "(ARGUMENT_VAL TEXT NOT NULL," +
	    		  "ARGUMENT_ID INTEGER,"+
	              "FOREIGN KEY(ARGUMENT_ID) REFERENCES ARGUMENT(ARGUMENT_ID))"; 
	      stmt.executeUpdate(sql);
	      
	      stmt.close();

	    } catch ( Exception e ) {
	    	e.printStackTrace();
	      System.err.println( e.getClass().getName() + ": " + e.getMessage() );
	      System.exit(0);
	    }
	    System.out.println("Table created successfully");
	  }
	  
	  
	  public static void loadJSONData(Connection c)
	  {
		  	InputStream input = null;
	 
			Properties prop = new Properties();

			try 
			{
				prop.load(SearchCinemaDAO.class.getResourceAsStream("/appProperties.properties"));

				String fileName = prop.getProperty("cinemaRootDirectory");

				JSONParser parser = new JSONParser();
			   try 
			   {
			         	
			
						Object obj = parser.parse(new BufferedReader(new InputStreamReader(SearchCinemaDAO.class.getResourceAsStream("/info.json"))));

						Statement stmt = c.createStatement();
			            JSONObject jsonObject = (JSONObject) obj; 
			            JSONObject arguments = (JSONObject) jsonObject.get("arguments");
			            String namePattern = (String) jsonObject.get("name_pattern");
			            JSONObject metadata = (JSONObject) jsonObject.get("metadata");

			            
			            String sql = "INSERT INTO JSON_INFO  VALUES (1, \""+fileName+"\",\"" +namePattern+"\",\""+metadata.get("type").toString()+"\");"; 
			            System.out.println(sql);
			            stmt.executeUpdate(sql);
			            
			            for(Iterator iterator = arguments.keySet().iterator(); iterator.hasNext();) {
			                String key = (String) iterator.next();
			                JSONObject argumentValue = (JSONObject)arguments.get(key);
			                System.out.println(key+" values   " +argumentValue.toString());
			                
			                sql = "INSERT INTO ARGUMENT VALUES ((SELECT MAX(argument_id) FROM argument) + 1"+",\"" +argumentValue.get("label") +"\",'"+argumentValue.get("default")+"', (SELECT MIN(json_id) FROM JSON_INFO));"; 
			                System.out.println(sql);
			                stmt.executeUpdate(sql);
			                
			                JSONArray argValueList = (JSONArray) argumentValue.get("values");
			                for(int i=0; i< argValueList.size(); i++)
			                {
			                	Object val = argValueList.get(i);
			                	sql = "INSERT INTO ARGUMENT_VALUE VALUES ('"+val+"',(SELECT MAX(argument_id) FROM argument));"; 
					            System.out.println(sql);
					            stmt.executeUpdate(sql);
			                }
			                
			            }
			            stmt.close();
			        } catch (Exception e) {
			            e.printStackTrace();
			        }
				
			} 
			catch (IOException ex) 
			{
				ex.printStackTrace();
			} 
			finally 
			{
				if (input != null) 
				{
					try 
					{
						input.close();
						
					} 
					catch (IOException e) 
					{
						e.printStackTrace();
					}
				}
			}
			
			
	  }
}
