Access Request Approvals

export interface SolicitudAcceso {
  id: string
  user_id: string | null        // Links to auth.users after creation
  nombre: string                 // Full name
  email: string                  // Email address
  sucursal_id: string | null     // Requested branch assignment
  mensaje: string | null         // Optional message from user
  estado: 'pendiente' | 'aprobado' | 'rechazado'
  revisado_por: string | null    // Admin user ID who reviewed
  revisado_at: string | null     // Timestamp of review
  created_at: string             // Request submission time
}

CREATE TABLE IF NOT EXISTS solicitudes_acceso (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id       uuid REFERENCES users(id) ON DELETE CASCADE,
  nombre        text NOT NULL,
  email         text NOT NULL,
  sucursal_id   uuid REFERENCES sucursales(id),
  mensaje       text,
  estado        text NOT NULL DEFAULT 'pendiente'
    CHECK (estado IN ('pendiente','aprobado','rechazado')),
  revisado_por  uuid REFERENCES users(id),
  revisado_at   timestamptz,
  created_at    timestamptz NOT NULL DEFAULT now()
);

<span className="bg-amber-500 text-white text-xs font-bold px-1.5 py-0.5 rounded-full">
  {pendingSolicitudes}
</span>

const { data } = await supabase
  .from('solicitudes_acceso')
  .select('*, sucursal:sucursales(nombre)')
  .order('created_at', { ascending: false })

const aprobar = async (s: SolicitudConSucursal) => {
  if (!s.user_id) return
  
  // Activate the user account
  await supabase
    .from('users')
    .update({ estado_cuenta: 'activo' })
    .eq('id', s.user_id)
  
  // Mark request as approved
  await supabase
    .from('solicitudes_acceso')
    .update({ 
      estado: 'aprobado', 
      revisado_at: new Date().toISOString() 
    })
    .eq('id', s.id)
}

const rechazar = async (s: SolicitudConSucursal) => {
  if (!s.user_id) return
  
  // Deactivate the user account
  await supabase
    .from('users')
    .update({ estado_cuenta: 'inactivo' })
    .eq('id', s.user_id)
  
  // Mark request as rejected
  await supabase
    .from('solicitudes_acceso')
    .update({ 
      estado: 'rechazado', 
      revisado_at: new Date().toISOString() 
    })
    .eq('id', s.id)
}

const estadoBadge = (estado: string) => {
  if (estado === 'pendiente') 
    return 'bg-amber-100 text-amber-700 border border-amber-200'
  if (estado === 'aprobado') 
    return 'bg-green-100 text-green-700 border border-green-200'
  return 'bg-red-100 text-red-700 border border-red-200'
}

<details className="group">
  <summary>Historial revisadas ({revisadas.length})</summary>
  {/* List of approved/rejected requests */}
</details>

-- Admins see all requests
CREATE POLICY "sol_admin_sel" ON solicitudes_acceso FOR SELECT
  USING (EXISTS (
    SELECT 1 FROM users WHERE id = auth.uid() AND rol = 'admin'
  ));

-- Admins can approve/reject
CREATE POLICY "sol_admin_upd" ON solicitudes_acceso FOR UPDATE
  USING (EXISTS (
    SELECT 1 FROM users WHERE id = auth.uid() AND rol = 'admin'
  ));

-- Users can insert (self-registration)
CREATE POLICY "sol_insert" ON solicitudes_acceso FOR INSERT
  WITH CHECK (auth.role() = 'authenticated');

-- Users can view their own requests
CREATE POLICY "sol_own_sel_2" ON solicitudes_acceso FOR SELECT
  USING (user_id = auth.uid());

// Count pending requests
const { count } = await supabase
  .from('solicitudes_acceso')
  .select('id', { count: 'exact', head: true })
  .eq('estado', 'pendiente')
  
setPendingSolicitudes(count ?? 0)

SELECT rol FROM users WHERE id = auth.uid();

UPDATE users 
SET estado_cuenta = 'activo' 
WHERE email = '[email protected]';

-- View old approved/rejected requests
SELECT * FROM solicitudes_acceso
WHERE estado IN ('aprobado', 'rechazado')
  AND revisado_at < NOW() - INTERVAL '6 months'
ORDER BY revisado_at DESC;

-- Optional: Delete very old requests (use with caution)
DELETE FROM solicitudes_acceso
WHERE estado IN ('aprobado', 'rechazado')
  AND revisado_at < NOW() - INTERVAL '1 year';