package util.sqlparse.visitor.oracle.test;

import bean.Column;
import bean.DataBase;
import bean.Schema;
import bean.Table;
import bean.View;
import java.util.ArrayList;
import java.util.List;
import test.StopWatch;
import util.StringJoin;
import util.sqlparse.visitor.common.Context;
import util.sqlparse.visitor.common.bean.SQLResult;
import util.sqlparse.visitor.common.bean.TableInfo;
import util.sqlparse.visitor.common.memo.FieldMemo;
import util.sqlparse.visitor.common.memo.TableMemo;
import util.sqlparse.visitor.oracle.SQLParser;

public class SQLParseTest {
   public static void main(String[] args) {
      testSelect();
   }

   static DataBase createDB() {
      DataBase db = new DataBase();
      db.setSchemaList(new ArrayList());
      Schema schema = new Schema("test");
      schema.setViewList(new ArrayList());
      View view = new View("vtest", "create or replace view vtest  as with a as (select login_name as lname,dept_id as did from vtest1) select b.dept_id as did,b.dept_name as dept_name, a.lname as account from a left join sys_dept b on a.did=b.dept_id");
      View view1 = new View("vtest1", "create or replace view vtest1 as select a.user_id,a.user_name,a.login_name,a.dept_id from sys_user a");
      schema.getViewList().add(view);
      schema.getViewList().add(view1);
      db.getSchemaList().add(schema);
      Table table = new Table("sys_user", "test");
      table.setColumnList(new ArrayList());
      table.getColumnList().add(new Column("user_id", null, false, null));
      table.getColumnList().add(new Column("user_name", null, false, null));
      table.getColumnList().add(new Column("login_name", null, false, null));
      table.getColumnList().add(new Column("dept_id", null, false, null));
      table.getColumnList().add(new Column("user_type", null, false, null));
      table.getColumnList().add(new Column("email", null, false, null));
      table.getColumnList().add(new Column("sex", null, false, null));
      table.getColumnList().add(new Column("avatar", null, false, null));
      table.getColumnList().add(new Column("password", null, false, null));
      table.getColumnList().add(new Column("salt", null, false, null));
      table.getColumnList().add(new Column("status", null, false, null));
      table.getColumnList().add(new Column("del_flag", null, false, null));
      table.getColumnList().add(new Column("login_ip", null, false, null));
      table.getColumnList().add(new Column("login_date", null, false, null));
      table.getColumnList().add(new Column("pwd_update_date", null, false, null));
      table.getColumnList().add(new Column("create_by", null, false, null));
      table.getColumnList().add(new Column("create_time", null, false, null));
      table.getColumnList().add(new Column("update_by", null, false, null));
      table.getColumnList().add(new Column("update_time", null, false, null));
      table.getColumnList().add(new Column("phonenumber", null, false, null));
      table.getColumnList().add(new Column("remark", null, false, null));
      schema.getTableList().add(table);
      table = new Table("sys_dept", "test");
      table.setColumnList(new ArrayList());
      table.getColumnList().add(new Column("dept_id", null, false, null));
      table.getColumnList().add(new Column("parent_id", null, false, null));
      table.getColumnList().add(new Column("ancestors", null, false, null));
      table.getColumnList().add(new Column("dept_name", null, false, null));
      table.getColumnList().add(new Column("order_num", null, false, null));
      table.getColumnList().add(new Column("leader", null, false, null));
      table.getColumnList().add(new Column("status", null, false, null));
      table.getColumnList().add(new Column("del_flag", null, false, null));
      table.getColumnList().add(new Column("create_by", null, false, null));
      table.getColumnList().add(new Column("create_time", null, false, null));
      table.getColumnList().add(new Column("update_by", null, false, null));
      table.getColumnList().add(new Column("update_time", null, false, null));
      table.getColumnList().add(new Column("phone", null, false, null));
      table.getColumnList().add(new Column("email", null, false, null));
      schema.getTableList().add(table);
      table = new Table("hr_info", "test");
      table.setColumnList(new ArrayList());
      String tableName = "HR_INFO";
      String schemaName = "TEST";
      table.getColumnList().add(new Column("department_id", schemaName, tableName, null));
      table.getColumnList().add(new Column("location_id", schemaName, tableName, null));
      table.getColumnList().add(new Column("manager_id", schemaName, tableName, null));
      table.getColumnList().add(new Column("people", schemaName, tableName, null));
      table.getColumnList().add(new Column("last_name", schemaName, tableName, "people"));
      table.getColumnList().add(new Column("department_id", schemaName, tableName, "people"));
      table.getColumnList().add(new Column("salary", schemaName, tableName, "people"));
      schema.getTableList().add(table);
      return db;
   }

   static Context createContext() {
      Context context = new Context();
      DataBase db = createDB();
      context.setDataBase(db);
      context.initialize(db, "test", "oracle");
      return context;
   }

   static void testSelect() {
      StopWatch sw = new StopWatch();

      for(int i = 0; i < 1; ++i) {
         String[] sqls = new String[]{"SELECT count(0) FROM sys_user u LEFT JOIN sys_dept d ON u.dept_id = d.dept_id WHERE u.del_flag = '0' AND u.phonenumber LIKE concat(concat('%', ?), '%') AND (u.dept_id = ? OR u.dept_id IN (SELECT t.dept_id FROM sys_dept t WHERE FIND_IN_SET(?, ancestors) <> 0)) "};
         List<String> alls = new ArrayList();

         for(int i1 = 0; i1 < sqls.length; ++i1) {
            alls.add(sqls[i]);
         }

         int k = 0;

         for(int i1 = 0; i1 < sqls.length; ++i1) {
            System.out.println("[" + i1 + "]*****************************************************");
            String sql = sqls[i1];
            System.out.println(sql);
            sw.begin();

            try {
               SQLParser parser = new SQLParser();
               SQLResult result = parser.parse(sql, createDB(), "test");
               sw.end();
               sw.print();
               assertResult(result);
            } catch (Exception e) {
               System.out.println(sql);
               e.printStackTrace();
            }
         }
      }

      sw.printAvg();
   }

   static List<String> buildMergeSql() {
      String[] part1 = new String[]{"MERGE  INTO test.SYS_USER a", "MERGE  INTO SYS_USER  a"};
      String[] part2 = new String[]{"USING (SELECT * FROM SYS_DEPT union select aaa from sys_dept1) b ON a.DEPT_ID=b.DEPT_ID"};
      String[] part3 = new String[]{"WHEN NOT MATCHED THEN INSERT  (USER_ID,DEPT_ID,LOGIN_NAME,USER_TYPE,SEX,AVATAR,PASSWORD,SALT,STATUS,DEL_FLAG,LOGIN_IP,LOGIN_DATE,PWD_UPDATE_DATE,CREATE_BY,CREATE_TIME,UPDATE_BY,UPDATE_TIME,PHONENUMBER,REMARK,USER_NAME,EMAIL) VALUES (102,100,'TEST','00',1,NULL,'29c67a30398638269fe600f73a054934','111111',0,0,'127.0.0.1',sysdate,sysdate,'admin',sysdate,'admin',SYSDATE,'111111','','测试11','xx@xx.com')", "WHEN NOT MATCHED THEN INSERT  VALUES (102,100,'TEST','00',1,NULL,'29c67a30398638269fe600f73a054934','111111',0,0,'127.0.0.1',sysdate,sysdate,'admin',sysdate,'admin',SYSDATE,'111111','','测试11','xx@xx.com')", "WHEN NOT MATCHED THEN INSERT  (USER_ID,DEPT_ID,LOGIN_NAME,USER_TYPE,SEX,AVATAR,PASSWORD,SALT,STATUS,DEL_FLAG,LOGIN_IP,LOGIN_DATE,PWD_UPDATE_DATE,CREATE_BY,CREATE_TIME,UPDATE_BY,UPDATE_TIME,PHONENUMBER,REMARK,USER_NAME,EMAIL) VALUES (102,100,'TEST','00',1,NULL,'29c67a30398638269fe600f73a054934','111111',0,0,'127.0.0.1',sysdate,sysdate,'admin',sysdate,'admin',SYSDATE,'111111','','测试11','xx@xx.com') WHERE 1=1", "WHEN NOT MATCHED THEN INSERT  VALUES (102,100,'TEST','00',1,NULL,'29c67a30398638269fe600f73a054934','111111',0,0,'127.0.0.1',sysdate,sysdate,'admin',sysdate,'admin',SYSDATE,'111111','','测试11','xx@xx.com') WHERE 1=1"};
      String[] part4 = new String[]{"WHEN MATCHED THEN UPDATE SET user_name='测试2',LOGIN_IP='localhost' WHERE 1=1 OR 1<>1", "WHEN MATCHED THEN UPDATE SET user_name='测试2',LOGIN_IP='localhost'", "WHEN MATCHED THEN UPDATE SET user_name='测试2',LOGIN_IP=DEFAULT WHERE 1=1 OR 1<>1", "WHEN MATCHED THEN UPDATE SET user_name='测试2',LOGIN_IP=DEFAULT", "WHEN MATCHED THEN UPDATE SET user_name='测试2',LOGIN_IP='localhost' WHERE 1=1 OR 1<>1 DELETE  WHERE dept_id=1001", "WHEN MATCHED THEN UPDATE SET user_name='测试2',LOGIN_IP='localhost' DELETE  WHERE dept_id=1001", "WHEN MATCHED THEN UPDATE SET user_name='测试2',LOGIN_IP=DEFAULT WHERE 1=1 OR 1<>1 DELETE  WHERE dept_id=1001", "WHEN MATCHED THEN UPDATE SET user_name='测试2',LOGIN_IP=DEFAULT DELETE  WHERE dept_id=1001"};
      List<String> sqls = new ArrayList();

      for(String s1 : part1) {
         for(String s2 : part2) {
            for(String s3 : part3) {
               for(String s4 : part4) {
                  String sql = s1 + " " + s2 + " " + s3 + " " + s4;
                  sqls.add(sql);
               }
            }
         }
      }

      return sqls;
   }

   static String getString(FieldMemo memo) {
      StringBuffer s = new StringBuffer();
      s.append(memo.getAtomName());
      if (memo.children.size() > 0) {
         s.append(" [ ");
         if (memo.children != null) {
            for(FieldMemo child : memo.children) {
               s.append(getString(child));
               s.append(",");
            }

            s.deleteCharAt(s.length() - 1);
         }

         s.append(" ] ");
      }

      return s.toString();
   }

   static void assertResult(SQLResult result) {
      System.out.println("------------------[TABLES-INFO]----------------");
      List<TableInfo> tables = result.tables;
      List<String> tableList = new ArrayList();

      for(int i = 0; i < tables.size(); ++i) {
         tableList.add(((TableInfo)tables.get(i)).getTable().getAtomName());
      }

      System.out.println(StringJoin.join(tableList, "\n"));
      int b = 0;
      System.out.println("------------------[TABLES-MEMO]----------------");
      List<TableMemo> tableMemos = result.tableMemos;
      List<String> tableMemoList = new ArrayList();
      List<String> qualifiedNameList = new ArrayList();

      for(int i = 0; i < tableMemos.size(); ++i) {
         TableMemo tableMemo = (TableMemo)tableMemos.get(i);
         List<TableMemo> children = tableMemo.getChildren();

         for(int i1 = 0; i1 < children.size(); ++i1) {
            TableMemo tableMemo1 = (TableMemo)children.get(i1);
            qualifiedNameList.add(tableMemo1.getQualifiedName().toString());
         }

         tableMemoList.add(tableMemo.getAtomName() + "->" + StringJoin.join(qualifiedNameList, ","));
      }

      System.out.println(StringJoin.join(tableMemoList, "\n"));
      System.out.println("------------------[FIELDS-INFO]----------------");
      System.out.println("------------------[FIELDS-MEMO]----------------");
      System.out.println("------------------[OUTPUT-INFO]----------------");
      System.out.println("------------------[OUTPUT-MEMO]----------------");
      int a = 0;
      System.out.println("------------------[VALUES-INFO]----------------");
      System.out.println(result.statement.toString());
   }

   static void testInsert() {
   }

   static void testUpdate() {
   }

   static void testDelete() {
   }
}
