<?php

declare(strict_types=1);

namespace FecaMailshots\Repository;

use FecaMailshots\Infrastructure\DatabaseRouter;
use PDO;

final class PdfAssetRepository
{
    private DatabaseRouter $router;

    public function __construct(DatabaseRouter $router)
    {
        $this->router = $router;
    }

    /** @return list<array<string, mixed>> */
    public function all(): array
    {
        $sql = 'SELECT id, name, file_name, mime_type, width_mm, height_mm, justification, OCTET_LENGTH(file_bytes) AS byte_size, created_at, updated_at FROM mailshot_pdf_assets ORDER BY name ASC';
        return $this->router->mailshotsPdo()->query($sql)->fetchAll(PDO::FETCH_ASSOC);
    }

    /** @return array<string,mixed>|null */
    public function findByName(string $name): ?array
    {
        $name = trim($name);
        if ($name === '') {
            return null;
        }
        $sql = 'SELECT id, name, file_name, mime_type, TO_BASE64(file_bytes) AS file_bytes_b64, width_mm, height_mm, justification
                FROM mailshot_pdf_assets
                WHERE LOWER(name) = LOWER(:name)
                LIMIT 1';
        $stmt = $this->router->mailshotsPdo()->prepare($sql);
        $stmt->execute(['name' => $name]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        if ($row === false) {
            return null;
        }
        $row['file_bytes'] = $this->decodeBase64Blob((string) ($row['file_bytes_b64'] ?? ''));
        unset($row['file_bytes_b64']);
        return $row;
    }

    /** @param array<string, mixed> $row */
    public function create(array $row): int
    {
        $sql = 'INSERT INTO mailshot_pdf_assets (name, file_name, mime_type, file_bytes, width_mm, height_mm, justification) VALUES (:name, :file_name, :mime_type, :file_bytes, :width_mm, :height_mm, :justification)';
        $stmt = $this->router->mailshotsPdo()->prepare($sql);
        $stmt->bindValue(':name', trim((string) ($row['name'] ?? '')));
        $stmt->bindValue(':file_name', trim((string) ($row['file_name'] ?? '')));
        $stmt->bindValue(':mime_type', trim((string) ($row['mime_type'] ?? '')));
        $stmt->bindValue(':file_bytes', $row['file_bytes'] ?? '', PDO::PARAM_LOB);
        $stmt->bindValue(':width_mm', (float) ($row['width_mm'] ?? 0.0));
        $stmt->bindValue(':height_mm', (float) ($row['height_mm'] ?? 0.0));
        $stmt->bindValue(':justification', (string) ($row['justification'] ?? 'in-place'));
        $stmt->execute();
        return (int) $this->router->mailshotsPdo()->lastInsertId();
    }

    /** @param array<string, mixed> $row */
    public function update(int $id, array $row): void
    {
        $sql = 'UPDATE mailshot_pdf_assets SET name = :name, file_name = :file_name, mime_type = :mime_type, file_bytes = :file_bytes, width_mm = :width_mm, height_mm = :height_mm, justification = :justification WHERE id = :id';
        $stmt = $this->router->mailshotsPdo()->prepare($sql);
        $stmt->bindValue(':id', $id, PDO::PARAM_INT);
        $stmt->bindValue(':name', trim((string) ($row['name'] ?? '')));
        $stmt->bindValue(':file_name', trim((string) ($row['file_name'] ?? '')));
        $stmt->bindValue(':mime_type', trim((string) ($row['mime_type'] ?? '')));
        $stmt->bindValue(':file_bytes', $row['file_bytes'] ?? '', PDO::PARAM_LOB);
        $stmt->bindValue(':width_mm', (float) ($row['width_mm'] ?? 0.0));
        $stmt->bindValue(':height_mm', (float) ($row['height_mm'] ?? 0.0));
        $stmt->bindValue(':justification', (string) ($row['justification'] ?? 'in-place'));
        $stmt->execute();
    }

    public function delete(int $id): void
    {
        $stmt = $this->router->mailshotsPdo()->prepare('DELETE FROM mailshot_pdf_assets WHERE id = :id');
        $stmt->execute(['id' => $id]);
    }

    private function decodeBase64Blob(string $value): string
    {
        if ($value === '') {
            return '';
        }
        $decoded = base64_decode($value, true);
        if (!is_string($decoded)) {
            throw new \RuntimeException('Failed to decode PDF asset blob.');
        }
        return $decoded;
    }
}
