<?php

class InboxRepository
{
    private PDO $pdo;

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

    public function findInboxById(int $id): ?array
    {
        $stmt = $this->pdo->prepare('SELECT * FROM inboxes WHERE id = :id LIMIT 1');
        $stmt->execute(['id' => $id]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    public function findUserInboxByUserId(int $userId): ?array
    {
        $stmt = $this->pdo->prepare("SELECT * FROM inboxes WHERE user_id = :user_id AND type = 'user' AND is_active = 1 ORDER BY id ASC LIMIT 1");
        $stmt->execute(['user_id' => $userId]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    public function listActiveInboxesForUser(int $userId): array
    {
        $stmt = $this->pdo->prepare("SELECT * FROM inboxes WHERE user_id = :user_id AND is_active = 1 ORDER BY CASE WHEN type = 'user' THEN 0 WHEN type = 'alias' THEN 1 ELSE 2 END, id ASC");
        $stmt->execute(['user_id' => $userId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    public function findInboxByEmail(string $email): ?array
    {
        $stmt = $this->pdo->prepare('SELECT * FROM inboxes WHERE email_address = :email LIMIT 1');
        $stmt->execute(['email' => strtolower(trim($email))]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    public function findGuestInboxByToken(string $token): ?array
    {
        $stmt = $this->pdo->prepare("SELECT * FROM inboxes WHERE access_token = :access_token AND type = 'guest' LIMIT 1");
        $stmt->execute(['access_token' => $token]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    public function createInbox(array $data): array
    {
        $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, :type, :access_token, :expires_at, :is_active, NOW(), NOW())'
        );
        $stmt->execute([
            'user_id' => $data['user_id'],
            'email_address' => strtolower(trim($data['email_address'])),
            'type' => $data['type'],
            'access_token' => $data['access_token'],
            'expires_at' => $data['expires_at'],
            'is_active' => (int) ($data['is_active'] ?? 1),
        ]);

        return $this->findInboxById((int) $this->pdo->lastInsertId()) ?? [];
    }

    public function deactivateInbox(int $id): void
    {
        $stmt = $this->pdo->prepare('UPDATE inboxes SET is_active = 0, updated_at = NOW() WHERE id = :id');
        $stmt->execute(['id' => $id]);
    }

    public function createUserInboxIfMissing(int $userId, string $emailAddress): array
    {
        $emailAddress = strtolower(trim($emailAddress));
        $existing = $this->findUserInboxByUserId($userId);

        if ($existing) {
            if (strtolower((string) $existing['email_address']) !== $emailAddress) {
                $stmt = $this->pdo->prepare("UPDATE inboxes SET email_address = :email_address, updated_at = NOW() WHERE id = :id");
                $stmt->execute(['email_address' => $emailAddress, 'id' => $existing['id']]);
                $existing = $this->findInboxById((int) $existing['id']);
            }
            return $existing ?: [];
        }

        $byEmail = $this->findInboxByEmail($emailAddress);
        if ($byEmail && ((int) ($byEmail['user_id'] ?? 0) === 0 || (int) ($byEmail['user_id'] ?? 0) === $userId)) {
            $stmt = $this->pdo->prepare("UPDATE inboxes SET user_id = :user_id, type = 'user', access_token = NULL, expires_at = NULL, is_active = 1, updated_at = NOW() WHERE id = :id");
            $stmt->execute(['user_id' => $userId, 'id' => $byEmail['id']]);
            return $this->findInboxById((int) $byEmail['id']) ?: [];
        }

        return $this->createInbox([
            'user_id' => $userId,
            'email_address' => $emailAddress,
            'type' => 'user',
            'access_token' => null,
            'expires_at' => null,
            'is_active' => 1,
        ]);
    }

    public function createGuestInbox(string $emailAddress, string $token, string $expiresAt): array
    {
        return $this->createInbox([
            'user_id' => null,
            'email_address' => strtolower($emailAddress),
            'type' => 'guest',
            'access_token' => $token,
            'expires_at' => $expiresAt,
            'is_active' => 1,
        ]);
    }

    public function transferGuestInboxToUser(int $guestInboxId, int $userId): void
    {
        $stmt = $this->pdo->prepare("UPDATE inboxes SET user_id = :user_id, type = 'alias', access_token = NULL, expires_at = NULL, is_active = 1, updated_at = NOW() WHERE id = :id");
        $stmt->execute([
            'user_id' => $userId,
            'id' => $guestInboxId,
        ]);
    }

    private function normalizeInboxIds(array $inboxIds): array
    {
        $ids = array_values(array_unique(array_filter(array_map('intval', $inboxIds), static fn ($id) => $id > 0)));
        return $ids;
    }

    private function inboxPlaceholders(array $inboxIds): string
    {
        return implode(',', array_fill(0, count($inboxIds), '?'));
    }

    public function listInboxMessages(int $inboxId, int $page = 1, int $limit = 25): array
    {
        return $this->listMessagesForInboxIds([$inboxId], $page, $limit);
    }

    public function listMessagesForInboxIds(array $inboxIds, int $page = 1, int $limit = 25): array
    {
        $inboxIds = $this->normalizeInboxIds($inboxIds);
        $page = max(1, $page);
        $limit = max(1, min($limit, 100));

        if (empty($inboxIds)) {
            return [
                'items' => [],
                'pagination' => [
                    'page' => 1,
                    'limit' => $limit,
                    'total' => 0,
                    'totalPages' => 1,
                    'hasNext' => false,
                    'hasPrev' => false,
                ],
            ];
        }

        $placeholders = $this->inboxPlaceholders($inboxIds);

        $countStmt = $this->pdo->prepare("SELECT COUNT(*) FROM mailbox_messages WHERE inbox_id IN ({$placeholders}) AND is_deleted = 0");
        foreach ($inboxIds as $index => $id) {
            $countStmt->bindValue($index + 1, $id, PDO::PARAM_INT);
        }
        $countStmt->execute();
        $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.inbox_id,
                i.email_address AS inbox_email_address,
                i.type AS inbox_type,
                m.mailbox_id,
                m.uid,
                m.message_id,
                m.message_id_header,
                m.from_email,
                m.from_name,
                m.to_email,
                m.subject,
                m.text_body,
                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
             LEFT JOIN inboxes i ON i.id = m.inbox_id
             WHERE m.inbox_id IN ({$placeholders}) AND m.is_deleted = 0
             ORDER BY m.received_at DESC, m.id DESC
             LIMIT ? OFFSET ?"
        );

        $paramIndex = 1;
        foreach ($inboxIds as $id) {
            $stmt->bindValue($paramIndex++, $id, PDO::PARAM_INT);
        }
        $stmt->bindValue($paramIndex++, $limit, PDO::PARAM_INT);
        $stmt->bindValue($paramIndex, $offset, PDO::PARAM_INT);
        $stmt->execute();

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

    public function getInboxMessageById(int $inboxId, int $messageId): ?array
    {
        return $this->getMessageByIdForInboxIds([$inboxId], $messageId);
    }

    public function getMessageByIdForInboxIds(array $inboxIds, int $messageId): ?array
    {
        $inboxIds = $this->normalizeInboxIds($inboxIds);
        if (empty($inboxIds)) {
            return null;
        }

        $placeholders = $this->inboxPlaceholders($inboxIds);
        $stmt = $this->pdo->prepare("SELECT m.*, i.email_address AS inbox_email_address, i.type AS inbox_type FROM mailbox_messages m LEFT JOIN inboxes i ON i.id = m.inbox_id WHERE m.inbox_id IN ({$placeholders}) AND m.id = ? LIMIT 1");

        $paramIndex = 1;
        foreach ($inboxIds as $id) {
            $stmt->bindValue($paramIndex++, $id, PDO::PARAM_INT);
        }
        $stmt->bindValue($paramIndex, $messageId, PDO::PARAM_INT);
        $stmt->execute();
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    public function getMessageAttachmentsByMessageId(int $messageId): array
    {
        $stmt = $this->pdo->prepare('SELECT * FROM mailbox_message_attachments WHERE message_id = :message_id ORDER BY id ASC');
        $stmt->execute(['message_id' => $messageId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    public function markMessageReadState(int $inboxId, int $messageId, int $isRead): void
    {
        $this->markMessageReadStateForInboxIds([$inboxId], $messageId, $isRead);
    }

    public function markMessageReadStateForInboxIds(array $inboxIds, int $messageId, int $isRead): void
    {
        $inboxIds = $this->normalizeInboxIds($inboxIds);
        if (empty($inboxIds)) {
            return;
        }

        $placeholders = $this->inboxPlaceholders($inboxIds);
        $stmt = $this->pdo->prepare("UPDATE mailbox_messages SET is_read = ?, updated_at = NOW() WHERE inbox_id IN ({$placeholders}) AND id = ?");
        $paramIndex = 1;
        $stmt->bindValue($paramIndex++, $isRead, PDO::PARAM_INT);
        foreach ($inboxIds as $id) {
            $stmt->bindValue($paramIndex++, $id, PDO::PARAM_INT);
        }
        $stmt->bindValue($paramIndex, $messageId, PDO::PARAM_INT);
        $stmt->execute();
    }

    public function markMessageStarState(int $inboxId, int $messageId, int $isStarred): void
    {
        $this->markMessageStarStateForInboxIds([$inboxId], $messageId, $isStarred);
    }

    public function markMessageStarStateForInboxIds(array $inboxIds, int $messageId, int $isStarred): void
    {
        $inboxIds = $this->normalizeInboxIds($inboxIds);
        if (empty($inboxIds)) {
            return;
        }

        $placeholders = $this->inboxPlaceholders($inboxIds);
        $stmt = $this->pdo->prepare("UPDATE mailbox_messages SET is_starred = ?, updated_at = NOW() WHERE inbox_id IN ({$placeholders}) AND id = ?");
        $paramIndex = 1;
        $stmt->bindValue($paramIndex++, $isStarred, PDO::PARAM_INT);
        foreach ($inboxIds as $id) {
            $stmt->bindValue($paramIndex++, $id, PDO::PARAM_INT);
        }
        $stmt->bindValue($paramIndex, $messageId, PDO::PARAM_INT);
        $stmt->execute();
    }

    public function cleanupExpiredGuestInboxes(): int
    {
        $stmt = $this->pdo->prepare("UPDATE inboxes SET is_active = 0, updated_at = NOW() WHERE type = 'guest' AND user_id IS NULL AND is_active = 1 AND expires_at IS NOT NULL AND expires_at < NOW()");
        $stmt->execute();
        return $stmt->rowCount();
    }
}
