SELECT re.username,
        CASE
            WHEN re.middle_name IS NOT NULL THEN (((re.first_name::text || ' '::text) || re.middle_name::text) || ' '::text) || re.last_name::text
            ELSE (re.first_name::text || ' '::text) || re.last_name::text
        END AS full_name,
    COALESCE(re.affiliation, 'Unknown Affiliation'::character varying) AS affiliation,
    COALESCE(re.bio, 'No Bio'::text) AS bio,
    COALESCE(re.city, 'Unknown Location'::character varying) AS city,
    COALESCE(re.yoe, 0) AS yoe,
    COALESCE(pub.publications, 0::bigint) AS publications,
    COALESCE(pub.citations, 0::numeric) AS citations,
    COALESCE(proj.active_projects, 0::bigint) AS active_projects,
    COALESCE(fld.fields, ''::text) AS fields,
    re.email
   FROM researchers re
     LEFT JOIN ( SELECT rpu.username,
            count(rpu.publication_id) AS publications,
            sum(pu.citations_count) AS citations
           FROM researcher_publication rpu
             JOIN publications pu ON rpu.publication_id = pu.publication_id
          GROUP BY rpu.username) pub ON re.username::text = pub.username::text
     LEFT JOIN ( SELECT rpr.username,
            count(rpr.project_id) AS active_projects
           FROM researcher_project rpr
             JOIN projects pr ON rpr.project_id = pr.project_id AND pr.isactive = true
          GROUP BY rpr.username) proj ON re.username::text = proj.username::text
     LEFT JOIN ( SELECT rf.username,
            string_agg(f.name::text, ', '::text) AS fields
           FROM researcher_field rf
             JOIN fields f ON rf.field_id = f.field_id
          GROUP BY rf.username) fld ON re.username::text = fld.username::text
  GROUP BY re.email, re.username, (
        CASE
            WHEN re.middle_name IS NOT NULL THEN (((re.first_name::text || ' '::text) || re.middle_name::text) || ' '::text) || re.last_name::text
            ELSE (re.first_name::text || ' '::text) || re.last_name::text
        END), re.affiliation, re.bio, re.city, re.yoe, pub.publications, pub.citations, proj.active_projects, fld.fields;