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

class ConnectedMailbox extends Model
{
    public function all(): array
    {
        $sql = 'SELECT cm.*, u.name AS created_by_name,
            (SELECT COUNT(*) FROM mailbox_webhook_subscriptions mws WHERE mws.connected_mailbox_id = cm.id) AS subscription_count
            FROM connected_mailboxes cm
            LEFT JOIN users u ON u.id = cm.created_by
            ORDER BY cm.is_active DESC, cm.provider ASC, cm.email_address ASC';
        return $this->db->query($sql)->fetchAll();
    }

    public function active(): array
    {
        $stmt = $this->db->query('SELECT * FROM connected_mailboxes WHERE is_active = 1 ORDER BY provider ASC, email_address ASC');
        return $stmt->fetchAll();
    }

    public function find(int $id): ?array
    {
        $stmt = $this->db->prepare('SELECT * FROM connected_mailboxes WHERE id = ? LIMIT 1');
        $stmt->execute([$id]);
        return $stmt->fetch() ?: null;
    }

    public function save(array $data): int
    {
        $id = (int)($data['id'] ?? 0);
        $provider = trim((string)($data['provider'] ?? 'gmail')) ?: 'gmail';
        $name = trim((string)($data['name'] ?? '')) ?: ucfirst($provider) . ' mailbox';
        $email = normalize_address((string)($data['email_address'] ?? ''));
        if ($email === '') {
            throw new RuntimeException('Mailbox email address is required.');
        }
        $scopeJson = $this->encodeJson($data['scope_json'] ?? [
            'mail.read', 'mail.send', 'mail.modify', 'contacts.read'
        ]);
        $oauthStatus = trim((string)($data['oauth_status'] ?? 'planned')) ?: 'planned';
        $syncState = trim((string)($data['sync_state'] ?? 'idle')) ?: 'idle';
        $tokenExpiresAt = trim((string)($data['token_expires_at'] ?? '')) ?: null;
        $subscriptionExpiresAt = trim((string)($data['subscription_expires_at'] ?? '')) ?: null;
        $historyCursor = trim((string)($data['history_cursor'] ?? '')) ?: null;
        $deltaLink = trim((string)($data['delta_link'] ?? '')) ?: null;
        $watchTopic = trim((string)($data['watch_topic'] ?? '')) ?: null;
        $subscriptionId = trim((string)($data['subscription_id'] ?? '')) ?: null;
        $accountIdentifier = trim((string)($data['account_identifier'] ?? '')) ?: null;
        $webhookEndpoint = trim((string)($data['webhook_endpoint'] ?? '')) ?: null;
        $accessToken = trim((string)($data['access_token'] ?? '')) ?: null;
        $refreshToken = trim((string)($data['refresh_token'] ?? '')) ?: null;
        $lastSyncedAt = trim((string)($data['last_synced_at'] ?? '')) ?: null;
        $lastError = trim((string)($data['last_error'] ?? '')) ?: null;
        $isActive = !empty($data['is_active']) ? 1 : 0;

        if ($id > 0) {
            $stmt = $this->db->prepare('UPDATE connected_mailboxes SET provider = ?, name = ?, email_address = ?, account_identifier = ?, scope_json = ?, oauth_status = ?, sync_state = ?, access_token = ?, refresh_token = ?, token_expires_at = ?, history_cursor = ?, delta_link = ?, watch_topic = ?, webhook_endpoint = ?, subscription_id = ?, subscription_expires_at = ?, last_synced_at = ?, last_error = ?, is_active = ?, updated_at = NOW() WHERE id = ?');
            $stmt->execute([$provider, $name, $email, $accountIdentifier, $scopeJson, $oauthStatus, $syncState, $accessToken, $refreshToken, $tokenExpiresAt, $historyCursor, $deltaLink, $watchTopic, $webhookEndpoint, $subscriptionId, $subscriptionExpiresAt, $lastSyncedAt, $lastError, $isActive, $id]);
            return $id;
        }

        $stmt = $this->db->prepare('INSERT INTO connected_mailboxes (provider, name, email_address, account_identifier, scope_json, oauth_status, sync_state, access_token, refresh_token, token_expires_at, history_cursor, delta_link, watch_topic, webhook_endpoint, subscription_id, subscription_expires_at, last_synced_at, last_error, is_active, created_by) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)');
        $stmt->execute([$provider, $name, $email, $accountIdentifier, $scopeJson, $oauthStatus, $syncState, $accessToken, $refreshToken, $tokenExpiresAt, $historyCursor, $deltaLink, $watchTopic, $webhookEndpoint, $subscriptionId, $subscriptionExpiresAt, $lastSyncedAt, $lastError, $isActive, (int)(current_user()['id'] ?? 0) ?: null]);
        return (int)$this->db->lastInsertId();
    }

    public function delete(int $id): void
    {
        $stmt = $this->db->prepare('DELETE FROM connected_mailboxes WHERE id = ?');
        $stmt->execute([$id]);
    }

    public function stats(): array
    {
        $row = $this->db->query('SELECT COUNT(*) total_count,
            SUM(is_active = 1) active_count,
            SUM(provider = "gmail") gmail_count,
            SUM(provider = "microsoft365") microsoft_count,
            SUM(oauth_status = "connected") connected_count,
            SUM(sync_state = "error") error_count,
            SUM(subscription_expires_at IS NOT NULL AND subscription_expires_at <= DATE_ADD(NOW(), INTERVAL 2 DAY)) renewal_due_count,
            MAX(last_synced_at) last_synced_at
            FROM connected_mailboxes')->fetch();
        return $row ?: [
            'total_count' => 0,
            'active_count' => 0,
            'gmail_count' => 0,
            'microsoft_count' => 0,
            'connected_count' => 0,
            'error_count' => 0,
            'renewal_due_count' => 0,
            'last_synced_at' => null,
        ];
    }

    public function queueSync(int $id, string $reason = 'manual'): int
    {
        $mailbox = $this->find($id);
        if (!$mailbox) {
            throw new RuntimeException('Mailbox not found.');
        }
        $stmt = $this->db->prepare('UPDATE connected_mailboxes SET sync_state = "queued", updated_at = NOW() WHERE id = ?');
        $stmt->execute([$id]);
        return (new QueueJob())->create('mailbox_sync', ['connected_mailbox_id' => $id, 'reason' => $reason], null);
    }

    public function queueSubscriptionRenewal(int $id): int
    {
        $mailbox = $this->find($id);
        if (!$mailbox) {
            throw new RuntimeException('Mailbox not found.');
        }
        return (new QueueJob())->create('mailbox_subscription_renew', ['connected_mailbox_id' => $id], null);
    }

    public function updateSyncResult(int $id, array $updates): void
    {
        $allowed = [
            'oauth_status', 'sync_state', 'token_expires_at', 'history_cursor', 'delta_link', 'subscription_id',
            'subscription_expires_at', 'last_synced_at', 'last_error', 'watch_topic', 'webhook_endpoint'
        ];
        $set = [];
        $params = [];
        foreach ($allowed as $column) {
            if (array_key_exists($column, $updates)) {
                $set[] = $column . ' = ?';
                $params[] = $updates[$column] === '' ? null : $updates[$column];
            }
        }
        if (!$set) {
            return;
        }
        $params[] = $id;
        $sql = 'UPDATE connected_mailboxes SET ' . implode(', ', $set) . ', updated_at = NOW() WHERE id = ?';
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
    }

    private function encodeJson($value): string
    {
        if (is_string($value)) {
            $decoded = json_decode($value, true);
            if (is_array($decoded)) {
                $value = $decoded;
            } else {
                $value = preg_split('/\s*,\s*/', trim($value), -1, PREG_SPLIT_NO_EMPTY) ?: [];
            }
        }
        return json_encode(is_array($value) ? array_values($value) : [], JSON_UNESCAPED_SLASHES);
    }
}
