<?php
namespace App\Models;

use App\Core\Database;

final class FamilyMessage
{
    public static function threads(int $organisationId, ?int $clientId = null): array
    {
        $sql = "SELECT t.*,c.nom,c.prenom,c.code_client,
                       (SELECT COUNT(*) FROM cda_family_message_posts p
                        WHERE p.thread_id=t.id AND p.read_at IS NULL
                          AND p.author_side='family') AS unread_direction
                FROM cda_family_message_threads t
                JOIN clients c ON c.id=t.client_id
                WHERE t.organisation_id=:organisation_id";
        $params = ['organisation_id'=>$organisationId];
        if ($clientId !== null) {
            $sql .= ' AND t.client_id=:client_id';
            $params['client_id']=$clientId;
        }
        $sql .= ' ORDER BY t.updated_at DESC,t.id DESC';
        $stmt=Database::connection()->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll() ?: [];
    }

    public static function find(int $id): ?array
    {
        $stmt=Database::connection()->prepare(
            "SELECT t.*,c.nom,c.prenom,c.email,c.code_client
             FROM cda_family_message_threads t
             JOIN clients c ON c.id=t.client_id
             WHERE t.id=:id LIMIT 1"
        );
        $stmt->execute(['id'=>$id]);
        return $stmt->fetch() ?: null;
    }

    public static function posts(int $threadId): array
    {
        $stmt=Database::connection()->prepare(
            "SELECT p.*,u.nom AS auteur_nom,u.prenom AS auteur_prenom
             FROM cda_family_message_posts p
             LEFT JOIN utilisateurs u ON u.id=p.author_user_id
             WHERE p.thread_id=:thread_id ORDER BY p.created_at,p.id"
        );
        $stmt->execute(['thread_id'=>$threadId]);
        return $stmt->fetchAll() ?: [];
    }

    public static function create(int $organisationId,int $clientId,int $userId,string $type,string $subject,string $body): int
    {
        $pdo=Database::connection();
        $pdo->beginTransaction();
        try {
            $stmt=$pdo->prepare(
                "INSERT INTO cda_family_message_threads
                 (organisation_id,client_id,type_code,subject,status,created_by,updated_at)
                 VALUES (?,?,?,?, 'nouvelle',?,NOW())"
            );
            $stmt->execute([$organisationId,$clientId,$type,$subject,$userId]);
            $id=(int)$pdo->lastInsertId();
            self::addPostInternal($pdo,$id,$userId,'family',$body);
            $pdo->commit();
            return $id;
        } catch (\Throwable $e) {
            if ($pdo->inTransaction()) $pdo->rollBack();
            throw $e;
        }
    }

    public static function reply(int $threadId,int $userId,string $side,string $body,string $status): void
    {
        $pdo=Database::connection();
        $pdo->beginTransaction();
        try {
            self::addPostInternal($pdo,$threadId,$userId,$side,$body);
            $stmt=$pdo->prepare(
                "UPDATE cda_family_message_threads
                 SET status=:status,updated_at=NOW(),closed_at=IF(:closed_status='fermee',NOW(),NULL)
                 WHERE id=:id"
            );
            $stmt->execute(['status'=>$status,'closed_status'=>$status,'id'=>$threadId]);
            $pdo->commit();
        } catch (\Throwable $e) {
            if ($pdo->inTransaction()) $pdo->rollBack();
            throw $e;
        }
    }

    public static function markRead(int $threadId,string $readerSide): void
    {
        $authorSide=$readerSide==='family' ? 'direction' : 'family';
        $stmt=Database::connection()->prepare(
            "UPDATE cda_family_message_posts SET read_at=COALESCE(read_at,NOW())
             WHERE thread_id=? AND author_side=?"
        );
        $stmt->execute([$threadId,$authorSide]);
    }

    private static function addPostInternal(\PDO $pdo,int $threadId,int $userId,string $side,string $body): void
    {
        $stmt=$pdo->prepare(
            "INSERT INTO cda_family_message_posts(thread_id,author_user_id,author_side,body)
             VALUES (?,?,?,?)"
        );
        $stmt->execute([$threadId,$userId,$side,$body]);
    }
}
