package syndiff;

import java.util.HashMap;
import java.util.Map;

public class MetaSqls {
	public final Map<String, String> tablesqls;
	public final Map<String, String> viewsqls;
	public final Map<String, String> indexsqls;
	public final Map<String, String> schemasqls;
	
	public MetaSqls() {
		tablesqls = new HashMap<String, String>();
		viewsqls = new HashMap<String, String>();
		indexsqls = new HashMap<String, String>();
		schemasqls = new HashMap<String, String>();
	
		tablesqls.put("oracle","select owner||'.'||table_name from all_tables where lower(owner) in ($schemalist$) order by 1");
		viewsqls.put("oracle","select owner||'.'||view_name from all_views where lower(owner) in ($schemalist$) order by 1");
		indexsqls.put("oracle","select  a.table_owner || '.' || a.table_name || '.' ||"
				+ "	case when b.uniqueness = 'UNIQUE' then decode(c.constraint_type,NULL,'UNI','U','UNI','P','PRI','IDX') else 'IDX' end "
				+ "	||'.'||a.cols "
				+ " from (select a1.table_owner,a1.table_name,a1.index_name, "
				+ "        listagg(case when a2.column_expression is null then a1.column_name else '#'||a2.index_name || '#' || a2.column_position||'#' end,',') within group(order by a1.column_position) cols "
				+ " from all_ind_columns a1 "
				+ " left join  all_ind_expressions a2 " 
				+ "  on  a1.table_owner = a2.table_owner "
				+ "  and a1.table_name = a2.table_name "
				+ "  and a1.index_name = a2.index_name "
				+ "  and a1.column_position = a2.column_position "
				+ " where lower(a1.table_owner) in ($schemalist$) "
				+ " group by a1.table_owner,a1.table_name,a1.index_name "
				+ ") a join all_indexes b "
				+ "on a.table_owner = b.table_owner "
				+ "and a.table_name = b.table_name "
				+ "and a.index_name = b.index_name "
				+ "left join all_constraints c "
				+ " on a.table_owner = c.owner "
				+ "and a.table_name = c.table_name "
				+ "and a.index_name = c.index_name "
				+ "order by a.table_owner,a.table_name "
				);
		indexsqls.put("oracle_fun_index", "select table_owner ||'.' || table_name || '.' ||'#'||index_name || '#' || column_position||'#',column_expression from all_ind_expressions "
				+    "where lower(table_owner) IN ($schemalist$) "
				);
		
		tablesqls.put("tidb","select concat(table_schema,'.',table_name) from information_schema.tables where lower(table_type) = 'base table' and lower(table_schema) in ($schemalist$) order by 1");
		viewsqls.put("tidb","select concat(table_schema,'.',table_name) from information_schema.views where lower(table_schema) in ($schemalist$) order by 1");
		indexsqls.put("tidb", "select concat(table_schema,'.',table_name,'.',case when key_name = 'PRIMARY' then 'PRI' when non_unique = 1 then 'IDX' else 'UNI' end,'.',cols) from (select table_schema,table_name,key_name,non_unique,group_concat(coalesce(replace(expression,'`',''),column_name) order by seq_in_index) cols from information_schema.tidb_indexes where lower(table_schema) in ($schemalist$)  group by 1,2,3,4) t order by 1");
	
		tablesqls.put("mysql", tablesqls.get("tidb"));
		viewsqls.put("mysql", viewsqls.get("tidb"));
		indexsqls.put("mysql", "select concat(table_schema,'.',table_name,'.',case when index_name = 'PRIMARY' then 'PRI' when non_unique = 1 then 'IDX' else 'UNI' end,'.',cols) from (select table_schema,table_name,index_name,non_unique,group_concat(column_name order by seq_in_index) cols from information_schema.STATISTICS  where lower(table_schema) in ($schemalist$)  group by 1,2,3,4) t order by 1");
		
		schemasqls.put("tidb","select schema_name from information_schema.schemata where lower(schema_name) not in ('information_schema','metrics_schema','performance_schema','mysql','test')");
		schemasqls.put("mysql", "select schema_name from information_schema.schemata where lower(schema_name) not in ('information_schema','metrics_schema','performance_schema','mysql','test','sys')");
		schemasqls.put("oracle", "select username from sys.all_users where username not in('SYS','SYSTEM','OUTLN','LBACSYS','XS$NULL','SYS$UMF','OJVMSYS','GGSYS','OLAPSYS','ANONYMOUS','DVF','DBSNMP','GSMADMIN_INTERNAL','MDSYS','XDB','WMSYS','CTXSYS','DBSFWUSER','DVSYS','APPQOSSYS','GSMUSER','MDDATA','SI_INFORMTN_SCHEMA','ORDDATA','GSMROOTUSER','SYSDG','SYSKM','ORACLE_OCM','GSMCATUSER','REMOTE_SCHEDULER_AGENT','ORDSYS','SYSBACKUP','SYSRAC','DIP','AUDSYS','ORDPLUGINS')");
	}
}