SELECT 
    dept_meta.text_value AS department_name,

    /* ---- Total Publications (EXCLUDING e-Print) ---- */
    COUNT(DISTINCT CASE 
        WHEN eprint_meta.text_value IS NULL THEN c2i.item_id 
    END) AS total_publications,

    /* ---- Total Citations (EXCLUDING e-Print) ---- */
    SUM(CASE 
        WHEN eprint_meta.text_value IS NULL 
        THEN COALESCE(pub_cite.citation_count, 0) 
    END) AS total_publication_citations,

    /* ---- Open Access Publications (EXCLUDING e-Print) ---- */
    COUNT(DISTINCT CASE 
        WHEN eprint_meta.text_value IS NULL
         AND oa_meta.text_value != 'close' 
        THEN c2i.item_id 
    END) AS open_access_publications,

    /* ---- Open Access Citations (EXCLUDING e-Print) ---- */
    SUM(CASE 
        WHEN eprint_meta.text_value IS NULL
         AND oa_meta.text_value != 'close' 
        THEN COALESCE(pub_cite.citation_count, 0)
    END) AS open_access_citations,

    /* ---- Scopus Publications (EXCLUDING e-Print) ---- */
    COUNT(DISTINCT CASE 
        WHEN eprint_meta.text_value IS NULL
         AND scopus_meta.dspace_object_id IS NOT NULL
        THEN c2i.item_id 
    END) AS scopus_publications,

    /* ---- Scopus Citations (EXCLUDING e-Print) ---- */
    SUM(CASE 
        WHEN eprint_meta.text_value IS NULL
         AND scopus_meta.dspace_object_id IS NOT NULL
        THEN COALESCE(pub_cite.citation_count, 0)
    END) AS scopus_citations,

    /* ---- WoS Publications (EXCLUDING e-Print) ---- */
    COUNT(DISTINCT CASE 
        WHEN eprint_meta.text_value IS NULL
         AND wos_meta.dspace_object_id IS NOT NULL
        THEN c2i.item_id 
    END) AS wos_publications,

    /* ---- WoS Citations (EXCLUDING e-Print) ---- */
    SUM(CASE 
        WHEN eprint_meta.text_value IS NULL
         AND wos_meta.dspace_object_id IS NOT NULL
        THEN COALESCE(wos_cite_meta.text_value::INTEGER, 0)
    END) AS wos_citations,

    /* ---- Not Indexed (EXCLUDING e-Print) ---- */
    COUNT(DISTINCT CASE 
        WHEN eprint_meta.text_value IS NULL
         AND scopus_meta.dspace_object_id IS NULL 
         AND wos_meta.dspace_object_id IS NULL
        THEN c2i.item_id 
    END) AS not_indexed_publications,

    /* ---- Not Indexed Citations (EXCLUDING e-Print) ---- */
    SUM(CASE 
        WHEN eprint_meta.text_value IS NULL
         AND scopus_meta.dspace_object_id IS NULL 
         AND wos_meta.dspace_object_id IS NULL
        THEN COALESCE(pub_cite.citation_count, 0)
    END) AS not_indexed_citations,

    /* ---- Scopus ENG (EXCLUDING e-Print) ---- */
    COUNT(DISTINCT CASE 
        WHEN eprint_meta.text_value IS NULL
         AND scopus_eng_meta.text_value = 'ENG' 
        THEN c2i.item_id 
    END) AS scopus_eng_publications,

    SUM(CASE 
        WHEN eprint_meta.text_value IS NULL
         AND scopus_eng_meta.text_value = 'ENG' 
        THEN COALESCE(pub_cite.citation_count, 0)
    END) AS scopus_eng_citations,

    /* ---- WoS ENG (EXCLUDING e-Print) ---- */
    COUNT(DISTINCT CASE 
        WHEN eprint_meta.text_value IS NULL
         AND wos_eng_meta.text_value = 'ENG' 
        THEN c2i.item_id 
    END) AS wos_eng_publications,

    SUM(CASE 
        WHEN eprint_meta.text_value IS NULL
         AND wos_eng_meta.text_value = 'ENG'
        THEN COALESCE(wos_cite_meta.text_value::INTEGER, 0)
    END) AS wos_eng_citations,

    /* ---- E-Print Publications ONLY (INCLUDED separately) ---- */
    COUNT(DISTINCT CASE 
        WHEN eprint_meta.text_value = 'e-Print' THEN c2i.item_id 
    END) AS eprint_publications


FROM collection2item c2i


/* Exclusion metadata (remove everything except e-Print) */
LEFT JOIN metadatavalue exclude_meta
    ON exclude_meta.dspace_object_id = c2i.item_id
   AND exclude_meta.metadata_field_id = 84
   AND exclude_meta.text_value IN ('Ph.D.', 'M.Tech', 'M.Sc.', 'Poster Presented')

/* E-Print (separate join) */
LEFT JOIN metadatavalue eprint_meta
    ON eprint_meta.dspace_object_id = c2i.item_id
   AND eprint_meta.metadata_field_id = 84
   AND eprint_meta.text_value = 'e-Print'

/* Community relations */
JOIN community2collection c2c 
    ON c2i.collection_id = c2c.collection_id

JOIN community dept_comm 
    ON c2c.community_id = dept_comm.uuid

/* Department name */
JOIN metadatavalue dept_meta 
    ON dept_meta.dspace_object_id = dept_comm.uuid
   AND dept_meta.metadata_field_id = 82

/* Publication filter */
JOIN metadatavalue pub_meta 
    ON pub_meta.dspace_object_id = c2i.item_id
   AND pub_meta.metadata_field_id = 7
   AND pub_meta.text_value = 'Publication'

/* Citations */
LEFT JOIN (
    SELECT dspace_object_id,
           MAX(text_value::INTEGER) AS citation_count
    FROM metadatavalue
    WHERE metadata_field_id = 496
    GROUP BY dspace_object_id
) pub_cite ON pub_cite.dspace_object_id = c2i.item_id

/* OA */
LEFT JOIN metadatavalue oa_meta 
    ON oa_meta.dspace_object_id = c2i.item_id
   AND oa_meta.metadata_field_id = 487 

/* Scopus */
LEFT JOIN metadatavalue scopus_meta
    ON scopus_meta.dspace_object_id = c2i.item_id
   AND scopus_meta.metadata_field_id = 33

/* WoS */
LEFT JOIN metadatavalue wos_meta
    ON wos_meta.dspace_object_id = c2i.item_id
   AND wos_meta.metadata_field_id = 513

/* WoS citations */
LEFT JOIN metadatavalue wos_cite_meta
    ON wos_cite_meta.dspace_object_id = c2i.item_id
   AND wos_cite_meta.metadata_field_id = 505

/* Scopus ENG */
LEFT JOIN metadatavalue scopus_eng_meta
    ON scopus_eng_meta.dspace_object_id = c2i.item_id
   AND scopus_eng_meta.metadata_field_id = 515

/* WoS ENG */
LEFT JOIN metadatavalue wos_eng_meta
    ON wos_eng_meta.dspace_object_id = c2i.item_id
   AND wos_eng_meta.metadata_field_id = 516


/* Keep only items NOT excluded OR those that are e-Print */
WHERE (
    exclude_meta.dspace_object_id IS NULL
    OR eprint_meta.text_value = 'e-Print'
)

GROUP BY dept_meta.text_value
ORDER BY total_publications DESC;

-- ================================================================= Person Publication Count Query=====================================================================


SELECT
    p_item.item_id AS person_id,
    p_name.text_value AS person_name,

    /* ---- Total Publications ---- */
    COUNT(DISTINCT pub_author.dspace_object_id) AS total_publications,

    /* ---- Total Citations ---- */
    SUM(COALESCE(pub_cite.citation_count, 0)) AS total_publication_citations,

    /* ---- Open Access Publications ---- */
	    COUNT(DISTINCT CASE
		    WHEN oa_meta.text_value IS NOT NULL 
		     AND oa_meta.text_value IN ('green','gold','hybrid','bronze')
		    THEN pub_author.dspace_object_id
		END) AS open_access_publications,


    /* ---- Open Access Citations ---- */
    SUM(CASE
    WHEN oa_meta.text_value IS NOT NULL 
     AND oa_meta.text_value IN ('green','gold','hybrid','bronze')
    THEN COALESCE(pub_cite.citation_count, 0)
END) AS open_access_citations,

    /* ---- Scopus Publications ---- */
    COUNT(DISTINCT CASE
        WHEN scopus_meta.dspace_object_id IS NOT NULL
        THEN pub_author.dspace_object_id
    END) AS scopus_publications,

    /* ---- Scopus Citations ---- */
    SUM(CASE
        WHEN scopus_meta.dspace_object_id IS NOT NULL
        THEN COALESCE(pub_cite.citation_count, 0)
    END) AS scopus_citations,

    /* ---- WoS Publications ---- */
    COUNT(DISTINCT CASE
        WHEN wos_meta.dspace_object_id IS NOT NULL
        THEN pub_author.dspace_object_id
    END) AS wos_publications,

    /* ---- WoS Citations ---- */
    SUM(CASE
        WHEN wos_meta.dspace_object_id IS NOT NULL
        THEN COALESCE(wos_cite_meta.text_value::INTEGER, 0)
    END) AS wos_citations,

    /* ---- Not Indexed Publications ---- */
    COUNT(DISTINCT CASE
        WHEN scopus_meta.dspace_object_id IS NULL
         AND wos_meta.dspace_object_id IS NULL
        THEN pub_author.dspace_object_id
    END) AS not_indexed_publications,

    /* ---- Not Indexed Citations ---- */
    SUM(CASE
        WHEN scopus_meta.dspace_object_id IS NULL
         AND wos_meta.dspace_object_id IS NULL
        THEN COALESCE(pub_cite.citation_count, 0)
    END) AS not_indexed_citations,

    /* ---- Scopus ENG ---- */
    COUNT(DISTINCT CASE
        WHEN scopus_eng_meta.text_value = 'ENG'
        THEN pub_author.dspace_object_id
    END) AS scopus_eng_publications,

    SUM(CASE
        WHEN scopus_eng_meta.text_value = 'ENG'
        THEN COALESCE(pub_cite.citation_count, 0)
    END) AS scopus_eng_citations,

    /* ---- WoS ENG ---- */
    COUNT(DISTINCT CASE
        WHEN wos_eng_meta.text_value = 'ENG'
        THEN pub_author.dspace_object_id
    END) AS wos_eng_publications,

    SUM(CASE
        WHEN wos_eng_meta.text_value = 'ENG'
        THEN COALESCE(wos_cite_meta.text_value::INTEGER, 0)
    END) AS wos_eng_citations

FROM collection2item AS p_item

/* Person type */
JOIN metadatavalue AS p_type
    ON p_type.dspace_object_id = p_item.item_id
   AND p_type.metadata_field_id = 7
   AND p_type.text_value = 'Person'

/* Person name */
JOIN metadatavalue AS p_name
    ON p_name.dspace_object_id = p_item.item_id
   AND p_name.metadata_field_id = 82

/* Author rows linking person → publication */
LEFT JOIN metadatavalue AS pub_author
    ON pub_author.metadata_field_id = 10
   AND pub_author.authority = p_item.item_id::text

/* Ensure the linked item is actually a Publication */
LEFT JOIN metadatavalue AS pub_type
    ON pub_type.dspace_object_id = pub_author.dspace_object_id
   AND pub_type.metadata_field_id = 7
   AND pub_type.text_value = 'Publication'

/* Citations */
LEFT JOIN (
    SELECT dspace_object_id,
           MAX(text_value::INTEGER) AS citation_count
    FROM metadatavalue
    WHERE metadata_field_id = 496
    GROUP BY dspace_object_id
) pub_cite ON pub_cite.dspace_object_id = pub_author.dspace_object_id

/* OA */
LEFT JOIN metadatavalue AS oa_meta
    ON oa_meta.dspace_object_id = pub_author.dspace_object_id
   AND oa_meta.metadata_field_id = 487

/* Scopus */
LEFT JOIN metadatavalue AS scopus_meta
    ON scopus_meta.dspace_object_id = pub_author.dspace_object_id
   AND scopus_meta.metadata_field_id = 33

/* WoS */
LEFT JOIN metadatavalue AS wos_meta
    ON wos_meta.dspace_object_id = pub_author.dspace_object_id
   AND wos_meta.metadata_field_id = 513

/* WoS citations */
LEFT JOIN metadatavalue AS wos_cite_meta
    ON wos_cite_meta.dspace_object_id = pub_author.dspace_object_id
   AND wos_cite_meta.metadata_field_id = 505

/* Scopus ENG */
LEFT JOIN metadatavalue AS scopus_eng_meta
    ON scopus_eng_meta.dspace_object_id = pub_author.dspace_object_id
   AND scopus_eng_meta.metadata_field_id = 515

/* WoS ENG */
LEFT JOIN metadatavalue AS wos_eng_meta
    ON wos_eng_meta.dspace_object_id = pub_author.dspace_object_id
   AND wos_eng_meta.metadata_field_id = 516

/* Keep only actual publications */
WHERE pub_type.dspace_object_id IS NOT NULL

GROUP BY p_item.item_id, p_name.text_value
ORDER BY total_publications DESC;
