package frame.persist;

import java.math.BigDecimal;
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.util.Date;
import java.util.HashSet;
import java.util.Set;

import org.apache.log4j.Logger;

import frame.object.data.Entity;
import frame.object.data.EntityFactory;
import frame.object.data.EntitySet;
import frame.object.data.ObjectList;
import frame.persist.loader.EntityLoader;
import frame.persist.loader.EntitySetLoader;
import frame.persist.loader.ListLoader;
import frame.persist.loader.ObjectLoader;
import frame.persist.loader.ValueLoader;
import frame.util.Util;
import frame.variant.ValueType;


public class SQLRunner {
	
	protected static Logger logger;
	protected static ThreadLocal<Connection> activeConn;
	private static Set<String> tableSet;

	static {
		logger = Logger.getLogger(SQLRunner.class);
		tableSet = new HashSet<String>();
		activeConn = new ThreadLocal<Connection>();
	}
	
	public static synchronized void beginTrans() throws SQLException {
		Connection conn = activeConn.get();
		
		if (conn == null) {
			conn = DataSourceManager.createConnection();
			conn.setAutoCommit(false);
			activeConn.set(conn);
		}
	}
	
	public static void commit() throws Exception {
		Connection conn = activeConn.get();
		
		if (conn == null) {
			throw new Exception("empty conn, can not commit!");
		}
		
		try {
			conn.commit();
		}
		finally {
			try {
				conn.close();
			}
			catch (Exception e) {
			}
			activeConn.remove();	
		}
	}
	
	public static void rollback() throws Exception {
		Connection conn = activeConn.get();
		
		if (conn == null) {
			throw new Exception("empty conn, can not commit!");
		}
		
		try {
			conn.rollback();
		}
		finally {
			try {
				conn.close();
			}
			catch (Exception e) {
			}
			activeConn.remove();	
		}		
	}
	
	public static int execSQL(String sql) throws Exception {
		return execSQL(null, sql);
	}
	
	public static int execSQL(NamedSQL sql) throws Exception {
		return execSQL(null, sql.getSQLString());
	}
	
	public static int execSQL(Connection conn, String sql) throws Exception {
		Statement stmt = null;
		int result = 0;
		
		if (conn == null) {
			conn = activeConn.get();
		}
		boolean closeFlag = conn == null;

		try {
			if (conn == null) {
				conn = DataSourceManager.createConnection();
			}
			
			if (conn != null) {
				logger.debug(sql);
				stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
				
				result = stmt.executeUpdate(sql);
			}
		} 
		catch (SQLException e) {
			onError(sql, e);
			throw e;
		}
		finally {
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
			} finally {
				if (closeFlag && conn != null) {
					conn.close();
				}
			}
		}
		return result;
	}
	
	public static void getData(NamedSQL namedSQL, ILoadable loadable, Object ...args) throws Exception {
		getData(null, namedSQL, loadable, args);
	}
	
	public static void getData(NamedSQL namedSQL, IStepLoadable loadable, Object ...args) throws Exception {
		getData(null, namedSQL, loadable, args);
	}
	
	public static void getData(Connection conn, NamedSQL namedSQL, ILoadable loadable, Object ...args) throws Exception {
		ResultSet rslt = null;
		Statement stmt = null;

		if (conn == null) {
			conn = activeConn.get();
		}
		boolean closeFlag = conn == null;
		String sql = namedSQL.getSQL();
		
		if (sql != null) {
			sql.trim();
			
			if (!"".equals(sql)) {
				try {
					if (conn == null) {
						conn = DataSourceManager.createConnection();
					}

					if (conn != null) {
						logger.debug(sql);
						stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
						rslt = stmt.executeQuery(sql);
						
						loadable.load(rslt, args);
					}
					else{
						throw new SQLException("can not get coinnection");
					}
				} 
				catch (SQLException e) {
					logger.error("getDataFromDB error:" + e.getMessage());
					throw e;
				} 
				finally {
					try {
						if (rslt != null) {
							rslt.close();
						}
					} catch (SQLException e) {
					}
					finally {
						try {
							if (stmt != null) {
								stmt.close();
							}
						} catch (SQLException e) {
						}
						finally {
							if (closeFlag && conn != null) {
								conn.close();
							}
						}
					}
				}
			}
		}
	}
	
	public static void getData(Connection conn, NamedSQL namedSQL, IStepLoadable loadable, Object ...args) throws Exception {
		ResultSet rslt = null;
		PreparedStatement stmt = null;

		if (conn == null) {
			conn = activeConn.get();
		}
		boolean closeFlag = conn == null;
		String sql = namedSQL.getSQLString();
		
		if(!sql.contains("limit")) {
			sql += " limit ?, ?";
		}
		
		try {
			if (conn == null) {
				conn = DataSourceManager.createConnection();
			}

			if (conn != null) {
				while (loadable.hasNextForLoad()) {
					stmt = conn.prepareStatement(sql);
					loadable.setLoadParameters(stmt);
					
					try {
						try {
							rslt = stmt.executeQuery();
							loadable.load(rslt, args);
						}
						finally {
							if (rslt != null) {
								rslt.close();
							}
						}						
					}
					finally {
						if (stmt != null) {
							stmt.close();
						}
					}
				}
			}
			else{
				throw new SQLException("can not get coinnection");
			}
		} 
		catch (SQLException e) {
			logger.debug(sql);
			logger.error("getDataFromDB error:" + e.getMessage());
			throw e;
		} 
		finally {
			if (closeFlag && conn != null) {
				conn.close();
			}
		}
	}
	
	public static void saveData(NamedSQL namedSQL1, NamedSQL namedSQL2, IDoubleSavable savable, Object ...args) throws Exception {
		saveData(null, namedSQL1, namedSQL2, savable, args);
	}
	
	public static void saveData(NamedSQL namedSQL, ISavable savable, Object ...args) throws Exception {
		saveData(null, namedSQL, savable, args);
	}
	
	public static void saveData(Connection conn, NamedSQL namedSQL, ISavable savable, Object ...args) throws Exception {
		ResultSet rslt = null;
		PreparedStatement stmt = null;

		String sql = namedSQL.getSQLString();
		
		if (sql != null) {
			sql = sql.trim();
			
			if (conn == null) {
				conn = activeConn.get();
			}
			boolean closeFlag = conn == null;
			
			if (!"".equals(sql)) {
				try {
					if (conn == null) {
						conn = DataSourceManager.createConnection();
					}
					
					if (conn != null) {
						logger.debug(sql);
						stmt = conn.prepareStatement(sql);
						savable.saveData(stmt, args);
					}
					else{
						throw new SQLException("can not get coinnection");
					}
				} 
				catch (SQLException e) {
					logger.error("getDataFromDB error:" + e.getMessage());
					throw e;
				} finally {
					try {
						if (rslt != null) {
							rslt.close();
						}
					} catch (SQLException e) {
					}
					finally {
						try {
							if (stmt != null) {
								stmt.close();
							}
						} catch (SQLException e) {
						}
						finally {
							if (closeFlag && conn != null) {
								conn.close();
							}
						}
					}
				}
			}
		}
	}
	
	public static void saveData(Connection conn, NamedSQL namedSQL1, NamedSQL namedSQL2, IDoubleSavable savable, Object ...args) throws Exception {
		PreparedStatement stmt1 = null, stmt2 = null;

		String sql1 = namedSQL1.getSQLString();
		String sql2 = namedSQL2.getSQLString();
		
		if (!Util.isEmptyStr(sql1) && !Util.isEmptyStr(sql2)) {
			if (conn == null) {
				conn = activeConn.get();
			}
			boolean closeFlag = conn == null;
			
			try {
				if (conn == null) {
					conn = DataSourceManager.createConnection();
				}
					
				if (conn != null) {
					logger.debug(sql1);
					logger.debug(sql2);
					
					stmt1 = conn.prepareStatement(sql1);
					stmt2 = conn.prepareStatement(sql2);
					
					savable.save(stmt1, stmt2, args);
				}
				else{
					throw new SQLException("can not get coinnection");
				}
			} 
			catch (SQLException e) {
				logger.error("getDataFromDB error:" + e.getMessage());
				throw e;
			} finally {
				try {
					if (stmt1 != null) {
						stmt1.close();
					}
				} catch (SQLException e) {
				}
				finally {
					try {
						if (stmt2 != null) {
							stmt2.close();
						}
					} catch (SQLException e) {
					}
					finally {
						if (closeFlag && conn != null) {
							conn.close();
						}
					}
				}
			}
		}
	}
	
	public static void saveData(NamedSQL namedSQL, IStepSavable stepSavable, Object ...args) throws Exception {
		saveData(null, namedSQL, stepSavable, args);
	}
	
	public static void saveData(Connection conn, NamedSQL namedSQL, IStepSavable stepSavable, Object ...args) throws Exception {
		PreparedStatement stmt = null;

		String sql = namedSQL.getSQLString();
		
		if (conn == null) {
			conn = activeConn.get();
		}
		boolean closeFlag = conn == null;
		
		if (sql != null) {
			sql.trim();
			
			if (!"".equals(sql)) {
				try {
					if (conn == null) {
						conn = DataSourceManager.createConnection();
					}
					
					if (conn != null) {
						while (stepSavable.hasNextForSave()) {
							stmt = conn.prepareStatement(sql);
							
							try {
								stepSavable.save(stmt, args);
							}
							finally {
								if (stmt != null) {
									stmt.close();
								}
							}
						}
					}
					else{
						throw new SQLException("can not get coinnection");
					}
				} 
				catch (SQLException e) {
					logger.debug(sql);
					logger.error("getDataFromDB error:" + e.getMessage());
					throw e;
				} 
				finally {
					if (closeFlag && conn != null) {
						conn.close();
					}
				}
			}
		}
	}

	public static EntitySet getEntitySet(NamedSQL namedSQL) throws Exception {
		EntitySet result = null;
		
		EntitySetLoader entitySetLoader = new EntitySetLoader(namedSQL.getName(), null);
		getData(namedSQL, entitySetLoader);
		result = entitySetLoader.getDataSet();
		
		return result;
	}
	
	public static EntitySet getEntitySet(NamedSQL namedSQL, EntityFactory entityFactory) throws Exception {
		EntitySet result = null;
		
		EntitySetLoader entitySetLoader = new EntitySetLoader(namedSQL.getName(), entityFactory);
		getData(namedSQL, entitySetLoader);
		result = entitySetLoader.getDataSet();
		
		return result;
	}
	
	public static <T> ObjectList<T> getList(NamedSQL namedSQL, Class<T> clazz) throws Exception {
		ObjectList<T> result = null;
		
		ListLoader<T> listLoader = new ListLoader<T>(namedSQL.getName(), clazz);
		getData(namedSQL, listLoader);
		result = listLoader.getList();
		
		return result;
	}	
	
	public static Entity getEntity(NamedSQL namedSQL) throws Exception {
		Entity result = null;
		
		EntityLoader entityLoader = new EntityLoader(namedSQL.getName());
		getData(namedSQL, entityLoader);
		result = entityLoader.getEntity();
		
		return result;
	}
	
	public static Object getObject(NamedSQL namedSQL, Class<?> clazz) throws Exception {
		Object result = null;
		
		ObjectLoader objectLoader = new ObjectLoader(namedSQL.getName(), clazz);
		getData(namedSQL, objectLoader);
		result = objectLoader.getObject();
		
		return result;
	}
	public static Entity getEntity(NamedSQL namedSQL, EntityFactory entityFactory) throws Exception {
		Entity result = null;

		EntityLoader entityLoader = new EntityLoader(namedSQL.getName(), entityFactory);
		getData(namedSQL, entityLoader);
		result = entityLoader.getEntity();

		return result;
	}

	public static String getString(NamedSQL namedSQL, int idx) throws Exception {
		String result = null;
		
		ValueLoader valueLoader = new ValueLoader();
		getData(namedSQL, valueLoader);
		result = valueLoader.getString();
		
		return result;
	}
	
	public static int getInteger(NamedSQL namedSQL) throws Exception {
		int result = 0;
		
		ValueLoader valueLoader = new ValueLoader();
		getData(namedSQL, valueLoader);
		result = valueLoader.getInt();
		
		return result;
	}
	
	public static BigDecimal getBigDecimal(NamedSQL namedSQL) throws Exception {
		BigDecimal result = null;
		
		ValueLoader valueLoader = new ValueLoader();
		getData(namedSQL, valueLoader);
		result = valueLoader.getBigDecimal();
		
		return result;
	}
	
	public static Date getDate(NamedSQL namedSQL) throws Exception {
		Date result = null;
		
		ValueLoader valueLoader = new ValueLoader();
		getData(namedSQL, valueLoader);
		result = valueLoader.getDate();
		
		return result;
	}
	
	public static Result getResult(NamedSQL namedSQL) throws Exception {
		Result result = new Result();
		ValueType returnType = namedSQL.getReturnType();
		
		if (ValueType.EntitySet == returnType) {
			EntitySet value = getEntitySet(namedSQL);
			result.setValue(value);
		}
		else if (ValueType.Entity == returnType) {
			Entity value = getEntity(namedSQL);
			result.setValue(value);
		}
		else if (ValueType.Int == returnType) {
			int value = getInteger(namedSQL);
			result.setValue(value);
		}
		else if (ValueType.String == returnType) {
			String value = getString(namedSQL, 1);
			result.setValue(value);
		}
		else if (ValueType.Decimal == returnType) {
			BigDecimal value = getBigDecimal(namedSQL);
			result.setValue(value);
		}
		else if (ValueType.Date == returnType) {
			Date value = getDate(namedSQL);
			result.setValue(value);
		}		
		else {
			int value = execSQL(namedSQL);
			result.setValue(value);
		}
		
		return result;		
	}

	public static void getTableMetaData(IMetaDataLoader loader, String sql) throws SQLException {
		getTableMetaData(null, loader, sql);
	}
	
	public static void getTableMetaData(Connection conn, IMetaDataLoader loader, String sql) throws SQLException {
		Statement stmt = null;
		ResultSet rslt = null;
		
		if (conn == null) {
			conn = activeConn.get();
		}
		boolean closeFlag = conn == null;
		
		try {
			if (conn == null) {
				conn = DataSourceManager.createConnection();
			}

			stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			
			logger.debug(sql);
			
			rslt = stmt.executeQuery(sql);
			ResultSetMetaData result = rslt.getMetaData();
			loader.load(result);
		} 
		catch (Exception e) {
			onError(sql, e);
		} 
		finally {
			try {
				if (rslt != null) {
					rslt.close();					
				}
			} 
			catch (SQLException e) {
			} 
			finally {
				try {
					if (stmt != null) {
						stmt.close();						
					}
				} 
				catch (SQLException e) {
				} 
				finally {
					if (closeFlag && conn != null) {
						conn.close();
					}
				}
			}
		}
	}
	
	public static boolean isTableExists(String tableName, boolean cache) throws Exception {
		if (tableName == null) {
			return false;
		}
		
		if (tableSet.contains(tableName.toLowerCase())) {
			return true;
		}
		
		boolean result = isTableExists(null, tableName);
		
		if (result) {
			tableSet.add(tableName.toLowerCase());
		}
		
		return result;
	}
	
	public static boolean isTableExists(Connection conn, String tableName) throws Exception {
		String sql = null;
		Statement stmt = null;
		ResultSet rslt = null;
		
		if (conn == null) {
			conn = activeConn.get();
		}
		boolean closeFlag = conn == null;
		
		try {
			if (conn == null) {
				conn = DataSourceManager.createConnection();
			}

			stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			sql = "select 1 from user_tables where table_name ='" + tableName.toUpperCase() + "'";
			rslt = stmt.executeQuery(sql);
			
			if (rslt.next()) {
				return true;
			}
			
			return false;
		} 
		catch (Exception e) {
			onError(sql, e);
			throw e;
		} 
		finally {
			try {
				if (rslt != null) {
					rslt.close();					
				}
			} 
			catch (SQLException e) {
			} 
			finally {
				try {
					if (stmt != null) {
						stmt.close();						
					}
				} 
				catch (SQLException e) {
				} 
				finally {
					if (closeFlag && conn != null) {
						conn.close();
					}
				}
			}
		}
	}
	
	protected static void onError(String sql, Exception e) {
		logger.error("executor exec error: " + e.getMessage());
	}
}