-- Outgoing mail (M1). The SMTP account lives here, not in forge.toml, so admins change it on
-- /admin/mail. The password is sealed with the instance key (secret::seal).
CREATE TABLE mail_settings (
    id INTEGER PRIMARY KEY CHECK (id = 1),
    host TEXT NOT NULL DEFAULT '',
    port INTEGER NOT NULL DEFAULT 587 CHECK (port BETWEEN 1 AND 65535),
    security TEXT NOT NULL DEFAULT 'starttls' CHECK (security IN ('starttls', 'tls', 'none')),
    username TEXT NOT NULL DEFAULT '',
    password_sealed BLOB,
    from_addr TEXT NOT NULL DEFAULT '',
    from_name TEXT NOT NULL DEFAULT '',
    enabled INTEGER NOT NULL DEFAULT 0 CHECK (enabled IN (0, 1)),
    updated_at TEXT
);
INSERT INTO mail_settings (id) VALUES (1);

-- Durable outbox. `idem_key` makes enqueue idempotent. A row is due when `sent_at` is NULL and
-- `next_attempt_at` is set; `lease_until` keeps two passes from sending the same row. A row with
-- no `next_attempt_at` and no `sent_at` has given up.
CREATE TABLE mail_outbox (
    id TEXT PRIMARY KEY,
    idem_key TEXT NOT NULL UNIQUE,
    to_addr TEXT NOT NULL,
    subject TEXT NOT NULL,
    body_text TEXT NOT NULL,
    created_at TEXT NOT NULL,
    attempts INTEGER NOT NULL DEFAULT 0,
    next_attempt_at TEXT,
    lease_until TEXT,
    sent_at TEXT,
    last_error TEXT
);
CREATE INDEX mail_outbox_due ON mail_outbox (next_attempt_at);
CREATE INDEX mail_outbox_created ON mail_outbox (created_at);

ALTER TABLE users ADD COLUMN email_verified_at TEXT;