<?php

declare(strict_types=1);

namespace App\Models;

use Core\Database;

final class Attachment
{
    public static function upsert(int $admissionId, string $labelKey, array $payload): ?array
    {
        $existing = self::findByAdmissionAndLabel($admissionId, $labelKey);
        if ($existing) {
            Database::connection()->prepare('UPDATE attachments SET original_name=:original_name, stored_name=:stored_name, relative_path=:relative_path, mime_type=:mime_type, size_bytes=:size_bytes, updated_at=NOW() WHERE id=:id')->execute([
                'id' => $existing['id'],
                'original_name' => $payload['original_name'],
                'stored_name' => $payload['stored_name'],
                'relative_path' => $payload['relative_path'],
                'mime_type' => $payload['mime_type'],
                'size_bytes' => $payload['size_bytes'],
            ]);
            return $existing;
        }

        Database::connection()->prepare('INSERT INTO attachments (admission_id, label_key, original_name, stored_name, relative_path, mime_type, size_bytes, created_at, updated_at) VALUES (:admission_id,:label_key,:original_name,:stored_name,:relative_path,:mime_type,:size_bytes,NOW(),NOW())')->execute([
            'admission_id' => $admissionId,
            'label_key' => $labelKey,
            'original_name' => $payload['original_name'],
            'stored_name' => $payload['stored_name'],
            'relative_path' => $payload['relative_path'],
            'mime_type' => $payload['mime_type'],
            'size_bytes' => $payload['size_bytes'],
        ]);

        return null;
    }

    public static function forAdmission(int $admissionId): array
    {
        $st = Database::connection()->prepare('SELECT * FROM attachments WHERE admission_id = :id ORDER BY id ASC');
        $st->execute(['id' => $admissionId]);
        return $st->fetchAll() ?: [];
    }

    public static function find(int $id): ?array
    {
        $st = Database::connection()->prepare('SELECT * FROM attachments WHERE id = :id LIMIT 1');
        $st->execute(['id' => $id]);
        return $st->fetch() ?: null;
    }

    public static function findByAdmissionAndLabel(int $admissionId, string $labelKey): ?array
    {
        $st = Database::connection()->prepare('SELECT * FROM attachments WHERE admission_id = :admission_id AND label_key = :label_key LIMIT 1');
        $st->execute(['admission_id' => $admissionId, 'label_key' => $labelKey]);
        return $st->fetch() ?: null;
    }

    /**
     * @return list<array>
     */
    public static function deleteDependentCertificatesNotIn(int $admissionId, array $keepLabelKeys): array
    {
        $st = Database::connection()->prepare('SELECT * FROM attachments WHERE admission_id = :admission_id AND label_key LIKE :prefix ORDER BY id ASC');
        $st->execute([
            'admission_id' => $admissionId,
            'prefix' => 'dependent_birth_certificate_%',
        ]);

        $attachments = $st->fetchAll() ?: [];
        if ($attachments === []) {
            return [];
        }

        $keepMap = array_fill_keys($keepLabelKeys, true);
        $toDelete = array_values(array_filter($attachments, static fn (array $attachment): bool => !isset($keepMap[(string) $attachment['label_key']])));
        if ($toDelete === []) {
            return [];
        }

        $placeholders = implode(', ', array_fill(0, count($toDelete), '?'));
        $ids = array_map(static fn (array $attachment): int => (int) $attachment['id'], $toDelete);
        Database::connection()->prepare("DELETE FROM attachments WHERE id IN ({$placeholders})")->execute($ids);

        return $toDelete;
    }
}
