package com.weaver.formmodel.base.dao;

import java.lang.reflect.Field;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import weaver.conn.RecordSet;
import weaver.conn.WeaverConnection;
import weaver.formmode.IgnoreCaseHashMap;
import weaver.general.StaticObj;
import weaver.interfaces.datasource.DataSource;

import com.weaver.formmodel.base.define.Nopersistent;
import com.weaver.formmodel.base.define.PersistentTable;
import com.weaver.formmodel.base.handler.WeaverBeanHandler;
import com.weaver.formmodel.base.handler.WeaverBeanListHandler;
import com.weaver.formmodel.base.model.PageModel;
import com.weaver.formmodel.base.model.PersistenceModel;
import com.weaver.formmodel.util.GenericsUtils;
import com.weaver.formmodel.util.NumberHelper;
import com.weaver.formmodel.util.StringHelper;

import weaver.monitor.cache.CacheFactory;
import weaver.servicefiles.DataSourceXML;


 /**
  * Entity Dao基类
  * @author Administrator
  *
  * @param <T>
  */
 public abstract class AbstractBaseDao<T extends PersistenceModel> implements IBaseDao<T> {
	protected final Log logger = LogFactory.getLog(getClass());

    private static List<String> logicalDeleteTables = Arrays.asList("AppHomepage", "AppHomepageFolder", "MobileAppModelInfo", "AppFormUI");
    
	/** 设置一些操作的常量 */
	private static final String SQL_INSERT = "insert";
	private static final String SQL_UPDATE = "update";
	private static final String SQL_DELETE = "delete";
	
	//private static final ThreadLocal<WeaverConnection> connectionHolder = new ThreadLocal<WeaverConnection>();
	/**
	 * Dao所管理的Entity类型
	 */
	private Class<T> entityClass;

	private String poolname;
	 
	/**
	 * 在构造函数中将T赋给supportsClass
	 */
	@SuppressWarnings("unchecked")
	public AbstractBaseDao() {
		entityClass = GenericsUtils.getGenericClass(getClass());
	}

	protected Class<T> getEntityClass() {
		return entityClass;
	}

	@Override
	public int create(T t) {
		return create(t, null);
	}
	
	public int create(T t, String tablename) {
		if(t != null && t.getId() != null && t.getId() != 0) {
			this.update(t);
			return t.getId();
		}
		
		String sql = t.toCreateSQL();
		if(StringHelper.isEmpty(sql)) {
			sql = this.makeSql(SQL_INSERT);
		}
		Object[] args = null;
		List<Object> datas = t.getValues();
		if(datas == null) {
			args = this.setArgs(t, SQL_INSERT);
		} else {
			args = datas.toArray();
		}
		int result = this.create(sql, tablename, t.getKeyfield(), args);
		t.setId(result);
        return result;
	}
	
	@Override
    public int create(String sql, Object... params) {
	    return this.create(sql, this.getPersistentTable(), "id", params);
	}

	@Override
	public int create(String sql, String tablename, String primKey, Object... params) {
		WeaverConnection conn = getConnection();
		if(conn == null) {
			return 0;
		}
        try {
            WeaverQueryRunner qr = new WeaverQueryRunner();   
            primKey = StringHelper.isEmpty(primKey) ? "id" : primKey;
            tablename = StringHelper.isEmpty(tablename) ? this.getPersistentTable() : tablename;
            Object result = qr.insert(this.poolname, tablename, primKey, conn, sql, "", params);
            return NumberHelper.getIntegerValue(result,0);
        } catch (Exception e) {
            logger.error(getClass(),e);
            e.printStackTrace();
            TransactionContextHolder.setHappenException(true);
            return -1;   
        } finally {
        	TransactionContextHolder.close(conn);   
        }
	}

	@Override
	public T get(int id) { 
		T object = null;
		if(id == 0) {			
			try {
				object = (T)getEntityClass().newInstance(); // 调用无参构造函数来生成指定类的实例。
			} catch (Exception e) {
				e.printStackTrace();
			}
			return object;
		}
		WeaverQueryRunner qr = new WeaverQueryRunner();
		String ptablename = this.getPersistentTable();
		String sql = "SELECT * FROM " + ptablename + " WHERE id=?";	
		Object[] ids = {id};
		try {
			object = qr.query(sql, this.poolname, new WeaverBeanHandler<T>(entityClass), ids);
		} catch (Exception e) {
			logger.error(getClass(),e);
			e.printStackTrace();
		}
		if(object == null) {
			try {
				object = (T)getEntityClass().newInstance(); // 调用无参构造函数来生成指定类的实例。
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return object;
	}

	@Override
	public T get(T t) {
		String entityName = t.getName();
		if(entityName == null) {
			entityName = this.getPersistentTable();
		}
		WeaverQueryRunner qr = new WeaverQueryRunner();   
		String sql = "SELECT * FROM " + entityName + " WHERE id=?";	
		Integer id = t.getId();
		Object[] ids = {id};
		T object = null;
		try {
			object = qr.query(sql, this.poolname, new WeaverBeanHandler<T>(entityClass), ids);
			if(object == null){
				object = (T)getEntityClass().newInstance(); // 调用无参构造函数来生成指定类的实例。
			}
		} catch (Exception e) {
			logger.error(getClass(),e);
			e.printStackTrace();
		}
		return object;
	}
	
	@Override
	public int delete(int id) {
		WeaverConnection conn = getConnection();
		if(conn == null || id == 0) {			
			return 0;
		}
		WeaverQueryRunner qr = new WeaverQueryRunner();  
		String entityName = this.getPersistentTable();
		
		String sql;
        if(logicalDeleteTables.contains(entityName)){
            sql = "UPDATE " + entityName + " SET isdelete=1 WHERE id=?";    
        }else{
            sql = "DELETE FROM " + entityName + " WHERE id=?";    
        }
        
		Object[] ids = {id};
		try {
			int rows = qr.update(conn,sql,ids);
			CacheFactory.getInstance().removeCacheForSql(sql);
			return rows;
		} catch (SQLException e) {
			logger.error(getClass(),e);
			e.printStackTrace();
			TransactionContextHolder.setHappenException(true);
		} finally{
			TransactionContextHolder.close(conn);   
		}
		return 0;
	}
	
	@Override
	public void delete(T t) {
		WeaverConnection conn = getConnection();
		if(conn == null) {			
			return;
		}
		String entityName = t.getName();
		Integer id = t.getId();
		if(entityName == null) {
			entityName = this.getPersistentTable();
		}
		WeaverQueryRunner qr = new WeaverQueryRunner();
		
		String sql;
		if(logicalDeleteTables.contains(entityName)){
		    sql = "UPDATE " + entityName + " SET isdelete=1 WHERE id=?";    
		}else{
		    sql = "DELETE FROM " + entityName + " WHERE id=?";    
		}
		
		Object[] ids = {id};
		try {
			qr.update(conn,sql,ids);
			CacheFactory.getInstance().removeCacheForSql(sql);
		} catch (SQLException e) {
			logger.error(getClass(),e);
			e.printStackTrace();
			TransactionContextHolder.setHappenException(true);
		} finally{
			TransactionContextHolder.close(conn);   
		}
	}
	
	@Override
    public PageModel pageQuery(String sql,Map<String,Object> orderby, int pageNo, int pageSize,
            Object... params) {
	    return pageQuery(sql, poolname, orderby, pageNo, pageSize, params);
	}
	public PageModel pageQuery(String sql, String poolname, Map<String,Object> orderby, int pageNo, int pageSize,
            Object... params) {
	    String orderKey = "";
	    if(orderby != null && !orderby.isEmpty()){
	        StringBuilder orderKeyBuilder= new StringBuilder("order by");
            int i = 0;
            for(String fieldname : orderby.keySet()){
                if(i++ > 0){
                    orderKeyBuilder.append(",");
                }
                String ordertype = StringHelper.null2String(orderby.get(fieldname));
                orderKeyBuilder.append(" ").append(fieldname).append(" ").append(ordertype);
            }
            orderKey = orderKeyBuilder.toString();
        }
        return pageQuery(sql, poolname, orderKey, pageNo, pageSize, params);
	}
	public PageModel pageQuery(String sql, String poolname, String orderKey, int pageNo, int pageSize,
			Object... params) {
		PageModel pageModel=null;
		try {
			
		    String dbType;
            if (StringHelper.isEmpty(poolname) || DataSourceXML.SYS_LOCAL_POOLNAME.equals(poolname)) {
            	dbType = new RecordSet().getDBType(poolname);
            } else {
                DataSource datasource = (DataSource) StaticObj.getServiceByFullname("datasource."+poolname, DataSource.class);
                dbType = datasource.getType();
            }
			
			int totalSize=0;
			String sqlCount="select count(1) c from ("+sql+") temp";
			if(dbType.toLowerCase().startsWith("informix")){
				sqlCount = "select count(1) c from ("+sql+") as temp";
			}
			logger.info("pageQuery informix >>>>> [sqlCount:"+sqlCount+",poolname:"+poolname+",orderKey:"+orderKey+",dbType:"+dbType+"]");
			
			Map<String,Object> map=this.queryMap(sqlCount, poolname);
			if(map!=null){
				totalSize=NumberHelper.string2Int(map.get("c"), 0);
			}
			int avaCount=0;
			List<Map<String,Object>> dataList=new ArrayList<>();
			if (pageSize == -1) pageSize = totalSize > 0 ? totalSize : 1;
			if(totalSize>0){
			    if(StringHelper.isEmpty(orderKey)){
			        orderKey = "order by id desc";
			    }
				int iNextNum = pageNo * pageSize;
		       	
		       	if(dbType.toLowerCase().startsWith("oracle")){
		       		sql = "select tl2.*,rownum rownum_ from (select tl1.* from(" + sql + ") tl1 "+orderKey+" nulls last) tl2";
		       		sql = "select tl3.* from(" + sql + ") tl3  where rownum_ > " + (iNextNum - pageSize) + " and rownum_ <= " + iNextNum;
		       	}else if(dbType.toLowerCase().startsWith("mysql")){
		       		sql = "select * from ("+sql+") t " + orderKey + " limit " + pageSize*(pageNo-1) + " , " + pageSize;
		       	}else if(dbType.toLowerCase().startsWith("db2")){
		       		sql = "select ROW_NUMBER() OVER ("+orderKey+") AS RowNumber,A.* from (" + sql + ") A";
	       			sql = "select * from (" + sql + ") B where B.RowNumber > " + (iNextNum - pageSize) + " and B.RowNumber <= " + iNextNum;
		       	}
				else if(dbType.toLowerCase().startsWith("postgresql")){
					sql = "select * from ("+sql+") t " + orderKey + " limit " + pageSize + " offset  " + pageSize*(pageNo-1);
				}
				else if(dbType.toLowerCase().startsWith("informix")){
					sql = "select skip "+pageSize*(pageNo-1)+" first "+pageSize+" t.* from ("+sql+") t " + orderKey;
				}
		       	else{
		       		int formIndex = sql.toLowerCase().indexOf("from");
					String preSql = "";
					if (formIndex != -1) {
						preSql = sql.substring(0, formIndex);
					}
					if (!"".equals(preSql) && preSql.matches("(?i)[\\S\\s]+[\\s]+(top)[\\s]+[0-9]+[\\s]+(?:(?!percent).)+[\\S\\s]+")) {
						sql += " " + orderKey;
					}
		       		sql = "select ROW_NUMBER() OVER ("+orderKey+") AS _RowNumber,A.* from (" + sql + ") A";
	       			sql = "select * from (" + sql + ") B where B._RowNumber > " + (iNextNum - pageSize) + " and B._RowNumber <= " + iNextNum;
		       	}

				logger.info("pageQuery informix2 >>>>> [sql:"+sql+", poolname:"+poolname+", params:"+params+"]");
		       	dataList = this.queryMapList(sql, poolname, params);
		       	if(dataList!=null){
					avaCount=dataList.size();
				}
			}
			int start=(pageNo-1)*pageSize+1;
			pageModel=new PageModel(start, avaCount, totalSize, pageSize, dataList);
		}catch (Exception e) {
			logger.error(getClass(),e);
			e.printStackTrace();
		}
		return pageModel;
	}
	
	@Override
	public List<T> query(String sql, Object... params) {
	    WeaverQueryRunner qr = new WeaverQueryRunner();
        List<T> dataList = new ArrayList<T>();
        try {
            dataList = qr.query(sql, this.poolname, new WeaverBeanListHandler<T>(entityClass), params);
        } catch (Exception e) {
            logger.error(getClass(),e);
            e.printStackTrace();
        }
        return dataList;
	}
	
	@Override
    public List<T> query(String sql) {
        return this.query(sql, new Object[]{});
    }
	
	@Override
	public List<Map<String,Object>> queryMapList(String sql, Object... params) {
	    return this.queryMapList(sql, this.poolname, params);
	}
	
	public List<Map<String,Object>> queryMapList(String sql, String poolname, Object... params) {
	    List<Map<String, Object>> resultList = new ArrayList<Map<String,Object>>();
        RecordSet rs = new RecordSet();
        if(StringHelper.isEmpty(poolname) || DataSourceXML.SYS_LOCAL_POOLNAME.equals(poolname)){
            rs.executeQuery(sql, params);
        }else{
            rs.executeQueryWithDatasource(sql, poolname, params);
        }
        String[] columnNames = rs.getColumnName();
        while(rs.next()){
            Map<String, Object> resultMap = new IgnoreCaseHashMap<String, Object>();
            for(String columnName : columnNames){
                resultMap.put(columnName, rs.getString(columnName));
            }
            resultList.add(resultMap);
        }
        return resultList;
	}
	
	@Override
	public Map<String,Object> queryMap(String sql){
	    return this.queryMap(sql, this.poolname);
	}

	public Map<String,Object> queryMap(String sql, String poolname){
	    Map<String, Object> resultMap = new IgnoreCaseHashMap<String, Object>(); 
        RecordSet rs = new RecordSet();
        if(StringHelper.isEmpty(poolname) || DataSourceXML.SYS_LOCAL_POOLNAME.equals(poolname)){
            rs.executeQuery(sql);
        }else{
            rs.executeQueryWithDatasource(sql, poolname);
        }
        String[] columnNames = rs.getColumnName();
        if(rs.next()){
            for(String columnName : columnNames){
                resultMap.put(columnName, rs.getString(columnName));
            }
        }
        return resultMap;
	}
	
	@Override
	public boolean update(T t) {
		WeaverConnection conn = getConnection();
		if(conn == null) {
			return false;
		}
		String sql = t.toModifySQL();
		if(StringHelper.isEmpty(sql)) {
			sql = this.makeSql(SQL_UPDATE);
			sql = sql +" WHERE id=" + t.getId();
		}
		Object[] args = null;
		List<Object> datas = t.getValues();
		if(datas == null) {
			args = this.setArgs(t, SQL_UPDATE);
		} else {
			args = datas.toArray();
		}	
		WeaverQueryRunner qr = new WeaverQueryRunner();
		try {
			int row=qr.alter(conn,sql.toString(), args);
			CacheFactory.getInstance().removeCacheForSql(sql);
			return row>0;
		} catch (SQLException e) {
			logger.error(getClass(),e);
			e.printStackTrace();
			TransactionContextHolder.setHappenException(true);
			return false;
		} finally {   
			TransactionContextHolder.close(conn);   
        } 
	
	}

	@Override
	public boolean update(String sql, Object... params) {
        boolean flag = false;   
        WeaverConnection conn = getConnection();
        if(conn == null) {
        	return false;
        } 
        try {            
            QueryRunner qRunner = new QueryRunner();   
            int i = qRunner.update(conn, sql,params);   
            if (i > 0) {   
                flag = true;   
            }
			CacheFactory.getInstance().removeCacheForSql(sql);
        } catch (SQLException e) {
        	logger.error(getClass(),e);
            e.printStackTrace();   
            TransactionContextHolder.setHappenException(true);
        } finally {   
        	TransactionContextHolder.close(conn);   
        }   
        return flag;   

	}

	@Override
	public boolean alter(String sql) {
        boolean flag = false;   
        WeaverConnection conn = getConnection();
        if(conn == null) {
        	return false;
        } 
        try {
        	WeaverQueryRunner qRunner = new WeaverQueryRunner();   
            qRunner.alter(conn, sql);   
            flag = true;
			CacheFactory.getInstance().removeCacheForSql(sql);
        } catch (SQLException e) {
        	logger.error(getClass(),e);
            e.printStackTrace();   
            TransactionContextHolder.setHappenException(true);
        } finally {   
        	TransactionContextHolder.close(conn);   
        }   
        return flag;   

	}
	
	/**
	 * 获取数据库链接
	 *
	 */
	public WeaverConnection getConnection(){
		String poolname=getPoolname();
		return getConnection(poolname);
	}
	
	/**
	 * 获取数据库链接
	 *
	 */
	private WeaverConnection getConnection(String poolname) {
		WeaverConnection conn = null;
        try {
        	conn = TransactionContextHolder.getConnection(poolname);
    		if(conn == null || conn.isClosed()) {
    			return null;
    		}
        }
        catch(Exception e) {
        	logger.error(getClass(),e);
        	e.printStackTrace();
        }
        return conn;
    }
	    
	    /**
	     * 获取持久化表名
	     * @return
	     */
	    private String getPersistentTable() {
	    	PersistentTable ptable = entityClass.getAnnotation(PersistentTable.class);
			String ptablename = null;
			if(ptable != null) {
				ptablename = ptable.name();
			}
			ptablename = StringHelper.isEmpty(ptablename)?entityClass.getSimpleName():ptablename;
			return ptablename;
	    }
	    
	 // 组装SQL
		private String makeSql(String sqlFlag) {
			StringBuffer sql = new StringBuffer();
			StringBuffer vsql = new StringBuffer();
			Field[] fields = entityClass.getDeclaredFields();
			String ptablename = this.getPersistentTable();
			if (sqlFlag.equals(SQL_INSERT)) {
				sql.append(" INSERT INTO " + ptablename);
				sql.append("(");
				vsql.append(") VALUES (");
				for (int i = 0; fields != null && i < fields.length; i++) {
					Object np = fields[i].getAnnotation(Nopersistent.class);
					if(np != null) {
						continue;
					}
					fields[i].setAccessible(true); // 
					String column = fields[i].getName();
					sql.append(column).append(",");
					vsql.append("?,");
				}
				sql = sql.deleteCharAt(sql.length() - 1);
				vsql = vsql.deleteCharAt(vsql.length() - 1);
				vsql.append(")");
				sql.append(vsql);
			} else if (sqlFlag.equals(SQL_UPDATE)) {
				sql.append(" UPDATE " + ptablename + " SET ");
				for (int i = 0; fields != null && i < fields.length; i++) {
					Object np = fields[i].getAnnotation(Nopersistent.class);
					if(np != null) {
						continue;
					}
					fields[i].setAccessible(true); // 
					String column = fields[i].getName();
					if (column.equals("id")) { // id 代表主键
						continue;
					}
					sql.append(column).append("=").append("?,");
				}
				sql = sql.deleteCharAt(sql.length() - 1);
				//sql.append(" WHERE id=?");
			} else if (sqlFlag.equals(SQL_DELETE)) {
				sql.append(" DELETE FROM " + ptablename + " WHERE id=?");
			}
			return sql.toString();

		}

		// 设置参数
		private Object[] setArgs(T entity, String sqlFlag) {
			Field[] fields = entityClass.getDeclaredFields();
			int size = fields.length;
			List<Object> args = new ArrayList<Object>();
			if (sqlFlag.equals(SQL_INSERT)) {				
				for (int i = 0; args != null && i < size; i++) {
					try {
						Object np = fields[i].getAnnotation(Nopersistent.class);
						if(np != null) {
							continue;
						}
						fields[i].setAccessible(true); // 
						Object value = fields[i].get(entity);
						args.add(value);
					} catch (Exception e) {
						e.printStackTrace();
					}
				}
				return args.toArray();
			} else if (sqlFlag.equals(SQL_UPDATE)) {
				for (int i = 0;  i < size; i++) {
					try {
						Object np = fields[i].getAnnotation(Nopersistent.class);
						if(np != null) {
							continue;
						}
						fields[i].setAccessible(true); // 
						Object value  = fields[i].get(entity);
						args.add(value);
					} catch (Exception e) {
						e.printStackTrace();
					}
				}
				
				return args.toArray();
			} else if (sqlFlag.equals(SQL_DELETE)) {
				fields[0].setAccessible(true); // 
				try {
					Object value = fields[0].get(entity);
					args.add(value);
				} catch (Exception e) {
					e.printStackTrace();
				}
				return args.toArray();
			}
			return null;

		}

		// 设置参数类型（还没写全，只是一些常用的）
		@SuppressWarnings("unused")
		private int[] setArgTypes(T entity, String sqlFlag) {
			Field[] fields = entityClass.getDeclaredFields();
			if (sqlFlag.equals(SQL_INSERT)) {
				int[] argTypes = new int[fields.length];
				try {
					for (int i = 0; argTypes != null && i < argTypes.length; i++) {
						fields[i].setAccessible(true); // 
						if (fields[i].get(entity).getClass().getName().equals("java.lang.String")) {
							argTypes[i] = Types.VARCHAR;
						} else if (fields[i].get(entity).getClass().getName().equals("java.lang.Double")) {
							argTypes[i] = Types.DECIMAL;
						} else if (fields[i].get(entity).getClass().getName().equals("java.lang.Integer")) {
							argTypes[i] = Types.INTEGER;
						} else if (fields[i].get(entity).getClass().getName().equals("java.util.Date")) {
							argTypes[i] = Types.DATE;
						}
					}
				} catch (Exception e) {
					e.printStackTrace();
				}
				return argTypes;
			} else if (sqlFlag.equals(SQL_UPDATE)) {
				int[] tempArgTypes = new int[fields.length];
				int[] argTypes = new int[fields.length];
				try {
					for (int i = 0; tempArgTypes != null && i < tempArgTypes.length; i++) {
						fields[i].setAccessible(true); // 
						if (fields[i].get(entity).getClass().getName().equals("java.lang.String")) {
							tempArgTypes[i] = Types.VARCHAR;
						} else if (fields[i].get(entity).getClass().getName().equals("java.lang.Double")) {
							tempArgTypes[i] = Types.DECIMAL;
						} else if (fields[i].get(entity).getClass().getName().equals("java.lang.Integer")) {
							tempArgTypes[i] = Types.INTEGER;
						} else if (fields[i].get(entity).getClass().getName().equals("java.util.Date")) {
							tempArgTypes[i] = Types.DATE;
						}
					}
					System.arraycopy(tempArgTypes, 1, argTypes, 0, tempArgTypes.length - 1); // 数组拷贝
					argTypes[argTypes.length - 1] = tempArgTypes[0];

				} catch (Exception e) {
					e.printStackTrace();
				}
				return argTypes;

			} else if (sqlFlag.equals(SQL_DELETE)) {
				int[] argTypes = new int[1]; // 长度是1
				try {
					fields[0].setAccessible(true); // 
					if (fields[0].get(entity).getClass().getName().equals("java.lang.String")) {
						argTypes[0] = Types.VARCHAR;
					} else if (fields[0].get(entity).getClass().getName().equals("java.lang.Integer")) {
						argTypes[0] = Types.INTEGER;
					}

				} catch (Exception e) {
					e.printStackTrace();
				}
				return argTypes;
			}
			return null;
		}

	public String getPoolname() {
		return poolname;
	}

	public void setPoolname(String poolname) {
		this.poolname = poolname;
	}
}