<?php

class MailboxRepository
{
    private PDO $pdo;

    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }

    public function paginate(int $page = 1, int $limit = 20): array
    {
        $page = max(1, $page);
        $limit = max(1, min($limit, 100));

        $total = (int) $this->pdo->query('SELECT COUNT(*) FROM mailboxes')->fetchColumn();
        $totalPages = max(1, (int) ceil($total / $limit));
        $page = min($page, $totalPages);
        $offset = ($page - 1) * $limit;

        $stmt = $this->pdo->prepare(
            'SELECT
                id,
                name,
                email,
                username,
                host,
                port,
                protocol,
                encryption,
                is_active,
                is_default,
                notes,
                last_tested_at,
                last_test_status,
                last_test_message,
                last_sync_at,
                created_at,
                updated_at
             FROM mailboxes
             ORDER BY is_default DESC, is_active DESC, name ASC, id DESC
             LIMIT :limit OFFSET :offset'
        );
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        $stmt->execute();

        return [
            'items' => $stmt->fetchAll(),
            'pagination' => [
                'page' => $page,
                'limit' => $limit,
                'total' => $total,
                'totalPages' => $totalPages,
                'hasNext' => $page < $totalPages,
                'hasPrev' => $page > 1,
            ],
        ];
    }

    public function all(): array
    {
        return $this->paginate(1, 100000)['items'];
    }

    public function findById(int $id, bool $includeSecret = false): ?array
    {
        $columns = $includeSecret
            ? 'id, name, email, username, password_encrypted, host, port, protocol, encryption, is_active, is_default, notes, last_tested_at, last_test_status, last_test_message, last_sync_at, created_at, updated_at'
            : 'id, name, email, username, host, port, protocol, encryption, is_active, is_default, notes, last_tested_at, last_test_status, last_test_message, last_sync_at, created_at, updated_at';

        $stmt = $this->pdo->prepare("SELECT {$columns} FROM mailboxes WHERE id = :id LIMIT 1");
        $stmt->execute(['id' => $id]);
        $row = $stmt->fetch();

        return $row ?: null;
    }

    public function findByEmail(string $email, ?int $excludeId = null): ?array
    {
        $sql = 'SELECT id, email FROM mailboxes WHERE email = :email';
        $params = ['email' => $email];

        if ($excludeId) {
            $sql .= ' AND id != :exclude_id';
            $params['exclude_id'] = $excludeId;
        }

        $sql .= ' LIMIT 1';

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        $row = $stmt->fetch();

        return $row ?: null;
    }

    public function clearDefaultFlag(): void
    {
        $this->pdo->exec('UPDATE mailboxes SET is_default = 0 WHERE is_default = 1');
    }

    public function create(array $data): int
    {
        $stmt = $this->pdo->prepare(
            'INSERT INTO mailboxes (
                name,
                email,
                username,
                password_encrypted,
                host,
                port,
                protocol,
                encryption,
                is_active,
                is_default,
                notes,
                created_at,
                updated_at
            ) VALUES (
                :name,
                :email,
                :username,
                :password_encrypted,
                :host,
                :port,
                :protocol,
                :encryption,
                :is_active,
                :is_default,
                :notes,
                NOW(),
                NOW()
            )'
        );

        $stmt->execute([
            'name' => $data['name'],
            'email' => $data['email'],
            'username' => $data['username'],
            'password_encrypted' => $data['password_encrypted'],
            'host' => $data['host'],
            'port' => $data['port'],
            'protocol' => $data['protocol'],
            'encryption' => $data['encryption'],
            'is_active' => $data['is_active'],
            'is_default' => $data['is_default'],
            'notes' => $data['notes'],
        ]);

        return (int) $this->pdo->lastInsertId();
    }

    public function update(int $id, array $data): void
    {
        $sql = 'UPDATE mailboxes SET
            name = :name,
            email = :email,
            username = :username,
            host = :host,
            port = :port,
            protocol = :protocol,
            encryption = :encryption,
            is_active = :is_active,
            is_default = :is_default,
            notes = :notes,
            updated_at = NOW()';

        $params = [
            'id' => $id,
            'name' => $data['name'],
            'email' => $data['email'],
            'username' => $data['username'],
            'host' => $data['host'],
            'port' => $data['port'],
            'protocol' => $data['protocol'],
            'encryption' => $data['encryption'],
            'is_active' => $data['is_active'],
            'is_default' => $data['is_default'],
            'notes' => $data['notes'],
        ];

        if (!empty($data['password_encrypted'])) {
            $sql .= ', password_encrypted = :password_encrypted';
            $params['password_encrypted'] = $data['password_encrypted'];
        }

        $sql .= ' WHERE id = :id';

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
    }

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

    public function recordTestResult(int $id, bool $ok, string $messageKey): void
    {
        $stmt = $this->pdo->prepare(
            'UPDATE mailboxes
             SET
                last_tested_at = NOW(),
                last_test_status = :status,
                last_test_message = :message,
                updated_at = NOW()
             WHERE id = :id'
        );

        $stmt->execute([
            'id' => $id,
            'status' => $ok ? 'success' : 'failed',
            'message' => $messageKey,
        ]);
    }

    public function createSyncLog(int $mailboxId): int
    {
        $stmt = $this->pdo->prepare(
            'INSERT INTO mailbox_sync_logs (
                mailbox_id,
                status,
                message,
                imported_count,
                started_at,
                created_at
            ) VALUES (
                :mailbox_id,
                :status,
                :message,
                0,
                NOW(),
                NOW()
            )'
        );

        $stmt->execute([
            'mailbox_id' => $mailboxId,
            'status' => 'running',
            'message' => 'mailboxes.sync_started',
        ]);

        return (int) $this->pdo->lastInsertId();
    }

    public function finishSyncLog(int $logId, string $status, string $message, int $importedCount = 0): void
    {
        $stmt = $this->pdo->prepare(
            'UPDATE mailbox_sync_logs
             SET
                status = :status,
                message = :message,
                imported_count = :imported_count,
                finished_at = NOW()
             WHERE id = :id'
        );

        $stmt->execute([
            'id' => $logId,
            'status' => $status,
            'message' => $message,
            'imported_count' => $importedCount,
        ]);
    }

    public function touchLastSyncAt(int $mailboxId): void
    {
        $stmt = $this->pdo->prepare(
            'UPDATE mailboxes
             SET
                last_sync_at = NOW(),
                updated_at = NOW()
             WHERE id = :id'
        );

        $stmt->execute(['id' => $mailboxId]);
    }

    public function findInboxByEmailAddress(string $emailAddress): ?array
    {
        $stmt = $this->pdo->prepare('SELECT id, user_id, email_address, type, access_token, expires_at, is_active FROM inboxes WHERE email_address = :email_address AND is_active = 1 LIMIT 1');
        $stmt->execute(['email_address' => strtolower(trim($emailAddress))]);
        $row = $stmt->fetch();

        return $row ?: null;
    }

    public function ensureUserInbox(int $userId, string $emailAddress): ?array
    {
        $stmt = $this->pdo->prepare("SELECT id, user_id, email_address, type, access_token, expires_at, is_active FROM inboxes WHERE user_id = :user_id AND type = 'user' AND is_active = 1 LIMIT 1");
        $stmt->execute(['user_id' => $userId]);
        $row = $stmt->fetch();
        if ($row) {
            return $row;
        }

        $stmt = $this->pdo->prepare("INSERT INTO inboxes (user_id, email_address, type, access_token, expires_at, is_active, created_at, updated_at) VALUES (:user_id, :email_address, 'user', NULL, NULL, 1, NOW(), NOW())");
        $stmt->execute(['user_id' => $userId, 'email_address' => strtolower(trim($emailAddress))]);

        return [
            'id' => (int) $this->pdo->lastInsertId(),
            'user_id' => $userId,
            'email_address' => strtolower(trim($emailAddress)),
            'type' => 'user',
            'access_token' => null,
            'expires_at' => null,
            'is_active' => 1,
        ];
    }

    public function findMessageByUid(int $mailboxId, int $uid): ?array
    {
        $stmt = $this->pdo->prepare(
            'SELECT id, mailbox_id, inbox_id, uid
             FROM mailbox_messages
             WHERE mailbox_id = :mailbox_id AND uid = :uid
             LIMIT 1'
        );

        $stmt->execute([
            'mailbox_id' => $mailboxId,
            'uid' => $uid,
        ]);

        $row = $stmt->fetch();

        return $row ?: null;
    }

    public function createMessage(array $data): int
    {
        $stmt = $this->pdo->prepare(
            'INSERT INTO mailbox_messages (
                mailbox_id,
                inbox_id,
                uid,
                message_id,
                message_id_header,
                from_email,
                from_name,
                to_email,
                subject,
                text_body,
                html_body,
                raw_headers,
                raw_body,
                received_at,
                is_read,
                is_starred,
                is_deleted,
                created_at,
                updated_at
            ) VALUES (
                :mailbox_id,
                :inbox_id,
                :uid,
                :message_id,
                :message_id_header,
                :from_email,
                :from_name,
                :to_email,
                :subject,
                :text_body,
                :html_body,
                :raw_headers,
                :raw_body,
                :received_at,
                :is_read,
                :is_starred,
                :is_deleted,
                NOW(),
                NOW()
            )'
        );

        $stmt->execute([
            'mailbox_id' => $data['mailbox_id'],
            'inbox_id' => $data['inbox_id'] ?? null,
            'uid' => $data['uid'],
            'message_id' => $data['message_id'],
            'message_id_header' => $data['message_id_header'],
            'from_email' => $data['from_email'],
            'from_name' => $data['from_name'],
            'to_email' => $data['to_email'],
            'subject' => $data['subject'],
            'text_body' => $data['text_body'],
            'html_body' => $data['html_body'],
            'raw_headers' => $data['raw_headers'],
            'raw_body' => $data['raw_body'],
            'received_at' => $data['received_at'],
            'is_read' => $data['is_read'],
            'is_starred' => $data['is_starred'],
            'is_deleted' => $data['is_deleted'],
        ]);

        return (int) $this->pdo->lastInsertId();
    }

    public function updateMessageByUid(int $mailboxId, int $uid, array $data): void
    {
        $stmt = $this->pdo->prepare(
            'UPDATE mailbox_messages
             SET
                inbox_id = :inbox_id,
                message_id = :message_id,
                message_id_header = :message_id_header,
                from_email = :from_email,
                from_name = :from_name,
                to_email = :to_email,
                subject = :subject,
                text_body = :text_body,
                html_body = :html_body,
                raw_headers = :raw_headers,
                raw_body = :raw_body,
                received_at = :received_at,
                is_read = :is_read,
                is_starred = :is_starred,
                is_deleted = :is_deleted,
                updated_at = NOW()
             WHERE mailbox_id = :mailbox_id AND uid = :uid'
        );

        $stmt->execute([
            'mailbox_id' => $mailboxId,
            'uid' => $uid,
            'inbox_id' => $data['inbox_id'] ?? null,
            'message_id' => $data['message_id'],
            'message_id_header' => $data['message_id_header'],
            'from_email' => $data['from_email'],
            'from_name' => $data['from_name'],
            'to_email' => $data['to_email'],
            'subject' => $data['subject'],
            'text_body' => $data['text_body'],
            'html_body' => $data['html_body'],
            'raw_headers' => $data['raw_headers'],
            'raw_body' => $data['raw_body'],
            'received_at' => $data['received_at'],
            'is_read' => $data['is_read'],
            'is_starred' => $data['is_starred'],
            'is_deleted' => $data['is_deleted'],
        ]);
    }

    public function upsertMessageByUid(array $data): int
    {
        $existing = $this->findMessageByUid((int) $data['mailbox_id'], (int) $data['uid']);

        if ($existing) {
            $this->updateMessageByUid((int) $data['mailbox_id'], (int) $data['uid'], $data);
            return (int) $existing['id'];
        }

        return $this->createMessage($data);
    }

    public function replaceAttachmentsForMessage(int $messageId, array $attachments): void
    {
        $stmt = $this->pdo->prepare('DELETE FROM mailbox_message_attachments WHERE message_id = :message_id');
        $stmt->execute(['message_id' => $messageId]);

        if (!$attachments) {
            return;
        }

        $insert = $this->pdo->prepare(
            'INSERT INTO mailbox_message_attachments (
                message_id,
                mailbox_id,
                imap_part,
                filename,
                original_name,
                mime_type,
                extension,
                size,
                storage_path,
                public_url,
                content_id,
                disposition,
                is_inline,
                created_at
            ) VALUES (
                :message_id,
                :mailbox_id,
                :imap_part,
                :filename,
                :original_name,
                :mime_type,
                :extension,
                :size,
                :storage_path,
                :public_url,
                :content_id,
                :disposition,
                :is_inline,
                NOW()
            )'
        );

        foreach ($attachments as $attachment) {
            $insert->execute([
                'message_id' => $messageId,
                'mailbox_id' => $attachment['mailbox_id'],
                'imap_part' => $attachment['imap_part'],
                'filename' => $attachment['filename'],
                'original_name' => $attachment['original_name'],
                'mime_type' => $attachment['mime_type'],
                'extension' => $attachment['extension'],
                'size' => $attachment['size'],
                'storage_path' => $attachment['storage_path'],
                'public_url' => $attachment['public_url'],
                'content_id' => $attachment['content_id'],
                'disposition' => $attachment['disposition'],
                'is_inline' => $attachment['is_inline'],
            ]);
        }
    }

    public function getMessagesByMailboxId(int $mailboxId, int $limit = 50): array
    {
        return $this->getMessagesByMailboxIdPaginated($mailboxId, 1, $limit)['items'];
    }

    public function getMessagesByMailboxIdPaginated(int $mailboxId, int $page = 1, int $limit = 25): array
    {
        $page = max(1, $page);
        $limit = max(1, min($limit, 200));

        $countStmt = $this->pdo->prepare('SELECT COUNT(*) FROM mailbox_messages WHERE mailbox_id = :mailbox_id');
        $countStmt->execute(['mailbox_id' => $mailboxId]);
        $total = (int) $countStmt->fetchColumn();

        $totalPages = max(1, (int) ceil($total / $limit));
        $page = min($page, $totalPages);
        $offset = ($page - 1) * $limit;

        $stmt = $this->pdo->prepare(
            'SELECT
                m.id,
                m.mailbox_id,
                m.inbox_id,
                m.uid,
                m.message_id,
                m.message_id_header,
                m.from_email,
                m.from_name,
                m.to_email,
                m.subject,
                m.received_at,
                m.is_read,
                m.is_starred,
                m.is_deleted,
                m.created_at,
                m.updated_at,
                EXISTS(
                    SELECT 1
                    FROM mailbox_message_attachments a
                    WHERE a.message_id = m.id
                ) AS has_attachments
             FROM mailbox_messages m
             WHERE m.mailbox_id = :mailbox_id
             ORDER BY m.received_at DESC, m.id DESC
             LIMIT :limit OFFSET :offset'
        );

        $stmt->bindValue(':mailbox_id', $mailboxId, PDO::PARAM_INT);
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        $stmt->execute();

        return [
            'items' => $stmt->fetchAll(),
            'pagination' => [
                'page' => $page,
                'limit' => $limit,
                'total' => $total,
                'totalPages' => $totalPages,
                'hasNext' => $page < $totalPages,
                'hasPrev' => $page > 1,
            ],
        ];
    }

    public function getMessageById(int $mailboxId, int $messageId): ?array
    {
        $stmt = $this->pdo->prepare(
            'SELECT
                id,
                mailbox_id,
                inbox_id,
                uid,
                message_id,
                message_id_header,
                from_email,
                from_name,
                to_email,
                subject,
                text_body,
                html_body,
                raw_headers,
                raw_body,
                received_at,
                is_read,
                is_starred,
                is_deleted,
                created_at,
                updated_at
             FROM mailbox_messages
             WHERE mailbox_id = :mailbox_id AND id = :id
             LIMIT 1'
        );

        $stmt->execute([
            'mailbox_id' => $mailboxId,
            'id' => $messageId,
        ]);

        $row = $stmt->fetch();

        return $row ?: null;
    }

    public function markMessageAsRead(int $mailboxId, int $messageId): void
    {
        $stmt = $this->pdo->prepare(
            'UPDATE mailbox_messages
             SET is_read = 0, updated_at = NOW()
             WHERE mailbox_id = :mailbox_id AND id = :id'
        );

        $stmt->execute([
            'mailbox_id' => $mailboxId,
            'id' => $messageId,
        ]);
    }

    public function markMessageAsUnread(int $mailboxId, int $messageId): void
    {
        $stmt = $this->pdo->prepare(
            'UPDATE mailbox_messages
             SET is_read = 1, updated_at = NOW()
             WHERE mailbox_id = :mailbox_id AND id = :id'
        );

        $stmt->execute([
            'mailbox_id' => $mailboxId,
            'id' => $messageId,
        ]);
    }

    public function getMessageAttachmentsByMessageId(int $messageId): array
    {
        $stmt = $this->pdo->prepare(
            'SELECT
                id,
                message_id,
                mailbox_id,
                imap_part,
                filename,
                original_name,
                mime_type,
                extension,
                size,
                storage_path,
                public_url,
                content_id,
                disposition,
                is_inline,
                created_at
             FROM mailbox_message_attachments
             WHERE message_id = :message_id
             ORDER BY id ASC'
        );

        $stmt->execute(['message_id' => $messageId]);

        return $stmt->fetchAll();
    }

    public function getAttachmentById(int $messageId, int $attachmentId): ?array
    {
        $stmt = $this->pdo->prepare(
            'SELECT
                id,
                message_id,
                mailbox_id,
                imap_part,
                filename,
                original_name,
                mime_type,
                extension,
                size,
                storage_path,
                public_url,
                content_id,
                disposition,
                is_inline,
                created_at
             FROM mailbox_message_attachments
             WHERE message_id = :message_id AND id = :id
             LIMIT 1'
        );

        $stmt->execute([
            'message_id' => $messageId,
            'id' => $attachmentId,
        ]);

        $row = $stmt->fetch();

        return $row ?: null;
    }
    
    public function markMessageAsStarred(int $mailboxId, int $messageId): void
    {
        $stmt = $this->pdo->prepare(
            'UPDATE mailbox_messages
             SET is_starred = 1, updated_at = NOW()
             WHERE mailbox_id = :mailbox_id AND id = :id'
        );
    
        $stmt->execute([
            'mailbox_id' => $mailboxId,
            'id' => $messageId,
        ]);
    }
    
    public function markMessageAsUnstarred(int $mailboxId, int $messageId): void
    {
        $stmt = $this->pdo->prepare(
            'UPDATE mailbox_messages
             SET is_starred = 0, updated_at = NOW()
             WHERE mailbox_id = :mailbox_id AND id = :id'
        );
    
        $stmt->execute([
            'mailbox_id' => $mailboxId,
            'id' => $messageId,
        ]);
    }

}
