1package db
  2
  3import (
  4	"context"
  5	"database/sql"
  6	"errors"
  7	"fmt"
  8	"log/slog"
  9
 10	"postern/internal/model"
 11)
 12
 13func (db *DB) GetUserMailbox(ctx context.Context, userID int, mailboxName string) (model.Mailbox, error) {
 14	mailboxes, err := db.GetUserMailboxes(ctx, userID)
 15	if err != nil {
 16		return model.Mailbox{}, err
 17	}
 18
 19	for _, mb := range mailboxes {
 20		if mb.Name == mailboxName {
 21			return mb, nil
 22		}
 23	}
 24	return model.Mailbox{}, ErrMailboxNotFound
 25}
 26
 27// GetUserMailboxes returns all existing mailboxes with their calculated IMAP status values.
 28func (db *DB) GetUserMailboxes(ctx context.Context, userID int) ([]model.Mailbox, error) {
 29	// This query safely aggregates counts using subqueries, preventing
 30	// Cartesian products if a message has multiple flags.
 31	query := `
 32		SELECT
 33		    mb.id,
 34			mb.name,
 35			mb.special_use,
 36			mb.uidnext,
 37			mb.uidvalidity,
 38			mb.highest_modseq,
 39			(s.id IS NOT NULL) AS is_subscribed,
 40			(SELECT COUNT(*) FROM mailbox_messages mm WHERE mm.mailbox_id = mb.id AND mm.expunged_modseq IS NULL) AS num_messages,
 41			(SELECT COALESCE(SUM(m.size), 0) FROM mailbox_messages mm JOIN messages m ON mm.message_id = m.id WHERE mm.mailbox_id = mb.id AND mm.expunged_modseq IS NULL) AS size,
 42			(SELECT COUNT(*) FROM mailbox_messages mm WHERE mm.mailbox_id = mb.id AND mm.expunged_modseq IS NULL AND NOT EXISTS (SELECT 1 FROM message_flags mf WHERE mf.mailbox_id = mm.mailbox_id AND mf.uid = mm.uid AND mf.flag = '\Seen')) AS num_unseen,
 43			(SELECT COUNT(*) FROM mailbox_messages mm WHERE mm.mailbox_id = mb.id AND mm.expunged_modseq IS NULL AND EXISTS (SELECT 1 FROM message_flags mf WHERE mf.mailbox_id = mm.mailbox_id AND mf.uid = mm.uid AND mf.flag = '\Deleted')) AS num_deleted
 44		FROM mailboxes mb
 45		LEFT JOIN subscriptions s ON mb.id = s.mailbox_id
 46		WHERE mb.user_id = ?
 47	`
 48
 49	rows, err := db.read.QueryContext(ctx, query, userID)
 50	if err != nil {
 51		return nil, err
 52	}
 53	defer func(rows *sql.Rows) {
 54		_ = rows.Close()
 55	}(rows)
 56
 57	var mailboxes []model.Mailbox
 58	for rows.Next() {
 59		var mb model.Mailbox
 60		var specialUse sql.NullString
 61
 62		err := rows.Scan(
 63			&mb.ID,
 64			&mb.Name,
 65			&specialUse,
 66			&mb.UIDNext,
 67			&mb.UIDValidity,
 68			&mb.HighestModSeq,
 69			&mb.IsSubscribed,
 70			&mb.NumMessages,
 71			&mb.Size,
 72			&mb.NumUnseen,
 73			&mb.NumDeleted,
 74		)
 75		if err != nil {
 76			return nil, err
 77		}
 78
 79		if specialUse.Valid {
 80			mb.SpecialUse = new(specialUse.String)
 81		}
 82		mailboxes = append(mailboxes, mb)
 83	}
 84
 85	return mailboxes, rows.Err()
 86}
 87
 88func (db *DB) CreateMailbox(ctx context.Context, userID int, mailbox string) error {
 89	tx, err := db.write.BeginTx(ctx, nil)
 90	if err != nil {
 91		return fmt.Errorf("beginning transaction: %w", err)
 92	}
 93	defer txRollback(tx)
 94
 95	nextUID, err := nextUIDValidity(tx)
 96	if err != nil {
 97		return err
 98	}
 99
100	_, err = tx.ExecContext(ctx, `
101            INSERT INTO mailboxes
102                (user_id, name, uidvalidity, uidnext, highest_modseq)
103            VALUES
104                (?, ?, ?, 1, 0)`,
105		userID, mailbox, nextUID,
106	)
107	if err != nil {
108		return fmt.Errorf("creating mailbox %q: %w", mailbox, err)
109	}
110
111	return tx.Commit()
112}
113
114func (db *DB) DeleteMailbox(ctx context.Context, userID int, mailbox string) error {
115	_, err := db.write.ExecContext(ctx, `DELETE FROM mailboxes WHERE user_id = ? AND name = ?`, userID, mailbox)
116	if err != nil {
117		return fmt.Errorf("deleting mailbox %q: %w", mailbox, err)
118	}
119
120	return nil
121}
122
123func (db *DB) RenameMailbox(ctx context.Context, userID int, oldName, newName string) error {
124	tx, err := db.write.BeginTx(ctx, nil)
125	if err != nil {
126		return fmt.Errorf("beginning transaction: %w", err)
127	}
128	defer txRollback(tx)
129
130	_, err = tx.ExecContext(ctx, `
131    UPDATE mailboxes 
132    SET name = ? || SUBSTR(name, LENGTH(?)+1)
133    WHERE user_id = ? 
134    AND (name = ? OR name LIKE ? ESCAPE '\')
135`, newName, oldName, userID, oldName, escapeLike(oldName)+"/%")
136	if err != nil {
137		return fmt.Errorf("renaming mailbox %q: %v", oldName, err.Error())
138	}
139
140	return tx.Commit()
141}
142
143// GetMailboxID returns the mailbox ID und UID Validity for the given mailbox name, scoped to the user ID
144func (db *DB) GetMailboxID(ctx context.Context, userID int, mailbox string) (int, uint32, error) {
145	var id int
146	var uidValidity uint32
147	err := db.read.QueryRowContext(ctx, "SELECT id, uidvalidity FROM mailboxes WHERE name = ? AND user_id = ?", mailbox, userID).Scan(&id, &uidValidity)
148	if errors.Is(err, sql.ErrNoRows) {
149		return 0, 0, ErrMailboxNotFound
150	}
151	return id, uidValidity, err
152}
153
154// CreateAndMoveAllMessagesFromINBOXToMailbox is special handling for RENAME of mailbox INBOX
155func (db *DB) CreateAndMoveAllMessagesFromINBOXToMailbox(ctx context.Context, userID int, toMailbox string) error {
156	tx, err := db.write.BeginTx(ctx, nil)
157	if err != nil {
158		return err
159	}
160	defer func(tx *sql.Tx) {
161		err := tx.Rollback()
162		if err != nil {
163			slog.Error("failed to rollback transaction", "error", err.Error())
164		}
165	}(tx)
166
167	// Rename INBOX -> toMailbox
168	_, err = tx.ExecContext(ctx, `UPDATE mailboxes SET name = ? WHERE user_id = ? AND name = 'INBOX'`, toMailbox, userID)
169	if err != nil {
170		return err
171	}
172
173	// Create new INBOX mailbox
174	nextUID, err := nextUIDValidity(tx)
175	if err != nil {
176		return err
177	}
178
179	_, err = tx.ExecContext(ctx, `
180            INSERT INTO mailboxes
181                (user_id, name, uidvalidity, uidnext, highest_modseq)
182            VALUES
183                (?, 'INBOX', ?, 1, 0)`,
184		userID, nextUID,
185	)
186	if err != nil {
187		return err
188	}
189
190	return tx.Commit()
191}
192
193// ListSubscribed fulfills IMAP LIST (SUBSCRIBED).
194// It returns ONLY mailboxes that are subscribed to. The boolean map value
195// indicates if the mailbox still exists (true) or was deleted (false).
196func (db *DB) ListSubscribed(ctx context.Context, userID int) (map[model.Mailbox]bool, error) {
197	// INNER JOIN ensures we only get existing mailboxes the user is actually subscribed to.
198	// The UNION ALL handles mailboxes that were deleted (mailbox_id IS NULL)
199	// but still have a subscription record.
200	query := `
201		SELECT mb.name, mb.special_use, 1 AS mb_exists
202		FROM subscriptions s
203		INNER JOIN mailboxes mb ON s.mailbox_id = mb.id
204		WHERE s.user_id = ?
205		
206		UNION ALL
207		
208		SELECT s.mailbox_name AS name, NULL AS special_use, 0 AS mb_exists
209		FROM subscriptions s
210		WHERE s.user_id = ? AND s.mailbox_id IS NULL
211	`
212
213	rows, err := db.read.QueryContext(ctx, query, userID, userID)
214	if err != nil {
215		return nil, err
216	}
217	defer func(rows *sql.Rows) {
218		_ = rows.Close()
219	}(rows)
220
221	out := make(map[model.Mailbox]bool)
222	for rows.Next() {
223		var mb model.Mailbox
224		var exists bool
225		var specialUse sql.NullString
226
227		if err := rows.Scan(&mb.Name, &specialUse, &exists); err != nil {
228			return nil, err
229		}
230
231		if specialUse.Valid {
232			mb.SpecialUse = new(specialUse.String)
233		}
234
235		mb.IsSubscribed = true
236		out[mb] = exists
237	}
238
239	return out, rows.Err()
240}
241
242func (db *DB) UIDToServerSeq(ctx context.Context, mailboxID int, uid uint32) (uint32, error) {
243	var seq uint32
244	err := db.read.QueryRowContext(ctx, `
245        SELECT COUNT(*)
246        FROM mailbox_messages
247        WHERE mailbox_id = ? AND expunged_modseq IS NULL AND uid <= ?
248    `, mailboxID, uid).Scan(&seq)
249	return seq, err // 0 means "no live message at or below this uid"
250}
251
252func (db *DB) ServerSeqToUID(ctx context.Context, mailboxID int, seq uint32) (uint32, error) {
253	var uid uint32
254	err := db.read.QueryRowContext(ctx, `
255        SELECT uid FROM mailbox_messages
256        WHERE mailbox_id = ? AND expunged_modseq IS NULL
257        ORDER BY uid LIMIT 1 OFFSET ?
258    `, mailboxID, seq-1).Scan(&uid) // OFFSET is 0-based, seqnum 1-based
259	return uid, err
260}
261
262// MailboxMessageCount returns the number of live (non-expunged) messages in the
263// mailbox: the server-view EXISTS value, which is what RFC 9051 binds "*" to.
264func (db *DB) MailboxMessageCount(ctx context.Context, mailboxID int) (uint32, error) {
265	var count uint32
266	err := db.read.QueryRowContext(ctx, `
267        SELECT COUNT(*) FROM mailbox_messages
268        WHERE mailbox_id = ? AND expunged_modseq IS NULL
269    `, mailboxID).Scan(&count)
270	return count, err
271}