package de.fuberlin.wiwiss.d2rq.sql.vendor;

import java.math.BigInteger;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Properties;
import java.util.regex.Pattern;

import de.fuberlin.wiwiss.d2rq.sql.Quoter;
import de.fuberlin.wiwiss.d2rq.sql.Quoter.PatternDoublingQuoter;
import de.fuberlin.wiwiss.d2rq.sql.types.DataType;
import de.fuberlin.wiwiss.d2rq.sql.types.SQLBit;
import de.fuberlin.wiwiss.d2rq.sql.types.SQLBoolean;
import de.fuberlin.wiwiss.d2rq.sql.types.SQLDate;
import de.fuberlin.wiwiss.d2rq.sql.types.SQLExactNumeric;
import de.fuberlin.wiwiss.d2rq.sql.types.SQLTime;
import de.fuberlin.wiwiss.d2rq.sql.types.SQLTimestamp;

/**
 * This syntax class implements MySQL-compatible SQL syntax.
 * 
 * @author Richard Cyganiak 
 */
public class MySQL extends SQL92 {

	public MySQL() {
		super(true);
	}
	
	@Override
	public String getConcatenationExpression(String[] sqlFragments) {
		StringBuffer result = new StringBuffer("CONCAT(");
		for (int i = 0; i < sqlFragments.length; i++) {
			if (i > 0) {
				result.append(", ");
			}
			result.append(sqlFragments[i]);
		}
		result.append(")");
		return result.toString();
	}

	@Override
	public String quoteIdentifier(String identifier) {
		return backtickEscaper.quote(identifier);
	}
	private final static Quoter backtickEscaper = 
		new PatternDoublingQuoter(Pattern.compile("([\\\\`])"), "`");
	
	@Override
	public String quoteStringLiteral(String s) {
		return singleQuoteEscaperWithBackslash.quote(s);
	}
	private final static Quoter singleQuoteEscaperWithBackslash = 
		new PatternDoublingQuoter(Pattern.compile("([\\\\'])"), "'");
	
	@Override
	public Properties getDefaultConnectionProperties() {
		Properties result = new Properties();
		result.setProperty("autoReconnect", "true");
		result.setProperty("zeroDateTimeBehavior", "convertToNull");
		return result;
	}

	@Override
	// TODO: The MySQL JDBC driver reports TINYINT(1) as BIT, should be handled as xsd:boolean?
	public DataType getDataType(int jdbcType, String name, int size) {

		// MySQL reports BIT columns in result sets as VARBINARY,
		// and formats the value as a number. This makes no sense.
		if (jdbcType == Types.VARBINARY && "BIT".equals(name)) {
			return new MySQLCompatibilityBitDataType(this);
		}
		
		// TINYINT(1) is conventionally treated as BOOLEAN in MySQL.
		// MySQL reports TINYINT(1) either as Types.BIT with size 0,
		// or as Types.BIT with type name still TINYINT. All real BIT
		// types are reported with a size > 0.
		if (jdbcType == Types.BIT && ("TINYINT".equals(name) || size == 0)) {
			return new SQLBoolean(this, name);
		}

		// MySQL supports UNSIGNED varieties of the integer types
		if (name.contains("UNSIGNED")) {
			return new SQLExactNumeric(this, name, jdbcType, true);
		}
		
		// The MySQL driver chokes on some values that are supported by the
		// MySQL database but not by the corresponding Java objects.
		if (jdbcType == Types.DATE) {
			return new MySQLCompatibilityDateDataType(this);
		}
		if (jdbcType == Types.TIME) {
			return new MySQLCompatibilityTimeDataType(this);
		}
		if (jdbcType == Types.TIMESTAMP) {
			return new MySQLCompatibilityTimestampDataType(this);
		}
		
		return super.getDataType(jdbcType, name, size);
	}

	public static class MySQLCompatibilityBitDataType extends SQLBit {
		public MySQLCompatibilityBitDataType(Vendor syntax) {
			super(syntax, "BIT");
		}
		@Override
		public String value(ResultSet resultSet, int column) throws SQLException {
			String value = resultSet.getString(column);
			if (resultSet.wasNull()) return null;
			try {
				// TODO: We would like to request the actual length of the bit type
				// from the type descriptor, and only return the appropriate number
				// of binary digits, but metaData.getPrecision() doesn't
				// work on streaming result sets (because it requires opening
				// a new result set internal to the JDBC driver). So any leading
				// zeroes will be dropped.
				return new BigInteger(value).toString(2);
			} catch (NumberFormatException ex) {
				log.warn("Expected numeric, got '" + value + "'; treating as null");
				return null;
			}
		}
	}
	
	public static class MySQLCompatibilityDateDataType extends SQLDate {
		public MySQLCompatibilityDateDataType(Vendor syntax) {
			super(syntax, "DATE");
		}
		@Override
		public String value(ResultSet resultSet, int column) throws SQLException {
			// MySQL JDBC connector 5.1.18 chokes on the zero/error
			// value 0000-00-00 with a SQLException
			try {
				return super.value(resultSet, column);
			} catch (SQLException ex) {
				return null;
			}
		}
	}
	
	public static class MySQLCompatibilityTimeDataType extends SQLTime {
		public MySQLCompatibilityTimeDataType(Vendor syntax) {
			super(syntax, "TIME");
		}
		@Override
		public String value(ResultSet resultSet, int column) throws SQLException {
			// MySQL JDBC connector 5.1.18 chokes on negative or too
			// large TIME values with a SQLException
			try {
				return super.value(resultSet, column);
			} catch (SQLException ex) {
				log.warn(ex);
				return null;
			}
		}
	}
	
	public static class MySQLCompatibilityTimestampDataType extends SQLTimestamp {
		public MySQLCompatibilityTimestampDataType(Vendor syntax) {
			super(syntax, "TIMESTAMP");
		}
		@Override
		public String value(ResultSet resultSet, int column) throws SQLException {
			// MySQL JDBC connector 5.1.18 chokes on the zero/error
			// value 0000-00-00 00:00:00 with a SQLException
			try {
				return super.value(resultSet, column);
			} catch (SQLException ex) {
				return null;
			}
		}
	}
}