package com.vci.server.omd.typeindex.service;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import org.dom4j.DocumentException;
import com.vci.common.log.ServerWithLog4j;
import com.vci.common.utility.ObjectUtility;
import com.vci.corba.omd.tim.TypeIndexDef;
import com.vci.server.base.persistence.dao.HibernateSessionFactory;
import com.vci.server.base.utility.OmdHelper;

public class TIService {
	private static TIService instance;
	
	private TIService() {
		
	}
	
	public static TIService getInstance() {
		if (instance == null) {
			instance = new TIService();
		}
		return instance;
	}
	
	public void addTypeIndex(TypeIndexDef tid) throws Exception {
		String insertSql = "insert into pltypeindex (oid, name, typename, attributes, description, creator, createtime, modifier, modifytime) values(?,?,?,?,?,?,?,?,?)";
		Connection connection = HibernateSessionFactory.getSessionConnection();
		PreparedStatement pst = connection.prepareStatement(insertSql);
		pst.setString(1, ObjectUtility.getNewObjectID36());
		pst.setString(2, tid.name);
		pst.setString(3, tid.typeName);
		pst.setString(4, tid.attributes);
		pst.setString(5, tid.description);
		long time = Calendar.getInstance().getTimeInMillis();
		Timestamp ts = new Timestamp(time);
		pst.setString(6, tid.creator);
		pst.setTimestamp(7, ts);
		pst.setString(8, tid.modifier);
		pst.setTimestamp(9, ts);

		pst.execute();
		pst.close();
		
		String indexName = OmdHelper.getIndexName(tid.typeName, tid.name);
		String tableName = OmdHelper.getBTTableName(tid.typeName);
		String sql = String.format("CREATE INDEX %s ON %s (%s)", indexName, tableName, tid.attributes);
		pst = connection.prepareStatement(sql);
		pst.execute();
		pst.close();
		
		ServerWithLog4j.logger.debug(insertSql);
	}
	
	
	public void modifyTypeIndex(TypeIndexDef tid) throws Exception {
		String updateSql = "update pltypeindex t set t.name=?, t.typename=?, t.attributes=?, t.description=?, t.modifier=?, t.modifytime=? where t.oid=?";
		ServerWithLog4j.logger.debug(updateSql);
		
		Connection connection = HibernateSessionFactory.getSessionConnection();
		PreparedStatement pst = connection.prepareStatement(updateSql);
		pst.setString(1, tid.name);
		pst.setString(2, tid.typeName);
		pst.setString(3, tid.attributes);
		pst.setString(4, tid.description);
		long time = Calendar.getInstance().getTimeInMillis();
		Timestamp ts = new Timestamp(time);
		pst.setString(5, tid.modifier);
		pst.setTimestamp(6, ts);
		pst.setString(7, tid.oid);
		pst.executeUpdate();
		pst.close();
		
		// 删除已经存在的索引
		String indexName = OmdHelper.getIndexName(tid.typeName, tid.name);
		String sql = "drop index " + indexName;
		pst = connection.prepareStatement(sql);
		pst.execute();
		pst.close();
		
		// 重建索引
		String tableName = OmdHelper.getBTTableName(tid.typeName);
		sql = String.format("CREATE INDEX %s ON %s (%s)", indexName, tableName, tid.attributes);
		pst = connection.prepareStatement(sql);
		pst.execute();
		pst.close();
	}
	
	public boolean deleteTypeIndex(String oid) throws Exception {
		boolean flag = false;
		Connection connection = HibernateSessionFactory.getSessionConnection();
		
		String sql = "select name, typeName from pltypeindex where oid=?";
		PreparedStatement pst = connection.prepareStatement(sql);
		pst.setString(1, oid);
		
		String typeName, name;
		ResultSet rs = pst.executeQuery();
		if (rs.next()) {
			name = rs.getString("name");
			typeName = rs.getString("typeName");
			rs.close();
		} else {
			rs.close();
			return false;
		}
		pst.close();
		
		sql = "delete from pltypeindex where oid = ?";
		pst = connection.prepareStatement(sql);
		pst.setString(1, oid);
		pst.execute();
		pst.close();
		
		ServerWithLog4j.logger.debug(sql);
		
		String indexName = OmdHelper.getIndexName(typeName, name);
		sql = "drop index " + indexName;
		
		pst = connection.prepareStatement(sql);
		pst.execute();
		pst.close();
		
		ServerWithLog4j.logger.debug(sql);

		flag = true;
		return flag;
	}
	
	public TypeIndexDef[] getTypeIndexs(String typeName) throws SQLException, IOException, DocumentException {
		String sql = "select oid, name, typename, attributes, description, creator, createtime, modifier, modifytime from pltypeindex t where t.typename=?";
		
		Connection connection = HibernateSessionFactory.getSessionConnection();
		PreparedStatement pst = connection.prepareStatement(sql);
		pst.setString(1, typeName);
		ResultSet rs = pst.executeQuery();
		ServerWithLog4j.logger.debug(sql);
		List<TypeIndexDef> tids = new ArrayList<TypeIndexDef>();
		while(rs.next()){
			TypeIndexDef tid = getTypeIndexDef(rs);
			tids.add(tid);
		}
		rs.close();
		pst.close();
		return tids.toArray(new TypeIndexDef[0]);
	}
	
	/**
	 * 将一条数据库中的记录转化成btmitem
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	public TypeIndexDef getTypeIndexDef(ResultSet rs) throws SQLException {
		TypeIndexDef tid = new TypeIndexDef();
		String value = rs.getString("oid");
		tid.oid = (value == null ? "" : value);
		value = rs.getString("name");
		tid.name = (value == null ? "" : value);
		value = rs.getString("typename");
		tid.typeName = (value == null ? "" : value);
		value = rs.getString("attributes");
		tid.attributes = (value == null ? "" : value);
		value = rs.getString("description");
		tid.description = (value == null ? "" : value);
		value = rs.getString("creator");
		tid.creator = (value == null ? "" : value);
		tid.createTime = rs.getTimestamp("createTime").getTime();
		value = rs.getString("modifier");
		tid.modifier = (value == null ? "" : value);
		tid.modifyTime = rs.getTimestamp("modifyTime").getTime();

		return tid;
	}

}