CREATE TABLE IF NOT EXISTS user_phones (
  id serial PRIMARY KEY,
  user_id integer NOT NULL REFERENCES auth_users(id) ON DELETE CASCADE,
  phone 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_phones_phone_unique ON user_phones(phone);
CREATE UNIQUE INDEX IF NOT EXISTS user_phones_user_unique ON user_phones(user_id);

CREATE TABLE IF NOT EXISTS phone_verification_challenges (
  id serial PRIMARY KEY,
  phone 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 phone_challenges_phone_idx ON phone_verification_challenges(phone, created_at DESC);
CREATE UNIQUE INDEX IF NOT EXISTS phone_challenges_open_unique ON phone_verification_challenges(phone) WHERE consumed_at IS NULL;
