<?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);
        $existing = $id > 0 ? $this->find($id) : null;
        $provider = trim((string)($data['provider'] ?? ($existing['provider'] ?? 'gmail'))) ?: 'gmail';
        $name = trim((string)($data['name'] ?? ($existing['name'] ?? ''))) ?: ucfirst($provider) . ' mailbox';
        $email = normalize_address((string)($data['email_address'] ?? ($existing['email_address'] ?? '')));
        if ($email === '') {
            throw new RuntimeException('Mailbox email address is required.');
        }
        $scopeJson = $this->encodeJson($data['scope_json'] ?? ($existing['scope_json'] ?? [
            'mail.read', 'mail.send', 'mail.modify', 'contacts.read'
        ]));
        $oauthStatus = trim((string)($data['oauth_status'] ?? ($existing['oauth_status'] ?? 'planned'))) ?: 'planned';
        $syncState = trim((string)($data['sync_state'] ?? ($existing['sync_state'] ?? 'idle'))) ?: 'idle';
        $syncMode = trim((string)($data['sync_mode'] ?? ($existing['sync_mode'] ?? 'imap_bridge'))) ?: 'imap_bridge';
        $tokenExpiresAt = $this->nullIfEmpty($data['token_expires_at'] ?? ($existing['token_expires_at'] ?? null));
        $subscriptionExpiresAt = $this->nullIfEmpty($data['subscription_expires_at'] ?? ($existing['subscription_expires_at'] ?? null));
        $historyCursor = $this->nullIfEmpty($data['history_cursor'] ?? ($existing['history_cursor'] ?? null));
        $deltaLink = $this->nullIfEmpty($data['delta_link'] ?? ($existing['delta_link'] ?? null));
        $watchTopic = $this->nullIfEmpty($data['watch_topic'] ?? ($existing['watch_topic'] ?? null));
        $subscriptionId = $this->nullIfEmpty($data['subscription_id'] ?? ($existing['subscription_id'] ?? null));
        $accountIdentifier = $this->nullIfEmpty($data['account_identifier'] ?? ($existing['account_identifier'] ?? null));
        $webhookEndpoint = $this->nullIfEmpty($data['webhook_endpoint'] ?? ($existing['webhook_endpoint'] ?? null));
        $accessToken = $this->preserveSensitive($data, 'access_token', $existing['access_token'] ?? null);
        $refreshToken = $this->preserveSensitive($data, 'refresh_token', $existing['refresh_token'] ?? null);
        $lastSyncedAt = $this->nullIfEmpty($data['last_synced_at'] ?? ($existing['last_synced_at'] ?? null));
        $lastError = $this->nullIfEmpty($data['last_error'] ?? ($existing['last_error'] ?? null));
        $isActive = !empty($data['is_active']) ? 1 : 0;
        $imapHost = $this->nullIfEmpty($data['imap_host'] ?? ($existing['imap_host'] ?? null));
        $imapPort = (int)($data['imap_port'] ?? ($existing['imap_port'] ?? 993));
        $imapFlags = $this->nullIfEmpty($data['imap_flags'] ?? ($existing['imap_flags'] ?? '/imap/ssl/novalidate-cert'));
        $imapUsername = $this->nullIfEmpty($data['imap_username'] ?? ($existing['imap_username'] ?? null));
        $imapPassword = $this->preserveSensitive($data, 'imap_password', $existing['imap_password'] ?? null);
        $imapMailbox = $this->nullIfEmpty($data['imap_mailbox'] ?? ($existing['imap_mailbox'] ?? 'INBOX')) ?: 'INBOX';
        $lastMessageUid = (int)($data['last_message_uid'] ?? ($existing['last_message_uid'] ?? 0));
        $importedMessageCount = (int)($data['imported_message_count'] ?? ($existing['imported_message_count'] ?? 0));
        $lastImportedAt = $this->nullIfEmpty($data['last_imported_at'] ?? ($existing['last_imported_at'] ?? null));

        if ($id > 0) {
            $stmt = $this->db->prepare('UPDATE connected_mailboxes SET provider = ?, name = ?, email_address = ?, account_identifier = ?, scope_json = ?, oauth_status = ?, sync_state = ?, sync_mode = ?, 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 = ?, imap_host = ?, imap_port = ?, imap_flags = ?, imap_username = ?, imap_password = ?, imap_mailbox = ?, last_message_uid = ?, imported_message_count = ?, last_imported_at = ?, updated_at = NOW() WHERE id = ?');
            $stmt->execute([$provider, $name, $email, $accountIdentifier, $scopeJson, $oauthStatus, $syncState, $syncMode, $accessToken, $refreshToken, $tokenExpiresAt, $historyCursor, $deltaLink, $watchTopic, $webhookEndpoint, $subscriptionId, $subscriptionExpiresAt, $lastSyncedAt, $lastError, $isActive, $imapHost, $imapPort, $imapFlags, $imapUsername, $imapPassword, $imapMailbox, $lastMessageUid, $importedMessageCount, $lastImportedAt, $id]);
            return $id;
        }

        $stmt = $this->db->prepare('INSERT INTO connected_mailboxes (provider, name, email_address, account_identifier, scope_json, oauth_status, sync_state, sync_mode, 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, imap_host, imap_port, imap_flags, imap_username, imap_password, imap_mailbox, last_message_uid, imported_message_count, last_imported_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)');
        $stmt->execute([$provider, $name, $email, $accountIdentifier, $scopeJson, $oauthStatus, $syncState, $syncMode, $accessToken, $refreshToken, $tokenExpiresAt, $historyCursor, $deltaLink, $watchTopic, $webhookEndpoint, $subscriptionId, $subscriptionExpiresAt, $lastSyncedAt, $lastError, $isActive, (int)(current_user()['id'] ?? 0) ?: null, $imapHost, $imapPort, $imapFlags, $imapUsername, $imapPassword, $imapMailbox, $lastMessageUid, $importedMessageCount, $lastImportedAt]);
        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(sync_mode = "imap_bridge") imap_bridge_count,
            SUM(sync_mode = "api_foundation") api_foundation_count,
            SUM(subscription_expires_at IS NOT NULL AND subscription_expires_at <= DATE_ADD(NOW(), INTERVAL 2 DAY)) renewal_due_count,
            SUM(imported_message_count) imported_total,
            MAX(last_synced_at) last_synced_at,
            MAX(last_imported_at) last_imported_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,
            'imap_bridge_count' => 0,
            'api_foundation_count' => 0,
            'renewal_due_count' => 0,
            'imported_total' => 0,
            'last_synced_at' => null,
            'last_imported_at' => null,
        ];
    }

    public function providerPresets(): array
    {
        return [
            'gmail' => [
                'sync_mode' => 'imap_bridge',
                'imap_host' => 'imap.gmail.com',
                'imap_port' => 993,
                'imap_flags' => '/imap/ssl/novalidate-cert',
                'imap_mailbox' => 'INBOX',
                'watch_topic' => 'projects/your-project/topics/gmail-mail-sync',
            ],
            'microsoft365' => [
                'sync_mode' => 'imap_bridge',
                'imap_host' => 'outlook.office365.com',
                'imap_port' => 993,
                'imap_flags' => '/imap/ssl/novalidate-cert',
                'imap_mailbox' => 'INBOX',
                'webhook_endpoint' => 'https://your-app.example.com/index.php?route=webhook/mailbox',
            ],
        ];
    }

    public function queueSync(int $id, string $reason = 'manual'): int
    {
        $mailbox = $this->find($id);
        if (!$mailbox) {
            throw new RuntimeException('Mailbox not found.');
        }
        $existingJob = $this->pendingJobId($id, 'mailbox_sync');
        if ($existingJob !== null) {
            return $existingJob;
        }
        $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.');
        }
        $existingJob = $this->pendingJobId($id, 'mailbox_subscription_renew');
        if ($existingJob !== null) {
            return $existingJob;
        }
        return (new QueueJob())->create('mailbox_subscription_renew', ['connected_mailbox_id' => $id], null);
    }

    public function queueAllActive(string $reason = 'bulk_manual'): int
    {
        $count = 0;
        foreach ($this->active() as $mailbox) {
            try {
                $this->queueSync((int)$mailbox['id'], $reason);
                $count++;
            } catch (Throwable $e) {
            }
        }
        return $count;
    }

    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',
            'last_message_uid', 'imported_message_count', 'last_imported_at', 'sync_mode', 'imap_host', 'imap_port',
            'imap_flags', 'imap_username', 'imap_password', 'imap_mailbox'
        ];
        $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 pendingJobId(int $mailboxId, string $jobType): ?int
    {
        $stmt = $this->db->prepare('SELECT * FROM queue_jobs WHERE job_type = ? AND status IN ("queued", "processing") ORDER BY id DESC');
        $stmt->execute([$jobType]);
        foreach ($stmt->fetchAll() as $job) {
            $payload = json_decode((string)($job['payload_json'] ?? ''), true) ?: [];
            if ((int)($payload['connected_mailbox_id'] ?? 0) === $mailboxId) {
                return (int)$job['id'];
            }
        }
        return null;
    }

    private function preserveSensitive(array $data, string $key, mixed $fallback): mixed
    {
        if (!array_key_exists($key, $data)) {
            return $fallback;
        }
        $value = trim((string)$data[$key]);
        if ($value === '') {
            return $fallback;
        }
        return $value;
    }

    private function nullIfEmpty(mixed $value): ?string
    {
        $value = trim((string)$value);
        return $value !== '' ? $value : null;
    }

    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);
    }
}
