-- Multiple email addresses per user, one marked primary, plus a per-user toggle
-- to show the primary email on the public profile.
ALTER TABLE users ADD COLUMN email_public INTEGER NOT NULL DEFAULT 0;

CREATE TABLE user_emails (
    id          TEXT PRIMARY KEY,
    user_id     TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    email       TEXT NOT NULL,
    email_lower TEXT NOT NULL,
    is_primary  INTEGER NOT NULL DEFAULT 0,
    created_at  BIGINT NOT NULL
);
CREATE UNIQUE INDEX idx_user_emails_lower ON user_emails (email_lower);
CREATE INDEX idx_user_emails_user ON user_emails (user_id);

-- Seed each existing user's current email as their primary address.
INSERT INTO user_emails (id, user_id, email, email_lower, is_primary, created_at)
SELECT id, id, email, email_lower, 1, created_at FROM users;