/* Copyright (c) restSQL Project Contributors. Licensed under MIT. */
package org.restsql.core.impl.postgresql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

import org.postgresql.PGResultSetMetaData;
import org.postgresql.core.Field;
import org.postgresql.jdbc2.AbstractJdbc2ResultSetMetaData;
import org.restsql.core.impl.AbstractSqlResourceMetaData;
import org.restsql.core.impl.ColumnMetaDataImpl;
import org.restsql.core.sqlresource.SqlResourceDefinition;
import org.restsql.core.sqlresource.SqlResourceDefinitionUtils;

/**
 * Implements SqlResourceMetaData for PostgreSQL.
 * 
 * @author Mark Sawers
 */
public class PostgreSqlSqlResourceMetaData extends AbstractSqlResourceMetaData {
	private static final String SQL_COLUMNS_QUERY = "select column_name, data_type, column_default from information_schema.columns where table_catalog = ? and table_name = ?";
	private static final String SQL_PK_QUERY = "select column_name from information_schema.table_constraints tc, information_schema.key_column_usage kcu"
			+ " where tc.constraint_catalog = ? and tc.table_name = ?"
			+ " and tc.constraint_type = 'PRIMARY KEY'"
			+ " and tc.constraint_schema = kcu.constraint_schema and tc.table_name = kcu.table_name"
			+ " and tc.constraint_name = kcu.constraint_name";
	private static final String SQL_TABLE_SCHEMA_QUERY = "select table_schema from information_schema.tables where table_catalog = ? and table_name = ?";

	/**
	 * Retrieves database name from result set meta data. Hook method for buildTablesAndColumns() allows
	 * database-specific overrides.
	 */
	@Override
	protected String getColumnDatabaseName(final SqlResourceDefinition definition,
			final ResultSetMetaData resultSetMetaData, final int colNumber) throws SQLException {
		return SqlResourceDefinitionUtils.getDefaultDatabase(definition);
	}

	/**
	 * Retrieves actual column name from result set meta data. Hook method for buildTablesAndColumns() allows
	 * database-specific overrides.
	 */
	@Override
	protected String getColumnName(final SqlResourceDefinition definition,
			final ResultSetMetaData resultSetMetaData, final int colNumber) throws SQLException {
		return ((PGResultSetMetaData) resultSetMetaData).getBaseColumnName(colNumber);
	}

	/**
	 * Retrieves table name from result set meta data. Hook method for buildTablesAndColumns() allows database-specific
	 * overrides.
	 */
	@Override
	protected String getColumnTableName(final SqlResourceDefinition definition,
			final ResultSetMetaData resultSetMetaData, final int colNumber) throws SQLException {
		return ((PGResultSetMetaData) resultSetMetaData).getBaseTableName(colNumber);
	}

	/**
	 * Retrieves sql for querying columns. Hook method for buildInvisibleForeignKeys() and buildJoinTableMetadata()
	 * allows database-specific overrides.
	 */
	@Override
	protected String getSqlColumnsQuery() {
		return SQL_COLUMNS_QUERY;
	}

	/**
	 * Retrieves sql for querying primary keys. Hook method for buildPrimaryKeys allows database-specific overrides.
	 */
	@Override
	protected String getSqlPkQuery() {
		return SQL_PK_QUERY;
	}

	/**
	 * Retrieves qualified column label for disambiguating duplicate labels in SQL
	 * statements. MySQL can use the form <code>database.table.label</code>, for example <code>sakila.film.id</code>.
	 */
	@Override
	protected String getQualifiedColumnLabel(String tableName, String qualifiedTableName, final boolean readOnly, final String label) {
		return label;
	}

	/** Retrieves database-specific table name used in SQL statements. */
	@Override
	protected String getQualifiedTableName(final SqlResourceDefinition definition,
			final ResultSetMetaData resultSetMetaData, final int colNumber) throws SQLException {
		final PGResultSetMetaData pgMetaData = (PGResultSetMetaData) resultSetMetaData;
		return SqlResourceDefinitionUtils.getDefaultDatabase(definition) + "."
				+ pgMetaData.getBaseSchemaName(colNumber) + "." + pgMetaData.getBaseTableName(colNumber);
	}

	/** Retrieves database-specific table name used in SQL statements. Used to build join table meta data. */
	@Override
	protected String getQualifiedTableName(Connection connection, String databaseName, String tableName)
			throws SQLException {
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			statement = connection.prepareStatement(SQL_TABLE_SCHEMA_QUERY);
			String schemaName = "unknown";
			statement.setString(1, databaseName);
			statement.setString(2, tableName);
			resultSet = statement.executeQuery();
			if (resultSet.next()) {
				schemaName = resultSet.getString(1);
			}
			return databaseName + "." + schemaName + "." + tableName;
		} catch (final SQLException exception) {
			if (resultSet != null) {
				resultSet.close();
			}
			if (statement != null) {
				statement.close();
			}
			throw exception;
		}
	}

	/**
	 * Return whether a column in the given result set is read-only. The PostgreSQL implementation calls isReadOnly() on
	 * the result set and uses reflection to detect columns with SQL functions as read-only.
	 * 
	 * Contributed by <a href="https://github.com/rhuitl">rhuitl</a>.
	 * 
	 * @param resultSetMetaData Result set metadata
	 * @param colNumber Column number (1..N)
	 * @throws SQLException if a database access error occurs
	 */
	@Override
	protected boolean isColumnReadOnly(ResultSetMetaData resultSetMetaData, int colNumber)
			throws SQLException {
		if (resultSetMetaData.isReadOnly(colNumber))
			return true;

		// Hack access to protected member "fields" as we need to find the value
		// of Field.positionInTable which is 0 for columns based on SQL functions
		try {
			java.lang.reflect.Field fields_array = AbstractJdbc2ResultSetMetaData.class
					.getDeclaredField("fields");
			fields_array.setAccessible(true);
			final Field[] fields = (Field[]) fields_array.get(resultSetMetaData);
			return fields[colNumber - 1].getPositionInTable() == 0;
		} catch (Exception e) {
			throw new SQLException(e.toString());
		}
	}

	/**
	 * Sets sequence metadata for a column with the columns query result set. The column_default column will contain a
	 * string in the format nextval('sequence-name'::regclass), where sequence-name is the sequence name.
	 * 
	 * @throws SQLException when a database error occurs
	 */
	@Override
	protected void setSequenceMetaData(ColumnMetaDataImpl column, ResultSet resultSet) throws SQLException {
		final String columnDefault = resultSet.getString(3);
		if (columnDefault != null && columnDefault.startsWith("nextval")) {
			column.setSequence(true);
			column.setSequenceName(columnDefault.substring(9, columnDefault.indexOf('\'', 10)));
		}
	}
}
