Inventario de Seguridad — Backend Functions

BEGIN;

ALTER TABLE "Company" ENABLE ROW LEVEL SECURITY;

-- Policy 1: Admin — lookup directo a tabla User (NO depende de auth.jwt())
CREATE POLICY "company_admin_read"
ON "Company" FOR SELECT TO authenticated
USING (
  EXISTS (
    SELECT 1 FROM "User"
    WHERE id::text = auth.uid()::text
    AND role = 'admin'
  )
  AND (is_archived IS NULL OR is_archived = false)
);

-- Policy 2: Equipo — acceso global por user_type
CREATE POLICY "company_equipo_read"
ON "Company" FOR SELECT TO authenticated
USING (
  EXISTS (
    SELECT 1 FROM "User"
    WHERE id::text = auth.uid()::text
    AND user_type = 'equipo'
  )
  AND (is_archived IS NULL OR is_archived = false)
);

-- Policy 3: Owner — ownership directo
CREATE POLICY "company_owner_read"
ON "Company" FOR SELECT TO authenticated
USING (
  created_by_id::text = auth.uid()::text
  AND (is_archived IS NULL OR is_archived = false)
);

-- Policy 4: Assigned access — CompanyAccess explícito y vigente
CREATE POLICY "company_assigned_read"
ON "Company" FOR SELECT TO authenticated
USING (
  EXISTS (
    SELECT 1 FROM "CompanyAccess"
    WHERE user_id::text = auth.uid()::text
    AND company_id::text = "Company".id::text
    AND status = 'active'
    AND (expires_at IS NULL OR expires_at > now())
  )
  AND (is_archived IS NULL OR is_archived = false)
);

COMMIT;

BEGIN;
DROP POLICY IF EXISTS "company_admin_read"    ON "Company";
DROP POLICY IF EXISTS "company_equipo_read"   ON "Company";
DROP POLICY IF EXISTS "company_owner_read"    ON "Company";
DROP POLICY IF EXISTS "company_assigned_read" ON "Company";
ALTER TABLE "Company" DISABLE ROW LEVEL SECURITY;
COMMIT;