<commit_before>require 'csv'
require 'wcmc_components'

class PameEvaluation < ApplicationRecord
  include WcmcComponents::Loadable

  belongs_to :protected_area, optional: true
  belongs_to :pame_source
  has_and_belongs_to_many :countries

  validates :methodology, :year, :metadata_id, presence: true

  # config for loadable mixin
  ignore_column :designation

  import_by protected_area: :wdpa_id
  import_by countries: :iso_3
  

  TABLE_ATTRIBUTES = [
    {
      title: 'Name',
      field: 'name'
    },
    {
      title: 'Designation',
      field: 'designation'
    },
    {
      title: 'WDPA ID',
      field: 'wdpa_id',
      tooltip: 'Unrestricted Protected Areas can be viewed on Protected Planet'
    },
    {
      title: 'Assessment ID',
      field: 'id'
    },
    {
      title: 'Country',
      field: 'iso3'
    },
    {
      title: 'Methodology',
      field: 'methodology'
    },
    {
      title: 'Year of assessment',
      field: 'year'
    },
    {
      title: 'Link to assessment',
      field: 'url'
    },
    {
      title: 'Metadata ID',
      field: 'metadata_id'
    }
  ].freeze

  def self.paginate_evaluations(json=nil, order=nil)
    json_params = json.nil? ? nil : JSON.parse(json)
    page = json_params.present? ? json_params["requested_page"].to_i : 1

    order = (order && ['ASC', 'DESC'].include?(order.upcase)) ? order : 'DESC'
    evaluations = generate_query(page, json_params["filters"])
    items = serialise(evaluations)
    structure_data(page, items)
  end

  def self.structure_data(page, items)
    {
      current_page: page,
      per_page: 50,
      total_entries: (items.count > 0 ? items[0][:total_entries] : 0),
      total_pages:   (items.count > 0 ? items[0][:total_pages] : 0),
      items: items
    }
  end

  def self.generate_query(page, filter_params)
    # if params are empty then return the paginated results without filtering
    return PameEvaluation.where('protected_area_id IS NOT NULL OR restricted').order('id ASC').paginate(page: page || 1, per_page: 50) if filter_params.empty?

    filters = filter_params.select { |hash| hash["options"].present? }

    where_params = parse_filters(filters)
    run_query(page, where_params)
  end

  def self.parse_filters(filters)
    site_ids = []
    country_ids = []
    where_params = {sites: "", methodology: "", year: "", iso3: ""}
    filters.each do |filter|
      options = filter["options"]
      case filter['name']
      when 'iso3'
        countries = options
        site_ids << countries.map{ |iso3| Country.find_by(iso_3: iso3).protected_areas.pluck(:id) }
        where_params[:sites] = site_ids.flatten.empty? ? nil : "pame_evaluations.protected_area_id IN (#{site_ids.join(',')})"
        country_ids << countries.map{ |iso3| "#{ Country.find_by(iso_3: iso3).id }" }
        where_params[:iso3] = country_ids.flatten.empty? ? nil : "countries.id IN (#{country_ids.join(',')})"
      when 'methodology'
        options = options.map{ |e| "'#{e}'" }
        where_params[:methodology] = options.empty? ? nil : "methodology IN (#{options.join(',')})"
      when 'year'
        where_params[:year] = options.empty? ? nil : "pame_evaluations.year IN (#{options.join(',')})"
      end
    end
    where_params
  end

  def self.run_query(page, where_params)
    if where_params[:sites].present?
      query = PameEvaluation.connection.unprepared_statement {
        "((#{pame_evaluations_from_pa_query(where_params)}) UNION (#{pame_evaluations_from_countries_query(where_params)})) AS pame_evaluations"
      }

      PameEvaluation
      .from(query)
    else
      PameEvaluation
      .where(where_params[:methodology])
      .where(where_params[:year])
    end
    .where("protected_area_id IS NOT NULL OR restricted")
    .paginate(page: page || 1, per_page: 50).order('id ASC')
  end

  def self.pame_evaluations_from_pa_query(where_params)
    PameEvaluation
    .joins(:protected_area)
    .where(where_params[:sites])
    .where(where_params[:methodology])
    .where(where_params[:year])
    .to_sql
  end

  def self.pame_evaluations_from_countries_query(where_params)
    PameEvaluation
    .joins(:countries)
    .where(where_params[:iso3])
    .where(where_params[:methodology])
    .where(where_params[:year])
    .to_sql
  end

  def self.serialise(evaluations)
    evaluations.to_a.map! do |evaluation|

      wdpa_id = evaluation.protected_area&.wdpa_id || evaluation.wdpa_id
      name  = evaluation.protected_area&.name || evaluation.name
      designation = evaluation.protected_area&.designation&.name || "N/A"
      countries = evaluation.protected_area&.countries || evaluation.countries
      iso3 = countries.pluck(:iso_3).sort
      {
        current_page: evaluations.current_page,
        per_page: evaluations.per_page,
        total_entries: evaluations.total_entries,
        total_pages: evaluations.total_pages,
        id: evaluation.id,
        wdpa_id: wdpa_id,
        restricted: evaluation.restricted,
        iso3: iso3,
        methodology: evaluation.methodology,
        year: evaluation.year.to_s,
        url: evaluation.url,
        metadata_id: evaluation.metadata_id,
        source_id: evaluation.pame_source&.id,
        name: name,
        designation: designation,
        data_title: evaluation.pame_source&.data_title,
        resp_party: evaluation.pame_source&.resp_party,
        language: evaluation.pame_source&.language,
        source_year: evaluation.pame_source&.year
      }
    end
  end

  def self.sources_to_json
    sources = PameSource.all.order(id: :asc)
    sources.to_a.map! do |source|
      {
        id: source.id,
        data_title: source.data_title,
        resp_party: source.resp_party,
        year: source.year,
        language: source.language
      }
    end.to_json
  end

  def self.filters_to_json
    unique_methodologies = PameEvaluation.pluck(:methodology).uniq.sort
    unique_iso3 = Country.pluck(:iso_3).uniq.sort
    unique_year = PameEvaluation.pluck(:year).uniq.map(&:to_s).sort

    [
      {
        name: 'methodology',
        title: 'Methodology',
        options: unique_methodologies,
        type: 'multiple'
      },
      {
        name: "iso3",
        title: "Country",
        options: unique_iso3,
        type: 'multiple'
      },
      {
        name: "year",
        title: "Year of assessment",
        options: unique_year,
        type: 'multiple'
      }
    ].to_json
  end
<commit_msg>Make sure to exclude nil values<commit_after>require 'csv'
require 'wcmc_components'

class PameEvaluation < ApplicationRecord
  include WcmcComponents::Loadable

  belongs_to :protected_area, optional: true
  belongs_to :pame_source
  has_and_belongs_to_many :countries

  validates :methodology, :year, :metadata_id, presence: true

  # config for loadable mixin
  ignore_column :designation

  import_by protected_area: :wdpa_id
  import_by countries: :iso_3
  

  TABLE_ATTRIBUTES = [
    {
      title: 'Name',
      field: 'name'
    },
    {
      title: 'Designation',
      field: 'designation'
    },
    {
      title: 'WDPA ID',
      field: 'wdpa_id',
      tooltip: 'Unrestricted Protected Areas can be viewed on Protected Planet'
    },
    {
      title: 'Assessment ID',
      field: 'id'
    },
    {
      title: 'Country',
      field: 'iso3'
    },
    {
      title: 'Methodology',
      field: 'methodology'
    },
    {
      title: 'Year of assessment',
      field: 'year'
    },
    {
      title: 'Link to assessment',
      field: 'url'
    },
    {
      title: 'Metadata ID',
      field: 'metadata_id'
    }
  ].freeze

  def self.paginate_evaluations(json=nil, order=nil)
    json_params = json.nil? ? nil : JSON.parse(json)
    page = json_params.present? ? json_params["requested_page"].to_i : 1

    order = (order && ['ASC', 'DESC'].include?(order.upcase)) ? order : 'DESC'
    evaluations = generate_query(page, json_params["filters"])
    items = serialise(evaluations)
    structure_data(page, items)
  end

  def self.structure_data(page, items)
    {
      current_page: page,
      per_page: 50,
      total_entries: (items.count > 0 ? items[0][:total_entries] : 0),
      total_pages:   (items.count > 0 ? items[0][:total_pages] : 0),
      items: items
    }
  end

  def self.generate_query(page, filter_params)
    # if params are empty then return the paginated results without filtering
    return PameEvaluation.where('protected_area_id IS NOT NULL OR restricted').order('id ASC').paginate(page: page || 1, per_page: 50) if filter_params.empty?

    filters = filter_params.select { |hash| hash["options"].present? }

    where_params = parse_filters(filters)
    run_query(page, where_params)
  end

  def self.parse_filters(filters)
    site_ids = []
    country_ids = []
    where_params = {sites: "", methodology: "", year: "", iso3: ""}
    filters.each do |filter|
      options = filter["options"]
      case filter['name']
      when 'iso3'
        countries = options
        site_ids << countries.map{ |iso3| Country.find_by(iso_3: iso3).protected_areas.pluck(:id) }
        where_params[:sites] = site_ids.flatten.empty? ? nil : "pame_evaluations.protected_area_id IN (#{site_ids.join(',')})"
        country_ids << countries.map{ |iso3| "#{ Country.find_by(iso_3: iso3).id }" }
        where_params[:iso3] = country_ids.flatten.empty? ? nil : "countries.id IN (#{country_ids.join(',')})"
      when 'methodology'
        options = options.map{ |e| "'#{e}'" }
        where_params[:methodology] = options.empty? ? nil : "methodology IN (#{options.join(',')})"
      when 'year'
        where_params[:year] = options.empty? ? nil : "pame_evaluations.year IN (#{options.join(',')})"
      end
    end
    where_params
  end

  def self.run_query(page, where_params)
    if where_params[:sites].present?
      query = PameEvaluation.connection.unprepared_statement {
        "((#{pame_evaluations_from_pa_query(where_params)}) UNION (#{pame_evaluations_from_countries_query(where_params)})) AS pame_evaluations"
      }

      PameEvaluation
      .from(query)
    else
      PameEvaluation
      .where(where_params[:methodology])
      .where(where_params[:year])
    end
    .where("protected_area_id IS NOT NULL OR restricted")
    .paginate(page: page || 1, per_page: 50).order('id ASC')
  end

  def self.pame_evaluations_from_pa_query(where_params)
    PameEvaluation
    .joins(:protected_area)
    .where(where_params[:sites])
    .where(where_params[:methodology])
    .where(where_params[:year])
    .to_sql
  end

  def self.pame_evaluations_from_countries_query(where_params)
    PameEvaluation
    .joins(:countries)
    .where(where_params[:iso3])
    .where(where_params[:methodology])
    .where(where_params[:year])
    .to_sql
  end

  def self.serialise(evaluations)
    evaluations.to_a.map! do |evaluation|

      wdpa_id = evaluation.protected_area&.wdpa_id || evaluation.wdpa_id
      name  = evaluation.protected_area&.name || evaluation.name
      designation = evaluation.protected_area&.designation&.name || "N/A"
      countries = evaluation.protected_area&.countries || evaluation.countries
      iso3 = countries.pluck(:iso_3).sort
      {
        current_page: evaluations.current_page,
        per_page: evaluations.per_page,
        total_entries: evaluations.total_entries,
        total_pages: evaluations.total_pages,
        id: evaluation.id,
        wdpa_id: wdpa_id,
        restricted: evaluation.restricted,
        iso3: iso3,
        methodology: evaluation.methodology,
        year: evaluation.year.to_s,
        url: evaluation.url,
        metadata_id: evaluation.metadata_id,
        source_id: evaluation.pame_source&.id,
        name: name,
        designation: designation,
        data_title: evaluation.pame_source&.data_title,
        resp_party: evaluation.pame_source&.resp_party,
        language: evaluation.pame_source&.language,
        source_year: evaluation.pame_source&.year
      }
    end
  end

  def self.sources_to_json
    sources = PameSource.all.order(id: :asc)
    sources.to_a.map! do |source|
      {
        id: source.id,
        data_title: source.data_title,
        resp_party: source.resp_party,
        year: source.year,
        language: source.language
      }
    end.to_json
  end

  def self.filters_to_json
    unique_methodologies = PameEvaluation.pluck(:methodology).uniq.sort
    unique_iso3 = Country.pluck(:iso_3).compact.uniq.sort
    unique_year = PameEvaluation.pluck(:year).uniq.map(&:to_s).sort

    [
      {
        name: 'methodology',
        title: 'Methodology',
        options: unique_methodologies,
        type: 'multiple'
      },
      {
        name: "iso3",
        title: "Country",
        options: unique_iso3,
        type: 'multiple'
      },
      {
        name: "year",
        title: "Year of assessment",
        options: unique_year,
        type: 'multiple'
      }
    ].to_json
  end

{'fraction_non_alphanumeric': 0.12085785072777949, 'fraction_numerical': 0.005419634561783834, 'mean_word_length': 2.898883187443405, 'pattern_counts': {'":': 0, '<': 13, '<?xml version=': 0, '>': 7, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, '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': '6411645', 'n_tokens_mistral': 4301, 'n_tokens_neox': 4167, 'n_words': 811}