/*-
 * #%L
 * HAPI FHIR JPA Server Test Utilities
 * %%
 * Copyright (C) 2014 - 2023 Smile CDR, Inc.
 * %%
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 * #L%
 */
package ca.uhn.fhir.jpa.embedded;

import com.google.common.collect.HashMultimap;
import com.google.common.collect.Multimap;
import org.intellij.lang.annotations.Language;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
import javax.sql.DataSource;

import static org.junit.jupiter.api.Assertions.assertTrue;

/**
 * This test class is used to verify all foreign key constraints
 * are indexed as well.
 * --
 * We use postgres to do this, because H2 automatically adds
 * indexes to fk contraints. But Oracle, Postgres, etc do not.
 * And Oracle will have deadlocks if there are unindexed FKs
 * --
 * It should be noted that because of this restriction,
 * Indexes on columns that are foreign keys must have the
 * exact same name as their FK constraint name.
 * ---
 * If while running this test, you get the error
 * "Could not find a valid docker environment"
 * and you have docker running, the issue
 * is permissions.
 * See <a href="https://stackoverflow.com/questions/61108655/test-container-test-cases-are-failing-due-to-could-not-find-a-valid-docker-envi">...</a>
 */
public class HapiForeignKeyIndexHelper {

	@Language("SQL")
	private static final String FK_QUERY =
			"""
		SELECT c.conrelid::regclass AS "table",
				/* list of key column names in order */
				string_agg(a.attname, ',' ORDER BY x.n) AS columns,
				pg_catalog.pg_size_pretty(
					pg_catalog.pg_relation_size(c.conrelid)
				) AS size,
				c.conname AS constraint,
				c.confrelid::regclass AS referenced_table
		FROM pg_catalog.pg_constraint c
			/* enumerated key column numbers per foreign key */
			CROSS JOIN LATERAL
				unnest(c.conkey) WITH ORDINALITY AS x(attnum, n)
			/* name for each key column */
			JOIN pg_catalog.pg_attribute a
				ON a.attnum = x.attnum
					AND a.attrelid = c.conrelid
		WHERE NOT EXISTS
				/* is there a matching index for the constraint? */
				(SELECT 1 FROM pg_catalog.pg_index i
					WHERE i.indrelid = c.conrelid
					/* it must not be a partial index */
					AND i.indpred IS NULL
					/* the first index columns must be the same as the
						key columns, but order doesn't matter */
					AND (i.indkey::smallint[])[0:cardinality(c.conkey)-1]
							OPERATOR(pg_catalog.@>) c.conkey)
		AND c.contype = 'f'
		GROUP BY c.conrelid, c.conname, c.confrelid
		ORDER BY pg_catalog.pg_relation_size(c.conrelid) DESC;
	""";

	private static final Logger ourLog = LoggerFactory.getLogger(HapiForeignKeyIndexHelper.class);

	protected static final Multimap<String, String> ourTableToColumnsWhitelist = HashMultimap.create();

	public HapiForeignKeyIndexHelper() {
		populateWhiteList();
	}

	/**
	 * This method populates a whitelist of table name -> column name
	 * for foreign key constraints that do not have proper indexes.
	 * --
	 * Any whitelisted table:column pairing should be documented why it
	 * doesn't require indexing, or be provided an explicit index.
	 */
	protected void populateWhiteList() {
		// HFJ_BLK_EXPORT_COLFILE - deprecated table
		ourTableToColumnsWhitelist.put("HFJ_BLK_EXPORT_COLFILE", "COLLECTION_PID");

		// HFJ_BLK_EXPORT_COLLECTION - deprecated table
		ourTableToColumnsWhitelist.put("HFJ_BLK_EXPORT_COLLECTION", "JOB_PID");

		// TODO - LS - entries below here require further investigation
		// MPI_LINK_AID - autogenerated table
		ourTableToColumnsWhitelist.put("MPI_LINK_AUD", "REV");

		// hfj_history_tag - tag_id - do we ever delete by tag history by tag_id (or at all)?
		ourTableToColumnsWhitelist.put("HFJ_HISTORY_TAG", "TAG_ID");
	}

	public void ensureAllForeignKeysAreIndexed(DataSource theDataSource) throws SQLException {
		try (Connection connection = theDataSource.getConnection()) {
			try (Statement statement = connection.createStatement()) {
				ResultSet results = statement.executeQuery(FK_QUERY);

				while (results.next()) {
					String tableName = results.getString("table").toUpperCase();
					String columns = results.getString("columns").toUpperCase();
					String constraint = results.getString("constraint").toUpperCase();
					String referenced_table =
							results.getString("referenced_table").toUpperCase();

					ourLog.warn(String.format(
							"Table %s, Columns %s, Constraint %s, Referenced Table %s",
							tableName, columns, constraint, referenced_table));

					Collection<String> whiteListColumns = ourTableToColumnsWhitelist.get(tableName);
					boolean isWhiteListed = whiteListColumns.contains(columns);

					assertTrue(
							isWhiteListed,
							String.format(
									"Unindexed foreign key detected! Table.column: %s.%s. Constraint: %s",
									tableName, columns, constraint));
				}
			}
		}
	}
}
