<commit_before><?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="life.catalogue.db.mapper.NameUsageWrapperMapper">

    <resultMap id="issueResultMap" type="NameUsageWrapper" autoMapping="true">
        <result property="issues" column="issues" typeHandler="life.catalogue.db.type.IssueSetTypeHandler" />
    </resultMap>


    <resultMap id="usageResultMap" type="NameUsageWrapper" autoMapping="true" extends="issueResultMap">
        <id column="uid" />
        <association property="usage">
            <discriminator column="is_synonym" javaType="boolean">
                <case value="false" resultType="Taxon"   resultMap="life.catalogue.db.mapper.TaxonMapper.taxonResultMap"/>
                <case value="true"  resultType="Synonym" resultMap="life.catalogue.db.mapper.SynonymMapper.synonymResultMap"/>
            </discriminator>
        </association>
        <collection property="vernacularNames" ofType="VernacularName" autoMapping="true" columnPrefix="v_" notNullColumn="id">
            <id property="id" column="id"/>
        </collection>
        <collection property="decisions" ofType="SimpleDecision" autoMapping="true" columnPrefix="ed_" notNullColumn="key">
            <id property="key" column="key"/>
        </collection>
    </resultMap>

    <resultMap id="classificationResultMap" type="SimpleNameClassification">
        <id property="id" column="uid"/>
        <result property="classification" column="classification" typeHandler="life.catalogue.db.type2.SimpleNameArrayTypeHandler" />
    </resultMap>

    <resultMap id="taxonGetClassificationResultMap" type="NameUsageWrapper" autoMapping="true" extends="usageResultMap">
        <collection property="classification" ofType="SimpleName" column="{key.id=id,key.datasetKey=dataset_key}" autoMapping="true"
                    select="life.catalogue.db.mapper.TaxonMapper.classificationSimple"/>
    </resultMap>

    <resultMap id="bareNameResultMap" type="NameUsageWrapper" autoMapping="true" extends="issueResultMap">
        <id column="n_id"/>
        <association property="usage" javaType="BareName">
            <id column="n_id" />
            <association property="name" javaType="Name" resultMap="life.catalogue.db.mapper.NameMapper.nameResultMap" columnPrefix="n_" />
        </association>
    </resultMap>

    <sql id="taxonSelectFrom">
        <include refid="life.catalogue.db.mapper.NameUsageMapper.SELECT" />,
        d.gbif_publisher_key AS publisherKey,
        s.subject_dataset_key AS sectorDatasetKey,
        array_distinct(vbn.issues || vbt.issues || xdes.issues || xdis.issues || xmed.issues || xver.issues) AS issues,
        ed.key AS ed_key, ed.dataset_key AS ed_dataset_key, ed.mode AS ed_mode,
        vn.id AS v_id, vn.name AS v_name, vn.latin AS v_latin, vn.language AS v_language
    FROM <include refid="life.catalogue.db.mapper.NameUsageMapper.FROM"/>
        JOIN dataset d ON d.key=u.dataset_key
        LEFT JOIN sector s ON s.key=u.sector_key
        LEFT JOIN verbatim_${partition} vbt ON vbt.id=u.verbatim_key
        LEFT JOIN verbatim_${partition} vbn ON vbn.id=n.verbatim_key
        LEFT JOIN (SELECT taxon_id, array_agg_nonull(v.issues) AS issues FROM description_${partition} x JOIN verbatim_${partition} v ON x.verbatim_key=v.id GROUP BY x.taxon_id) xdes ON xdes.taxon_id=u.id
        LEFT JOIN (SELECT taxon_id, array_agg_nonull(v.issues) AS issues FROM distribution_${partition} x JOIN verbatim_${partition} v ON x.verbatim_key=v.id GROUP BY x.taxon_id) xdis ON xdis.taxon_id=u.id
        LEFT JOIN (SELECT taxon_id, array_agg_nonull(v.issues) AS issues FROM media_${partition} x JOIN verbatim_${partition} v ON x.verbatim_key=v.id GROUP BY x.taxon_id) xmed ON xmed.taxon_id=u.id
        LEFT JOIN (SELECT taxon_id, array_agg_nonull(v.issues) AS issues FROM vernacular_name_${partition} x JOIN verbatim_${partition} v ON x.verbatim_key=v.id GROUP BY x.taxon_id) xver ON xver.taxon_id=u.id
        LEFT JOIN vernacular_name_${partition} vn ON vn.taxon_id=u.id
        LEFT JOIN decision ed ON ed.subject_dataset_key=#{datasetKey} AND ed.subject_id=u.id
    </sql>


    <select id="processTree" parameterType="map" resultMap="classificationResultMap" resultOrdered="true" fetchSize="1000" resultSetType="FORWARD_ONLY">
        <include refid="life.catalogue.db.Common.partitionByDatasetKey"/>
        WITH RECURSIVE tree AS (
            SELECT #{usageId} AS uid, classification_sn(#{datasetKey}, #{usageId}, true) AS classification
        UNION ALL
            SELECT u.id, array_append(tree.classification, (u.id,n.rank,n.scientific_name)::simple_name)
            FROM name_usage_${datasetKey} u JOIN name_${datasetKey} n ON u.name_id=n.id JOIN tree ON (tree.uid = u.parent_id)
            <if test="sectorKey != null">
                WHERE u.sector_key = #{sectorKey}
            </if>
	   )
       SELECT * FROM tree
       <if test="sectorKey != null">
            WHERE uid != #{usageId} OR (SELECT sector_key=#{sectorKey} FROM name_usage_${datasetKey} WHERE id=#{usageId})
       </if>
    </select>

    <select id="processDatasetUsageOnly" parameterType="map" resultMap="usageResultMap" resultOrdered="true" fetchSize="1000" resultSetType="FORWARD_ONLY">
        <include refid="life.catalogue.db.Common.partitionByDatasetKey"/>
        SELECT u.id AS uid, null AS ed_key, null AS v_id,
            <include refid="life.catalogue.db.mapper.NameUsageMapper.SELECT" />,
            vbn.issues || vbt.issues AS issues
        FROM <include refid="life.catalogue.db.mapper.NameUsageMapper.FROM"/>
            LEFT JOIN verbatim_${partition} vbt ON vbt.id=u.verbatim_key
            LEFT JOIN verbatim_${partition} vbn ON vbn.id=n.verbatim_key
        <if test="withIssueOnly"><commit_msg>Also include type material issues in search, see #600<commit_after><?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="life.catalogue.db.mapper.NameUsageWrapperMapper">

    <resultMap id="issueResultMap" type="NameUsageWrapper" autoMapping="true">
        <result property="issues" column="issues" typeHandler="life.catalogue.db.type.IssueSetTypeHandler" />
    </resultMap>


    <resultMap id="usageResultMap" type="NameUsageWrapper" autoMapping="true" extends="issueResultMap">
        <id column="uid" />
        <association property="usage">
            <discriminator column="is_synonym" javaType="boolean">
                <case value="false" resultType="Taxon"   resultMap="life.catalogue.db.mapper.TaxonMapper.taxonResultMap"/>
                <case value="true"  resultType="Synonym" resultMap="life.catalogue.db.mapper.SynonymMapper.synonymResultMap"/>
            </discriminator>
        </association>
        <collection property="vernacularNames" ofType="VernacularName" autoMapping="true" columnPrefix="v_" notNullColumn="id">
            <id property="id" column="id"/>
        </collection>
        <collection property="decisions" ofType="SimpleDecision" autoMapping="true" columnPrefix="ed_" notNullColumn="key">
            <id property="key" column="key"/>
        </collection>
    </resultMap>

    <resultMap id="classificationResultMap" type="SimpleNameClassification">
        <id property="id" column="uid"/>
        <result property="classification" column="classification" typeHandler="life.catalogue.db.type2.SimpleNameArrayTypeHandler" />
    </resultMap>

    <resultMap id="taxonGetClassificationResultMap" type="NameUsageWrapper" autoMapping="true" extends="usageResultMap">
        <collection property="classification" ofType="SimpleName" column="{key.id=id,key.datasetKey=dataset_key}" autoMapping="true"
                    select="life.catalogue.db.mapper.TaxonMapper.classificationSimple"/>
    </resultMap>

    <resultMap id="bareNameResultMap" type="NameUsageWrapper" autoMapping="true" extends="issueResultMap">
        <id column="n_id"/>
        <association property="usage" javaType="BareName">
            <id column="n_id" />
            <association property="name" javaType="Name" resultMap="life.catalogue.db.mapper.NameMapper.nameResultMap" columnPrefix="n_" />
        </association>
    </resultMap>

    <sql id="taxonSelectFrom">
        <include refid="life.catalogue.db.mapper.NameUsageMapper.SELECT" />,
        d.gbif_publisher_key AS publisherKey,
        s.subject_dataset_key AS sectorDatasetKey,
        array_distinct(vbn.issues || vbt.issues || xdes.issues || xdis.issues || xmed.issues || xver.issues || xtyp.issues) AS issues,
        ed.key AS ed_key, ed.dataset_key AS ed_dataset_key, ed.mode AS ed_mode,
        vn.id AS v_id, vn.name AS v_name, vn.latin AS v_latin, vn.language AS v_language
    FROM <include refid="life.catalogue.db.mapper.NameUsageMapper.FROM"/>
        JOIN dataset d ON d.key=u.dataset_key
        LEFT JOIN sector s ON s.key=u.sector_key
        LEFT JOIN verbatim_${partition} vbt ON vbt.id=u.verbatim_key
        LEFT JOIN verbatim_${partition} vbn ON vbn.id=n.verbatim_key
        LEFT JOIN (SELECT taxon_id, array_agg_nonull(v.issues) AS issues FROM description_${partition} x JOIN verbatim_${partition} v ON x.verbatim_key=v.id GROUP BY x.taxon_id) xdes ON xdes.taxon_id=u.id
        LEFT JOIN (SELECT taxon_id, array_agg_nonull(v.issues) AS issues FROM distribution_${partition} x JOIN verbatim_${partition} v ON x.verbatim_key=v.id GROUP BY x.taxon_id) xdis ON xdis.taxon_id=u.id
        LEFT JOIN (SELECT taxon_id, array_agg_nonull(v.issues) AS issues FROM media_${partition} x JOIN verbatim_${partition} v ON x.verbatim_key=v.id GROUP BY x.taxon_id) xmed ON xmed.taxon_id=u.id
        LEFT JOIN (SELECT taxon_id, array_agg_nonull(v.issues) AS issues FROM vernacular_name_${partition} x JOIN verbatim_${partition} v ON x.verbatim_key=v.id GROUP BY x.taxon_id) xver ON xver.taxon_id=u.id
        LEFT JOIN (SELECT name_id, array_agg_nonull(v.issues) AS issues FROM type_material_${partition} x JOIN verbatim_${partition} v ON x.verbatim_key=v.id GROUP BY x.name_id) xtyp ON xtyp.name_id=u.name_id
        LEFT JOIN vernacular_name_${partition} vn ON vn.taxon_id=u.id
        LEFT JOIN decision ed ON ed.subject_dataset_key=#{datasetKey} AND ed.subject_id=u.id
    </sql>


    <select id="processTree" parameterType="map" resultMap="classificationResultMap" resultOrdered="true" fetchSize="1000" resultSetType="FORWARD_ONLY">
        <include refid="life.catalogue.db.Common.partitionByDatasetKey"/>
        WITH RECURSIVE tree AS (
            SELECT #{usageId} AS uid, classification_sn(#{datasetKey}, #{usageId}, true) AS classification
        UNION ALL
            SELECT u.id, array_append(tree.classification, (u.id,n.rank,n.scientific_name)::simple_name)
            FROM name_usage_${datasetKey} u JOIN name_${datasetKey} n ON u.name_id=n.id JOIN tree ON (tree.uid = u.parent_id)
            <if test="sectorKey != null">
                WHERE u.sector_key = #{sectorKey}
            </if>
	   )
       SELECT * FROM tree
       <if test="sectorKey != null">
            WHERE uid != #{usageId} OR (SELECT sector_key=#{sectorKey} FROM name_usage_${datasetKey} WHERE id=#{usageId})
       </if>
    </select>

    <select id="processDatasetUsageOnly" parameterType="map" resultMap="usageResultMap" resultOrdered="true" fetchSize="1000" resultSetType="FORWARD_ONLY">
        <include refid="life.catalogue.db.Common.partitionByDatasetKey"/>
        SELECT u.id AS uid, null AS ed_key, null AS v_id,
            <include refid="life.catalogue.db.mapper.NameUsageMapper.SELECT" />,
            vbn.issues || vbt.issues AS issues
        FROM <include refid="life.catalogue.db.mapper.NameUsageMapper.FROM"/>
            LEFT JOIN verbatim_${partition} vbt ON vbt.id=u.verbatim_key
            LEFT JOIN verbatim_${partition} vbn ON vbn.id=n.verbatim_key
        <if test="withIssueOnly">
{'fraction_non_alphanumeric': 0.12387462487495832, 'fraction_numerical': 0.002750916972324108, 'mean_word_length': 3.69182635901447, 'pattern_counts': {'":': 0, '<': 105, '<?xml version=': 2, '>': 105, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 2}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4943827', 'n_tokens_mistral': 3936, 'n_tokens_neox': 3706, 'n_words': 785}