package hiro.yoshioka.sql;

import hiro.yoshioka.ast.sql.oracle.WolfSQLParserConstants;
import hiro.yoshioka.sdh.ResultSetDataHolder;
import hiro.yoshioka.sql.engine.SQLOperationType;
import hiro.yoshioka.sql.resource.IDBTable;
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.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class SQLServerSQL extends GeneralSQL {
	private static Pattern DATABASE_PATTERN = Pattern.compile(
			"jdbc:sqlserver://([^;]+);.*databaseName=([^;]+)",
			Pattern.CASE_INSENSITIVE);

	protected SQLServerSQL(Driver driver) {
		super(driver);
	}

	public static void main(String[] args) {
		System.out
				.println(getHostString("jdbc:sqlserver://127.0.0.1:1433;databaseName=<NAME>;selectMethod=cursor"));
		System.out
				.println(getDatabaseName("jdbc:sqlserver://127.0.0.1:1433;databaseName=<NAME>;selectMethod=cursor"));
		System.out
				.println(getDatabaseName("jdbc:mysql://localhost/mysql_ssss"));
	}

	@Override
	public boolean commit() {
		fLogger.trace("now autoCommit=true");
		return true;
	}

	@Override
	public boolean rollback() {
		fLogger.trace("now autoCommit=true");
		return true;
	}

	// @Override
	// public boolean createUser(String name, Properties properties)
	// throws SQLException {
	// String database = getDatabaseName(_info.getURLString());
	// if (properties != null && properties.contains(PROPERTY.DATABASE.name()))
	// {
	// database = properties.getProperty(PROPERTY.DATABASE.name());
	// }
	// String sql_statement = String
	// .format("EXEC ('USE \"%s\"; CREATE USER \"%s\" FOR LOGIN \"%s\" WITH DEFAULT_SCHEMA = \"dbo\";') ",
	// database, name, name);
	// boolean ret = execute(sql_statement);
	// return ret;
	// }

	@Override
	protected boolean dropTable(String schemaName, IDBTable table,
			boolean cascade) throws SQLException {
		StringBuilder st = new StringBuilder();
		if (StringUtil.isEmpty(schemaName)) {
			st.append(String.format("DROP TABLE %s", table.getName()));
		} else {
			st.append(String.format("DROP TABLE %s.%s", schemaName,
					table.getName()));
		}
		// if (cascade) {
		// st.append(" CASCADE ");
		// }
		executePrepare(st.toString(), EMPTY);
		return true;
	}

	@Override
	public boolean existsTable(String schemaName, String tableName)
			throws SQLException {
		String sql = "SELECT count(*) as CNT "
				+ " FROM INFORMATION_SCHEMA.TABLES "
				+ " where upper(table_schema) = ? and upper(table_name) = ?";
		ResultSetDataHolder rdh = executePrepareQuery(sql, new String[] {
				schemaName.toUpperCase(), tableName.toUpperCase() });
		return rdh.getIntDataDefaultZero(0, "CNT") > 0;
	}

	@Override
	public boolean canDoOperation(SQLOperationType operation) {
		switch (operation) {
		case CREATE_SCHEMA:
			return false;
		default:
			return super.canDoOperation(operation);
		}
	}

	@Override
	public String getDefaultSchemaName() {
		String user = _info.getAuthenticate().getUser();
		if ("sa".equalsIgnoreCase(user)) {
			return "dbo";
		}
		return user;
	}

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

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

	@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 SCHEMA_NAME \"TABLE_SCHEM\" 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 + "]");
		}
		return retSet;
	}

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

		List<String> items = new ArrayList<String>();

		String sql = "select column_name  from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk , "
				+ " INFORMATION_SCHEMA.KEY_COLUMN_USAGE c "
				+ " where upper(pk.TABLE_NAME) = ? and upper(pk.table_schema) = ? "
				+ " and CONSTRAINT_TYPE = 'PRIMARY KEY' "
				+ " and c.TABLE_NAME = pk.TABLE_NAME and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME "
				+ " order by ordinal_position ";

		ResultSet rs = null;
		PreparedStatement st = null;
		try {
			st = _extra_con.prepareStatement(sql);
			st.setString(1, table.toUpperCase());
			st.setString(2, schema.toUpperCase());
			rs = st.executeQuery();
			while (rs.next()) {
				items.add(rs.getString(1));
			}
		} catch (Exception e) {
			fLogger.error(StringUtil.EMPTY_STRING, e);
			return Collections.EMPTY_LIST;
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
				}
			}
			if (st != null) {
				try {
					st.close();
				} catch (SQLException e) {
				}
			}
			fLogger.info("end [" + items + "]");
		}

		return items;
	}

	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 static String getSuggestURL() {
		return "jdbc:sqlserver://127.0.0.1:1433;databaseName=<NAME>";
	}

	public static String getLimitString() {
		return "WHERE ROWNUM < 30";
	}

}