邮件管理

mailbox.sql

src/main/resources/sql-templates/mailbox.sql

--# mail.baseColumns
-- 邮件实例和消息的核心字段
m.id,
m.uid,
m.internal_date,
msg.raw_content,
msg.size_in_bytes

--# mailbox.user.findByUsername
-- 根据用户名查找未删除的用户ID
SELECT * FROM mw_user WHERE username = ? AND deleted = 0;

--# mailbox.message.findByHash
-- 根据内容哈希查找已存在的邮件消息
SELECT id FROM mw_mail_message WHERE content_hash = ?;

--# mailbox.updateUidNextAndGet
-- 原子性地将uid_next加1，并返回更新前的uid_next值作为新邮件的UID
UPDATE mw_mailbox SET uid_next = uid_next + 1 WHERE id = ? RETURNING uid_next - 1 AS next_uid;

--# mailbox.getMessageByUid
-- 根据 UID 获取单封邮件，包含聚合后的标志
SELECT
  --#include(mail.baseColumns),
  COALESCE(
    (SELECT ARRAY_AGG(f.flag) FROM mw_mail_flag f WHERE f.mail_id = m.id),
    '{}'
  ) as flags
FROM
  mw_mail m
JOIN
  mw_mail_message msg ON m.message_id = msg.id
WHERE
  m.mailbox_id = ? AND m.uid = ? AND m.deleted = 0

--# mailbox.flags.addBatch
INSERT INTO mw_mail_flag (id,mail_id, flag) VALUES (?,?,?) ON CONFLICT (mail_id, flag) DO NOTHING;

--# mailbox.flags.removeBatch
-- 批量删除标志，%s 会被Java代码动态替换为占位符 ?, ?, ...
DELETE FROM mw_mail_flag WHERE mail_id = ? AND flag IN (%s);

--# mailbox.flags.clearRecent
-- 清除指定邮箱的所有 \Recent 标志
DELETE FROM mw_mail_flag WHERE flag = '\Recent' AND mail_id IN (SELECT id FROM mw_mail WHERE mailbox_id = ?);

--# mailbox.findEmails.baseQuery
SELECT
  m.id, m.uid, m.internal_date,
  msg.raw_content, msg.size_in_bytes,
  ARRAY_AGG(mf.flag) FILTER (WHERE mf.flag IS NOT NULL) as flags
FROM mw_mail m
JOIN mw_mail_message msg ON m.message_id = msg.id
LEFT JOIN mw_mail_flag mf ON m.id = mf.mail_id
WHERE m.mailbox_id = ?
  AND (%s)
  AND NOT EXISTS (
    SELECT 1 FROM mw_mail_flag del_mf WHERE del_mf.mail_id = m.id AND del_mf.flag = '\Deleted'
  )
GROUP BY m.id, msg.id
ORDER BY m.uid ASC;

--# mailbox.findEmails.bySeqSet
-- 使用窗口函数 ROW_NUMBER() 来按序号过滤邮件
-- %s 将被替换为具体的序号过滤条件 (e.g., seq_num IN (1,5) OR seq_num BETWEEN 10 AND 20)
WITH ranked_emails AS (
  SELECT
    m.id,
    m.uid,
    m.internal_date,
    m.message_id,
    -- 在未删除的邮件中计算序号
    ROW_NUMBER() OVER (ORDER BY m.uid ASC) as seq_num
  FROM mw_mail m
  WHERE m.mailbox_id = ?
    AND NOT EXISTS (
      SELECT 1 FROM mw_mail_flag del_mf WHERE del_mf.mail_id = m.id AND del_mf.flag = '\Deleted'
    )
)
SELECT
  r.id, r.uid, r.internal_date,
  msg.raw_content, msg.size_in_bytes,
  ARRAY_AGG(mf.flag) FILTER (WHERE mf.flag IS NOT NULL) as flags
FROM ranked_emails r
JOIN mw_mail_message msg ON r.message_id = msg.id
LEFT JOIN mw_mail_flag mf ON r.id = mf.mail_id
WHERE %s -- 动态序号条件占位符
GROUP BY r.id, r.uid, r.internal_date, msg.id
ORDER BY r.uid ASC;

--# mailbox.getMaxUid
-- 获取邮箱中最大的UID，用于处理 * 通配符
SELECT MAX(uid) FROM mw_mail WHERE mailbox_id = ?;

--# mailbox.getStat
-- 直接在数据库中计算邮件数量和总大小
SELECT
  COUNT(*) as message_count,
  COALESCE(SUM(msg.size_in_bytes), 0) as total_size
FROM mw_mail m
JOIN mw_mail_message msg ON m.message_id = msg.id
WHERE m.mailbox_id = ?
  AND NOT EXISTS (
    SELECT 1 FROM mw_mail_flag del_mf WHERE del_mf.mail_id = m.id AND del_mf.flag = '\Deleted'
  );
  
--# mailbox.baseRankedEmailsCTE
-- 这个SQL块现在既可以被独立获取，也可以被其他块包含
-- 它定义了一个公共表表达式（CTE）
WITH ranked_emails AS (
  SELECT
    m.id, m.uid, m.internal_date, m.message_id,
    ROW_NUMBER() OVER (ORDER BY m.uid ASC) as seq_num
  FROM mw_mail m
  WHERE m.mailbox_id = ?
    AND NOT EXISTS (
      SELECT 1 FROM mw_mail_flag del_mf WHERE del_mf.mail_id = m.id AND del_mf.flag = '\Deleted'
    )
)
--# mailbox.getActiveMailFlags
SELECT
  m.id,
  m.uid,
  m.internal_date,
  COALESCE(
    (SELECT ARRAY_AGG(f.flag) FROM mw_mail_flag f WHERE f.mail_id = m.id),
    '{}'
  ) as flags
FROM
  mw_mail m
JOIN
  mw_mail_message msg ON m.message_id = msg.id
WHERE
  m.mailbox_id = ? AND m.deleted = 0
  AND NOT EXISTS (
    SELECT 1 FROM mw_mail_flag f WHERE f.mail_id = m.id AND f.flag = '\\Deleted'
  )
ORDER BY
  m.uid ASC
  
--# mailbox.getActiveMessages
-- 获取一个邮箱中所有未删除的邮件，包含聚合后的标志
SELECT
  --#include(mail.baseColumns),
  -- 这里也需要聚合标志
  COALESCE(
    (SELECT ARRAY_AGG(f.flag) FROM mw_mail_flag f WHERE f.mail_id = m.id),
    '{}'
  ) as flags
FROM
  mw_mail m
JOIN
  mw_mail_message msg ON m.message_id = msg.id
WHERE
  m.mailbox_id = ? AND m.deleted = 0
  AND NOT EXISTS (
    SELECT 1 FROM mw_mail_flag f WHERE f.mail_id = m.id AND f.flag = '\\Deleted'
  )
ORDER BY
  m.uid ASC

--# mailbox.findEmails.byUidSet
-- 同样可以包含
--#include(mailbox.baseRankedEmailsCTE)
SELECT
  r.id, r.uid, r.internal_date, r.seq_num,
  msg.raw_content, msg.size_in_bytes,
  ARRAY_AGG(mf.flag) FILTER (WHERE mf.flag IS NOT NULL) as flags
FROM ranked_emails r

--# mailbox.findEmails.BySeqSet
SELECT *
FROM (
    SELECT
        m.id,
        m.uid,
        m.internal_date,
        msg.raw_content,
        msg.size_in_bytes,
        ROW_NUMBER() OVER (ORDER BY m.uid ASC) AS seq_num,
        COALESCE(
            (
                SELECT ARRAY_AGG(f.flag)
                FROM mw_mail_flag AS f
                WHERE f.mail_id = m.id
            ),
            '{}'
        ) AS flags
    FROM mw_mail AS m
    JOIN mw_mail_message AS msg
        ON m.message_id = msg.id
    WHERE m.mailbox_id = ?
      AND m.deleted = 0
) AS subquery
WHERE %s;

--# mailbox.getExpungeSeqNums
-- 获取指定用户、指定邮箱中所有标记为 \Deleted 的邮件原始序号（seq_num）
WITH ranked_emails AS (
  SELECT
    m.id,
    ROW_NUMBER() OVER (ORDER BY m.uid ASC) AS seq_num
  FROM mw_mail m
  JOIN mw_mailbox b ON m.mailbox_id = b.id
  JOIN mw_user u    ON b.user_id    = u.id
  WHERE u.username = ?
    AND b.name     = ?
    AND m.deleted  = 0
)
SELECT r.seq_num
FROM ranked_emails r
WHERE EXISTS (
  SELECT 1
  FROM mw_mail_flag f
  WHERE f.mail_id = r.id
    AND f.flag    = '\\Deleted'
);

--# mailbox.expunge
-- 逻辑删除所有已标记 \Deleted 的邮件实例
UPDATE mw_mail m
SET deleted = 1
FROM mw_mailbox b, mw_user u
WHERE m.mailbox_id = b.id
  AND b.user_id    = u.id
  AND u.username   = ?
  AND b.name       = ?
  AND EXISTS (
    SELECT 1
    FROM mw_mail_flag f
    WHERE f.mail_id = m.id
      AND f.flag    = '\\Deleted'
  );


--# mailbox.moveEmails
WITH newuids AS (
  UPDATE mw_mailbox mb
     SET uid_next = uid_next + ?
   WHERE mb.id = ?
 RETURNING mb.uid_next - ? AS start_uid
),
moved AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY uid) - 1 AS idx
    FROM mw_mail
   WHERE mailbox_id = ?
     AND uid IN (%s)
)
UPDATE mw_mail m
   SET mailbox_id = ?,
       uid        = nu.start_uid + mv.idx
  FROM moved mv
  CROSS JOIN newuids nu
 WHERE m.id = mv.id
RETURNING mv.idx, m.id, m.uid;  

--# mailbox.listUids
SELECT m.uid
FROM mw_mail m
JOIN mw_mailbox b ON m.mailbox_id = b.id
WHERE b.user_id = ?
  AND b.id = ?
  AND NOT EXISTS (
    SELECT 1
    FROM mw_mail_flag f
    WHERE f.mail_id = m.id
      AND f.flag = '\Deleted'
  )
ORDER BY m.uid ASC;

--# mailbox.status
SELECT
  (SELECT uid_next
     FROM mw_mailbox
    WHERE id = ?
  ) AS uidnext,
  (SELECT COUNT(*)
     FROM mw_mail m
    WHERE m.mailbox_id = ?
      AND m.deleted = 0
      AND NOT EXISTS (
        SELECT 1
          FROM mw_mail_flag f
         WHERE f.mail_id = m.id
           AND f.flag = '\Deleted'
      )
  ) AS messages,
  (SELECT COUNT(*)
     FROM mw_mail m
    WHERE m.mailbox_id = ?
      AND m.deleted = 0
      AND NOT EXISTS (
        SELECT 1
          FROM mw_mail_flag f
         WHERE f.mail_id = m.id
           AND f.flag = '\Deleted'
      )
      AND NOT EXISTS (
        SELECT 1
          FROM mw_mail_flag f2
         WHERE f2.mail_id = m.id
           AND f2.flag = '\Seen'
      )
  ) AS unseen,
  (SELECT COUNT(*)
     FROM mw_mail_flag f
     JOIN mw_mail m ON m.id = f.mail_id
    WHERE m.mailbox_id = ?
      AND m.deleted = 0
      AND f.flag = '\Recent'
      AND NOT EXISTS (
        SELECT 1
          FROM mw_mail_flag del_f
         WHERE del_f.mail_id = m.id
           AND del_f.flag = '\Deleted'
      )
  ) AS recent;

Email

package com.tio.mail.wing.model;

import java.time.OffsetDateTime;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;

import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * 邮件实体类 (面向数据库和真实场景设计)
 * 代表一封存储在服务器上的邮件实例。
 */
@Data
@NoArgsConstructor
public class Email {

  /**
   * 数据库主键 (例如：自增ID)
   * 这个ID主要用于数据库内部关联，不直接暴露给IMAP客户端。
   */
  private Long id;

  /**
   * 所属用户ID
   * 用于标识这封邮件属于哪个用户。
   */
  private Long userId;

  /**
   * 所属邮箱ID (Mailbox ID)
   * 用于标识这封邮件属于用户的哪个邮箱（INBOX, Sent, etc.）。
   */
  private Long mailboxId;

  /**
   * IMAP UID (Unique Identifier)
   * 这是邮件在【一个特定邮箱内】的唯一标识符。
   * 在数据库中，对于同一个 mailboxId，uid 必须是唯一的。
   */
  private long uid;

  /**
   * 邮件的完整原始内容 (MIME 格式)
   * 存储在数据库中通常使用 TEXT 或 BLOB 类型。
   * 为了性能，有时会将其存储在文件系统或对象存储中，数据库只存路径。
   */
  private String rawContent;

  /**
   * 邮件大小 (单位：字节)
   * 在存入时计算一次，避免重复计算。
   */
  private int size;

  /**
   * 邮件的内部接收日期
   * 服务器接收到这封邮件的时间。用于排序等。
   */
  private OffsetDateTime internalDate;

  // --- IMAP 状态字段 ---

  /**
   * IMAP 标志 (e.g., \Seen, \Answered, \Flagged, \Deleted, \Draft, \Recent)
   * 在数据库中可以存为以逗号分隔的字符串，或使用关联表。
   */
  private Set<String> flags = new HashSet<>();

  // --- 非持久化字段 (用于业务逻辑) ---

  /**
   * 解析后的邮件头 (懒加载)
   * 为了避免每次都解析 rawContent，可以解析一次后缓存起来。
   * transient 表示这个字段不会被JPA/MyBatis等ORM框架持久化。
   */
  private transient Map<String, String> headers;
  
  /**
   * The sequence number of this email within its mailbox.
   * This is a transient field, calculated at query time, not stored in the database.
   */
  private int sequenceNumber; 

  /**
   * 构造函数，用于从原始MIME内容创建邮件对象。
   * @param rawContent 邮件的原始内容
   */
  public Email(String rawContent) {
    this.rawContent = rawContent;
    this.size = rawContent.getBytes(java.nio.charset.StandardCharsets.UTF_8).length;
    this.internalDate = OffsetDateTime.now(); // 记录接收时间
    this.flags.add("\\Recent"); // 新邮件默认为 Recent
  }

  /**
   * 懒加载并获取解析后的邮件头。
   * @return 解析后的邮件头 Map
   */
  public Map<String, String> getHeaders() {
    if (headers == null) {
      headers = new HashMap<>();
      if (rawContent != null) {
        String[] lines = rawContent.split("\\r?\\n");
        for (String line : lines) {
          if (line.isEmpty()) {
            break; // 邮件头结束
          }
          int colonIndex = line.indexOf(':');
          if (colonIndex > 0) {
            String key = line.substring(0, colonIndex).trim();
            String value = line.substring(colonIndex + 1).trim();
            // 只存第一个同名header，或拼接，根据需要
            headers.putIfAbsent(key.toUpperCase(), value);
          }
        }
      }
    }
    return headers;
  }

  /**
   * 便捷方法，获取邮件头中的 Message-ID
   * @return Message-ID 或 null
   */
  public String getMessageId() {
    return getHeaders().get("MESSAGE-ID");
  }

  // 重写 equals 和 hashCode 以确保基于数据库ID的唯一性
  @Override
  public boolean equals(Object o) {
    if (this == o)
      return true;
    if (o == null || getClass() != o.getClass())
      return false;
    Email email = (Email) o;
    return id != null && id.equals(email.id);
  }

  @Override
  public int hashCode() {
    return id != null ? id.hashCode() : 0;
  }
}

WhereClauseResult

package com.tio.mail.wing.result;

import java.util.List;

public class WhereClauseResult {
  private final String clause;
  private final List<Object> params;

  public WhereClauseResult(String clause, List<Object> params) {
    this.clause = clause;
    this.params = params;
  }

  public String getClause() {
    return clause;
  }

  public List<Object> getParams() {
    return params;
  }
}

MailBoxService

package com.tio.mail.wing.service;

import nexus.io.db.activerecord.Db;
import nexus.io.db.activerecord.Row;

public class MailBoxService {

  public Row getMailboxByName(long userId, String mailboxName) {
    String sql = "SELECT id, uid_validity, uid_next FROM mw_mailbox WHERE user_id = ? AND name = ? AND deleted = 0";
    return Db.findFirst(sql, userId, mailboxName);
  }

  public Row getMailboxById(long userId, long mailboxId) {
    String sql = "SELECT id, uid_validity, uid_next FROM mw_mailbox WHERE user_id = ? AND id = ? AND deleted = 0";
    return Db.findFirst(sql, userId, mailboxId);
  }

  public Long getMailboxIdByName(long userId, String mailboxName) {
    String sql = "SELECT id WHERE user_id = ? AND name = ? AND deleted = 0";
    return Db.queryLong(sql, userId, mailboxName);
  }
}

MailSaveAtom

package com.tio.mail.wing.service;

import java.nio.charset.StandardCharsets;
import java.sql.SQLException;
import java.util.Date;
import java.util.Map;

import nexus.io.db.activerecord.Db;
import nexus.io.db.activerecord.Row;
import nexus.io.model.db.IAtom;
import nexus.io.template.SqlTemplates;
import nexus.io.tio.utils.digest.Sha256Utils;
import nexus.io.tio.utils.snowflake.SnowflakeIdUtils;
import com.tio.mail.wing.utils.MailRawUtils;

import lombok.extern.slf4j.Slf4j;

@Slf4j
public class MailSaveAtom implements IAtom {

  private Long userId;
  private String username;
  private Long mailboxId;
  private String mailboxName;
  private String rawContent;

  public MailSaveAtom(Long userId, String username, Long mailboxId, String mailboxName, String rawContent) {
    this.userId = userId;
    this.username = username;
    this.mailboxId = mailboxId;
    this.mailboxName = mailboxName;
    this.rawContent = rawContent;
  }

  @Override
  public boolean run() throws SQLException {

    // 2. 处理邮件内容，实现去重 (mw_mail_message)
    String contentHash = Sha256Utils.digestToHex(rawContent);
    int sizeInBytes = rawContent.getBytes(StandardCharsets.UTF_8).length;

    Row message = Db.findFirst(SqlTemplates.get("mailbox.message.findByHash"), contentHash);
    long messageId;
    long id = SnowflakeIdUtils.id();
    if (message == null) {
      Map<String, String> headers = MailRawUtils.parseHeaders(rawContent);
      Row newMessage = Row.by("id", id).set("content_hash", contentHash)
          //
          .set("message_id_header", headers.get("Message-ID"))
          //
          .set("subject", headers.get("Subject"))
          //
          .set("from_address", headers.get("From")).set("to_address", headers.get("To"))
          //
          .set("size_in_bytes", sizeInBytes).set("raw_content", rawContent);
      Db.save("mw_mail_message", "id", newMessage);
      messageId = newMessage.getLong("id");
    } else {
      messageId = message.getLong("id");
    }

    // 3. 原子地获取并更新邮箱的下一个UID (mw_mailbox)
    String updateSql = SqlTemplates.get("mailbox.updateUidNextAndGet");
    Row result = Db.findFirst(updateSql, mailboxId);
    if (result == null) {
      throw new SQLException("Failed to increment and retrieve uid_next for mailbox " + mailboxId);
    }
    long nextUid = result.getLong("next_uid");

    // 4. 创建邮件实例 (mw_mail)
    Row mailInstance = Row.by("id", id).set("user_id", userId).set("mailbox_id", mailboxId).set("message_id", messageId).set("uid", nextUid).set("internal_date", new Date());
    Db.save("mw_mail", "id", mailInstance);

    // 5. 为新邮件设置 \Recent 标志 (mw_mail_flag)
    long flagId = SnowflakeIdUtils.id();
    Row recentFlag = Row.by("id", flagId).set("mail_id", id).set("flag", "\\Recent");
    Db.save("mw_mail_flag", recentFlag);
    log.info("Saved new email for {} in mailbox {} with UID {}. Mail instance ID: {}", username, mailboxName, nextUid, id);
    return true;
  }
}

MailSaveService

package com.tio.mail.wing.service;

import java.util.List;
import java.util.Set;

import nexus.io.db.activerecord.Db;
import nexus.io.db.activerecord.Row;
import nexus.io.jfinal.aop.Aop;
import nexus.io.model.db.IAtom;
import nexus.io.tio.core.ChannelContext;
import nexus.io.tio.core.Tio;
import nexus.io.tio.utils.lock.SetWithLock;
import com.tio.mail.wing.config.ImapServerConfig;
import com.tio.mail.wing.consts.MailBoxName;
import com.tio.mail.wing.model.Email;
import com.tio.mail.wing.model.MailRaw;
import com.tio.mail.wing.packet.ImapPacket;
import com.tio.mail.wing.utils.MailRawUtils;

import lombok.extern.slf4j.Slf4j;

@Slf4j
public class MailSaveService {
  private MailService mailService = Aop.get(MailService.class);
  private MwUserService mwUserService = Aop.get(MwUserService.class);
  private MailBoxService mailBoxService = Aop.get(MailBoxService.class);
  
  public boolean saveEmail(String toUser, MailRaw mail) {
    
    String rawContent = MailRawUtils.toRawContent(mail);
    return this.saveEmail(toUser, rawContent);
  }

  /**
   * [兼容SMTP] 将接收到的邮件保存到指定用户的收件箱(INBOX)中。
   */
  public boolean saveEmail(String username, String rawContent) {
    return saveEmailInternal(username, MailBoxName.INBOX, rawContent);
  }

  public boolean saveEmail(String toUser, String mailBoxName, MailRaw mail) {
    String rawContent = MailRawUtils.toRawContent(mail);
    return this.saveEmailInternal(toUser, mailBoxName, rawContent);
  }

  /**
   * 内部核心的邮件保存方法。
   * 优化：使用事务和原子性UID更新。
   */
  public boolean saveEmailInternal(String username, String mailboxName, String rawContent) {
    // 1. 获取用户和邮箱信息
    
    Row user = mwUserService.getUserByUsername(username);
    if (user == null) {
      log.error("User '{}' not found. Cannot save email.", username);
      return false;
    }
    Long userId = user.getLong("id");

    
    Row mailbox = mailBoxService.getMailboxByName(userId, mailboxName);
    if (mailbox == null) {
      log.error("Mailbox '{}' not found for user '{}'.", mailboxName, username);
      return false;
    }

    Long mailboxId = mailbox.getLong("id");
    return saveEmailInternal(username, mailboxName, rawContent, userId, mailboxId);
  }

  private boolean saveEmailInternal(String username, String mailboxName, String rawContent, Long userId, Long mailboxId) {
    IAtom atom = new MailSaveAtom(userId, username, mailboxId, mailboxName, rawContent);
    try {
      boolean result = Db.tx(atom);
      if (!result) {
        return result;
      }

      // 通知客户端

      SetWithLock<ChannelContext> channelContexts = Tio.getByUserId(ImapServerConfig.serverTioConfig, userId.toString());
      if (channelContexts == null) {
        return true;
      }
      Set<ChannelContext> ctxs = channelContexts.getObj();
      if (ctxs != null && ctxs.size() > 0) {
        List<Email> all = mailService.getActiveMailFlags(mailboxId);
        long exists = all.size();
        int recent = 0;
        for (Email e : all) {
          Set<String> flags = e.getFlags();
          if (flags.size() > 0) {
            if (flags.contains("\\Recent")) {
              recent++;
            }
          }
        }

        StringBuffer sb = new StringBuffer();
        sb.append("* ").append(exists).append(" EXISTS").append("\r\n");
        sb.append("* ").append(recent).append(" RECENT").append("\r\n");

        ImapPacket imapPacket = new ImapPacket(sb.toString());

        for (ChannelContext ctx : ctxs) {
          //* 9 EXISTS  
          //* 1 RECENT
          Tio.send(ctx, imapPacket);
        }

      }

      return result;
    } catch (Exception e) {
      log.error("Error saving email for user '{}' in mailbox '{}'", username, mailboxName, e);
      return false;
    }
  }
}

MailService

package com.tio.mail.wing.service;

import java.time.OffsetDateTime;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import java.util.stream.Collectors;

import nexus.io.db.activerecord.Db;
import nexus.io.db.activerecord.Row;
import nexus.io.jfinal.aop.Aop;
import nexus.io.template.SqlTemplates;
import nexus.io.tio.utils.hutool.StrUtil;
import nexus.io.tio.utils.snowflake.SnowflakeIdUtils;
import com.tio.mail.wing.consts.MailBoxName;
import com.tio.mail.wing.model.Email;
import com.tio.mail.wing.result.WhereClauseResult;

import lombok.extern.slf4j.Slf4j;

@Slf4j
public class MailService {

  private MwUserService mwUserService = Aop.get(MwUserService.class);
  private MailBoxService mailBoxService = Aop.get(MailBoxService.class);
  private MailSaveService mailSaveService = Aop.get(MailSaveService.class);

  /**
   * 单独的 * → 匹配所有序号
     n:* → 序号 ≥ n
     *:n → 序号 ≤ n
     n:m → 正常范围
     *:* → 匹配所有
   * @param messageSet
   * @param mailboxId
   * @return
   */
  private WhereClauseResult buildSeqWhereClause(String messageSet, long mailboxId) {
    StringBuilder clause = new StringBuilder();
    List<Object> params = new ArrayList<>();

    String[] parts = messageSet.split(",");
    for (String rawPart : parts) {
      String part = rawPart.trim();
      if (clause.length() > 0) {
        clause.append(" OR ");
      }

      if (part.contains(":")) {
        String[] range = part.split(":", 2);
        String startStr = range[0].trim();
        String endStr = range[1].trim();
        boolean startIsStar = "*".equals(startStr);
        boolean endIsStar = "*".equals(endStr);

        if (startIsStar && endIsStar) {
          // *:* → 全部
          clause.append("TRUE");
        } else if (startIsStar) {
          // *:n → seq_num <= n
          int end = Integer.parseInt(endStr);
          clause.append("seq_num <= ?");
          params.add(end);
        } else if (endIsStar) {
          // n:* → seq_num >= n
          int start = Integer.parseInt(startStr);
          clause.append("seq_num >= ?");
          params.add(start);
        } else {
          // n:m → BETWEEN
          int start = Integer.parseInt(startStr);
          int end = Integer.parseInt(endStr);
          clause.append("seq_num BETWEEN ? AND ?");
          params.add(Math.min(start, end));
          params.add(Math.max(start, end));
        }
      } else {
        if ("*".equals(part)) {
          // 单独的 * → 全部
          clause.append("TRUE");
        } else {
          // 单个序号
          clause.append("seq_num = ?");
          params.add(Integer.parseInt(part));
        }
      }
    }

    return new WhereClauseResult(clause.toString(), params);
  }

  /**
   * 将 IMAP UID 集合字符串（如 "1,2,5:7"）解析为具体的 UID 列表。
   * 支持单个 UID、逗号分隔的多段、数字范围，以及 "*" 通配符（代表最大 UID）。
   *
   * @param uidSet      IMAP UID set，比如 "1,2,5:7,*"
   * @param mailboxId   用于查询最大 UID（处理 '*' 通配符）
   * @return            按顺序展开的 UID 列表
   */
  public List<Long> parseUidSet(String uidSet, long mailboxId) {
    // 拿到当前 mailbox 的最大 UID，用于 '*' 扩展
    long maxUid = getMaxUid(mailboxId);
    List<Long> uids = new ArrayList<>();
    String[] parts = uidSet.split(",");
    for (String raw : parts) {
      String part = raw.trim();
      if (part.contains(":")) {
        String[] range = part.split(":", 2);
        long start = "*".equals(range[0]) ? maxUid : Long.parseLong(range[0]);
        long end = "*".equals(range[1]) ? maxUid : Long.parseLong(range[1]);
        long min = Math.min(start, end), max = Math.max(start, end);
        for (long uid = min; uid <= max; uid++) {
          uids.add(uid);
        }
      } else if ("*".equals(part)) {
        // 单独的 '*' → 取最大那条
        uids.add(maxUid);
      } else {
        // 单个数字
        uids.add(Long.parseLong(part));
      }
    }
    return uids;
  }



  /**
   * [兼容POP3] 获取用户收件箱(INBOX)中所有未删除的邮件。
   * 注意：此方法现在性能更高，但如果邮箱巨大，仍需考虑分页。
   */
  public List<Email> getActiveMessagesByUsername(String username) {
    Long userId = mwUserService.getUserIdByUsername(username);
    return getActiveMessages(userId, MailBoxName.INBOX);
  }

  public List<Email> getActiveMessagesByUserId(Long userId) {
    return getActiveMessages(userId, MailBoxName.INBOX);
  }

  /**
   * [兼容POP3] 获取邮箱状态（邮件数，总大小），针对INBOX。
   * 优化：直接在数据库中计算，避免加载所有邮件到内存。
   */
  public int[] getStat(String username) {

    Row user = mwUserService.getUserByUsername(username);
    if (user == null) {
      return new int[] { 0, 0 };
    }

    Row mailbox = mailBoxService.getMailboxByName(user.getLong("id"), MailBoxName.INBOX);
    if (mailbox == null)
      return new int[] { 0, 0 };

    String sql = SqlTemplates.get("mailbox.getStat");
    Row statRow = Db.findFirst(sql, mailbox.getLong("id"));

    if (statRow == null) {
      return new int[] { 0, 0 };
    }
    return new int[] { statRow.getLong("message_count").intValue(), statRow.getBigDecimal("total_size").intValue() };
  }

  /**
   * [兼容POP3] 获取指定邮件内容，针对INBOX。
   * 优化：使用 findEmailsBySeqSet 获取单封邮件，避免加载整个列表。
   */
  public String getMessageContent(String username, int msgNumber) {
    List<Email> emails = findEmailsBySeqSet(username, MailBoxName.INBOX, String.valueOf(msgNumber));
    if (emails != null && !emails.isEmpty()) {
      return emails.get(0).getRawContent();
    }
    return null;
  }

  /**
   * [兼容POP3] 获取邮件大小列表，用于 LIST 命令，针对INBOX。
   */
  public List<Integer> listMessages(Long userId) {
    return getActiveMessages(userId, MailBoxName.INBOX).stream().map(Email::getSize).collect(Collectors.toList());
  }

  /**
   * [兼容POP3] 获取邮件的唯一ID列表，用于 UIDL 命令，针对INBOX。
   */
  public List<Long> listUids(Long userId) {
    Long mailboxId = mailBoxService.getMailboxIdByName(userId, MailBoxName.INBOX);
    return this.listUids(userId, mailboxId);
  }

  /**
   * [IMAP核心] 获取用户【指定邮箱】中所有未被标记为删除的邮件。
   * 优化：使用单个SQL查询，将邮件、内容和标志一次性获取。
   */
  public List<Email> getActiveMessages(Long userId, String mailboxName) {
    Row mailbox = mailBoxService.getMailboxByName(userId, mailboxName);
    if (mailbox == null)
      return Collections.emptyList();

    String sql = SqlTemplates.get("mailbox.getActiveMessages");
    List<Row> mailRows = Db.find(sql, mailbox.getLong("id"));

    return mailRows.stream().map(this::rowToEmailWithAggregatedFlags).collect(Collectors.toList());
  }

  public List<Email> getActiveMessages(Long mailboxId) {
    String sql = SqlTemplates.get("mailbox.getActiveMessages");
    List<Row> mailRows = Db.find(sql, mailboxId);
    return mailRows.stream().map(this::rowToEmailWithAggregatedFlags).collect(Collectors.toList());
  }

  public List<Email> getActiveMailFlags(Long mailboxId) {
    String sql = SqlTemplates.get("mailbox.getActiveMailFlags");
    List<Row> mailRows = Db.find(sql, mailboxId);
    return mailRows.stream().map(this::rowToEmailWithAggregatedFlags).collect(Collectors.toList());
  }

  /**
   * [IMAP核心] 获取邮箱的元数据。
   */
  public Row getMailboxMetadata(Long userId, String mailboxName) {
    Row mailbox = mailBoxService.getMailboxByName(userId, mailboxName);
    if (mailbox == null) {
      return null;
    }
    return mailbox;
  }

  /**
   * [IMAP核心] 根据序号获取邮件。
   * 优化：使用 findEmailsBySeqSet 避免加载全量数据。
   */
  public Email getMessageByNumber(String username, String mailboxName, int msgNumber) {
    List<Email> emails = findEmailsBySeqSet(username, mailboxName, String.valueOf(msgNumber));
    return (emails != null && !emails.isEmpty()) ? emails.get(0) : null;
  }

  /**
   * [IMAP核心] 根据 UID 获取邮件。
   * 优化：使用单个SQL查询，直接获取目标邮件。
   */
  public Email getMessageByUid(String username, String mailboxName, long uid) {
    Row user = mwUserService.getUserByUsername(username);
    if (user == null)
      return null;
    Row mailbox = mailBoxService.getMailboxByName(user.getLong("id"), mailboxName);
    if (mailbox == null)
      return null;

    String sql = SqlTemplates.get("mailbox.getMessageByUid");
    Row row = Db.findFirst(sql, mailbox.getLong("id"), uid);
    if (row == null)
      return null;

    return rowToEmailWithAggregatedFlags(row);
  }

  /**
   * [IMAP核心] 修改邮件标志。
   * 优化：使用批量、原子的SQL操作。
   */
  public void storeFlags(Long mailId, Set<String> newFlags, boolean add) {

    if (add) {
      String sql = SqlTemplates.get("mailbox.flags.addBatch");
      for (String flag : newFlags) {
        Db.updateBySql(sql, SnowflakeIdUtils.id(), mailId, flag);
      }

    } else {
      String flagPlaceholders = String.join(",", Collections.nCopies(newFlags.size(), "?"));
      String sql = String.format(SqlTemplates.get("mailbox.flags.removeBatch"), flagPlaceholders);

      List<Object> params = new ArrayList<>();
      params.add(mailId);
      params.addAll(newFlags);
      Db.updateBySql(sql, params.toArray());
    }
  }

  /**
   * [IMAP核心] 清除指定用户邮箱的所有 \Recent 标志。
   */
  public void clearRecentFlags(String username, String mailboxName) {
    Row user = mwUserService.getUserByUsername(username);
    if (user == null) {
      return;
    }

    Long userId = user.getLong("id");
    clearRecentFlags(userId, mailboxName);
  }

  public void clearRecentFlags(Long userId, String mailboxName) {
    Row mailbox = mailBoxService.getMailboxByName(userId, mailboxName);
    if (mailbox == null) {
      return;
    }
    Long mailBoxId = mailbox.getLong("id");
    clearRecentFlags(mailBoxId);
  }

  public void clearRecentFlags(Long mailBoxId) {
    String sql = SqlTemplates.get("mailbox.flags.clearRecent");
    Db.updateBySql(sql, mailBoxId);
  }

  /**
  * [IMAP核心] 根据UID集合获取邮件列表。
  * 优化：将过滤逻辑下推到数据库，避免在内存中操作大列表。
  */
  public List<Email> findEmailsByUidSet(String username, String mailboxName, String messageSet) {
    Row user = mwUserService.getUserByUsername(username);
    if (user == null) {
      return Collections.emptyList();
    }

    Row mailbox = mailBoxService.getMailboxByName(user.getLong("id"), mailboxName);
    if (mailbox == null) {
      return Collections.emptyList();
    }

    long mailboxId = mailbox.getLong("id");

    return findEmailsByUidSet(mailboxId, messageSet);
  }

  public List<Email> findEmailsByUidSet(long mailboxId, String messageSet) {
    // 解析 messageSet 并构建 SQL 条件
    WhereClauseResult whereClause = buildUidWhereClause(messageSet, mailboxId);
    if (whereClause.getClause().isEmpty()) {
      return Collections.emptyList();
    }
    // 使用新的带占位符的SQL模板
    String baseSql = SqlTemplates.get("mailbox.findEmails.baseQuery");
    // 将动态生成的WHERE条件填入占位符 %s
    String finalSql = String.format(baseSql, whereClause.getClause());

    List<Object> params = new ArrayList<>();
    params.add(mailboxId); // 这是 baseQuery 中的第一个 '?'
    params.addAll(whereClause.getParams()); // 这是动态条件中的参数

    List<Row> mailRows = Db.find(finalSql, params.toArray());
    return mailRows.stream().map(this::rowToEmailWithAggregatedFlags).collect(Collectors.toList());
  }

  /**
  * [IMAP核心] 根据序号集合获取邮件列表。
  * 优化：使用窗口函数在数据库中过滤，避免内存操作。
  */
  public List<Email> findEmailsBySeqSet(String username, String mailboxName, String messageSet) {
    Row user = mwUserService.getUserByUsername(username);
    if (user == null) {
      return Collections.emptyList();
    }

    Row mailbox = mailBoxService.getMailboxByName(user.getLong("id"), mailboxName);
    if (mailbox == null) {
      return Collections.emptyList();
    }

    long mailboxId = mailbox.getLong("id");

    return findEmailsBySeqSet(mailboxId, messageSet);
  }

  public List<Email> findEmailsBySeqSet(long mailboxId, String messageSet) {
    // 解析 messageSet 并构建 SQL 条件
    WhereClauseResult whereClause = buildSeqWhereClause(messageSet, mailboxId);
    if (whereClause.getClause().isEmpty()) {
      return Collections.emptyList();
    }

    // 使用 SqlTemplates 动态解析 #include
    String resolvedBaseQuery = SqlTemplates.get("mailbox.findEmails.BySeqSet");
    String finalSql = String.format(resolvedBaseQuery, whereClause.getClause());

    List<Object> params = new ArrayList<>();
    params.add(mailboxId); // 这是子查询中的 '?'
    params.addAll(whereClause.getParams()); // 这是外部WHERE条件的参数

    List<Row> mailRows = Db.find(finalSql, params.toArray());
    return mailRows.stream().map(this::rowToEmailWithAggregatedFlags).collect(Collectors.toList());
  }

  /**
  * 将数据库行（包含聚合后的标志数组）转换为 Email DTO 对象。
  */
  private Email rowToEmailWithAggregatedFlags(Row row) {
    Email email = new Email();
    email.setId(row.getLong("id"));
    email.setUid(row.getLong("uid"));
    email.setRawContent(row.getStr("raw_content"));
    Integer sizeInBytes = row.getInt("size_in_bytes");
    if (sizeInBytes != null) {
      email.setSize(sizeInBytes);
    }

    // 新增：设置序列号
    if (row.get("sequence_number") != null) {
      email.setSequenceNumber(row.getInt("sequence_number"));
    }

    OffsetDateTime internalDate = row.getOffsetDateTime("internal_date");
    if (internalDate != null) {
      email.setInternalDate(internalDate);
    }
    String[] flagsArray = row.getStringArray("flags");

    if (flagsArray != null) {
      Set<String> flags = new HashSet<>(flagsArray.length);
      for (String string : flagsArray) {
        flags.add(string);
      }
      email.setFlags(flags);
    }

    return email;
  }

  private WhereClauseResult buildUidWhereClause(String messageSet, long mailboxId) {
    StringBuilder clause = new StringBuilder();
    List<Object> params = new ArrayList<>();
    Long maxUid = null; // Lazy-loaded max UID

    String[] parts = messageSet.split(",");
    for (String part : parts) {
      part = part.trim();
      if (clause.length() > 0)
        clause.append(" OR ");

      if (part.contains(":")) {
        String[] range = part.split(":", 2);
        if ("*".equals(range[0]) || "*".equals(range[1])) {
          if (maxUid == null)
            maxUid = getMaxUid(mailboxId);
        }
        long start = "*".equals(range[0]) ? maxUid : Long.parseLong(range[0]);
        long end = "*".equals(range[1]) ? maxUid : Long.parseLong(range[1]);
        clause.append("m.uid BETWEEN ? AND ?");
        params.add(Math.min(start, end));
        params.add(Math.max(start, end));
      } else {
        if ("*".equals(part)) {
          if (maxUid == null)
            maxUid = getMaxUid(mailboxId);
          clause.append("m.uid = ?");
          params.add(maxUid);
        } else {
          clause.append("m.uid = ?");
          params.add(Long.parseLong(part));
        }
      }
    }
    return new WhereClauseResult(clause.toString(), params);
  }

  private Long getMaxUid(long mailboxId) {
    Row row = Db.findFirst(SqlTemplates.get("mailbox.getMaxUid"), mailboxId);
    return (row != null && row.getLong("max") != null) ? row.getLong("max") : 0L;
  }

  /**
   * 获取待 EXPUNGE 的邮件序号列表
   */
  public List<Integer> getExpungeSeqNums(String username, String mailboxName) {
    String sql = SqlTemplates.get("mailbox.getExpungeSeqNums");
    List<Row> rows = Db.find(sql, username, mailboxName);
    return rows.stream().map(r -> r.getInt("seq_num")).collect(Collectors.toList());
  }

  /**
   * 逻辑删除所有已标记 \Deleted 的邮件实例
   */
  public void expunge(String username, String mailboxName) {
    String sql = SqlTemplates.get("mailbox.expunge");
    Db.updateBySql(sql, username, mailboxName);
  }

  /**
   * 列出指定用户的所有邮箱目录名称
   */
  public List<String> listMailboxes(String username) {
    Row user = mwUserService.getUserByUsername(username);
    if (user == null) {
      return new ArrayList<>(0);
    }
    Long userId = user.getLong("id");
    String sql = "SELECT name FROM mw_mailbox WHERE user_id = ? AND deleted = 0";
    List<Row> rows = Db.find(sql, userId);
    return rows.stream().map(r -> r.getStr("name")).collect(Collectors.toList());
  }

  /**
   * 为指定用户创建一个新的邮箱目录
   */
  public void createMailbox(String username, String mailboxName) {
    if (StrUtil.isBlank(mailboxName)) {
      throw new IllegalArgumentException("Mailbox name cannot be blank");
    }
    Row user = mwUserService.getUserByUsername(username);
    if (user == null) {
      throw new IllegalStateException("User not found: " + username);
    }
    long mailboxId = SnowflakeIdUtils.id();
    long userId = user.getLong("id");

    Row newMailbox = Row.by("id", mailboxId).set("user_id", userId).set("name", mailboxName)
        //
        .set("uid_validity", mailboxId).set("uid_next", 1).set("creator", "system").set("updater", "system")
        //
        .set("tenant_id", user.getLong("tenant_id"));

    Db.save("mw_mailbox", "id", newMailbox);
    log.info("Created mailbox '{}' (id={}) for user {}", mailboxName, mailboxId, username);
  }

  public void copyEmailsByUidSet(String username, String srcMailboxName, String uidSet, String destMailboxName) {
    // 1. 找到要复制的邮件
    List<Email> toCopy = findEmailsByUidSet(username, srcMailboxName, uidSet);
    if (toCopy.isEmpty()) {
      return;
    }
    // 2. 对每封邮件，重新用 saveEmailInternal 插入到目标 mailbox
    for (Email e : toCopy) {
      // rawContent 来自 Email.getRawContent()
      mailSaveService.saveEmailInternal(username, destMailboxName, e.getRawContent());
    }
  }

  public boolean exitsMailBox(Long userId, String mailboxName) {
    String sql = "select count(1) from mw_mailbox where user_id=? and name=?";
    return Db.existsBySql(sql, userId, mailboxName);
  }

  public Long queryMailBoxId(Long userId, String mailboxName) {
    String sql = "select id from mw_mailbox where user_id=? and name=?";
    return Db.queryLong(sql, userId, mailboxName);
  }

  public void moveEmailsByUidSet(Long userId, String src, String uidSet, String dest) {
    long srcMailboxId = mailBoxService.getMailboxByName(userId, src).getLong("id");
    long destMailboxId = mailBoxService.getMailboxByName(userId, dest).getLong("id");

    // 1. 解析 UID set
    List<Long> uids = parseUidSet(uidSet, srcMailboxId); // 比如 [42L, 43L, 44L]
    // 2. 计算要 bump 的数量
    int cnt = uids.size();
    // 3. 准备 IN 占位符

    String inClause = uids.stream().map(u -> "?").collect(Collectors.joining(","));

    // 4. 从模板中取出 SQL，然后替换占位注释
    String raw = SqlTemplates.get("mailbox.moveEmails");
    String sql = String.format(raw, inClause);
    // 5. 按顺序组装参数：cnt, destMailboxId, srcMailboxId, 然后是每个 uid
    List<Object> params = new ArrayList<>();
    params.add(cnt);
    params.add(destMailboxId);
    params.add(cnt);
    params.add(srcMailboxId);
    params.addAll(uids);
    params.add(destMailboxId);
    // 6. 执行
    Db.updateBySql(sql, params.toArray());
  }

  public long highest_modseq(long mailboxId) {
    String sql = "select highest_modseq from mw_mailbox where id=?";
    return Db.queryLong(sql, mailboxId);
  }

  public List<Long> listUids(Long userId, Long mailBoxId) {
    String sql = SqlTemplates.get("mailbox.listUids");
    return Db.queryListLong(sql, userId, mailBoxId);
  }

  public Row status(Long boxId) {
    String sql = SqlTemplates.get("mailbox.status");
    return Db.findFirst(sql, boxId, boxId, boxId, boxId);
  }

}