package test;

import bean.Column;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import util.JdbcUtil;
import util.SqlUtil;
import util.StringJoin;

public class GaussTest1 {
   public static void main(String[] args) throws Exception {
      String content = "{\n      \"ip_port\": \"192.168.2.108:5432\",\n      \"url\": \"jdbc:postgresql://192.168.2.108:5432/db_tpcc?DataSourceName=gauss \",\n      \"username\": \"test\",\n      \"isIgnorecase\":\"1\",\n      \"password\": \"gauss@123\",\n      \"dbType\": \"gauss\",\n      \"excludeSchema\": []\n    }";
      JdbcUtil.initData(content);
      Thread.sleep(1000L);
      parseSelect1();
   }

   private static void parseTrigger() throws Exception {
      testGauss("DROP TRIGGER IF EXISTS trigger_name ON table_name CASCADE");
   }

   private static void parseSet() throws Exception {
      testGauss("SET config_parameter = DOCUMENT ");
   }

   private static void parseRole() throws Exception {
      testGauss("DROP role IF EXISTS dim,a,b ");
   }

   static void parseSequence() throws Exception {
      testGauss("DROP LARGE SEQUENCE IF EXISTS aa.myserial1,myserial2 RESTRICT;");
   }

   static void parseDCL() throws Exception {
      testGauss("SAVEPOINT savepoint_name");
   }

   static void parseGroup() throws Exception {
   }

   static void parseGrant() throws Exception {
   }

   static void parseRevoke() throws Exception {
      testGauss("REVOKE ALL PRIVILEGES ON topten TO sally CASCADE ");
   }

   static void parseUser() throws Exception {
      testGauss("DROP USER IF EXISTS dim,a,b RESTRICT");
   }

   static void parseIndexes() throws Exception {
      testGauss("DROP INDEX IF EXISTS index_name1,index_name2 RESTRICT");
   }

   static void parseValues() throws Exception {
   }

   static void parseAlterView() throws Exception {
      testGauss("DROP VIEW a_view  ");
   }

   static void parseAlterSchema() throws Exception {
      testGauss("ALTER SCHEMA schema_name  WITH  BLOCKCHAIN");
   }

   static void parseDrop() throws Exception {
      testGauss("DROP SCHEMA public CASCADE;");
      testGauss("DROP SCHEMA IF EXISTS mystuff CASCADE;");
      testGauss("DROP SCHEMA mystuff,public RESTRICT;");
      testGauss("DROP TABLE IF EXISTS films RESTRICT;");
      testGauss("DROP TABLE IF EXISTS schema1.mytable RESTRICT;");
      testGauss("DROP TABLE mytable,schema2.films CASCADE;");
   }

   static void parseAlterTable() throws Exception {
      testGauss("alter table only PUBLIC.person rename name to person_name");
      testGauss("alter table if exists only mytable OWNER to root ,set tablespace my_new_tablespace ");
   }

   static void parseUpdate() throws Exception {
   }

   static void parseCreateView() throws Exception {
   }

   static void parseCreateTable() throws Exception {
      testGauss("CREATE TABLE test.store_returns_t1 AS SELECT * FROM test.t_dept WHERE id > '4795' WITH NO DATA");
   }

   static void parseInsert() throws Exception {
      testGauss("insert into t_dept select * from t_dept");
   }

   static void parseDelete() throws Exception {
      testGauss("DELETE FROM rank WHERE id IN (SELECT id FROM names WHERE name = 'Hannah');");
   }

   static void parseTruncate() throws Exception {
   }

   static void parseSelect1() throws Exception {
      testGauss("select d.dept_id, d.parent_id, d.ancestors, d.dept_name, d.order_num, d.leader, d.phone, d.email, d.status,\n\t\t\t(select dept_name from sys_dept where dept_id = d.parent_id) parent_name\n\t\tfrom sys_dept d\n\t\twhere d.dept_id = ? -- appUsername=");
   }

   static void parseShow() throws Exception {
      testGauss("SHOW timezone ");
   }

   static void parseSelect() throws Exception {
      testGauss("select * from films");
      testGauss("select code,title,kind from films f");
      testGauss("select 1");
      testGauss("select * from (select code from films) a");
      testGauss("select code,title,kind from films a left join person b on a.did=b.id");
      testGauss("select code,title,kind,age from films a left join person b on a.did=b.id");
      testGauss("select * from (select code,title,kind from films a left join person b on a.did=b.id) t");
      testGauss("select * from (select code,title,kind,age from films a left join person b on a.did=b.id) t");
      testGauss("select * from person union all select * from person2 t");
      testGauss("select * from person t(id1,name1,sex1) where t.id1='1'");
      testGauss("select id1,name,sex from person3 as t(id1,name,sex)");
      testGauss("SELECT  username as name from person ;");
      testGauss("SELECT  'Hello World' as hello ;");
      testGauss("select title from person where title is not null");
      testGauss("select * from substr('hello world',2,3)");
      testGauss("select * from  person * ");
      testGauss("select * from  ( select * from person *) t");
      testGauss("select * from  ( select id,name,sex from person *) t (tid,tname,tsex)");
      testGauss("select * from substr('hello world',2,3) with ordinality");
      testGauss("SELECT * FROM substr('hello world',2,3) WITH ORDINALITY as t (name)");
      testGauss("select * from ccc3() as (name varchar)");
      testGauss("select * from ccc3(1,'2') as (name varchar,addr varchar)");
      testGauss("select * from person natural join person1");
      testGauss("select * from person  join person1 on person.id=person1.id");
      testGauss("select * from person inner join person1 on person.id=person1.id");
      testGauss("select * from person LEFT  join person1 on person.id=person1.id");
      testGauss("select * from person left OUTER  join person1 on person.id=person1.id");
      testGauss("select * from person RIGHT   join person1 on person.id=person1.id");
      testGauss("select * from person RIGHT  OUTER  join person1 on person.id=person1.id");
      testGauss("select * from person FULL     join person1 on person.id=person1.id");
      testGauss("select * from person FULL   OUTER  join person1 on person.id=person1.id");
      testGauss("select * from person CROSS JOIN person1");
      testGauss("select * from person inner join person1 using(id);");
      testGauss("select * from person LEFT  join person1  using(id);");
      testGauss("select * from person left OUTER  join person1  using(id);");
      testGauss("select * from person RIGHT   join person1   using(id);");
      testGauss("select * from person RIGHT  OUTER  join person1  using(id);");
      testGauss("select * from person FULL     join person1  using(id);");
      testGauss("select * from person FULL   OUTER  join person1  using(id);");
      testGauss("with films_with(aa,bb,cc) as ( select code,title,did from films)\nselect * from films_with;");
      testGauss("WITH RECURSIVE all_managers(aa, bb, cc, level) AS (\n    SELECT eid, ename, manager_id, 0 FROM employees WHERE ename='Bob'\n    UNION ALL\n    SELECT e.eid, e.ename, e.manager_id, level+1\n    FROM employees e\n    JOIN all_managers am ON e.eid = am.manager_id\n)\nSELECT * FROM all_managers ORDER BY level DESC;");
      testGauss("WITH RECURSIVE foo(i) AS (\n    SELECT 1\n  UNION ALL\n    SELECT i+1 FROM (SELECT * FROM foo UNION SELECT 0) bar\n)\nSELECT * FROM foo LIMIT 5;");
      testGauss("WITH RECURSIVE foo(i) AS (\n    SELECT 1\n  UNION ALL\n    SELECT i+1 from foo\n)\nSELECT * FROM foo LIMIT 5;\n");
      testGauss("WITH  foo  AS (\n\t\tSELECT 1\n\t)\nSELECT *\nFROM foo");
      testGauss("WITH  foo  AS (\n\t\tvalues (1,2),(3,4)\n\t\t\t)\nSELECT *\nFROM foo");
      testGauss("WITH  foo  AS (\n\t\tselect * from person\n\t\t\t)\nSELECT *\nFROM foo");
      testGauss("WITH  foo  AS (\n\t\tinsert into person values('3','王五','女','20','财务',3000) returning *\n\t\t)\nSELECT *\nFROM foo");
      testGauss("\nWITH  foo  AS (\n\t\tdelete from person where id='5' returning *\n\t\t)\nSELECT *\nFROM foo");
      testGauss("select * from person union select * from person1;");
      testGauss("select * from person union all select * from person1;");
      testGauss("select * from person union distinct select * from person1;");
      testGauss("select * from person INTERSECT  select * from person1;");
      testGauss("select * from person INTERSECT  all select * from person1;");
      testGauss("select * from person INTERSECT  distinct select * from person1;");
      testGauss("select * from person EXCEPT  select * from person1;");
      testGauss("select * from person EXCEPT  all select * from person1;");
      testGauss("select * from person EXCEPT  distinct select * from person1;");
      testGauss("with t as (select * from person) select * from t");
      testGauss("with t as (select * from person) table only t");
      testGauss("table only person");
      testGauss("table person *");
      testGauss("select * from (\n\twith t as (select * from person) table only t\n) tx join person1 p  on tx.id=p.id");
      testGauss("select  distinct on (name='李四') * from person t  ");
      testGauss("select  all  * from person t ");
      testGauss("select  all  * from person t where id='1' ");
      testGauss("select * from person as t");
      testGauss("\nwith  tb1 as (select  * from films)\nselect * from  person as t(a,b,c,d)");
      testGauss("select * from (values (1),(2),(3)) t (id)");
      testGauss("select * from (\nSELECT *\nFROM \n(VALUES (1),(2),(3)) t (id)\n) t1 cross join person p  \n");
      testGauss("select * from substr('hello world',2,3)");
      testGauss("SELECT name, rank() OVER (mywindow) FROM person\nGROUP BY name\nWINDOW mywindow AS (ORDER BY sum(salary));\n");
      testGauss("\t\nSELECT \n\tname, rank() OVER ( mywindow) ,rank()OVER(win1)\nFROM person\nGROUP BY name\nWINDOW mywindow AS (ORDER BY sum(salary))\n, win1 as (mywindow )");
      testGauss("select * from films as t(xx,n1,n2,n3)");
      testGauss("select * from (select * from person as t(x,y,z)) t1x   (a,b,c,d)");
      testGauss("select * from (select * from films) t");
      testGauss("select id ,count(*) from person group by id");
      testGauss("select id , addr,count(*) from t1 group by id,addr");
      testGauss("select id ,count(*) from t1 group by id+1");
      testGauss("select id ,count(*) from t1 group by ROLLUP (id)");
      testGauss("select id ,count(*) from t1 group by CUBE  (id)");
      testGauss("select id,addr ,count(*) from t1 group by ROLLUP (id,addr)");
      testGauss("select id,addr ,count(*) from t1 group by CUBE  (id,addr)");
      testGauss("select id ,count(*) from t1 group by GROUPING SETS (id)");
      testGauss("select id ,count(*) from t1 group by GROUPING  SETS (  ROLLUP (id) )");
      testGauss("select id ,count(*) from t1 group by GROUPING  SETS (  CUBE (id) )");
      testGauss("select id ,count(*) from t1 group by id having count(*)>2");
      testGauss("select id , addr,count(*) from t1 group by id,addr having count(*)>2");
      testGauss("select id ,count(*) from t1 group by id+1 having count(*)>2");
      testGauss("select id ,count(*) from t1 group by ROLLUP (id) having count(*)>2");
      testGauss("select id ,count(*) from t1 group by CUBE  (id) having count(*)>2");
      testGauss("select id,addr ,count(*) from t1 group by ROLLUP (id,addr) having count(*)>2");
      testGauss("select id,addr ,count(*) from t1 group by CUBE  (id,addr) having count(*)>2");
      testGauss("select id ,count(*) from t1 group by GROUPING SETS (id) having count(*)>2");
      testGauss("select id ,count(*) from t1 group by GROUPING  SETS (  ROLLUP (id) ) having count(*)>2");
      testGauss("select id ,count(*) from t1 group by GROUPING  SETS (  CUBE (id) ) having count(*)>2");
      testGauss("select * from t1 order by id");
      testGauss("select * from t1 order by id asc");
      testGauss("select * from t1 order by id desc");
      testGauss("select * from t1 order by id nulls first");
      testGauss("select * from t1 order by id asc nulls first");
      testGauss("select * from t1 order by id desc nulls first");
      testGauss("select * from t1 order by id nulls LAST");
      testGauss("select * from t1 order by id asc nulls LAST");
      testGauss("select * from t1 order by id desc nulls LAST");
      testGauss("select * from t1 order by id using operator <");
      testGauss("select * from t1 order by id using operator <=");
      testGauss("select * from public.person ");
      testGauss("select * from person ");
      testGauss("select * from t1 limit 1");
      testGauss("select * from t1 limit all");
      testGauss("select * from person order by id limit 2 offset 1");
      testGauss("select * from person order by id offset 1 fetch next 5 rows only");
      testGauss("select * from person order by id offset 1 fetch next 5 row only");
      testGauss("select * from person order by id offset 1 fetch first 5 rows only");
      testGauss("select * from person order by id offset 1 fetch first 5 row only");
      testGauss("select * from person order by id offset 1 rows fetch next 5 rows only");
      testGauss("select * from person order by id offset 1 rows fetch next 5 row only");
      testGauss("select * from person order by id offset 1 rows fetch first 5 rows only");
      testGauss("select * from person order by id offset 1 rows fetch first 5 row only");
      testGauss("select * from person order by id offset 1 row fetch next 5 rows only");
      testGauss("select * from person order by id offset 1 row fetch next 5 row only");
      testGauss("select * from person order by id offset 1 row fetch first 5 rows only");
      testGauss("select * from person order by id offset 1 row fetch first 5 row only");
      testGauss("select * from t1 offset 10 ");
      testGauss("select * from t1 offset 10 row");
      testGauss("select * from t1 offset 10 rows");
      testGauss("select * from person fetch first 10 row only");
      testGauss("select * from person fetch next 10  rows only");
      testGauss("select * from person for update");
      testGauss("select * from person for no key update");
      testGauss("select * from person for  SHARE");
      testGauss("select * from person for KEY SHARE");
      testGauss("select * from person for update of person");
      testGauss("select * from person t for update of t");
      testGauss("select * from person t1,person1 t2 for update of t1,t2\n");
      testGauss("select * from person t1,person1 t2 for update of t1,t2 nowait");
      testGauss("select * from person t1,person1 t2 for key share of t2 nowait for update of t1 nowait");
      testGauss("select * into public.person4  from public.person ");
      testGauss("select * into temporary person_temp  from public.person ");
      testGauss("select * into person from person1 as t(xx,n1,n2,n3)");
      testGauss("select * into temporary person_temp1  from public.person ;");
      testGauss("select * into temp person_temp2  from public.person ;");
      testGauss("select * into UNLOGGED person_temp3  from public.person ;");
      testGauss("select * into temporary table person_temp1  from public.person ;");
      testGauss("select * into temp table person_temp2  from public.person ;");
      testGauss("select * into UNLOGGED table person_temp3  from public.person ;");
      testGauss("select * into UNLOGGED table person_temp3  from public.person for update;");
   }

   static void testGauss(String sql) throws Exception {
      Map<String, String> params = new HashMap();
      params.put("sql", sql);
      params.put("dbType", "gauss");
      params.put("schema", "test");
      params.put("url", "jdbc:postgresql://192.168.2.108:5432/db_tpcc");
      params.put("replaceSchema", ".*");
      params.put("replaceNewTable", "people_bk");
      params.put("replaceTable", ".*");
      params.put("replaceColumn", ".*title.*");
      params.put("expression", "concat(substr(${value},1,1),'**')");
      params.put("configPath", "D:/masking-mysql.properties");
      params.put("limit", "3");
      System.out.println("...................................................");
      System.out.println("\n\n[0]********************************************************************************");
      System.out.println("--------------------- source sql    ------------------------------------------------");
      System.out.println(sql);
      System.out.println("---------------------/source sql    ------------------------------------------------");
      System.out.println("---------------------parse structure------------------------------------------------");
      SqlUtil sqlUtil = new SqlUtil();
      Map<String, Object> s2 = sqlUtil.parseSqlStructure(params);
      System.out.println("\n\n====schemaMap====");
      Map<String, Object> schemaMap = (Map)s2.get("schemaMap");
      if (schemaMap != null && !schemaMap.isEmpty()) {
         for(Map.Entry<String, Object> item : schemaMap.entrySet()) {
            System.out.println((String)item.getKey() + "\t" + item.getValue());
         }
      }

      System.out.println("\n\n====tableMap====");
      Map<String, Object> tableMap = (Map)s2.get("tableMap");
      if (tableMap != null && !tableMap.isEmpty()) {
         for(Map.Entry<String, Object> item : tableMap.entrySet()) {
            System.out.println((String)item.getKey() + "\t" + item.getValue());
         }
      }

      System.out.println("\n\n====whereMap====");
      Map<String, Object> whereMap = (Map)s2.get("whereMap");
      if (whereMap != null && !whereMap.isEmpty()) {
         for(Map.Entry<String, Object> item : whereMap.entrySet()) {
            System.out.println((String)item.getKey() + "\t" + item.getValue());
         }
      }

      System.out.println("\n\n====whereList====");
      ArrayList<Map<String, Object>> whereList = (ArrayList)s2.get("whereList");
      if (whereList != null && !whereList.isEmpty()) {
         for(Map<String, Object> where : whereList) {
            for(Map.Entry<String, Object> item : where.entrySet()) {
               System.out.println((String)item.getKey() + "\t" + item.getValue());
            }
         }
      }

      System.out.println("\n\n====columnList====");
      ArrayList<Column> columnList = (ArrayList)s2.get("columnList");
      String[] fields = new String[]{"columnName", "columnAlisa", "fullName", "nowName", "dataType", "schema", "table", "value", "expr"};
      int[] size = max(fields, columnList);

      for(int j = 0; j < fields.length; ++j) {
         fields[j] = String.format("%" + size[j] + "s", fields[j]);
      }

      System.out.println(StringJoin.join(fields, "  "));
      if (columnList != null && !columnList.isEmpty()) {
         for(Column item : columnList) {
            StringBuilder output = new StringBuilder();
            printColumn(item, output, size);
            System.out.println(output);
         }
      }

      System.out.println("\n\n====SQL====");
      System.out.println(s2.get("sql"));
      System.out.println("\n\n====/SQL====");
      System.out.println("---------------------/parse structure------------------------------------------------");
   }

   static void printColumn(Column col, StringBuilder s, int[] size) {
      s.append(format(col.getColumnName(), size[0])).append("  ").append(format(col.getColumnAlisa(), size[1])).append("  ").append(format(col.getFullName(), size[2])).append("  ").append(format(col.getNowName(), size[3])).append("  ").append(format(col.getDataType(), size[4])).append("  ").append(format(col.getSchema(), size[5])).append("  ").append(format(col.getTable(), size[6])).append("  ").append(format(col.getValue(), size[7])).append("  ").append(format(col.getExpr(), size[8]));
   }

   static String format(Object value, int size) {
      return String.format("%" + size + "s", value);
   }

   static int[] max(String[] fields, List<Column> columns) {
      int[] size = new int[fields.length];

      try {
         for(int i = 0; i < fields.length; ++i) {
            String name = fields[i];
            name = (name.charAt(0) + "").toUpperCase() + name.substring(1);
            String method = "get" + name;
            Method m = Column.class.getDeclaredMethod(method);
            int max = name.length();
            if (columns != null) {
               for(Column column : columns) {
                  Object value = m.invoke(column);
                  if (value != null) {
                     int s = value.toString().length();
                     max = max < s ? s : max;
                  }
               }
            }

            size[i] = max;
         }
      } catch (Exception e) {
         e.printStackTrace();
      }

      return size;
   }

   static void parseTest() throws Exception {
      testGauss("\nWITH  foo  AS (\n\t\tdelete from person where id='5' returning *\n\t\t)\nSELECT *\nFROM foo");
   }
}
