package db

import (
	"database/sql"
	"errors"
)

const contactColumns = `id, mailbox_id, email, name, phone, created_at`

func scanContact(scan func(dest ...any) error) (MailboxContact, error) {
	var c MailboxContact
	var createdAt string
	err := scan(&c.ID, &c.MailboxID, &c.Email, &c.Name, &c.Phone, &createdAt)
	if err != nil {
		return c, err
	}
	c.CreatedAt, _ = parseTime(createdAt)
	return c, nil
}

// ListContacts returns a mailbox's saved contacts, alphabetical by name.
func (d *DB) ListContacts(mailboxID int64) ([]MailboxContact, error) {
	rows, err := d.Query(`SELECT `+contactColumns+` FROM esrv_mailbox_contacts WHERE mailbox_id = ? ORDER BY name COLLATE NOCASE`, mailboxID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []MailboxContact
	for rows.Next() {
		c, err := scanContact(rows.Scan)
		if err != nil {
			return nil, err
		}
		out = append(out, c)
	}
	return out, rows.Err()
}

// GetContactByID scopes the lookup to mailboxID so one mailbox can never read or (via
// UpdateContact/DeleteContact, which reuse this same WHERE clause) modify another's
// contact by guessing an id.
func (d *DB) GetContactByID(mailboxID, id int64) (*MailboxContact, error) {
	row := d.QueryRow(`SELECT `+contactColumns+` FROM esrv_mailbox_contacts WHERE id = ? AND mailbox_id = ?`, id, mailboxID)
	c, err := scanContact(row.Scan)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, nil
		}
		return nil, err
	}
	return &c, nil
}

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

func (d *DB) UpdateContact(mailboxID, id int64, email, name, phone string) error {
	_, err := d.Exec(`UPDATE esrv_mailbox_contacts SET email = ?, name = ?, phone = ? WHERE id = ? AND mailbox_id = ?`, email, name, phone, id, mailboxID)
	return err
}

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