<?php
namespace App\Models;

use App\Core\Database;

final class CommunicationTemplate
{
    public static function all(): array
    {
        return Database::connection()->query(
            "SELECT
                m.*,
                s.contenu_html AS signature_html,
                s.contenu_texte AS signature_texte
             FROM communication_modeles m
             LEFT JOIN communication_signatures s
                    ON s.organisation_id=m.organisation_id
                   AND s.code COLLATE utf8mb4_unicode_ci = m.signature_code COLLATE utf8mb4_unicode_ci
                   AND s.actif=1
                   AND s.deleted_at IS NULL
             WHERE m.actif=1
               AND m.deleted_at IS NULL
             ORDER BY m.contexte,m.libelle"
        )->fetchAll();
    }

    public static function find(int $id): ?array
    {
        $stmt=Database::connection()->prepare(
            "SELECT
                m.*,
                s.contenu_html AS signature_html,
                s.contenu_texte AS signature_texte
             FROM communication_modeles m
             LEFT JOIN communication_signatures s
                    ON s.organisation_id=m.organisation_id
                   AND s.code COLLATE utf8mb4_unicode_ci = m.signature_code COLLATE utf8mb4_unicode_ci
                   AND s.actif=1
                   AND s.deleted_at IS NULL
             WHERE m.id=:id
               AND m.deleted_at IS NULL
             LIMIT 1"
        );
        $stmt->execute(['id'=>$id]);

        return $stmt->fetch() ?: null;
    }
}
