package com.vci.server.omd.ddlTool;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.Writer;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.hibernate.Session;
import com.vci.server.base.persistence.dao.HibernateSessionFactory;

/**
 * 
 * 业务类型系统级属性：
1)	对象唯一性标记：OID VTString 36 必填
2)	版本对象唯一性标记：REVISIONOID VTString 36
3)	名称对象唯一性标记：NAMEOID VTString 36
	业务类型名：btwName VTString 36
	最新版本：ISLastR VTINTEGER            //标记是否为同一个名称对象中最新版本
	第一个版本：ISFirstR VTINTEGER        //标记是否为同一个名称对象中的第一个版本对象
	最新版次：ISLastV VTINTEGER            //标记是否为同一个版本的最新版次
	第一个版次：ISFirstV VTINTEGER        //标记是否为同一个版本的第一个版次


4)	创建者：Creator VTString 36 ref User
5)	创建时间：CreateTime VTDateTime
6)	最后修改者：LastModifier VTString 36 ref User
7)	最后修改时间：LastModifyTime VTDateTime
8)  时间戳：TIMESTAMP VTLong

  ID varchar2(36),
  NAME varchar2(128),
  DESCRIPTION varchar2(255),
  owner varchar2(36),
  checkInBy varchar2(36),
  checkInTime timestamp,
  checkoutBy varchar2(36),
  checkoutTime timestamp,
  copyFromVersion varchar2(36),

业务类型中版本相关属性：
1)	版本号规则：RevisionRule     VTString 36      ref RevisionRule
2)	版次号规则：VersionRule       VTString 36
3)	版本号序号：RevisionSeq       VTINTEGER
4)	版本号值：RevisionValue        VTString 10
5)	版次号序号：VersionSeq        VTINTEGER
6)	版次号值：VersionValue         VTString 10


业务类型中生命周期相关属性：
1）生命周期模板：LCTID     VTString ref LCT
2）生命周期状态：LCStatus         VTString ref LCS


链接类型系统级属性：
1）	对象唯一性标记：OID VTString 必填
2）	创建者：Creator VTString 36 ref User
3）	创建时间：CreateTime VTDateTime
4）	最后修改者：LastModifier VTString 36 ref User
5）	最后修改时间：LastModifyTime VTDateTime
6）	From对象唯一性标记：OID VTString 36 必填
7）	From版本对象唯一性标记：REVISIONOID VTString 36
8）	From名称对象唯一性标记：NAMEOID VTString 36
	From业务类型名：btwName VTString 36
9）	To对象唯一性标记：OID VTString 36 必填
10）	To版本对象唯一性标记：REVISIONOID VTString 36
11）	To名称对象唯一性标记：NAMEOID VTString 36
	To业务类型名：btwName VTString 36
12) 时间戳：TIMESTAMP VTLong

 * @author Administrator
 *
 */
public class DDLForOracle{
	private String btDDLCreatePath = "";
	private String btDDLDropPath = "";
	private String btDDLAlterPath = "";
	private String linkTypeDDLCreatePath = "";
	private String linkTypeDDLDropPath = "";
	private static final String BTM = "btm";
	private static final String ATTRIBITEM = "attribItem";
	private static final String LINKTYPES = "linkTypes";
	private static final String LINKTYPE = "linkType";
	private static final String ATTRIBUTE = "attibute";
	private static final String ROW = "row";
	private final String otherFieldBt = "\n\tOID VARCHAR2(36) not null," +
			"\n\tREVISIONOID VARCHAR2(36),\n\tNAMEOID VARCHAR2(36),\n\tBtwName 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 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";
	private final String NEWLINE = "\n";
	private static DDLForOracle gOracle = null;
	private static final String PLATFORMBTM_ = "PLATFORMBTM_";
	
	private DDLForOracle(){
		
	}
	public static DDLForOracle getInstance(){
		if(gOracle == null){
			gOracle = new DDLForOracle();
		}
		return gOracle;
	}
	/**
	 * 为业务类型生成DDL
	 * @param apContent
	 * @param btContent
	 * @return
	 */
	public boolean generatorODDLForBt(String apContent, String btContent) {
		btDDLCreatePath = DataFileConfig.getProperty("btDDLCreatePathOracle");
		btDDLDropPath = DataFileConfig.getProperty("btDDLDropPathOracle");
		// 业务类型DDL
		Document btDocument = null;
		try {
			btDocument = DocumentHelper.parseText(btContent);
		} catch (DocumentException e) {
			e.printStackTrace();
		}
		if (btDocument == null) {
			return false;
		}
		boolean flag = generateDDLForBt(btDocument, apContent);
		
		return flag;
	}
	
	/**
	 * 为链接类型生成DDL
	 * @param apContent
	 * @param ltContent
	 * @return
	 */
	public boolean generatorODDLForLt(String apContent, String ltContent) {
		linkTypeDDLCreatePath = DataFileConfig.getProperty("linkTypeDDLCreatePathOracle");
		linkTypeDDLDropPath = DataFileConfig.getProperty("linkTypeDDLDropPathOracle");
		// 业务类型DDL
		Document ltDocument = null;
		try {
			ltDocument = DocumentHelper.parseText(ltContent);
		} catch (DocumentException e) {
			e.printStackTrace();
		}
		if (ltDocument == null) {
			return false;
		}
		boolean flag = generateDDLForLt(ltDocument, apContent);
		
		return flag;
	}
	
	/**
	 * 业务类型DDL
	 * @param document
	 * @return
	 */
	private boolean generateDDLForBt(Document document, String apContent){
		Element rootNode = document.getRootElement();
		Element tableNode = rootNode.element(BTM);
		List<Element> rowNodes = tableNode.elements(ROW);
		ArrayList<String> sqlCreateList = new ArrayList<String>();
		ArrayList<String> sqlDropList = new ArrayList<String>();
		
		Document apDocument = null;
		try {
			apDocument = DocumentHelper.parseText(apContent);
		} catch (DocumentException e) {
			e.printStackTrace();
		}
		if(apDocument == null){
			return false;
		}
		Element apRootNode = apDocument.getRootElement();
		Element apTableNode = apRootNode.element(ATTRIBITEM);
		List<Element> apRowNodes = apTableNode.elements(ROW);
		
		for(Iterator<Element> i = rowNodes.iterator(); i.hasNext();){
			Element rowNode = i.next();
			String tableName = PLATFORMBTM_ + rowNode.element("name").getText().toUpperCase();
			String[] apNames = getBtwApNameArray(document, rowNode.element("name").getText());
			String sqlCreate = "create Table " + tableName + "(" + otherFieldBt;
			String sqlDrop = "drop Table " + tableName + ";\n";
			for(int k = 0; k < apNames.length; k++){
				sqlCreate += getAPSql(apRowNodes, apNames[k]);
			}
			sqlCreate = sqlCreate.substring(0, sqlCreate.lastIndexOf(","));
			sqlCreate += "\n);\n";
			sqlCreate += "alter table " + tableName + " add constraint PK_" + tableName + " primary key (OID);\n";
			
			sqlCreateList.add(sqlCreate);
			sqlDropList.add(sqlDrop);
		}
		writeFile(btDDLCreatePath, sqlCreateList);
		writeFile(btDDLDropPath, sqlDropList);
		return true;
	}
	
	/**
	 * 链接类型DDL
	 * @param document
	 * @return
	 */
	private boolean generateDDLForLt(Document document, String apContent){
		Element rootNode = document.getRootElement();
		Element dataNode = rootNode.element(LINKTYPES);
		List<Element> rowNodes = dataNode.elements(LINKTYPE);
		ArrayList<String> sqlCreateList = new ArrayList<String>();
		ArrayList<String> sqlDropList = new ArrayList<String>();
		
		Document apDocument = null;
		try {
			apDocument = DocumentHelper.parseText(apContent);
		} catch (DocumentException e) {
			e.printStackTrace();
		}
		if(apDocument == null){
			return false;
		}
		Element apRootNode = apDocument.getRootElement();
		Element apTableNode = apRootNode.element(ATTRIBITEM);
		List<Element> apRowNodes = apTableNode.elements(ROW);
		
		for(Iterator<Element> i = rowNodes.iterator(); i.hasNext();){
			Element rowNode = i.next();
			String tableName = rowNode.elementText("name").toUpperCase();
			String sqlCreate = "create Table " + tableName + "(" + otherFieldLt;
			String sqlDrop = "drop Table " + tableName + ";\n";
			
			List<Element> apNodes_ = rowNode.elements(ATTRIBUTE);
			for(Iterator<Element> k = apNodes_.iterator(); k.hasNext();){
				Element apNode_ = k.next();
				String apName = apNode_.elementText("name");
				sqlCreate += getAPSql(apRowNodes, apName);
			}
			
			sqlCreate = sqlCreate.substring(0, sqlCreate.lastIndexOf(","));
			sqlCreate += "\n);\n";
			sqlCreate += "alter table " + tableName + " add constraint PK_" + tableName + " primary key (OID);\n";
			
			sqlCreateList.add(sqlCreate);
			sqlDropList.add(sqlDrop);
		}
		
		writeFile(linkTypeDDLCreatePath, sqlCreateList);
		writeFile(linkTypeDDLDropPath, sqlDropList);
		return true;
	}
	
	/**
	 * 将ddl的内容(contentList)写进sql文件里
	 * @param ddlPath
	 * @param contentList
	 */
	private void writeFile(String ddlPath, ArrayList<String> contentList){
		File file = new File(ddlPath);
		File pFile = file.getParentFile();
		if(!pFile.exists()){
			pFile.mkdirs();
		}
		if(!file.exists()){
			try {
				file.createNewFile();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		try {
			
			
			FileWriter fW = new FileWriter(file);
			for(int i = 0; i < contentList.size(); i++){
				fW.write(contentList.get(i));
			}
			fW.flush();
			fW.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
		
	}
	
	/**
	 * 根据属性名获取该属性字段的DDL
	 * @param apRowNodes
	 * @param apName
	 * @return
	 */
	private String getAPSql(List<Element> apRowNodes, String apName){
		Element apRowNode = null;
		String sqlAp = "";
		for(Iterator<Element> i = apRowNodes.iterator(); i.hasNext();){
			Element apRowNode_ = i.next();
			if(apRowNode_.element("name").getText().equals(apName)){
				apRowNode = apRowNode_;
				break;
			}
		}
		
		if(apRowNode == null){
			return sqlAp;
		}
		
		String vtType = apRowNode.elementText("vtDataType");
		String other = apRowNode.elementText("other");
		String defValue = apRowNode.elementText("defValue");
		
		if(vtType.equals("VTString")){
			int length = 50;
			String lengthStr = getOtherValueByType(other, "length");
			if(lengthStr != null && !lengthStr.equals("")){
				length = Integer.valueOf(lengthStr);
			}
			sqlAp += apName.toUpperCase() + " VARCHAR2(" + length + ")";
			if(!defValue.equals("")){
				sqlAp += " default '" + defValue + "'";
			}
			sqlAp += ",\n\t";
		}else if(vtType.equals("VTInteger") || vtType.equals("VTLong")){
			sqlAp += apName.toUpperCase() + " NUMBER";
			if(!defValue.equals("")){
				sqlAp += " default " + defValue;
			}
			sqlAp += ",\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);
			}
			sqlAp += apName.toUpperCase() + " NUMBER(" + length + ", " + accuracy +")";
			if(!defValue.equals("")){
				sqlAp += " default " + defValue;
			}
			sqlAp += ",\n\t";
		}else if(vtType.equals("VTBoolean")){
			sqlAp += apName.toUpperCase() + " VARCHAR2(8)";
			if(!defValue.equals("")){
				sqlAp += " default '" + defValue + "'";
			}
			sqlAp += ",\n\t";
		}else if(vtType.equals("VTImage")){
			sqlAp += apName.toUpperCase() + " VARCHAR2(255)";
			sqlAp += ",\n\t";
		}else if(vtType.equals("VTDate")){
			sqlAp += apName.toUpperCase() + " TIMESTAMP";
			sqlAp += ",\n\t";
		}else if(vtType.equals("VTTime")){
			sqlAp += apName.toUpperCase() + " TIMESTAMP";
			sqlAp += ",\n\t";
		}else if(vtType.equals("VTDateTime")){
			sqlAp += apName.toUpperCase() + " TIMESTAMP";
			sqlAp += ",\n\t";
		}else if(vtType.equals("VTNote")){
			sqlAp += apName.toUpperCase() + " VARCHAR2(255)";
			sqlAp += ",\n\t";
		}else if(vtType.equals("VTFilePath")){
			sqlAp += apName.toUpperCase() + " VARCHAR2(255)";
			sqlAp += ",\n\t";
		}
		
		return sqlAp;
	}
	
	/**
	 * 获取属性other中type的值
	 * @param other
	 * @param type
	 * @return
	 */
	public 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;
		
	}
	
	/**
	 * 获取业务类型的属性名数组
	 */
	public String[] getBtwApNameArray(Document document, String btwName){
		ArrayList<String> nameList = new ArrayList<String>();
		
		Element rootNode = document.getRootElement();
		Element tableNode = rootNode.element(BTM);
		List<Element> rowNodes = tableNode.elements(ROW);
		while(!btwName.equals("")){
			for(Iterator<Element> i = rowNodes.iterator(); i.hasNext();){
				Element rowNode = i.next();
				if(rowNode.elementText("name").equals(btwName)){
					
					String[] apNames = rowNode.elementText("apName").split(",");
					for(int k = 0; k < apNames.length; k++){
						nameList.add(apNames[k]);
					}
					btwName = rowNode.elementText("fName");
					break;
				}
			}
		}	
			
		return nameList.toArray(new String[0]);
	}
	
	/**
	 * 创建业务类型table
	 * @param sql
	 * @return
	 */
	public 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;
	}
	
	/**
	 * 记录修改Table DDL(alter table t add(column type)
	 * Oracle
	 */
	public boolean alterTableDDL(String sql){
		btDDLAlterPath = DataFileConfig.getProperty("btDDLAlterPathOracle");
		File file = new File(btDDLAlterPath);
		File pFile = file.getParentFile();
		if(!pFile.exists()){
			pFile.mkdirs();
		}
		if(!file.exists()){
			try {
				file.createNewFile();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		try {
			Writer writer = new FileWriter(file);
			writer.write(sql);
			writer.write(NEWLINE);
			writer.flush();
			writer.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return true;
	}
	
	/**
	 * 
	 * @param name
	 * @return
	 */
	public 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;
	}
}