CREATE TABLE IF NOT EXISTS user_emails (
  id serial PRIMARY KEY,
  user_id integer NOT NULL REFERENCES auth_users(id) ON DELETE CASCADE,
  email text NOT NULL,
  verified_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX IF NOT EXISTS user_emails_email_unique ON user_emails(email);
CREATE UNIQUE INDEX IF NOT EXISTS user_emails_user_unique ON user_emails(user_id);

CREATE TABLE IF NOT EXISTS email_verification_challenges (
  id serial PRIMARY KEY,
  email text NOT NULL,
  pin_hash text NOT NULL,
  user_id integer REFERENCES auth_users(id) ON DELETE CASCADE,
  ip text,
  attempts integer NOT NULL DEFAULT 0,
  expires_at timestamptz NOT NULL,
  consumed_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS email_challenges_email_idx ON email_verification_challenges(email, created_at DESC);
CREATE UNIQUE INDEX IF NOT EXISTS email_challenges_open_unique ON email_verification_challenges(email) WHERE consumed_at IS NULL;

CREATE TABLE IF NOT EXISTS messenger_login_requests (
  id serial PRIMARY KEY,
  nonce text NOT NULL UNIQUE,
  messenger text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  expires_at timestamptz NOT NULL,
  claimed_at timestamptz,
  claimed_messenger_user_id text,
  claimed_display_name text,
  claimed_phone text,
  phone_confirmed boolean NOT NULL DEFAULT false
);
