package syndiff;

import java.io.File;
import java.net.MalformedURLException;
import java.net.URL;
import java.net.URLClassLoader;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;


public class MetaDic {
	private Map<String, String> dbcfg;
	private Connection conn;
	private String LibPath = "libs";
	private static MetaSqls sqls = new MetaSqls();
	public MetaDic(Map<String, String> cfg) {
		dbcfg = cfg;
	}
	public List<String> getAllSchemas() {
		String sql = sqls.schemasqls.get(dbcfg.get("dbtype"));
		return this.getRows(sql);
	}
	public List<String> getTables(String schemasfilter) {
		String sql = sqls.tablesqls.get(dbcfg.get("dbtype")).replace("$schemalist$", schemasfilter);
		return this.getRows(sql);
	}
	private String formatTable(String tbl) {
		switch (dbcfg.get("dbtype")) {
		case "tidb":
		case "mysql": return "`" + tbl.replace(".", "`.`") + "`";
		case "oracle": return "\"" + tbl.replace(".", "\".\"") + "\"";
			default:
		}
		return  tbl;
	}
	public List<String> getTablesWithRows(String schemasfilter, int threadcount, int timeout) {
		String sql = sqls.tablesqls.get(dbcfg.get("dbtype")).replace("$schemalist$", schemasfilter);
		List<String> tbls = getRows(sql);
		List<Thread> threads = new ArrayList<Thread>();
		List<String> ret = new ArrayList<String>();
		Integer[] index = new Integer[1];
		index[0] = -1;
		for (int i = 0; i < threadcount; i++) {
			Thread t = new Thread(() -> {
				try (Connection con = getConnection();
					 Statement st = con.createStatement();
					){
						st.setQueryTimeout(timeout);
						int num = -1;
						for(;;) {
							synchronized(index) {
								index[0]++;
								num = index[0];
							}
							if (num >= tbls.size()) {
								break;
							}
							try {
								ResultSet rs = st.executeQuery("select count(1) from " + formatTable(tbls.get(num)));
								rs.next();
								synchronized(index) {
									ret.add(String.format("%s: %s", tbls.get(num), rs.getString(1)));
								}
								rs.close();
							}catch (SQLException ex) {
								System.err.println(tbls.get(num) + ":" + ex.getMessage());
								synchronized(index) {
									ret.add(String.format("%s: %s", tbls.get(num), -1));
								}
							}
						}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			});
			threads.add(t);
			t.start();
		}
		
		threads.forEach(x -> {
			try {
				x.join();
			} catch (InterruptedException e) {
				e.printStackTrace();
			}
		});
		
		return ret;
	}
	public List<String> getViews(String schemasfilter) {
		String sql = sqls.viewsqls.get(dbcfg.get("dbtype")).replace("$schemalist$", schemasfilter);
		return this.getRows(sql);
	}
	public List<String> getIndexes(String schemasfilter) {
		if(! dbcfg.get("dbtype").equals("oracle")) {
			String sql = sqls.indexsqls.get(dbcfg.get("dbtype")).replace("$schemalist$", schemasfilter);
			return this.getRows(sql);
		}
		return getOracleIndexes(schemasfilter);
		
	}
	private List<String> getOracleIndexes(String schemasfilter) {
		String sql = sqls.indexsqls.get(dbcfg.get("dbtype")).replace("$schemalist$", schemasfilter);
		List<String> index = this.getRows(sql);
		sql = sqls.indexsqls.get("oracle_fun_index").replace("$schemalist$", schemasfilter);
		try(
				Statement st = conn.createStatement()
			){
				ResultSet rs = st.executeQuery(sql);
				rs.setFetchSize(1000);
				Map<String,String> map = new HashMap<String,String>(128);
				while (rs.next()) {
					map.put(rs.getString(1),rs.getString(2).replace("\"", ""));
				}
				rs.close();
				if (map.size() == 0) return index;
				String s = "";
				for (int i=0; i < index.size(); i++) {
					s = index.get(i);
					if (s.indexOf('#') < 0) continue;
					String[] k = s.split("\\.");
					for(String idx: k[3].split(",")) {
						if(!idx.startsWith("#")) continue;
						String key = String.format("%s.%s.%s", k[0],k[1],idx);
						String funvalue = map.get(key);
						if(funvalue != null) {
							s = s.replaceFirst(idx, funvalue);
						}
					}
					index.set(i, s);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		return index;
	}
	public boolean initConnection() {
		conn = getConnection();
		return conn != null;
	}
	private Connection getConnection() {
		String driver = "";
		String url = "";
		String user = dbcfg.get("user");
		String pswd = dbcfg.get("password");
		String dbtype = dbcfg.get("dbtype");
		Properties props = new Properties();
		if (dbtype == null || dbtype.trim().length() == 0) {
			dbtype = "tidb";
			dbcfg.put("dbtype", "tidb");
		} else {
			dbtype = dbtype.trim().toLowerCase();
			dbcfg.put("dbtype", dbtype);
		}
		switch (dbtype) {
		case "tidb":
		case "mysql":
			driver = "com.mysql.cj.jdbc.Driver";
			url = getConnectURL("tidb");
			break;
		case "oracle":
			driver = "oracle.jdbc.driver.OracleDriver";
			url = getConnectURL("oracle");
			break;
		default:
			System.out.println("unsupport database type [" + dbtype + "], please check config.");
			return null;
		}
		
		try {
			List<URL> urls = getURLs();
			URLClassLoader	loader = new URLClassLoader(urls.toArray(new URL[urls.size()]));
			Class<?> clazz	= loader.loadClass(driver);
			Driver dr = (Driver) clazz.getDeclaredConstructor().newInstance();
			
			props.put("user",user);
			props.put("password", pswd);
			Connection ret = dr.connect(url, props);
			//loader.close();
			return ret;
		} catch (Exception e) {
			e.printStackTrace();
			System.err.println("please make sure the matched jdbc driver files(*.jar) were in the libs directory.");
		}
		return null;
	}
	private List<URL> getURLs() {
		String spath = new File("").getAbsolutePath() + File.separator + LibPath;
		File jarlib = new File(spath);
		List<URL> ret = new ArrayList<URL>();
		if(jarlib.isDirectory()){
			Arrays.asList(jarlib.listFiles()).stream()
			.filter(x -> x.getName().endsWith(".jar"))
			.forEach(x -> {
				try {
					URL u = x.toURI().toURL();
					ret.add(u);
				}catch (MalformedURLException e) {e.printStackTrace();}
			});
		}
		return ret;
	}
	private String getConnectURL(String dbtype) {
		String sid;
		String host = dbcfg.get("host");
		String port = dbcfg.get("port");
		
		if (dbtype.equals("oracle")) {
			String tnsname = dbcfg.get("oracle-tns-name");
			if (tnsname != null && tnsname.trim().length() > 0) {
				return "jdbc:oracle:thin@" + tnsname;
			}
			sid = dbcfg.get("oracle-service-name");
			if (sid == null || sid.trim().length() == 0) {
				sid = "";
			}
			String s = String.format("jdbc:oracle:thin:@//%s:%s/%s",host,port,sid);
			return s;
		}
		if (dbtype.equals("tidb") || dbtype.equals("mysql")) {
			sid = dbcfg.get("dbname");
			if (sid == null || sid.trim().length() == 0) {
				sid = "";
			}
			return String.format("jdbc:mysql://%s:%s/%s",host,port,sid);
		}
		
		return "";
	}
	private List<String> getRows(String sql) {
		List<String> ret = null;
		try(
			Statement st = conn.createStatement()
		){
			//st.setFetchSize(1000);
			ResultSet rs = st.executeQuery(sql);
			rs.setFetchSize(1000);
			ret = new ArrayList<String>(128);
			while (rs.next()) {
				ret.add(rs.getString(1));
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
		return ret;
	}
}