<?php

/**
 * @package Dotclear
 *
 * @copyright Olivier Meunier & Association Dotclear
 * @copyright AGPL-3.0
 */
declare(strict_types=1);

namespace Dotclear\Schema\Database\PdoSqlite;

use Dotclear\Database\AbstractSchema;
use Dotclear\Database\MetaRecord;
use Dotclear\Exception\DatabaseException;

/**
 * @class Schema
 *
 * SQLite Database schema Handler
 */
class Schema extends AbstractSchema
{
    /**
     * @var array<string, array<string, array{type: string, len: int, default: mixed, null: bool}> >     $table_hist
     */
    private array $table_hist = [];

    /**
     * Stack for tables creation
     *
     * @var array<string, string[]>    $table_stack
     */
    private array $table_stack = [];

    /**
     * Execution stack
     *
     * @var string[]    $x_stack
     */
    private array $x_stack = [];

    public function dbt2udt(string $type, int &$len, &$default): string
    {
        $type = parent::dbt2udt($type, $len, $default);

        switch ($type) {
            case 'float':
                return 'real';

            case 'double':
                return 'float';

            case 'timestamp':
                # DATETIME real type is TIMESTAMP
                if ($default === '1970-01-01 00:00:00') {
                    # Bad hack
                    $default = 'now()';
                }

                return 'timestamp';

            case 'integer':
            case 'mediumint':
            case 'bigint':
            case 'tinyint':
            case 'smallint':
            case 'numeric':
                return 'integer';

            case 'tinytext':
            case 'longtext':
                return 'text';
        }

        return $type;
    }

    public function udt2dbt(string $type, ?int &$len, &$default): string
    {
        $type = parent::udt2dbt($type, $len, $default);

        switch ($type) {
            case 'integer':
            case 'smallint':
            case 'bigint':
                return 'integer';

            case 'real':
            case 'float:':
                return 'real';

            case 'date':
            case 'time':
                return 'timestamp';

            case 'timestamp':
                if ($default === 'now()') {
                    # SQLite does not support now() default value...
                    $default = '1970-01-01 00:00:00';
                }

                return $type;
        }

        return $type;
    }

    public function flushStack(): void
    {
        foreach ($this->table_stack as $table => $def) {
            $sql = 'CREATE TABLE ' . $table . ' (' . implode(', ', $def) . ')';
            $this->con->execute($sql);
        }

        foreach ($this->x_stack as $x) {
            $this->con->execute($x);
        }
    }

    public function db_get_tables(): array
    {
        $res = [];
        $sql = "SELECT * FROM sqlite_master WHERE type = 'table'";
        $rs  = new MetaRecord($this->con->select($sql));

        $res = [];
        while ($rs->fetch()) {
            $name = $rs->strField('tbl_name');
            if ($name !== '') {
                $res[] = $name;
            }
        }

        return $res;
    }

    public function db_get_columns(string $table): array
    {
        $sql = 'PRAGMA table_info(' . $this->con->escapeSystem($table) . ')';
        $rs  = new MetaRecord($this->con->select($sql));

        /**
         * @var array<string, array{type: string, len: int, null: bool, default: mixed}>
         */
        $res = [];
        while ($rs->fetch()) {
            $field   = trim($rs->strField('name'));
            $type    = trim($rs->strField('type'));
            $null    = !$rs->boolField('notnull');
            $default = trim($rs->strField('dflt_value'));

            $len = 0;
            if (preg_match('/^(.+?)\(([\d,]+)\)$/si', $type, $m)) {
                $type = $m[1];
                $len  = (int) $m[2];
            }

            $res[$field] = [
                'type'    => $type,
                'len'     => $len,
                'null'    => $null,
                'default' => $default,
            ];
        }

        return $res;
    }

    public function db_get_keys(string $table): array
    {
        $res = [];

        # Get primary keys first
        $sql = "SELECT sql FROM sqlite_master WHERE type='table' AND name='" . $this->con->escapeStr($table) . "'";
        $rs  = new MetaRecord($this->con->select($sql));

        if ($rs->isEmpty()) {
            return [];
        }

        # Get primary keys
        $n = preg_match_all('/^\s*CONSTRAINT\s+([^,]+?)\s+PRIMARY\s+KEY\s+\((.+?)\)/msi', $rs->strField('sql'), $match);
        if ($n > 0) {
            foreach ($match[1] as $i => $name) {
                $cols = preg_split('/\s*,\s*/', $match[2][$i]);
                if ($cols !== false) {
                    $res[] = [
                        'name'    => $name,
                        'primary' => true,
                        'unique'  => false,
                        'cols'    => $cols,
                    ];
                }
            }
        }

        # Get unique keys
        $n = preg_match_all('/^\s*CONSTRAINT\s+([^,]+?)\s+UNIQUE\s+\((.+?)\)/msi', $rs->strField('sql'), $match);
        if ($n > 0) {
            foreach ($match[1] as $i => $name) {
                $cols = preg_split('/\s*,\s*/', $match[2][$i]);
                if ($cols !== false) {
                    $res[] = [
                        'name'    => $name,
                        'primary' => false,
                        'unique'  => true,
                        'cols'    => $cols,
                    ];
                }
            }
        }

        return $res;
    }

    public function db_get_indexes(string $table): array
    {
        $sql = 'PRAGMA index_list(' . $this->con->escapeSystem($table) . ')';
        $rs  = new MetaRecord($this->con->select($sql));

        $res = [];
        while ($rs->fetch()) {
            $name = $rs->strField('name');
            if (preg_match('/^sqlite_/', $name)) {
                continue;
            }

            $idx  = new MetaRecord($this->con->select('PRAGMA index_info(' . $this->con->escapeSystem($name) . ')'));
            $cols = [];
            while ($idx->fetch()) {
                $idx_name = $rs->strField('name');
                if ($idx_name !== '') {
                    $cols[] = $idx_name;
                }
            }

            $res[] = [
                'name' => $name,
                'type' => 'btree',
                'cols' => $cols,
            ];
        }

        return $res;
    }

    public function db_get_references(string $table): array
    {
        $sql = 'SELECT * FROM sqlite_master WHERE type=\'trigger\' AND tbl_name = \'%1$s\' AND name LIKE \'%2$s_%%\' ';
        $res = [];

        # Find constraints on table
        $bir = new MetaRecord($this->con->select(sprintf($sql, $this->con->escapeStr($table), 'bir')));
        $bur = new MetaRecord($this->con->select(sprintf($sql, $this->con->escapeStr($table), 'bur')));

        if ($bir->isEmpty() || $bur->isEmpty()) {
            return $res;
        }

        while ($bir->fetch()) {
            # Find child column and parent table and column
            if (!preg_match('/FROM\s+(.+?)\s+WHERE\s+(.+?)\s+=\s+NEW\.(.+?)\s*?\) IS\s+NULL/msi', $bir->strField('sql'), $m)) {
                continue;
            }

            $c_col   = $m[3];
            $p_table = $m[1];
            $p_col   = $m[2];

            # Find on update
            $on_update = 'restrict';
            $aur       = new MetaRecord($this->con->select(sprintf($sql, $this->con->escapeStr($p_table), 'aur')));
            while ($aur->fetch()) {
                $aur_sql = $aur->strField('sql');
                if (!preg_match('/AFTER\s+UPDATE/msi', $aur_sql)) {
                    continue;
                }

                if (preg_match('/UPDATE\s+' . $table . '\s+SET\s+' . $c_col . '\s*=\s*NEW.' . $p_col .
                    '\s+WHERE\s+' . $c_col . '\s*=\s*OLD\.' . $p_col . '/msi', $aur_sql)) {
                    $on_update = 'cascade';

                    break;
                }

                if (preg_match('/UPDATE\s+' . $table . '\s+SET\s+' . $c_col . '\s*=\s*NULL' .
                    '\s+WHERE\s+' . $c_col . '\s*=\s*OLD\.' . $p_col . '/msi', $aur_sql)) {
                    $on_update = 'set null';

                    break;
                }
            }

            # Find on delete
            $on_delete = 'restrict';
            $bdr       = new MetaRecord($this->con->select(sprintf($sql, $this->con->escapeStr($p_table), 'bdr')));
            while ($bdr->fetch()) {
                $bdr_sql = $bdr->strField('sql');
                if (!preg_match('/BEFORE\s+DELETE/msi', $bdr_sql)) {
                    continue;
                }

                if (preg_match('/DELETE\s+FROM\s+' . $table . '\s+WHERE\s+' . $c_col . '\s*=\s*OLD\.' . $p_col . '/msi', $bdr_sql)) {
                    $on_delete = 'cascade';

                    break;
                }

                if (preg_match('/UPDATE\s+' . $table . '\s+SET\s+' . $c_col . '\s*=\s*NULL' .
                    '\s+WHERE\s+' . $c_col . '\s*=\s*OLD\.' . $p_col . '/msi', $bdr_sql)) {
                    $on_update = 'set null';

                    break;
                }
            }

            $res[] = [
                'name'    => substr($bir->strField('name'), 4),
                'c_cols'  => [$c_col],
                'p_table' => $p_table,
                'p_cols'  => [$p_col],
                'update'  => $on_update,
                'delete'  => $on_delete,
            ];
        }

        return $res;
    }

    public function db_create_table(string $name, array $fields): void
    {
        $a = [];

        foreach ($fields as $n => $f) {
            $type    = $f['type'];
            $len     = $f['len'];
            $default = $f['default'];
            $null    = $f['null'];

            $type = $this->udt2dbt($type, $len, $default);
            $len  = $len > 0 ? '(' . $len . ')' : '';
            $null = $null ? 'NULL' : 'NOT NULL';

            $default = $this->con->formatValue($default);
            if ($default !== '') {
                $default = 'DEFAULT ' . $default;
            }

            $a[] = $n . ' ' . $type . $len . ' ' . $null . ' ' . $default;
        }

        $this->table_stack[$name][] = implode(', ', $a);
        $this->table_hist[$name]    = $fields;
    }

    public function db_create_field(string $table, string $name, string $type, ?int $len, bool $null, $default): void
    {
        $type = $this->udt2dbt($type, $len, $default);

        $default = $this->con->formatValue($default);
        if ($default !== '') {
            $default = 'DEFAULT ' . $default;
        }

        $sql = 'ALTER TABLE ' . $this->con->escapeSystem($table) . ' ADD COLUMN ' . $this->con->escapeSystem($name) . ' ' . $type . ($len > 0 ? '(' . $len . ')' : '') . ' ' . ($null ? 'NULL' : 'NOT NULL') . ' ' . $default;

        $this->con->execute($sql);
    }

    public function db_create_primary(string $table, string $name, array $fields): void
    {
        $this->table_stack[$table][] = 'CONSTRAINT ' . $name . ' PRIMARY KEY (' . implode(',', $fields) . ')';
    }

    public function db_create_unique(string $table, string $name, array $fields): void
    {
        $this->table_stack[$table][] = 'CONSTRAINT ' . $name . ' UNIQUE (' . implode(',', $fields) . ')';
    }

    public function db_create_index(string $table, string $name, string $type, array $fields): void
    {
        $this->x_stack[] = 'CREATE INDEX ' . $name . ' ON ' . $table . ' (' . implode(',', $fields) . ')';
    }

    public function db_create_reference(string $name, string $table, array $fields, string $foreign_table, array $foreign_fields, false|string $update, false|string $delete): void
    {
        if (!isset($this->table_hist[$table])) {
            return;
        }

        if (count($fields) > 1 || count($foreign_fields) > 1) {
            throw new DatabaseException('SQLite UDBS does not support multiple columns foreign keys');
        }

        $c_col = $fields[0];
        $p_col = $foreign_fields[0];

        $update = $update !== false ? strtolower($update) : '';
        $delete = $delete !== false ? strtolower($delete) : '';

        $cnull = $this->table_hist[$table][$c_col]['null'];

        # Create constraint
        $this->x_stack[] = 'CREATE TRIGGER bir_' . $name . ' ' .
            'BEFORE INSERT ON ' . $table . ' ' .
            'FOR EACH ROW BEGIN ' .
            'SELECT RAISE(ROLLBACK,\'insert on table "' . $table . '" violates foreign key constraint "' . $name . '"\')' . ' ' .
            'WHERE ' .
            ($cnull ? 'NEW.' . $c_col . ' IS NOT NULL AND ' : '') .
            '(SELECT ' . $p_col . ' FROM ' . $foreign_table . ' WHERE ' . $p_col . ' = NEW.' . $c_col . ') IS NULL; ' .
            'END;';

        # Update constraint
        $this->x_stack[] = 'CREATE TRIGGER bur_' . $name . ' ' .
            'BEFORE UPDATE ON ' . $table . ' ' .
            'FOR EACH ROW BEGIN ' .
            'SELECT RAISE(ROLLBACK,\'update on table "' . $table . '" violates foreign key constraint "' . $name . '"\')' . ' ' .
            'WHERE ' .
            ($cnull ? 'NEW.' . $c_col . ' IS NOT NULL AND ' : '') .
            '(SELECT ' . $p_col . ' FROM ' . $foreign_table . ' WHERE ' . $p_col . ' = NEW.' . $c_col . ') IS NULL; ' .
            'END;';

        # ON UPDATE
        if ($update === 'cascade') {
            $this->x_stack[] = 'CREATE TRIGGER aur_' . $name . ' ' .
                'AFTER UPDATE ON ' . $foreign_table . ' ' .
                'FOR EACH ROW BEGIN ' .
                'UPDATE ' . $table . ' SET ' . $c_col . ' = NEW.' . $p_col . ' WHERE ' . $c_col . ' = OLD.' . $p_col . '; ' .
                'END;';
        } elseif ($update === 'set null') {
            $this->x_stack[] = 'CREATE TRIGGER aur_' . $name . ' ' .
                'AFTER UPDATE ON ' . $foreign_table . ' ' .
                'FOR EACH ROW BEGIN ' .
                'UPDATE ' . $table . ' SET ' . $c_col . ' = NULL WHERE ' . $c_col . ' = OLD.' . $p_col . '; ' .
                'END;';
        } else { # default on restrict
            $this->x_stack[] = 'CREATE TRIGGER burp_' . $name . ' ' .
                'BEFORE UPDATE ON ' . $foreign_table . ' ' .
                'FOR EACH ROW BEGIN ' .
                'SELECT RAISE (ROLLBACK,\'update on table "' . $foreign_table . '" violates foreign key constraint "' . $name . '"\')' . ' ' .
                'WHERE (SELECT ' . $c_col . ' FROM ' . $table . ' WHERE ' . $c_col . ' = OLD.' . $p_col . ') IS NOT NULL; ' .
                'END;';
        }

        # ON DELETE
        if ($delete === 'cascade') {
            $this->x_stack[] = 'CREATE TRIGGER bdr_' . $name . ' ' .
                'BEFORE DELETE ON ' . $foreign_table . ' ' .
                'FOR EACH ROW BEGIN ' .
                'DELETE FROM ' . $table . ' WHERE ' . $c_col . ' = OLD.' . $p_col . '; ' .
                'END;';
        } elseif ($delete === 'set null') {
            $this->x_stack[] = 'CREATE TRIGGER bdr_' . $name . ' ' .
                'BEFORE DELETE ON ' . $foreign_table . ' ' .
                'FOR EACH ROW BEGIN ' .
                'UPDATE ' . $table . ' SET ' . $c_col . ' = NULL WHERE ' . $c_col . ' = OLD.' . $p_col . '; ' .
                'END;';
        } else {
            $this->x_stack[] = 'CREATE TRIGGER bdr_' . $name . ' ' .
                'BEFORE DELETE ON ' . $foreign_table . ' ' .
                'FOR EACH ROW BEGIN ' .
                'SELECT RAISE (ROLLBACK,\'delete on table "' . $foreign_table . '" violates foreign key constraint "' . $name . '"\')' . ' ' .
                'WHERE (SELECT ' . $c_col . ' FROM ' . $table . ' WHERE ' . $c_col . ' = OLD.' . $p_col . ') IS NOT NULL; ' .
                'END;';
        }
    }

    public function db_alter_field(string $table, string $name, string $type, ?int $len, bool $null, $default): void
    {
        $type = $this->udt2dbt($type, $len, $default);
        if (!in_array($type, ['integer', 'text', 'timestamp'], true)) {
            throw new DatabaseException('SQLite fields cannot be changed.');
        }
    }

    public function db_alter_primary(string $table, string $name, string $newname, array $fields): void
    {
        throw new DatabaseException('SQLite primary key cannot be changed.');
    }

    public function db_alter_unique(string $table, string $name, string $newname, array $fields): void
    {
        throw new DatabaseException('SQLite unique index cannot be changed.');
    }

    public function db_alter_index(string $table, string $name, string $newname, string $type, array $fields): void
    {
        $this->con->execute('DROP INDEX IF EXISTS ' . $name);
        $this->con->execute('CREATE INDEX ' . $newname . ' ON ' . $table . ' (' . implode(',', $fields) . ')');
    }

    public function db_alter_reference(string $name, string $newname, string $table, array $fields, string $foreign_table, array $foreign_fields, false|string $update, false|string $delete): void
    {
        $this->con->execute('DROP TRIGGER IF EXISTS bur_' . $name);
        $this->con->execute('DROP TRIGGER IF EXISTS burp_' . $name);
        $this->con->execute('DROP TRIGGER IF EXISTS bir_' . $name);
        $this->con->execute('DROP TRIGGER IF EXISTS aur_' . $name);
        $this->con->execute('DROP TRIGGER IF EXISTS bdr_' . $name);

        $this->table_hist[$table] = $this->db_get_columns($table);
        $this->db_create_reference($newname, $table, $fields, $foreign_table, $foreign_fields, $update, $delete);
    }

    public function db_drop_unique(string $table, string $name): void
    {
        throw new DatabaseException('SQLite unique index cannot be removed.');
    }
}
