/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package eg.edu.alexu.csd.oop.db;

import java.lang.reflect.Array;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Scanner;
import java.util.StringTokenizer;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 *
 * @author TOSHIBA PC
 */
public class Sql implements Database {

    @Override
    public boolean executeStructureQuery(String query) throws SQLException {
        //throw new UnsupportedOperationException("Not supported yet.");
        //To change body of generated methods, choose Tools | Templates.

        String editedQuery;
        editedQuery=Sql.regexReplace(query);
         StringTokenizer words= new StringTokenizer(editedQuery, ",");

          ArrayList<String> ColumnNames =new  ArrayList<String>();
      ArrayList<String> ColumnTypes =new  ArrayList<String>();



     String FirstToken=null;


     FirstToken=words.nextToken();

     String SecondToken=words.nextToken();


String method=null;
     String TableName = null;


            if
        (FirstToken.equalsIgnoreCase("create")&&SecondToken.equalsIgnoreCase("table")&&words.hasMoreTokens())

     {


         String temp=null;
         method=FirstToken;

         TableName=words.nextToken();
    if
            (words.hasMoreTokens())
    {
         temp=words.nextToken();
         if
                 (!temp.equalsIgnoreCase("("))
         {

             return false;

         }

    }

        if
                (words.hasMoreTokens())
        {
             temp=words.nextToken();
        }



       while
    (words.hasMoreTokens()&&(!(temp.equalsIgnoreCase(")"))&&!(temp.equalsIgnoreCase(");"))))
    {
           System.out.println(temp);
    ColumnNames.add(temp);
    if
                (!words.hasMoreTokens())
        {
             return false;
        }
    temp=words.nextToken();
        System.out.println(temp);
        if
                (!(temp.equalsIgnoreCase("varchar"))&&!(temp.equalsIgnoreCase("int")))
        {
            return false;
        }

     ColumnTypes.add(temp);

       if
                (!words.hasMoreTokens())
        {
             return false;
        }
        temp=words.nextToken();
    }

       String temp2=null;
       if
               (words.hasMoreTokens())
       {
           temp2=words.nextToken();

       if
               ((temp.equalsIgnoreCase(")")&&temp2.equalsIgnoreCase(";")))
       {
           CheckCreate(ColumnNames, ColumnTypes, TableName, method);
           return true;
       }
       else
           return false;

       }

       else
       {
           if
                   (!temp.equalsIgnoreCase(");"))
               return false;
       }


         CheckCreate(ColumnNames, ColumnTypes, TableName, method);
        return true;


     }






        else if
         (FirstToken.equalsIgnoreCase("drop")&& SecondToken.equalsIgnoreCase("table")&&words.hasMoreTokens())
            {
                String temp=null;
                method=FirstToken;

                TableName=words.nextToken();

     if
             (words.hasMoreTokens()&&words.nextToken().equalsIgnoreCase(";"))
     {
         CheckDrop(TableName, method);
             return true;

     }

  char[] tableName=TableName.toCharArray();
     if
             (tableName[tableName.length-1]!=';')

     {

         CheckDrop(TableName, method);
     return false;

     }
     else
     {

TableName=TableName.replace(TableName.substring(TableName.length()-1), "");
     CheckDrop(TableName, method);
     }
            }


        return true;

    }

    @Override
    public Object[][] executeRetrievalQuery(String query) throws SQLException {
        //throw new UnsupportedOperationException("Not supported yet.");
        //To change body of generated methods, choose Tools | Templates.

          String editedQuery;
        editedQuery=Sql.regexReplace(query);
         StringTokenizer words= new StringTokenizer(editedQuery, ",");

          ArrayList<String> ColumnNames =new  ArrayList<String>();
      ArrayList<String> ColumnTypes =new  ArrayList<String>();


     String FirstToken=null;
     String temp2=null;


     FirstToken=words.nextToken();

     String SecondToken=words.nextToken();


     String method=null;
     String TableName = null;
      String conditionSelect=null;


        if
                    (FirstToken.equalsIgnoreCase("select")&&words.hasMoreElements())
            {

                method=FirstToken;
                String temp= SecondToken;


                while
                        (words.hasMoreTokens()&&!temp.equalsIgnoreCase("From"))
                {
                    ColumnNames.add(temp);
                    temp=words.nextToken();

                }

                  if
                        (!words.hasMoreTokens())
                {
                    System.out.println("not valid");
                }

                  else
                  {
                      temp=words.nextToken();
                       TableName=temp;
                  }

                if
                        (!words.hasMoreTokens())
                {
                     System.out.println("not valid1");
                }

                else
                {
                     if
                        (!words.nextToken().equalsIgnoreCase("where"))
                {
                     System.out.println("not valid2");
                }
                     else
                     {
                         if
                                 (words.hasMoreTokens())
                         {
                         conditionSelect =words.nextToken();

                         if
                                 (words.hasMoreTokens())
                         {
                             temp2=words.nextToken();
                             if
                                     (!temp2.equalsIgnoreCase(";"))
                             {
                             System.out.println("Not Valid3");

                             }


                         }

                         else
                         {

                              char[]  c = conditionSelect.toCharArray();

                              if
             (c[c.length-1]!=';')

     {

         System.out.println("Not valid4");


     }
     else
     {

conditionSelect=conditionSelect.replace(conditionSelect.substring(conditionSelect.length()-1), "");
         CheckSelect(ColumnNames, TableName, method, conditionSelect);
     }

                         }





                         }




                         else
                         {
                             System.out.println("not valid4");
                         }
                     }

                }


            }
         CheckSelect(ColumnNames, TableName, method, conditionSelect);
            Object object [][]=new Object[ColumnNames.size()][ColumnTypes.size()];
           // object[][]=[CloumNames][CloumTypes];

       // return object[CloumNames.toArray()][CloumTypes.toArray()];
       return object;


    }

    @Override
    public int executeUpdateQuery(String query) throws SQLException {
       // throw new UnsupportedOperationException("Not supported yet.");
       //To change body of generated methods, choose Tools | Templates.



        String editedQuery;
        editedQuery=Sql.regexReplace(query);
         StringTokenizer words= new StringTokenizer(editedQuery, ",");

          ArrayList<String> ColumnNames =new  ArrayList<String>();
      ArrayList<String> ColumnTypes =new  ArrayList<String>();
       ArrayList<String> ColumnValues =new  ArrayList<String>();



     String FirstToken=null;


     FirstToken=words.nextToken();

     String SecondToken=words.nextToken();


String method=null;
     String TableName = null;



        if
      (FirstToken.equalsIgnoreCase("insert")&&SecondToken.equalsIgnoreCase("into")&&words.hasMoreTokens())
            {

                method=FirstToken;
                TableName=words.nextToken();
                String temp=null;
                 if
            (words.hasMoreTokens())
    {
         temp=words.nextToken();
         if
                 (!temp.equalsIgnoreCase("("))
         {

            System.out.println("not valid1");
             return 0;

         }

    }
                 else
                 {
                     System.out.println("not valid2");
                     return 0;
                 }



                        if
            (words.hasMoreTokens())
    {
         temp=words.nextToken();
    }
                        else
                        {
                            System.out.println("not valid3");
                            return 0;
                        }
                 while
                      (words.hasMoreTokens()&&!temp.equalsIgnoreCase(")"))
                    {

                        ColumnNames.add(temp);
                        temp=words.nextToken();


                    }
                 if
                         (!words.hasMoreTokens())
                 {
                     System.out.println("not valid4");
                     return 0;
                 }
                 if
                         (!temp.equalsIgnoreCase(")"))
                 {

                     System.out.println("not valid5");
                     return 0;
                 }


                 if
                         (!words.nextToken().equalsIgnoreCase("values"))
                 {

                     System.out.println("not valid6");
                     return 0;
                 }


                   if
            (words.hasMoreTokens())
    {
         temp=words.nextToken();
         if
                 (!temp.equalsIgnoreCase("("))
         {
             System.out.println("not valid7");

             return 0;

         }



    }
                   else
         {
             System.out.println("not valid8");

           return 0;

         }


           if
                   (words.hasMoreTokens())

           {
               temp=words.nextToken();
           }
           else
           {
               System.out.println("not valid9");
               return 0;
           }


           while
                      (words.hasMoreTokens()&&!temp.equalsIgnoreCase(")"))
                    {

                        ColumnValues.add(temp);
                        temp=words.nextToken();
                        System.out.println(temp);

    }
     System.out.println(temp);

            String temp2=null;
       if
               (words.hasMoreTokens())
       {
           temp2=words.nextToken();

       if
               ((temp.equalsIgnoreCase(")")&&temp2.equalsIgnoreCase(";")))
       {
           CheckCreate(ColumnNames, ColumnValues, TableName, method);
           return 1;
       }
       else
       {
           System.out.println("not valid10");
           return 0;
       }
       }


       else{
           if
                   (!temp.equalsIgnoreCase(");"))
           {
               System.out.println("not valid11");
               return 0;
           }
       }

                CheckCreate(ColumnNames, ColumnValues, TableName, method);









            }


          else if
         (FirstToken.equalsIgnoreCase("delete")&&SecondToken.equalsIgnoreCase("from")&&words.hasMoreTokens())

            {
               method=FirstToken;
                String ThirdToken=null;
               TableName=words.nextToken();
               if
                       (words.hasMoreTokens())
               {
               ThirdToken=words.nextToken();
               }
               else
               {System.out.println("Not valid");
                   return 0;
                           }
               if
                       (!ThirdToken.equalsIgnoreCase("where"))
               {
                   System.out.println("Not valid");
               return 0;
               }


                    String condition=null;



                   if
                       (words.hasMoreTokens())
               {
               condition=words.nextToken();

                if
             (words.hasMoreTokens()&&words.nextToken().equalsIgnoreCase(";"))
     {
         CheckDelete(TableName, method,condition);
             return 1;

     }

                char[] c=condition.toCharArray();
     if
             (c[c.length-1]!=';')

     {

         System.out.println("Not valid");
     return 0;

     }
     else
     {

condition=condition.replace(condition.substring(condition.length()-1), "");
     CheckDelete(TableName, method,condition);
     }




               }
               else
                     System.out.println("Not valid");
                   return 0;




               }








       return 1;
    }





     public static void regexChecker(String theRegex ,String str2Check)
      {
          Pattern checkRegex =Pattern.compile(theRegex);
          Matcher regexMatcher = checkRegex.matcher(str2Check);

          while
                  (regexMatcher.find())
          {
          if
                  (regexMatcher.group().length()!=0)
          {
              System.out.println(regexMatcher.group().trim());
          }
       //   System.out.println("Start index: "+regexMatcher.start());
         // System.out.println("End of index: "+regexMatcher.end());

          }
      }

     public static String regexReplace(String str2Replace)
          {
         // Pattern replace =Pattern.compile("[A-Za-z]",Pattern.CASE_INSENSITIVE);
           Pattern replace =Pattern.compile("\\s+");
           Matcher regexMatcher = replace.matcher(str2Replace.trim());
           System.out.println(regexMatcher.replaceAll(","));
           return regexMatcher.replaceAll(",");
          }



public int validate(String code)
{
    String method;
    String editedCode;
    int flag=1;


     editedCode=regexReplace(code);
    System.out.println(editedCode);
     StringTokenizer words= new StringTokenizer(editedCode, ",");
     ArrayList<String> ColumnNames =new  ArrayList<String>();
      ArrayList<String> ColumnTypes =new  ArrayList<String>();


     String FirstToken=null;


     FirstToken=words.nextToken();

     String SecondToken=null;
     if
             (words.hasMoreTokens()&&flag==1)
     {
           SecondToken=words.nextToken();

     }
     else
     {
         return 0;
     }
     String TableName = null;










     if
        (FirstToken.equalsIgnoreCase("create")&&SecondToken.equalsIgnoreCase("table")&&words.hasMoreTokens()&&flag==1)

     {


         String temp=null;
         method=FirstToken;

         TableName=words.nextToken();
    if
            (words.hasMoreTokens())
    {
         temp=words.nextToken();
         if
                 (!temp.equalsIgnoreCase("("))
         {

             return 0;

         }

    }

        if
                (words.hasMoreTokens())
        {
             temp=words.nextToken();
        }



       while
    (words.hasMoreTokens()&&!temp.equalsIgnoreCase(")"))
    {
           System.out.println(temp);
    ColumnNames.add(temp);
    if
                (!words.hasMoreTokens())
        {
             return 0;
        }
    temp=words.nextToken();
        System.out.println(temp);
        if
                (!((temp.equalsIgnoreCase("varchar")))&&!(temp.equalsIgnoreCase("int")))
        {
            return 2;
        }
     ColumnTypes.add(temp);

       if
                (!words.hasMoreTokens())
        {
             return 0;
        }
        temp=words.nextToken();
    }
       if
               (!temp.equalsIgnoreCase(");"))
       {
           return 0;
       }






     }












            else if
         (FirstToken.equalsIgnoreCase("delete")&&SecondToken.equalsIgnoreCase("from")&&words.hasMoreTokens()&&flag==1)

            {
               method=FirstToken;
                String ThirdToken=null;
               TableName=words.nextToken();
               if
                       (words.hasMoreTokens())
               {
               ThirdToken=words.nextToken();
               }
               else
                   return 0;
               if
                       (!ThirdToken.equalsIgnoreCase("where"))
               {
               return 5;
               }
               else{

                    String condition=null;
                   if
                       (words.hasMoreTokens())
               {
               condition=words.nextToken();

                if
             (words.hasMoreTokens()&&words.nextToken().equalsIgnoreCase(";"))
     {
         CheckDelete(TableName, method,condition);
             return 1;

     }

                char[] c=condition.toCharArray();
     if
             (c[c.length-1]!=';')

     {

         CheckDelete(TableName, method,condition);
     return 3;

     }
     else
     {

condition=condition.replace(condition.substring(condition.length()-1), "");
     CheckDelete(TableName, method,condition);
     }



                 CheckDelete(TableName, method,condition);

               }
               else
                   return 4;




               }


            }
















            else if
         (FirstToken.equalsIgnoreCase("drop")&& SecondToken.equalsIgnoreCase("table")&&words.hasMoreTokens()&&flag==1)
            {
                String temp=null;
                method=FirstToken;

                TableName=words.nextToken();

     if
             (words.hasMoreTokens()&&words.nextToken().equalsIgnoreCase(";"))
     {
         CheckDrop(TableName, method);
             return 1;

     }

  char[] tableName=TableName.toCharArray();
     if
             (tableName[tableName.length-1]!=';')

     {

         CheckDrop(TableName, method);
     return 3;

     }
     else
     {

TableName=TableName.replace(TableName.substring(TableName.length()-1), "");
     CheckDrop(TableName, method);
     }
            }











            else if
      (FirstToken.equalsIgnoreCase("insert")&&SecondToken.equalsIgnoreCase("into")&&words.hasMoreTokens()&&flag==1)
            {

                method=FirstToken;
                TableName=words.nextToken();
                words.nextToken();
                String s1=words.nextToken();
                 while
                      (words.hasMoreTokens()&&s1!=")")
                    {

                        ColumnNames.add(s1);
                            s1=words.nextToken();

    }
                 words.nextToken();
                 if
                         (!words.nextToken().equalsIgnoreCase("value"))
                 {
                     return 0;
                 }

                 else
                 {
                     words.nextToken();





                 }







            }

            else if
                    (FirstToken.equalsIgnoreCase("select")&&words.hasMoreElements()&&flag==1)
            {

                method=FirstToken;
                String temp= SecondToken;
                String conditionSelect=null;

                while
                        (words.hasMoreTokens()&&!temp.equalsIgnoreCase("From"))
                {
                    ColumnNames.add(temp);
                    temp=words.nextToken();

                }

                  if
                        (!words.hasMoreTokens())
                {
                    return 0;
                }

                  else
                  {
                       TableName=words.nextToken();
                  }

                if
                        (!words.hasMoreTokens())
                {
                    return 5;
                }

                else
                {
                     if
                        (!words.hasMoreTokens())
                {
                    return 4;
                }
                     else
                     {
                         conditionSelect =words.nextToken();
                     }

                }
                        CheckSelect(ColumnNames, TableName, method, conditionSelect);

            }











            else
            {
                return 0;
            }







     // TableName=words.nextToken();
    // words.nextToken();
      //String s1=words.nextToken();


 /*
while
    (words.hasMoreTokens()&&s1!=")")
    {


    CloumNames.add(s1);
     CloumTypes.add(words.nextToken());
      s1=words.nextToken();
    }
  */

return flag;

}




        public void CheckCreate (ArrayList ColumnNames,ArrayList ColumnTypes, String TableName,String Method )
    {


   System.out.println("Table name :" +TableName);
   System.out.println("Method name :" +Method);


   System.out.println("Column Names :");

    for(int i = 0; i < ColumnNames.size(); i++) {
    System.out.println(ColumnNames.get(i));
}


   System.out.println("Column Types :");

    for(int i = 0; i < ColumnTypes.size(); i++) {
    System.out.println(ColumnTypes.get(i));
}



    }

        public void CheckDelete ( String TableName,String Method, String condition)
        {
         System.out.println("Table name :" +TableName);
         System.out.println("Method name :" +Method);
          System.out.println("Condition :" +condition);



        }


        public void CheckDrop (String TableName,String Method)
        {
              System.out.println("Table name :" +TableName);
         System.out.println("Method name :" +Method);



        }


            public void CheckSelect (ArrayList ColumnNames,String TableName,String Method,String condition )
    {


   System.out.println("Table name :" +TableName);
   System.out.println("Method name :" +Method);




   System.out.println("Column Names :");

    for(int i = 0; i < ColumnNames.size(); i++) {
    System.out.println(ColumnNames.get(i));

}
     System.out.println("Condition :" +condition);







    }


}