-- User management U3: self-service profile + email + email-based
-- password reset infrastructure.
--
--   - users.email: optional; required for the forgot-password flow.
--     Lowercase-unique via a partial index to allow many NULL emails
--     while preventing case-insensitive duplicates.
--   - smtp_config singleton: SMTP server settings + from-address +
--     enabled flag. The actual mailer reads this row at send time so
--     config edits take effect without a server restart.
--   - password_resets: short-lived single-use tokens for the
--     forgot-password flow. 24h TTL enforced at issue time.

ALTER TABLE users ADD COLUMN IF NOT EXISTS email text;
CREATE UNIQUE INDEX IF NOT EXISTS users_email_unique
    ON users (lower(email))
    WHERE email IS NOT NULL;

CREATE TABLE smtp_config (
    id           boolean PRIMARY KEY DEFAULT true,
    enabled      boolean NOT NULL DEFAULT false,
    host         text NOT NULL DEFAULT '',
    port         int NOT NULL DEFAULT 587,
    username     text NOT NULL DEFAULT '',
    password     text NOT NULL DEFAULT '',
    from_address text NOT NULL DEFAULT '',
    from_name    text NOT NULL DEFAULT 'Minstrel',
    use_tls      boolean NOT NULL DEFAULT true,
    updated_at   timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT smtp_config_singleton CHECK (id = true)
);
INSERT INTO smtp_config (id) VALUES (true)
    ON CONFLICT (id) DO NOTHING;

CREATE TABLE password_resets (
    token       text PRIMARY KEY,
    user_id     uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    created_at  timestamptz NOT NULL DEFAULT now(),
    expires_at  timestamptz NOT NULL,
    used_at     timestamptz
);
CREATE INDEX password_resets_user_id_idx ON password_resets (user_id);
CREATE INDEX password_resets_active_idx
    ON password_resets (expires_at)
    WHERE used_at IS NULL;