package com.smtaiserver.smtaiserver.database;

import com.smtaiserver.smtaiserver.core.SMTAIServerApp;
import com.smtservlet.util.SMTStatic;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

public class SMTDatabase implements AutoCloseable
{
	public static class DBRecord
	{
		protected Map<String, Integer> _name2index;
		protected Object[] 	_rec;
		
		public DBRecord(Map<String, Integer> name2index, Object[] rec)
		{
			_name2index = name2index;
			_rec = rec;
		}
		
		@Override
		public String toString()
		{
			if(_rec == null)
				return null;
			StringBuilder sb = new StringBuilder();
			
			for(Entry<String, Integer> entry : _name2index.entrySet())
			{
				sb.append(String.format("%s=[%s] ", entry.getKey(), _rec[entry.getValue()]));
			}
			
			return sb.toString();
		}
		
		public Object[] getValues()
		{
			return _rec;
		}
		
		public Map<String, Integer> getFieldMap()
		{
			return _name2index;
		}
		
		public int getColIndex(String colName)
		{
			Integer index = _name2index.get(((String)colName).toUpperCase());
			if(index == null)
				return -1;
			
			return index;
		}
		
		public Date getDate(Object col) throws Exception
		{
			Object value = getValue(col);
			if(value == null)
				return null;
			return SMTStatic.toDate(value);
		}
		
		private static double toDouble(Object value)
		{
			try
			{
				if(value instanceof Double)
					return (Double)value;
				
				if(value instanceof String)
					return Double.parseDouble((String)value);
				
				return (Double)(value.getClass().getMethod("doubleValue").invoke(value));
			}
			catch(Exception ex)
			{
				return Double.parseDouble(value.toString());
			}
		}

		
		private static int toInt(Object value)
		{
			try
			{
				if(value instanceof Integer)
					return (Integer)value;
				
				if(value instanceof Long)
					return (int)(long)(Long)value;
				
				if(value instanceof String)
					return Integer.parseInt((String)value);
				
				return (Integer)(value.getClass().getMethod("intValue").invoke(value));
			}
			catch(Exception ex)
			{
				return Integer.parseInt(value.toString());
			}
		}
		
		private static long toLong(Object value)
		{
			try
			{
				if(value instanceof Long)
					return (Long)value;
				
				if(value instanceof Integer)
					return (long)(Integer)value;
				
				if(value instanceof String)
					return Long.parseLong((String)value);

				return (Long)(value.getClass().getMethod("longValue").invoke(value));
			}
			catch(Exception ex)
			{
				return Long.parseLong(value.toString());
			}
		}

		
		public Object getValue(int col)
		{
			return _rec[col];
		}
		
		public Long getLong(Object col) throws Exception
		{
			Object value = getValue(col);
			if(value == null || (value instanceof String && ((String)value).length() == 0))
				return null;
			return toLong(value);
		}
		
		public String getString(Object col) throws Exception
		{
			Object value = getValue(col);
			if(value == null)
				return null;
			return SMTStatic.toString(value);
		}
		
		public Integer getInteger(Object col) throws Exception
		{
			Object value = getValue(col);
			if(value == null || (value instanceof String && ((String)value).length() == 0))
				return null;
			return toInt(value);
		}
		
		public Double getDouble(Object col) throws Exception
		{
			Object value = getValue(col);
			if(value == null || (value instanceof String && ((String)value).length() == 0))
				return null;
			return toDouble(value);
		
		}

		
		public Object getValue(Object col) throws Exception
		{
			Integer index;
			if(col instanceof String)
			{
				index = _name2index.get(((String)col).toUpperCase());
				if(index == null)
					throw new Exception("can'find col : " + col);
			}
			else if(col instanceof Integer)
			{
				index = (Integer)col;
			}
			else
			{
				throw new Exception("unsupported type : " + col.getClass());
			}
			
			return _rec[index];
		}
		
		public Map<String, Object> toRecordMap()
		{
			Map<String, Object> recMap = new LinkedHashMap<String, Object>();
			for(Entry<String, Integer> entry : _name2index.entrySet())
			{
				String key = entry.getKey();
				Integer index = entry.getValue();
				recMap.put(key, _rec[index]);
			}
			
			return recMap;
		}
		
		public String[] getColNames()
		{
			String[] colNames = new String[_name2index.size()];
			for(Entry<String, Integer> entry : _name2index.entrySet())
			{
				colNames[entry.getValue()] = entry.getKey();
			}
			
			return colNames;
		}
	}
	
	public static class DBRecords
	{
		private Map<String, Integer> _name2index;
		private List<DBRecord> _recs;
		
		public DBRecords cloneReccsDef()
		{
			DBRecords recs = new DBRecords();
			recs._name2index = this._name2index;
			recs._recs = new ArrayList<DBRecord>();
			
			return recs;
		}

		public int getColIndex(String colName)
		{
			if(colName == null)
				return -1;
			
			Integer index = _name2index.get(((String)colName).toUpperCase());
			if(index == null)
				return -1;
			
			return index;
		}
		public Map<String, Object> toRecordMap(int row)
		{
			return _recs.get(row).toRecordMap();
		}
		
		public List<Map<String, Object>> toRecordMapList()
		{
			List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
			for(DBRecord rec : _recs)
			{
				list.add(rec.toRecordMap());
			}
			return list;
		}
		
		public void setColumnMap(Map<String, Integer> name2index)
		{
			_name2index = name2index;
		}
		
		public void initColumn(Map<String, Integer> name2index)
		{
			_name2index = new LinkedHashMap<String, Integer>();
			for(Entry<String, Integer> entry : name2index.entrySet())
			{
				_name2index.put(entry.getKey(), entry.getValue());
			}			
			_recs = new ArrayList<DBRecord>();
		}
	
		public void initColumn(String[] fields)
		{
			_name2index = new LinkedHashMap<String, Integer>();
			for(int i = 0; i < fields.length; i ++)
			{
				_name2index.put(fields[i], i);
			}
			_recs = new ArrayList<DBRecord>();
		}
		
		public void initColumn(ResultSet rs) throws Exception
		{
			ResultSetMetaData metaData = rs.getMetaData();
			int count = metaData.getColumnCount();
			_name2index = new LinkedHashMap<String, Integer>();
			for(int i = 0; i < count; i ++)
			{
				String colName = metaData.getColumnName(i + 1).toUpperCase();
				if(!_name2index.containsKey(colName))
				{
					_name2index.put(colName, i);
				}		
				else
				{
					for(int j = 1;;j ++)
					{
						String aliasName = String.format("%s#%d", colName, j);
						if(!_name2index.containsKey(aliasName))
						{
							_name2index.put(aliasName, i);
							break;
						}
					}					
				}
			}
			_recs = new ArrayList<DBRecord>();
		}
		
		public void limitRecord(int limit)
		{
			for(int i = _recs.size() - 1; i >= limit; i --)
				_recs.remove(i);
		}
		
		public void removeRecord(int row)
		{
			_recs.remove(row);
		}
		
		public void addRecord(DBRecord rec)
		{
			_recs.add(rec);
		}
		
		public void addRecord(Object[] values)
		{
			_recs.add(new DBRecord(_name2index, values));
		}
		
		public DBRecord newRecord(ResultSet rs) throws Exception
		{
			int colCount = _name2index.size();
			Object[] rec = new Object[colCount];
			for(int i = 0; i < colCount; i ++)
			{
				rec[i] = rs.getObject(i + 1);
				
				if(rec[i] instanceof Timestamp)
				{
					rec[i] = new Date(((Timestamp)rec[i]).getTime());
				}
			}
			return new DBRecord(_name2index, rec);
		}
		
		public void addRecord(ResultSet rs) throws Exception
		{
			_recs.add(newRecord(rs));
		}
		
		public int getColCount()
		{
			return _name2index.size();
		}
		
		public int getRowCount()
		{
			return _recs.size();
		}
		
		public Object getValue(int col, int row)
		{
			return _recs.get(row).getValue(col);
		}
		
		public Map<String, Integer> getFieldMap()
		{
			return _name2index;
		}
		
		public String[] getColNames()
		{
			String[] colNames = new String[_name2index.size()];
			for(Entry<String, Integer> entry : _name2index.entrySet())
			{
				colNames[entry.getValue()] = entry.getKey();
			}
			
			return colNames;
		}
		
		public int getFieldIndex(String col)
		{
			Integer index = _name2index.get(((String)col).toUpperCase());
			if(index == null)
				return -1;
			
			return index;
		}
		
		public Object getValue(String col, int row) throws Exception
		{
			return _recs.get(row).getValue(col);
		}

		public DBRecord getRecord(int row)
		{
			return _recs.get(row);
		}
		
		public List<DBRecord> getRecords()
		{
			return _recs;
		}
	}

	public static class HighlightONAME
	{
		public String 	_OTYPE;
		public String 	_ONAME;
		
		public Integer 	_PSTYLE;
		public Integer  _PSIZE;
		public Integer  _PCOLOR;
		
		public Integer 	_LSTYLE;
		public Integer  _LSIZE;
		public Integer  _LCOLOR;
		
		public HighlightONAME(String OTYPE, String ONAME)
		{
			_OTYPE = OTYPE;
			_ONAME = ONAME;
		}
	}
	
	public static interface DBQueryNotify
	{
		boolean onNextRecord(DBRecord rec)throws Exception;
	}
	
	public static interface DBQueryNotifyMeta extends DBQueryNotify
	{
		boolean onMetaInfo(DBRecords metaInfo, String[] colTypes)throws Exception;
	}
	
	private static Logger 			_logger = LogManager.getLogger(SMTDatabase.class);
	protected boolean				_inTrans = false;
	protected Connection 			_conn;
	protected Set<Statement>		_setTraceStatement = null;
	
	public SMTDatabase(Connection conn)
	{
		_conn = conn;
	}
	
	public Connection getConnection()
	{
		return _conn;
	}
	
	public void enableTraceStatement()
	{
		if(_setTraceStatement != null)
			return;
		_setTraceStatement = new HashSet<>();
	}
	
	public void abortStmt()
	{
		if(_setTraceStatement != null)
		{
			synchronized(_setTraceStatement)
			{
				for(Statement stmt : _setTraceStatement)
				{
					try {
						stmt.cancel();
					} catch (SQLException e) {
						
					}
				}
				_setTraceStatement.clear();
			}
		}
	}
	
	public void beginTran() throws Exception
	{
		_conn.setAutoCommit(false);
		_inTrans = true;
	}
	
	public void commitTran() throws Exception
	{
		_conn.commit();
		_inTrans = false;
	}
	
	public void rollbackTran() throws Exception
	{
		_conn.rollback();
		_inTrans = false;
	}
	
	public boolean isClosed()
	{
		try {
			return _conn == null || _conn.isClosed();
		}
		catch(Exception ex) {
			return false;
		}
	}

	public void close()
	{
		if(_conn != null)
		{
			try {
				if(!_conn.isClosed())
				{
					if(_inTrans)
						rollbackTran();
					_conn.close();
				}
			} catch (Exception ex) {
				//_logger.fatal("close database error", ex);
			}
			_conn = null;
		}
	}
	
	protected String getLogSQL(String perfix, String sql, Object[] params)
	{
		StringBuilder sbLog = new StringBuilder();
		sbLog.append(perfix + ":\n" + sql + "\n");
		if(params != null)
		{
			for(int i = 0; i < params.length; i ++)
			{
				if(params[i] == null)
					sbLog.append("    NULL\n");
				else
					sbLog.append("    (" + params[i].getClass().toString() + ") : [" + SMTStatic.toString(params[i]) + "]\n");
			}			
		}
		return sbLog.toString();
	}
	
	public int executeSQL(String sql, Object[] params) throws Exception
	{
		return executeSQL(true, sql, params);
	}
	
	public Integer getPGBackendPID() throws Exception
	{
		PreparedStatement stmt = this._conn.prepareStatement("SELECT pg_backend_pid()");
		try
		{
			ResultSet rs = stmt.executeQuery();
			try
			{
				if(!rs.next())
					return null;
				
				return rs.getInt(1);
			}
			finally
			{
				rs.close();
			}
		}
		finally
		{
			stmt.close();
		}
	}
	
	public void rollbackDB(Exception ex) throws Exception
	{
		try
		{
			this.executeSQL("ROLLBACK", null);
		}
		catch(Exception ex1)
		{
		}
		if(ex != null)
			throw ex;
	}
	
	public PreparedStatement prepareStatement(String sql) throws Exception
	{
		return this._conn.prepareStatement(sql);
	}
	
	public int executeSQL(boolean bLog, String sql, Object[] params) throws Exception
	{
		String uuid = null;
		String logSql = null;
		long ticket = System.currentTimeMillis();
		if(bLog && SMTAIServerApp.getApp().isLogSQL())
		{
			uuid = SMTStatic.newUUID();
			logSql = getLogSQL("executeSQL : " + uuid, sql, params);
			_logger.info(logSql);
		}
		
		PreparedStatement stmt = _conn.prepareStatement(sql);
		if(_setTraceStatement != null)
		{
			synchronized(_setTraceStatement)
			{
				_setTraceStatement.add(stmt);
			}
		}
		try
		{
			if(params != null)
			{
				for(int i = 0; i < params.length; i ++)
				{
					if(params[i] instanceof Date)
					{
						Date time = (Date)params[i];
						stmt.setTimestamp(i + 1, new Timestamp(time.getTime()));
					}
					else
					{
						stmt.setObject(i + 1, params[i]);
					}
				}
			}
			int ret = stmt.executeUpdate();
			if(uuid != null)
			{
				long times = System.currentTimeMillis() - ticket;
				if(times > 2000)
					_logger.info("querySQL LONG : " + uuid + " : FINISH : TIME : " + times + " ms");
			}
			return ret;
		}
		catch(Exception ex)
		{
			if(uuid != null)
			{
				if(logSql == null)
					logSql = getLogSQL("executeSQL : " + uuid, sql, params);
				_logger.fatal(logSql + " : Exception", ex);
			}
			throw ex;
		}
		finally
		{
			if(_setTraceStatement != null)
			{
				synchronized(_setTraceStatement)
				{
					_setTraceStatement.remove(stmt);
				}
			}
			stmt.close();
		}
	}
	
	public DBRecords querySQL(String sql, Object[] params) throws Exception
	{
		return querySQL(true, sql, params);
	}
	
	public void querySQLNotify(String sql, Object[] params, DBQueryNotify notify) throws Exception
	{
		try
		{
			DBRecords recs = new DBRecords();
			PreparedStatement stmt = _conn.prepareStatement(sql);
			if(_setTraceStatement != null)
			{
				synchronized(_setTraceStatement)
				{
					_setTraceStatement.add(stmt);
				}
			}
			try
			{
				if(params != null)
				{
					for(int i = 0; i < params.length; i ++)
					{
						if(params[i] instanceof Date)
						{
							Date time = (Date)params[i];
							stmt.setTimestamp(i + 1, new Timestamp(time.getTime()));
						}
						else
						{
							stmt.setObject(i + 1, params[i]);
						}
					}
				}
				ResultSet rs = stmt.executeQuery();
				try
				{
					recs.initColumn(rs);
					
					if(notify instanceof DBQueryNotifyMeta)
					{
						ResultSetMetaData meta = rs.getMetaData();
						int colCount = meta.getColumnCount();
						String[] sColTypes = new String[colCount];

						for (int i = 0; i < colCount; i++) {
						  switch (meta.getColumnType(i + 1)) {
							case java.sql.Types.BIGINT:
							  sColTypes[i] = "bigint";
							  break;

							case java.sql.Types.FLOAT:
							  sColTypes[i] = "float4";
							  break;

							case java.sql.Types.DOUBLE:
							case java.sql.Types.NUMERIC:
							case java.sql.Types.DECIMAL:
							  sColTypes[i] = "float8";
							  break;
							case java.sql.Types.INTEGER:
							  sColTypes[i] = "integer";
							  break;
							case java.sql.Types.TIMESTAMP:
							  sColTypes[i] = "timestamp";
							  break;
							case java.sql.Types.CHAR:
							case java.sql.Types.VARCHAR:
							  sColTypes[i] = "varchar(" + meta.getColumnDisplaySize(i + 1) + ")";
							  break;

							default:
							  throw new Exception("unknow column type : " + meta.getColumnType(i + 1));
						  }
						}

						if(!((DBQueryNotifyMeta)notify).onMetaInfo(recs, sColTypes))
							return;
					}
					
					while(rs.next())
					{
						DBRecord rec = recs.newRecord(rs);
						if(!notify.onNextRecord(rec))
							break;
					}
					
					return;
				}
				finally
				{
					rs.close();
				}
			}
			finally
			{
				if(_setTraceStatement != null)
				{
					synchronized(_setTraceStatement)
					{
						_setTraceStatement.remove(stmt);
					}
				}
				stmt.close();
			}
		}
		catch(Exception ex)
		{
			String logSql = getLogSQL("executeSQL : ", sql, params);
			_logger.fatal(logSql + " : Exception", ex);
			throw ex;
		}
	}

	
	public DBRecords querySQL(boolean bLog, String sql, Object[] params) throws Exception
	{
		String uuid = null;
		long ticket = 0;
		String logSql = null;
		if(bLog && SMTAIServerApp.getApp().isLogSQL())
		{
			uuid = SMTStatic.newUUID();
			ticket = System.currentTimeMillis();
			logSql = getLogSQL("querySQL : " + uuid, sql, params);
			_logger.info(logSql);
		}
		
		try
		{
			DBRecords recs = new DBRecords();
			PreparedStatement stmt = _conn.prepareStatement(sql);
			if(_setTraceStatement != null)
			{
				synchronized(_setTraceStatement)
				{
					_setTraceStatement.add(stmt);
				}
			}
			try
			{
				if(params != null)
				{
					for(int i = 0; i < params.length; i ++)
					{
						if(params[i] instanceof Date)
						{
							Date time = (Date)params[i];
							stmt.setTimestamp(i + 1, new Timestamp(time.getTime()));
						}
						else
						{
							stmt.setObject(i + 1, params[i]);
						}
					}
				}
				ResultSet rs = stmt.executeQuery();
				try
				{
					recs.initColumn(rs);
					
					while(rs.next())
					{
						recs.addRecord(rs);
					}
					
					if(uuid != null)
					{
						long times = System.currentTimeMillis() - ticket;
						if(times > 2000)
							_logger.info("querySQL LONG : " + uuid + " : FINISH : " + recs.getRowCount() + " : TIME : " + times + " ms");
					}
					return recs;
				}
				finally
				{
					rs.close();
				}
			}
			finally
			{
				if(_setTraceStatement != null)
				{
					synchronized(_setTraceStatement)
					{
						_setTraceStatement.remove(stmt);
					}
				}
				stmt.close();
			}
		}
		catch(Exception ex)
		{
			if(uuid != null)
			{
				if(logSql == null)
					logSql = getLogSQL("executeSQL : " + uuid, sql, params);
				_logger.fatal(logSql + " : Exception", ex);
			}
			else
			{
				if(logSql == null)
					logSql = getLogSQL("executeSQL : " + uuid, sql, params);
				_logger.fatal(logSql + " : Exception", ex);
			}
			throw ex;
		}
	}
	
	
}