-- OAuth account linkage.
--
-- One row per (provider, provider_user_id). A single user can have several
-- providers linked (e.g. signed up with email + later linked Google). The
-- provider's `sub` (Google) / `id` (Facebook) is the stable identity — email
-- is captured for display but is NOT the join key, since providers can let
-- users change the email on the account.
--
-- When a callback fires:
--   1. Look up (provider, provider_user_id) — found → log that user in.
--   2. Otherwise look up users.email — found → link new oauth_accounts row
--      to the existing user and log them in.
--   3. Otherwise create a new user (password_hash NULL) + an oauth_accounts
--      row, log them in.
CREATE TABLE IF NOT EXISTS oauth_accounts (
    id                 UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id            UUID         NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    provider           VARCHAR(32)  NOT NULL,                -- "google" | "facebook"
    provider_user_id   TEXT         NOT NULL,                -- sub (Google) / id (Facebook)
    email              CITEXT,
    name               TEXT,
    avatar_url         TEXT,
    created_at         TIMESTAMPTZ  NOT NULL DEFAULT now(),
    updated_at         TIMESTAMPTZ  NOT NULL DEFAULT now(),
    UNIQUE (provider, provider_user_id)
);

CREATE INDEX IF NOT EXISTS oauth_accounts_user_id_idx   ON oauth_accounts (user_id);
CREATE INDEX IF NOT EXISTS oauth_accounts_provider_idx  ON oauth_accounts (provider);

-- Password hash must be optional now: OAuth-only users have no password.
-- Existing email/password users keep their hash; new OAuth users get NULL
-- and can set a password later via the forgot-password flow if they want
-- a fallback login method.
ALTER TABLE users ALTER COLUMN password_hash DROP NOT NULL;