<commit_before>package au.org.biodiversity.nsl

import au.org.biodiversity.nsl.api.ValidationUtils
import grails.converters.JSON
import grails.transaction.Transactional
import groovy.sql.Sql

import javax.sql.DataSource

@Transactional
class TreeReportService implements ValidationUtils {

    DataSource dataSource_nsl

    def treeService

    /**
     * create a difference between the first and second version. Normally the first version would be the currently
     * published version and the second version would be the "newer" draft version.
     *
     * This report is with respect to the second version. i.e. it will report things added too or removed from the
     * second version. 
     *
     * @param first
     * @param second
     * @return
     */
    Map diffReport(TreeVersion first, TreeVersion second) {
        mustHave("version 1": first, "version 2": second)
        use(TreeReportUtils) {
            Sql sql = getSql()

            List<Long> treeElementsNotInSecond = first.notIn(second, sql)
            List<Long> treeElementsNotInFirst = second.notIn(first, sql)
            if (treeElementsNotInSecond.empty && treeElementsNotInFirst.empty) {
                return [v1: first, v2: second, added: [], removed: [], modified: [], changed: false, overflow: false]
            }

            if (treeElementsNotInSecond.size() > 1000 || treeElementsNotInFirst.size() > 1000) {
                return [v1: first, v2: second, added: [], removed: [], modified: [], changed: true, overflow: true]
            }

            List<List<TreeVersionElement>> modified = findModified(first, second, treeElementsNotInFirst)

            List<Long> treeElementsAddedToSecond = treeElementsNotInFirst - modified.collect { mod -> mod[0].treeElement.id }
            List<TreeVersionElement> added = getTvesInVersion(treeElementsAddedToSecond, second)

            List<Long> treeElementsRemovedFromSecond = treeElementsNotInSecond - modified.collect { mod -> mod[1].treeElement.id }
            List<TreeVersionElement> removed = getTvesInVersion(treeElementsRemovedFromSecond, first)

            [v1: first, v2: second, added: added, removed: removed, modified: modified, changed: true, overflow: false]
        }
    }

    private
    static List<List<TreeVersionElement>> findModified(TreeVersion first, TreeVersion second, List<Long> treeElementsNotInFirst) {
        if (treeElementsNotInFirst.empty) {
            return []
        }
        (TreeVersionElement.executeQuery('''
select tve, ptve 
    from TreeVersionElement tve, TreeVersionElement ptve
where tve.treeVersion = :version
    and ptve.treeVersion =:previousVersion
    and ptve.treeElement.nameId = tve.treeElement.nameId
    and tve.treeElement.id in :elementIds
''', [version: second, previousVersion: first, elementIds: treeElementsNotInFirst])) as List<List<TreeVersionElement>>
    }

    private static getTvesInVersion(List<Long> elementIds, TreeVersion version) {
        printf "querying ${elementIds.size()} elements"
        if (elementIds.empty) {
            return []
        }
        return TreeVersionElement.executeQuery(
                'select tve from TreeVersionElement tve where treeVersion = :version and treeElement.id in :elementIds',
                [version: version, elementIds: elementIds]
        )
    }

    Map validateTreeVersion(TreeVersion treeVersion) {
        if (!treeVersion) {
            throw new BadArgumentsException("Tree version needs to be set.")
        }
        Sql sql = getSql()

        Map problems = [:]

        problems.synonymsOfAcceptedNames = checkVersionSynonyms(sql, treeVersion)
        problems.commonSynonyms = sortCommonSynonyms(checkVersionCommonSynonyms(sql, treeVersion))
        return problems
    }

    List<EventRecord> treeEventRecords(Tree tree) {
        Sql sql = getSql()
        List<EventRecord> records = []
        sql.eachRow('''select id
from event_record
where type = 'Synonymy Updated\'
  and dealt_with = false
  and (data ->> 'treeId') :: NUMERIC :: BIGINT = :treeId
''', [treeId: tree.id]) { row ->
            records.add(EventRecord.get(row.id as Long))
        }
        return records
    }


    private static List<Map> checkVersionSynonyms(Sql sql, TreeVersion treeVersion) {
        List<Map> problems = []
        sql.eachRow('''
SELECT
  e1.name_id                                                            AS accepted_name_id,
  e1.simple_name                                                        AS accepted_name,
  '<div class="tr">' || e1.display_html || e1.synonyms_html || '</div>' as accepted_html,
  tve1.element_link                                                     AS accepted_name_tve,
  e2.simple_name                                                        AS synonym_accepted_name,
  '<div class="tr">' || e2.display_html || e2.synonyms_html || '</div>' as synonym_accepted_html,
  tve2.element_link                                                     AS synonym_tve,
  tax_syn                                                               AS synonym_record,
  tax_syn ->> 'type'                                                    AS synonym_type,
  tax_syn ->> 'name_id'                                                 as synonym_name_id,
  tax_syn ->> 'simple_name'                                             as synonym_name
FROM tree_version_element tve1
  JOIN tree_element e1 ON tve1.tree_element_id = e1.id
  ,
  tree_version_element tve2
  JOIN tree_element e2 ON tve2.tree_element_id = e2.id
  ,
      jsonb_array_elements(e2.synonyms -> 'list') AS tax_syn
WHERE tve1.tree_version_id = :treeVersionId
      AND tve2.tree_version_id = :treeVersionId
      AND tve2.tree_element_id <> tve1.tree_element_id
      AND e1.excluded = FALSE
      AND e2.excluded = FALSE
      AND e2.synonyms IS NOT NULL
      AND (tax_syn ->> 'name_id') :: NUMERIC :: BIGINT = e1.name_id
      AND tax_syn ->> 'type' !~ '.*(misapp|pro parte|common|vernacular).*';
      ''', [treeVersionId: treeVersion.id]) { row ->
            Map record = [
                    accepted_name_id     : row.accepted_name_id,
                    accepted_name        : row.accepted_name,
                    accepted_html        : row.accepted_html,
                    accepted_name_tve    : row.accepted_name_tve,
                    synonym_accepted_name: row.synonym_accepted_name,
                    synonym_accepted_html: row.synonym_accepted_html,
                    synonym_tve          : row.synonym_tve,
                    synonym_record       : row.synonym_record,
                    synonym_type         : row.synonym_type,
                    synonym_name_id      : row.synonym_name_id,
                    synonym_name         : row.synonym_name
            ]
            record.description = "Accepted name ${record.accepted_name} is a ${record.synonym_type} of accepted name ${record.synonym_accepted_name}."
            problems.add(record)
        }
        return problems
    }

    @SuppressWarnings("GrMethodMayBeStatic")
    private sortCommonSynonyms(List<Map> commonSynonyms) {
        for (Map result in commonSynonyms) {
            String nameId = result.keySet().first()
            result.elements = result.remove(nameId) as List<Map>
            Name synonym = Name.get(nameId as Long)
            result.commonSynonym = synonym
            if (!synonym) {
                log.error "No name found for ${result.keySet().first()}"
                log.debug result
            }
        }
        return commonSynonyms.sort { it.commonSynonym?.sortName }
    }

    private static List<Map> checkVersionCommonSynonyms(Sql sql, TreeVersion treeVersion) {
        List<Map> problems = []
        sql.eachRow('''
SELECT
  (tax_syn2 ->> 'name_id')       AS common_synonym,
  jsonb_build_object((tax_syn2 ->> 'name_id'),
                     jsonb_agg(jsonb_build_object('html',
                                                  '<div class="tr">' || e1.display_html || e1.synonyms_html || '</div>',
                                                  'name_link', e1.name_link,
                                                  'tree_link', tree.host_name || tve1.element_link,
                                                  'type', tax_syn1 ->> 'type',
                                                  'syn_name_id', tax_syn2 ->> 'name_id\'
                               ))) as names
FROM tree_version_element tve1
  JOIN tree_element e1 ON tve1.tree_element_id = e1.id
  ,
  tree_version_element tve2
  JOIN tree_element e2 ON tve2.tree_element_id = e2.id
  ,
      jsonb_array_elements(e1.synonyms -> 'list') AS tax_syn1,
      jsonb_array_elements(e2.synonyms -> 'list') AS tax_syn2,<commit_msg>NSL-2776 change validation to sort on name path<commit_after>package au.org.biodiversity.nsl

import au.org.biodiversity.nsl.api.ValidationUtils
import grails.converters.JSON
import grails.transaction.Transactional
import groovy.sql.Sql

import javax.sql.DataSource

@Transactional
class TreeReportService implements ValidationUtils {

    DataSource dataSource_nsl

    def treeService

    /**
     * create a difference between the first and second version. Normally the first version would be the currently
     * published version and the second version would be the "newer" draft version.
     *
     * This report is with respect to the second version. i.e. it will report things added too or removed from the
     * second version. 
     *
     * @param first
     * @param second
     * @return
     */
    Map diffReport(TreeVersion first, TreeVersion second) {
        mustHave("version 1": first, "version 2": second)
        use(TreeReportUtils) {
            Sql sql = getSql()

            List<Long> treeElementsNotInSecond = first.notIn(second, sql)
            List<Long> treeElementsNotInFirst = second.notIn(first, sql)
            if (treeElementsNotInSecond.empty && treeElementsNotInFirst.empty) {
                return [v1: first, v2: second, added: [], removed: [], modified: [], changed: false, overflow: false]
            }

            if (treeElementsNotInSecond.size() > 1000 || treeElementsNotInFirst.size() > 1000) {
                return [v1: first, v2: second, added: [], removed: [], modified: [], changed: true, overflow: true]
            }

            List<List<TreeVersionElement>> modified = findModified(first, second, treeElementsNotInFirst)

            List<Long> treeElementsAddedToSecond = treeElementsNotInFirst - modified.collect { mod -> mod[0].treeElement.id }
            List<TreeVersionElement> added = getTvesInVersion(treeElementsAddedToSecond, second)

            List<Long> treeElementsRemovedFromSecond = treeElementsNotInSecond - modified.collect { mod -> mod[1].treeElement.id }
            List<TreeVersionElement> removed = getTvesInVersion(treeElementsRemovedFromSecond, first)

            [v1: first, v2: second, added: added, removed: removed, modified: modified, changed: true, overflow: false]
        }
    }

    private
    static List<List<TreeVersionElement>> findModified(TreeVersion first, TreeVersion second, List<Long> treeElementsNotInFirst) {
        if (treeElementsNotInFirst.empty) {
            return []
        }
        (TreeVersionElement.executeQuery('''
select tve, ptve 
    from TreeVersionElement tve, TreeVersionElement ptve
where tve.treeVersion = :version
    and ptve.treeVersion =:previousVersion
    and ptve.treeElement.nameId = tve.treeElement.nameId
    and tve.treeElement.id in :elementIds
''', [version: second, previousVersion: first, elementIds: treeElementsNotInFirst])) as List<List<TreeVersionElement>>
    }

    private static getTvesInVersion(List<Long> elementIds, TreeVersion version) {
        printf "querying ${elementIds.size()} elements"
        if (elementIds.empty) {
            return []
        }
        return TreeVersionElement.executeQuery(
                'select tve from TreeVersionElement tve where treeVersion = :version and treeElement.id in :elementIds',
                [version: version, elementIds: elementIds]
        )
    }

    Map validateTreeVersion(TreeVersion treeVersion) {
        if (!treeVersion) {
            throw new BadArgumentsException("Tree version needs to be set.")
        }
        Sql sql = getSql()

        Map problems = [:]

        problems.synonymsOfAcceptedNames = checkVersionSynonyms(sql, treeVersion)
        problems.commonSynonyms = sortCommonSynonyms(checkVersionCommonSynonyms(sql, treeVersion))
        return problems
    }

    List<EventRecord> treeEventRecords(Tree tree) {
        Sql sql = getSql()
        List<EventRecord> records = []
        sql.eachRow('''select id
from event_record
where type = 'Synonymy Updated\'
  and dealt_with = false
  and (data ->> 'treeId') :: NUMERIC :: BIGINT = :treeId
''', [treeId: tree.id]) { row ->
            records.add(EventRecord.get(row.id as Long))
        }
        return records
    }


    private static List<Map> checkVersionSynonyms(Sql sql, TreeVersion treeVersion) {
        List<Map> problems = []
        sql.eachRow('''
SELECT
  e1.name_id                                                            AS accepted_name_id,
  e1.simple_name                                                        AS accepted_name,
  '<div class="tr">' || e1.display_html || e1.synonyms_html || '</div>' as accepted_html,
  tve1.element_link                                                     AS accepted_name_tve,
  e2.simple_name                                                        AS synonym_accepted_name,
  '<div class="tr">' || e2.display_html || e2.synonyms_html || '</div>' as synonym_accepted_html,
  tve2.element_link                                                     AS synonym_tve,
  tax_syn                                                               AS synonym_record,
  tax_syn ->> 'type'                                                    AS synonym_type,
  tax_syn ->> 'name_id'                                                 as synonym_name_id,
  tax_syn ->> 'simple_name'                                             as synonym_name
FROM tree_version_element tve1
  JOIN tree_element e1 ON tve1.tree_element_id = e1.id
  ,
  tree_version_element tve2
  JOIN tree_element e2 ON tve2.tree_element_id = e2.id
  ,
      jsonb_array_elements(e2.synonyms -> 'list') AS tax_syn
WHERE tve1.tree_version_id = :treeVersionId
      AND tve2.tree_version_id = :treeVersionId
      AND tve2.tree_element_id <> tve1.tree_element_id
      AND e1.excluded = FALSE
      AND e2.excluded = FALSE
      AND e2.synonyms IS NOT NULL
      AND (tax_syn ->> 'name_id') :: NUMERIC :: BIGINT = e1.name_id
      AND tax_syn ->> 'type' !~ '.*(misapp|pro parte|common|vernacular).*';
      ''', [treeVersionId: treeVersion.id]) { row ->
            Map record = [
                    accepted_name_id     : row.accepted_name_id,
                    accepted_name        : row.accepted_name,
                    accepted_html        : row.accepted_html,
                    accepted_name_tve    : row.accepted_name_tve,
                    synonym_accepted_name: row.synonym_accepted_name,
                    synonym_accepted_html: row.synonym_accepted_html,
                    synonym_tve          : row.synonym_tve,
                    synonym_record       : row.synonym_record,
                    synonym_type         : row.synonym_type,
                    synonym_name_id      : row.synonym_name_id,
                    synonym_name         : row.synonym_name
            ]
            record.description = "Accepted name ${record.accepted_name} is a ${record.synonym_type} of accepted name ${record.synonym_accepted_name}."
            problems.add(record)
        }
        return problems
    }

    @SuppressWarnings("GrMethodMayBeStatic")
    private sortCommonSynonyms(List<Map> commonSynonyms) {
        for (Map result in commonSynonyms) {
            String nameId = result.keySet().first()
            result.elements = result.remove(nameId) as List<Map>
            Name synonym = Name.get(nameId as Long)
            result.commonSynonym = synonym
            if (!synonym) {
                log.error "No name found for ${result.keySet().first()}"
                log.debug result
            }
        }
        return commonSynonyms.sort { it.commonSynonym?.namePath }
    }

    private static List<Map> checkVersionCommonSynonyms(Sql sql, TreeVersion treeVersion) {
        List<Map> problems = []
        sql.eachRow('''
SELECT
  (tax_syn2 ->> 'name_id')       AS common_synonym,
  jsonb_build_object((tax_syn2 ->> 'name_id'),
                     jsonb_agg(jsonb_build_object('html',
                                                  '<div class="tr">' || e1.display_html || e1.synonyms_html || '</div>',
                                                  'name_link', e1.name_link,
                                                  'tree_link', tree.host_name || tve1.element_link,
                                                  'type', tax_syn1 ->> 'type',
                                                  'syn_name_id', tax_syn2 ->> 'name_id\'
                               ))) as names
FROM tree_version_element tve1
  JOIN tree_element e1 ON tve1.tree_element_id = e1.id
  ,
  tree_version_element tve2
  JOIN tree_element e2 ON tve2.tree_element_id = e2.id
  ,
      jsonb_array_elements(e1.synonyms -> 'list') AS tax_syn1,
      jsonb_array_elements(e2.synonyms -> 'list') AS tax_syn2,
{'fraction_non_alphanumeric': 0.08999770852428965, 'fraction_numerical': 0.007561869844179652, 'mean_word_length': 2.0228571428571427, 'pattern_counts': {'":': 4, '<': 61, '<?xml version=': 0, '>': 119, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 58, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3634000', 'n_tokens_mistral': 4906, 'n_tokens_neox': 4773, 'n_words': 1102}