-- 20260527_login_tracking.sql
-- One-time OTP codes for 2-step login
CREATE TABLE IF NOT EXISTS login_otp (
  id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email      TEXT NOT NULL,
  code       TEXT NOT NULL,
  expires_at TIMESTAMPTZ NOT NULL,
  used       BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT now() NOT NULL
);

-- Permanent login audit trail
CREATE TABLE IF NOT EXISTS login_logs (
  id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email      TEXT NOT NULL,
  ip_address TEXT,
  user_agent TEXT,
  logged_at  TIMESTAMPTZ DEFAULT now() NOT NULL
);

-- No RLS — backend uses service role key which bypasses RLS anyway
ALTER TABLE login_otp  DISABLE ROW LEVEL SECURITY;
ALTER TABLE login_logs DISABLE ROW LEVEL SECURITY;

-- Index for fast lookup by email
CREATE INDEX IF NOT EXISTS login_otp_email_idx  ON login_otp  (email);
CREATE INDEX IF NOT EXISTS login_logs_email_idx ON login_logs (email);
