package hiro.yoshioka.sql.util;

import hiro.yoshioka.sdh.DatabaseType;
import hiro.yoshioka.sql.engine.MirroringRequest;
import hiro.yoshioka.sql.engine.MirroringRequest.SQL_TYPES_CONVERT_MODE;
import hiro.yoshioka.sql.resource.DBColumn;
import hiro.yoshioka.sql.resource.DBSchema;
import hiro.yoshioka.sql.resource.DBTable;
import hiro.yoshioka.sql.resource.IDBColumn;
import hiro.yoshioka.sql.resource.IDBTable;
import hiro.yoshioka.sql.resource.notes.NotesDBColumn;
import hiro.yoshioka.util.SQLDataType;
import hiro.yoshioka.util.StringUtil;

import java.beans.XMLDecoder;
import java.beans.XMLEncoder;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.ObjectInputStream;
import java.util.Set;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class SQLUtil2 {
	private static final Log fLogger = LogFactory.getLog(SQLUtil2.class
			.getClass());

	public static final String createCallableSql(IDBTable dbTable) {
		// if (fLogger.isTraceEnabled()) {
		// fLogger.info("\n----- INPUTS PARAMETERS -----");
		// }

		StringBuilder sql = new StringBuilder();

		if (dbTable.isFunction()) {
			sql.append("{ ? = call ");
		} else {
			sql.append("{ call ");
		}
		if (dbTable.getParent() != null) {
			if (dbTable.getParent().getName().length() > 0) {
				sql.append(dbTable.getParent().getName()).append(".");
			}
		}
		if (dbTable.getCatalog().length() > 0) {
			sql.append(dbTable.getCatalog()).append(".");
		}
		sql.append(dbTable.getName()).append("(");

		IDBColumn[] cols = dbTable.getColumns();
		boolean existsParam = false;
		for (int i = 0; i < cols.length; i++) {
			if (cols[i].isColumnReturn()) {
				continue;
			}
			sql.append("?,");
			existsParam = true;
		}
		sql.setLength(sql.length() - 1);
		if (existsParam) {
			sql.append(") }");
		} else {
			sql.append(" }");
		}
		return sql.toString();
	}

	public static String getTypeString(int type) {
		SQLDataType ret = SQLDataType.parse(type);
		if (ret == null) {
			return "UnKnown[" + type + "]";
		}
		return ret.getMessage();
	}

	public static String getTypeString(DatabaseType mappingFrom,
			DatabaseType mappingTo, IDBColumn column,
			Set<SQL_TYPES_CONVERT_MODE> sqlTypesConvertSet,
			String substitutionColumnName) {
		StringBuilder buf = new StringBuilder();
		if (StringUtil.isEmpty(substitutionColumnName)) {
			buf.append(String.format("%s ", column.getName()));
		} else {
			buf.append(String.format("%s ", substitutionColumnName));
		}
		int size = column.getSize();
		if (size <= 0) {
			size = Integer.MAX_VALUE;
		}
		if (mappingTo.isMySQL() && column.isPkey() && size > 255) {
			fLogger.warn("for MySQL database system constraints.. Key max size = 767byte, utf-8 use 3byte 255*3= 765");
			size = 255; // key max size = 767byte, utf-8 use 3byte 255*3= 765
		}

		switch (column.getDataType()) {
		// --------------------------------------------------------------------
		// CHARACTORS
		// --------------------------------------------------------------------
		case CHAR:
		case NCHAR:
			if (mappingFrom.isSQLServer()) {
				switch (mappingTo) {
				case ORACLE:
					buf.append(hiro.yoshioka.util.SQLUtil.VARCHAR2_4000);
					break;
				default:
					buf.append(String.format("CHAR(%d)", size));
					break;
				}
			} else {
				buf.append(String.format("CHAR(%d)", size));
				break;
			}
			break;
		case VARCHAR:
		case NVARCHAR:
			switch (mappingTo) {
			case ORACLE:
				if (mappingFrom.isSQLServer()) {
					buf.append(hiro.yoshioka.util.SQLUtil.VARCHAR2_4000);
				} else {
					buf.append(String.format("VARCHAR2(%d)", column.getSize()));
				}
				break;
			case MYSQL:
				if (mappingFrom.isDomino() && size > 255) {
					buf.append("LONGTEXT");
				} else {
					buf.append(String.format("VARCHAR(%d)", column.getSize()));
				}
				break;
			default:
				buf.append(String.format("VARCHAR(%d)", size));
				break;
			}
			break;
		case LONGVARCHAR:
		case CLOB:
			switch (mappingTo) {
			case HSQL:
				buf.append(String.format("VARCHAR(%d)", Integer.MAX_VALUE));
				break;
			case MS_SQLSERVER:
				buf.append("TEXT");
				break;
			case MYSQL:
				buf.append("LONGTEXT");
				break;
			case POSTGRES:
				buf.append("TEXT");
				break;
			default:
				buf.append("CLOB");
				break;
			}
			break;
		case LONGNVARCHAR:
		case NCLOB:
			switch (mappingTo) {
			case HSQL:
				buf.append(String.format("VARCHAR(%d)", Integer.MAX_VALUE));
				break;
			case MS_SQLSERVER:
				buf.append("NTEXT");
				break;
			case MYSQL:
				buf.append("LONGTEXT");
				break;
			case POSTGRES:
				buf.append("TEXT");
				break;
			default:
				buf.append("NCLOB");
				break;
			}
			break;
		// --------------------------------------------------------------------
		// NUMERICS
		// --------------------------------------------------------------------
		case TINYINT: // 1byte -128-127, 0-255
			switch (mappingTo) {
			case ORACLE:
				buf.append(String.format("NUMBER(%d)", size));
				break;
			default:
				buf.append(SQLDataType.TINYINT.name());
				break;
			}
			break;
		case SMALLINT: // 2byte -32768-32767, 0-65535
			switch (mappingTo) {
			case ORACLE:
				buf.append(String.format("NUMBER(%d)", size));
				break;
			default:
				buf.append(SQLDataType.SMALLINT.name());
				break;
			}
			break;
		case NUMERIC:
			switch (mappingTo) {
			case ORACLE:
				buf.append(String.format("NUMBER(%d)", size));
				break;
			default:
				buf.append(String.format("NUMERIC(%d)", size));
				break;
			}
			break;
		case DECIMAL:
			switch (mappingTo) {
			case ORACLE:
				if (column.hasDecimalDigits()) {
					buf.append(String.format("NUMBER(%d,%d)", size,
							column.getDecimalDigits()));
				} else {
					buf.append(String.format("NUMBER(%d)", size));
				}
				break;
			default:
				buf.append(String.format("DECIMAL(%d)", size));
				break;
			}
			break;
		case INTEGER:
			switch (mappingTo) {
			case ORACLE:
				buf.append(String.format("NUMBER(%d)", size));
				break;
			default:
				buf.append("INTEGER");
				break;
			}
			break;
		case REAL:
			switch (mappingTo) {
			case ORACLE:
				buf.append(String.format("NUMBER(%d)", size));
				break;
			default:
				buf.append("REAL");
				break;
			}
			break;
		case FLOAT:
			switch (mappingTo) {
			case ORACLE:
				buf.append("BINARY_FLOAT");
				break;
			case POSTGRES:
				buf.append("REAL");
				break;
			default:
				buf.append("FLOAT");
				break;
			}
			break;
		case DOUBLE:
			switch (mappingTo) {
			case MS_SQLSERVER:
				buf.append("FLOAT");
				break;
			case ORACLE:
				buf.append("BINARY_DOUBLE");
				break;
			case POSTGRES:
				buf.append("DOUBLE PRECISION");
				break;
			default:
				buf.append("DOUBLE");
				break;
			}
			break;
		case BIGINT:
			switch (mappingTo) {
			case ORACLE:
				buf.append(hiro.yoshioka.util.SQLUtil.BIGINT_TO_NUMBER);
				break;
			default:
				buf.append("BIGINT");
				break;
			}
			break;

		case BINARY_FLOAT:
			switch (mappingTo) {
			case ORACLE:
				buf.append("BINARY_FLOAT");
				break;
			default:
				buf.append("FLOAT");
				break;
			}
			break;
		case BINARY_DOUBLE:
			switch (mappingTo) {
			case ORACLE:
				buf.append("BINARY_DOUBLE");
				break;
			default:
				buf.append("FLOAT");
				break;
			}
			break;
		// --------------------------------------------------------------------
		// DATE
		// --------------------------------------------------------------------
		case DATE:
			switch (mappingTo) {
			case MS_SQLSERVER:
				buf.append("DATETIME");
				break;
			default:
				buf.append("DATE");
				break;
			}
			break;
		case TIMESTAMP:
			switch (mappingTo) {
			case MS_SQLSERVER:
				buf.append("DATETIME");
				break;
			default:
				buf.append("TIMESTAMP");
				break;
			}
			break;
		case TIME:
			switch (mappingTo) {
			case MS_SQLSERVER:
				buf.append("DATETIME");
				break;
			case ORACLE:
				buf.append("DATE");
				break;
			default:
				buf.append("TIME");
				break;
			}
			break;
		// --------------------------------------------------------------------
		// BINARY BLOB
		// --------------------------------------------------------------------
		case BINARY:
			switch (mappingTo) {
			case HSQL:
				buf.append(String.format("VARBINARY(%d)", size));
				break;
			case ORACLE:
				buf.append("BLOB");
				break;
			case MYSQL:
				buf.append("LONGBLOB");
				break;
			case POSTGRES:
				buf.append("BYTEA");
				break;
			default:
				buf.append(String.format("VARBINARY(%d)", size));
				break;
			}
			break;
		case VARBINARY:
			switch (mappingTo) {
			case HSQL:
				buf.append(String.format("VARBINARY(%d)", Integer.MAX_VALUE));
				break;
			case ORACLE:
				buf.append("BLOB");
				break;
			case MYSQL:
				buf.append("LONGBLOB");
				break;
			case POSTGRES:
				buf.append("BYTEA");
				break;
			default:
				buf.append(String.format("VARBINARY(%d)", Integer.MAX_VALUE));
				break;
			}
			break;
		case BLOB:
			switch (mappingTo) {
			case HSQL:
				buf.append(String.format("VARBINARY(%d)", Integer.MAX_VALUE));
				break;
			case MYSQL:
				// BLOB => MAX SIZE 65K
				// MEDIUMBLOB => MAX SIZE 16M
				// LONGBLOB => MAX SIZE 4GB
				buf.append("LONGBLOB");
				break;
			case MS_SQLSERVER:
				buf.append("IMAGE");
				break;
			case POSTGRES:
				buf.append("BYTEA");
				break;
			default:
				buf.append("BLOB");
				break;
			}
			break;
		case LONGVARBINARY:
			switch (mappingTo) {
			case HSQL:
				buf.append(String.format("VARBINARY(%d)", Integer.MAX_VALUE));
				break;
			case MS_SQLSERVER:
				buf.append("IMAGE");
				break;
			case ORACLE:
				buf.append("BLOB");
				break;
			case POSTGRES:
				buf.append("BYTEA");
				break;
			default:
				buf.append("LONGVARBINARY");
				break;
			}
			break;
		// --------------------------------------------------------------------
		// BIT BOOLEAN
		// --------------------------------------------------------------------
		case BIT:
			switch (mappingTo) {
			case ORACLE:
				buf.append(SQL_TYPES_CONVERT_MODE.getBooleanCnv(
						sqlTypesConvertSet).getColumnTypeDdl());
				break;
			default:
				buf.append("BIT");
				break;
			}
			break;
		case BOOLEAN:
			switch (mappingTo) {
			case ORACLE:
				buf.append(SQL_TYPES_CONVERT_MODE.getBooleanCnv(
						sqlTypesConvertSet).getColumnTypeDdl());
				break;
			case MS_SQLSERVER:
				SQL_TYPES_CONVERT_MODE mode = SQL_TYPES_CONVERT_MODE
						.getBooleanCnv(sqlTypesConvertSet);
				if (SQL_TYPES_CONVERT_MODE.BOOLEAN_TO_NUMBER.equals(mode)) {
					buf.append("NUMERIC(1)");
				} else {
					buf.append(mode.getColumnTypeDdl());
				}
				break;
			default:
				buf.append("BOOLEAN");
				break;
			}
			break;
		case STRUCT:
			switch (mappingTo) {
			case ORACLE:
				buf.append(hiro.yoshioka.util.SQLUtil.VARCHAR2_4000);
				break;
			default:
				buf.append(hiro.yoshioka.util.SQLUtil.VARCHAR_4000);
				break;
			}
			break;

		// case BINARY:
		// buf.append("NCLOB");
		// break;
		default:
			fLogger.fatal(String.format(
					"what is this column? name[%s] type[%s] typeString[%s]",
					column.getName(), column.getDataType(),
					column.getDataTypeString()));
		}
		if (column.isNotNull() && !column.isPkey()) {
			buf.append(" NOT NULL");
		}
		return buf.toString();
	}

	public static void main(String[] args) {
		DBTable table = new DBTable((DBSchema) null);
		DBColumn column = new DBColumn(table);
		table.putResource(column.getName(), column);
		column.setName("DATA");
		column.setDataType(SQLDataType.BLOB);

		// System.out.println(cnvEmptyBlob(table,
		// "update BL set data=? where pk=121"));
	}

	public static Object readObjectXML(File f) {
		XMLDecoder decoder = null;
		try {
			decoder = new XMLDecoder(new BufferedInputStream(
					new FileInputStream(f)));
			return decoder.readObject();
		} catch (FileNotFoundException e) {
			fLogger.error(e);
		} finally {
			if (decoder != null) {
				decoder.close();
			}
		}
		return null;
	}

	public static boolean saveXML(File f, Object saveObject) {
		XMLEncoder encoder = null;

		try {
			fLogger.info("save file->" + f);

			encoder = new XMLEncoder(new BufferedOutputStream(
					new FileOutputStream(f)));
			encoder.writeObject(saveObject);
			encoder.close();
			return true;
		} catch (Throwable e) {
			fLogger.error(e);
		}
		return false;
	}

	public static Object readObject(File f) {
		ObjectInputStream in = null;
		try {
			if (f.exists() && f.isFile()) {
				in = new ObjectInputStream(new FileInputStream(f));
				return in.readObject();

			} else {
				fLogger.info("File Not Found or Not File ["
						+ f.getAbsolutePath() + "]");
				return null;
			}
		} catch (Throwable e) {
			fLogger.error("Deserialize error", e);
			return null;
		} finally {
			if (in != null) {
				try {
					in.close();
				} catch (IOException e1) {
					return null;
				}
			}
		}
	}

	public static String createTableAtMirroring(String schemaName,
			IDBTable mappingFromTable, MirroringRequest request) {
		StringBuilder buf = new StringBuilder();
		if (mappingFromTable.isTable()) {
			buf.append("CREATE TABLE ");
			if (schemaName.length() > 0) {
				buf.append(schemaName).append(".");
			}
			buf.append(mappingFromTable.getName());
			buf.append(" ( ").append(StringUtil.LINE_SEPARATOR);
			IDBColumn[] columns = mappingFromTable.getColumns();
			fLogger.info("columns.length = " + columns.length);
			for (int j = 0; j < columns.length; j++) {
				if (j > 0) {
					buf.append(",").append(StringUtil.LINE_SEPARATOR);
				}
				buf.append("  ").append(
						getTypeString(request.getMirroringFromDatabaseType(),
								request.getMirroringToDatabaseType(),
								columns[j], request.getSqlTypesConvertSet(),
								request.getSubsutituteColumnName(columns[j]
										.getName())));

				if (request.makeBlob && columns[j] instanceof NotesDBColumn) {
					NotesDBColumn nc = (NotesDBColumn) columns[j];
					if (nc.isRichText()) {
						if (request.getMirroringToDatabaseType()
								.supportVarchar2()) {
							buf.append(String.format(
									"  ,%s_ATT VARCHAR2(512) %n",
									columns[j].getName()));
						} else {
							buf.append(String.format(
									"  ,%s_ATT VARCHAR(512) %n",
									columns[j].getName()));
						}
					}
				}
			}
			buf.append(StringUtil.LINE_SEPARATOR);
			if (mappingFromTable.hasPk()) {
				buf.append("  ,PRIMARY KEY (");
				int[] pk = mappingFromTable.getPkPositions();
				for (int j = 0; j < pk.length; j++) {
					if (j > 0) {
						buf.append(",");
					}
					buf.append(mappingFromTable.getColumns()[pk[j]].getName());
				}
				buf.append(")").append(StringUtil.LINE_SEPARATOR);
			}
			buf.append(")");
		} else {
			if (StringUtil.nvl(mappingFromTable.getText()).length() == 0) {
				return buf.toString();
			}
			String vt = mappingFromTable.getText().replaceFirst(
					"([\"a-zA-Z_]+[.])?\"?([a-zA-Z_]+)\"? *[(]", "$2 (");
			buf.append(vt);
			buf.append(StringUtil.LINE_SEPARATOR);
		}
		fLogger.info("=== CREATE TABLE ===");
		fLogger.info(buf.toString());
		return buf.toString();
	}

}
