package org.ithang;

import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.ithang.tools.ModelTools;
import org.ithang.tools.lang.StrUtils;

/**
 * 用于生成SQL的唯一可用类
 * @author ithang
 *
 * @param <T>
 */
@Deprecated
public class ModelSQL<T> {

	private String tableName;//表名称
	private String tableLabel;//表别名 在ModelTools中随机出来
	private String[] columnNames;//所有表列名称
	private String[] fieldNames;//所有实体类字段名称
	private String[] columnNames_noid;//所有表列名称,不包括主键
	private String[] fieldNames_noid;//所有实体类字段名称,不包括主键
	
	private String columnNameStr;//所有列名称以逗号链接的字符串
	private String fieldNameStr;//所有字段名称以逗号链接的字符串
	private String columnNameStr_noid;//所有列名称以逗号链接的字符串,不包括主键
	private String fieldNameStr_noid;//所有字段名称以逗号链接的字符串,不包括主键
	
	private String fieldHodler;//例如 insert into Tab(a,b,c)values([?,?,?])
	private String fieldNameHolder;//例如 insert into Tab(a,b,c)values([:a,:b,:c])
	private String fieldHodler_noid;//例如 insert into Tab(a,b,c)values([?,?,?]),不包括主键
	private String fieldNameHolder_noid;//例如 insert into Tab(a,b,c)values([:a,:b,:c]),不包括主键
	
	private String queryColumns;//带前缀的查询列
	
	private int fieldNum=0;
	private List<Model> models;
	
	private StringBuilder sb=null; //用于构建查询
	private boolean selected=false;//用于标示是否己构建查询
	private Class<T> prototype=null;//封装类型
	private static Map<String,List<Model>> allTabs=new HashMap<String,List<Model>>(50);
	
	public ModelSQL(Class<T> cls){
		setPrototype(cls);
		sb=new StringBuilder();
		if(null!=allTabs.get(cls.getSimpleName())){//如果缓存中存在，则用缓存
			models=allTabs.get(cls.getSimpleName());
		}else{//如果缓存中不存在，则解析
			models=ModelTools.parseCls(cls);
			allTabs.put(cls.getSimpleName(), models);
		}
		
		if(null!=models&&models.size()>0){
			fieldNum=models.size();
			tableName=models.get(0).getTableName();
			tableLabel=models.get(0).getTableLabel();
			columnNames=new String[fieldNum];
			fieldNames=new String[fieldNum];
			
			columnNames_noid=new String[fieldNum];
			fieldNames_noid=new String[fieldNum];
			
			int i=0;
			int ii=0;
			StringBuilder sber=new StringBuilder();//构建占位符字符串
			StringBuilder sber_noid=new StringBuilder();//构建占位符字符串
			
			StringBuilder sfer=new StringBuilder();//构建还前缀的查询列
			
			for(Model md:models){
				fieldNames[i]=md.getFieldName();
				columnNames[i]=md.getColumnName();
				
				if(0!=i++){
					sber.append(",");
					sfer.append(",");
				}
				sber.append("?");
				sfer.append(md.getTableLabel()).append(".").append(md.getColumnName());
				
				
				if(!md.isPrimary()){
					fieldNames_noid[ii]=md.getFieldName();
					columnNames_noid[ii]=md.getColumnName();
					if(0!=ii++){
						sber_noid.append(",");
					}
					sber_noid.append("?");
				}
			}
			fieldNameStr=StrUtils.pkg(fieldNames, ":", ",");
			fieldNameStr=fieldNameStr.substring(0,fieldNameStr.length()-1);
			
			fieldNameStr_noid=StrUtils.pkg(fieldNames_noid, ":", ",");
			fieldNameStr_noid=fieldNameStr_noid.substring(0,fieldNameStr_noid.length()-1);
			
			columnNameStr=StrUtils.pkg(columnNames, "", ",");
			columnNameStr=columnNameStr.substring(0,columnNameStr.length()-1);
			
			columnNameStr_noid=StrUtils.pkg(columnNames_noid, "", ",");
			columnNameStr_noid=columnNameStr_noid.substring(0,columnNameStr_noid.length()-1);
			
			fieldHodler=sber.toString();
			fieldNameHolder=StrUtils.pkg(fieldNames, ":", ",");
			fieldNameHolder=fieldNameHolder.substring(0,fieldNameHolder.length()-1);
			
			fieldHodler_noid=sber.toString();
			fieldNameHolder_noid=StrUtils.pkg(fieldNames_noid, ":", ",");
			fieldNameHolder_noid=fieldNameHolder_noid.substring(0,fieldNameHolder_noid.length()-1);
			
			queryColumns=sfer.toString();
		}
	}
	
	/**
	 * 生成insertSQL,默认byName=false;includeID=true
	 * @return
	 */
	public String insertSQL(){
		return insertSQL(false,true);
	}
	
	/**
	 * 生成insert sql 不带参数值
	 * @param byName 是否字段名做为参数
	 * @return
	 */
	public String insertSQL(boolean byName){
		return insertSQL(byName,true);
	}
	
	/**
	 * 生成insert语句
	 * @param byName 是否能过字段匹配参数,true是,false否
	 * @param includeID 是否包括ID字段
	 * @return
	 */
	public String insertSQL(boolean byName,boolean includeID){
		StringBuilder sber=new StringBuilder();
		sber.append("insert into ").append(tableName);
		sber.append("(");
		if(includeID){
		    sber.append(columnNameStr);
		}else{
			sber.append(columnNameStr_noid);
		}
		
		sber.append(") values (");
		
		if(byName){
			if(includeID){
			    sber.append(fieldNameHolder);
			}else{
				sber.append(fieldNameHolder_noid);
			}
		}else{
			if(includeID){
			    sber.append(fieldHodler);
			}else{
				sber.append(fieldHodler_noid);
			}
		}
		sber.append(")");
		return sber.toString();
	}
	
	
	/**
	 * 根据实体Bean生成insert sql
	 * @param bean
	 * @return
	 */
	public String insertSQL(T bean){
		StringBuilder sber=new StringBuilder();
		sber.append("insert into ").append(tableName);
		sber.append("(").append(columnNameStr).append(") values (");
		
		for(int i=0;i<fieldNum;i++){
			if(0!=i){
				sber.append(",");
			}
			sber.append(getModel(fieldNames[i]).getBeanFieldValue(bean));
		}
		
		sber.append(")");
		return sber.toString();
	}
	
	/**
	 * 根据map生成insert sql
	 * @param map 以fieldName为键，以fieldValue为值的键值对
	 * @return
	 */
	public String insertSQL(Map<String,Object> map){
		StringBuilder sber=new StringBuilder();
		sber.append("insert into ").append(tableName);
		sber.append("(");
		int i=0;
		for(String fieldName:fieldNames){
			if(map.containsKey(fieldName)){
				if(0!=i++){
					sber.append(",");
				}
				sber.append(getModel(fieldName).getColumnName());
			}
		}
		sber.append(") values (");
		i=0;
		for(String fieldName:fieldNames){
			if(map.containsKey(fieldName)){
				if(0!=i++){
					sber.append(",");
				}
				Model m=getModel(fieldName);
				sber.append(m.getMapFieldValue(map));
			}
		}
		sber.append(")");
		return sber.toString();
	}
	
	
	public String insertMapsSQL(List<Map<String,Object>> maps){
		StringBuilder sber=new StringBuilder();
		sber.append("insert into ").append(tableName);
		sber.append("(").append(columnNameStr).append(") values ");
		
		maps.forEach(mp->{
			int i=0;
			for(String fieldName:fieldNames){
				if(0!=i++){
					sber.append(",");
				}else{
					sber.append("(");
				}
				Model m=getModel(fieldName);
				sber.append(m.getMapFieldValue(mp));
			}
			if(fieldNum==i){
				sber.append("),");	
			}
		});
		return sber.delete(sber.length()-1, sber.length()).toString();
	}
	
	public String insertBeansSQL(List<T> beans){
		StringBuilder sber=new StringBuilder();
		sber.append("insert into ").append(tableName);
		sber.append("(").append(columnNameStr).append(") values ");
		
		beans.forEach(bean->{
			int i=0;
			for(;i<fieldNum;i++){
				if(0!=i++){
					sber.append(",");
				}else{
					sber.append("(");
				}
				sber.append(getModel(fieldNames[i]).getBeanFieldValue(bean));
			}
			if(fieldNum==i){
				sber.append("),");	
			}
		});
		return sber.delete(sber.length()-1, sber.length()).toString();
	}
	
	
	
	/**
	 * 生成update sql
	 */
	public String updateSQL(boolean byName){
		StringBuilder sber=new StringBuilder();
		sber.append("update ").append(tableName).append(" set ");
		StringBuilder where=new StringBuilder(" where ");
		int k=0;
		int f=0;
		for(int i=0;i<fieldNum;i++){
			if(getModel(fieldNames[i]).isPrimary()){
				if(f++>0){
					where.append(" and ");
				}
				where.append(columnNames[i]).append("=");
				if(byName){
					where.append(":").append(fieldNames[i]);
				}else{
					where.append("?");
				}
			}else{
				if(k++>0){
					sber.append(",");
				}
				sber.append(columnNames[i]).append("=");
				if(byName){
					sber.append(":").append(fieldNames[i]);
				}else{
					sber.append("?");
				}
			}
		}
		sber.append(where.toString());
		return sber.toString();
	}
	
	/**
	 * 
	 * @param bean
	 * @return
	 */
	public String updateSQL(Object bean){
		StringBuilder sber=new StringBuilder();
		sber.append("update ").append(tableName).append(" set ");
		StringBuilder where=new StringBuilder(" where ");
		int k=0;
		int f=0;
		for(int i=0;i<fieldNum;i++){
			Model m=getModel(fieldNames[i]);
			if(m.isPrimary()){
				if(f++>0){
					where.append(" and ");
				}
				where.append(columnNames[i]).append("=").append(m.getBeanFieldValue(bean));
			}else{
				if(k++>0){
					sber.append(",");
				}
				sber.append(columnNames[i]).append("=").append(m.getBeanFieldValue(bean));
			}
		}
		sber.append(where.toString());
		return sber.toString();
	}
	
	/**
	 * 根据map生成update sql
	 * @param values
	 * @return
	 */
	public String updateSQL(Map<String,Object> values){
		StringBuilder sber=new StringBuilder();
		sber.append("update ").append(tableName).append(" set ");
		StringBuilder where=new StringBuilder(" where ");
		int k=0;
		int f=0;
		for(int i=0;i<fieldNum;i++){
			if(!values.containsKey(fieldNames[i])){
				continue;
			}
			Model m=getModel(fieldNames[i]);
			if(m.isPrimary()){
				if(f++>0){
					where.append(" and ");
				}
				where.append(columnNames[i]).append("=").append(m.getMapFieldValue(values));
			}else{
				if(k++>0){
					sber.append(",");
				}
				sber.append(columnNames[i]).append("=").append(m.getMapFieldValue(values));
			}
		}
		sber.append(where.toString());
		return sber.toString();
	}
	
	/**
	 * 
	 * @param byName
	 * @return
	 */
	public String deleteSQL(boolean byName){
		StringBuilder sber=new StringBuilder("delete from ");
		sber.append(tableName).append(" where ");
		Model[] ms=getPrimaryModel();
		int i=0;
		for(Model m:ms){
			if(i++>0){
				sber.append(" and ");
			}
			sber.append(m.getColumnName()).append("=");
			if(byName){
				sber.append(":").append(m.getFieldName());
			}else{
				sber.append("?");
			}
		}
	    return sber.toString();	
	}
	
	/**
	 * 批量删除,适合只有一个主键字段
	 * @param ids
	 * @return
	 */
	public String deleteSQL(String...ids){
		StringBuilder sber=new StringBuilder("delete from ");
		sber.append(tableName).append(" where ");
		Model[] ms=getPrimaryModel();
		if(null!=ids&&ids.length>0){
			if(ids.length>1){
				int i=0;
				for(Model m:ms){
					if(i++>0){
						sber.append(" and ");
					}
					sber.append(m.getColumnName()).append(" in (");
					if("String".equals(m.getFieldType())){
						sber.append(StrUtils.pkg(ids,"'","',"));
					}else{
						sber.append(StrUtils.pkg(ids,"",","));
					}
					sber.delete(sber.length()-1, sber.length());
					sber.append(")");
				}
			}else{
				sber.append(ms[0].getColumnName()).append("=");
				if("String".equals(ms[0].getFieldType())){
					sber.append("'").append(ids[0]).append("'");
				}else{
					sber.append(ids[0]);
				}
			}
		}else{
			return deleteSQL(false);
		}
		
		return sber.toString();
	}
	
	/**
	 * 根据条件删除
	 * @param values
	 * @return
	 */
	public String deleteSQL(Map<String,Object> values){
		StringBuilder sber=new StringBuilder("delete from ");
		sber.append(tableName).append(" where ");
		int i=0;
		for(Model m:models){
			if(values.containsKey(m.getFieldName())){
				if(i++>0){
					sber.append(" and ");
				}
				sber.append(m.getColumnName()).append("=").append(m.getMapFieldValue(values));
			}
		}
		return sber.toString();
	}
	
	/**
	 * 查询起步
	 * @return modelSQL 在其础上进行灵活配置
	 */
	public ModelSQL<T> select(){
		if(selected){//如果己构建则删除，因为可能包含了where join 等子句
			sb.delete(0, sb.length());
		}
		sb.append("select ");
		sb.append(queryColumns).append(" from ").append(tableName).append(" ").append(tableLabel);
		selected=true;
		return this;
	}
	
	/**
	 * 简单分页查询,相当于select+limit
	 * @param from
	 * @param size
	 * @return
	 */
	public String listSQL(int from,int size){
		return select().limit(from, size);
	}
	
	/**
	 * 简单综合查询,相当于select+where
	 * @param values
	 * @return
	 */
	public String listSQL(Map<String,Object> values){
		return select().where(values).toString();
	}
	
	/**
	 * 直接返回一个新构建的StringBuilder让使用者自己构建where
	 * @param columnName
	 * @param columnValue
	 * @return
	 */
	public StringBuilder where(String columnName,Object columnValue){
		if(!selected){//如果未生成select语句，则生成
			select();
		}
		sb.append(" where ").append(columnName).append("=");
		if(columnValue instanceof String){
			sb.append("'").append(columnValue).append("'");
		}else{
			sb.append(columnValue);
		}
		
		return sb;
	}
	
	/**
	 * 简单综合查询
	 * @param values
	 * @return
	 */
	public StringBuilder where(Map<String,Object> values){
		if(!selected){//如果未生成select语句，则生成
			select();
		}
		if(null!=values&&values.size()>0){
			sb.append(" where ");
			int i=0;
			for(Model m:models){
				if(values.containsKey(m.getFieldName())){
					if(i++>0){
						sb.append(" and ");
					}
					sb.append(tableLabel).append(".").append(m.getColumnName()).append("=").append(m.getMapFieldValue(values));
				}
			}
			if(0==i){
				sb.append(" 1=1");
			}
		}
		
		return sb;
	}
	
	public Limit ascGroupBy(String..._columnNames){
		if(!selected){
			select();
		}
		if(null!=_columnNames&&_columnNames.length>0){
			sb.append(" group by ");
			for(String cn:_columnNames){
				sb.append(tableLabel).append(".").append(cn).append(",");
			}
			sb.delete(sb.length()-1, sb.length());
			sb.append(" asc ");
		}
		return new Limit(sb);
	}
	
	public Limit descGroupBy(String..._columnNames){
		if(!selected){
			select();
		}
		if(null!=_columnNames&&_columnNames.length>0){
			sb.append(" group by ");
			for(String cn:_columnNames){
				sb.append(tableLabel).append(".").append(cn).append(",");
			}
			sb.delete(sb.length()-1, sb.length());
			sb.append(" desc ");
		}
		return new Limit(sb);
	}
	
	/**
	 * 分页
	 * @param from
	 * @param size
	 * @return
	 */
	public String limit(int from,int size){
		if(!selected){
			select();
		}
		sb.append(" limit ").append(from).append(",").append(size);
		return sb.toString();
	}
	
	public ModelSQL<T> leftJoin(ModelSQL<?> _ms,String acolumnName,String bcolumnName){
		if(!selected){
			select();
		}
		sb.insert(sb.indexOf("from")-1,","+_ms.getQureyColumns());
		sb.append(" left join ").append(_ms.getTableName()).append(" ").append(_ms.getTableLabel());
		sb.append(" on ");
		sb.append(" ").append(getTableLabel()).append(".").append(acolumnName).append("=").append(_ms.getTableLabel()).append(".").append(bcolumnName);
		return this;
	}
	
	public ModelSQL<T> leftJoin(ModelSQL<?> _ms,String[] acolumnNames,String[] bcolumnNames){
		if(!selected){
			select();
		}
		sb.insert(sb.indexOf("from")-1,","+_ms.getQureyColumns());
		sb.append(" left join ").append(_ms.getTableName()).append(" ").append(_ms.getTableLabel());
		sb.append(" on ");
		int len=acolumnNames.length>bcolumnNames.length?bcolumnNames.length:acolumnNames.length;
		for(int i=0;i<len;i++){
			sb.append(" ").append(getTableLabel()).append(".").append(acolumnNames[i]).append("=").append(_ms.getTableLabel()).append(".").append(bcolumnNames[i]);
		}
		return this;
	}
	
	public ModelSQL<T> innerJoin(ModelSQL<?> _ms,String acolumnName,String bcolumnName){
		if(!selected){
			select();
		}
		sb.insert(sb.indexOf("from")-1,","+_ms.getQureyColumns());
		sb.append(" inner join ").append(_ms.getTableName()).append(" ").append(_ms.getTableLabel());
	    sb.append(" ").append(getTableLabel()).append(".").append(acolumnName).append("=").append(_ms.getTableLabel()).append(".").append(bcolumnName);
		return this;
	}
	
	public ModelSQL<T> innerJoin(ModelSQL<?> _ms,String[] acolumnNames,String[] bcolumnNames){
		if(!selected){
			select();
		}
		sb.insert(sb.indexOf("from")-1,","+_ms.getQureyColumns());
		sb.append(" inner join ").append(_ms.getTableName()).append(" ").append(_ms.getTableLabel());
		int len=acolumnNames.length>bcolumnNames.length?bcolumnNames.length:acolumnNames.length;
		for(int i=0;i<len;i++){
			sb.append(" ").append(getTableLabel()).append(".").append(acolumnNames[i]).append("=").append(_ms.getTableLabel()).append(".").append(bcolumnNames[i]);
		}
		return this;
	}
	
	public String createTable(boolean delExist){
		StringBuilder sber=new StringBuilder();
		if(delExist){
			sber.append(" drop table `").append(tableName).append("` ;\n");
		}
		sber.append(" create table `").append(tableName).append("` ( \n");
		String primaryKey=null;
		for(Model model:models){
			if(model.isPrimary()){
				if(null==primaryKey){
					primaryKey="`"+model.getColumnName()+"`";	
				}else{
					primaryKey+=",`"+model.getColumnName()+"`";
				}
			}
			sber.append("\t\t\t`").append(model.getColumnName()).append("` ").append(model.getColumnType());
			if(model.getColumnLen()>0){
				sber.append("(").append(model.getColumnLen()).append(")");
			}
			
			//是否可为空
			if(model.isNULL()){
				sber.append(" NULL ");
			}else{
				sber.append(" NOT NULL ");
			}
			
			if(null!=model.getValue()){
				sber.append(" default ");
				if("varchar".equals(model.getColumnType())||model.getColumnType().toLowerCase().endsWith("text")){
					sber.append("'").append(model.getValue()).append("'");
				}else{
				    sber.append(model.getValue());
				}
			}
			if(model.isAutoIncrement()){
				sber.append(" AUTO_INCREMENT");
			}
			sber.append(",\n");
		}
		
		if(null!=primaryKey){
			sber.append("\t\t\tPRIMARY KEY (").append(primaryKey).append(")");
		}else{
			sber.delete(sber.length()-2, sber.length());
		}
		sber.append("\n\t)ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci;");
		return sber.toString();
	}
	
	public String getQureyColumns(){
		return queryColumns;
	}
	
	public String getTableName(){
		return tableName;
	}
	
	public String getTableLabel(){
		return tableLabel;
	}
	
	public String toSQL(){
		return sb.toString();
	}
	
	private Model[] getPrimaryModel(){
		Model[] ms=new Model[fieldNum];
		int i=0;
		for(Model m:models){
			if(m.isPrimary()){
				ms[i++]=m;
			}
		}
		return Arrays.copyOf(ms, i);
	}
	
	private Model getModel(String fieldName){
		Model model=null;
		for(Model m:models){
			if(fieldName.equals(m.getFieldName())){
				model=m;
				break;
			}
		}
		return model;
	}
	
	protected StringBuilder getSQL(){
		return sb;
	}
	
	@Override
	public String toString() {
		return toSQL();
	}

	public Class<T> getPrototype() {
		return prototype;
	}

	public void setPrototype(Class<T> prototype) {
		this.prototype = prototype;
	}
	
}