ygggo/internal/storage/sqlite3/table_mails.go

/*
 * (Original Copyright (c) 2021 Neil Alexander)
 *
 * This Source Code Form is subject to the terms of the Mozilla Public
 * License, v. 2.0. If a copy of the MPL was not distributed with this
 * file, You can obtain one at http://mozilla.org/MPL/2.0/.
 */

package sqlite3

import (
	"bytes"
	"database/sql"
	"fmt"
	"io"
	"time"

	"github.com/neilalexander/yggmail/internal/storage/types"
)

type TableMails struct {
	db               *sql.DB
	writer           *Writer
	selectMails      *sql.Stmt
	selectMail       *sql.Stmt
	selectMailNextID *sql.Stmt
	selectIDForSeq   *sql.Stmt
	searchMail       *sql.Stmt
	createMail       *sql.Stmt
	countMails       *sql.Stmt
	countUnseenMails *sql.Stmt
	updateMailFlags  *sql.Stmt
	deleteMail       *sql.Stmt
	expungeMail      *sql.Stmt
	moveMail         *sql.Stmt
}

const mailsSchema = `
    CREATE TABLE IF NOT EXISTS mails (
        account_pubkey TEXT NOT NULL,
        mailbox        TEXT NOT NULL,
        id             INTEGER NOT NULL DEFAULT 1,
        mail           BLOB NOT NULL,
        mail_file      TEXT NOT NULL DEFAULT '', 
        size           INTEGER NOT NULL DEFAULT 0, 
        datetime       INTEGER NOT NULL,
        seen           BOOLEAN NOT NULL DEFAULT 0,
        answered       BOOLEAN NOT NULL DEFAULT 0,
        flagged        BOOLEAN NOT NULL DEFAULT 0,
        deleted        BOOLEAN NOT NULL DEFAULT 0,
        PRIMARY KEY (account_pubkey, mailbox, id),
        FOREIGN KEY (account_pubkey, mailbox) REFERENCES mailboxes(account_pubkey, mailbox) ON DELETE CASCADE ON UPDATE CASCADE
    );

    -- View for sequential numbering (important for IMAP FETCH sequence numbers)
    CREATE VIEW IF NOT EXISTS inboxes AS SELECT * FROM (
        SELECT ROW_NUMBER() OVER (PARTITION BY account_pubkey, mailbox ORDER BY id) AS seq, * FROM mails
    )
    ORDER BY account_pubkey, mailbox, id;
`

const selectMailsStmt = `
    SELECT seq, id, mail, mail_file, size, datetime, seen, answered, flagged, deleted FROM inboxes
    WHERE account_pubkey = $1 AND mailbox = $2
    ORDER BY id
`

const selectMailStmt = `
    SELECT seq, id, mail, mail_file, size, datetime, seen, answered, flagged, deleted FROM inboxes
    WHERE account_pubkey = $1 AND mailbox = $2 AND id = $3
`

const insertMailStmt = `
    INSERT INTO mails (account_pubkey, mailbox, id, mail, mail_file, size, datetime) VALUES(
        $1, $2, (
            SELECT IFNULL(MAX(id)+1,1) AS id FROM mails
            WHERE account_pubkey = $1 AND mailbox = $2
        ), $3, $4, $5, $6
    )
    RETURNING id;
`

const selectMailCountStmt = `
    SELECT COUNT(*) FROM mails WHERE account_pubkey = $1 AND mailbox = $2
`

const selectMailUnseenStmt = `
    SELECT COUNT(*) FROM mails WHERE account_pubkey = $1 AND mailbox = $2 AND seen = 0
`

const searchMailStmt = `
    SELECT id FROM mails
    WHERE account_pubkey = $1 AND mailbox = $2
    ORDER BY id
`

const selectIDForSeqStmt = `
    SELECT id FROM inboxes
    WHERE account_pubkey = $1 AND mailbox = $2 AND seq = $3
`

const selectMailNextIDStmt = `
    SELECT IFNULL(MAX(id)+1,1) AS id FROM mails
    WHERE account_pubkey = $1 AND mailbox = $2
`

const updateMailFlagsStmt = `
    UPDATE mails SET seen = $1, answered = $2, flagged = $3, deleted = $4 
    WHERE account_pubkey = $5 AND mailbox = $6 AND id = $7
`

const deleteMailStmt = `
    UPDATE mails SET deleted = 1 WHERE account_pubkey = $1 AND mailbox = $2 AND id = $3
`

const expungeMailStmt = `
    DELETE FROM mails WHERE account_pubkey = $1 AND mailbox = $2 AND deleted = 1
`

const moveMailStmt = `
    UPDATE mails SET mailbox = $3, id = $4 WHERE account_pubkey = $1 AND mailbox = $2 AND id = $5
`

func NewTableMails(db *sql.DB, writer *Writer) (*TableMails, error) {
	t := &TableMails{
		db:     db,
		writer: writer,
	}
	_, err := db.Exec(mailsSchema)
	if err != nil {
		return nil, fmt.Errorf("db.Exec: %w", err)
	}

	t.selectMails, err = db.Prepare(selectMailsStmt)
	if err != nil {
		return nil, err
	}
	t.selectMail, err = db.Prepare(selectMailStmt)
	if err != nil {
		return nil, err
	}
	t.selectMailNextID, err = db.Prepare(selectMailNextIDStmt)
	if err != nil {
		return nil, err
	}
	t.selectIDForSeq, err = db.Prepare(selectIDForSeqStmt)
	if err != nil {
		return nil, err
	}
	t.searchMail, err = db.Prepare(searchMailStmt)
	if err != nil {
		return nil, err
	}
	t.createMail, err = db.Prepare(insertMailStmt)
	if err != nil {
		return nil, err
	}
	t.updateMailFlags, err = db.Prepare(updateMailFlagsStmt)
	if err != nil {
		return nil, err
	}
	t.deleteMail, err = db.Prepare(deleteMailStmt)
	if err != nil {
		return nil, err
	}
	t.expungeMail, err = db.Prepare(expungeMailStmt)
	if err != nil {
		return nil, err
	}
	t.countMails, err = db.Prepare(selectMailCountStmt)
	if err != nil {
		return nil, err
	}
	t.countUnseenMails, err = db.Prepare(selectMailUnseenStmt)
	if err != nil {
		return nil, err
	}
	t.moveMail, err = db.Prepare(moveMailStmt)
	if err != nil {
		return nil, err
	}

	return t, nil
}

// MailRawCreate inserts mail metadata.
// data: content blob (can be nil/empty if stored in mailFile).
// mailFile: relative path in the BlobStore.
// size: pre-calculated size of the full message.
func (t *TableMails) MailCreateRaw(accountPubKey string, mailbox string, data io.Reader, mailFile string, size int64) (int, error) {
	var id int
	err := t.writer.Do(t.db, nil, func(txn *sql.Tx) error {
		if data == nil {
			data = bytes.NewBuffer(nil)
		}
		body, err := io.ReadAll(data)
		if err != nil {
			return fmt.Errorf("MailRawCreate: error read data: %w", err)
		}
		// Use empty slice instead of nil to satisfy NOT NULL constraint if necessary
		if body == nil {
			body = []byte{}
		}
		return t.createMail.QueryRow(accountPubKey, mailbox, body, mailFile, size, time.Now().Unix()).Scan(&id)
	})
	return id, err
}

func (t *TableMails) MailCreate(accountPubKey string, mailbox string, data io.Reader, size int) (int, error) {
	panic("sqlite3: MailCreate(stream) must not be called directly. Use LocalStorage wrapper.")
}

// MailSelect retrieves mail metadata.
// Note: The Body field in types.Mail will be populated by the storage coordinator.
func (t *TableMails) MailSelect(accountPubKey string, mailbox string, id int) (int, *types.Mail, error) {
	var seq int
	var datetime int64
	mail := &types.Mail{}
	err := t.selectMail.QueryRow(accountPubKey, mailbox, id).Scan(
		&seq, &mail.ID, &mail.RawData, &mail.MailFile, &mail.Size, &datetime,
		&mail.Seen, &mail.Answered, &mail.Flagged, &mail.Deleted,
	)
	if err != nil {
		return 0, nil, err
	}
	mail.Date = time.Unix(datetime, 0)
	return seq, mail, nil
}

func (t *TableMails) MailSearch(accountPubKey string, mailbox string) ([]uint32, error) {
	var ids []uint32
	rows, err := t.searchMail.Query(accountPubKey, mailbox)
	if err != nil {
		return nil, fmt.Errorf("t.searchMail.Query: %w", err)
	}
	defer rows.Close()
	for rows.Next() {
		var id uint32
		if err := rows.Scan(&id); err != nil {
			return nil, fmt.Errorf("rows.Scan: %w", err)
		}
		ids = append(ids, id)
	}
	return ids, nil
}

func (t *TableMails) MailNextID(accountPubKey string, mailbox string) (int, error) {
	var id int
	err := t.selectMailNextID.QueryRow(accountPubKey, mailbox).Scan(&id)
	return id, err
}

func (t *TableMails) MailIDForSeq(accountPubKey string, mailbox string, seq int) (int, error) {
	var id int
	err := t.selectIDForSeq.QueryRow(accountPubKey, mailbox, seq).Scan(&id)
	return id, err
}

func (t *TableMails) MailUnseen(accountPubKey string, mailbox string) (int, error) {
	var unseen int
	err := t.countUnseenMails.QueryRow(accountPubKey, mailbox).Scan(&unseen)
	return unseen, err
}

func (t *TableMails) MailUpdateFlags(accountPubKey string, mailbox string, id int, seen, answered, flagged, deleted bool) error {
	return t.writer.Do(t.db, nil, func(txn *sql.Tx) error {
		_, err := t.updateMailFlags.Exec(seen, answered, flagged, deleted, accountPubKey, mailbox, id)
		return err
	})
}

func (t *TableMails) MailDelete(accountPubKey string, mailbox string, id int) error {
	return t.writer.Do(t.db, nil, func(txn *sql.Tx) error {
		_, err := t.deleteMail.Exec(accountPubKey, mailbox, id)
		return err
	})
}

func (t *TableMails) MailExpunge(accountPubKey string, mailbox string) error {
	return t.writer.Do(t.db, nil, func(txn *sql.Tx) error {
		_, err := t.expungeMail.Exec(accountPubKey, mailbox)
		return err
	})
}

func (t *TableMails) MailCount(accountPubKey string, mailbox string) (int, error) {
	var count int
	err := t.countMails.QueryRow(accountPubKey, mailbox).Scan(&count)
	return count, err
}

func (t *TableMails) MailMove(accountPubKey string, mailbox string, id int, destination string) (int, error) {
	var newID int
	err := t.writer.Do(t.db, nil, func(txn *sql.Tx) error {
		if err := t.selectMailNextID.QueryRow(accountPubKey, destination).Scan(&newID); err != nil {
			return err
		}
		_, err := t.moveMail.Exec(accountPubKey, mailbox, destination, newID, id)
		return err
	})
	return newID, err
}