<?php

declare(strict_types=1);

namespace FecaMailshots\Repository;

use FecaMailshots\Infrastructure\DatabaseRouter;
use PDO;

final class AttachmentRepository
{
    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, OCTET_LENGTH(file_bytes) AS byte_size, created_at, updated_at FROM mailshot_attachments ORDER BY name ASC';
        return $this->router->mailshotsPdo()->query($sql)->fetchAll(PDO::FETCH_ASSOC);
    }

    /** @param array<string, mixed> $row */
    public function create(array $row): int
    {
        $sql = 'INSERT INTO mailshot_attachments (name, file_name, mime_type, file_bytes) VALUES (:name, :file_name, :mime_type, :file_bytes)';
        $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->execute();
        return (int) $this->router->mailshotsPdo()->lastInsertId();
    }

    /** @param array<string, mixed> $row */
    public function update(int $id, array $row): void
    {
        $sql = 'UPDATE mailshot_attachments SET name = :name, file_name = :file_name, mime_type = :mime_type, file_bytes = :file_bytes 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->execute();
    }

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

    /** @return list<string> */
    public function names(): array
    {
        $rows = $this->router->mailshotsPdo()->query('SELECT name FROM mailshot_attachments ORDER BY name ASC')->fetchAll(PDO::FETCH_ASSOC);
        return array_map(static fn(array $r): string => (string) $r['name'], $rows);
    }

    /** @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, created_at, updated_at
                FROM mailshot_attachments
                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']);
        $mimeType = trim((string) ($row['mime_type'] ?? ''));
        if ($mimeType === '') {
            $mimeType = $this->inferMimeTypeFromFilename((string) ($row['file_name'] ?? ''));
            if ($mimeType !== '') {
                $row['mime_type'] = $mimeType;
                $this->updateMimeType((int) ($row['id'] ?? 0), $mimeType);
            }
        }
        return $row;
    }

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

    private function updateMimeType(int $id, string $mimeType): void
    {
        if ($id <= 0 || trim($mimeType) === '') {
            return;
        }
        $stmt = $this->router->mailshotsPdo()->prepare('UPDATE mailshot_attachments SET mime_type = :mime WHERE id = :id');
        $stmt->execute(['mime' => $mimeType, 'id' => $id]);
    }

    private function inferMimeTypeFromFilename(string $fileName): string
    {
        $fileName = trim($fileName);
        if ($fileName === '') {
            return '';
        }
        $ext = strtolower(pathinfo($fileName, PATHINFO_EXTENSION));
        if ($ext === '') {
            return '';
        }
        $map = [
            'txt' => 'text/plain',
            'csv' => 'text/csv',
            'pdf' => 'application/pdf',
            'doc' => 'application/msword',
            'docx' => 'application/vnd.openxmlformats-officedocument.wordprocessingml.document',
            'odt' => 'application/vnd.oasis.opendocument.text',
            'rtf' => 'application/rtf',
            'html' => 'text/html',
            'htm' => 'text/html',
            'json' => 'application/json',
            'xml' => 'application/xml',
            'jpg' => 'image/jpeg',
            'jpeg' => 'image/jpeg',
            'png' => 'image/png',
            'gif' => 'image/gif',
            'webp' => 'image/webp',
            'svg' => 'image/svg+xml',
            'zip' => 'application/zip',
        ];
        return $map[$ext] ?? '';
    }
}
