package foundation.persist;

import java.math.BigDecimal;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.Set;

import foundation.dao.Filter;
import foundation.dao.OrderBy;
import foundation.dao.Page;
import foundation.data.entity.Entity;
import foundation.data.entity.EntitySet;
import foundation.data.meta.field.Field;
import foundation.data.meta.field.FieldsRuntime;
import foundation.data.object.DataJoins;
import foundation.server.config.DBaseType;
import foundation.util.ContentBuilder;
import foundation.util.DBDate;
import foundation.util.Util;
import foundation.variant.expression.VariantExpression;
import foundation.variant.expression.VariantList;
import foundation.variant.expression.VariantSegment;
import foundation.variant.provider.DataEvent;
import foundation.variant.provider.IVariantsConsumer;
import foundation.variant.provider.IVariantsProvider;
import foundation.variant.provider.VariantLink;


public class NamedSQL implements Iterable<VariantSegment>, IVariantsConsumer {

	public static final String Code_GetEntity = "getEntity";
	public static final String Code_GetJoinEntity = "getJoinEntity";
	public static final String Code_GetFirstEntity = "getFirstEntity";
	public static final String Code_GetCount = "getCount";
	public static final String Code_GetJoinCount = "getJoinCount";
	public static final String Code_InsertEntity = "insertEntity";
	public static final String Code_UpdateEntityById = "updateById";
	public static final String Code_UpdateByCriteria = "updateByCriteria";
	public static final String Code_UpdateByFilter = "updateByFilter";
	
	public static final String Code_DeleteByID = "deleteByID";
	public static final String Code_DeleteByFilter = "deleteByCriteria";
	public static final String Code_DeleteChildrenByID = "deleteChildrenByID";
	
	public static final String Param_Schema = "schema";
	public static final String Param_TableName = "tablename";
	public static final String Param_FieldNames = "fieldNames";
	public static final String Param_FieldNameValues = "fieldNameValues";
	public static final String Param_FieldValues = "fieldValues";
	public static final String Param_FieldMetas = "fieldMetas";
	public static final String Param_PlaceHolders = "placeHolders";
	public static final String Param_Filter = "filter";
	public static final String Param_FieldNamePlaceHolders = "fieldNamePlaceHolders";
	public static final String Param_KeyFieldName = "keyFieldName";
	public static final String Param_OrderBy = "orderby";
	public static final String Param_Page_limit = "limit";
	public static final String Param_Page_RowNo = "rowNo"; 
	
	private static final String Param_CommaSeparator = "commaSeparator";
	private static final String Param_JoinSelectFields = "joinSelectFields";
	private static final String Param_JoinSQL = "joinSQL";
	
	private static NamedSQLContainer namedSQLContainer;
	
	protected String name;
	protected String sql;
	protected VariantExpression expression;
	protected DBaseType dbaseType;
	
	static {
		namedSQLContainer = NamedSQLContainer.getInstance();
	}

	public NamedSQL() throws Exception {
	}
	
	public NamedSQL(String name, String sql) throws Exception {
		this.name = name;
		this.sql = sql;
		
		parseSQL(sql);
	}
	
	public NamedSQL(DBaseType dBaseType, String sql) throws Exception {
		this.dbaseType = dBaseType;
		this.sql = sql;
		
		parseSQL(sql);
	}
	
	protected void parseSQL(String sql) throws Exception {
		expression = new SQLCreator(sql);
	}
	
	public static NamedSQL getInstance(String name) throws Exception {
		return getInstance(DBaseType.getMain(), name);
	}

	public static NamedSQL getInstance(DBaseType dbaseType, String name) throws Exception {
		if (Util.isEmpty(name)) {
			return null;
		}
		
		NamedSQL result = namedSQLContainer.get(name);
		
		if (result == null) {
			result = namedSQLContainer.get(name);
		}
		
		if (result == null) {
			throw new Exception("can not find named sql: " + name);
		}
		
		result = result.createInstance();
		result.dbaseType = dbaseType;
		
		return result;
	}
	
	public NamedSQL createInstance() throws Exception {
		NamedSQL instance = new NamedSQL();
		instance.name = this.name;
		instance.sql = this.sql;
		instance.expression = this.expression.newInstance();
		instance.dbaseType = this.dbaseType;
		
		return instance;
	}

	public NamedSQL setSchema(String schema) {
		return setParam(Param_Schema, schema);
	}

	public NamedSQL setTableName(String tableName) {
		return setParam(Param_TableName, tableName.toLowerCase());
	}

	public NamedSQL setFieldNames(DataEvent event, String names) {
		return setParam(Param_FieldNames, names);
	}
	
	public NamedSQL setFieldNames(DataEvent event, FieldsRuntime tableMeta) {
		StringBuilder result = new StringBuilder();
		boolean empty = true;
		
		for (Field field: tableMeta) {
			String name = field.getName();
			
			if ((DataEvent.Update == event) && "id".equalsIgnoreCase(name)) {
				continue;
			}
			
			if (!empty) {
				result.append(", ");
			}
			
			result.append(name);
			
			empty = false;
		}
		
		return setParam(Param_FieldNames, result.toString());
	}
	
	public NamedSQL setFieldNames(DataEvent event, FieldsRuntime tableMeta, Entity entity) {
		StringBuilder result = new StringBuilder();
		boolean empty = true;
		
		FieldsRuntime entityMeta = entity.getFieldsMeta();
		
		String[] names = tableMeta.getFieldNames();
		int max = names.length;
		
		for (int i = 0; i < max; i++) {
			String name = names[i];
			
			if ((DataEvent.Update == event) && ("id".equalsIgnoreCase(name))) {
				continue;
			}
			
			if (!entityMeta.contains(name)) {
				continue;
			}
			
			if (entity.isAbsentValue(name)) {
				continue;
			}
			
			if (!empty) {
				result.append(", ");
			}

			result.append(name);
			empty = false;
		}
		
		return setParam(Param_FieldNames, result.toString());
	}
	
	public NamedSQL setFieldNames(Collection<Field> fields) {
		ContentBuilder result = new ContentBuilder();
		
		for (Field field: fields) {
			result.append(field.getName(), ", ");
		}
		
		return setParam(Param_FieldNames, result.toString());
	}

	public NamedSQL setFieldValues(Entity entity) {
		ContentBuilder result = new ContentBuilder(", ");
		
		int max = entity.getFieldCount();
		
		for (int i = 0; i < max; i++) {
			if (entity.isAbsentValue(i)) {
				continue;
			}
				
			result.append(entity.getSQLString(dbaseType, i, "null"));
		}
		
		return setParam(Param_FieldValues, result.toString());
	}
	
	public NamedSQL setFieldValues(DataEvent event, FieldsRuntime tableFieldsMeta, Entity entity) throws Exception {
		ContentBuilder result = new ContentBuilder(", ");
		
		FieldsRuntime entityMeta = entity.getFieldsMeta();
		String[] names = tableFieldsMeta.getFieldNames();
		int max = names.length;
		
		for (int i = 0; i < max; i++) {
			String name = names[i];
			
			if ((DataEvent.Update == event) && ("id".equalsIgnoreCase(name))) {
				continue;
			}
			
			if (!entityMeta.contains(name)) {
				continue;
			}
			
			if (entity.isAbsentValue(name)) {
				continue;
			}
				
			result.append(entity.getSQLString(dbaseType, name));
		}
		
		return setParam(Param_FieldValues, result.toString());		
	}

	public NamedSQL setTableMeta(FieldsRuntime entityMeta) {
		ContentBuilder result = new ContentBuilder(", ");
		
		for (Field field: entityMeta) {
			String line = "`" + field.getName() + "` " + field.getTypeSQLSegment() + " " + field.getNullableSQLSegment();
			result.append(line);
		}
		
		return setParam(Param_FieldMetas, result.toString());
	}
	
	public NamedSQL setTableMeta(String tableName, EntitySet entitySet) {
		//1. set table name
		setTableName(tableName);
		
		//2. set field meta
		ContentBuilder result = new ContentBuilder(", ");
		
		for (Entity entity: entitySet) {
			String fieldName = entity.getString("fieldName");
			String fieldType = entity.getSQLTypeCode("fieldType", "fieldLength");
			String sqlNullCode = entity.getSQLNullCode("nullable");
			
			String line = "`" + fieldName + "` " + fieldType + " " + sqlNullCode;
			result.append(line);
		}
		
		return setParam(Param_FieldMetas, result.toString());
	}
	
	public NamedSQL setQuotedFieldNames(FieldsRuntime tableMeta) {
		ContentBuilder result = new ContentBuilder();
		
		for (Field field: tableMeta) {
			result.append(Util.doubleQuotedStr(field.getName()), ", ");
		}
		
		return setParam(Param_FieldNames, result.toString());
	}

	public NamedSQL setPlaceHolders(int count) {
		ContentBuilder result = new ContentBuilder();
		
		for (int i = 0; i < count; i++) {
			result.append("?", ", ");
		}
		
		if (containsVariant(Param_PlaceHolders)) {
			return setParam(Param_PlaceHolders, result.toString());
		}
		else if (containsVariant(Param_FieldValues)) {
			return setParam(Param_FieldValues, result.toString());
		}
		
		return this;
	}
	
	public NamedSQL setPlaceHolders(String placeHolders) {
		return setParam(Param_PlaceHolders, placeHolders);
	}

	public NamedSQL setFieldNamePlaceHolders(FieldsRuntime tableMeta) {
		ContentBuilder result = new ContentBuilder();
		
		for (Field field: tableMeta) {
			result.append(field.getName() + " = ? ", ", ");
		}
		
		return setParam(Param_FieldNamePlaceHolders, result.toString());
	}

	public NamedSQL setFilter(String filter) {
		if (Util.isEmpty(filter)) {
			return this;
		}
		
		return setParam(Param_Filter, filter);	
	}
	
	public NamedSQL setFilter(Filter filter) {
		if (filter == null) {
			return this;
		}
		
		if (filter.isRaw()) {
			return setFilter(filter.getRawFilter());
		}
		
		String segment = filter.toString();
		return setFilter(segment);
	}

	public NamedSQL setOrderBy(String orderby) {
		if (Util.isEmpty(orderby)) {
			setParam(Param_OrderBy, orderby);
			return this;
		}
		
		String lower = orderby.toLowerCase();
		if (lower.indexOf("order by") < 0) {
			orderby = " order by " + orderby;
		}
		
		return setParam(Param_OrderBy, orderby);	
	}
	
	public NamedSQL setOrderBy(OrderBy orderBy) {
		if (orderBy == null) {
			return this;
		}
		
		return setParam(Param_OrderBy, orderBy.getValue());	
	}

	public NamedSQL setPage(Page page) {
		//1. 如果 page为空，清空分页
		if (page == null) {
			setParam(Param_Page_RowNo, "");
			setParam(Param_Page_limit, "");
			return this;
		}
		
		//2. 如果 page有值，设置分页
		if (dbaseType.isMySQL()) {
			setParam(Param_Page_RowNo, "");
			setParam(Param_Page_limit, page.toSQLString());
		}
		else if (dbaseType.isSQLServer()) {
			setParam(Param_Page_RowNo, "");
			setParam(Param_Page_limit, page.toSQLString());
		}
		else if (dbaseType.isOracle()) {
			setParam(Param_Page_RowNo, page.toSQLString());
			setParam(Param_Page_limit, "");
		}
		
		return this;
	}
	
	public void clearPage() {
		setParam(Param_Page_RowNo, "");
		setParam(Param_Page_limit, "");
	}
		
	public NamedSQL setFieldNameValues(Entity entity) throws Exception {
		ContentBuilder result = new ContentBuilder();
		
		FieldsRuntime tableMeta = entity.getFieldsMeta();
		int cnt = tableMeta.getFieldCount();
		
		for (int i = 0; i < cnt; i++) {
			if (entity.isAbsentValue(i)) {
				continue;
			}
			
			Field field = tableMeta.get(i);
			result.append(field.getName() + "=" + entity.getSQLString(dbaseType, i), ", ");
		}

		return setParam(Param_FieldNameValues, result.toString());
	}
	
	public NamedSQL setFieldNameValues(DataEvent event, FieldsRuntime tableMeta, Entity entity) throws Exception {
		ContentBuilder result = new ContentBuilder();
		
		String[] names = tableMeta.getFieldNames();
		int cnt = names.length;
		
		for (int i = 0; i < cnt; i++) {
			String name = names[i];
			
			if (!tableMeta.contains(name)) {
				continue;
			}
			
			if ((DataEvent.Update == event) && "id".equalsIgnoreCase(name)) {
				continue;
			}
			
			if (entity.isAbsentValue(name)) {
				continue;
			}
			
			result.append(name + "=" + entity.getSQLString(dbaseType, name), ", ");
		}
		
		return setParam(Param_FieldNameValues, result.toString());		
	}
	
	public NamedSQL setFieldNameValuesIncludeId(DataEvent event, FieldsRuntime tableMeta, Entity entity) throws Exception {
		ContentBuilder result = new ContentBuilder();
		
		String[] names = tableMeta.getFieldNames();
		int cnt = names.length;
		
		for (int i = 0; i < cnt; i++) {
			String name = names[i];
			
			if (!tableMeta.contains(name)) {
				continue;
			}
			
			if (entity.isAbsentValue(name)) {
				continue;
			}
			
			result.append(name + "=" + entity.getSQLString(dbaseType, name), ", ");
		}
		
		return setParam(Param_FieldNameValues, result.toString());		
	}

	public String getParam(String name) {
		VariantSegment variant = expression.getVariant(name);
		return variant.getValue();
	}
	
	public NamedSQL setParam(String name, String value) {	
		VariantSegment variant = expression.getVariant(name);
		
		if (variant != null) {
			variant.setValue(value);
		}
		
		return this;
	}
	
	public NamedSQL setParam(String name, String value, String defaultValue) {
		if (value == null) {
			value = defaultValue;
		}
		
		VariantSegment sqllVariant = expression.getVariant(name);
		
		if (sqllVariant != null) {
			sqllVariant.setValue(value);
		}
		
		return this;
	}
	
	public NamedSQL setParam(String name, int value) {
		String StringValue = String.valueOf(value);
		return setParam(name, StringValue);
	}
	
	public NamedSQL setParam(String name, BigDecimal value) {
		String StringValue = value.toString();
		return setParam(name, StringValue);		
	}
	
	public NamedSQL setParam(String name, Date date) throws Exception {
		DBDate dbDate = new DBDate(date);
		String value = dbDate.toSqlString(dbaseType);
		return setParam(name, value);
	}
	
	public NamedSQL setParam(String name, Date date, boolean includeTime) throws Exception {
		DBDate dbDate = new DBDate(date, includeTime);
		String value = dbDate.toSqlString(dbaseType);
		return setParam(name, value);
	}
	
	public NamedSQL setParam(String name, boolean value) {
		String StringValue = Util.booleanToStr(value);
		return setParam(name, StringValue);
	}

	public NamedSQL setJoins(DataJoins dataJoins) {
		//1. 替换逗号分隔符变量
		String commaSeparator = dataJoins.getCommaSeparator();
		setParam(Param_CommaSeparator, commaSeparator);
		
		//2. 替换 join select fields 变量
		String joinSelectFields = dataJoins.getJoinSelectFields();
		setParam(Param_JoinSelectFields, joinSelectFields);
		
		//3. 替换 join SQL 变量
		String joinSQL = dataJoins.getJoinSQL();
		setParam(Param_JoinSQL, joinSQL);
		
		//4. return
		return this;
	}
	
	public String getName() {
		return name;
	}

	@Override
	public Iterator<VariantSegment> iterator() {
		return expression.iterator();
	}

	public void clearVariantValues() {
		expression.clearVariantValues();
	}

	public VariantList getVariantList() { 
		return expression.getVariantList();
	}
	
	public void setVariants(IVariantsProvider... providers) throws Exception {
		VariantLink.overWriteOnConsumer(null, this, providers);
	}
	
	@Override
	public void setVariants(DataEvent dataEvent, IVariantsProvider... providers) throws Exception {
		VariantLink.overWriteOnConsumer(dataEvent, this, providers);
	}
	
	public void fillVariants(IVariantsProvider... providers) throws Exception {
		VariantLink.fillOnConsumer(null, this, providers); 
	}
	
	public void overWriteVariants(IVariantsProvider... providers) throws Exception {
		VariantLink.overWriteOnConsumer(null, this, providers);
	}
	
	private void setDefaultParams() {
		//1. 日期函数
		if (expression.containsVariant("dateAddFunction")) {
			String dateAddFunction = DBDate.getDayAddFunciton(dbaseType);
			setParam("dateAddFunction", dateAddFunction);
		}
		
		if (expression.containsVariant("currentTime")) {
			String dateAddFunction = DBDate.getCurrentTimeFunciton(dbaseType);
			setParam("currentTime", dateAddFunction);
		}
		
		if (expression.containsVariant("currentDate")) {
			String dateAddFunction = DBDate.getCurrentDateFunciton(dbaseType);
			setParam("currentDate", dateAddFunction);
		}
	}

	public void setSql(String sql) throws Exception {
		this.sql = sql;
		expression = new SQLCreator(sql);
	}
	
	@Override
	public Set<String> getVariantNames() {
		return expression.getVariantNames();
	}
	
	@Override
	public boolean containsVariant(String name) {
		return expression.containsVariant(name);
	}

	@Override
	public void setVariant(DataEvent dataEvent, String name, Object value) throws Exception {
		expression.setVariant(dataEvent, name, value);
	}

	@Override
	public boolean isVariantNull(String name) {
		return expression.isVariantNull(name);
	}

	public Entity getEntity() throws Exception {
		 return SQLRunner.getEntity(this);
	}

	public EntitySet getEntitySet() throws Exception {
		return SQLRunner.getEntitySet(this);
	}

	public int execute() throws Exception {
		return SQLRunner.execSQL(this);
	}

	public static int execute(String name, ISQLContext sqlContext) throws Exception {
		NamedSQL namedSQL = NamedSQL.getInstance(name);
		sqlContext.setParametersTo(namedSQL);
		return SQLRunner.execSQL(namedSQL);
	}

	public String getOriginalSql(){
		return sql;
	}

	public String toString() {
		//1. 设置默认参数
		setDefaultParams();
		
		//2.
		return expression.toString(); 
	}
}