<?php

declare(strict_types=1);

namespace PpPhp\Core\Deploy;

use PpPhp\Core\DB;

/**
 * Live-server database connections, managed entirely inside the Deploy client
 * and stored in `deploy_db_connections` (passwords encrypted at rest).
 *
 * These are DISTINCT from the project's development database (config/database.php).
 * They exist only to be written into the *uploaded* copy of config/database.php
 * so the app running on the live server talks to the right production database.
 * Nothing here ever changes how the app behaves during local development.
 */
class DbConnectionRepository
{
    public function __construct(private int $projectId)
    {
        DeploySchema::ensureSchema();
        DB::Init();
        DB::SetConnection();
    }

    /** All live-DB connections for the project, sanitised for the browser. */
    public function forClient(): array
    {
        $rows = DB::FetchAll(
            'SELECT * FROM `deploy_db_connections` WHERE `project_id` = ? ORDER BY `name`',
            [$this->projectId]
        ) ?: [];
        return array_map([$this, 'sanitise'], $rows);
    }

    /** A transport/injection-ready profile (decrypted). Server-side only. */
    public function dbProfile(int $id): ?array
    {
        $row = $this->row($id);
        if (!$row) {
            return null;
        }
        return [
            'id'      => (int) $row['id'],
            'name'    => $row['name'],
            'type'    => $row['type'],
            'host'    => $row['host'],
            'port'    => $row['port'],
            'dbname'  => $row['dbname'],
            'user'    => $row['username'],
            'pass'    => DeploySecret::decrypt((string) ($row['password_enc'] ?? '')),
            'charset' => $row['charset'] ?: 'utf8mb4',
        ];
    }

    /** Insert or update. Blank password on update keeps the stored secret. */
    public function save(array $in): int
    {
        $id = (int) ($in['id'] ?? 0);
        $existing = $id > 0 ? $this->row($id) : null;

        $type = in_array(($in['type'] ?? 'mysql'), ['mysql', 'pgsql', 'sqlite', 'sqlsrv'], true) ? $in['type'] : 'mysql';
        $passEnc = ($in['pass'] ?? '') !== ''
            ? DeploySecret::encrypt((string) $in['pass'])
            : ($existing['password_enc'] ?? '');

        $data = [
            'project_id'   => $this->projectId,
            'name'         => trim((string) ($in['name'] ?? 'Database')),
            'type'         => $type,
            'host'         => trim((string) ($in['host'] ?? '127.0.0.1')),
            'port'         => trim((string) ($in['port'] ?? '3306')),
            'dbname'       => trim((string) ($in['dbname'] ?? '')),
            'username'     => (string) ($in['user'] ?? ''),
            'password_enc' => $passEnc,
            'charset'      => trim((string) ($in['charset'] ?? 'utf8mb4')) ?: 'utf8mb4',
        ];

        if ($existing) {
            DB::Update('deploy_db_connections', $data, ['id' => $id, 'project_id' => $this->projectId]);
            return $id;
        }
        return (int) DB::Insert('deploy_db_connections', $data);
    }

    public function delete(int $id): bool
    {
        return (bool) DB::Delete('deploy_db_connections', ['id' => $id, 'project_id' => $this->projectId]);
    }

    private function row(int $id): ?array
    {
        $rows = DB::Select('deploy_db_connections', ['id' => $id, 'project_id' => $this->projectId]);
        return $rows[0] ?? null;
    }

    private function sanitise(array $r): array
    {
        return [
            'id'          => (int) $r['id'],
            'key'         => (int) $r['id'],
            'name'        => $r['name'],
            'type'        => $r['type'],
            'host'        => $r['host'],
            'port'        => $r['port'],
            'dbname'      => $r['dbname'],
            'user'        => $r['username'],
            'charset'     => $r['charset'],
            'hasPassword' => ($r['password_enc'] ?? '') !== '',
        ];
    }
}
