-- Migration: 00010_waitlist_and_referrals
-- Description: Add access_status gating, referral system, and waitlist queue tables
-- Date: 2025-12-21

-- ============================================
-- ACCESS STATUS ENUM
-- ============================================
DO $$
BEGIN
  CREATE TYPE access_status AS ENUM (
    'onboarding',
    'idv_pending',
    'idv_verified_waitlisted',
    'active',
    'banned'
  );
EXCEPTION
  WHEN duplicate_object THEN NULL;
END $$;

-- ============================================
-- USERS: ACCESS + REFERRAL FIELDS
-- ============================================
ALTER TABLE users
  ADD COLUMN IF NOT EXISTS access_status access_status DEFAULT 'onboarding',
  ADD COLUMN IF NOT EXISTS waitlisted_at TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS activated_at TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS referral_code TEXT UNIQUE,
  ADD COLUMN IF NOT EXISTS referred_by_user_id UUID REFERENCES users(id),
  ADD COLUMN IF NOT EXISTS referral_count_verified INTEGER NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS priority_score INTEGER NOT NULL DEFAULT 0;

CREATE INDEX IF NOT EXISTS idx_users_access_status ON users(access_status);
CREATE INDEX IF NOT EXISTS idx_users_referral_code ON users(referral_code) WHERE referral_code IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_users_referred_by ON users(referred_by_user_id) WHERE referred_by_user_id IS NOT NULL;

-- ============================================
-- REFERRALS TABLE
-- ============================================
DO $$
BEGIN
  CREATE TYPE referral_status AS ENUM ('pending', 'earned', 'revoked');
EXCEPTION
  WHEN duplicate_object THEN NULL;
END $$;

CREATE TABLE IF NOT EXISTS referrals (
  id UUID PRIMARY KEY DEFAULT extensions.gen_random_uuid(),
  referrer_user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  referred_user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  status referral_status NOT NULL DEFAULT 'pending',
  earned_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE (referred_user_id),
  UNIQUE (referrer_user_id, referred_user_id),
  CONSTRAINT referrals_no_self_referral CHECK (referrer_user_id <> referred_user_id)
);

CREATE INDEX IF NOT EXISTS idx_referrals_referrer ON referrals(referrer_user_id);
CREATE INDEX IF NOT EXISTS idx_referrals_referred ON referrals(referred_user_id);
CREATE INDEX IF NOT EXISTS idx_referrals_status ON referrals(status);

ALTER TABLE referrals ENABLE ROW LEVEL SECURITY;

-- Users can view their own referral status (as referrer or referred)
CREATE POLICY "Users can view own referrals"
  ON referrals FOR SELECT
  USING (referrer_user_id = auth.uid() OR referred_user_id = auth.uid());

-- Only service role can insert/update referrals (via Edge Functions/webhooks)
CREATE POLICY "Service role can manage referrals"
  ON referrals FOR ALL
  USING (auth.role() = 'service_role')
  WITH CHECK (auth.role() = 'service_role');

-- ============================================
-- WAITLIST ENTRIES TABLE
-- ============================================
CREATE TABLE IF NOT EXISTS waitlist_entries (
  user_id UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  base_joined_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  priority_score INTEGER NOT NULL DEFAULT 0,
  last_score_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  admitted_at TIMESTAMPTZ,
  daily_batch_day DATE
);

CREATE INDEX IF NOT EXISTS idx_waitlist_admit ON waitlist_entries(admitted_at) WHERE admitted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_waitlist_score ON waitlist_entries(priority_score DESC, base_joined_at ASC)
  WHERE admitted_at IS NULL;

ALTER TABLE waitlist_entries ENABLE ROW LEVEL SECURITY;

-- Users can view their own waitlist entry
CREATE POLICY "Users can view own waitlist entry"
  ON waitlist_entries FOR SELECT
  USING (user_id = auth.uid());

-- Service role manages waitlist
CREATE POLICY "Service role can manage waitlist"
  ON waitlist_entries FOR ALL
  USING (auth.role() = 'service_role')
  WITH CHECK (auth.role() = 'service_role');

-- ============================================
-- REFERRAL CODE GENERATION
-- ============================================
-- Generates a short, non-guessable referral code.
-- Note: We keep it simple + collision-safe via retry loop.
CREATE OR REPLACE FUNCTION generate_referral_code()
RETURNS TEXT AS $$
DECLARE
  candidate TEXT;
  tries INTEGER := 0;
BEGIN
  LOOP
    tries := tries + 1;
    candidate := lower(substr(encode(extensions.gen_random_bytes(8), 'base64'), 1, 10));
    candidate := regexp_replace(candidate, '[^a-z0-9]', '', 'g');
    IF length(candidate) < 8 THEN
      CONTINUE;
    END IF;
    IF NOT EXISTS (SELECT 1 FROM users WHERE referral_code = candidate) THEN
      RETURN candidate;
    END IF;
    IF tries > 10 THEN
      RAISE EXCEPTION 'Failed to generate unique referral_code';
    END IF;
  END LOOP;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public, auth, extensions;

CREATE OR REPLACE FUNCTION ensure_user_referral_code()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.referral_code IS NULL THEN
    NEW.referral_code := generate_referral_code();
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public, auth, extensions;

DROP TRIGGER IF EXISTS trigger_users_referral_code ON users;
CREATE TRIGGER trigger_users_referral_code
  BEFORE INSERT ON users
  FOR EACH ROW
  EXECUTE FUNCTION ensure_user_referral_code();

-- ============================================
-- SCORE COMPUTATION (referral_count_verified * constant)
-- ============================================
CREATE OR REPLACE FUNCTION compute_priority_score(referral_verified_count INTEGER)
RETURNS INTEGER AS $$
BEGIN
  -- Configurable constant (MVP default): 100 points per earned referral.
  RETURN GREATEST(0, referral_verified_count) * 100;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Keep users.priority_score in sync when referral_count_verified changes.
CREATE OR REPLACE FUNCTION sync_user_priority_score()
RETURNS TRIGGER AS $$
BEGIN
  NEW.priority_score := compute_priority_score(NEW.referral_count_verified);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trigger_users_priority_score ON users;
CREATE TRIGGER trigger_users_priority_score
  BEFORE INSERT OR UPDATE OF referral_count_verified ON users
  FOR EACH ROW
  EXECUTE FUNCTION sync_user_priority_score();


