-- Verification (blue tick) system
-- ------------------------------------------------------------------
-- Two changes:
--   1. A `verified` boolean on `profiles` so anyone can cheaply read a
--      user's verification status alongside their display name / avatar
--      (and the app can broadcast it in the WebRTC meta signal).
--   2. A `verification_requests` table where users submit KYC-style
--      details (full legal name, Aadhaar number, mobile number) and
--      admins can approve/reject them. Sensitive columns are kept out
--      of the profiles table so regular users can never query them.

-- 1) Add verification flag to profiles ------------------------------
alter table if exists public.profiles
  add column if not exists verified boolean not null default false;

-- 2) Verification requests table ------------------------------------
create table if not exists public.verification_requests (
  id            uuid primary key default gen_random_uuid(),
  user_id       uuid not null references auth.users(id) on delete cascade,
  -- Applicant-submitted KYC fields. Kept as text because Aadhaar starts
  -- with digits but is displayed as a 12-digit string, and mobile may
  -- include country-code prefixes.
  full_name     text not null,
  aadhaar       text not null,
  mobile        text not null,
  -- Workflow status.
  status        text not null default 'pending'
                check (status in ('pending','approved','rejected')),
  -- Optional reason the admin supplies on rejection.
  rejection_reason text,
  -- Timestamps + who reviewed.
  created_at    timestamptz not null default now(),
  reviewed_at   timestamptz,
  reviewed_by   uuid references auth.users(id)
);

-- Most admin queries filter by status + order by created_at; this index
-- keeps the "Pending requests" screen fast even as the table grows.
create index if not exists verification_requests_status_created_at_idx
  on public.verification_requests (status, created_at desc);

-- Lookup by user (for the profile page to show current status quickly).
create index if not exists verification_requests_user_id_idx
  on public.verification_requests (user_id, created_at desc);

-- 3) RLS ------------------------------------------------------------
alter table public.verification_requests enable row level security;

-- Users can INSERT their own request.
drop policy if exists "verification_requests_insert_own"
  on public.verification_requests;
create policy "verification_requests_insert_own"
  on public.verification_requests
  for insert
  with check (auth.uid() = user_id);

-- Users can SELECT only their own requests (so the profile page can
-- show "pending / approved / rejected" for their latest submission).
drop policy if exists "verification_requests_select_own"
  on public.verification_requests;
create policy "verification_requests_select_own"
  on public.verification_requests
  for select
  using (auth.uid() = user_id);

-- No update/delete policies — admins use the service-role key via
-- server actions / API routes, which bypasses RLS.