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.engine.Request;
import hiro.yoshioka.sql.engine.SQLOperationType;
import hiro.yoshioka.sql.resource.DBTable;
import hiro.yoshioka.sql.resource.IDBSchema;
import hiro.yoshioka.sql.resource.IDBTable;
import hiro.yoshioka.sql.resource.ProcedureType;
import hiro.yoshioka.sql.resource.TableType;
import hiro.yoshioka.util.StringUtil;

import java.sql.Driver;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Properties;
import java.util.Set;
import java.util.TreeSet;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class MySQL extends AbsTransactionSQL implements IMYSQLDBConst {
	Pattern COMMENT_PATTERN = Pattern.compile("COMMENT=\'(.*)\'");
	static Pattern DATABASE_PATTERN = Pattern
			.compile("jdbc:mysql://([^/]+)/([^?]+)");

	public static String getSuggestURL() {
		return JDBC_URL_EXAMPLE;
	}

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

	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 boolean doOperation(SQLOperationType operation, Request request)
			throws SQLException {
		boolean retCode = true;
		switch (operation) {
		case SELECT_SESSION:
			request.setRDH(getSessionInfo());
			break;
		default:
			retCode = super.doOperation(operation, request);
			break;
		}
		return retCode;
	}

	/**
	 * @param operationCode
	 * @return
	 */
	public boolean canDoOperation(SQLOperationType operation) {
		switch (operation) {
		case CREATE_DATABASE:
		case DROP_DATABASE:
			return _info.isConnected();
		case SELECT_SESSION:
			return connected();
		case SELECT_LOCK:
			return false;
		default:
			return super.canDoOperation(operation);
		}
	}

	public static void main(String[] args) {
		System.out
				.println(getDatabaseName("jdbc:mysql://localhost/mysql_s?zeroDateTimeBehavior=convertToNull"));
		System.out
				.println(getDatabaseName("jdbc:mysql://localhost/mysql_ssss"));
	}

	public static String getDatabaseName(String urlString) {
		Matcher m = DATABASE_PATTERN.matcher(urlString);
		if (m.find()) {
			return m.group(2);
		}
		return null;
	}

	public static String getHostString(String urlString) {
		Matcher m = DATABASE_PATTERN.matcher(urlString);
		if (m.find()) {
			return m.group(1);
		}
		return null;
	}

	protected void setComments(GettingResourceRequest request)
			throws SQLException {
		ResultSetDataHolder rsC = null;
		int row = DEFAULT_ROW_NUM;

		request.beginTask("Grab Remarks", row);

		IDBSchema schema = getRoot().getCurrentSchema();
		IDBTable[] tables = schema.getTableOrViews();
		for (int i = 0; i < tables.length; i++) {
			if (!tables[i].isTable()) {
				continue;
			}
			rsC = executePrepareQuery(SHOW_CREATE_TABLE + tables[i].getName(),
					new String[0]);
			String comment = rsC.getStringData()[0][2];
			Matcher m = COMMENT_PATTERN.matcher(comment);
			if (m.find()) {
				comment = m.group(1);
			} else {
				comment = "";
			}
			tables[i].setComment(comment);
			request.subTask(comment);
			request.worked(1);
		}

	}

	@Override
	public boolean createDatabase(String name, Properties properties)
			throws SQLException {
		return this.createSchema(name, null);
	}

	@Override
	public boolean dropDatabase(String name) throws SQLException {
		return this.dropSchema(name, true);
	}

	@Override
	protected boolean createSchema(String schemaName, Properties properties)
			throws SQLException {
		String st = String.format("CREATE DATABASE  %s ", schemaName);
		return execute(st);
	}

	@Override
	protected boolean dropSchema(String schemaName, boolean cascade)
			throws SQLException {
		String st = String.format("DROP DATABASE IF EXISTS %s", schemaName);
		return execute(st);
	}

	@Override
	public boolean existsTable(String schemaName, String tableName)
			throws SQLException {

		String st = "SELECT COUNT(*) AS CNT FROM information_schema.TABLES WHERE UPPER(TABLE_SCHEMA)=? AND UPPER(TABLE_NAME)=? ";
		ResultSetDataHolder rdh = executePrepareQuery(st, new String[] {
				schemaName.toUpperCase(), tableName.toUpperCase() });
		boolean ret = rdh.getIntDataDefaultZero(0, "CNT") > 0;
		fLogger.info(String.format(" schemaName[%s] tableName[%s] result[%b]",
				schemaName, tableName, ret));
		return ret;
	}

	@Override
	public void setMaxRowNum(int max) {
		if (max > 50000000) {
			fLogger.warn("setMaxRows() out of range. " + max + " > 50000000");
			super.setMaxRowNum(50000000);
			return;
		}
		super.setMaxRowNum(max);
	}

	public boolean createUser(String user, String password,
			String targetDatabaseName) throws SQLException {
		String sql_statement = String.format(
				"create user '%s' identified by '%s'", user, password);
		boolean ret = execute(sql_statement);
		if (!StringUtil.isEmpty(targetDatabaseName)) {
			sql_statement = String
					.format("GRANT ALL ON testdb.* TO '%s'", user);
			ret = execute(sql_statement);
		}

		return ret;
	}

	// --
	public ResultSetDataHolder2 getSessionInfo() throws SQLException {
		return executePrepareQuery(_extra_con, SELECT_SESSION_INFO,
				(String[]) null);
	}

	public ResultSetDataHolder2 getLockInfo() throws SQLException {
		return null;
	}

	@Override
	public List<String> getTablePrimaryKeys(String catalog, String schema,
			String table) throws SQLException {

		List<String> items = new ArrayList<String>();
		ResultSetDataHolder2 rdh = executePrepareQuery(SELECT_PRIMARY_KEY,
				new String[] { schema.toUpperCase(), table.toUpperCase() });
		for (int i = 0; i < rdh.getRowCount(); i++) {
			items.add(rdh.getStringRecordRow(i)[1].getString());
		}
		return items;
	}

	@Override
	public String getDefaultSchemaName() {
		return getDatabaseName(_info.getURLString());
	}

	@Override
	public Set<String> getSchemas() {
		ResultSet rs = null;
		Statement st = null;
		Set<String> retSet = new LinkedHashSet<String>();
		try {
			st = _extra_con.createStatement();
			rs = st.executeQuery("SELECT DISTINCT SCHEMA_NAME FROM information_schema.SCHEMATA");
			while (rs.next()) {
				retSet.add(rs.getString(1));
			}
		} catch (Exception e) {
			fLogger.error(StringUtil.EMPTY_STRING, e);
			return Collections.EMPTY_SET;
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
				}
			}
			if (st != null) {
				try {
					st.close();
				} catch (SQLException e) {
				}
			}
			fLogger.info("end [" + retSet + "]");
			capturing = false;
		}
		return retSet;
	}

	protected void createDBTableDef(GettingResourceRequest request)
			throws SQLException {

		request.beginTask("Grab table defenitions",
				getRoot().getSchemas().length);
		if (request.targetType.isOnlySchema()) {
			IDBSchema target_schema = (IDBSchema) request.selectionResource;
			createTableResouceSub(request, target_schema);
			request.worked(1);
		} else {
			for (IDBSchema mschema : getRoot().getSchemas()) {
				createTableResouceSub(request, mschema);
				request.worked(1);
			}
		}
	}

	private boolean createTableResouceSub(GettingResourceRequest request,
			IDBSchema mschema) throws SQLException {
		ResultSetDataHolder rdh = executePrepareQuery(_extra_con,
				SELECT_TABLE_LIST, new String[] { mschema.getName() });
		for (int i = 0; i < rdh.getRowCount(); i++) {
			String tableName = rdh.getStringData(i, "TABLE_NAME");
			String type = rdh.getStringData(i, "TABLE_TYPE").toUpperCase();
			// TODO: add grab condition

			DBTable dbTable = new DBTable(mschema);
			dbTable.setName(tableName);
			request.subTask(dbTable.getNameWithAsComment());
			dbTable.setTableType(type);
			if (doCaptureColumn(dbTable, request)) {
				setTableColumns(mschema.getName(), dbTable);
			}
			mschema.putTable(dbTable);

			setResourceProperties(dbTable, i, rdh);
			if (request.canceld()) {
				return false;
			}
		}
		return true;
	}

	@Override
	protected void createDBProcedureDef(GettingResourceRequest request)
			throws SQLException {
		if (request.targetType.isOnlySchema()) {
			IDBSchema mschema = (IDBSchema) request.selectionResource;
			if (createProcedureResouceSub(request, mschema)) {

			}
		} else {
			IDBSchema[] schemas = getRoot().getSchemas();
			request.beginTask("DatabaseMetaData#getProcedures", schemas.length);
			for (IDBSchema mschema : schemas) {
				request.worked(1);
				String sn = mschema.getName();
				if (isSkipSchemaAtGetAllMetaData(sn)) {
					continue;
				}

				createProcedureResouceSub(request, mschema);

				if (request.canceld()) {
					break;
				}
			}
		}
	}

	protected boolean createProcedureResouceSub(GettingResourceRequest request,
			IDBSchema mschema) throws SQLException {
		ResultSetDataHolder rdh = executePrepareQuery(_extra_con,
				SELECT_SCHEMAS_PROCEDURE_LIST,
				new String[] { mschema.getUName() });

		System.out.println(rdh);

		for (int i = 0; i < rdh.getRowCount(); i++) {
			String tableName = rdh.getStringData(i, "ROUTINE_NAME");
			String type = rdh.getStringData(i, "ROUTINE_TYPE").toUpperCase();
			String comment = rdh.getStringData(i, "ROUTINE_COMMENT");
			String ddl = rdh.getStringData(i, "ROUTINE_DEFINITION");

			if (tableName.indexOf("/") >= 0 || tableName.indexOf("\\") >= 0
					|| tableName.indexOf("BIN$") >= 0) {

				continue;
			}
			DBTable dbProcudure = new DBTable(mschema);
			dbProcudure.setName(tableName);
			dbProcudure.setComment(comment);
			dbProcudure.setProcedureType(ProcedureType.parse(type));
			if (dbProcudure.getProcedureType().isProcedureOrUnknown()) {
				dbProcudure.setTableType(TableType.PROCEDURE);
			} else {
				dbProcudure.setTableType(TableType.FUNCTION);
			}

			dbProcudure.setText(ddl);
			if (!request.targetType.isOnlySchema()) {
				dbProcudure.setResources(getProcedureColumns(mschema.getName(),
						dbProcudure));
			}
			mschema.putProcedure(dbProcudure);

			setResourceProperties(dbProcudure, i, rdh);

			request.subTask(dbProcudure.toString());
			if (request.canceld()) {
				return false;
			}
		}
		return true;
	}

	@Override
	public Set<String> getTables(String schema) {
		PreparedStatement pre = null;
		ResultSet rsC = null;
		try {
			notifyExecute(_extra_con, SQLExecutionStatus.GET_META_TABLE);
			capturing = true;
			pre = _extra_con
					.prepareStatement(
							"SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ? AND TABLE_TYPE='BASE TABLE'",
							ResultSet.TYPE_FORWARD_ONLY,
							ResultSet.CONCUR_READ_ONLY);
			pre.setString(1, schema);
			rsC = pre.executeQuery();
			Set<String> retSet = new TreeSet<String>();
			while (rsC.next()) {
				retSet.add(rsC.getString("TABLE_NAME"));
			}
			return retSet;
		} catch (Exception e) {
			fLogger.error(StringUtil.EMPTY_STRING, e);
			return Collections.EMPTY_SET;
		} finally {
			fLogger.info("end"); //$NON-NLS-1$
			capturing = false;
			try {
				if (rsC != null) {
					rsC.close();
				}
				if (pre != null) {
					pre.close();
				}
			} catch (SQLException e) {
			}
		}
	}

	@Override
	protected boolean setTableText(GettingResourceRequest request)
			throws SQLException {
		boolean retCode = true;
		ResultSetDataHolder rdh = null;

		String show_query = null;
		if (request.targetType.isOnlySelectedResouce()) {
			request.beginTask(String.format("SCHEMA[%s] GET_DDL ",
					request.selectionResource.getName()), 3);
			if (request.selectionResource instanceof IDBTable) {
				IDBTable tbl = (IDBTable) request.selectionResource;

				if (tbl.isTable()) {
					show_query = String.format("show create table %s", tbl
							.getParent().getNameWithParentName());
				} else {
					show_query = String.format("show create view %s", tbl
							.getParent().getNameWithParentName());
				}
				rdh = executePrepareQuery(_extra_con, show_query,
						StringUtil.EMPTY_STRING_ARRAY);
				if (rdh.getRowCount() > 0) {
					tbl.setText(rdh.getStringRecordRow(0)[2].getString());
				} else {
					retCode = false;
				}
			}
			return retCode;
		}
		IDBSchema[] schemas = getRoot().getSchemas();

		for (int i = 0; i < schemas.length; i++) {
			List<IDBTable> tableSet = schemas[i]
					.getTablesList(TableType.TABLE_OR_VIEW_OR_PROCEDURE_OR_FUNCTION_SET);

			IDBTable[] prs = schemas[i].getTableOrSystemTables();
			request.beginTask(
					String.format("SCHEMA[%s] GET_DDL ", schemas[i].getName()),
					tableSet.size());
			for (IDBTable tbl : prs) {
				request.subTask(String.format("%s[%s]", tbl.getTableType(),
						tbl.getName()));
				setDDLToTable(schemas[i], tbl);
				request.worked(1);
			}
			// IDBTrigger[] trg = schemas[i].getTriggers();
			// request.beginTask(
			// String.format("SCHEMA[%s] GET_DDL TRIGGERS ",
			// schemas[i].getName()), trg.length);
			// for (int j = 0; j < trg.length; j++) {
			// request.subTask(String.format("TRIGGER[%s]", trg[j].getName()));
			// rdh = executePrepareQuery(
			// _extra_con,
			// SELECT_GET_DDL,
			// new String[] { "TRIGGER", trg[j].getUName(),
			// schemas[i].getUName() });
			// if (rdh.getRowCount() > 0) {
			// trg[j].setText(rdh.getStringData(0, "TEXT"));
			// }
			// request.worked(1);
			// }
		}
		return retCode;
	}

	private boolean setDDLToTable(IDBSchema schema, IDBTable tbl)
			throws SQLException {
		String show_query = null;
		switch (tbl.getTableType()) {
		case TABLE:
		case SYSTEM_TABLE:
			show_query = String.format("show create table %s.%s",
					schema.getName(), tbl.getName());
			break;
		case VIEW:
			show_query = String.format("show create view %s.%s",
					schema.getName(), tbl.getName());
			break;
		case FUNCTION:
			show_query = String.format("show create function %s.%s",
					schema.getName(), tbl.getName());
			break;
		case PROCEDURE:
			show_query = String.format("show create procedure %s.%s",
					schema.getName(), tbl.getName());
			break;
		}
		ResultSetDataHolder2 rdh = executePrepareQuery(_extra_con, show_query,
				StringUtil.EMPTY_STRING_ARRAY);
		if (rdh.getRowCount() > 0) {
			tbl.setText(rdh.getStringRecordRow(0)[2].getString());
			return true;
		}
		return false;
	}

	@Override
	protected void getTrigger(GettingResourceRequest request)
			throws SQLException {
		fLogger.fatal("Not support yet...");
	}

	public static String getLimitString() {
		return "LIMIT 0, 30";
	}

	@Override
	protected void getSequence(GettingResourceRequest request)
			throws SQLException {
		fLogger.fatal("Not support yet...");

	}

}