package com.vci.server.omd.ddlTool;

import java.io.File;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;

import org.hibernate.Session;

import com.vci.corba.common.VCIError;
import com.vci.corba.omd.atm.AttribItem;
import com.vci.server.base.persistence.dao.HibernateSessionFactory;
import com.vci.server.base.utility.AttributeHelper;

public final class DDLHelper {
	
	/**
	 * 业务类型的系统属性
	 */
	private static final String otherFieldBt = "\n\tOID VARCHAR2(36) not null," +
			"\n\tREVISIONOID VARCHAR2(36),\n\tNAMEOID VARCHAR2(36),\n\tBtmName VARCHAR2(36)," +
			"\n\tISLastR CHAR(1),\n\tISFirstR CHAR(1),\n\tISLastV CHAR(1),\n\tISFirstV CHAR(1)," +
			"\n\tCreator VARCHAR2(36)," + 
			"\n\tCreateTime TIMESTAMP,\n\tLastModifier VARCHAR2(36),\n\tLastModifyTime TIMESTAMP," + 
			"\n\tRevisionRule VARCHAR2(36),\n\tVersionRule VARCHAR2(36),\n\tRevisionSeq NUMBER," + 
			"\n\tRevisionValue VARCHAR2(10),\n\tVersionSeq NUMBER,\n\tVersionValue VARCHAR2(10)," + 
			"\n\tLCTID VARCHAR2(36),\n\tLCStatus VARCHAR2(36),\n\tTS TIMESTAMP," +
			"\n\tID VARCHAR2(36),\n\tNAME VARCHAR2(128),\n\tDESCRIPTION VARCHAR2(255)," +
			"\n\tOWNER VARCHAR2(36),\n\tCHECKINBY VARCHAR2(36),\n\tCHECKINTIME TIMESTAMP," +
			"\n\tCHECKOUTBY VARCHAR2(36),\n\tCHECKOUTTIME TIMESTAMP,\n\tCOPYFROMVERSION VARCHAR2(36),\n\t";
	

	private static final String otherFieldLt = "\n\tOID VARCHAR2(36) not null," + 
			"\n\tCreator VARCHAR2(36),\n\tCreateTime TIMESTAMP,\n\tLastModifier VARCHAR2(36)," +
			"\n\tLastModifyTime TIMESTAMP,\n\tF_OID VARCHAR2(36) not null,\n\tF_REVISIONOID VARCHAR2(36)," + 
			"\n\tF_NAMEOID VARCHAR2(36),\n\tF_BtwName VARCHAR2(36),\n\tT_OID VARCHAR2(36) not null,\n\tT_REVISIONOID VARCHAR2(36)," + 
			"\n\tT_NAMEOID VARCHAR2(36),\n\tT_BtwName VARCHAR2(36),\n\tTS TIMESTAMP,\n\t";


	
	public static boolean executeSql(String sql) throws Throwable{
		Session session = HibernateSessionFactory.getSession();
		try{
			session.createSQLQuery(sql).executeUpdate();
		}catch(Throwable e){
			throw e;
		}
		return true;
	}

	/**
	 * 清空表中数据 truncate table platformlt_ebom
	 */

	public static boolean truncateTable(String tableName) throws VCIError {
		Session session = HibernateSessionFactory.getSession();
		session.createSQLQuery("truncate table " + tableName).executeUpdate();
		return true;
	}

	/**
	 * 清空表中数据 当表主键或唯一键被引用为外键时, 就算引用方没有数据, truncate还不是不能删除表中数据 而 delete可以 delete
	 * table platformlt_ebom
	 */

	public static boolean deleteTable(String tableName) throws VCIError {
		Session session = HibernateSessionFactory.getSession();
		session.createSQLQuery("delete from " + tableName).executeUpdate();
		return true;
	}
	
	public static boolean dropTable(String tableName) throws VCIError {
		if (!existsTable(tableName))
			return true;
		
		Session session = HibernateSessionFactory.getSession();
		session.createSQLQuery("drop table " + tableName).executeUpdate();
		return true;
	}	
	
	public static boolean existsTable(String tableName)  {
		int count = 0;
		try {
			Connection connection = HibernateSessionFactory.getSessionConnection();
			String sql = "SELECT COUNT(1) FROM User_Tables WHERE table_name = ?";
			PreparedStatement pst = connection.prepareStatement(sql);
			pst.setString(1, tableName);
			ResultSet rs = pst.executeQuery();
			
			if (rs.next()) {
				count = rs.getInt(0);
			}
		} catch (Exception e) {
			return false;
		}
		
		return count != 0;
	}

	/**
	 * 
	 * @param name
	 * @return
	 */
	public static boolean hasInstance(String name){
		boolean flag = false;
		Session session = HibernateSessionFactory.getSession();
		//判断表是否存在, 表不存在则该表无数据
		String sql_ = "select count(1) from user_tables where TABLE_NAME = '" + name.toUpperCase() +"'";
		List<Object> list_ = session.createSQLQuery(sql_).list();
		// 当list.get(i)中Object数量为1时, list.get(i)为Object
		// 当list.get(i)中Object数量 > 1时, list.get(i)为Object
		Object obj_ = list_.get(0);
		int count_ = ((BigDecimal) obj_).intValue();
		if (count_ < 1) {
			return false;
		}
		String sql = "select count(*) from " + name;
		List<Object> list = session.createSQLQuery(sql).list();
		//当list.get(i)中Object数量为1时, list.get(i)为Object
		//当list.get(i)中Object数量 > 1时, list.get(i)为Object
		Object obj = list.get(0);
		int count = ((BigDecimal)obj).intValue();
		if(count > 0){
			flag = true;
		}
		return flag;
	}
	
	
	/**
	 * 获取属性字段的sql语句
	 * @param array
	 * @return
	 */
	public static 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 = AttributeHelper.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 = AttributeHelper.getOtherValueByType(other, "length");
			if(lengthStr != null && !lengthStr.equals("")){
				length = Integer.valueOf(lengthStr);
			}
			
			int accuracy = 2;
			String accuracyStr = AttributeHelper.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;
	}

	public static String getBTSysFields() {
		return otherFieldBt;
	}

	public static String getLTSysFields() {
		return otherFieldLt;
	}


	/**
	 * 获取属性other中type的值
	 * @param other
	 * @param type
	 * @return
	 */
//	static 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;
//		
//	}
}