<?php

/**
 * MIT License. This file is part of the Propel package.
 * For the full copyright and license information, please view the LICENSE
 * file that was distributed with this source code.
 */

namespace Propel\Generator\Reverse;

use PDO;
use Propel\Generator\Model\Column;
use Propel\Generator\Model\ColumnDefaultValue;
use Propel\Generator\Model\Database;
use Propel\Generator\Model\Domain;
use Propel\Generator\Model\ForeignKey;
use Propel\Generator\Model\Index;
use Propel\Generator\Model\PropelTypes;
use Propel\Generator\Model\Table;
use Propel\Generator\Model\Unique;
use Propel\Generator\Platform\MysqlPlatform;
use Propel\Runtime\Connection\ConnectionInterface;
use RuntimeException;

/**
 * Mysql database schema parser.
 *
 * @author Hans Lellelid <hans@xmpl.org>
 */
class MysqlSchemaParser extends AbstractSchemaParser
{
    /**
     * @var bool
     */
    private $addVendorInfo = false;

    /**
     * Map MySQL native types to Propel types.
     *
     * @var array<string>
     */
    private static $mysqlTypeMap = [
        'tinyint' => PropelTypes::TINYINT,
        'smallint' => PropelTypes::SMALLINT,
        'mediumint' => PropelTypes::SMALLINT,
        'int' => PropelTypes::INTEGER,
        'integer' => PropelTypes::INTEGER,
        'bigint' => PropelTypes::BIGINT,
        'int24' => PropelTypes::BIGINT,
        'real' => PropelTypes::DOUBLE,
        'float' => PropelTypes::FLOAT,
        'decimal' => PropelTypes::DECIMAL,
        'numeric' => PropelTypes::NUMERIC,
        'double' => PropelTypes::DOUBLE,
        'char' => PropelTypes::CHAR,
        'varchar' => PropelTypes::VARCHAR,
        'date' => PropelTypes::DATE,
        'time' => PropelTypes::TIME,
        'year' => PropelTypes::INTEGER,
        'datetime' => PropelTypes::DATETIME,
        'timestamp' => PropelTypes::TIMESTAMP,
        'tinyblob' => PropelTypes::BINARY,
        'blob' => PropelTypes::BLOB,
        'mediumblob' => PropelTypes::VARBINARY,
        'longblob' => PropelTypes::LONGVARBINARY,
        'tinytext' => PropelTypes::VARCHAR,
        'text' => PropelTypes::LONGVARCHAR,
        'mediumtext' => PropelTypes::LONGVARCHAR,
        'longtext' => PropelTypes::CLOB,
        'enum' => PropelTypes::CHAR,
        'set' => PropelTypes::CHAR,
        'binary' => PropelTypes::BINARY,
        'uuid' => PropelTypes::UUID, // for MariaDB
    ];

    /**
     * @var array<int>
     */
    protected static $defaultTypeSizes = [
        'char' => 1,
        'tinyint' => 4,
        'smallint' => 6,
        'int' => 11,
        'bigint' => 20,
        'decimal' => 10,
    ];

    /**
     * Gets a type mapping from native types to Propel types
     *
     * @return array<string>
     */
    #[\Override]
    protected function getTypeMapping(): array
    {
        return self::$mysqlTypeMap;
    }

    /**
     * @param \Propel\Runtime\Connection\ConnectionInterface|null $dbh Optional database connection
     */
    public function __construct(?ConnectionInterface $dbh = null)
    {
        parent::__construct($dbh);

        $this->setPlatform(new MysqlPlatform());
    }

    /**
     * @param \Propel\Generator\Model\Database $database
     * @param array<\Propel\Generator\Model\Table> $additionalTables
     *
     * @return int
     */
    #[\Override]
    public function parse(Database $database, array $additionalTables = []): int
    {
        if ($this->getGeneratorConfig() !== null) {
            $this->addVendorInfo = $this->getGeneratorConfig()->get()['migrations']['addVendorInfo'];
        }

        $this->parseTables($database);
        foreach ($additionalTables as $table) {
            $this->parseTables($database, $table);
        }

        // Now populate only columns.
        foreach ($database->getTables() as $table) {
            $this->addColumns($table);
        }

        // Now add indices and constraints.
        foreach ($database->getTables() as $table) {
            $this->addForeignKeys($table);
            $this->addIndexes($table);
            $this->addPrimaryKey($table);

            $this->addTableVendorInfo($table);
            $this->addDescriptionToTable($table);
            $this->addColumnDescriptionsToTable($table);
        }

        return count($database->getTables());
    }

    /**
     * @param \Propel\Generator\Model\Database $database
     * @param \Propel\Generator\Model\Table|null $filterTable
     *
     * @throws \RuntimeException
     *
     * @return void
     */
    protected function parseTables(Database $database, ?Table $filterTable = null): void
    {
        $sql = 'SHOW FULL TABLES';

        if ($filterTable) {
            $schema = $filterTable->getSchema();
            if ($schema) {
                $sql .= ' FROM ' . $database->getPlatform()->doQuoting($schema);
            }

            $sql .= sprintf(" LIKE '%s'", $filterTable->getCommonName());
        } else {
            $schema = $database->getSchema();
            if ($schema) {
                $sql .= ' FROM ' . $database->getPlatform()->doQuoting($schema);
            }
        }

        $dataFetcher = $this->dbh->query($sql);

        if ($dataFetcher === false) {
            throw new RuntimeException('PdoConnection::query() did not return a result set as a statement object.');
        }

        // First load the tables (important that this happens before filling out details of tables)
        foreach ($dataFetcher as $row) {
            $name = $row[0];
            $type = $row[1];

            if ($name == $this->getMigrationTable() || $type !== 'BASE TABLE') {
                continue;
            }

            $table = new Table($name);
            $table->setIdMethod($database->getDefaultIdMethod());
            if ($filterTable && $filterTable->getSchema()) {
                $table->setSchema($filterTable->getSchema());
            }
            $database->addTable($table);
        }
    }

    /**
     * Adds Columns to the specified table.
     *
     * @param \Propel\Generator\Model\Table $table The Table model class to add columns to.
     *
     * @return void
     */
    protected function addColumns(Table $table): void
    {
        /** @var \PDOStatement $stmt */
        $stmt = $this->dbh->query(sprintf('SHOW COLUMNS FROM %s', $this->getPlatform()->doQuoting($table->getName())));

        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $column = $this->getColumnFromRow($row, $table);
            $table->addColumn($column);
        }
    }

    /**
     * Factory method creating a Column object
     * based on a row from the 'show columns from ' MySQL query result.
     *
     * @param array $row An associative array with the following keys:
     *                     Field, Type, Null, Key, Default, Extra.
     * @param \Propel\Generator\Model\Table $table
     *
     * @return \Propel\Generator\Model\Column
     */
    public function getColumnFromRow(array $row, Table $table): Column
    {
        [
            'Field' => $columnName,
            'Type' => $type,
            'Null' => $null,
            // 'Key' => $key,
            'Default' => $default,
            'Extra' => $extra,
        ] = $row;

        $column = new Column($columnName);
        $column->setTable($table);

        $domain = $this->extractTypeDomain($type, $default, $column->getFullyQualifiedName(), $extra);
        $column->setDomain($domain);

        $autoincrement = (strpos($extra, 'auto_increment') !== false);
        $column->setAutoIncrement($autoincrement);

        $column->setNotNull($null === 'NO');

        if ($this->addVendorInfo) {
            $vi = $this->getNewVendorInfoObject($row);
            $column->addVendorInfo($vi);
        }

        return $column;
    }

    /**
     * @param string $typeDeclaration MySQL type declaration string (like "VARCHAR(16) CHARACTER SET utf8mb4", "INT UNSIGNED", etc)
     * @param string|null $defaultValueLiteral Default value declaration
     * @param string $columnName Used when printing warninga
     * @param string $extra Additional type specification (i.e. UNSIGNED)
     *
     * @return \Propel\Generator\Model\Domain
     */
    protected function extractTypeDomain(string $typeDeclaration, ?string $defaultValueLiteral, string $columnName, string $extra): Domain
    {
        [$nativeType, $sqlType, $size, $scale] = $this->parseType($typeDeclaration);

        $propelType = $this->getMappedPropelType($nativeType);
        if (!$propelType) {
            $propelType = Column::DEFAULT_TYPE;
            $sqlType = $typeDeclaration;
            $this->warn("Column [{$columnName}] has a column type ({$nativeType}) that Propel does not support.");
        }

        // Special case for TINYINT(1) which is a BOOLEAN
        if ($propelType === PropelTypes::TINYINT && $size === 1) {
            $propelType = PropelTypes::BOOLEAN;
        }

        $domain = clone $this->getPlatform()->getDomainForType($propelType);
        if ($sqlType) {
            $domain->replaceSqlType($sqlType);
        } elseif (in_array(strtoupper($nativeType), ['TINYTEXT', 'MEDIUMTEXT', 'TINYBLOB'], true)) {
            $domain->replaceSqlType(strtoupper($nativeType));
        }
        $domain->replaceSize($size);
        $domain->replaceScale($scale);

        $defaultValue = $this->extractDefaultValue($defaultValueLiteral, $propelType, $nativeType, $extra);
        if ($defaultValue) {
            $domain->setDefaultValue($defaultValue);
        }

        return $domain;
    }

    /**
     * Parse values from a MySQL type declaration string.
     *
     * @param string $typeDeclaration MySQL type declaration string (like "VARCHAR(16) CHARACTER SET utf8mb4", "INT UNSIGNED", etc)
     *
     * @return list{string, string|false, ?int, ?int} Array with the extracted values (type name, type declaration with parameters or false, size, precision)
     */
    protected function parseType(string $typeDeclaration): array
    {
        $size = null;
        $scale = null;
        $sqlType = false;

        $regexp = '/^
            (\w+)        # column type [1]
            [\(]         # (
                ?([\d,]*)  # size or size, precision [2]
            [\)]         # )
            ?\s*         # whitespace
            ([\w ]*)     # extra description (UNSIGNED, CHARACTER SET, ...) [3]
        $/x';
        if (preg_match($regexp, $typeDeclaration, $matches)) {
            $nativeType = $matches[1];
            if ($matches[2]) {
                $cpos = strpos($matches[2], ',');
                if ($cpos !== false) {
                    $size = (int)substr($matches[2], 0, $cpos);
                    $scale = (int)substr($matches[2], $cpos + 1);
                } else {
                    $size = (int)$matches[2];
                }
            }
            if ($matches[3]) {
                $sqlType = $typeDeclaration;
            }
            if (isset(static::$defaultTypeSizes[$nativeType]) && $scale == null && $size === static::$defaultTypeSizes[$nativeType]) {
                $size = null;
            }
        } elseif (preg_match('/^(\w+)\(/', $typeDeclaration, $matches)) {
            $nativeType = $matches[1];
            if ($nativeType === 'enum' || $nativeType === 'set') {
                $sqlType = $typeDeclaration;
            }
        } else {
            $nativeType = $typeDeclaration;
        }

        return [$nativeType, $sqlType, $size, $scale];
    }

    /**
     * @param string|null $parsedValue Default value declaration
     * @param string $propelType Column type indicator from \Propel\Generator\Model\PropelTypes
     * @param string $nativeType MySQL type name
     * @param string $extra Additional type specification (i.e. UNSIGNED)
     *
     * @return \Propel\Generator\Model\ColumnDefaultValue|null
     */
    protected function extractDefaultValue(?string $parsedValue, string $propelType, string $nativeType, string $extra): ?ColumnDefaultValue
    {
        // BLOBs can't have any default values in MySQL
        $isBlob = preg_match('~blob|text~', $nativeType);

        if ($parsedValue === null || $isBlob) {
            return null;
        }
        $default = $parsedValue;

        if ($propelType == PropelTypes::BOOLEAN) {
            if ($parsedValue == '1') {
                $default = 'true';
            }
            if ($parsedValue == '0') {
                $default = 'false';
            }
        }

        if (in_array($default, ['CURRENT_TIMESTAMP', 'current_timestamp()'], true)) {
            $default = 'CURRENT_TIMESTAMP';
            if (strpos(strtolower($extra), 'on update current_timestamp') !== false) {
                $default = 'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP';
            }
            $type = ColumnDefaultValue::TYPE_EXPR;
        } else {
            $type = ColumnDefaultValue::TYPE_VALUE;
        }

        return new ColumnDefaultValue($default, $type);
    }

    /**
     * Load and set table description.
     *
     * @param \Propel\Generator\Model\Table $table
     *
     * @return void
     */
    protected function addDescriptionToTable(Table $table): void
    {
        $tableDescription = $this->loadTableDescription($table);
        if ($tableDescription) {
            $table->setDescription($tableDescription);
        }
    }

    /**
     * Sets column descriptions according to source.
     *
     * @param \Propel\Generator\Model\Table $table
     *
     * @return void
     */
    protected function addColumnDescriptionsToTable(Table $table): void
    {
        foreach ($table->getColumns() as $column) {
            $columnDescription = $this->loadColumnDescription($column);
            if ($columnDescription) {
                $column->setDescription($columnDescription);
            }
        }
    }

    /**
     * Load a comment for this table.
     *
     * @param \Propel\Generator\Model\Table $table
     *
     * @throws \RuntimeException
     *
     * @return string|null
     */
    protected function loadTableDescription(Table $table): ?string
    {
        $tableName = $this->getPlatform()->quote($table->getName());
        $query = <<<EOT
SELECT table_comment
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema=DATABASE()
  AND table_name=($tableName)
EOT;

        $dataFetcher = $this->dbh->query($query);
        if ($dataFetcher === false) {
            throw new RuntimeException('PdoConnection::query() did not return a result set as a statement object.');
        }

        /** @phpstan-var string|null */
        return $dataFetcher->fetchColumn();
    }

    /**
     * Load a comment for this column.
     *
     * @param \Propel\Generator\Model\Column $column
     *
     * @throws \RuntimeException
     *
     * @return string|null
     */
    protected function loadColumnDescription(Column $column): ?string
    {
        $tableName = $this->getPlatform()->quote($column->getTableName());
        $columnName = $this->getPlatform()->quote($column->getName());
        $query = <<<EOT
SELECT column_comment
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema=DATABASE()
  AND table_name=($tableName)
  AND column_name=($columnName)
EOT;

        $dataFetcher = $this->dbh->query($query);
        if ($dataFetcher === false) {
            throw new RuntimeException('PdoConnection::query() did not return a result set as a statement object.');
        }

        /** @phpstan-var string|null */
        return $dataFetcher->fetchColumn();
    }

    /**
     * Load foreign keys for this table.
     *
     * @param \Propel\Generator\Model\Table $table
     *
     * @throws \RuntimeException
     *
     * @return void
     */
    protected function addForeignKeys(Table $table): void
    {
        $database = $table->getDatabase();

        $dataFetcher = $this->dbh->query(sprintf('SHOW CREATE TABLE %s', $this->getPlatform()->doQuoting($table->getName())));

        if ($dataFetcher === false) {
            throw new RuntimeException('PdoConnection::query() did not return a result set as a statement object.');
        }

        $row = $dataFetcher->fetch();
        $foreignKeys = []; // local store to avoid duplicates

        // Get the information on all the foreign keys
        $pattern = '/CONSTRAINT `([^`]+)` FOREIGN KEY \((.+)\) REFERENCES `([^\s]+)` \((.+)\)(.*)/';
        if (preg_match_all($pattern, $row[1], $matches)) {
            $tmpArray = array_keys($matches[0]);
            foreach ($tmpArray as $curKey) {
                $name = $matches[1][$curKey];
                $rawlcol = $matches[2][$curKey];
                $ftbl = str_replace('`', '', $matches[3][$curKey]);
                $rawfcol = $matches[4][$curKey];
                $fkey = $matches[5][$curKey];

                $lcols = [];
                $pieces = explode('`, `', $rawlcol);
                foreach ($pieces as $piece) {
                    $lcols[] = trim($piece, '` ');
                }

                $fcols = [];
                $pieces = explode('`, `', $rawfcol);
                foreach ($pieces as $piece) {
                    $fcols[] = trim($piece, '` ');
                }

                $fkactions = [
                    'ON DELETE' => null,
                    'ON UPDATE' => null,
                ];

                $availableActions = [ForeignKey::CASCADE, ForeignKey::SETNULL, ForeignKey::RESTRICT, ForeignKey::NOACTION];
                $pipedActionsString = implode('|', $availableActions);

                if ($fkey) {
                    // split foreign key information -> search for ON DELETE and afterwards for ON UPDATE action
                    foreach (array_keys($fkactions) as $fkaction) {
                        $result = null;
                        $regex = sprintf('/ %s (%s)/', $fkaction, $pipedActionsString);
                        preg_match($regex, $fkey, $result);
                        if ($result && is_array($result) && isset($result[1])) {
                            $fkactions[$fkaction] = $result[1];
                        }
                    }
                }

                $localColumns = [];
                $foreignColumns = [];
                if ($table->guessSchemaName() != $database->getSchema() && strpos($ftbl, $database->getPlatform()->getSchemaDelimiter()) === false) {
                    $ftbl = $table->guessSchemaName() . $database->getPlatform()->getSchemaDelimiter() . $ftbl;
                }

                $foreignTable = $database->getTable($ftbl, true);

                if (!$foreignTable) {
                    continue;
                }

                foreach ($fcols as $fcol) {
                    $foreignColumns[] = $foreignTable->getColumn($fcol);
                }
                foreach ($lcols as $lcol) {
                    $localColumns[] = $table->getColumn($lcol);
                }

                if (!isset($foreignKeys[$name])) {
                    $fk = new ForeignKey($name);
                    $fk->setForeignTableCommonName($foreignTable->getCommonName());
                    if ($table->guessSchemaName() != $foreignTable->guessSchemaName()) {
                        $fk->setForeignSchemaName($foreignTable->guessSchemaName());
                    }
                    $fk->setOnDelete($fkactions['ON DELETE']);
                    $fk->setOnUpdate($fkactions['ON UPDATE']);
                    $table->addForeignKey($fk);
                    $foreignKeys[$name] = $fk;
                }

                $max = count($localColumns);
                for ($i = 0; $i < $max; $i++) {
                    $foreignKeys[$name]->addReference($localColumns[$i], $foreignColumns[$i]);
                }
            }
        }
    }

    /**
     * Load indexes for this table
     *
     * @param \Propel\Generator\Model\Table $table
     *
     * @return void
     */
    protected function addIndexes(Table $table): void
    {
        /** @var \PDOStatement $stmt */
        $stmt = $this->dbh->query(sprintf('SHOW INDEX FROM %s', $this->getPlatform()->doQuoting($table->getName())));

        // Loop through the returned results, grouping the same key_name together
        // adding each column for that key.

        /** @var array<\Propel\Generator\Model\Index> $indexes */
        $indexes = [];
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $colName = $row['Column_name'];
            $colSize = $row['Sub_part'];
            $name = (string)$row['Key_name'];

            if ($name === 'PRIMARY') {
                continue;
            }

            if (!isset($indexes[$name])) {
                $isUnique = ($row['Non_unique'] == 0);
                if ($isUnique) {
                    $indexes[$name] = new Unique($name);
                } else {
                    $indexes[$name] = new Index($name);
                }
                if ($this->addVendorInfo) {
                    $vi = $this->getNewVendorInfoObject($row);
                    $indexes[$name]->addVendorInfo($vi);
                }
                $indexes[$name]->setTable($table);
            }

            $indexes[$name]->addColumn([
                'name' => $colName,
                'size' => $colSize,
            ]);
        }

        foreach ($indexes as $index) {
            if ($index instanceof Unique) {
                $table->addUnique($index);
            } else {
                $table->addIndex($index);
            }
        }
    }

    /**
     * Loads the primary key for this table.
     *
     * @param \Propel\Generator\Model\Table $table
     *
     * @return void
     */
    protected function addPrimaryKey(Table $table): void
    {
        /** @var \PDOStatement $stmt */
        $stmt = $this->dbh->query(sprintf('SHOW KEYS FROM %s', $this->getPlatform()->doQuoting($table->getName())));

        // Loop through the returned results, grouping the same key_name together
        // adding each column for that key.
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            // Skip any non-primary keys.
            if ($row['Key_name'] !== 'PRIMARY') {
                continue;
            }
            $name = $row['Column_name'];
            $column = $table->getColumn($name);
            if ($column) {
                $column->setPrimaryKey(true);
            }
        }
    }

    /**
     * Adds vendor-specific info for table.
     *
     * @param \Propel\Generator\Model\Table $table
     *
     * @return void
     */
    protected function addTableVendorInfo(Table $table): void
    {
        /** @var \PDOStatement $stmt */
        $stmt = $this->dbh->query("SHOW TABLE STATUS LIKE '" . $table->getName() . "'");
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$this->addVendorInfo) {
            // since we depend on `Engine` in the MysqlPlatform, we always have to extract this vendor information
            $row = ['Engine' => $row ? $row['Engine'] : null];
        }
        $vi = $this->getNewVendorInfoObject($row);
        $table->addVendorInfo($vi);
    }
}
