//===========================================================================
 /*
    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.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.Iterator;


public class SearchCinemaDAO
{
 
  public static ArrayList<String> getHierarchy(Connection c )
  {

	  Statement stmt = null;
	  ArrayList<String> hierarchy = new ArrayList<String>();
	  try 
	  {

		  stmt = c.createStatement();
	      ResultSet rs = stmt.executeQuery( "SELECT * FROM JSON_INFO where JSON_ID = 1;" );
	      while ( rs.next() ) 
	      {
	         String  name = rs.getString("name_pattern"); 
	         parseNamePattern(name, hierarchy);
	      }
	      rs.close();
	      stmt.close();

	    } 
	  catch ( Exception e ) 
	  {
	      System.err.println( e.getClass().getName() + ": " + e.getMessage() );
	      System.exit(0);
	    }
	  return hierarchy;
  }
  
  
  
  
  public static void parseNamePattern(String str, ArrayList<String> hierarchy)
  {
		String[] strs = str.split("[^a-zA-Z']+");

		
		for(int i = 1; i< strs.length - 1; i++)
		{
			hierarchy.add(strs[i]);
		}
  }
  
  
 
    
  public static String searchCinema(String query)
  {
	  
	  ArrayList<String> hierarchy = new ArrayList<String>();
	  List<List<String>> jsonList = new ArrayList<List<String>>();
	  ArrayList<String> tempList = new ArrayList<String>();
	  Connection c = null;
	  String namePattern = null;
	  try 
	  {
		  System.out.println("inside searchcinemadao");
	      Class.forName("org.sqlite.JDBC");
	      Properties prop = new Properties();
		    prop.load(SearchCinemaDAO.class.getResourceAsStream("/appProperties.properties"));
		    c = DriverManager.getConnection(prop.getProperty("dbname"));
	     
	      hierarchy = getHierarchy(c);
	      namePattern = getNamePattern(c);
	      
	    } 
	    catch ( Exception e ) 
	   {
	      System.err.println( e.getClass().getName() + ": " + e.getMessage() );
	      System.exit(0);
	   }

	  String[] strs = query.split("and");
  
	 
	  Iterator<String> hierarchyIterator = hierarchy.iterator();
	  while (hierarchyIterator.hasNext()) {
			boolean found = false;
			String temp = hierarchyIterator.next();
			for(int i = 0; i< strs.length ; i++)
			{

				if((strs[i]).contains(temp))
				{
					found = true;
					if(strs[i].contains("between"))
					{
						String queryCondition= strs[i].replaceAll("between", "");
						queryCondition= queryCondition.replaceAll(temp, "");
						System.out.println("Query condition: "+queryCondition);
						tempList=fetchRangeValues(c, temp, queryCondition);
					}
					else 
					{
						if(strs[i].contains("like"))
						{
							String queryCondition= strs[i].replaceAll("like", "");
							queryCondition= queryCondition.replaceAll(temp, "");
							System.out.println("Query condition: "+queryCondition);
							tempList=fetchSimilarValues(c, temp, queryCondition);
						}
						else
						{
							String queryCondition= strs[i].replaceAll("[^0-9.><=-]", "");
							System.out.println("Query condition: "+queryCondition);
							tempList=fetchQueryValues(c, temp, queryCondition);
						}
					}

					if(tempList!=null)
						jsonList.add(tempList);
					else
						return null;
				}
				
			}
			if(!found)
			{
				tempList = fetchArgumentValues(c, temp);
				if(tempList!=null)
					jsonList.add(tempList);
				else
					return null;
			}
		}
		return prepareOutputJSON(jsonList, namePattern);
  }
  
  public static void main(String args[])
  {
	
	 // parseJSON();
	  searchCinema("Theta=39");
  }
  
 
  
  public static ArrayList<String> fetchQueryValues(Connection c, String argumentString, String queryString )
  {

	  Statement stmt = null;
	  ArrayList<String> queryValues=new ArrayList<String>();
	  int argument_id=999;
	  String arg_value = "999";
	  try 
	    {
		      stmt = c.createStatement();
		      ResultSet rs = stmt.executeQuery( "SELECT ARGUMENT_ID FROM ARGUMENT WHERE ARGUMENT_NAME = '"+ argumentString+"' and JSON_ID=1");
		      while ( rs.next() ) 
		      {
		         argument_id = rs.getInt("argument_id");
		      }
		      stmt = c.createStatement();
		      String sql=new String("SELECT ARGUMENT_VAL FROM ARGUMENT_VALUE WHERE ARGUMENT_ID ="+ argument_id+" AND cast(ARGUMENT_VAL as REAL) "+queryString);
		      rs = stmt.executeQuery( sql);
		  //    System.out.println(sql);
		      while ( rs.next() ) 
		      {
		    	 arg_value = rs.getString("ARGUMENT_VAL");
		               
		         queryValues.add("\""+argumentString+"\":\""+arg_value+""
			         		+ "\",");
	
			         
		      }
		   

		      if(arg_value.equalsIgnoreCase("999") || argument_id==999)
		    	  return null;
		      rs.close();
		      stmt.close();
	    } 
	    catch ( Exception e ) 
	    {
	      System.err.println( e.getClass().getName() + ": " + e.getMessage() );
	      System.exit(0);
	    }
	  	return queryValues;
  }
  
  
  
  public static ArrayList<String> fetchRangeValues(Connection c, String argumentString, String queryString )
  {

	  Statement stmt = null;
	  ArrayList<String> queryValues=new ArrayList<String>();
	  int argument_id=0;
	  String arg_value = "0";
	  try 
	    {
		      stmt = c.createStatement();
		      ResultSet rs = stmt.executeQuery( "SELECT ARGUMENT_ID FROM ARGUMENT WHERE ARGUMENT_NAME = '"+ argumentString+"' and JSON_ID=1");
		      while ( rs.next() ) 
		      {
		         argument_id = rs.getInt("argument_id");
		      }
		      stmt = c.createStatement();
		      System.out.println("SELECT ARGUMENT_VAL FROM ARGUMENT_VALUE WHERE ARGUMENT_ID ="+ argument_id+" AND cast(ARGUMENT_VAL as REAL) between "+queryString);
		      rs = stmt.executeQuery( "SELECT ARGUMENT_VAL FROM ARGUMENT_VALUE WHERE ARGUMENT_ID ="+ argument_id+" AND cast(ARGUMENT_VAL as REAL) between "+queryString);
		    
		      while ( rs.next() ) 
		      {
		        arg_value = rs.getString("ARGUMENT_VAL");
		               
		         queryValues.add("\""+argumentString+"\":\""+arg_value+""
			         		+ "\",");
			         
		      }
		   
		      if(arg_value.equalsIgnoreCase("0") || argument_id==0)
		    	  return null;
		      rs.close();
		      stmt.close();
	    } 
	    catch ( Exception e ) 
	    {
	      System.err.println( e.getClass().getName() + ": " + e.getMessage() );
	      System.exit(0);
	    }
	  	return queryValues;
  }
  
  public static ArrayList<String> fetchSimilarValues(Connection c, String argumentString, String queryString )
  {

	  Statement stmt = null;
	  ArrayList<String> queryValues=new ArrayList<String>();
	  int argument_id=0;
	  String arg_value = "0";
	  try 
	    {
		      stmt = c.createStatement();
		      ResultSet rs = stmt.executeQuery( "SELECT ARGUMENT_ID FROM ARGUMENT WHERE ARGUMENT_NAME = '"+ argumentString+"' and JSON_ID=1");
		      while ( rs.next() ) 
		      {
		         argument_id = rs.getInt("argument_id");
		      }
		      stmt = c.createStatement();
		      System.out.println("SELECT ARGUMENT_VAL FROM ARGUMENT_VALUE WHERE ARGUMENT_ID ="+ argument_id+" AND ARGUMENT_VAL like "+queryString);
		      rs = stmt.executeQuery( "SELECT ARGUMENT_VAL FROM ARGUMENT_VALUE WHERE ARGUMENT_ID ="+ argument_id+"  AND ARGUMENT_VAL like "+queryString);
		    
		      while ( rs.next() ) 
		      {
		        arg_value = rs.getString("ARGUMENT_VAL");
		               
		         queryValues.add("\""+argumentString+"\":\""+arg_value+""
			         		+ "\",");
			         
		      }
		   
		      if(arg_value.equalsIgnoreCase("0") || argument_id==0)
		    	  return null;
		      rs.close();
		      stmt.close();
	    } 
	    catch ( Exception e ) 
	    {
	      System.err.println( e.getClass().getName() + ": " + e.getMessage() );
	      System.exit(0);
	    }
	  	return queryValues;
  }
  
  
  public static ArrayList<String> fetchArgumentValues(Connection c, String argumentString )
  {

	  Statement stmt = null;
	  ArrayList<String> argumentValues = new ArrayList<String>();
	  int argument_id=999;
	  String arg_value= "999";
	  try 
	    {
		      stmt = c.createStatement();
		      ResultSet rs = stmt.executeQuery( "SELECT ARGUMENT_ID FROM ARGUMENT WHERE ARGUMENT_NAME = '"+ argumentString+"' and JSON_ID=1");
		      while ( rs.next() ) 
		      {
		         argument_id = rs.getInt("argument_id");
		      }
		      stmt = c.createStatement();
		      rs = stmt.executeQuery( "SELECT ARGUMENT_VAL FROM ARGUMENT_VALUE WHERE ARGUMENT_ID ="+ argument_id);
		      while ( rs.next() ) 
		      {
		         arg_value = rs.getString("ARGUMENT_VAL");
		         argumentValues.add("\""+argumentString+"\":\""+arg_value+""
		         		+ "\",");
		         
		      }
		      
		      if(arg_value.equalsIgnoreCase("999") || argument_id==999)
		    	  return null;
		      rs.close();
		      stmt.close();
	    } 
	    catch ( Exception e ) 
	    {
	      System.err.println( e.getClass().getName() + ": " + e.getMessage() );
	      System.exit(0);
	    }
	    return argumentValues;
  }
  
  
  public static String prepareOutputJSON(List<List<String>> jsonList, String namePattern)
  {
	  StringBuffer outputJSON = new StringBuffer("[");
	  System.out.println("JSONLIST size : "+jsonList.size());
	  System.out.println("JSONLIST : "+jsonList);
	  
	  System.out.println("Generating output JSON....\n");

	  outputJSON=outputJSON.append(generate(jsonList,"", new StringBuffer(""), namePattern));

	  outputJSON.deleteCharAt(outputJSON.length()-1);
	  outputJSON.append("]");
	 System.out.println(outputJSON);
	  return outputJSON.toString();
  }
  
  
  public static String generate(List<List<String>> outerList, String outPut, StringBuffer outputJSON, String namePattern) 
  {
      List<String> list = outerList.get(0);

      for(String str : list) {
          List<List<String>> newOuter = new ArrayList<List<String>>(outerList);
          newOuter.remove(list);

          if(outerList.size() > 1) 
          {
              generate(newOuter, outPut+str,outputJSON, namePattern);
           } 
          else 
          {
        	outputJSON.append("{"+outPut+str);
        	String imageLoc =   new String(outPut+str);
        	String[] pairs = imageLoc.split(", *(?![^\\[\\]]*\\])");


			StringBuffer imgSrc = new StringBuffer(namePattern.replaceAll("\\{.*?}", "?"));
 			for (String pair : pairs) 
 			{
 			    String[] parts = pair.split(":");
 			    System.out.println(parts[1].toString().replaceAll("\"",""));

 			    int index = imgSrc.indexOf("?");
 			    imgSrc = imgSrc.replace(index, index+1, parts[1].toString().replaceAll("\"",""));
 			    
 			}
 			
	        outputJSON.append("\"imgSrc\""+ " : \""+imgSrc+"\"");
        	outputJSON.append("},") ;

           }
      }
      return outputJSON.toString();
  }
  
 
  
  
  public static String getNamePattern(Connection c )
  {

	  Statement stmt = null;
	  String  namePattern = null;
	  try 
	  {

		  stmt = c.createStatement();
	      ResultSet rs = stmt.executeQuery( "SELECT * FROM JSON_INFO where JSON_ID = 1;" );
	      while ( rs.next() ) 
	      {
	         namePattern = rs.getString("name_pattern"); 

	      }
	      rs.close();
	      stmt.close();

	    } 
	  catch ( Exception e ) 
	  {
	      System.err.println( e.getClass().getName() + ": " + e.getMessage() );
	      System.exit(0);
	    }
	  return namePattern;

  }
  
 
}


