package util.sqlparse.visitor.spark.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.mysql.visitor.FieldVisitor;
import util.sqlparse.visitor.mysql.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 = "select a.id,b.name from a left join (select f1,f2 from xtb)b on a.id=b.id where a.name in (select xx.name from xx)";
      sql = "select a.id,a.name from a  union select b.id,b.name from b join (select c.aa1 from tb3 c) d on d.aa1=b.id ";
      sql = "select id,name from a  union select id,name from b join (select aa1 from tb3 c) d on d.aa1=b.id ";
      sql = "SELECT distinct             SZ.JHDM JHID,             SZ.RQ,             SZ.ZJDWDM,             SZ.ZJDWMC,             SZ.ZJDH,             SZ.ZJDMC,             SZ.JHDM,             SZ.JHBM,             SZ.JBBMDM,             SZ.JBBM,             DECODE(SZ.ZJDTDM,                    '23',                    D.WZJS,                    '14',                    D.WZJS,                    '12',                    D.WZJS,                    DDT.JS) JS,             D.SJJS,             DECODE(DECODE(SZ.ZJDTDM,                           '23',                           D.WZJS,                           '14',                           D.WZJS,                           '12',                           D.WZJS,                           DDT.JS),                    NULL,                    DDT1.JS,                    DECODE(SZ.ZJDTDM,                           '23',                           D.WZJS,                           '14',                           D.WZJS,                           '12',                           D.WZJS,                           DDT.JS)) SJJSA,             D.MDCDM,             BCB.HZMC MDCMC,             DECODE(SZ.ZJDTDM, '23', D.WZCWDM, '14', D.WZCWDM, DDZ.CWDM) ZDCWDM,             DECODE(SZ.ZJDTDM,                    '23',                    BCA.HZMC,                    '14',                    BCA.HZMC,                    '12',                    BCA.HZMC,                    BC.HZMC) ZDCWMC,             CASE               WHEN DDT.JS >= DDJ.JS THEN                DDT.JS - DECODE(DDJ.JS, NULL, 0, DDJ.JS)               ELSE                DECODE(DDJ.JS, NULL, DDT.JS, 0)             END RJC,             SZ.YJHJC,             SZ.YLJJC,             SZ.ZTXH,             SZ.ZTZJ,             SZ.ZY,             SZ.ZSB,             SZ.BYA,             SZ.PL,             ZJJYA.MD,             ZJJYA.NDB,             SZ.ZJDTDM,             SZ.ZJDT,             SZ.GZNR,             ZYLSLA.ZYLSL,             (SELECT MIN(SYJS)                FROM (SELECT JHDM,                             JXJ,                             JS,                             LAG(JS) OVER(PARTITION BY JHDM ORDER BY JS) SYJS                        FROM ZJSJ_GD)               WHERE JXJ <> 0                 AND JHDM = SZ.JHDM) SJZXDJS,             JJ.ZXD,                          ZZ.SCSJ,             ZZ.JC,             ZZ.CZJ,             ZZ.FZSC,             ZZ.GJSC,             ZZ.CJSC,                          ZZ.FSCSJ,             ZZ.SGHJ,             ZZ.QZKZ,             ZZ.XL,             ZZ.ZZTG,             ZZ.FZ,             ZZ.ZRZH,             ZZ.QT,             ZZX.GGXH ZJXH,             BZ.ZJZYXMMC SGZT,             ZJ.ZTCX || ZT.TGCX JSJG,             (SELECT SUM(RJC)                FROM ZJGC_ZJSCRB               WHERE JHDM = SZ.JHDM                 AND TO_CHAR(RQ, 'YYYYMMDD') <= TO_CHAR(SZ.RQ, 'YYYYMMDD')                 AND RQ >= TO_DATE(TO_CHAR(SZ.RQ, 'YYYY') - 1 || '-12-26',                                   'YYYY-MM-DD')) DJNLJJC,             (SELECT SUM(RJC)                FROM ZJGC_ZJSCRB               WHERE ZJDH = SZ.ZJDH                 AND TO_CHAR(RQ, 'YYYYMMDD') <= TO_CHAR(SZ.RQ, 'YYYYMMDD')                 AND RQ >= TO_DATE(TO_CHAR(SZ.RQ, 'YYYY') - 1 || '-12-26',                                   'YYYY-MM-DD')) JDNLJJC,             (SELECT SUM(RJC) FROM ZJGC_ZJSCRB WHERE JHDM = SZ.JHDM) DJLNLJJC,             (SELECT SUM(RJC) FROM ZJGC_ZJSCRB WHERE ZJDH = SZ.ZJDH) JDLNLJJC        FROM SCJH_ZJSCDT SZ        LEFT JOIN DJJCXX D          ON D.JHDM = SZ.JHDM        LEFT JOIN BLBM_JBDM BJ          ON BJ.JBDM = D.JBDM        LEFT JOIN (SELECT JHDM,                          ZJSBDM,                          GGXH,                          ROW_NUMBER() OVER(PARTITION BY JHDM, ZJSBDM ORDER BY CJSJ DESC) XH                     FROM ZJJTG_ZJSBJBXX                    WHERE ZJSBDM = '0100') ZZX          ON ZZX.JHDM = SZ.JHDM         AND ZZX.XH = 1        LEFT JOIN JSXX_JSJG JJ          ON JJ.JHDM = SZ.JHDM        LEFT JOIN (SELECT JHDM,                          ROUND(sum(decode(SUBSTR(ZJSXLXDM, 1, 1), '1', ZYSJ)) / 60,                                2) SCSJ,                          ROUND(sum(decode(SUBSTR(ZJSXLXDM, 1, 3),                                           '101',                                           ZYSJ)) / 60,                                2) JC,                          ROUND(sum(decode(SUBSTR(ZJSXLXDM, 1, 5),                                           '10101',                                           ZYSJ)) / 60,                                2) CZJ,                          ROUND(sum(decode(SUBSTR(ZJSXLXDM, 1, 3),                                           '106',                                           ZYSJ)) / 60,                                2) FZSC,                          ROUND(sum(decode(SUBSTR(ZJSXLXDM, 1, 3),                                           '103',                                           ZYSJ)) / 60,                                2) GJSC,                          ROUND(sum(decode(SUBSTR(ZJSXLXDM, 1, 3),                                           '102',                                           ZYSJ)) / 60,                                2) CJSC,                                                    ROUND(sum(decode(SUBSTR(ZJSXLXDM, 1, 1), '2', ZYSJ)) / 60,                                2) FSCSJ,                          ROUND(sum(decode(SUBSTR(ZJSXLXDM, 1, 3),                                           '201',                                           ZYSJ)) / 60,                                2) SGHJ,                          ROUND(sum(decode(ZJSXLXDM, '2010100', ZYSJ)) / 60,                                2) QZKZ,                          ROUND(sum(decode(SUBSTR(ZJSXLXDM, 1, 3),                                           '202',                                           ZYSJ)) / 60,                                2) XL,                          NULL ZZTG,                          ROUND(sum(decode(SUBSTR(ZJSXLXDM, 1, 3),                                           '205',                                           ZYSJ)) / 60,                                2) FZ,                          ROUND(sum(decode(SUBSTR(ZJSXLXDM, 1, 3),                                           '204',                                           ZYSJ)) / 60,                                2) ZRZH,                          ROUND(sum(decode(SUBSTR(ZJSXLXDM, 1, 3),                                           '299',                                           ZYSJ)) / 60,                                2) QT                     FROM (SELECT ZZ.JHDM,                                  ZZ.ZYKSSJ,                                  ZZ.ZYZZSJ,                                  CASE                                    WHEN ZZ.ZYKSSJ <                                         TO_DATE(TO_CHAR(TO_DATE(I_V_RQ,                                                                 'YYYY-MM-DD') - 1,                                                         'YYYY-MM-DD') ||                                                 ' 8:00:00',                                                 'YYYY-MM-DD HH24:MI:SS') THEN                                     (ZZ.ZYZZSJ -                                     TO_DATE(TO_CHAR(TO_DATE(I_V_RQ,                                                              'YYYY-MM-DD') - 1,                                                      'YYYY-MM-DD') ||                                              ' 8:00:00',                                              'YYYY-MM-DD HH24:MI:SS')) * 24 * 60                                    WHEN ZYZZSJ >                                         TO_DATE(I_V_RQ || ' 8:00:00',                                                 'YYYY-MM-DD HH24:MI:SS') THEN                                     (TO_DATE(I_V_RQ || ' 8:00:00',                                              'YYYY-MM-DD HH24:MI:SS') -                                     ZZ.ZYKSSJ) * 24 * 60                                    ELSE                                     (ZZ.ZYZZSJ - ZZ.ZYKSSJ) * 24 * 60                                  END ZYSJ,                                  --ZZ.ZYSJ,                                  ZZ.ZJSXLXDM                             FROM ZJGC_ZJZY ZZ                            WHERE (ZYZZSJ <=                                  TO_DATE(I_V_RQ || ' 8:00:00',                                           'YYYY-MM-DD HH24:MI:SS') AND                                  ZYZZSJ >                                  TO_DATE(TO_CHAR(TO_DATE(I_V_RQ,                                                           'YYYY-MM-DD') - 1,                                                   'YYYY-MM-DD') ||                                           ' 8:00:00',                                           'YYYY-MM-DD HH24:MI:SS'))                               OR (ZYKSSJ <                                  TO_DATE(I_V_RQ || ' 8:00:00',                                           'YYYY-MM-DD HH24:MI:SS') AND                                  ZYKSSJ >=                                  TO_DATE(TO_CHAR(TO_DATE(I_V_RQ,                                                           'YYYY-MM-DD') - 1,                                                   'YYYY-MM-DD') ||                                           ' 8:00:00',                                           'YYYY-MM-DD HH24:MI:SS')))                    GROUP BY JHDM) ZZ          ON ZZ.JHDM = SZ.JHDM        LEFT JOIN (SELECT JHDM, MAX(SJ) SJ, TO_CHAR(SJ, 'YYYY-MM-DD') RQ                     FROM DZLJ_DTJS                    WHERE TO_CHAR(SJ, 'HH24MI') <= '0800'                    GROUP BY JHDM, TO_CHAR(SJ, 'YYYY-MM-DD')) DJ          ON DJ.JHDM = SZ.JHDM         AND DJ.RQ = TO_CHAR(SZ.RQ, 'YYYY-MM-DD')        LEFT JOIN (SELECT ROW_NUMBER() OVER(PARTITION BY JHDM, TO_CHAR(SJ, 'YYYYMMDD') ORDER BY SJ) XH,                          JHDM,                          SJ,                          JS                     FROM DZLJ_DTJS) DDT          ON DDT.JHDM = DJ.JHDM         AND DDT.SJ = DJ.SJ        LEFT JOIN (SELECT D1.JHDM JHDM, D1.JS JS                     FROM (SELECT JHDM, MAX(SJ) SJ                             FROM DZLJ_DTJS                            WHERE SJ < TO_DATE(I_V_RQ, 'YYYY-MM-DD') + 1                            GROUP BY JHDM) D2                     LEFT JOIN (SELECT ROW_NUMBER() OVER(PARTITION BY JHDM, TO_CHAR(SJ, 'YYYYMMDD') ORDER BY SJ DESC) XH,                                      JHDM,                                      SJ,                                      JS                                 FROM DZLJ_DTJS) D1                       ON D2.JHDM = D1.JHDM                      AND D2.SJ = D1.SJ                      AND D1.XH = 1) DDT1          ON DDT1.JHDM = SZ.JHDM        LEFT JOIN (SELECT JHDM, MAX(SJ) SJ, TO_CHAR(SJ, 'YYYY-MM-DD') RQ                     FROM DZLJ_DTJS                    WHERE TO_CHAR(SJ, 'HH24') <= '08'                    GROUP BY JHDM, TO_CHAR(SJ, 'YYYY-MM-DD')) DJ2          ON DJ2.JHDM = SZ.JHDM         AND TO_CHAR(DJ2.SJ, 'YYYY-MM-DD') =             TO_CHAR(SZ.RQ - 1, 'YYYY-MM-DD')        LEFT JOIN (SELECT *                     FROM (SELECT ROW_NUMBER() OVER(PARTITION BY JHDM ORDER BY SJ DESC) XH,                                  JHDM,                                  SJ,                                  JS                             FROM DZLJ_DTJS                            WHERE SJ <=                                  TO_DATE(I_V_RQ || ' 8:00:00',                                          'YYYY-MM-DD HH24:MI;SS') - 1) DDD                    WHERE DDD.XH = 1) DDJ          ON DDJ.JHDM = DDT.JHDM        LEFT JOIN DZLJ_DZRZ DDZ          ON DDZ.JHDM = SZ.JHDM         AND DDZ.RQ = SZ.RQ        LEFT JOIN BLBM_CWDM BC          ON BC.CWDM = DDZ.CWDM        LEFT JOIN BLBM_CWDM BCA          ON BCA.CWDM = D.WZCWDM        LEFT JOIN BLBM_CWDM BCB          ON BCB.CWDM = D.MDCDM        LEFT JOIN (SELECT JHDM, MAX(ZYZZSJ) ZYZZSJ                     FROM ZJGC_ZJZY                    WHERE ZYZZSJ <=                          TO_DATE(I_V_RQ || ' 8:00:00',                                  'YYYY-MM-DD HH24:MI:SS')                      AND ZYZZSJ >=                          TO_DATE(TO_CHAR(TO_DATE(I_V_RQ, 'YYYY-MM-DD') - 1,                                          'YYYY-MM-DD') || ' 8:00:00',                                  'YYYY-MM-DD HH24:MI:SS')                    GROUP BY JHDM) ZZA          ON ZZA.JHDM = SZ.JHDM        LEFT JOIN ZJGC_ZJZY ZZB          ON ZZB.JHDM = ZZA.JHDM         AND ZZB.ZYZZSJ = ZZA.ZYZZSJ        LEFT JOIN (SELECT JHDM, MAX(QYSJ) QYSJ                     FROM ZJY_JYXN                    WHERE QYSJ <= TO_DATE(I_V_RQ || ' 8:00:00',                                          'YYYY-MM-DD HH24:MI:SS')                      AND QYSJ >=                          TO_DATE(TO_CHAR(TO_DATE(I_V_RQ, 'YYYY-MM-DD') - 1,                                          'YYYY-MM-DD') || ' 8:00:00',                                  'YYYY-MM-DD HH24:MI:SS')                    GROUP BY JHDM) ZJJY          ON ZJJY.JHDM = SZ.JHDM        LEFT JOIN ZJY_JYXN ZJJYA          ON ZJJY.JHDM = ZJJYA.JHDM         AND ZJJY.QYSJ = ZJJYA.QYSJ        LEFT JOIN (SELECT JHDM, MAX(QYSJ) QYSJ                     FROM ZJY_QTXN                    WHERE QYSJ <= TO_DATE(I_V_RQ || ' 8:00:00',                                          'YYYY-MM-DD HH24:MI:SS')                      AND QYSJ >=                          TO_DATE(TO_CHAR(TO_DATE(I_V_RQ, 'YYYY-MM-DD') - 1,                                          'YYYY-MM-DD') || ' 8:00:00',                                  'YYYY-MM-DD HH24:MI:SS')                    GROUP BY JHDM) ZYLSL          ON ZYLSL.JHDM = SZ.JHDM        LEFT JOIN ZJY_QTXN ZYLSLA          ON ZYLSLA.JHDM = ZYLSL.JHDM         AND ZYLSLA.QYSJ = ZYLSL.QYSJ        LEFT JOIN BLBM_ZJZYXMDM BZ          ON BZ.ZJZYXMDM = ZZB.ZJZYXMDM        LEFT JOIN ( /*SELECT ZJ.JHDM,                                             '钻头程序：'||LISTAGG('Φ' || ZJ.GG || 'mm ×' || XRSD||'m','+') WITHIN GROUP(ORDER BY ZJ.JHDM,ZJ.GG DESC)ZTCX                                         FROM (SELECT DISTINCT ZJ.JHDM, ZJ.GG, MAX(ZJX.QCSD)XRSD                                                 FROM ZJGC_JBZJZHXX ZJ                                            LEFT JOIN ZJGC_JBZJZH ZJX ON ZJX.JHDM=ZJ.JHDM                                                                      AND ZJX.RQ=ZJ.RQ                                                WHERE ZJMCDM LIKE '01%'                                                  AND ZJ.RQ<=TO_DATE(I_V_RQ, 'YYYY-MM-DD HH24:MI:SS')                                                  AND ZJX.QCSD IS NOT NULL                                               GROUP BY ZJ.JHDM,ZJ.GG)ZJ                                        GROUP BY ZJ.JHDM*/                   SELECT ZJ.JHDM,                           '钻头程序：' || LISTAGG('Φ' || ZJ.ZTZJ || 'mm ×' || XRSD || 'm',                                              '+') WITHIN GROUP(ORDER                                                      BY ZJ.JHDM, ZJ.ZTZJ DESC) ZTCX                     FROM (select DISTINCT ZJH.jhdm,                                            ZZ.ZTZJ,                                            MAX(ZJH.QCSD) XRSD                              from ZJGC_JBZJZH ZJH                              left join ZJGC_ZTJBXX zz                                on ZJH.jhdm = zz.jhdm                               and ZJH.ztbh = zz.ztbh                             where ZJH.RQ <=                                   TO_DATE(I_V_RQ, 'YYYY-MM-DD HH24:MI:SS')                               AND ZJH.ZTBH IS NOT NULL                               AND ZJH.QCSD IS NOT NULL                             GROUP BY ZJH.JHDM, ZZ.ZTZJ) ZJ                    GROUP BY ZJ.JHDM) ZJ          ON ZJ.JHDM = SZ.JHDM        LEFT JOIN (SELECT ZT.JHDM,                          ' 套管程序：' ||                          LISTAGG('Φ' || ZT.WJ || 'mm ×' || ZT.XS || 'm',                                  '+') WITHIN GROUP(ORDER BY ZT.JHDM, ZT.XS) TGCX                     FROM (WITH TB AS (SELECT DISTINCT ZT.JHDM,                                                       ZT.TGMCDM,                                                       BT.TGMC,                                                       ZT.WJ,                                                       MAX(TGBHA) TGBHA                                         FROM ZJJTG_TG ZT                                         LEFT JOIN BLBM_TGMCDM BT                                           ON BT.TGMCDM = ZT.TGMCDM                                        GROUP BY ZT.JHDM,                                                 ZT.TGMCDM,                                                 BT.TGMC,                                                 ZT.WJ)                            SELECT T.JHDM,                                   T.TGMCDM,                                   (SELECT SUM(DGCD)                                      FROM ZJJTG_TG                                     WHERE JHDM = T.JHDM                                       AND TGMCDM = T.TGMCDM                                       AND TGBHA <= T.TGBHA) XS,                                   T.WJ,                                   T.TGMC                              FROM TB T) ZT                             GROUP BY JHDM                   ) ZT          ON ZT.JHDM = SZ.JHDM       WHERE SZ.RQ = TO_DATE(I_V_RQ, 'YYYY-MM-DD')         AND SZ.JHDM IS NOT NULL;";
      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 ?,?";
      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", "mysql");
      Scope scope = new 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";
      ScopeVisitor visitor = new ScopeVisitor(scope);
      List<SQLStatement> statements = SQLUtils.parseStatements(sql, "mysql", 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 + "-");
      }

   }
}
