<?php
require_once __DIR__ . '/../Core.php';

class MailboxWebhookSubscription extends Model
{
    public function all(): array
    {
        $sql = 'SELECT mws.*, cm.name AS mailbox_name, cm.email_address, cm.provider
            FROM mailbox_webhook_subscriptions mws
            LEFT JOIN connected_mailboxes cm ON cm.id = mws.connected_mailbox_id
            ORDER BY COALESCE(mws.expires_at, "9999-12-31") ASC, mws.id DESC';
        return $this->db->query($sql)->fetchAll();
    }

    public function byMailbox(int $mailboxId): array
    {
        $stmt = $this->db->prepare('SELECT * FROM mailbox_webhook_subscriptions WHERE connected_mailbox_id = ? ORDER BY id DESC');
        $stmt->execute([$mailboxId]);
        return $stmt->fetchAll();
    }

    public function dueRenewal(): array
    {
        $stmt = $this->db->query('SELECT mws.*, cm.name AS mailbox_name, cm.email_address, cm.provider
            FROM mailbox_webhook_subscriptions mws
            LEFT JOIN connected_mailboxes cm ON cm.id = mws.connected_mailbox_id
            WHERE mws.expires_at IS NOT NULL AND mws.expires_at <= DATE_ADD(NOW(), INTERVAL 2 DAY)
            ORDER BY mws.expires_at ASC');
        return $stmt->fetchAll();
    }

    public function upsertForMailbox(int $mailboxId, array $data): int
    {
        $resourceScope = trim((string)($data['resource_scope'] ?? 'messages')) ?: 'messages';
        $existing = $this->findByMailboxAndScope($mailboxId, $resourceScope);
        $provider = trim((string)($data['provider'] ?? 'gmail')) ?: 'gmail';
        $resourceName = trim((string)($data['resource_name'] ?? 'mail')) ?: 'mail';
        $subscriptionId = trim((string)($data['subscription_id'] ?? '')) ?: null;
        $expiresAt = trim((string)($data['expires_at'] ?? '')) ?: null;
        $verificationState = trim((string)($data['verification_state'] ?? 'planned')) ?: 'planned';
        $metadataJson = json_encode($data['metadata_json'] ?? [], JSON_UNESCAPED_SLASHES);
        if ($existing) {
            $stmt = $this->db->prepare('UPDATE mailbox_webhook_subscriptions SET provider = ?, resource_name = ?, subscription_id = ?, expires_at = ?, verification_state = ?, metadata_json = ?, updated_at = NOW() WHERE id = ?');
            $stmt->execute([$provider, $resourceName, $subscriptionId, $expiresAt, $verificationState, $metadataJson, (int)$existing['id']]);
            return (int)$existing['id'];
        }
        $stmt = $this->db->prepare('INSERT INTO mailbox_webhook_subscriptions (connected_mailbox_id, provider, resource_name, resource_scope, subscription_id, expires_at, verification_state, metadata_json) VALUES (?, ?, ?, ?, ?, ?, ?, ?)');
        $stmt->execute([$mailboxId, $provider, $resourceName, $resourceScope, $subscriptionId, $expiresAt, $verificationState, $metadataJson]);
        return (int)$this->db->lastInsertId();
    }

    public function findByMailboxAndScope(int $mailboxId, string $scope): ?array
    {
        $stmt = $this->db->prepare('SELECT * FROM mailbox_webhook_subscriptions WHERE connected_mailbox_id = ? AND resource_scope = ? LIMIT 1');
        $stmt->execute([$mailboxId, $scope]);
        return $stmt->fetch() ?: null;
    }
}
