package db

import (
	"database/sql"
	"errors"
)

const signatureColumns = `id, mailbox_id, name, content_html, is_default_new, is_default_reply, created_at`

func scanSignature(scan func(dest ...any) error) (MailboxSignature, error) {
	var s MailboxSignature
	var createdAt string
	err := scan(&s.ID, &s.MailboxID, &s.Name, &s.ContentHTML, &s.IsDefaultNew, &s.IsDefaultReply, &createdAt)
	if err != nil {
		return s, err
	}
	s.CreatedAt, _ = parseTime(createdAt)
	return s, nil
}

// ListSignatures returns a mailbox's saved signatures, most recently created first.
func (d *DB) ListSignatures(mailboxID int64) ([]MailboxSignature, error) {
	rows, err := d.Query(`SELECT `+signatureColumns+` FROM esrv_mailbox_signatures WHERE mailbox_id = ? ORDER BY id DESC`, mailboxID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []MailboxSignature
	for rows.Next() {
		s, err := scanSignature(rows.Scan)
		if err != nil {
			return nil, err
		}
		out = append(out, s)
	}
	return out, rows.Err()
}

// GetSignatureByID scopes the lookup to mailboxID so one mailbox can never read or
// (via UpdateSignature/DeleteSignature, which reuse this same WHERE clause) modify
// another's signature by guessing an id.
func (d *DB) GetSignatureByID(mailboxID, id int64) (*MailboxSignature, error) {
	row := d.QueryRow(`SELECT `+signatureColumns+` FROM esrv_mailbox_signatures WHERE id = ? AND mailbox_id = ?`, id, mailboxID)
	s, err := scanSignature(row.Scan)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, nil
		}
		return nil, err
	}
	return &s, nil
}

// GetDefaultSignature returns the mailbox's default-for-new (forReply=false) or
// default-for-reply/forward (forReply=true) signature, or nil if none is set.
func (d *DB) GetDefaultSignature(mailboxID int64, forReply bool) (*MailboxSignature, error) {
	col := "is_default_new"
	if forReply {
		col = "is_default_reply"
	}
	row := d.QueryRow(`SELECT `+signatureColumns+` FROM esrv_mailbox_signatures WHERE mailbox_id = ? AND `+col+` = 1 LIMIT 1`, mailboxID)
	s, err := scanSignature(row.Scan)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, nil
		}
		return nil, err
	}
	return &s, nil
}

func (d *DB) CreateSignature(mailboxID int64, name, contentHTML string) (int64, error) {
	res, err := d.Exec(`INSERT INTO esrv_mailbox_signatures (mailbox_id, name, content_html) VALUES (?, ?, ?)`, mailboxID, name, contentHTML)
	if err != nil {
		return 0, err
	}
	return res.LastInsertId()
}

func (d *DB) UpdateSignature(mailboxID, id int64, name, contentHTML string) error {
	_, err := d.Exec(`UPDATE esrv_mailbox_signatures SET name = ?, content_html = ? WHERE id = ? AND mailbox_id = ?`, name, contentHTML, id, mailboxID)
	return err
}

func (d *DB) DeleteSignature(mailboxID, id int64) error {
	_, err := d.Exec(`DELETE FROM esrv_mailbox_signatures WHERE id = ? AND mailbox_id = ?`, id, mailboxID)
	return err
}

// SetDefaultSignature makes id the mailbox's default-for-new (forReply=false) or
// default-for-reply/forward (forReply=true) signature, clearing the flag from every
// other row first so at most one ever holds it — id=0 just clears the flag from
// everything, i.e. "no default".
func (d *DB) SetDefaultSignature(mailboxID, id int64, forReply bool) error {
	col := "is_default_new"
	if forReply {
		col = "is_default_reply"
	}
	if _, err := d.Exec(`UPDATE esrv_mailbox_signatures SET `+col+` = 0 WHERE mailbox_id = ?`, mailboxID); err != nil {
		return err
	}
	if id == 0 {
		return nil
	}
	_, err := d.Exec(`UPDATE esrv_mailbox_signatures SET `+col+` = 1 WHERE id = ? AND mailbox_id = ?`, id, mailboxID)
	return err
}
