package slite.lib_java;

import java.sql.*;
import java.util.*;
import java.util.Map.Entry;

import com.google.gson.JsonArray;
import com.google.gson.JsonObject;

public class DbCockroach
{
	public String dbName;
	public String dbUser  = "root";
	private String dbPass = "hcet";
	public String dbHost = "cockroach.intra.techss.co.za";
	public int dbPort = 26257;

	private final HashMap<Thread, PgSqlConnection> connections = new HashMap<Thread, PgSqlConnection>();	

	public DbCockroach()
	{
		this.loadFromEnv();
	}

	public DbCockroach(String database)
	{
		this.dbName = database; // This is usually the test / development database
		this.loadFromEnv();
	}
	
	public void loadFromEnv()
	{
		String tmp;
		tmp = System.getenv("SLITE_LIB_JAVA_COCKROACHDB_NAME");
		if(tmp!=null) this.dbName = tmp;
		tmp = System.getenv("SLITE_LIB_JAVA_COCKROACHDB_USER");
		if(tmp!=null) this.dbUser = tmp;
		tmp = System.getenv("SLITE_LIB_JAVA_COCKROACHDB_PASS");
		if(tmp!=null) this.dbPass = tmp;
		tmp = System.getenv("SLITE_LIB_JAVA_COCKROACHDB_HOST");
		if(tmp!=null) this.dbHost = tmp;
		tmp = System.getenv("SLITE_LIB_JAVA_COCKROACHDB_PORT");
		if(tmp!=null) this.dbPort = Integer.parseInt(tmp);
	}

	public void setPass(String pass)
	{
		this.dbPass = pass;
	}

	public String getConnectionString()
	{
		return "jdbc:postgresql://"+dbHost+":"+dbPort+"/"+dbName;
	}

	public PgSqlConnection getConnection() throws Exception
	{
		PgSqlConnection connection;
		synchronized(connections)
		{
			Thread thread = Thread.currentThread();
			connection = this.connections.get(thread);
			if(connection==null || connection.con == null || connection.con.isClosed())
			{
				connection = new PgSqlConnection();
				connection.con = DriverManager.getConnection(this.getConnectionString(), dbUser, dbPass);
				this.connections.put(thread, connection);
			}
			
			connection.lastUsed = System.currentTimeMillis();
		}
		
		return connection;
	}	


	public int put(String sql) throws Exception
	{
		int updateCount = 0;
		
		try
		{
			PgSqlConnection con = this.getConnection();
			Statement stmt = con.con.createStatement();
			updateCount = stmt.executeUpdate(sql);
			con.lastError = null;

			SQLWarning warnings = stmt.getWarnings();
			if(warnings!=null) System.err.println(warnings.toString());
			stmt.close();
		}
		catch(Exception e)
		{
			System.err.println(sql);
			System.err.println(e);
			e.printStackTrace();
		}
		
		return updateCount;
	}

	public DbRows get(String sql)
	{
		return this.get(sql,0);
	}

	public DbRows get(String sql, int secondsTimeout)
	{
		DbRows result = null;
		Statement stmt = null;
		ResultSet resultSet = null;
		try
		{
			PgSqlConnection con = this.getConnection();
			stmt = con.con.createStatement();
			if(secondsTimeout>0) stmt.setQueryTimeout(secondsTimeout);
			resultSet = stmt.executeQuery(sql);
			ResultSetMetaData metaData = resultSet.getMetaData();
			int colCount = metaData.getColumnCount();
			String[] colNames = new String[colCount];
			int rowIndex = 0;
			
			while(resultSet.next())
			{
				if(result==null) result = new DbRows();
				DbRow row = new DbRow();
				
				for(int colIndex=0;colIndex<colCount;colIndex++)
				{
					String value = resultSet.getString(colIndex+1);
					if(rowIndex==0) colNames[colIndex] = metaData.getColumnName(colIndex+1);
					
					row.put(colNames[colIndex], value);
				}
				result.add(row);
				rowIndex ++;
			}
			
			resultSet.close();
			stmt.close();
		}
		catch(Exception e)
		{
			try
			{
				if(stmt!=null) stmt.close();
				if(resultSet!=null) resultSet.close();
			}
			catch(Exception e1){}
			System.err.println(sql);
			e.printStackTrace();
		}
		return result;
	}

	public JsonObject getJsonRow(String sql)
	{
		var listResult = this.getJson(sql);
		if(listResult == null || listResult.size()==0) return null;
		
		return listResult.get(0).getAsJsonObject();
	}

	public JsonArray getJson(String sql)
	{
		JsonArray result = null;
		Statement stmt = null;
		ResultSet resultSet = null;
		try
		{
			PgSqlConnection con = this.getConnection();
			stmt = con.con.createStatement();
			resultSet = stmt.executeQuery(sql);
			ResultSetMetaData metaData = resultSet.getMetaData();
			int colCount = metaData.getColumnCount();
			String[] colNames = new String[colCount];
			int rowIndex = 0;
			
			while(resultSet.next())
			{
				if(result==null) result = new JsonArray();
				var row = new JsonObject();
				
				for(int colIndex=0;colIndex<colCount;colIndex++)
				{
					var colType = metaData.getColumnTypeName(colIndex+1);
					// System.out.println(colType);
					if(rowIndex==0) colNames[colIndex] = metaData.getColumnName(colIndex+1);
					var colName = colNames[colIndex];

					switch(colType)
					{
						case "jsonb":
							var jsonString = resultSet.getString(colIndex+1);
							if(jsonString==null)
							{
								row.addProperty(colName, (String)null);
							}
							else
							{
								var value = GsonUtil.fromJson(jsonString);
								row.add(colName, value);
							}
						break;
						default:
							var value = resultSet.getString(colIndex+1);
							row.addProperty(colName, value);
						break;
					}
				}
				result.add(row);
				rowIndex ++;
			}
			
			resultSet.close();
			stmt.close();
		}
		catch(Exception e)
		{
			try
			{
				if(stmt!=null) stmt.close();
				if(resultSet!=null) resultSet.close();
			}
			catch(Exception e1){}
			System.err.println(sql);
			e.printStackTrace();
		}
		return result;
	}

	public String getCell(String sql)
	{
		Statement stmt = null;
		ResultSet resultSet = null;
		try
		{
			PgSqlConnection con = this.getConnection();
			stmt = con.con.createStatement();
			resultSet = stmt.executeQuery(sql);
			if(resultSet.next())
			{
				String result = resultSet.getString(1);
				stmt.close();
				resultSet.close();
				return result;
			}
		}
		catch(Exception e)
		{
			try
			{
				if(stmt!=null) stmt.close();
				if(resultSet!=null) resultSet.close();
			}
			catch(Exception e1){}			
			System.err.println(sql);
			e.printStackTrace();
		}
		return null;
	}

	public void insert(String table, DbRows rows) throws Exception
	{
		this.insert(table, rows, new String[]{"id"});
	}

	public void insert(String table, DbRows rows, String key) throws Exception
	{
		this.insert(table, rows, new String[]{key});
	}

	public void insert(String table, DbRows rows, String[] keys) throws Exception
	{
		StringBuilder values = new StringBuilder();
		StringBuilder fields = new StringBuilder();
		StringBuilder primaryKeys = new StringBuilder();
		StringBuilder excluded = new StringBuilder();
		
		for(var item : keys)
		{
			if(primaryKeys.length()>0) primaryKeys.append(",");
			primaryKeys.append(quoteField(item));
		}
		
		HashSet<String> cols = new HashSet<>();
		for(var item : rows) cols.addAll(item.keySet()); // gather all the columns in all the rows.
		
		for(var col : cols)
		{
			if(fields.length()>0) fields.append(",");
			fields.append(quoteField(col));
			
			boolean isKey = false;
			for(var k : keys)
			{
				if(k.equals(col)) isKey = true;
			}
			
			if(!isKey)
			{
				if(excluded.length()>0) excluded.append(",\n");
				excluded.append(quoteField(col)).append(" = excluded.").append(quoteField(col));
			}
		}
		
		for(var item : rows)
		{
			StringBuilder row = new StringBuilder();
			for(var col : cols)
			{
				if(row.length()>0) row.append(",");
				var cell = item.get(col);
				if(cell==null) row.append("NULL");
				else row.append(quoteValue(cell));
			}
			if(values.length()>0) values.append(",\n");
			values.append("(").append(row).append(")");
		}
		
		StringBuilder sql = new StringBuilder();
		sql.append("INSERT INTO ").append(quoteField(table)).append(" (").append(fields).append(")\n");
		sql.append("VALUES \n").append(values).append("\n");
		if(excluded.length()>0)
			sql.append("ON CONFLICT (").append(primaryKeys).append(") DO UPDATE SET\n").append(excluded);
		
		this.put(sql.toString());
	}

	public void insert(String table, DbRow row) throws Exception
	{
		this.insert(table, row, new String[]{"id"});
	}

	public void insert(String table, DbRow row, String key) throws Exception
	{
		this.insert(table, row, new String[]{key});
	}

	public void insert(String table, DbRow row, String[] keys) throws Exception
	{
		DbRows rows = new DbRows();
		rows.add(row);
		insert(table, rows, keys);
	}
	
	public static String quoteValue(Object value)
	{
		return '\''+value.toString().replace("'", "''")+"'";
	}
	
	public static String quoteField(Object value)
	{
		return '"'+value.toString().replace("\"", "\"\"")+'"';
	}

	public static String quoteSymbol(String symbol)
	{
		return quoteField(symbol);
	}
	

	public static String queryFromFile(String fileName) throws Exception
	{
		return queryFromFile(fileName, null);
	}

	public static String queryFromFile(String fileName, HashMap<String, Object> data) throws Exception
	{
		String sql = Util.stringFromFile(fileName);
		if(data==null || data.isEmpty()) return sql;

		return queryParse(sql, data);
	}

	public static String queryParse(String sql, HashMap<String, Object> data)
	{
		for(var entry : data.entrySet())
		{
			var key = entry.getKey();
			var value = entry.getValue();

			var rawHolder = "[--"+key+"--]";
			var fieldHolder = "\""+rawHolder+"\"";
			var valueHolder = "'"+rawHolder+"'";

			if(sql.indexOf(fieldHolder)>-1) sql = sql.replace(fieldHolder, queryParseSymbol(value));
			if(sql.indexOf(valueHolder)>-1) sql = sql.replace(valueHolder, queryParseValue(value));
			if(sql.indexOf(rawHolder)>-1) 	sql = sql.replace(rawHolder, value.toString());
		}
		
		return sql;
	}

	public static String queryParseSymbol(Object value)
	{
		String result = null;
		if(value.getClass().isAssignableFrom(String.class))
			result = quoteSymbol((String)value);
		else if(value.getClass().isArray())
			result = queryParseSymbolCollection(Arrays.asList((Object[])value));
		else if(value instanceof Collection)
			result = queryParseSymbolCollection((Collection) value);
		else if(value instanceof Map)
			result = queryParseSymbolMap((Map)value);

		return result;
	}

	public static String queryParseSymbolMap(Map map)
	{
		StringBuilder result = null;

		for(var item : map.entrySet())
		{
			var entry = (Entry<String, ?>)item;
			var key = entry.getKey();
			var value = entry.getValue();

			if(result == null) result = new StringBuilder();
			else result.append(",\n");

			result.append(value);
			result.append(" as ");
			result.append(queryParseSymbol(key));
		}

		return result.toString();
	}

	public static String queryParseSymbolCollection(Collection list)
	{
		StringBuilder result = null;

		for(var item : list)
		{
			if(result == null) result = new StringBuilder();
			else result.append(",\n");

			result.append(queryParseSymbol(item));
		}

		return result.toString();
	}

	public static String queryParseValue(Object value)
	{
		String result = null;

		if(value==null) return "NULL";

		if(value.getClass().isAssignableFrom(String.class))
			result = quoteValue((String)value);
		else if
		(
			value.getClass().isAssignableFrom(Byte.class) ||
			value.getClass().isAssignableFrom(Short.class) ||
			value.getClass().isAssignableFrom(Integer.class) ||
			value.getClass().isAssignableFrom(Long.class) ||
			value.getClass().isAssignableFrom(Float.class) ||
			value.getClass().isAssignableFrom(Double.class)
		)
			result = value + "";
		else if(value.getClass().isArray())
			result = queryParseValueCollection(genericArrayToList(value));
		else if(value.getClass().isAssignableFrom(Collection.class))
			result = queryParseValueCollection((Collection) value);

		return result;
	}

	public static List genericArrayToList(Object array)
	{
		var list = new LinkedList<>();
		try { Object[] dataArray = (Object[])array; for(Object item : dataArray) list.add(item); return list; } catch (Exception e) {}
		try { long[] dataArray = (long[])array; for(long item : dataArray) list.add(item); return list; } catch (Exception e) {}
		try { int[] dataArray = (int[])array; for(int item : dataArray) list.add(item); return list; } catch (Exception e) {}
		try { short[] dataArray = (short[])array; for(short item : dataArray) list.add(item); return list; } catch (Exception e) {}
		try { byte[] dataArray = (byte[])array; for(byte item : dataArray) list.add(item); return list; } catch (Exception e) {}
		try { double[] dataArray = (double[])array; for(double item : dataArray) list.add(item); return list; } catch (Exception e) {}
		try { float[] dataArray = (float[])array; for(float item : dataArray) list.add(item); return list; } catch (Exception e) {}
		try { boolean[] dataArray = (boolean[])array; for(boolean item : dataArray) list.add(item); return list; } catch (Exception e) {}
		try { char[] dataArray = (char[])array; for(char item : dataArray) list.add(item); return list; } catch (Exception e) {}
		// try { return Arrays.asList((long[])array); } catch (Exception e) {}
		// try { return Arrays.asList((int[])array); } catch (Exception e) {}
		// try { return Arrays.asList((short[])array); } catch (Exception e) {}
		// try { return Arrays.asList((byte[])array); } catch (Exception e) {}
		// try { return Arrays.asList((double[])array); } catch (Exception e) {}
		// try { return Arrays.asList((float[])array); } catch (Exception e) {}
		// try { return Arrays.asList((boolean[])array); } catch (Exception e) {}
		// try { return Arrays.asList((char[])array); } catch (Exception e) {}

		return null;
	}

	public static String queryParseValueCollection(Collection list)
	{
		StringBuilder result = null;
		//Dev.debug(list);
		for(var item : list)
		{
			if(result == null)
				result = new StringBuilder("(");
			else
				result.append(",");

			result.append(queryParseValue(item));
		}

		if(result==null) return null;

		result.append(")");

		return result.toString();
	}

	public static class PgSqlConnection
	{
		public Connection con;
		public long lastUsed = 0;
		public String lastError = null;
		public boolean showError = true;
	}	
}