-- ============================================================================
-- POLITIQUES RLS SÉCURISÉES POUR LA TABLE DOCUMENTS
-- Date : 16 octobre 2025
-- ============================================================================

-- 1. Supprimer les anciennes politiques (si elles existent)
DROP POLICY IF EXISTS documents_client_read ON documents;
DROP POLICY IF EXISTS documents_staff_delete ON documents;
DROP POLICY IF EXISTS documents_staff_insert ON documents;
DROP POLICY IF EXISTS documents_staff_read ON documents;
DROP POLICY IF EXISTS documents_staff_update ON documents;
DROP POLICY IF EXISTS documents_system_delete ON documents;
DROP POLICY IF EXISTS documents_system_insert ON documents;
DROP POLICY IF EXISTS documents_system_update ON documents;

-- 2. S'assurer que RLS est activé
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- ============================================================================
-- POLITIQUES CLIENTS (READ ONLY - Leur organisation uniquement)
-- ============================================================================

CREATE POLICY "clients_can_read_own_org_documents"
ON documents
FOR SELECT
TO authenticated
USING (
  -- Vérifier que l'utilisateur appartient à l'organisation du document
  org_id IN (
    SELECT om.org_id 
    FROM organization_members om
    WHERE om.user_id = auth.uid()
      AND om.revoked = false
  )
  AND
  -- S'assurer que l'utilisateur n'est PAS staff
  NOT EXISTS (
    SELECT 1 
    FROM staff_users su
    WHERE su.user_id = auth.uid()
      AND su.is_staff = true
  )
);

-- ============================================================================
-- POLITIQUES STAFF (Accès complet à toutes les organisations)
-- ============================================================================

CREATE POLICY "staff_can_read_all_documents"
ON documents
FOR SELECT
TO authenticated
USING (
  EXISTS (
    SELECT 1 
    FROM staff_users su
    WHERE su.user_id = auth.uid()
      AND su.is_staff = true
  )
);

CREATE POLICY "staff_can_insert_documents"
ON documents
FOR INSERT
TO authenticated
WITH CHECK (
  EXISTS (
    SELECT 1 
    FROM staff_users su
    WHERE su.user_id = auth.uid()
      AND su.is_staff = true
  )
);

CREATE POLICY "staff_can_update_documents"
ON documents
FOR UPDATE
TO authenticated
USING (
  EXISTS (
    SELECT 1 
    FROM staff_users su
    WHERE su.user_id = auth.uid()
      AND su.is_staff = true
  )
)
WITH CHECK (
  EXISTS (
    SELECT 1 
    FROM staff_users su
    WHERE su.user_id = auth.uid()
      AND su.is_staff = true
  )
);

CREATE POLICY "staff_can_delete_documents"
ON documents
FOR DELETE
TO authenticated
USING (
  EXISTS (
    SELECT 1 
    FROM staff_users su
    WHERE su.user_id = auth.uid()
      AND su.is_staff = true
  )
);

-- ============================================================================
-- POLITIQUES SYSTÈME (Service Role uniquement - pour APIs backend)
-- ============================================================================

-- Ces politiques permettent l'accès via le Service Role Key
-- utilisé dans les APIs backend pour contourner RLS si nécessaire

CREATE POLICY "system_can_read_all_documents"
ON documents
FOR SELECT
TO service_role
USING (true);

CREATE POLICY "system_can_insert_documents"
ON documents
FOR INSERT
TO service_role
WITH CHECK (true);

CREATE POLICY "system_can_update_documents"
ON documents
FOR UPDATE
TO service_role
USING (true)
WITH CHECK (true);

CREATE POLICY "system_can_delete_documents"
ON documents
FOR DELETE
TO service_role
USING (true);

-- ============================================================================
-- VÉRIFICATION DES POLITIQUES
-- ============================================================================

-- Lister toutes les politiques sur la table documents
SELECT 
  schemaname,
  tablename,
  policyname,
  permissive,
  roles,
  cmd,
  qual,
  with_check
FROM pg_policies
WHERE tablename = 'documents'
ORDER BY policyname;

-- ============================================================================
-- TESTS DES POLITIQUES
-- ============================================================================

-- Test 1 : Vérifier qu'un client ne peut voir que les documents de son org
-- (Exécuter en tant que client via l'interface Supabase)
-- SELECT * FROM documents;
-- → Devrait retourner uniquement les documents de son organisation

-- Test 2 : Vérifier qu'un staff peut voir tous les documents
-- (Exécuter en tant que staff)
-- SELECT * FROM documents;
-- → Devrait retourner tous les documents

-- Test 3 : Vérifier qu'un client ne peut pas insérer de documents
-- (Exécuter en tant que client)
-- INSERT INTO documents (org_id, category, filename) VALUES ('uuid', 'test', 'test.pdf');
-- → Devrait échouer avec une erreur de permission

-- ============================================================================
-- MIGRATION NOTES
-- ============================================================================

-- 1. Exécuter ce script dans l'éditeur SQL de Supabase Dashboard
-- 2. Vérifier qu'aucune erreur n'apparaît
-- 3. Tester les accès avec un compte client et un compte staff
-- 4. Si tout fonctionne, déployer les changements dans l'API

-- ============================================================================
-- ROLLBACK (En cas de problème)
-- ============================================================================

-- Pour revenir aux anciennes politiques, exécuter :
-- DROP POLICY IF EXISTS clients_can_read_own_org_documents ON documents;
-- DROP POLICY IF EXISTS staff_can_read_all_documents ON documents;
-- DROP POLICY IF EXISTS staff_can_insert_documents ON documents;
-- DROP POLICY IF EXISTS staff_can_update_documents ON documents;
-- DROP POLICY IF EXISTS staff_can_delete_documents ON documents;
-- DROP POLICY IF EXISTS system_can_read_all_documents ON documents;
-- DROP POLICY IF EXISTS system_can_insert_documents ON documents;
-- DROP POLICY IF EXISTS system_can_update_documents ON documents;
-- DROP POLICY IF EXISTS system_can_delete_documents ON documents;

-- Puis recréer les anciennes politiques si nécessaire