<?php
// Copyright (c) Pickware GmbH. All rights reserved.
// This file is part of software that is released under a proprietary license.
// You must not copy, modify, distribute, make publicly available, or execute
// its contents or parts thereof without express permission by the copyright
// holder, unless otherwise permitted by law.

namespace Shopware\Plugins\ViisonCommon\Classes\Installation;

use InvalidArgumentException;
use RuntimeException;
use UnexpectedValueException;

/**
 * Provides some idempotent database utility functions to manipulate database tables during installation and update
 * methods.
 */
class SQLHelper
{

    /**
     * @var \Zend_Db_Adapter_Abstract $db
     */
    private $db;

    public function __construct(\Zend_Db_Adapter_Abstract $db)
    {
        $this->db = $db;
    }

    public function addColumnIfNotExists($tableName, $columnName, $columnSpecification)
    {
        if ($this->doesColumnExist($tableName, $columnName)) {
            return;
        }
        $sql = 'ALTER TABLE ' . $this->db->quoteIdentifier($tableName)
            . ' ADD ' . $this->db->quoteIdentifier($columnName)
            . ' ' . $columnSpecification;
        $this->db->exec($sql);
    }

    /**
     * @deprecated Please use `addColumnIfNotExists` instead, because this method is not idempotent (it silently fails to create the column if $insertAfterColumnName does not exist).
     *
     * @param $tableName
     * @param $columnName
     * @param $columnSpecification
     * @param $insertAfterColumnName
     */
    public function addColumnIfNotExistsAfterColumnName($tableName, $columnName, $columnSpecification, $insertAfterColumnName)
    {
        if ($this->doesColumnExist($tableName, $columnName) || !$this->doesColumnExist($tableName, $insertAfterColumnName)) {
            return;
        }
        $sql = 'ALTER TABLE ' . $this->db->quoteIdentifier($tableName)
            . ' ADD ' . $this->db->quoteIdentifier($columnName)
            . ' ' . $columnSpecification . ' AFTER ' . $insertAfterColumnName;
        $this->db->exec($sql);
    }

    public function dropColumnIfExists($tableName, $columnName)
    {
        if ($this->doesColumnExist($tableName, $columnName)) {
            $sql = 'ALTER TABLE ' . $this->db->quoteIdentifier($tableName)
                . ' DROP COLUMN ' . $this->db->quoteIdentifier($columnName);
            $this->db->exec($sql);
        }
    }

    /**
     * @deprecated please don't use this method in plugins which no longer use schema tool, since it is not fully
     *             idempotency-compatible. Use self::ensureColumnIsRenamed when trying to rename a column or plain sql
     *             when trying to change the definition of a column: ALTER TABLE ... MODIFY COLUMN ...
     *
     * @param string $tableName
     * @param string $oldColumnName
     * @param string $newColumnName
     * @param string $columnSpecification
     */
    public function alterColumnIfExists($tableName, $oldColumnName, $newColumnName, $columnSpecification)
    {
        if ($this->doesColumnExist($tableName, $oldColumnName)) {
            $sql = 'ALTER TABLE ' . $this->db->quoteIdentifier($tableName) . '
                 CHANGE ' . $this->db->quoteIdentifier($oldColumnName) . ' ' . $this->db->quoteIdentifier($newColumnName) . ' ' . $columnSpecification;
            $this->db->exec($sql);
        }
    }

    /**
     * Renames a table column in a manner which is compatible with idempotent plugin migrations.
     *
     * Renaming is performed according to the following rules:
     *
     * - If a column with the new name already exists and a column with the old name also exists, the column with the
     *   old name is deleted. This is because running the install steps above the rename after the rename was already
     *   successful (which happens during a safe reinstall of the plugin) may end up creating the original column again.
     * - If the new column doesn't exist and the old column exists, the old column is renamed to the new column name.
     * - If neither the old, nor the new column exist there's nothing to rename and the method was called with incorrect
     *   arguments or at the wrong time, so a RuntimeException is thrown in this case.
     *
     * **Important:** Always use
     * \Shopware\Plugins\ViisonCommon\Classes\Installation\InstallationCheckpointWriter::commitCheckpointAtVersion() to
     * create a checkpoint for your current migration step before calling this method. This ensures that previous
     * migrations which rely on the old column names don't crash an update retry when updating across multiple versions.
     *
     * @see \Shopware\Plugins\ViisonCommon\Classes\Installation\InstallationCheckpointWriter::commitCheckpointAtVersion()
     *
     * @param string $tableName The name of the table containing the column to rename
     * @param string $oldColumnName The name of the column before it is renamed
     * @param string $newColumnName The name of the column after it is renamed
     * @param string $columnDefinition The column definition of the existing column (e.g. "varchar(255) NOT NULL")
     * @throws \Zend_Db_Adapter_Exception
     */
    public function ensureColumnIsRenamed($tableName, $oldColumnName, $newColumnName, $columnDefinition)
    {
        if (mb_strtolower($oldColumnName) === mb_strtolower($newColumnName)) {
            throw new \InvalidArgumentException(sprintf(
                'Parameter $oldColumnName and $newColumnName of method %s must not be equal.',
                __METHOD__
            ));
        }

        // Idempotency check
        if ($this->doesColumnExist($tableName, $newColumnName)) {
            // Rename was successfully completed before
            // => delete the column if it was re-created by running previous migration steps as part of a reinstall
            $this->dropColumnIfExists($tableName, $oldColumnName);

            // New column already exists - we're done
            return;
        }

        if (!$this->doesColumnExist($tableName, $oldColumnName)) {
            throw new RuntimeException(
                sprintf(
                    'The column %s.%s could not be renamed to %s because it doesn\'t exist.',
                    $tableName,
                    $oldColumnName,
                    $newColumnName
                )
            );
        }

        // Perform the actual rename
        $this->db->exec(
            sprintf(
                'ALTER TABLE %s CHANGE %s %s %s',
                $this->db->quoteIdentifier($tableName),
                $this->db->quoteIdentifier($oldColumnName),
                $this->db->quoteIdentifier($newColumnName),
                $columnDefinition
            )
        );
    }

    /**
     * Renames a table in a manner which is compatible with idempotent plugin migrations.
     *
     * Renaming is performed according to the following rules:
     *
     * - If a table with the new name already exists and a table with the old name also exists, the table with the
     *   old name is deleted. This is because running the install steps above the rename after the rename was already
     *   successful (which happens during a safe reinstall of the plugin) may end up creating the original table again.
     * - If the new table doesn't exist and the old table exists, the old table is renamed to the new table name.
     * - If neither the old, nor the new table exist there's nothing to rename and the method was called with incorrect
     *   arguments or at the wrong time, so a RuntimeException is thrown in this case.
     *
     * **Important:** Always use
     * \Shopware\Plugins\ViisonCommon\Classes\Installation\InstallationCheckpointWriter::commitCheckpointAtVersion() to
     * create a checkpoint for your current migration step before calling this method. This ensures that previous
     * migrations which rely on the old table names don't crash an update retry when updating across multiple versions.
     *
     * @see \Shopware\Plugins\ViisonCommon\Classes\Installation\InstallationCheckpointWriter::commitCheckpointAtVersion()
     *
     * @param $oldTableName
     * @param $newTableName
     */
    public function ensureTableIsRenamed($oldTableName, $newTableName)
    {
        // Idempotency check
        if ($this->doesTableExist($newTableName)) {
            // Rename was successfully completed before
            // => delete the table if it was re-created by running previous migration steps as part of a reinstall
            $this->db->query(
                sprintf(
                    'SET FOREIGN_KEY_CHECKS = 0;
                    DROP TABLE IF EXISTS %s;
                    SET FOREIGN_KEY_CHECKS = 1;',
                    $this->db->quoteIdentifier($oldTableName)
                )
            );

            // New table already exists - we're done
            return;
        }

        if (!$this->doesTableExist($oldTableName)) {
            throw new RuntimeException(
                sprintf(
                    'The table %s could not be renamed to %s because it doesn\'t exist.',
                    $oldTableName,
                    $newTableName
                )
            );
        }

        // Perform the actual rename
        $this->db->exec(
            sprintf(
                'RENAME TABLE %s TO %s',
                $oldTableName,
                $newTableName
            )
        );
    }

    /**
     * Create an index for the specified column(s) if an index with the same name doesn't already exist on that table.
     *
     * @param string $tableName
     * @param string $indexName
     * @param array $indexColumns an array of index columns. Specify TEXT/BLOB columns as nested arrays [columnName => indexLength]
     */
    public function addIndexIfNotExists($tableName, $indexName, $indexColumns)
    {
        if ($this->doesIndexExist($tableName, $indexName)) {
            return;
        }

        $db = $this->db;
        $columnsList = implode(
            ',',
            array_map(
                function ($column) use ($db) {
                    if (is_array($column)) {
                        // For blob columns, specifiying the index length is required
                        reset($column);
                        $key = key($column);
                        $indexLength = $column[$key];

                        return $db->quoteIdentifier($key) . '(' . $indexLength . ')';
                    }

                    return $db->quoteIdentifier($column);
                },
                $indexColumns
            )
        );
        $sql = 'ALTER TABLE ' . $db->quoteIdentifier($tableName) . ' ADD INDEX ' . $db->quoteIdentifier($indexName) . ' (' . $columnsList . ')';
        $this->db->exec($sql);
    }

    /**
     * Deletes an index with the specified name if it exists for the specified table.
     *
     * @param string $tableName
     * @param string $indexName
     */
    public function dropIndexIfExists($tableName, $indexName)
    {
        if (!$this->doesIndexExist($tableName, $indexName)) {
            return;
        }

        $db = $this->db;
        $sql = 'ALTER TABLE ' . $db->quoteIdentifier($tableName) . ' DROP INDEX ' . $db->quoteIdentifier($indexName);
        $this->db->exec($sql);
    }

    public function doesRowExist($tableName, array $columnNames, array $values)
    {
        return $this->countRows($tableName, $columnNames, $values) > 0;
    }

    public function countRows($tableName, array $columnNames, array $values)
    {
        if (empty($columnNames)) {
            throw new \Exception(sprintf('No columns given in call to insertIfNotExists (table %s)', $tableName));
        }
        if (empty($values)) {
            throw new \Exception(sprintf('No values given in call to insertIfNotExists (table %s)', $tableName));
        }

        if (count($columnNames) != count($values)) {
            throw new \Exception(sprintf('Wrong number of insert values when inserting into table %s (columns: [%s], values: [%s])', $tableName, implode(', ', $columnNames), implode(', ', $values)));
        }

        $conditions = [];
        for ($i = 0; $i < count($columnNames); $i++) {
            if ($values[$i] === null) {
                $conditions[] = $this->db->quoteIdentifier($columnNames[$i]) . ' IS NULL';
            } else {
                $conditions[] = $this->db->quoteInto($this->db->quoteIdentifier($columnNames[$i]) . '=?', $values[$i]);
            }
        }

        $sql = 'SELECT COUNT(*) FROM ' . $this->db->quoteIdentifier($tableName) . '
                WHERE ' . implode(' AND ', $conditions);

        return (integer)$this->db->fetchOne($sql);
    }

    /**
     * !!!DEPRECATED, DO NOT USE ANYMORE!!!
     *
     * Insert INTO helper for MySQL
     *
     * @deprecated this method's signature is confusing and its implementation is too complicated. Also, `ignoreColumns`
     * seems to be either badly documented or incorrect. Please use individual calls to `insertOrUpdateUniqueRow()`
     * below instead.
     *
     * @param string $tableName The table name where the values needs to be inserted
     * @param array $columnNames The specified column names
     * @param array $values The values for the column names
     * @param array|null $ignoreColumns Optional array if we want to ignore some insert values in specified columns, example Idempotent issue
     * @return int Number of inserted values
     * @throws \Exception
     */
    public function insertIfNotExists($tableName, array $columnNames, array $values, array $ignoreColumns = null)
    {
        if (empty($columnNames)) {
            throw new \Exception(sprintf('No columns given in call to insertIfNotExists (table %s)', $tableName));
        }
        if (empty($values)) {
            throw new \Exception(sprintf('No values given in call to insertIfNotExists (table %s)', $tableName));
        }
        // Makes values an array of arrays if it is not the case (so that we support both single and multiple inserts)
        if (!is_array($values[0])) {
            $values = [$values];
        }

        $numInsertedRows = 0;
        $db = $this->db;

        // Case if we need to Ignore some Columns by Insert Into statement
        if (!empty($ignoreColumns)) {
            $indexes = [];
            $quotedColumnNames = array_filter(
                $columnNames,
                function ($columnName) use ($db, $ignoreColumns, &$indexes, $columnNames) {
                    if (!in_array($columnName, $ignoreColumns)) {
                        // Quote column name
                        return $db->quoteIdentifier($columnName);
                    } else {
                        // If column needs to be ignored save index so we can
                        // delete the insert value from $values array
                        $indexes[] = array_search($columnName, $columnNames);
                    }
                }
            );

            // Remove all columns from columnNames array
            $columnNames = array_diff($columnNames, $ignoreColumns);
            foreach ($values as &$singleValue) {
                foreach ($indexes as $index) {
                    // Remove column values from specified index
                    array_splice($singleValue, $index, 1);
                }
            }
        } else {
            $quotedColumnNames = array_map(function ($columnName) use ($db) {
                return $db->quoteIdentifier($columnName);
            }, $columnNames);
        }
        foreach ($values as $singleInsertValues) {
            if (count($columnNames) != count($singleInsertValues)) {
                throw new \Exception(sprintf('Wrong number of insert values when inserting into table %s (columns: [%s], values: [%s])', $tableName, implode(', ', $columnNames), implode(', ', $singleInsertValues)));
            }

            if (!$this->doesRowExist($tableName, $columnNames, $singleInsertValues)) {
                $sql = 'INSERT INTO ' . $this->db->quoteIdentifier($tableName) . '(' . implode(',', $quotedColumnNames) . ')
                    VALUES (' .
                    implode(
                        ',',
                        array_map(
                            function () {
                                return '?';
                            },
                            $singleInsertValues
                        )
                    ) . ')';
                $this->db->query($sql, $singleInsertValues);
                $numInsertedRows++;
            }
        }

        return $numInsertedRows;
    }

    /**
     * Insert or update a single, uniquely identified row in a table.
     *
     * @param string $tableName the table to operate on
     * @param array $keyColumns a dict of column names => values that uniquely identify the table
     * @param array $nonKeyColumns a dict of additional column names => values that should be set for the row, but are
     *                             not used to identify the row
     * @return int the number of modified rows (which is always 1)
     */
    public function insertOrUpdateUniqueRow($tableName, array $keyColumns, array $nonKeyColumns = [])
    {
        if (empty($keyColumns)) {
            throw new InvalidArgumentException(
                sprintf('No key columns given in call to insertOrUpdateUniqueRow (table %s)', $tableName)
            );
        }

        $rows = $this->countRows($tableName, array_keys($keyColumns), array_values($keyColumns));
        if ($rows > 1) {
            throw new UnexpectedValueException(
                sprintf(
                    'insertOrUpdateUniqueRow failed: Row not unique (table %s, key %s)',
                    $tableName,
                    json_encode($keyColumns)
                )
            );
        }

        $db = $this->db;
        if ($rows === 0) {
            $quotedColumnNames = array_map(function ($columnName) use ($db) {
                return $db->quoteIdentifier($columnName);
            }, array_merge(array_keys($keyColumns), array_keys($nonKeyColumns)));
            $valuePlaceholders = array_map(function () {
                return '?';
            }, array_merge(array_values($keyColumns), array_values($nonKeyColumns)));
            $sql = 'INSERT INTO ' . $db->quoteIdentifier($tableName) . '(' . implode(',', $quotedColumnNames) . ')
                    VALUES (' . implode(',', $valuePlaceholders) . ')';
            $this->db->query($sql, array_merge(array_values($keyColumns), array_values($nonKeyColumns)));
        } else {
            $makeColumnAssignment = function ($columnName) use ($db) {
                return $db->quoteIdentifier($columnName) . '=?';
            };
            $nonKeyColumnAssignments = array_map($makeColumnAssignment, array_keys($nonKeyColumns));
            $keyColumnPredicates = array_map($makeColumnAssignment, array_keys($keyColumns));
            $sql = 'UPDATE ' . $db->quoteIdentifier($tableName) . 'SET ' . implode(',', $nonKeyColumnAssignments) . '
                    WHERE ' . implode(' AND ', $keyColumnPredicates);
            $this->db->query($sql, array_merge(array_values($nonKeyColumns), array_values($keyColumns)));
        }

        return 1;
    }

    public function doesConstraintExist($tableName, $constraintName, $constraintType)
    {
        $hasConstraint = $this->db->fetchOne(
            'SELECT COUNT(CONSTRAINT_NAME)
            FROM information_schema.TABLE_CONSTRAINTS
            WHERE CONSTRAINT_SCHEMA = (SELECT DATABASE())
            AND TABLE_NAME = :tableName
            AND CONSTRAINT_NAME = :constraintName
            AND CONSTRAINT_TYPE = :constraintType',
            [
                'tableName' => $tableName,
                'constraintName' => $constraintName,
                'constraintType' => $constraintType,
            ]
        );

        return $hasConstraint !== '0';
    }

    public function doesIndexExist($tableName, $indexName)
    {
        $hasIndex = $this->db->fetchOne(
            'SELECT COUNT(1) indexExists
            FROM information_schema.STATISTICS
            WHERE table_schema = (SELECT DATABASE())
            AND TABLE_NAME = :tableName
            AND INDEX_NAME = :indexName',
            [
                'tableName' => $tableName,
                'indexName' => $indexName,
            ]
        );

        return $hasIndex !== '0';
    }

    public function doesColumnExist($tableName, $columnName)
    {
        $hasColumn = $this->db->fetchOne(
            'SELECT COUNT(COLUMN_NAME)
            FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = (SELECT DATABASE())
            AND TABLE_NAME = :tableName
            AND COLUMN_NAME = :columnName',
            [
                'tableName' => $tableName,
                'columnName' => $columnName,
            ]
        );

        return $hasColumn === '1';
    }

    /**
     * Makes sure that none of the values in the column with the given $columnName in the table with the
     * givne $tableName are NULL, by setting respective entries to the given $default value. Finally adds
     * a NOT NOLL constraint on the same column.
     *
     * @param string $tableName
     * @param string $columnName
     * @param string|int $default
     */
    public function addNotNullConstraint($tableName, $columnName, $default)
    {
        if (!$this->doesColumnExist($tableName, $columnName)) {
            return;
        }

        // Remove all NULL values from the columnName
        $this->db->exec(
            'UPDATE ' . $this->db->quoteIdentifier($tableName) . ' SET ' . $this->db->quoteIdentifier($columnName) . ' = ' . $default . ' WHERE ' . $columnName . ' IS NULL'
        );

        // Add constraint
        $columnType = $this->getColumnType($tableName, $columnName);
        $this->db->exec(
            'ALTER TABLE ' . $this->db->quoteIdentifier($tableName) . ' MODIFY COLUMN ' . $this->db->quoteIdentifier($columnName) . ' ' . $columnType . ' DEFAULT ' . $default . ' NOT NULL'
        );
    }

    /**
     * Removes any NOT NOLL constraints from the column with the given $columnName name in the table
     * having $tableName.
     *
     * @param string $tableName
     * @param string $columnName
     */
    public function removeNotNullConstraint($tableName, $columnName)
    {
        if (!$this->doesColumnExist($tableName, $columnName)) {
            return;
        }

        $columnType = $this->getColumnType($tableName, $columnName);
        $this->db->exec(
            'ALTER TABLE ' . $this->db->quoteIdentifier($tableName) . ' MODIFY COLUMN ' . $this->db->quoteIdentifier($columnName) . ' ' . $columnType . ' NULL'
        );
    }

    /**
     * Gets the type of the column with the given $columnName in the table with the the given
     * $tableName from the information schema and returns it.
     *
     * @param string $tableName
     * @param string $columnName
     * @return string|null
     */
    public function getColumnType($tableName, $columnName)
    {
        $dbConfig = $this->db->getConfig();
        $columnType = $this->db->fetchOne(
            'SELECT COLUMN_TYPE
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = :dbName
            AND TABLE_NAME = :tableName
            AND COLUMN_NAME = :columnName',
            [
                'dbName' => $dbConfig['dbname'],
                'tableName' => $tableName,
                'columnName' => $columnName,
            ]
        );
        // The integer display widths do not matter for the column type and also have been removed with MySQL 8.0.19.
        // Strip them here if they are present.
        $columnType = preg_replace('/^(smallint|mediumint|int|bigint)\\(\\d+\\)/', '$1', $columnType);

        return $columnType;
    }

    /**
     * @deprecated use ensureForeignKeyConstraint instead.
     * @param $referencingTableName
     * @param $referencingColumnName
     * @param $targetTableName
     * @param $targetColumnName
     * @param null $onDeleteAction
     * @param null $onUpdateAction
     */
    public function addForeignKeyConstraint(
        $referencingTableName,
        $referencingColumnName,
        $targetTableName,
        $targetColumnName,
        $onDeleteAction = null,
        $onUpdateAction = null
    ) {
        $this->ensureForeignKeyConstraint(
            $referencingTableName,
            $referencingColumnName,
            $targetTableName,
            $targetColumnName,
            $onDeleteAction,
            $onUpdateAction
        );
    }

    /**
     * Adds a foreign key constraint to a table with an action.
     * Actions ($onDeleteAction, $onUpdateAction) must be a string of type
     * ['NO ACTION', 'CASCADE', 'SET NULL', 'SET DEFAULT'] or can be left null
     * (which converts to a 'RESTRICT' constraint)
     * If such a constraint already exists, it will be removed and added anew with
     * actions given by the current parameter (i.e. overrides an existing foreign key)
     *
     * Remark: Always check the table before you add a new constraint if there are rows that
     * would violate the constraint. If it's the case, the addition of the constraint wil fail
     * with an SQL error.
     *
     * You probably want to use one of the `cleanUpAndEnsure*` methods below instead of calling this method directly.
     *
     * @param string $referencingTableName
     * @param string $referencingColumnName
     * @param string $targetTableName
     * @param string $targetColumnName
     * @param string|null $onDeleteAction
     * @param string|null $onUpdateAction
     * @throws \Exception If the referencing and target column types do not match.
     */
    public function ensureForeignKeyConstraint(
        $referencingTableName,
        $referencingColumnName,
        $targetTableName,
        $targetColumnName,
        $onDeleteAction = null,
        $onUpdateAction = null
    ) {
        if ($this->getColumnType($referencingTableName, $referencingColumnName) !== $this->getColumnType($targetTableName, $targetColumnName)) {
            throw new \Exception(sprintf(
                'Column type for foreign key constraint do not match! ("%s" and "%s")',
                $this->getColumnType($referencingTableName, $referencingColumnName),
                $this->getColumnType($targetTableName, $targetColumnName)
            ));
        }

        // Remove constraint beforehand
        $this->dropForeignKeyConstraintIfExists(
            $referencingTableName,
            $referencingColumnName,
            $targetTableName,
            $targetColumnName
        );

        // Add new foreign key
        $sql = 'ALTER TABLE ' . $referencingTableName . '
                ADD CONSTRAINT ' . $this->getConstraintIdentifier($referencingTableName, $referencingColumnName, 'fk') . '
                FOREIGN KEY (' . $referencingColumnName . ')
                REFERENCES ' . $targetTableName . ' (' . $targetColumnName . ')';
        if ($onDeleteAction) {
            $sql .= ' ON DELETE ' . $onDeleteAction;
        }
        if ($onUpdateAction) {
            $sql .= ' ON UPDATE ' . $onUpdateAction;
        }

        $this->db->exec($sql);
    }

    /**
     * Cleans up the database table having the `$referencingTableName` by deleting all rows that are not associated to
     * a row in the table named `$targetTableName`.
     *
     * @param string $referencingTableName
     * @param string $referencingColumnName
     * @param string $targetTableName
     * @param string $targetColumnName
     */
    public function cleanUpForRestrictingOrCascadingForeignKeyConstraint(
        $referencingTableName,
        $referencingColumnName,
        $targetTableName,
        $targetColumnName
    ) {
        $sqlTemplate = '
            DELETE `referencingTable`
            FROM `%1$s` AS `referencingTable`
            LEFT JOIN `%3$s` AS `targetTable`
                ON `targetTable`.`%4$s` = `referencingTable`.`%2$s`
            WHERE `targetTable`.`%4$s` IS NULL';
        $sql = sprintf(
            $sqlTemplate,
            $referencingTableName,
            $referencingColumnName,
            $targetTableName,
            $targetColumnName
        );
        $this->db->query($sql);
    }

    /**
     * Cleans up the database table having the `$referencingTableName` before adding a restricting foreign key
     * constraint to it, that references the table named `$targetTableName`.
     *
     * @param string $referencingTableName
     * @param string $referencingColumnName
     * @param string $targetTableName
     * @param string $targetColumnName
     */
    public function cleanUpAndEnsureRestrictingForeignKeyConstraint(
        $referencingTableName,
        $referencingColumnName,
        $targetTableName,
        $targetColumnName
    ) {
        $this->cleanUpForRestrictingOrCascadingForeignKeyConstraint(
            $referencingTableName,
            $referencingColumnName,
            $targetTableName,
            $targetColumnName
        );
        $this->ensureForeignKeyConstraint(
            $referencingTableName,
            $referencingColumnName,
            $targetTableName,
            $targetColumnName
        );
    }

    /**
     * Cleans up the database table having the `$referencingTableName` before adding a cascading foreign key constraint
     * to it, that references the table named `$targetTableName`.
     *
     * @param string $referencingTableName
     * @param string $referencingColumnName
     * @param string $targetTableName
     * @param string $targetColumnName
     */
    public function cleanUpAndEnsureCascadingForeignKeyConstraint(
        $referencingTableName,
        $referencingColumnName,
        $targetTableName,
        $targetColumnName
    ) {
        $this->cleanUpForRestrictingOrCascadingForeignKeyConstraint(
            $referencingTableName,
            $referencingColumnName,
            $targetTableName,
            $targetColumnName
        );
        $this->ensureForeignKeyConstraint(
            $referencingTableName,
            $referencingColumnName,
            $targetTableName,
            $targetColumnName,
            'CASCADE'
        );
    }

    /**
     * Cleans up the database table having the `$referencingTableName` by update all rows that are not associated to
     * a row in the table named `$targetTableName`.
     *
     * @param string $referencingTableName
     * @param string $referencingColumnName
     * @param string $targetTableName
     * @param string $targetColumnName
     */
    public function cleanUpForNullingForeignKeyConstraint(
        $referencingTableName,
        $referencingColumnName,
        $targetTableName,
        $targetColumnName
    ) {
        $sqlTemplate = '
            UPDATE `%1$s` AS `referencingTable`
            LEFT JOIN `%3$s` AS `targetTable`
                ON `targetTable`.`%4$s` = `referencingTable`.`%2$s`
            SET `referencingTable`.`%2$s` = NULL
            WHERE `targetTable`.`%4$s` IS NULL';
        $sql = sprintf(
            $sqlTemplate,
            $referencingTableName,
            $referencingColumnName,
            $targetTableName,
            $targetColumnName
        );
        $this->db->query($sql);
    }

    /**
     * Cleans up the database table having the `$referencingTableName` before adding a nulling (SET NULL) foreign key
     * constraint to it, that references the table named `$targetTableName`.
     *
     * @param string $referencingTableName
     * @param string $referencingColumnName
     * @param string $targetTableName
     * @param string $targetColumnName
     */
    public function cleanUpAndEnsureNullingForeignKeyConstraint(
        $referencingTableName,
        $referencingColumnName,
        $targetTableName,
        $targetColumnName
    ) {
        $this->cleanUpForNullingForeignKeyConstraint(
            $referencingTableName,
            $referencingColumnName,
            $targetTableName,
            $targetColumnName
        );
        $this->ensureForeignKeyConstraint(
            $referencingTableName,
            $referencingColumnName,
            $targetTableName,
            $targetColumnName,
            'SET NULL'
        );
    }

    /**
     * Removes a foreign key constraint for a given table and column (if it exists).
     *
     * @deprecated 3.40.0 Use {@link self::dropForeignKeyConstraintIfExists()} instead!
     *
     * @param string $referencingTableName
     * @param string $referencingColumnName
     */
    public function removeForeignKeyConstraintIfExists(
        $referencingTableName,
        $referencingColumnName
    ) {
        $foreignKey = $this->getConstraintIdentifier($referencingTableName, $referencingColumnName, 'fk');
        // Check if FK exists
        $keyExists = $this->db->fetchOne(
            'SELECT COUNT(*)
            FROM information_schema.TABLE_CONSTRAINTS
            WHERE CONSTRAINT_SCHEMA = (SELECT DATABASE())
            AND CONSTRAINT_NAME = :foreignKeyName
            AND TABLE_NAME = :tableName',
            [
                'foreignKeyName' => $foreignKey,
                'tableName' => $referencingTableName,
            ]
        );

        if ($keyExists == 1) {
            // Remove constraint
            $sql = 'ALTER TABLE ' . $referencingTableName . ' DROP FOREIGN KEY ' . $foreignKey . ';';
            $this->db->exec($sql);
        }
    }

    /**
     * Drops the foreign key constraint matching the given table and column names, if it exists. Note: This method
     * identifies the foreign key constraint solely based on the table and column names, not by matching the name of the
     * foreign key constraint to a name constructed from those table and column names.
     *
     * @param string $tableName
     * @param string $columnName
     * @param string $referencedTableName
     * @param string $referencedColumnName
     */
    public function dropForeignKeyConstraintIfExists(
        $tableName,
        $columnName,
        $referencedTableName,
        $referencedColumnName
    ) {
        // Check if a matching foreign key constraint exists
        $constraintName = $this->db->fetchOne(
            'SELECT `CONSTRAINT_NAME`
            FROM `information_schema`.`KEY_COLUMN_USAGE`
            WHERE
                `TABLE_SCHEMA` = (SELECT DATABASE())
                AND `TABLE_NAME` = :tableName
                AND `COLUMN_NAME` = :columnName
                AND `REFERENCED_TABLE_SCHEMA` = `TABLE_SCHEMA`
                AND `REFERENCED_TABLE_NAME` = :referencedTableName
                AND `REFERENCED_COLUMN_NAME` = :referencedColumnName',
            [
                'tableName' => $tableName,
                'columnName' => $columnName,
                'referencedTableName' => $referencedTableName,
                'referencedColumnName' => $referencedColumnName,
            ]
        );
        if (!$constraintName) {
            return;
        }

        // Remove the constraint
        $this->db->query(sprintf(
            'ALTER TABLE `%1$s` DROP FOREIGN KEY `%2$s`',
            $tableName,
            $constraintName
        ));
    }

    /**
     * Constructs a constraint name by concatenating the given $columnNames and checks the table with the given
     * $tableName for a constraint with that name. If no such constraint exists, a new UNIQUE constraint is created
     * for the given $columnNames.
     *
     * @param string $tableName
     * @param string[] $columnNames
     */
    public function addUniqueConstraintIfNotExists($tableName, array $columnNames)
    {
        // Check for existing unique key constraint with the same name
        $constraintName = implode('_', $columnNames);
        if ($this->doesConstraintExist($tableName, $constraintName, 'UNIQUE')) {
            return;
        }

        // Add a new unique key constraint
        $db = $this->db;
        $quotedColumnNames = array_map(function ($name) use ($db) {
            return $db->quoteIdentifier($name);
        }, $columnNames);
        $sql = 'ALTER TABLE ' . $this->db->quoteIdentifier($tableName)
            . ' ADD CONSTRAINT ' . $this->db->quoteIdentifier($constraintName)
            . ' UNIQUE (' . implode(', ', $quotedColumnNames) . ')';
        $this->db->exec($sql);
    }

    /**
     * Generates the constraint identifier for the given $tableName and $columnName. The identifier is created the same
     * way as in doctrine's schema creator.
     *
     * @param string $tableName
     * @param string $columnName
     * @param string $prefix
     * @return string
     */
    public function getConstraintIdentifier($tableName, $columnName, $prefix)
    {
        $hash = implode(
            '',
            array_map(
                function ($column) {
                    return dechex(crc32($column));
                },
                [
                    $tableName,
                    $columnName,
                ]
            )
        );

        return mb_substr(mb_strtoupper($prefix . '_' . $hash), 0, 30);
    }

    /**
     * @param string $tableName
     * @return bool true, iff the table with the given $tableName exists in the current schema. Otherwise false.
     */
    public function doesTableExist($tableName)
    {
        $foundTables = $this->db->fetchOne(
            'SELECT COUNT(*)
            FROM information_schema.TABLES
            WHERE TABLE_SCHEMA = (SELECT DATABASE())
            AND TABLE_NAME = :tableName',
            [
                'tableName' => $tableName,
            ]
        );

        return intval($foundTables) > 0;
    }

    /**
     * @param string $tableName
     * @param string $columnName
     * @return bool
     */
    public function isColumnNullable($tableName, $columnName)
    {
        $isNullable = $this->db->fetchOne(
            'SELECT IS_NULLABLE
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_SCHEMA = (SELECT DATABASE())
                AND TABLE_NAME = :tableName
                AND COLUMN_NAME = :columnName',
            [
                'tableName' => $tableName,
                'columnName' => $columnName,
            ]
        );

        return $isNullable === 'YES';
    }
}
