package util.sqlparse.visitor.oracle.test;

import bean.DataBase;
import bean.Schema;
import bean.View;
import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.parser.SQLParserFeature;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import util.sqlparse.visitor.common.Context;
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.oracle.visitor.FieldVisitor;
import util.sqlparse.visitor.oracle.visitor.OracleScopeDialector;
import util.sqlparse.visitor.oracle.visitor.ScopeVisitor;

public class TestScopeVisitor {
   public static void main(String[] args) {
      String sql = "   select            (select c.dept_id from sys_dept as c where c.dept_id=a.dept_id) as e,           ( case when exists (select b.dept_id from sys_dept b where a.dept_id=b.dept_id) then 1  else 0 end ) as fff        from sys_dept a ";
      sql = "insert into tb (id,name) values (?,?)";
      sql = "insert into sys_dept_1 select dept_id,dept_name,f1 from sys_dept where f2=1 or f3 like '%cc%' or exists (select f1 from sys_user where name=?)";
      sql = "insert into tb -- 这个是个注释 \n (id,name) values (?,?)";
      sql = " select fid,name from tb1 where tb1.id='1' union select fid,f2 from tb2 where tb2.id='1' limit ?";
      sql = " select fid,name from tb1 where tb1.id='1' union select fid,f2 from tb2 where tb2.id='1' limit ?,?";
      sql = " delete from tb where id=1";
      sql = " update tb set sex=?,name=?,dept=null,f3=nvl(f1,'1'),f2='2022-01-01 00:00:00' where  id =? or cc in (1,2,3)";
      sql = " select fid,name from tb1 where tb1.id='1' union select fid,f2 from tb2 where tb2.id='1' and f3 between ? and ? and f4 regexp ? limit ?,?";
      sql = "\nSELECT  max(rownum) rn,json_arrayagg\n(\n\tjson_object(\n\t\tKEY 'table_name' value table_name format json\n\t)  returning clob\n) AS json_doc\nFROM dba_tables;\n";
      Context context = new Context();
      DataBase db = new DataBase();
      context.setDataBase(db);
      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);
      context.initialize(db, "test", "oracle");
      Scope scope = new Scope();
      scope.dialector = new OracleScopeDialector(scope);
      scope.context = context;
      sql = "select user_id as uid,user_name as uname,login_name as lname,did,dept_name as dname from vtest1 a join vtest b on a.dept_id=b.did where user_id=1";
      sql = "\nSELECT  max(rownum) rn,json_arrayagg\n(\n\tjson_object(\n\t\tKEY 'table_name' value table_name format json\n\t)  returning clob\n) AS json_doc\nFROM dba_tables;\n";
      ScopeVisitor visitor = new ScopeVisitor(scope);
      List<SQLStatement> statements = SQLUtils.parseStatements(sql, "oracle", SQLParserFeature.SkipComments);
      visitor.perform(statements);
      scope.reset();
      FieldVisitor fieldVisitor = new FieldVisitor(scope);
      fieldVisitor.perform(statements);
      scope.reset();
      log(scope, "+");
      System.out.println("******************************************************************");
      logValue(scope, "+");
      System.out.println("******************************************************************");
      System.out.println(((SQLStatement)statements.get(0)).toString());
   }

   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 + "-");
      }

   }
}
