-- =============================================================================
-- Genesis ReceptionistAI — Supabase Row Level Security Policies
-- =============================================================================
-- Module 5, Story 5.04: RLS Policies
--
-- Enables Row Level Security on all customer-facing tables and creates
-- policies that guarantee each customer can only read/write their own data.
--
-- Run once during project bootstrap via Supabase SQL Editor or migration tool.
--
-- Tables covered:
--   customer_subaivas   — per-customer SubAIVA instances
--   customer_calls      — call records tied to a customer
--   customer_sessions   — active SubAIVA sessions
--   customer_metadata   — extended customer profile fields
--
-- Tier model (locked, Kinan 2026-02-21):
--   starter      $497 AUD/mo
--   professional $997 AUD/mo
--   enterprise   $1,497 AUD/mo
--   queen        $20K+/mo (private — internal only)
--
-- VERIFICATION_STAMP
-- Story: 5.04
-- Verified By: parallel-builder
-- Verified At: 2026-02-25
-- Tests: N/A (SQL — reviewed manually)
-- =============================================================================


-- ---------------------------------------------------------------------------
-- customer_subaivas
-- ---------------------------------------------------------------------------
-- Stores one row per SubAIVA instance assigned to a customer.

ALTER TABLE customer_subaivas ENABLE ROW LEVEL SECURITY;

-- Customers see only their own SubAIVA instances
CREATE POLICY "Users see own SubAIVAs"
  ON customer_subaivas
  FOR SELECT
  USING (auth.uid() = user_id);

-- Customers can create a SubAIVA tied to themselves
CREATE POLICY "Users create own SubAIVAs"
  ON customer_subaivas
  FOR INSERT
  WITH CHECK (auth.uid() = user_id);

-- Customers can update their own SubAIVA (e.g. rename, adjust config)
CREATE POLICY "Users update own SubAIVAs"
  ON customer_subaivas
  FOR UPDATE
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

-- Customers can delete their own SubAIVA (deactivation / cancellation)
CREATE POLICY "Users delete own SubAIVAs"
  ON customer_subaivas
  FOR DELETE
  USING (auth.uid() = user_id);


-- ---------------------------------------------------------------------------
-- customer_calls
-- ---------------------------------------------------------------------------
-- Stores one row per inbound/outbound call handled by a SubAIVA.

ALTER TABLE customer_calls ENABLE ROW LEVEL SECURITY;

-- Customers see only their own call records
CREATE POLICY "Users see own calls"
  ON customer_calls
  FOR SELECT
  USING (auth.uid() = user_id);

-- Supabase backend (service role) inserts call records — no customer INSERT
-- policy needed here. Service role bypasses RLS.

-- Customers cannot update or delete call records (immutable audit trail)


-- ---------------------------------------------------------------------------
-- customer_sessions
-- ---------------------------------------------------------------------------
-- Tracks active SubAIVA sessions (voice calls in progress, chat sessions).

ALTER TABLE customer_sessions ENABLE ROW LEVEL SECURITY;

-- Customers see only their own active sessions
CREATE POLICY "Users see own sessions"
  ON customer_sessions
  FOR SELECT
  USING (auth.uid() = user_id);

-- Service role manages session lifecycle (INSERT/UPDATE/DELETE)


-- ---------------------------------------------------------------------------
-- customer_metadata
-- ---------------------------------------------------------------------------
-- Extended customer profile data: business name, industry, GHL contact ID, etc.

ALTER TABLE customer_metadata ENABLE ROW LEVEL SECURITY;

-- Customers see their own metadata
CREATE POLICY "Users see own metadata"
  ON customer_metadata
  FOR SELECT
  USING (auth.uid() = user_id);

-- Customers can update their own metadata
CREATE POLICY "Users update own metadata"
  ON customer_metadata
  FOR UPDATE
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

-- Metadata row is created by the signup webhook (service role)


-- ---------------------------------------------------------------------------
-- Admin / Service Role Notes
-- ---------------------------------------------------------------------------
-- The Supabase service role key bypasses all RLS policies.
-- Internal Genesis workers (GHL sync, billing webhooks, transcript enrichment)
-- MUST use the service role key — never the anon key.
--
-- Never expose the service role key in client-side code or public repos.
-- Store in: E:\genesis-system\config\secrets.env as SUPABASE_SERVICE_KEY
-- ---------------------------------------------------------------------------
