-- Migration: 00002_create_users
-- Description: Create users table extending Supabase Auth with app-specific profile data
-- Author: Auto-generated
-- Date: 2025-12-15

-- ============================================
-- USERS TABLE
-- ============================================
CREATE TABLE users (
  -- Primary key matches Supabase Auth uid
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  
  -- Profile info
  email TEXT NOT NULL UNIQUE,
  username TEXT UNIQUE,  -- Optional display name
  gender gender NOT NULL,
  date_of_birth DATE NOT NULL,  -- For age verification (18+)
  
  -- Location (optional, for filtering)
  city TEXT,
  state TEXT,
  country TEXT DEFAULT 'US',
  
  -- Verification status
  email_verified BOOLEAN DEFAULT FALSE,
  phone_verified BOOLEAN DEFAULT FALSE,
  phone_number TEXT,
  id_verified BOOLEAN DEFAULT FALSE,
  id_verification_status verification_status DEFAULT 'not_started',
  id_verified_at TIMESTAMPTZ,
  
  -- Subscription
  subscription_tier subscription_tier DEFAULT 'free',
  subscription_expires_at TIMESTAMPTZ,
  stripe_customer_id TEXT,  -- For RevenueCat/Stripe
  
  -- Privacy settings
  google_indexing_enabled BOOLEAN DEFAULT TRUE,  -- Opt-out of SEO
  
  -- Moderation
  is_banned BOOLEAN DEFAULT FALSE,
  ban_reason TEXT,
  banned_at TIMESTAMPTZ,
  
  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  last_active_at TIMESTAMPTZ DEFAULT NOW(),
  
  -- Constraints
  CONSTRAINT users_date_of_birth_check CHECK (
    date_of_birth <= (CURRENT_DATE - INTERVAL '18 years')::date
  )
);

-- ============================================
-- INDEXES
-- ============================================
CREATE INDEX idx_users_gender ON users(gender);
CREATE INDEX idx_users_city_state ON users(city, state);
CREATE INDEX idx_users_subscription ON users(subscription_tier);
CREATE INDEX idx_users_id_verified ON users(id_verified);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_username ON users(username) WHERE username IS NOT NULL;

-- ============================================
-- ROW LEVEL SECURITY
-- ============================================
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- Users can read their own profile
CREATE POLICY "Users can view own profile"
  ON users FOR SELECT
  USING (auth.uid() = id);

-- Users can create their own profile (used by the iOS app right after signup)
-- Also verifies the inserted email matches the JWT email claim.
-- NOTE: Avoid referencing auth.users in RLS (not selectable for authenticated users).
CREATE POLICY "Users can create own profile"
  ON users FOR INSERT
  WITH CHECK (
    auth.uid() = id
    AND email = (auth.jwt() ->> 'email')
  );

-- Users can update their own profile
CREATE POLICY "Users can update own profile"
  ON users FOR UPDATE
  USING (auth.uid() = id)
  WITH CHECK (auth.uid() = id);

-- Service role can read all users (for admin/moderation)
CREATE POLICY "Service role can read all users"
  ON users FOR SELECT
  USING (auth.role() = 'service_role');

-- Service role can update all users (for webhooks/admin)
CREATE POLICY "Service role can update all users"
  ON users FOR UPDATE
  USING (auth.role() = 'service_role')
  WITH CHECK (auth.role() = 'service_role');

-- Service role can insert users (for trigger)
CREATE POLICY "Service role can insert users"
  ON users FOR INSERT
  WITH CHECK (auth.role() = 'service_role');

-- ============================================
-- COMMENTS
-- ============================================
COMMENT ON TABLE users IS 'Extended user profiles linked to Supabase Auth';
COMMENT ON COLUMN users.id IS 'Primary key matching auth.users.id';
COMMENT ON COLUMN users.date_of_birth IS 'Used for age verification (must be 18+)';
COMMENT ON COLUMN users.id_verified IS 'Whether user has completed ID verification via Veriff';
COMMENT ON COLUMN users.google_indexing_enabled IS 'Whether user allows their data to be indexed by search engines';

