package db

import (
	"database/sql"
	"errors"
	"strings"
)

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

// signatureColumnsPrefixed is signatureColumns qualified with a table alias, for
// queries that JOIN esrv_mailbox_signatures against another table.
func signatureColumnsPrefixed(alias string) string {
	cols := strings.Split(signatureColumns, ", ")
	for i, c := range cols {
		cols[i] = alias + "." + c
	}
	return strings.Join(cols, ", ")
}

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 default-for-new (forReply=false) or
// default-for-reply/forward (forReply=true) signature, or nil if none is set.
// forEmail scopes this to a specific send-as alias — see
// esrv_mailbox_signature_alias_defaults' schema comment: an alias with its own
// override uses that; forEmail == "" (or the mailbox's own primary address, which
// never has its own override row) falls straight through to the mailbox-wide
// is_default_new/is_default_reply columns, same as an alias with no override does.
func (d *DB) GetDefaultSignature(mailboxID int64, forReply bool, forEmail string) (*MailboxSignature, error) {
	if forEmail != "" {
		row := d.QueryRow(`SELECT `+signatureColumnsPrefixed("s")+`
			FROM esrv_mailbox_signature_alias_defaults ad JOIN esrv_mailbox_signatures s ON s.id = ad.signature_id
			WHERE ad.mailbox_id = ? AND ad.for_email = ? AND ad.for_reply = ?`, mailboxID, forEmail, forReply)
		s, err := scanSignature(row.Scan)
		if err == nil {
			return &s, nil
		}
		if !errors.Is(err, sql.ErrNoRows) {
			return nil, err
		}
	}
	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 {
	// Best-effort — a leftover alias-default row pointing at a deleted signature
	// would just silently fail its JOIN in GetDefaultSignature (falls through to
	// the mailbox-wide default), but there's no reason to leave it dangling.
	d.Exec(`DELETE FROM esrv_mailbox_signature_alias_defaults WHERE signature_id = ?`, id)
	_, 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
}

// ListSignatureAliasDefaults returns every per-alias default override for a mailbox
// (across all its signatures) — used to render "default for <alias>" badges on the
// signatures list without a query per signature.
func (d *DB) ListSignatureAliasDefaults(mailboxID int64) ([]SignatureAliasDefault, error) {
	rows, err := d.Query(`SELECT id, mailbox_id, for_email, for_reply, signature_id FROM esrv_mailbox_signature_alias_defaults WHERE mailbox_id = ?`, mailboxID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []SignatureAliasDefault
	for rows.Next() {
		var s SignatureAliasDefault
		if err := rows.Scan(&s.ID, &s.MailboxID, &s.ForEmail, &s.ForReply, &s.SignatureID); err != nil {
			return nil, err
		}
		out = append(out, s)
	}
	return out, rows.Err()
}

// SetSignatureAliasDefault makes signatureID the default-for-new/default-for-reply
// signature when composing as forEmail (a send-as alias) — upserts so re-setting the
// same scope just repoints it rather than erroring on the UNIQUE constraint.
func (d *DB) SetSignatureAliasDefault(mailboxID, signatureID int64, forEmail string, forReply bool) error {
	_, err := d.Exec(`INSERT INTO esrv_mailbox_signature_alias_defaults (mailbox_id, for_email, for_reply, signature_id) VALUES (?, ?, ?, ?)
		ON CONFLICT(mailbox_id, for_email, for_reply) DO UPDATE SET signature_id = excluded.signature_id`,
		mailboxID, forEmail, forReply, signatureID)
	return err
}

// ClearSignatureAliasDefault removes forEmail's default-for-new/default-for-reply
// override, falling back to the mailbox-wide default again.
func (d *DB) ClearSignatureAliasDefault(mailboxID int64, forEmail string, forReply bool) error {
	_, err := d.Exec(`DELETE FROM esrv_mailbox_signature_alias_defaults WHERE mailbox_id = ? AND for_email = ? AND for_reply = ?`, mailboxID, forEmail, forReply)
	return err
}
