package util.sqlparse.visitor.mysql.test;

import bean.Column;
import bean.DataBase;
import bean.Schema;
import bean.Table;
import bean.View;
import java.util.ArrayList;
import java.util.Map;
import test.StopWatch;
import util.sqlparse.visitor.common.Context;
import util.sqlparse.visitor.common.bean.SQLResult;
import util.sqlparse.visitor.common.bean.ValueInfo;
import util.sqlparse.visitor.common.memo.FieldMemo;
import util.sqlparse.visitor.common.memo.TableMemo;
import util.sqlparse.visitor.common.memo.ValueMemo;
import util.sqlparse.visitor.common.scope.Scope;
import util.sqlparse.visitor.mysql.SQLParser;

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

   static DataBase createDB() {
      DataBase db = new DataBase();
      db.setCaseSensitive(true);
      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("tb1", "test");
      table.setColumnList(new ArrayList());
      table.getColumnList().add(new Column("c1", null, false, null));
      table.getColumnList().add(new Column("c2", null, false, null));
      table.getColumnList().add(new Column("c3", null, false, null));
      table.getColumnList().add(new Column("name", null, false, null));
      schema.getTableList().add(table);
      table = new Table("tb2", "test");
      table.setColumnList(new ArrayList());
      table.getColumnList().add(new Column("c1", null, false, null));
      table.getColumnList().add(new Column("c2", null, false, null));
      table.getColumnList().add(new Column("c4", null, false, null));
      table.getColumnList().add(new Column("name", null, false, null));
      schema.getTableList().add(table);
      table = new Table("tb4", "test");
      table.setColumnList(new ArrayList());
      table.getColumnList().add(new Column("c2", null, false, null));
      table.getColumnList().add(new Column("c3", null, false, null));
      table.getColumnList().add(new Column("c4", null, false, null));
      table.getColumnList().add(new Column("name", null, false, null));
      schema.getTableList().add(table);
      table = new Table("tb5", "test");
      table.setColumnList(new ArrayList());
      table.getColumnList().add(new Column("c1", null, false, null));
      table.getColumnList().add(new Column("c2", null, false, null));
      table.getColumnList().add(new Column("c3", null, false, null));
      table.getColumnList().add(new Column("name", null, false, null));
      schema.getTableList().add(table);
      return db;
   }

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

   static void testSelect() {
      StopWatch sw = new StopWatch();
      String[] sqls = new String[]{"select * from (select * from sys_user) y"};
      int i1 = 0;

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

      sw.printAvg();
   }

   static void assertResult(SQLResult result) {
      System.out.println("------------------[TABLES-INFO]----------------");
      System.out.println(result.statement.toString());
   }

   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 String printValue(ValueInfo value) {
      return value.value.toString() + "->" + value.getValueNames();
   }

   static void log(Scope scope, String space) {
      System.out.println(space + "<scope>" + scope.getClass() + "--------------------------" + (scope.ref == null ? "" : scope.ref.getClass().toString()));

      for(Map.Entry<String, TableMemo> e : scope.getTableMap().entrySet()) {
         System.out.println("[T]" + (String)e.getKey() + "->" + ((TableMemo)e.getValue()).getQualifiedName() + "->" + ((TableMemo)e.getValue()).name + "->" + ((TableMemo)e.getValue()).alias);
      }

      for(Map.Entry<String, FieldMemo> e : scope.getFieldMap().entrySet()) {
         System.out.println("[F]" + (String)e.getKey() + "->" + ((FieldMemo)e.getValue()).getQualifiedName() + "->" + ((FieldMemo)e.getValue()).name + "->" + ((FieldMemo)e.getValue()).alias + "->" + ((FieldMemo)e.getValue()).isSelectItem);
         outputFieldChildren((FieldMemo)e.getValue(), "  -");
      }

      for(Scope child : scope.getChildren()) {
         log(child, space + "+");
      }

   }

   static void logValue(Scope scope, String space) {
      System.out.println(space + "<scope>" + scope.getClass() + "--------------------------" + (scope.ref == null ? "" : scope.getClass().toString()));

      for(ValueMemo value : scope.getValues()) {
         FieldMemo field = value.field;
         System.out.println("[F]" + (field != null ? field.getQualifiedName() + "->" + field.name + "->" + field.alias : "none") + "->" + (field == null ? false : field.isSelectItem));
         System.out.println("[V]" + value.value + "->" + value.operator + "->" + value.expr.getClass().toString());
      }

      for(Scope child : scope.getChildren()) {
         logValue(child, space + "+");
      }

   }

   static void outputFieldChildren(FieldMemo field, String space) {
      for(FieldMemo child : field.children) {
         System.out.println(space + "[F]" + child.getQualifiedName() + "->" + child.name + "->" + child.alias);
         outputFieldChildren(child, space + "-");
      }

   }
}
