<?php

declare(strict_types=1);

namespace PpPhp\Core\Database\Dialect;

/**
 * Oracle Database adapter for PDO_OCI connections.
 *
 * Oracle differs from the other engines in several load-bearing ways, and each is
 * handled locally in this file so the other dialects can never be affected:
 *
 *   • Identifiers are double-quoted and case-sensitive. We quote everything, so a
 *     lowercase `"orders"` is created and read back as lowercase.
 *   • There is no AUTO_INCREMENT keyword. Auto keys use 12c+
 *     `GENERATED BY DEFAULT ON NULL AS IDENTITY` on a NUMBER column.
 *   • There is no BOOLEAN column type before 23c, and no engine-native JSON type
 *     before 21c — so BOOLEAN maps to NUMBER(1) + a CHECK, and JSON to CLOB +
 *     `IS JSON`, both of which work from 12c onward.
 *   • Foreign keys support ON DELETE {CASCADE|SET NULL} only — never ON UPDATE,
 *     RESTRICT or SET DEFAULT — so those are stripped rather than emitted.
 *   • There is no `DROP … IF EXISTS`; a PL/SQL block swallows "does not exist".
 *   • Constraint names are capped to 30 chars for pre-12.2 compatibility.
 *
 * The SQL-generation methods (types, columns, foreign keys, quoting) are fully
 * covered by tests/tables-dialect-adapters-integration-test.php. The live
 * introspection / ALTER paths follow the documented Oracle data-dictionary views
 * (USER_TAB_COLUMNS, USER_CONSTRAINTS, …) and should be smoke-tested against a
 * real Oracle instance before a first production deploy.
 */
final class OracleDialect extends AbstractDatabaseDialect
{
    public function id(): string { return 'oci'; }

    public function label(): string { return 'Oracle Database'; }

    public function quoteIdentifier(string $identifier): string
    {
        return '"' . str_replace('"', '""', $identifier) . '"';
    }

    protected function fieldTypes(): array
    {
        return [
            'VARCHAR2', 'NVARCHAR2', 'CHAR', 'NCHAR', 'CLOB', 'NCLOB',
            'NUMBER', 'FLOAT', 'BINARY_DOUBLE', 'DATE', 'TIMESTAMP',
            'BLOB', 'RAW', 'JSON', 'BOOLEAN',
        ];
    }

    protected function integerTypes(): array
    {
        // Oracle expresses whole numbers as NUMBER(p,0); the app validates integer
        // defaults against this set. NUMBER is treated as numeric (see literal()).
        return ['INTEGER'];
    }

    protected function sizedTypes(): array
    {
        return ['VARCHAR2', 'NVARCHAR2', 'CHAR', 'NCHAR', 'NUMBER', 'RAW'];
    }

    protected function noLengthTypes(): array
    {
        return ['CLOB', 'NCLOB', 'FLOAT', 'BINARY_DOUBLE', 'DATE', 'TIMESTAMP', 'BLOB', 'JSON', 'BOOLEAN'];
    }

    protected function temporalTypes(): array
    {
        return ['DATE', 'TIMESTAMP'];
    }

    protected function referentialActions(): array
    {
        // Oracle enforces ON DELETE {CASCADE|SET NULL} only. NO ACTION is the
        // default (emitted as no clause); there is no ON UPDATE, RESTRICT or
        // SET DEFAULT.
        return ['CASCADE', 'SET NULL', 'NO ACTION'];
    }

    protected function capabilities(): array
    {
        $c = parent::capabilities();
        $c['replace_views'] = true;                       // CREATE OR REPLACE VIEW
        $c['identity_change_requires_rebuild'] = true;    // cannot toggle IDENTITY in place
        return $c;
    }

    protected function typeSql(array $field): string
    {
        $type = strtoupper((string)$field['type']);
        $size = max(1, (int)$field['size']);
        $scale = max(0, (int)$field['scale']);
        return match ($type) {
            'VARCHAR2', 'NVARCHAR2', 'CHAR', 'NCHAR', 'RAW' => $type . '(' . $size . ')',
            'NUMBER' => (int)$field['size'] > 0
                ? 'NUMBER(' . $size . ($scale > 0 ? ', ' . $scale : '') . ')'
                : 'NUMBER',
            'INTEGER' => 'NUMBER(19)',
            'BOOLEAN' => 'NUMBER(1)',
            'JSON' => 'CLOB',
            'FLOAT' => 'BINARY_DOUBLE',
            default => $type,
        };
    }

    protected function autoIncrementSql(array $field, bool $forAlter): string
    {
        return !empty($field['auto_increment']) ? ' GENERATED BY DEFAULT ON NULL AS IDENTITY' : '';
    }

    protected function compileColumn(array $field, bool $forAlter): string
    {
        // Identity columns are implicitly NOT NULL and reject an explicit
        // nullability clause or DEFAULT, so build them specially.
        if (!empty($field['auto_increment'])) {
            $sql = $this->quoteIdentifier((string)$field['name']) . ' ' . $this->typeSql($field)
                . $this->autoIncrementSql($field, $forAlter);
            if ($this->primaryKeyIsInline($field)) {
                $sql .= ' PRIMARY KEY';
            }
            return $sql;
        }
        return parent::compileColumn($field, $forAlter);
    }

    protected function currentTimestampSql(): string
    {
        return 'CURRENT_TIMESTAMP';
    }

    protected function columnCheckSql(array $field): string
    {
        $type = strtoupper((string)$field['type']);
        $name = $this->quoteIdentifier((string)$field['name']);
        if ($type === 'JSON') {
            return ' CHECK (' . $name . ' IS JSON)';
        }
        if ($type === 'BOOLEAN') {
            return ' CHECK (' . $name . ' IN (0, 1))';
        }
        return '';
    }

    protected function referentialAction(string $action): string
    {
        return match (strtoupper(trim($action))) {
            'CASCADE'  => 'CASCADE',
            'SET NULL' => 'SET NULL',
            default    => 'NO ACTION',
        };
    }

    protected function compileForeignKey(array $relation, array $config = []): string
    {
        $constraint = $relation['constraint_name']
            ?? $this->constraintName((string)$relation['detail_table'], (string)$relation['detail_field']);
        $sql = 'CONSTRAINT ' . $this->quoteIdentifier((string)$constraint)
            . ' FOREIGN KEY (' . $this->quoteIdentifier((string)$relation['detail_field']) . ')'
            . ' REFERENCES ' . $this->qualifiedTable((string)$relation['master_table'], $config)
            . ' (' . $this->quoteIdentifier((string)$relation['master_field']) . ')';
        // ON DELETE only, and only where Oracle actually supports it. No ON UPDATE.
        $onDelete = $this->referentialAction((string)($relation['on_delete_action'] ?? 'CASCADE'));
        if ($onDelete === 'CASCADE' || $onDelete === 'SET NULL') {
            $sql .= ' ON DELETE ' . $onDelete;
        }
        return $sql;
    }

    protected function constraintName(string $table, string $field): string
    {
        // Oracle identifiers were capped at 30 chars before 12.2; stay within it.
        $base = preg_replace('/[^A-Za-z0-9_]/', '_', 'fk_' . $table . '_' . $field) ?: 'fk_rel';
        return strtoupper(substr($base, 0, 21) . '_' . substr(sha1($base), 0, 8));
    }

    protected function literal(mixed $value, string $type): string
    {
        if ($value === null) {
            return 'NULL';
        }
        $type = strtoupper($type);
        if (in_array($type, ['NUMBER', 'INTEGER', 'FLOAT', 'BINARY_DOUBLE', 'BINARY_FLOAT'], true)) {
            if (!is_numeric($value)) {
                throw new \InvalidArgumentException("{$type} defaults must be numeric.");
            }
            return trim((string)$value);
        }
        if ($type === 'BOOLEAN') {
            return $this->booleanLiteral($value);
        }
        return "'" . str_replace("'", "''", (string)$value) . "'";
    }

    protected function qualifiedTable(string $table, array $config): string
    {
        $schema = trim((string)($config['schema'] ?? ''));
        return $schema !== ''
            ? $this->quoteIdentifier($schema) . '.' . $this->quoteIdentifier($table)
            : $this->quoteIdentifier($table);
    }

    // ── live introspection ────────────────────────────────────────────────

    public function listObjects(\PDO $pdo, array $config): array
    {
        $sql = "SELECT table_name AS name, 'TABLE' AS otype FROM user_tables
                UNION ALL
                SELECT view_name AS name, 'VIEW' AS otype FROM user_views
                ORDER BY name";
        $rows = $pdo->query($sql)->fetchAll(\PDO::FETCH_ASSOC) ?: [];
        return array_map(static fn(array $r): array => [
            'name' => (string)$r['name'],
            'type' => strtoupper((string)$r['otype']) === 'VIEW' ? 'VIEW' : 'TABLE',
        ], $rows);
    }

    public function describeTable(\PDO $pdo, string $table, array $config): array
    {
        // Columns + identity flag.
        $identity = [];
        try {
            $idStmt = $pdo->prepare('SELECT column_name FROM user_tab_identity_cols WHERE table_name = ?');
            $idStmt->execute([$table]);
            foreach ($idStmt->fetchAll(\PDO::FETCH_COLUMN) as $c) {
                $identity[strtolower((string)$c)] = true;
            }
        } catch (\Throwable $e) { /* pre-12c: no identity cols view */ }

        $primary = $this->primaryColumns($pdo, $table);

        $colStmt = $pdo->prepare(
            'SELECT column_name, data_type, data_length, data_precision, data_scale, nullable, data_default
               FROM user_tab_columns WHERE table_name = ? ORDER BY column_id'
        );
        $colStmt->execute([$table]);
        $columns = [];
        foreach ($colStmt->fetchAll(\PDO::FETCH_ASSOC) as $col) {
            $name = (string)$col['column_name'];
            $type = $this->canonicalType((string)$col['data_type']);
            $length = in_array($type, ['VARCHAR2', 'NVARCHAR2', 'CHAR', 'NCHAR', 'RAW'], true)
                ? (int)$col['data_length'] : null;
            $precision = $type === 'NUMBER' && $col['data_precision'] !== null ? (int)$col['data_precision'] : null;
            $scale = $type === 'NUMBER' && $col['data_scale'] !== null ? (int)$col['data_scale'] : null;
            $columns[] = $this->normalizedColumn(
                $name,
                $type,
                $this->typeDisplay($type, $length, $precision, $scale),
                strtoupper((string)$col['nullable']) === 'Y',
                $this->cleanDefault($col['data_default']),
                isset($primary[strtolower($name)]),
                isset($identity[strtolower($name)]),
                $length,
                $precision,
                $scale
            );
        }

        // Indexes (excluding the ones backing PK/unique constraints is not
        // required — the designer just lists them).
        $ixStmt = $pdo->prepare(
            'SELECT i.index_name, ic.column_name, i.uniqueness
               FROM user_indexes i
               JOIN user_ind_columns ic ON ic.index_name = i.index_name
              WHERE i.table_name = ? ORDER BY i.index_name, ic.column_position'
        );
        $ixStmt->execute([$table]);
        $indexes = array_map(
            fn(array $r): array => $this->normalizedIndex(
                (string)$r['index_name'],
                (string)$r['column_name'],
                strtoupper((string)$r['uniqueness']) === 'UNIQUE'
            ),
            $ixStmt->fetchAll(\PDO::FETCH_ASSOC)
        );

        // Foreign keys.
        $fkStmt = $pdo->prepare(
            "SELECT c.constraint_name, cc.column_name AS detail_field,
                    rcc.table_name AS master_table, rcc.column_name AS master_field,
                    c.delete_rule
               FROM user_constraints c
               JOIN user_cons_columns cc ON cc.constraint_name = c.constraint_name
               JOIN user_cons_columns rcc ON rcc.constraint_name = c.r_constraint_name
                                          AND rcc.position = cc.position
              WHERE c.constraint_type = 'R' AND c.table_name = ?
              ORDER BY c.constraint_name, cc.position"
        );
        $fkStmt->execute([$table]);
        $foreignKeys = array_map(static fn(array $r): array => [
            'constraint_name'  => (string)$r['constraint_name'],
            'detail_table'     => $table,
            'detail_field'     => (string)$r['detail_field'],
            'master_table'     => (string)$r['master_table'],
            'master_field'     => (string)$r['master_field'],
            'on_update_action' => 'NO ACTION',                         // Oracle has none
            'on_delete_action' => strtoupper(str_replace('_', ' ', (string)($r['delete_rule'] ?? 'NO ACTION'))),
        ], $fkStmt->fetchAll(\PDO::FETCH_ASSOC));

        return ['columns' => $columns, 'indexes' => $indexes, 'foreign_keys' => $foreignKeys];
    }

    public function updateTable(\PDO $pdo, string $oldTable, string $newTable, array $fields, array $currentDetails, array $config): void
    {
        $this->transaction($pdo, function () use ($pdo, $oldTable, $newTable, $fields, $currentDetails, $config): void {
            $table = $oldTable;
            if ($oldTable !== $newTable) {
                $pdo->exec('ALTER TABLE ' . $this->qualifiedTable($oldTable, $config)
                    . ' RENAME TO ' . $this->quoteIdentifier($newTable));
                $table = $newTable;
            }
            $qualified = $this->qualifiedTable($table, $config);

            $current = [];
            foreach ((array)($currentDetails['columns'] ?? []) as $c) {
                $current[(string)$c['COLUMN_NAME']] = $c;
            }

            // Renames + identity-change guard.
            foreach ($fields as $field) {
                $original = (string)$field['original_name'];
                if ($original === '' || !isset($current[$original])) {
                    continue;
                }
                if ((bool)($current[$original]['auto_increment'] ?? false) !== !empty($field['auto_increment'])) {
                    throw new \RuntimeException("Oracle cannot add or remove an IDENTITY on existing column '{$original}'. Add a new identity column or rebuild the table.");
                }
                if ($original !== $field['name']) {
                    $pdo->exec('ALTER TABLE ' . $qualified . ' RENAME COLUMN '
                        . $this->quoteIdentifier($original) . ' TO ' . $this->quoteIdentifier((string)$field['name']));
                    $current[(string)$field['name']] = $current[$original];
                    unset($current[$original]);
                }
            }

            // Drop columns removed from the design.
            $kept = array_map(static fn(array $f): string => (string)$f['name'], $fields);
            foreach (array_keys($current) as $name) {
                if (!in_array($name, $kept, true)) {
                    $pdo->exec('ALTER TABLE ' . $qualified . ' DROP COLUMN ' . $this->quoteIdentifier($name));
                }
            }

            // Add new columns / modify existing ones.
            foreach ($fields as $field) {
                $name = (string)$field['name'];
                if (!isset($current[$name])) {
                    $pdo->exec('ALTER TABLE ' . $qualified . ' ADD (' . $this->compileColumn($field, true) . ')');
                    continue;
                }
                if (!empty($field['auto_increment'])) {
                    continue;                                         // identity kept as-is
                }
                $pdo->exec('ALTER TABLE ' . $qualified . ' MODIFY (' . $this->quoteIdentifier($name) . ' '
                    . $this->typeSql($field) . $this->defaultSql($field) . ')');
                // Nullability is changed separately, only when it differs, because
                // Oracle errors on a no-op MODIFY … NOT NULL.
                $wasNullable = strtoupper((string)($current[$name]['IS_NULLABLE'] ?? 'YES')) === 'YES';
                $wantNullable = !empty($field['nullable']);
                if ($wasNullable !== $wantNullable) {
                    $pdo->exec('ALTER TABLE ' . $qualified . ' MODIFY (' . $this->quoteIdentifier($name) . ' '
                        . ($wantNullable ? 'NULL' : 'NOT NULL') . ')');
                }
            }

            // Primary key.
            $oldPk = array_values(array_map(static fn(array $c): string => (string)$c['COLUMN_NAME'],
                array_filter($current, static fn(array $c): bool => !empty($c['primary_key']))));
            $newPk = array_values(array_map(static fn(array $f): string => (string)$f['name'],
                array_filter($fields, static fn(array $f): bool => !empty($f['primary_key']))));
            if ($oldPk !== $newPk) {
                if ($oldPk !== []) {
                    $this->dropPrimaryKey($pdo, $table, $qualified);
                }
                if ($newPk !== []) {
                    $pdo->exec('ALTER TABLE ' . $qualified . ' ADD CONSTRAINT '
                        . $this->quoteIdentifier($this->constraintName($table, 'pk'))
                        . ' PRIMARY KEY (' . implode(', ', array_map([$this, 'quoteIdentifier'], $newPk)) . ')');
                }
            }
        });
    }

    public function createView(\PDO $pdo, string $name, string $selectSql, bool $replace, array $config): void
    {
        $verb = $replace ? 'CREATE OR REPLACE VIEW' : 'CREATE VIEW';
        $pdo->exec($verb . ' ' . $this->qualifiedTable($name, $config) . ' AS ' . $selectSql);
    }

    public function dropObject(\PDO $pdo, string $name, string $type, array $config): void
    {
        $kind = strtoupper($type) === 'VIEW' ? 'VIEW' : 'TABLE';
        $target = $this->qualifiedTable($name, $config);
        $extra = $kind === 'TABLE' ? ' CASCADE CONSTRAINTS PURGE' : '';
        $inner = str_replace("'", "''", 'DROP ' . $kind . ' ' . $target . $extra);
        // No DROP … IF EXISTS in Oracle; swallow "does not exist" (ORA-00942/-4043).
        $pdo->exec("BEGIN EXECUTE IMMEDIATE '{$inner}'; EXCEPTION WHEN OTHERS THEN IF SQLCODE NOT IN (-942, -4043) THEN RAISE; END IF; END;");
    }

    public function dropForeignKey(\PDO $pdo, array $relation, array $config): void
    {
        $constraint = trim((string)($relation['constraint_name'] ?? ''));
        if ($constraint === '') {
            $stmt = $pdo->prepare(
                "SELECT c.constraint_name FROM user_constraints c
                   JOIN user_cons_columns cc ON cc.constraint_name = c.constraint_name
                  WHERE c.constraint_type = 'R' AND c.table_name = ? AND cc.column_name = ?"
            );
            $stmt->execute([(string)$relation['detail_table'], (string)$relation['detail_field']]);
            $constraint = (string)($stmt->fetchColumn() ?: '');
        }
        if ($constraint === '') {
            return;
        }
        $pdo->exec('ALTER TABLE ' . $this->qualifiedTable((string)$relation['detail_table'], $config)
            . ' DROP CONSTRAINT ' . $this->quoteIdentifier($constraint));
    }

    // ── helpers ────────────────────────────────────────────────────────────

    private function primaryColumns(\PDO $pdo, string $table): array
    {
        $stmt = $pdo->prepare(
            "SELECT cc.column_name FROM user_constraints c
               JOIN user_cons_columns cc ON cc.constraint_name = c.constraint_name
              WHERE c.constraint_type = 'P' AND c.table_name = ?"
        );
        $stmt->execute([$table]);
        $out = [];
        foreach ($stmt->fetchAll(\PDO::FETCH_COLUMN) as $c) {
            $out[strtolower((string)$c)] = true;
        }
        return $out;
    }

    private function dropPrimaryKey(\PDO $pdo, string $table, string $qualified): void
    {
        $stmt = $pdo->prepare("SELECT constraint_name FROM user_constraints WHERE constraint_type = 'P' AND table_name = ?");
        $stmt->execute([$table]);
        $name = $stmt->fetchColumn();
        if ($name !== false) {
            $pdo->exec('ALTER TABLE ' . $qualified . ' DROP CONSTRAINT ' . $this->quoteIdentifier((string)$name));
        }
    }

    private function canonicalType(string $oracleType): string
    {
        $t = strtoupper(trim($oracleType));
        // TIMESTAMP variants carry precision/timezone suffixes.
        if (str_starts_with($t, 'TIMESTAMP')) {
            return 'TIMESTAMP';
        }
        return match ($t) {
            'VARCHAR2', 'VARCHAR' => 'VARCHAR2',
            'NVARCHAR2'           => 'NVARCHAR2',
            'CHAR'                => 'CHAR',
            'NCHAR'               => 'NCHAR',
            'CLOB', 'LONG'        => 'CLOB',
            'NCLOB'               => 'NCLOB',
            'NUMBER', 'INTEGER', 'INT', 'DECIMAL', 'NUMERIC' => 'NUMBER',
            'FLOAT', 'BINARY_DOUBLE', 'BINARY_FLOAT' => 'FLOAT',
            'DATE'                => 'DATE',
            'BLOB', 'LONG RAW'    => 'BLOB',
            'RAW'                 => 'RAW',
            'JSON'                => 'JSON',
            default               => 'VARCHAR2',
        };
    }

    private function typeDisplay(string $type, ?int $length, ?int $precision, ?int $scale): string
    {
        if ($length !== null && $length > 0) {
            return $type . '(' . $length . ')';
        }
        if ($type === 'NUMBER' && $precision !== null) {
            return 'NUMBER(' . $precision . ($scale ? ',' . $scale : '') . ')';
        }
        return $type;
    }

    private function cleanDefault(mixed $default): mixed
    {
        if ($default === null) {
            return null;
        }
        // Oracle stores defaults as LONG source text, often with a trailing newline.
        $value = trim((string)$default);
        return $value === '' ? null : $value;
    }
}
