package hiro.yoshioka.sql;

import hiro.yoshioka.ast.sql.oracle.WolfSQLParserConstants;
import hiro.yoshioka.sdh.ResultSetDataHolder;
import hiro.yoshioka.sdh2.ResultSetDataHolder2;
import hiro.yoshioka.sql.engine.GettingResourceRequest;
import hiro.yoshioka.sql.resource.DBColumn;
import hiro.yoshioka.sql.resource.DBResourceType;
import hiro.yoshioka.sql.resource.DBSequence;
import hiro.yoshioka.sql.resource.IDBColumn;
import hiro.yoshioka.sql.resource.IDBResource;
import hiro.yoshioka.sql.resource.IDBSchema;
import hiro.yoshioka.sql.resource.IDBTable;
import hiro.yoshioka.util.SQLDataType;
import hiro.yoshioka.util.SQLUtil;
import hiro.yoshioka.util.StringUtil;

import java.sql.Driver;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class HSQL extends GeneralSQL {
	static final String DEFAULT_SCHEMA_NAME = "PUBLIC";
	static final String SQL_EXISTS_SCHEMA = "SELECT COUNT(*) CNT FROM INFORMATION_SCHEMA.SYSTEM_SCHEMAS WHERE UPPER(TABLE_SCHEM) = ?";
	static final String SQL_CREATE_SCHEMA_PRE = "CREATE SCHEMA ";
	static final String SQL_CREATE_SCHEMA_POST = " AUTHORIZATION DBA";
	static final String SQL_SELECT_TABLES = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.SYSTEM_TABLES "
			+ " WHERE TABLE_TYPE='TABLE' AND upper(TABLE_SCHEM) = UPPER(?)";
	static final String SQL_EXISTS_TABLE = "SELECT COUNT(*) CNT FROM INFORMATION_SCHEMA.SYSTEM_TABLES WHERE upper(TABLE_SCHEM) = ? AND UPPER(TABLE_NAME) =?";
	static final String SQL_DROP_TABLE = "DROP TABLE ";

	static final String _SQL_COLUMN_DEF = "SELECT  "
			+ "  TABLE_SCHEM,TABLE_NAME,COLUMN_NAME,DATA_TYPE,TYPE_NAME "
			+ " ,COLUMN_SIZE,BUFFER_LENGTH,DECIMAL_DIGITS,NUM_PREC_RADIX,NULLABLE "
			+ " ,REMARKS,SQL_DATA_TYPE,CHAR_OCTET_LENGTH "
			+ " ,ORDINAL_POSITION,IS_NULLABLE "
			+ " FROM INFORMATION_SCHEMA.SYSTEM_COLUMNS  "
			+ " WHERE TABLE_SCHEM = ? AND TABLE_NAME=? ORDER BY TABLE_SCHEM,TABLE_NAME,ORDINAL_POSITION";

	static final String _SELECT_DIFF_TABLE = " SELECT                                                                          "
			+ "   TABLE_NAME,            NULL AS TABLE_COMMENT,                                 "
			+ "   COLUMN_NAME,           TYPE_NAME,                                             "
			+ "   COLUMN_SIZE AS COLUMN_LENGTH,                                                 "
			+ "   NULL AS FLOATPART,                                                            "
			+ "   CASE NULLABLE WHEN 1 THEN NULL ELSE 'NN' END not_null,                        "
			+ "   NULL AS PRI_KEY,                                                              "
			+ "   NULL AS uniq_KEY,                                                             "
			+ "   ORDINAL_POSITION AS COLUMN_NUMBER,                                             "
			+ "   REMARKS AS COLUMN_COMMENT                                                      "
			+ " FROM                                                                            "
			+ "   INFORMATION_SCHEMA.SYSTEM_COLUMNS                                             "
			+ " WHERE                                                                           "
			+ "   UPPER(TABLE_SCHEM) = UPPER(?)                                           ";

	static final String _SQL_COLUMN_PK = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.SYSTEM_PRIMARYKEYS  "
			+ " WHERE TABLE_SCHEM = ? AND TABLE_NAME=?";

	static final String _SELECT_ALL_SEQUENCE = "SELECT  "
			+ "  SEQUENCE_SCHEMA, " + "  SEQUENCE_NAME, "
			+ "  DTD_IDENTIFIER, " + "  MAXIMUM_VALUE \"MAX\", "
			+ "  MINIMUM_VALUE \"MIN\", " + "  INCREMENT, "
			+ "  CYCLE_OPTION CYCLE, " + "  START_WITH "
			+ " FROM INFORMATION_SCHEMA.SYSTEM_SEQUENCES  "
			+ " WHERE SEQUENCE_SCHEMA = ? ";
	static final String _SELECT_SEQUENCE_CNT = "SELECT COUNT(*) AS \"CNT\" FROM INFORMATION_SCHEMA.SYSTEM_SEQUENCES WHERE UPPER(SEQUENCE_SCHEMA) = UPPER(?) AND UPPER(SEQUENCE_NAME) =UPPER(?)";

	public static String getSuggestURL() {
		return "jdbc:hsqldb:hsql://127.0.0.1:9003/hsql_db_yon;shutdown=true";
	}

	protected HSQL(Driver ds) {
		super(ds);
	}

	// public void createTable(IDBTable table) throws SQLException {
	// IDBResource schema = table.getParent();
	// if (!existsSchema(schema.getName())) {
	// execute(SQL_CREATE_SCHEMA_PRE + schema.getName()
	// + SQL_CREATE_SCHEMA_POST);
	// }
	// if (existsTable(schema.getName(), table.getName())) {
	// execute(String.format("%s%s.%s", SQL_DROP_TABLE, schema.getName(),
	// table.getName()));
	// }
	//
	// IDBColumn[] columns = table.getColumns();
	// StringBuilder buff = new StringBuilder();
	// buff.append(String.format("CREATE TABLE %s.%s ( %n", table.getParent()
	// .getName(), table.getName()));
	// for (int i = 0; i < columns.length; i++) {
	// if (i > 0) {
	// buff.append(",");
	// }
	// buff.append(String.format("  %s %s", columns[i].getName(),
	// dataType(columns[i])));
	// if (columns[i].isNotNull()) {
	// buff.append(" NOT NULL");
	// }
	//
	// if (columns[i].isPkey()) {
	// buff.append(" PRIMARY KEY");
	// }
	// buff.append(String.format("%n"));
	// }
	// buff.append(") ");
	//
	// execute(buff.toString());
	//
	// }

	// private String dataType(IDBColumn column) {
	// switch (column.getDataType()) {
	// case Types.TINYINT:
	// case Types.SMALLINT:
	// case Types.INTEGER:
	// case Types.BIGINT:
	// case Types.NUMERIC:
	// case Types.DECIMAL:
	// return "NUMERIC(" + column.getSize() + ")";
	//
	// case Types.FLOAT:
	// case Types.REAL:
	// case Types.DOUBLE:
	// return "REAL";
	//
	// case Types.CHAR:
	// case Types.NCHAR:
	// return "CHAR(" + column.getSize() + ")";
	// case Types.VARCHAR:
	// case Types.LONGVARCHAR:
	// case Types.NVARCHAR:
	// case Types.LONGNVARCHAR:
	// return "VARCHAR(" + column.getSize() + ")";
	// case Types.DATE:
	// return "DATE";
	// case Types.TIME:
	// return "TIME";
	// case Types.TIMESTAMP:
	// return "TIMESTAMP";
	// case Types.BLOB:
	// return "BLOB";
	// case Types.CLOB:
	// return "CLOB";
	// case Types.BIT:
	// return "BIT";
	// case Types.BOOLEAN:
	// return "BOOLEAN";
	// case Types.BINARY:
	// return "BINARY";
	//
	// case Types.VARBINARY:
	// return "VARBINARY";
	// case Types.LONGVARBINARY:
	// return "LONGVARBINARY";
	// case Types.NULL:
	// case Types.OTHER:
	// case Types.JAVA_OBJECT:
	// case Types.DISTINCT:
	// case Types.STRUCT:
	// case Types.ARRAY:
	// case Types.REF:
	// case Types.DATALINK:
	// case Types.ROWID:
	// case Types.NCLOB:
	// case Types.SQLXML:
	//
	// return "UNDEFINED";
	// }
	// return "UNDEFINED";
	// }

	// public boolean existsSchema(String name) throws SQLException {
	// ResultSetDataHolder rdh = executePrepareQuery(SQL_EXISTS_SCHEMA,
	// new String[] { name.toUpperCase() });
	// return rdh.getIntData(0, "CNT") > 0;
	// }

	@Override
	protected boolean createSchema(String schemaName, Properties properties)
			throws SQLException {
		String st = String.format("CREATE SCHEMA %s AUTHORIZATION DBA",
				schemaName);
		executePrepare(st, EMPTY);
		return true;
	}

	@Override
	public String getDefaultSchemaName() {
		return HSQL.DEFAULT_SCHEMA_NAME;
	}

	@Override
	public boolean existsTable(String schemaName, String tableName)
			throws SQLException {
		ResultSetDataHolder rdh = executePrepareQuery(
				SQL_EXISTS_TABLE,
				new String[] { schemaName.toUpperCase(),
						tableName.toUpperCase() });
		return rdh.getIntDataDefaultZero(0, "CNT") > 0;
	}

	protected boolean dropTable(String schemaName, IDBTable table,
			boolean cascade) throws SQLException {
		// if (StringUtil.isEmpty(schemaName)) {
		// schemaName = HSQL.DEFAULT_SCHEMA_NAME;
		// }
		return super.dropTable(schemaName, table, cascade);
	}

	@Override
	public void setTableColumns(String schema, IDBTable table)
			throws SQLException {
		ResultSetDataHolder rdh = null;
		IDBSchema[] schemas = getRoot().getSchemas();
		for (int i = 0; i < schemas.length; i++) {
			if (schemas[i].getName().length() == 0) {
				continue;
			}
			rdh = executePrepareQuery(_SQL_COLUMN_DEF, new String[] { schema,
					table.getName() });

			for (int j = 0; j < rdh.getRowCount(); j++) {
				DBColumn col = new DBColumn(table);
				col.setName(rdh.getStringData(j, "COLUMN_NAME"));
				col.setDataType(SQLDataType.parse(rdh.getIntDataDefaultZero(j,
						"DATA_TYPE")));
				col.setSize(rdh.getIntDataDefaultZero(j, "COLUMN_SIZE"));
				col.setDecimalDigits(rdh.getIntDataDefaultZero(j,
						"DECIMAL_DIGITS"));

				col.setNotNull("NO".equalsIgnoreCase(rdh.getStringData(j,
						"IS_NULLABLE")));
				// col.setPKey(pkey);
				col.setDataTypeString(rdh.getStringData(j, "TYPE_NAME"));
				col.setMaxColumnNameLength(SQLUtil.getMaxColumnNameBytes(_con));

				table.putResource(col.getUName(), col);
			}
			rdh = executePrepareQuery(_SQL_COLUMN_PK, new String[] { schema,
					table.getName() });
			for (int j = 0; j < rdh.getRowCount(); j++) {
				IDBResource res = table.getResource(rdh.getStringData(j,
						"COLUMN_NAME"));
				if (res != null) {
					((IDBColumn) res).setPKey(true);
				}
			}
		}
	}

	@Override
	protected void getSequence(GettingResourceRequest request)
			throws SQLException {
		ResultSetDataHolder rdh = null;
		IDBSchema[] schemas = getRoot().getSchemas();
		for (int i = 0; i < schemas.length; i++) {
			if (schemas[i].getName().length() == 0) {
				continue;
			}
			if (request.targetType.isOnlySchema()
					&& !schemas[i].equals(request.selectionResource)) {
				continue;
			}

			rdh = executePrepareQuery(_SELECT_ALL_SEQUENCE,
					new String[] { schemas[i].getName() });
			DBSequence sequence = null;
			for (int j = 0; j < rdh.getRowCount(); j++) {
				sequence = new DBSequence(schemas[i]);
				String name = rdh.getStringData(j, "SEQUENCE_NAME");
				sequence.setName(name);
				// String text = _SELECT_SEQUENCE_BODY + schemas[i].getName()
				// + "." + name;
				// rdh2 = executePrepareQuery(text, EMPTY);
				if (rdh.getRowCount() > 0) {
					setResourceProperties(sequence, 0, rdh);
				}
				schemas[i].putSequence(sequence);
				// setViewText(trigger);
			}
			if (request.canceld()) {
				return;
			}
		}
	}

	protected String getSupportToken() {
		StringBuffer buf = new StringBuffer();
		Pattern p = Pattern.compile("\"(\\w+)\"");
		String[] str = WolfSQLParserConstants.tokenImage;
		for (int i = 0; i < str.length; i++) {
			Matcher m = p.matcher(str[i]);
			if (m.matches()) {
				buf.append(m.group(1)).append(",");
			}
		}
		if (buf.length() > 0) {
			buf.setLength(buf.length() - 1);
		}
		return buf.toString();
	}

	public ResultSetDataHolder2 executeBatUpdate(String[] statements)
			throws SQLException {
		Statement statement = null;
		ResultSetDataHolder2 sdh = null;
		if (_con == null) {
			return sdh;
		}
		try {
			boolean[] ret = new boolean[statements.length];
			long time = System.currentTimeMillis();
			for (int i = 0; i < ret.length; i++) {
				ret[i] = execute(statements[i]);
			}

			time = System.currentTimeMillis() - time;

			sdh = createRdh2(new String[] { "Returns", "Statement" }, null);
			for (int i = 0; i < ret.length; i++) {
				sdh.addRow(new String[] { String.valueOf(ret[i]), statements[i] });
			}

			sdh.setWrapTime(time);
			return sdh;
		} finally {
			setTransactionTime(true);

			if (statement != null) {
				statement.close();
			}
		}
	}

	// ----------------------------------------------------------------
	// [4] DIFF
	// ----------------------------------------------------------------
	@Override
	public ResultSetDataHolder2 getDiffInfoOf(DBResourceType resourceType,
			String schemaName, boolean withValue) {
		ResultSetDataHolder2 ret = null;
		try {
			switch (resourceType) {
			case TABLE:
				ret = executePrepareQuery(_SELECT_DIFF_TABLE,
						new String[] { schemaName });
				break;
			}
		} catch (SQLException e) {
			fLogger.error(StringUtil.EMPTY_STRING, e);
		}
		if (ret != null) {
			ret.setTableNameE(String.format("DEF_%s_%s", schemaName,
					resourceType.name()));
		}
		fLogger.debug("[" + _info.getDisplayString() + "] ret:[" + ret + "]");
		return ret;
	}

}