package com.taover.db;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;



import com.taover.tools.StringUtil;

public class TableColumn {	
    
    
	protected String tableCatalog;
	protected String tableSchema;
	protected String tableName;
	protected String columnName;
	protected int dataType;
	protected String typeName;
	protected int columnSize;
	protected int bufferLength;
	protected int decimalDigits;
	protected int numPrecRadix;
	protected boolean nullable;
	protected String remarks;
	protected String columnDefaults;//LONG
	protected int sqlDataType;
	protected int sqlDatatimeSubject;
	protected int charOctetLength;
	protected int ordinalPosition;
	protected String is_nullable;
	
	//for mysql
	protected String scopeCatalog;
	protected String scopeSchema;
	protected String scopeTable;
	protected int sourceDataType;
	protected String isAutoIncrement;

	protected String seqName;
	protected boolean primaryKey;	
	protected String dic;
	
	
	public String getTableCatalog() {
		return tableCatalog;
	}
	public void setTableCatalog(String tableCatalog) {
		this.tableCatalog = tableCatalog;
	}
	public String getTableSchema() {
		return tableSchema;
	}
	public void setTableSchema(String tableSchema) {
		this.tableSchema = tableSchema;
	}
	public String getTableName() {
		return tableName;
	}
	public void setTableName(String tableName) {
		this.tableName = tableName;
	}
	public String getColumnName() {
		return columnName;
	}
	public void setColumnName(String columnName) {
		this.columnName = columnName;
	}
	public int getDataType() {
		return dataType;
	}
	public String getTypeName() {
		return typeName;
	}
	public void setTypeName(String typeName) {
		this.typeName = typeName;
	}
	public int getColumnSize() {
		return columnSize;
	}
	public void setColumnSize(int columnSize) {
		this.columnSize = columnSize;
	}
	public int getBufferLength() {
		return bufferLength;
	}
	public void setBufferLength(int bufferLength) {
		this.bufferLength = bufferLength;
	}
	public int getDecimalDigits() {
		return decimalDigits;
	}
	public void setDecimalDigits(int decimalDigits) {
		this.decimalDigits = decimalDigits;
	}
	public int getNumPrecRadix() {
		return numPrecRadix;
	}
	public void setNumPrecRadix(int numPrecRadix) {
		this.numPrecRadix = numPrecRadix;
	}
	public boolean isNullable() {
		return nullable;
	}
	public void setNullable(boolean nullable) {
		this.nullable = nullable;
	}
	public String getRemarks() {
		return remarks;
	}
	public void setRemarks(String remarks) {
		if (remarks == null || remarks.isEmpty() || remarks.equalsIgnoreCase("null"))
			this.remarks = "";
		else {
			remarks.trim();
			remarks = remarks.replaceAll("\\r", "");
			remarks = remarks.replaceAll("\\n", "");
			this.remarks = remarks;
		}
	}
	public String getColumnDefaults() {
		return columnDefaults;
	}
	public void setColumnDefaults(String columnDefaults) {
		if (columnDefaults == null || columnDefaults.isEmpty() || columnDefaults.equalsIgnoreCase("null"))
			this.columnDefaults = "";
		else {
			columnDefaults.trim();
			columnDefaults = columnDefaults.replaceAll("\\r", "");
			columnDefaults = columnDefaults.replaceAll("\\n", "");
			this.columnDefaults = columnDefaults;
		}
	}
	public int getSqlDataType() {
		return sqlDataType;
	}
	public void setSqlDataType(int sqlDataType) {
		this.sqlDataType = sqlDataType;
	}
	public int getSqlDatatimeSubject() {
		return sqlDatatimeSubject;
	}
	public void setSqlDatatimeSubject(int sqlDatatimeSubject) {
		this.sqlDatatimeSubject = sqlDatatimeSubject;
	}
	public int getCharOctetLength() {
		return charOctetLength;
	}
	public void setCharOctetLength(int charOctetLength) {
		this.charOctetLength = charOctetLength;
	}
	public int getOrdinalPosition() {
		return ordinalPosition;
	}
	public void setOrdinalPosition(int ordinalPosition) {
		this.ordinalPosition = ordinalPosition;
	}
	public String getIs_nullable() {
		return is_nullable;
	}
	public void setIs_nullable(String is_nullable) {
		this.is_nullable = is_nullable;
	}
	public String getSeqName() {
		return seqName;
	}
	public void setSeqName(String seqName) {
		this.seqName = seqName;
	}
	public boolean isPrimaryKey() {
		return primaryKey;
	}
	public void setPrimaryKey(boolean primaryKey) {
		this.primaryKey = primaryKey;
	}
	public String getDic() {
		return dic;
	}
	
	public void setDic(String dic) {
		this.dic = dic;
	}
	
	public String getScopeCatalog() {
		return scopeCatalog;
	}
	public void setScopeCatalog(String scopeCatalog) {
		this.scopeCatalog = scopeCatalog;
	}
	public String getScopeSchema() {
		return scopeSchema;
	}
	public void setScopeSchema(String scopeSchema) {
		this.scopeSchema = scopeSchema;
	}
	public String getScopeTable() {
		return scopeTable;
	}
	public void setScopeTable(String scopeTable) {
		this.scopeTable = scopeTable;
	}
	public int getSourceDataType() {
		return sourceDataType;
	}
	public void setSourceDataType(int sourceDataType) {
		this.sourceDataType = sourceDataType;
	}
	public String getIsAutoIncrement() {
		return isAutoIncrement;
	}
	public void setIsAutoIncrement(String isAutoIncrement) {
		this.isAutoIncrement = isAutoIncrement;
	}
	
	public void setPrimaryKey(String sPKColumName) throws SQLException {
		if (sPKColumName.equalsIgnoreCase(columnName))
			primaryKey = true;
	}
	
	public void setPrimaryKey(int primarykey) {
		if (primarykey != 0)
			this.primaryKey = true;
		else
			this.primaryKey = false;
	}

	public void setPrimaryKey(ResultSet rs) throws SQLException {
		String columnname = rs.getString("COLUMN_NAME").trim();
		if (columnname.equalsIgnoreCase(this.columnName))
			this.primaryKey = true;
	}

	public String toString() {
		return "tableCatalog=" + tableCatalog + ","
				+ "tableSchema=" + tableSchema + ","
				+ "tableName=" + tableName + ","
				+ "columnName=" + columnName + ","
				+ "dataType=" + dataType + ","
				+ "typeName=" + typeName + ";,"
				+ "columnSize=" + columnSize + ","
				+ "bufferLength=" + bufferLength + ","
				+ "decimalDigits=" + decimalDigits + ","
				+ "numPrecRadix=" + numPrecRadix + ","
				+ "nullable=" + nullable + ","
				+ "remarks=" + remarks + ","
				+ "columnDefaults=" + columnDefaults + ","
				+ "sqlDataType=" + sqlDataType + ","
				+ "sqlDatatimeSubject=" + sqlDatatimeSubject + ","
				+ "charOctetLength=" + charOctetLength + ","
				+ "ordinalPosition=" + ordinalPosition + ","
				+ "Is_Nullable=" + is_nullable + ","
				+ "seq=" + seqName + ","
				+ "PrimaryKey=" + primaryKey + ","
				+ "dic=" + dic;
	}

	public TableColumn() {
		setTableCatalog("");
		setTableSchema("");
		setTableName("");
		setColumnName("");
		setDataType(0);
		setTypeName("");
		setColumnSize(0);
		setBufferLength(0);
		setDecimalDigits(0);
		setNumPrecRadix(0);
		setNullable(false);
		setColumnDefaults("");
		setSqlDataType(0);
		setSqlDatatimeSubject(0);
		setCharOctetLength(0);
		setOrdinalPosition(0);
		setIs_nullable("");
		
		
		setSeqName("");
		setPrimaryKey(false);
		setDic("");
	}

	public TableColumn(ResultSet rs, String dbType) throws SQLException {

		setTableCatalog(rs.getString("TABLE_CAT"));
		setTableSchema(rs.getString("TABLE_SCHEM"));
		if (getTableSchema() == null && dbType.equalsIgnoreCase("mysql"))
			setTableSchema(getTableCatalog());
		setTableName(rs.getString("TABLE_NAME").trim());
		setColumnName(rs.getString("COLUMN_NAME").trim());
		//setColumnSize 一定要在DataType之前,否则会影响返回的JAVA类型
		setColumnSize(rs.getInt("COLUMN_SIZE"));
		setTypeName(rs.getString("TYPE_NAME").trim());
		setDecimalDigits(rs.getInt("DECIMAL_DIGITS"));
		setDataType(rs.getInt("DATA_TYPE"));
		setBufferLength(rs.getInt("BUFFER_LENGTH"));
		setNumPrecRadix(rs.getInt("NUM_PREC_RADIX"));
		setNullable(rs.getBoolean("NULLABLE"));
		setColumnDefaults(rs.getString("COLUMN_DEF"));
		setSqlDataType(rs.getInt("SQL_DATA_TYPE"));
		setSqlDatatimeSubject(rs.getInt("SQL_DATETIME_SUB"));
		setCharOctetLength(rs.getInt("CHAR_OCTET_LENGTH"));
		setOrdinalPosition(rs.getInt("ORDINAL_POSITION"));
		setIs_nullable(rs.getString("IS_NULLABLE"));
		setRemarks(rs.getString("REMARKS"));
		
		if (dbType.equalsIgnoreCase("mysql")) {
			setScopeCatalog(rs.getString("SCOPE_CATALOG"));
			setScopeSchema(rs.getString("SCOPE_SCHEMA"));
			setScopeTable(rs.getString("SCOPE_TABLE"));
			setSourceDataType(rs.getInt("SOURCE_DATA_TYPE"));
			setIsAutoIncrement(rs.getString("IS_AUTOINCREMENT"));
		}
		if (null != remarks && !remarks.isEmpty()) {
			int iStartPos = remarks.indexOf("seq");
			int iSplitPos = remarks.indexOf("|");
			// 存在seq 设置
			if (iStartPos >= 0 && iSplitPos >0) {
				String seq_setting = remarks.substring(iStartPos);
				int iEndPos = seq_setting.indexOf("|", iStartPos);
				String rem = remarks.substring(0, iStartPos);
				if (iEndPos >= 0) {
					seq_setting = seq_setting.substring(0, iEndPos);
					rem += remarks.substring(iEndPos + 1);
				}
				//setRemarks(rem);
				String arr[] = seq_setting.split("=");

				if (arr.length == 2) {
					setSeqName(arr[1]);
				} else {
					setSeqName("SEQ_" + getTableSchema() + "_" + getTableName() + "_" + getColumnName());
				}
			}
		} else {
			//setRemarks(this.columnName);
		}
	}

	public void setDataType(int datatype) {
		this.dataType = datatype;
		//orcale 下， 91同93
//		if ((datatype == 91))
//			this.dataType = 93;
		if ((datatype == 1111) && getTypeName().length()>=9&&(getTypeName().substring(0, 9).equalsIgnoreCase("TIMESTAMP")))
			this.dataType = 93;

		if (((datatype == 2) || (datatype == 3)) && (this.decimalDigits == 0)) {
			if (this.columnSize < 3) {
				this.dataType = -6;
			} else if (this.columnSize < 5) {
				this.dataType = 5;
			} else if (this.columnSize < 11)
				this.dataType = 4;
			else
				this.dataType = -5;
		}
	}
	
	public String getJAVADataType() {
		switch (this.dataType) {
	    case -101:
	        return "oracle.sql.TIMESTAMPTZ";
	    case -102:
		    return "oracle.sql.TIMESTAMPLTZ";
		case -7:
			return "Boolean";
		case -6:
			return "Byte";
		case -5:
			return "Long";
		case -4:
		case -3:
		case -2:
			return "byte[]";
		case -1:
			return "String";
		case 1:
			return "String";
		case 2:
		case 3:
			if (this.decimalDigits != 0) {
				return "java.math.BigDecimal";
			}
			return "Long";
		case 4:
			return "Integer";
		case 5:
			return "Short";
		case 6:
			return "Float";
		case 7:
		case 8:
			return "Double";
		case 12:
			return "String";
		case 91:
			return "java.util.Date";
		case 92:
			return "java.sql.Time";
		case 93:
			return "java.sql.Timestamp";
		case 1111:
		case 2004:
		case 2005:
			return "String";
		}
		return "";
	}
	
	public String getTableBeanUName() {
		return StringUtil.formatBeanNameFirstUpper(getTableName());
	}

	public String getTableBeanLName() {
		return StringUtil.formatBeanNameFirstLow(getTableName());
	}

	public String getColumBeanUName() {
		return StringUtil.formatBeanNameFirstUpper(this.columnName);
	}

	public String getColumBeanLName() {
		return StringUtil.formatBeanNameFirstLow(this.columnName);
	}
	
	public static Vector<TableColumn> GetTableInfo(Connection Conn, String schameName, String sTableName, String likeStr, String dbType) {

		Vector<TableColumn> ColumnsInTable = new Vector<TableColumn>();
		try {

			DatabaseMetaData dbmd = Conn.getMetaData();
			ResultSet rs = dbmd.getColumns(null, schameName, sTableName, likeStr);
			if (rs == null) {
				System.out.println("The Result Set is null ");
				return null;
			}
			while (rs.next()) {
				TableColumn tableColumns = new TableColumn(rs, dbType);
				ColumnsInTable.addElement(tableColumns);
			}
			setPrimaryKey(Conn, likeStr, ColumnsInTable);
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return ColumnsInTable;
	}
	
	public static void setPrimaryKey (Connection Conn, String likeStr, Vector<TableColumn> Colums) {

		try {
			DatabaseMetaData dbmd = Conn.getMetaData();
			if (Colums == null || Colums.size() <= 0)
				return;
			ResultSet rs = dbmd.getPrimaryKeys(Colums.get(0).getTableCatalog(), Colums.get(0).getTableSchema(), Colums.get(0).getTableName());
			while (rs.next()) {
				String sPKColumName = rs.getString("COLUMN_NAME").trim();
				for (int i = 0; i < Colums.size(); i++) {
					TableColumn tc = Colums.get(i);
					tc.setPrimaryKey(sPKColumName);
				}
			}
			rs.close();
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
		return;
	}
	
	public static TableColumn getPKColum(Vector<TableColumn> Colums) {
		if (Colums == null || Colums.size() == 0)
			return null;
		for (int i = 0; i < Colums.size(); i++) {
			if (Colums.get(i).isPrimaryKey()) {
				return Colums.get(i);
			}
		}
		return null;
	}
	
	public static TableColumn getColumFromListByName(Vector<TableColumn> ColumList, String sColumnName) {
		for (int i = 0; ColumList != null && i < ColumList.size(); i++) {
			if (ColumList.get(i).getColumnName().equalsIgnoreCase(sColumnName))
				return ColumList.get(i);
		}
		return null;
	}
}