<?php
declare(strict_types=1);

namespace App\Models;

final class Signature extends BaseModel
{
    protected static string $table = 'signatures';

    public static function forDocument(int $documentId): ?array
    {
        return static::db()->fetch(
            'SELECT * FROM signatures WHERE tenant_id = :tid AND document_id = :id ORDER BY id DESC LIMIT 1',
            ['tid' => static::tenantId(), 'id' => $documentId]
        );
    }

    public static function forRelated(string $type, int $id): ?array
    {
        return static::db()->fetch(
            "SELECT s.*, d.file_name AS original_file
             FROM signatures s
             INNER JOIN portal_documents d ON d.id = s.document_id
             WHERE s.tenant_id = :tid AND d.related_type = :t AND d.related_id = :id
             ORDER BY FIELD(s.status, 'signed', 'pending', 'declined'), s.id DESC
             LIMIT 1",
            ['tid' => static::tenantId(), 't' => $type, 'id' => $id]
        );
    }

    public static function pendingForAccount(int $accountId): array
    {
        return static::db()->fetchAll(
            "SELECT s.*, d.title, d.body, d.kind
             FROM signatures s
             INNER JOIN portal_documents d ON d.id = s.document_id
             WHERE s.tenant_id = :tid AND s.account_id = :aid AND s.status = 'pending'
             ORDER BY s.created_at",
            ['tid' => static::tenantId(), 'aid' => $accountId]
        );
    }
}
