<?php
namespace App\Models;

use App\Core\Database;
use RuntimeException;

final class Communication
{
    public static function templates(): array
    {
        return Database::connection()->query(
            "SELECT *
             FROM communication_modeles
             WHERE actif=1
               AND deleted_at IS NULL
             ORDER BY contexte,libelle"
        )->fetchAll();
    }

    public static function campaigns(): array
    {
        return Database::connection()->query(
            "SELECT c.*,m.libelle AS modele_libelle
             FROM communication_campagnes c
             LEFT JOIN communication_modeles m ON m.id=c.modele_id
             WHERE c.deleted_at IS NULL
             ORDER BY c.created_at DESC,c.id DESC"
        )->fetchAll();
    }

    public static function createCampaign(array $data): int
    {
        $stmt=Database::connection()->prepare(
            "INSERT INTO communication_campagnes(
                organisation_id,
                modele_id,
                libelle,
                objet,
                corps_html,
                corps_texte,
                cible_type,
                cible_id,
                statut,
                created_by
             ) VALUES(
                :organisation_id,
                :modele_id,
                :libelle,
                :objet,
                :corps_html,
                :corps_texte,
                :cible_type,
                :cible_id,
                'brouillon',
                :created_by
             )"
        );
        $stmt->execute($data);

        return (int)Database::connection()->lastInsertId();
    }

    public static function findCampaign(int $id): ?array
    {
        $stmt=Database::connection()->prepare(
            "SELECT *
             FROM communication_campagnes
             WHERE id=:id
               AND deleted_at IS NULL
             LIMIT 1"
        );
        $stmt->execute(['id'=>$id]);

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

    public static function recipients(int $campaignId): array
    {
        $stmt=Database::connection()->prepare(
            "SELECT *
             FROM communication_destinataires
             WHERE campagne_id=:id
             ORDER BY nom_destinataire,email_destinataire"
        );
        $stmt->execute(['id'=>$campaignId]);

        return $stmt->fetchAll();
    }

    public static function replaceRecipients(
        int $campaignId,
        array $recipients
    ): void {
        $pdo=Database::connection();
        $pdo->beginTransaction();

        try{
            $pdo->prepare(
                "DELETE FROM communication_destinataires
                 WHERE campagne_id=:id
                   AND statut='a_envoyer'"
            )->execute(['id'=>$campaignId]);

            $insert=$pdo->prepare(
                "INSERT IGNORE INTO communication_destinataires(
                    campagne_id,
                    source_type,
                    source_id,
                    nom_destinataire,
                    email_destinataire,
                    statut
                 ) VALUES(
                    :campagne_id,
                    :source_type,
                    :source_id,
                    :nom,
                    :email,
                    'a_envoyer'
                 )"
            );

            foreach($recipients as $recipient){
                if(empty($recipient['email'])){
                    continue;
                }

                $insert->execute([
                    'campagne_id'=>$campaignId,
                    'source_type'=>$recipient['source_type'],
                    'source_id'=>$recipient['source_id'] ?? null,
                    'nom'=>$recipient['nom'] ?? null,
                    'email'=>mb_strtolower(trim($recipient['email'])),
                ]);
            }

            $stmt=$pdo->prepare(
                "SELECT COUNT(*)
                 FROM communication_destinataires
                 WHERE campagne_id=:id"
            );
            $stmt->execute(['id'=>$campaignId]);
            $count=(int)$stmt->fetchColumn();

            $pdo->prepare(
                "UPDATE communication_campagnes
                 SET nb_destinataires=:count,
                     statut='a_valider',
                     updated_at=CURRENT_TIMESTAMP
                 WHERE id=:id"
            )->execute([
                'count'=>$count,
                'id'=>$campaignId
            ]);

            $pdo->commit();
        }catch(\Throwable $error){
            $pdo->rollBack();
            throw $error;
        }
    }

    public static function markValidated(
        int $campaignId,
        int $userId
    ): void {
        Database::connection()->prepare(
            "UPDATE communication_campagnes
             SET statut='validee',
                 validated_by=:user_id,
                 updated_at=CURRENT_TIMESTAMP
             WHERE id=:id"
        )->execute([
            'user_id'=>$userId ?: null,
            'id'=>$campaignId
        ]);
    }

    public static function pendingRecipients(
        int $campaignId
    ): array {
        $stmt=Database::connection()->prepare(
            "SELECT *
             FROM communication_destinataires
             WHERE campagne_id=:id
               AND statut='a_envoyer'
             ORDER BY id"
        );
        $stmt->execute(['id'=>$campaignId]);

        return $stmt->fetchAll();
    }

    public static function markRecipient(
        int $recipientId,
        string $status,
        ?string $error=null
    ): void {
        Database::connection()->prepare(
            "UPDATE communication_destinataires
             SET statut=:statut,
                 erreur=:erreur,
                 envoye_at=CASE
                    WHEN :statut='envoye'
                    THEN CURRENT_TIMESTAMP
                    ELSE envoye_at
                 END
             WHERE id=:id"
        )->execute([
            'statut'=>$status,
            'erreur'=>$error,
            'id'=>$recipientId
        ]);
    }

    public static function finishCampaign(int $campaignId): void
    {
        $stmt=Database::connection()->prepare(
            "SELECT
                SUM(statut='envoye') AS sent,
                SUM(statut='echec') AS failed
             FROM communication_destinataires
             WHERE campagne_id=:id"
        );
        $stmt->execute(['id'=>$campaignId]);
        $counts=$stmt->fetch() ?: [];

        $sent=(int)($counts['sent'] ?? 0);
        $failed=(int)($counts['failed'] ?? 0);

        Database::connection()->prepare(
            "UPDATE communication_campagnes
             SET nb_envoyes=:sent,
                 nb_echecs=:failed,
                 statut=CASE
                    WHEN :failed=0 THEN 'terminee'
                    WHEN :sent=0 THEN 'echec'
                    ELSE 'partielle'
                 END,
                 terminee_at=CURRENT_TIMESTAMP
             WHERE id=:id"
        )->execute([
            'sent'=>$sent,
            'failed'=>$failed,
            'id'=>$campaignId
        ]);
    }
}
