package frame.persist;

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

import frame.data.DataType;
import frame.data.Entity;
import frame.data.IDataConsumer;
import frame.data.Page;
import frame.data.meta.EntityMeta;
import frame.data.meta.Field;
import frame.expression.Expression;
import frame.expression.IExpression;
import frame.expression.VariantList;
import frame.expression.VariantSegment;
import frame.object.data.Filter;
import frame.object.data.OrderBy;
import frame.util.ContentBuilder;
import frame.util.Util;


public class NamedSQL implements Iterable<VariantSegment>, IExpression, IDataConsumer {

	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_Values = "fieldValues";	
	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";
	
	private static NamedSQLContainer namedSQLContainer;
	
	protected String name;
	protected String sql;
	protected DataType returnType;
	protected Expression expression;
	
	static {
		namedSQLContainer = NamedSQLContainer.getInstance();
	}

	private NamedSQL(String name) {
		this.name = name;
		returnType = DataType.Void;
	}
	
	public NamedSQL(String name, String sql) throws Exception {
		this.name = name;
		this.sql = sql;
		
		parseSQL(sql);
	}
	
	protected void parseSQL(String sql) throws Exception {
		String lower = sql.toLowerCase();
		
		if (lower.indexOf("row_number()") > 0) {
			sql = "select * from (" + sql + ")table_t where rownum > @{beginNo} and rownum <= @{endNo}";
		}
		
		expression = new SQLCreator(sql);
	}

	public static NamedSQL[] getInstance(String[] names) throws Exception {
		if (names == null) { return new NamedSQL[0]; };
		
		NamedSQL[] result = new NamedSQL[names.length];
		
		for (int i = 0; i < names.length; i++) {
			result[i] = getInstance(names[i]);
		}
		
		return result;
	}
	
	public static NamedSQL getInstance(String name) throws Exception {
		String condtion = SystemCondition.getFilterValue();
		
		NamedSQL result = namedSQLContainer.get(name, condtion);
		
		if (result == null) {
			result = namedSQLContainer.get(name, null);
		}
		
		if (result == null) {
			throw new Exception("can not find named sql: " + name);
		}
		
		result = result.newInstance();
		return result;
	}
	
	public NamedSQL newInstance() throws Exception {
		NamedSQL instance = new NamedSQL(this.name);
		instance.sql = this.sql;
		instance.expression = this.expression.newInstance();
		
		return instance;
	}

	public String getSQL() throws Exception {
		String result = expression.getString();
		return result;
	}
	
	public String getOriginalSql(){
		return sql;
	}

	public Result exec() throws Exception {
		Result result = SQLRunner.getResult(this);
		return result;
	}
	
	public NamedSQL setSchema(String schema) {
		return setParam(Param_Schema, schema);
	}

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

	public NamedSQL setFieldNames(String names) {
		return setParam(Param_FieldNames, names);
	}
	
	public NamedSQL setFieldNames(EntityMeta tableMeta) {
		StringBuilder result = new StringBuilder();
		boolean empty = true;
		
		for (Field field: tableMeta) {
			if (!empty) {
				result.append(", ");
			}
			
			result.append(field.getName());
			
			empty = false;
		}
		
		return setParam(Param_FieldNames, result.toString());
	}
	
	public NamedSQL setFieldNames(EntityMeta tableMeta, Entity entity) {
		StringBuilder result = new StringBuilder();
		boolean empty = true;
		Field field;
		
		for (int i = 0; i < tableMeta.getFieldCount(); i++) {
			if (entity.isEmptyValue(i)) {
				continue;
			}
				
			field = tableMeta.get(i);
			
			if (!empty) {
				result.append(", ");
			}

			result.append(field.getName());
			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 setValues(Entity entity) throws Exception {
		ContentBuilder result = new ContentBuilder();
		int cnt = entity.getFieldCount();
		
		for (int i = 0; i < cnt; i++) {
			if (entity.isEmptyValue(i)) {
				continue;
			}
			
			result.append(entity.getSQLString(i), ", ");
		}
		
		return setParam(Param_Values, result.toString());
	}
	
	public NamedSQL setQuotedFieldNames(EntityMeta tableMeta) {
		ContentBuilder result = new ContentBuilder();
		
		for (Field field: tableMeta) {
			result.append(Util.doubleQuotedStr(field.getName()), ", ");
		}
		
		return setParam(Param_FieldNames, result.toString());
	}

	public NamedSQL setPlaceHolders(Collection<Field> fields) {
		ContentBuilder result = new ContentBuilder();
		int cnt = fields.size();
		
		for (int i = 0; i < cnt; i++) {
			result.append("?", ", ");
		}
		
		return setParam(Param_PlaceHolders, result.toString());
	}
	
	public NamedSQL setPlaceHolders(String placeHolders) {
		return setParam(Param_PlaceHolders, placeHolders);
	}

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

	public NamedSQL setKeyFieldName(EntityMeta tableMeta) throws Exception {
		String keyName = tableMeta.getFiledName_Key();
		return setParam(Param_KeyFieldName, keyName);		
	}

	public NamedSQL setKeyFieldName(String fieldName) throws Exception {
		return setParam(Param_KeyFieldName, fieldName);			
	}
	
	public NamedSQL setFilter(String filter) {
		if (Util.isEmptyStr(filter)) {
			filter = "1=1";
		}
		
		return setParam(Param_Filter, filter);	
	}

	public NamedSQL setOrderBy(String orderby) {
		return setParam(Param_OrderBy, orderby);	
	}

	public NamedSQL setFieldNameValues(Entity entity) throws Exception {
		ContentBuilder result = new ContentBuilder();
		
		EntityMeta tableMeta = entity.getEntityMeta();
		int cnt = tableMeta.getFieldCount();
		
		for (int i = 0; i < cnt; i++) {
			if (entity.isEmptyValue(i)) {
				continue;
			}
			
			Field field = tableMeta.get(i);
			result.append(field.getName() + "=" + entity.getSQLString(i), ", ");
		}
		
		return setParam(Param_FieldNameValues, result.toString());
	}

	public NamedSQL setParam(String name, String value) {
		if (value == null) {
			return this;
		}
		
		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) {
		String stringValue = Util.toMySQLDateStr(date);
		return setParam(name, stringValue);
	}
	
	public NamedSQL setParam(String name, boolean value) {
		String stringValue = Util.booleanToStr(value);
		return setParam(name, stringValue);
	}

	public String getName() {
		return name;
	}

	public DataType getReturnType() {
		return returnType;
	}

	public void setReturnType(DataType dataType) {
		this.returnType = dataType;
	}
	
	public String getSQLString() throws Exception {
		return expression.tryGetString(); 
	}
	
	@Override
	public String toString() {
		return expression.getString();
	}

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

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

	public VariantList getVariantList() {
		return expression.getVariantList();
	}

	public Expression getExpression() {
		return expression;
	}

	public NamedSQL setFilter(Filter filter) {
		// TODO Auto-generated method stub
		return null;
	}

	public void setOrderBy(OrderBy orderBy) {
		// TODO Auto-generated method stub
		
	}

	public NamedSQL setPage(Page page) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<String> getDataNameList() {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public boolean containsData(String name) {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public void setDataValue(String name, Object value) {
		// TODO Auto-generated method stub
		
	}

}