SELECT 
  pu.id,
  pu.email,
  pu.full_name,
  pu.created_at,
  COUNT(DISTINCT pa.entity_id) as entity_count
FROM partner_users pu
LEFT JOIN partner_affiliations pa ON pu.id = pa.partner_user_id
WHERE pu.email ILIKE '%partner@example%'
GROUP BY pu.id;

SELECT 
  po.name as org_name,
  pe.name as entity_name,
  pu.email,
  pa.role
FROM partner_organizations po
JOIN partner_entities pe ON po.id = pe.organization_id
LEFT JOIN partner_affiliations pa ON pe.id = pa.entity_id
LEFT JOIN partner_users pu ON pa.partner_user_id = pu.id
WHERE po.name ILIKE '%University%'
ORDER BY po.name, pe.name;

SELECT 
  pu.*,
  pe.name as entity_name,
  po.name as org_name
FROM partner_users pu
JOIN partner_affiliations pa ON pu.id = pa.partner_user_id
JOIN partner_entities pe ON pa.entity_id = pe.id
LEFT JOIN partner_organizations po ON pe.organization_id = po.id
WHERE pu.created_at > NOW() - INTERVAL '7 days'
ORDER BY pu.created_at DESC;

-- Complete permission check for a user
SELECT 
  pe.name as entity,
  pa.role,
  pa.permissions,
  pcr.display_name as custom_role
FROM partner_affiliations pa
JOIN partner_entities pe ON pa.entity_id = pe.id
LEFT JOIN partner_custom_roles pcr ON pa.role = pcr.role_name
WHERE pa.partner_user_id = 'USER_ID';

-- Find users without expected permissions
SELECT pu.email, pu.full_name
FROM partner_users pu
WHERE NOT EXISTS (
  SELECT 1 FROM partner_affiliations pa
  WHERE pa.partner_user_id = pu.id
  AND pa.role IN ('admin', 'staff')
);

-- Student interactions by partner entity
SELECT 
  pe.name,
  COUNT(DISTINCT ssi.student_id) as unique_students,
  COUNT(*) as total_interactions,
  COUNT(CASE WHEN ssi.liked = true THEN 1 END) as likes,
  COUNT(CASE WHEN ssi.disliked = true THEN 1 END) as dislikes
FROM student_school_interactions ssi
JOIN partner_entities pe ON ssi.partner_entity_id = pe.id
WHERE ssi.interaction_date > NOW() - INTERVAL '30 days'
GROUP BY pe.id, pe.name
ORDER BY unique_students DESC;

-- Recent partner-student conversations
SELECT 
  c.id as conversation_id,
  pe.name as partner_entity,
  COUNT(m.id) as message_count,
  MAX(m.created_at) as last_message
FROM conversations c
JOIN partner_entities pe ON c.partner_entity_id = pe.id
JOIN messages m ON c.id = m.conversation_id
WHERE c.created_at > NOW() - INTERVAL '7 days'
GROUP BY c.id, pe.name
ORDER BY last_message DESC
LIMIT 20;

-- Scholarship view and save metrics
SELECT 
  s.title,
  pe.name as entity_name,
  COUNT(DISTINCT ssi.student_id) as unique_views,
  COUNT(CASE WHEN ssi.saved = true THEN 1 END) as saves,
  s.amount
FROM scholarships s
JOIN partner_entities pe ON s.partner_entity_id = pe.id
LEFT JOIN student_scholarship_interactions ssi ON s.id = ssi.scholarship_id
GROUP BY s.id, s.title, pe.name, s.amount
ORDER BY unique_views DESC;

-- Check authentication attempts
SELECT 
  id,
  user_email,
  ip_address,
  created_at,
  CASE 
    WHEN raw_data->>'event_message' LIKE '%Invalid%' THEN 'Invalid Password'
    WHEN raw_data->>'event_message' LIKE '%not found%' THEN 'User Not Found'
    ELSE raw_data->>'event_message'
  END as error_type
FROM auth.audit_log_entries
WHERE user_email = 'partner@example.com'
AND created_at > NOW() - INTERVAL '24 hours'
ORDER BY created_at DESC;

-- Check invitation codes
SELECT 
  pi.*,
  pu.full_name as invited_by_name,
  pe.name as entity_name
FROM partner_invitations pi
JOIN partner_users pu ON pi.invited_by = pu.id
JOIN partner_entities pe ON pi.entity_id = pe.id
WHERE pi.email = 'invitee@example.com'
OR pi.invitation_code = 'INV-XXXXX';

-- Find affiliations without valid entities
SELECT pa.*
FROM partner_affiliations pa
WHERE NOT EXISTS (
  SELECT 1 FROM partner_entities pe
  WHERE pe.id = pa.entity_id
);

-- Find entities without organizations
SELECT pe.*
FROM partner_entities pe
WHERE pe.organization_id IS NOT NULL
AND NOT EXISTS (
  SELECT 1 FROM partner_organizations po
  WHERE po.id = pe.organization_id
);

-- Find potential duplicate organizations
SELECT 
  name, 
  COUNT(*) as count
FROM partner_organizations
GROUP BY name
HAVING COUNT(*) > 1;

-- Find users with multiple primary affiliations
SELECT 
  partner_user_id,
  COUNT(*) as primary_count
FROM partner_affiliations
WHERE is_primary = true
GROUP BY partner_user_id
HAVING COUNT(*) > 1;

-- Make user admin of their entity
UPDATE partner_affiliations
SET 
  role = 'admin',
  permissions = '{"all": true}'::jsonb
WHERE partner_user_id = 'USER_ID'
AND entity_id = 'ENTITY_ID';

-- Make entity visible in app
UPDATE partner_entities
SET 
  show_in_app = true,
  onboarding_status = 'completed'
WHERE id = 'ENTITY_ID';

-- Extend invitation expiry
UPDATE partner_invitations
SET 
  expires_at = NOW() + INTERVAL '7 days',
  status = 'pending'
WHERE invitation_code = 'INV-XXXXX'
AND status != 'accepted';