package com.vci.server.omd.ddlTool;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import org.hibernate.Session;

import com.vci.corba.common.VCIError;
import com.vci.corba.omd.atm.AttribItem;
import com.vci.corba.omd.btm.BtmItem;
import com.vci.server.base.persistence.dao.HibernateSessionFactory;
import com.vci.server.base.utility.OmdHelper;
import com.vci.server.cache.OMCacheProvider;
import com.vci.server.omd.biztype.delegate.BizTypeServerDelegate;

public class DDLToolDelegate {

	private static DDLToolDelegate _instance = null;
	
	public static DDLToolDelegate getInstance() {
		if (_instance == null) {
			_instance = new DDLToolDelegate();
		}
		
		return _instance;
	}
	
	private DDLToolDelegate() {}
	
	public boolean createBizTypeTable(BtmItem bt) {
		// 生成创建该业务类型表的DDL
		String createSql = getCreateBTTableSql(bt);
		String alterSql = getAddPKSql(bt.name);
		// 执行DDL
		boolean success = executeUpdate(createSql);
		if (success)
			success = executeUpdate(alterSql);
		
		return success;
	}
	
	
	public boolean updateBizTypeTable(BtmItem btmItem) {
		BtmItem[] childrenBTs = OMCacheProvider.getChildrenBizTypes(btmItem.name);
		
		BtmItem oldBt = OMCacheProvider.getBizType(btmItem.name);
		String[] oldAttrNames = oldBt.apNameArray;
		// 修改业务类型之前先操作数据库，数据库操作成功后，才去修改业务类型
		List<String> addedAbList = new ArrayList<String>(); 
		// 需要删除的字段
		List<String> removeAbList = new ArrayList<String>(); 
		getDiffAttribute(oldAttrNames, btmItem.apNameArray, addedAbList, removeAbList);
		
		if (addedAbList != null && addedAbList.size() > 0) {
			// 更新表btmName 增加属性
			String tableName = OmdHelper.getBTTableName(btmItem.name);
			String sql = "alter table " + tableName + " add(";
			for (int i = 0; i < addedAbList.size(); i++) {
				String abName = addedAbList.get(i);
				AttribItem abItem = OMCacheProvider.getAttribute(abName);
				sql += getAbSql(abItem);
			}
			sql = sql.substring(0, sql.lastIndexOf(","));
			sql += ")";
			List<String> sqlList = new ArrayList<String>();
			sqlList.add(sql);
			// 更新btmItem的子类型表
			for (BtmItem btChild : childrenBTs) {
				String tableName_ = OmdHelper.getBTTableName(btChild.name);
				String sql_ = sql.replace(tableName, tableName_);
				sqlList.add(sql_);
			}
			boolean flag = batchExecuteSql(sqlList.toArray(new String[0]));
			if (!flag) {
				return false;
			} 
		}
		
		//List<String> removeAbList = new ArrayList<String>();
		// 不能删除的字段
		List<String> unRemovableFields_ = new ArrayList<String>();
		
		String[] unRemovableFields = null;

		try {
			unRemovableFields = BizTypeServerDelegate.getInstance().getUnRemovableFields(
					btmItem.name, removeAbList.toArray(new String[0]));
		} catch (VCIError e1) {
			e1.printStackTrace();
		}
		
		// 没有业务对象时, 删除表中移除的属性
		if (removeAbList.size() > 0) {
			// 存在不可删除的列，删除removeAbList - 不可删除的列
			if (unRemovableFields_ != null && unRemovableFields_.size() > 0) {
				removeAbList.removeAll(unRemovableFields_);
				if (removeAbList.size() > 0) {
					String tableName = OmdHelper.getBTTableName(btmItem.name);
					String dropSql = "alter table " + tableName + " drop(";
					for (int i = 0; i < removeAbList.size(); i++) {
						dropSql += removeAbList.get(i);
						if (i < removeAbList.size() - 1) {
							dropSql += ",";
						}
					}
					dropSql += ")";
					List<String> sqlList = new ArrayList<String>();
					sqlList.add(dropSql);
					// 更新btmItem的子类型表
					for (BtmItem btChild : childrenBTs) {
						String tableName_ = OmdHelper.getBTTableName(btChild.name);
						String sql_ = dropSql.replace(tableName, tableName_);
						sqlList.add(sql_);
					}
					boolean dropFlag = batchExecuteSql(sqlList.toArray(new String[0]));
					if (!dropFlag) {
						return false;
					}
				}
				// 没有不可删除的列，删除removeAbList中所有列
			} else {
				String tableName = OmdHelper.getBTTableName(btmItem.name);
				String dropSql = "alter table " + tableName + " drop(";
				for (int i = 0; i < removeAbList.size(); i++) {
					dropSql += removeAbList.get(i);
					if (i < removeAbList.size() - 1) {
						dropSql += ",";
					}
				}
				dropSql += ")";
				List<String> sqlList = new ArrayList<String>();
				sqlList.add(dropSql);
				// 更新btmItem的子类型表
				for (BtmItem btChild : childrenBTs) {
					String tableName_ = OmdHelper.getBTTableName(btChild.name);
					String sql_ = dropSql.replace(tableName, tableName_);
					sqlList.add(sql_);
				}
				// org.hibernate.Session session = HibernateSessionFactory.getSession();
				// Transaction t = session.beginTransaction();

				boolean dropFlag = batchExecuteSql(sqlList.toArray(new String[0]));
				if (!dropFlag) {
					return false;
				}
			}
		}		
		return true;
	}
	
	/**
	 * 创建业务类型table
	 * @param sql
	 * @return
	 */
	private boolean executeUpdate(String sql){
		try {
			Session session = HibernateSessionFactory.getSession();
			/**
			 * executeUpdate returns: The Number of entities updated or deleted.
			 */
			session.createSQLQuery(sql).executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}
	
	public boolean batchExecuteSql(String[] sqls) {
		try {
			Session session = HibernateSessionFactory.getSession();
			for(String sql : sqls){
				session.createSQLQuery(sql).executeUpdate();
			}
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}
	
	/**
	 * 生成创建业务类型的DDL
	 * @param btm
	 * @return
	 */
	private String getCreateBTTableSql(String btmName){
		System.out.println("getCreateBTTableSql");
		String btmTableName = OmdHelper.getBTTableName(btmName);
		String sql = "create Table " + btmTableName + "(" + DDLHelper.getBTSysFields();
		
		AttribItem[] attrs = OMCacheProvider.getAttribItemsByBizType(btmName);
		for (AttribItem attr : attrs) {
			String abSql = getAbSql(attr);
			sql += abSql;			
		}
		
		sql = sql.substring(0, sql.lastIndexOf(","));
		sql += "\n)";
		
		return sql;
	}
	
	private String getCreateBTTableSql(BtmItem bt){
		System.out.println("getCreateBTTableSql");
		String btmTableName = OmdHelper.getBTTableName(bt.name);
		String sql = "create Table " + btmTableName + "(" + DDLHelper.getBTSysFields();
		
		AttribItem[] attrs = OMCacheProvider.getAttributes(bt.apNameArray);
		for (AttribItem attr : attrs) {
			String abSql = getAbSql(attr);
			sql += abSql;			
		}
		
		sql = sql.substring(0, sql.lastIndexOf(","));
		sql += "\n)";
		return sql;
	}
	
	/**
	 * 获取增加主键sql
	 * @param typeName
	 * @return
	 */
	private String getAddPKSql(String btName){
		return "alter table " + OmdHelper.getBTTableName(btName) + " add constraint PKBTM_" + btName + " primary key (OID)\n";
	}
	
	/**
	 * 获取属性字段的sql语句
	 * @param array
	 * @return
	 */
	private String getAbSql(AttribItem abItem){
		String sql = "";
		if(abItem == null){
			return sql;
		}
		String abName = abItem.name;
		String vtType = abItem.vtDataType;
		String other = abItem.other;
		String defValue = abItem.defValue;
		
		if(vtType.equals("VTString")){
			int length = 50;
			String lengthStr = getOtherValueByType(other, "length");
			if(lengthStr != null && !lengthStr.equals("")){
				length = Integer.valueOf(lengthStr);
			}
			sql += abName.toUpperCase() + " VARCHAR2(" + length + ")";
			if(!defValue.equals("")){
				sql += " default '" + defValue + "'";
			}
			sql += ",\n\t";
		}else if(vtType.equals("VTInteger") || vtType.equals("VTLong")){
			sql += abName.toUpperCase() + " NUMBER";
			if(!defValue.equals("")){
				sql += " default " + defValue;
			}
			sql += ",\n\t";
		}else if(vtType.equals("VTDouble")){
			int length = 20;
			String lengthStr = getOtherValueByType(other, "length");
			if(lengthStr != null && !lengthStr.equals("")){
				length = Integer.valueOf(lengthStr);
			}
			
			int accuracy = 2;
			String accuracyStr = getOtherValueByType(other, "accuracy");
			if(accuracyStr != null && !accuracyStr.equals("")){
				accuracy = Integer.valueOf(accuracyStr);
			}
			sql += abName.toUpperCase() + " NUMBER(" + length + ", " + accuracy +")";
			if(!defValue.equals("")){
				sql += " default " + defValue;
			}
			sql += ",\n\t";
		}else if(vtType.equals("VTBoolean")){
			sql += abName.toUpperCase() + " VARCHAR2(8)";
			if(!defValue.equals("")){
				sql += " default '" + defValue + "'";
			}
			sql += ",\n\t";
		}else if(vtType.equals("VTImage")){
			sql += abName.toUpperCase() + " VARCHAR2(255)";
			sql += ",\n\t";
		}else if(vtType.equals("VTDate")){
			sql += abName.toUpperCase() + " DATE";
			sql += ",\n\t";
		}else if(vtType.equals("VTTime")){
			sql += abName.toUpperCase() + " TIMESTAMP";
			sql += ",\n\t";
		}else if(vtType.equals("VTDateTime")){
			sql += abName.toUpperCase() + " TIMESTAMP";
			sql += ",\n\t";
		}else if(vtType.equals("VTNote")){
			sql += abName.toUpperCase() + " VARCHAR2(255)";
			sql += ",\n\t";
		}else if(vtType.equals("VTFilePath")){
			sql += abName.toUpperCase() + " VARCHAR2(255)";
			sql += ",\n\t";
		}else if(vtType.equals("VTClob")){
			sql += abName.toUpperCase() + " CLOB";
			sql += ",\n\t";
		}
		
		return sql;
	}
	
	/**
	 * 获取属性other中type的值
	 * @param other
	 * @param type
	 * @return
	 */
	private String getOtherValueByType(String other, String type){
		String[] otherArray = other.split(";");
		for(int i = 0; i < otherArray.length; i++){
			String otherValue = otherArray[i];
			if(otherValue.contains(type)){
				return otherValue.substring(otherValue.indexOf("=") + 2, otherValue.length());
			}
		}
		return null;
		
	}
	
	/**
	 * 获取修改业务类型时 增加的属性, 以便将这些属性增加到该业务类型表的属性列中
	 * 
	 * @param oldNames
	 * @param names
	 * @return
	 */
	private boolean getDiffAttribute(String[] oldNames, String[] names, List<String> lstAdd, List<String> lstRemove) {
		List<String> oldNameList = Arrays.asList(oldNames);
		List<String> newNameList = Arrays.asList(names);
		//List<String> addedApList = new ArrayList<String>();

		for (String newName : newNameList) {
			if (!oldNameList.contains(newName)) {
				lstAdd.add(newName);
			}
		}
		

		for (String oldName : oldNameList) {
			if (!newNameList.contains(oldName)) {
				lstRemove.add(oldName);
			}
		}
		
		return true;
	}	
}