<?php

namespace App\Core\Database\Services;

use App\Core\Database\Models\DatabaseConnectionModel;
use CodeIgniter\Database\BaseConnection;
use Config\Database;
use InvalidArgumentException;
use RuntimeException;

class DatabaseConnectionService
{
    private const BLOCKED_SQL = [
        'INSERT',
        'UPDATE',
        'DELETE',
        'DROP',
        'ALTER',
        'TRUNCATE',
        'CREATE',
        'REPLACE',
    ];

    public function encryptPassword(?string $password): ?string
    {
        return $password ? base64_encode(service('encrypter')->encrypt($password)) : null;
    }

    public function decryptPassword(?string $password): ?string
    {
        return $password ? service('encrypter')->decrypt(base64_decode($password)) : null;
    }

    public function connect(array $connection): BaseConnection
    {
        return Database::connect([
            'DSN' => '',
            'hostname' => $connection['host'],
            'username' => $connection['username'],
            'password' => $this->decryptPassword($connection['encrypted_password'] ?? null) ?? '',
            'database' => $connection['database_name'],
            'DBDriver' => $connection['driver'] ?: 'MySQLi',
            'DBPrefix' => '',
            'pConnect' => false,
            'DBDebug' => false,
            'charset' => $connection['charset'] ?: 'utf8mb4',
            'DBCollat' => 'utf8mb4_general_ci',
            'port' => (int) ($connection['port'] ?: 3306),
        ], false);
    }

    public function testConnection(array $connection): bool
    {
        $db = $this->connect($connection);
        $query = $db->query('SELECT 1 AS ok');
        if ($query === false) {
            throw new RuntimeException($this->databaseError($db));
        }

        $db->close();

        return (bool) $query->getRowArray();
    }

    public function getTables(int $connectionId): array
    {
        $connection = (new DatabaseConnectionModel())->find($connectionId);
        if (! $connection) {
            return [];
        }

        $db = $this->connect($connection);
        $tables = $db->listTables();
        $db->close();

        return $tables;
    }

    public function getColumns(int $connectionId, string $table): array
    {
        $connection = (new DatabaseConnectionModel())->find($connectionId);
        if (! $connection) {
            return [];
        }

        $db = $this->connect($connection);
        $columns = $db->getFieldData($table);
        $db->close();

        return $columns;
    }

    public function getPrimaryKey(int $connectionId, string $table): ?string
    {
        foreach ($this->getColumns($connectionId, $table) as $column) {
            if (! empty($column->primary_key)) {
                return $column->name;
            }
        }

        return null;
    }

    public function getIndexes(int $connectionId, string $table): array
    {
        return $this->runReadOnlyQuery($connectionId, 'SHOW INDEX FROM `' . str_replace('`', '', $table) . '`');
    }

    public function getRelations(int $connectionId, string $table): array
    {
        return [];
    }

    public function runReadOnlyQuery(int $connectionId, string $sql): array
    {
        $this->assertReadOnly($sql);
        $connection = (new DatabaseConnectionModel())->find($connectionId);
        if (! $connection) {
            throw new RuntimeException('Connexion SQL introuvable.');
        }

        $db = $this->connect($connection);
        $query = $db->query($sql);
        if ($query === false) {
            $message = $this->databaseError($db);
            $db->close();
            throw new RuntimeException($message);
        }

        $rows = $query->getResultArray();
        $db->close();

        return $rows;
    }

    public function assertReadOnly(string $sql): void
    {
        $normalized = strtoupper(preg_replace('/\s+/', ' ', trim($sql)) ?? '');

        if (! str_starts_with($normalized, 'SELECT') && ! str_starts_with($normalized, 'SHOW') && ! str_starts_with($normalized, 'DESCRIBE')) {
            throw new InvalidArgumentException('Seules les requêtes de lecture sont autorisées.');
        }

        foreach (self::BLOCKED_SQL as $keyword) {
            if (preg_match('/\b' . preg_quote($keyword, '/') . '\b/i', $sql)) {
                throw new InvalidArgumentException('Mot-clé SQL interdit : ' . $keyword);
            }
        }
    }

    private function databaseError(BaseConnection $db): string
    {
        $error = $db->error();
        $message = trim((string) ($error['message'] ?? ''));
        $code = (string) ($error['code'] ?? '');

        if ($message === '') {
            return 'La requête SQL n’a pas pu être exécutée.';
        }

        return $code !== '' && $code !== '0'
            ? 'Erreur SQL ' . $code . ' : ' . $message
            : 'Erreur SQL : ' . $message;
    }
}
