<?php
namespace App\Models;

use App\Core\Database;

final class ConsentDocument
{
    public static function create(array $data): int
    {
        $stmt=Database::connection()->prepare(
            "INSERT INTO membre_documents(
                organisation_id,
                membre_id,
                famille_id,
                type_document,
                annee_scolaire,
                version_document,
                statut,
                choix_json,
                email_envoi,
                created_by
             ) VALUES(
                :organisation_id,
                :membre_id,
                :famille_id,
                :type_document,
                :annee_scolaire,
                :version_document,
                'a_signer',
                :choix_json,
                :email_envoi,
                :created_by
             )"
        );
        $stmt->execute($data);

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

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

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

    public static function sign(
        int $id,
        string $signer,
        string $quality,
        string $signature,
        string $mode,
        string $pdfPath,
        string $checksum
    ): void {
        Database::connection()->prepare(
            "UPDATE membre_documents
             SET statut='signe',
                 nom_signataire=:nom_signataire,
                 qualite_signataire=:qualite_signataire,
                 mode_signature=:mode_signature,
                 signature_data=:signature_data,
                 fichier_pdf=:fichier_pdf,
                 checksum_sha256=:checksum,
                 signe_at=CURRENT_TIMESTAMP,
                 updated_at=CURRENT_TIMESTAMP
             WHERE id=:id
               AND statut<>'signe'
               AND deleted_at IS NULL"
        )->execute([
            'nom_signataire'=>$signer,
            'qualite_signataire'=>$quality,
            'mode_signature'=>$mode,
            'signature_data'=>$signature,
            'fichier_pdf'=>$pdfPath,
            'checksum'=>$checksum,
            'id'=>$id
        ]);
    }

    public static function forFamily(int $id, int $familyId, int $organisationId): ?array
    {
        $stmt=Database::connection()->prepare(
            "SELECT d.*,m.prenom AS membre_prenom,m.nom AS membre_nom
             FROM membre_documents d
             INNER JOIN membres_famille m ON m.id=d.membre_id
             WHERE d.id=:id
               AND d.famille_id=:famille_id
               AND d.organisation_id=:organisation_id
               AND d.deleted_at IS NULL
             LIMIT 1"
        );
        $stmt->execute(['id'=>$id,'famille_id'=>$familyId,'organisation_id'=>$organisationId]);
        return $stmt->fetch() ?: null;
    }

    public static function createToken(
        int $documentId,
        string $email,
        string $plainToken,
        string $expiresAt
    ): void {
        Database::connection()->prepare(
            "INSERT INTO consentement_tokens(
                document_id,
                token_hash,
                email_destinataire,
                expires_at
             ) VALUES(
                :document_id,
                :token_hash,
                :email,
                :expires_at
             )"
        )->execute([
            'document_id'=>$documentId,
            'token_hash'=>hash('sha256',$plainToken),
            'email'=>$email,
            'expires_at'=>$expiresAt
        ]);

        Database::connection()->prepare(
            "UPDATE membre_documents
             SET statut='envoye',
                 envoye_at=CURRENT_TIMESTAMP,
                 email_envoi=:email
             WHERE id=:id"
        )->execute(['email'=>$email,'id'=>$documentId]);
    }

    public static function tokenDocument(string $plainToken): ?array
    {
        $stmt=Database::connection()->prepare(
            "SELECT d.*,t.id AS token_id,t.expires_at,t.used_at
             FROM consentement_tokens t
             INNER JOIN membre_documents d ON d.id=t.document_id
             WHERE t.token_hash=:token_hash
             LIMIT 1"
        );
        $stmt->execute([
            'token_hash'=>hash('sha256',$plainToken)
        ]);

        $row=$stmt->fetch() ?: null;

        if(!$row){
            return null;
        }

        if(
            $row['used_at']
            ||
            strtotime((string)$row['expires_at'])<time()
        ){
            return null;
        }

        return $row;
    }

    public static function markTokenUsed(int $tokenId): void
    {
        Database::connection()->prepare(
            "UPDATE consentement_tokens
             SET used_at=CURRENT_TIMESTAMP
             WHERE id=:id"
        )->execute(['id'=>$tokenId]);
    }
}
