CREATE OR REPLACE FUNCTION public.fn_admin_partner_detail(p_partner_id bigint)
 RETURNS jsonb
 LANGUAGE plpgsql
 STABLE
AS $function$
DECLARE
  v_partner   JSONB;
  v_roles     JSONB;
  v_tutor     JSONB;
  v_professor JSONB;
  v_student   JSONB;
BEGIN
  SELECT jsonb_build_object(
    'partner_id',    p.partner_id,
    'name',          p.name,
    'surname',       p.surname,
    'email',         p.email::TEXT,
    'phone',         p.phone::TEXT,
    'country',       p.country::TEXT,
    'zinzino_range', p.zinzino_range
  ) INTO v_partner
  FROM public.partners p
  WHERE p.partner_id = p_partner_id;

  IF v_partner IS NULL THEN
    RETURN jsonb_build_object('error', 'Partner no encontrado');
  END IF;

  -- Role flags from view; creation dates still require individual profile joins
  SELECT jsonb_build_object(
    'is_tutor',         v.is_tutor,
    'tutor_since',      tp.created_at,
    'is_professor',     v.is_professor,
    'professor_since',  pp.created_at,
    'is_superadmin',    v.is_superadmin,
    'superadmin_since', sa.created_at,
    'is_student',       v.is_student
  ) INTO v_roles
  FROM public.vw_user_identity_roles v
  LEFT JOIN public.tutor_profiles      tp ON tp.partner_id = v.partner_id
  LEFT JOIN public.professor_profiles  pp ON pp.partner_id = v.partner_id
  LEFT JOIN public.superadmin_profiles sa ON sa.partner_id = v.partner_id
  WHERE v.partner_id = p_partner_id;

  -- Tutor section: all students (partners + guests) via master view
  SELECT jsonb_build_object(
    'student_count', COUNT(*) FILTER (WHERE s.enrollment_status = 'ACTIVE'),
    'students', COALESCE(
      jsonb_agg(
        jsonb_build_object(
          'partner_id', s.student_id,
          'name',       s.student_name,
          'surname',    s.student_surname,
          'email',      s.student_email,
          'level_id',   s.level_id,
          'level_name', s.level_name,
          'status',     s.enrollment_status
        ) ORDER BY s.student_surname, s.student_name
      ), '[]'::jsonb
    )
  ) INTO v_tutor
  FROM public.vw_student_management_master s
  WHERE s.tutor_id = p_partner_id;

  v_professor := jsonb_build_object(
    'levels', COALESCE(
      (SELECT jsonb_agg(
          jsonb_build_object(
            'level_id',   COALESCE(cpl.level_id, 0),
            'level_name', COALESCE(l.name, 'Todos los niveles')
          ) ORDER BY COALESCE(l.order_index, 0)
        )
       FROM   public.course_professor_levels cpl
       LEFT JOIN public.levels l ON l.level_id = cpl.level_id
       WHERE  cpl.professor_id = p_partner_id),
      '[]'::jsonb
    ),
    'session_count', (
      SELECT COUNT(*) FROM public.sessions WHERE created_by = p_partner_id
    )
  );

  -- Student section: own enrollment record
  SELECT jsonb_build_object(
    'tutor_id',    e.tutor_id,
    'tutor_name',  public.fn_full_name(pt.name, pt.surname),
    'tutor_email', COALESCE(pt.email::TEXT, ''),
    'level_id',    e.level_id,
    'level_name',  e.level_name,
    'status',      e.enrollment_status,
    'course_name', COALESCE(c.name, 'STC Core'),
    'enrolled_at', e.updated_at
  ) INTO v_student
  FROM   public.vw_enrolled_student e
  LEFT JOIN public.partners pt ON pt.partner_id = e.tutor_id
  LEFT JOIN public.courses  c  ON c.course_id   = e.course_id
  WHERE  e.student_id = p_partner_id
  LIMIT 1;

  RETURN jsonb_build_object(
    'partner',   v_partner,
    'roles',     v_roles,
    'tutor',     COALESCE(v_tutor,     '{}'::jsonb),
    'professor', COALESCE(v_professor, '{}'::jsonb),
    'student',   v_student
  );
END;
$function$
;