OC4IDS_Quality_Criteria_Checks_and_Metrics.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "toc_visible": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "source": [
        "# OC4IDS Quality Criteria, Checks and Metrics"
      ],
      "metadata": {
        "id": "BZCAUfvh72HR"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Use this notebook to check data against the OC4IDS publication criteria, to perform quality checks and to calculate quality metrics. After running checks, use the status report notebook to report on the results."
      ],
      "metadata": {
        "id": "2WPGV_vzQncX"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## How to use this notebook\n",
        "\n",
        "1. Run the cells in [Setup](#scrollTo=wh6V7iqi76GR)\n",
        "2. [Choose a load](#scrollTo=U4apQasSWqjv) to check\n",
        "3. Run all cells in [Run checks](#scrollTo=OPnM-ojl78gX)"
      ],
      "metadata": {
        "id": "F2GfFVHnRCWp"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Setup"
      ],
      "metadata": {
        "id": "wh6V7iqi76GR"
      }
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "27yRx-oaXPtd",
        "cellView": "form"
      },
      "outputs": [],
      "source": [
        "# @title ### Enter database credentials\n",
        "# @markdown ODS users: Enter the password for the `postgres` user, from the ODS password database.\n",
        "import getpass\n",
        "\n",
        "print('Enter your credentials')\n",
        "user = 'postgres'\n",
        "password = getpass.getpass('Password:')"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "VjuPJaBUMapU",
        "cellView": "form"
      },
      "outputs": [],
      "source": [
        "# @title ### Setup notebook environment\n",
        "\n",
        "!pip install --upgrade ipython-sql > pip.log\n",
        "\n",
        "connection_string = 'postgresql://' + user + ':' + password + '@oc4ids-database-2.cuujgua4wses.us-east-1.rds.amazonaws.com/postgres'\n",
        "\n",
        "# https://pypi.org/project/ipython-sql/\n",
        "%load_ext sql\n",
        "%sql $connection_string\n",
        "%config SqlMagic.autopandas = True  # Return Pandas DataFrames instead of regular result sets\n",
        "%config SqlMagic.displaycon = False  # Don't show connection string after execute\n",
        "%config SqlMagic.feedback = False  # Don't print number of rows affected by DML\n",
        "%config SqlMagic.style = '_DEPRECATED_DEFAULT'\n",
        "\n",
        "# https://colab.research.google.com/notebooks/data_table.ipynb\n",
        "%load_ext google.colab.data_table\n",
        "from google.colab.data_table import DataTable\n",
        "DataTable.max_columns = 50 # Increase max columns so that dataframes with many columns are rendered as data tables\n",
        "DataTable.include_index = False # Remove the index from data tables for easier copy-pasting to Google Docs"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# @title ### Define functions\n",
        "\n",
        "import csv\n",
        "import requests\n",
        "\n",
        "def get_csv(url):\n",
        "\n",
        "  response = requests.get(url)\n",
        "  content = response.content.decode('utf-8').splitlines(keepends=True)\n",
        "\n",
        "  return csv.DictReader(content, quotechar='\"')\n",
        "\n",
        "def add_exchange_rates(dates):\n",
        "\n",
        "  \"\"\"\n",
        "  Fetch exchange rates (USD base currency) for dates and add them to the exchange_rates table in the database.\n",
        "\n",
        "  :dates: A one-dimensional Pandas Dataframe containing dates in YYYY-MM-DD format.\n",
        "  \"\"\"\n",
        "\n",
        "  for date in dates['date']:\n",
        "    r = requests.get(f'https://openexchangerates.org/api/historical/{date}.json?app_id=a937e272181d4ed494e11fcd1fb83c0a&base=USD')\n",
        "\n",
        "    if r.status_code == requests.codes.ok:\n",
        "      rates = r.json()\n",
        "      pairs = json.dumps(rates['rates'])\n",
        "\n",
        "      query = f\"\"\"\n",
        "\n",
        "      INSERT INTO exchange_rates\n",
        "      SELECT\n",
        "          :date AS date,\n",
        "          key AS currency,\n",
        "          value::text::numeric AS rate\n",
        "      FROM\n",
        "          json_each('{pairs}')\n",
        "      WHERE ('{date}', key)\n",
        "      NOT IN ( SELECT DISTINCT\n",
        "              date,\n",
        "              currency\n",
        "          FROM\n",
        "              exchange_rates);\n",
        "\n",
        "      \"\"\"\n",
        "\n",
        "      %sql {query}\n",
        "\n",
        "def calculate_indicator_coverage(run_id, collection_ids, source, indicator, fields):\n",
        "\n",
        "  query = \"\"\"\n",
        "\n",
        "    INSERT INTO indicator_coverage (run_id, collection_id, indicator_source, indicator, fields, successes, checks)\n",
        "    SELECT\n",
        "        :run_id AS run_id,\n",
        "        collection_id,\n",
        "        :source AS indicator_source,\n",
        "        :indicator AS indicator,\n",
        "        jsonb_build_array(ARRAY {fields}) AS fields, SUM(\n",
        "                CASE WHEN ARRAY {fields} <@ paths THEN\n",
        "                    1\n",
        "                ELSE\n",
        "                    0\n",
        "                END) AS successes, count(*) AS checks FROM project_fields\n",
        "            WHERE\n",
        "                collection_id IN :collection_ids GROUP BY collection_id\n",
        "\n",
        "\n",
        "  \"\"\"\n",
        "\n",
        "  query = query.format(fields = fields)\n",
        "\n",
        "  %sql {query}"
      ],
      "metadata": {
        "id": "XjtR-NQpFGKA",
        "cellView": "form"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Choose a load"
      ],
      "metadata": {
        "id": "U4apQasSWqjv"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# @title ### Get a list of loads (collections of collections)\n",
        "\n",
        "%%sql\n",
        "\n",
        "SELECT\n",
        "    load_id,\n",
        "    array_agg(id) AS collection_ids,\n",
        "    min(data_version) AS min_data_version,\n",
        "    max(data_version) AS max_data_version\n",
        "FROM\n",
        "    collection\n",
        "WHERE\n",
        "    load_id IS NOT NULL\n",
        "GROUP BY\n",
        "    load_id\n",
        "ORDER BY\n",
        "    max(data_version) DESC;\n"
      ],
      "metadata": {
        "id": "alK10imnVmgA",
        "cellView": "form"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Choose `load_id`s to check and compare to\n",
        "\n",
        "Some checks compare results between loads. Choose the `load_id`s you want to compare."
      ],
      "metadata": {
        "id": "xKx6nWMUWy8F"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "load_id = '2024-12-30 03:09:14.752061'"
      ],
      "metadata": {
        "id": "7np8LAar6QUK"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "comparison_load_id = '2024-11-27 12:12:45.662233'"
      ],
      "metadata": {
        "id": "yW9mNXEuOHfV"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# @title ### Get collection ids\n",
        "\n",
        "result = %sql select array_agg(id) as collection_ids from collection where load_id = :load_id;\n",
        "collection_ids = tuple(result['collection_ids'][0])"
      ],
      "metadata": {
        "id": "LZutS-1bZcga",
        "cellView": "form"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Run checks"
      ],
      "metadata": {
        "id": "OPnM-ojl78gX"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# @title Set `run_id`\n",
        "\n",
        "from datetime import datetime\n",
        "run_id = datetime.now()"
      ],
      "metadata": {
        "id": "rUPEO9VKaDcT",
        "cellView": "form"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# @title Populate `run_collection` table\n",
        "\n",
        "%%sql\n",
        "\n",
        "INSERT INTO run_collection\n",
        "SELECT\n",
        "    :run_id AS run_id,\n",
        "    id AS collection_id\n",
        "FROM\n",
        "    collection\n",
        "WHERE\n",
        "    load_id = :load_id;\n",
        "\n"
      ],
      "metadata": {
        "id": "CdeApfOiXhOA",
        "cellView": "form"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### criteria_registered"
      ],
      "metadata": {
        "id": "ub2cqkA3j48v"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%sql\n",
        "\n",
        "WITH failures AS (\n",
        "    SELECT\n",
        "        collection_id AS collection_id,\n",
        "        json_object_agg(project_id,\n",
        "        LEFT (project_id, 13)) AS output\n",
        "    FROM\n",
        "        projects\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "        AND\n",
        "        LEFT (project_id,\n",
        "            13)\n",
        "        NOT IN (\n",
        "            SELECT\n",
        "                prefix\n",
        "            FROM\n",
        "                registered_prefixes)\n",
        "        GROUP BY\n",
        "            collection_id)\n",
        "    INSERT INTO check_results (run_id, check_id, collection_id, result, output)\n",
        "    SELECT\n",
        "        :run_id AS run_id,\n",
        "        'criteria_registered' AS check_id,\n",
        "        collection.id AS collection_id,\n",
        "        CASE WHEN output IS NOT NULL THEN\n",
        "            FALSE\n",
        "        ELSE\n",
        "            TRUE\n",
        "        END AS result,\n",
        "        output\n",
        "    FROM\n",
        "        failures\n",
        "    RIGHT JOIN collection ON failures.collection_id = collection.id\n",
        "WHERE\n",
        "    collection.id IN :collection_ids;\n",
        "\n"
      ],
      "metadata": {
        "id": "j5QV-yTgj89D"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### criteria_appropriate"
      ],
      "metadata": {
        "id": "kpZo1Beu_zLD"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%sql\n",
        "\n",
        "WITH additional_fields AS (\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        jsonb_object_agg(key, json_build_object('count', value -> 'count', 'examples', value -> 'examples')) AS output\n",
        "    FROM\n",
        "        collection_check\n",
        "        CROSS JOIN jsonb_each(cove_output -> 'additional_fields')\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "        AND value -> 'additional_field_descendance' IS NULL\n",
        "        AND jsonb_array_length(value -> 'examples') > 0\n",
        "    GROUP BY\n",
        "        collection_id)\n",
        "INSERT INTO check_results (run_id, check_id, collection_id, result, output)\n",
        "SELECT\n",
        "    :run_id AS run_id,\n",
        "    'criteria_appropriate' AS check_id,\n",
        "    collection.id,\n",
        "    NULL AS result,\n",
        "    output\n",
        "FROM\n",
        "    additional_fields\n",
        "    RIGHT JOIN collection ON additional_fields.collection_id = collection.id\n",
        "WHERE\n",
        "    collection.id IN :collection_ids;\n",
        "\n"
      ],
      "metadata": {
        "id": "avTOfygq_1Nk"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### criteria_active"
      ],
      "metadata": {
        "id": "8tWQIyFmC4nW"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%sql\n",
        "\n",
        "INSERT INTO check_results (run_id, check_id, collection_id, result, output)\n",
        "SELECT\n",
        "    :run_id AS run_id,\n",
        "    'criteria_active' AS check_id,\n",
        "    collection_id,\n",
        "    CASE WHEN max(data ->> 'updated')::date >= CURRENT_DATE - interval '12 month' THEN\n",
        "        TRUE\n",
        "    ELSE\n",
        "        FALSE\n",
        "    END AS result,\n",
        "    NULL AS output\n",
        "FROM\n",
        "    projects\n",
        "WHERE\n",
        "    collection_id IN :collection_ids\n",
        "GROUP BY\n",
        "    collection_id;\n",
        "\n"
      ],
      "metadata": {
        "id": "9xK5vQTuC6jf"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### criteria_valid"
      ],
      "metadata": {
        "id": "flMCP98DDWu8"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%sql\n",
        "\n",
        "WITH failures AS (\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        count(*) = 0 AS result\n",
        "    FROM\n",
        "        collection_check\n",
        "        CROSS JOIN jsonb_array_elements(cove_output -> 'validation_errors') AS validation_errors\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "    GROUP BY\n",
        "        collection_id)\n",
        "INSERT INTO check_results (run_id, check_id, collection_id, result, output)\n",
        "SELECT\n",
        "    :run_id AS run_id,\n",
        "    'criteria_valid' AS check_id,\n",
        "    collection.id,\n",
        "    CASE WHEN result IS NOT NULL THEN\n",
        "        FALSE\n",
        "    ELSE\n",
        "        TRUE\n",
        "    END AS result,\n",
        "    NULL AS output\n",
        "FROM\n",
        "    failures\n",
        "    RIGHT JOIN collection ON failures.collection_id = collection.id\n",
        "WHERE\n",
        "    collection.id IN :collection_ids;\n",
        "\n"
      ],
      "metadata": {
        "id": "nvXHNgO7DY60"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### criteria_conformant"
      ],
      "metadata": {
        "id": "MyIJzybNHC_2"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%sql\n",
        "WITH failures AS (\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        jsonb_build_object('count', count(DISTINCT key)) AS output\n",
        "    FROM\n",
        "        collection_check\n",
        "        CROSS JOIN jsonb_each(cove_output -> 'structure_warnings')\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "    GROUP BY\n",
        "        collection_id)\n",
        "INSERT INTO check_results (run_id, check_id, collection_id, result, output)\n",
        "SELECT\n",
        "    :run_id AS run_id,\n",
        "    'criteria_conformant' AS check_id,\n",
        "    collection.id AS collection_id,\n",
        "    CASE WHEN output IS NOT NULL THEN\n",
        "        FALSE\n",
        "    ELSE\n",
        "        TRUE\n",
        "    END AS result,\n",
        "    NULL AS output\n",
        "FROM\n",
        "    failures\n",
        "    RIGHT JOIN collection ON failures.collection_id = collection.id\n",
        "WHERE\n",
        "    collection.id IN :collection_ids;\n",
        "\n"
      ],
      "metadata": {
        "id": "HKyloD0yHFMX"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### metrics_new_projects"
      ],
      "metadata": {
        "id": "lGPvZmg7IVur"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%sql\n",
        "\n",
        "WITH new_projects AS (\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        jsonb_build_object('count', count(project_id)) AS output\n",
        "    FROM\n",
        "        projects\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "        AND project_id NOT IN (\n",
        "            SELECT\n",
        "                project_id\n",
        "            FROM\n",
        "                projects\n",
        "            WHERE\n",
        "                collection_id IN (\n",
        "                    SELECT\n",
        "                        id\n",
        "                    FROM\n",
        "                        collection\n",
        "                    WHERE\n",
        "                        load_id = :comparison_load_id))\n",
        "            GROUP BY\n",
        "                collection_id)\n",
        "    INSERT INTO check_results (run_id, check_id, collection_id, result, output)\n",
        "SELECT\n",
        "    :run_id AS run_id,\n",
        "    'metrics_new_projects' AS check_id,\n",
        "    collection.id AS collection_id,\n",
        "    NULL AS result,\n",
        "    coalesce(output, jsonb_build_object('count', NULL)) AS output\n",
        "FROM\n",
        "    new_projects\n",
        "    RIGHT JOIN collection ON new_projects.collection_id = collection.id\n",
        "WHERE\n",
        "    collection.id IN :collection_ids;\n",
        "\n"
      ],
      "metadata": {
        "id": "w-Izp0U5IYY1"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### metrics_last_updated"
      ],
      "metadata": {
        "id": "3Lh1_AAVOqyP"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%sql\n",
        "\n",
        "INSERT INTO check_results (run_id, check_id, collection_id, result, output)\n",
        "SELECT\n",
        "    :run_id AS run_id,\n",
        "    'metrics_last_updated' AS check_id,\n",
        "    collection_id,\n",
        "    NULL AS result,\n",
        "    jsonb_build_object('date', max(data ->> 'updated')::date) AS output\n",
        "FROM\n",
        "    projects\n",
        "WHERE\n",
        "    collection_id IN :collection_ids\n",
        "GROUP BY\n",
        "    collection_id;\n",
        "\n"
      ],
      "metadata": {
        "id": "57YP0OnbOuHo"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### metrics_earliest_start_date"
      ],
      "metadata": {
        "id": "N3KSMijYPTi7"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%sql\n",
        "\n",
        "INSERT INTO check_results (run_id, check_id, collection_id, result, output)\n",
        "SELECT\n",
        "    :run_id AS run_id,\n",
        "    'metrics_earliest_start_date' AS check_id,\n",
        "    collection_id,\n",
        "    NULL AS result,\n",
        "    jsonb_build_object('date', min(data -> 'period' ->> 'startDate')::date) AS output\n",
        "FROM\n",
        "    projects\n",
        "WHERE\n",
        "    collection_id IN :collection_ids\n",
        "GROUP BY\n",
        "    collection_id;\n",
        "\n"
      ],
      "metadata": {
        "id": "OJfdHWmzPWXH"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### metrics_latest_end_date"
      ],
      "metadata": {
        "id": "VhYv6au1Pu2M"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%sql\n",
        "\n",
        "INSERT INTO check_results (run_id, check_id, collection_id, result, output)\n",
        "SELECT\n",
        "    :run_id AS run_id,\n",
        "    'metrics_latest_end_date' AS check_id,\n",
        "    collection_id,\n",
        "    NULL AS result,\n",
        "    jsonb_build_object('date', max(data -> 'period' ->> 'endDate')::date) AS output\n",
        "FROM\n",
        "    projects\n",
        "WHERE\n",
        "    collection_id IN :collection_ids\n",
        "GROUP BY\n",
        "    collection_id;\n",
        "\n"
      ],
      "metadata": {
        "id": "0VLVKTp9PxjF"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### metrics_additional_field_count"
      ],
      "metadata": {
        "id": "nj0mjGgdQHKM"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%sql\n",
        "\n",
        "WITH counts AS (\n",
        "    SELECT DISTINCT\n",
        "        collection_id,\n",
        "        jsonb_build_object('count', count(DISTINCT key)) AS output\n",
        "    FROM\n",
        "        collection_check\n",
        "        CROSS JOIN jsonb_each(cove_output -> 'additional_fields')\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "    GROUP BY\n",
        "        collection_id)\n",
        "INSERT INTO check_results (run_id, check_id, collection_id, result, output)\n",
        "SELECT\n",
        "    :run_id AS run_id,\n",
        "    'metrics_additional_field_count' AS check_id,\n",
        "    collection.id AS collection_id,\n",
        "    NULL AS result,\n",
        "    coalesce(output, jsonb_build_object('count', NULL))\n",
        "FROM\n",
        "    counts\n",
        "    RIGHT JOIN collection ON counts.collection_id = collection.id\n",
        "WHERE\n",
        "    collection.id IN :collection_ids;\n",
        "\n"
      ],
      "metadata": {
        "id": "ROzU-FngQJJV"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### metrics_project_count"
      ],
      "metadata": {
        "id": "DyJhROFsRHe_"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%sql\n",
        "\n",
        "WITH counts AS (\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        jsonb_build_object('count', count(*)) AS output\n",
        "    FROM\n",
        "        projects\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "    GROUP BY\n",
        "        collection_id)\n",
        "INSERT INTO check_results (run_id, check_id, collection_id, result, output)\n",
        "SELECT\n",
        "    :run_id AS run_id,\n",
        "    'metrics_project_count' AS check_id,\n",
        "    collection.id AS collection_id,\n",
        "    NULL AS result,\n",
        "    coalesce(output, jsonb_build_object('count', NULL)) AS output\n",
        "FROM\n",
        "    counts\n",
        "    RIGHT JOIN collection ON counts.collection_id = collection.id\n",
        "WHERE\n",
        "    collection.id IN :collection_ids;\n",
        "\n"
      ],
      "metadata": {
        "id": "9hkeBZrERJBW"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### metrics_validation_error_count"
      ],
      "metadata": {
        "id": "mw33F8mCT3nr"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%sql\n",
        "\n",
        "WITH counts AS (\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        jsonb_build_object('count', count(DISTINCT replace(trim('\"' FROM (validation_errors -> 0)::text), '\\', '')::jsonb -> 'message')) AS output FROM collection_check\n",
        "                CROSS JOIN jsonb_array_elements(cove_output -> 'validation_errors') AS validation_errors\n",
        "                WHERE\n",
        "                    collection_id IN :collection_ids GROUP BY collection_id)\n",
        "                INSERT INTO check_results (run_id, check_id, collection_id, result, output)\n",
        "                SELECT\n",
        "                    :run_id AS run_id, 'metrics_validation_error_count' AS check_id, collection.id AS collection_id, NULL AS result, coalesce(output, jsonb_build_object('count', NULL))\n",
        "                    FROM counts\n",
        "                RIGHT JOIN collection ON counts.collection_id = collection.id\n",
        "                WHERE\n",
        "                    collection.id IN :collection_ids;\n",
        "\n"
      ],
      "metadata": {
        "id": "6_sjDw5VT83x"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### metrics_structure_warning_count"
      ],
      "metadata": {
        "id": "PY-ubRttUm26"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%sql\n",
        "\n",
        "WITH counts AS (\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        jsonb_build_object('count', count(DISTINCT key)) AS output\n",
        "    FROM\n",
        "        collection_check\n",
        "        CROSS JOIN jsonb_each(cove_output -> 'structure_warnings')\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "    GROUP BY\n",
        "        collection_id)\n",
        "INSERT INTO check_results (run_id, check_id, collection_id, result, output)\n",
        "SELECT\n",
        "    :run_id AS run_id,\n",
        "    'metrics_structure_warning_count' AS check_id,\n",
        "    collection.id AS collection_id,\n",
        "    NULL AS result,\n",
        "    coalesce(output, jsonb_build_object('count', NULL))\n",
        "FROM\n",
        "    counts\n",
        "    RIGHT JOIN collection ON counts.collection_id = collection.id\n",
        "WHERE\n",
        "    collection.id IN :collection_ids;\n",
        "\n"
      ],
      "metadata": {
        "id": "MjAQKESoUq7Z"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### semantics_sector_codelist"
      ],
      "metadata": {
        "id": "-ndjdEFexxDt"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%sql\n",
        "\n",
        "INSERT INTO check_results (run_id, check_id, collection_id, result, output)\n",
        "SELECT\n",
        "    :run_id AS run_id,\n",
        "    'semantics_sector_codelist' AS check_id,\n",
        "    collection_id AS collection_id,\n",
        "    CASE WHEN jsonb_array_length(cove_output -> 'additional_open_codelist_values' -> 'projects/sector' -> 'values') > 0 THEN\n",
        "        FALSE\n",
        "    ELSE\n",
        "        TRUE\n",
        "    END AS result,\n",
        "    jsonb_build_object('all_projects', cove_output -> 'additional_open_codelist_values' -> 'projects/sector' -> 'values') AS output\n",
        "FROM\n",
        "    collection_check\n",
        "WHERE\n",
        "    collection_id IN :collection_ids;\n",
        "\n"
      ],
      "metadata": {
        "id": "8a4ZEwGfwoMl"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### semantics_public_authority_names"
      ],
      "metadata": {
        "id": "AXaZ-gObx97W"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%sql\n",
        "\n",
        "WITH names AS (\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        project_id,\n",
        "        data -> 'publicAuthority' -> 'name' AS name,\n",
        "        row_number() OVER (PARTITION BY collection_id ORDER BY random()) AS rank\n",
        "    FROM\n",
        "        projects\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "        AND data -> 'publicAuthority' -> 'name' IS NOT NULL\n",
        "),\n",
        "output AS (\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        json_object_agg(project_id, name) AS output\n",
        "    FROM\n",
        "        names\n",
        "    WHERE\n",
        "        rank <= 10\n",
        "    GROUP BY\n",
        "        collection_id)\n",
        "INSERT INTO check_results (run_id, check_id, collection_id, result, output)\n",
        "SELECT\n",
        "    :run_id AS run_id,\n",
        "    'semantics_public_authority_names' AS check_id,\n",
        "    collection.id,\n",
        "    NULL AS result,\n",
        "    output\n",
        "FROM\n",
        "    output\n",
        "    RIGHT JOIN collection ON output.collection_id = collection.id\n",
        "WHERE\n",
        "    collection.id IN :collection_ids;\n",
        "\n"
      ],
      "metadata": {
        "id": "zJILuU3a08fj"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### semantics_supplier_names"
      ],
      "metadata": {
        "id": "RRXcQHN37Lcq"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%sql\n",
        "\n",
        "WITH names AS (\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        project_id,\n",
        "        suppliers -> 'name' AS name,\n",
        "        row_number() OVER (PARTITION BY collection_id ORDER BY random()) AS rank\n",
        "    FROM\n",
        "        projects\n",
        "        CROSS JOIN jsonb_array_elements(data -> 'contractingProcesses') AS contractingProcesses\n",
        "        CROSS JOIN jsonb_array_elements(contractingProcesses -> 'summary' -> 'suppliers') AS suppliers\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "        AND data -> 'contractingProcesses' IS NOT NULL\n",
        "        AND contractingProcesses -> 'summary' -> 'suppliers' IS NOT NULL\n",
        "        AND suppliers -> 'name' IS NOT NULL\n",
        "),\n",
        "output AS (\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        json_object_agg(project_id, name) AS output\n",
        "    FROM\n",
        "        names\n",
        "    WHERE\n",
        "        rank <= 10\n",
        "    GROUP BY\n",
        "        collection_id)\n",
        "INSERT INTO check_results (run_id, check_id, collection_id, result, output)\n",
        "SELECT\n",
        "    :run_id AS run_id,\n",
        "    'semantics_supplier_names' AS check_id,\n",
        "    collection.id,\n",
        "    NULL AS result,\n",
        "    output\n",
        "FROM\n",
        "    output\n",
        "    RIGHT JOIN collection ON output.collection_id = collection.id\n",
        "WHERE\n",
        "    collection.id IN :collection_ids;\n",
        "\n"
      ],
      "metadata": {
        "id": "zHq5rvlg7QE5"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### semantics_budgets"
      ],
      "metadata": {
        "id": "SOe5sSwmJYP2"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Get previously unseen dates:"
      ],
      "metadata": {
        "id": "aeR8GiIWQtN6"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%sql dates <<\n",
        "\n",
        "SELECT DISTINCT\n",
        "    coalesce(data -> 'budget' ->> 'approvalDate', data -> 'period' ->> 'startDate')::date AS date\n",
        "FROM\n",
        "    projects\n",
        "WHERE\n",
        "    collection_id IN :collection_ids\n",
        "    AND data -> 'period' -> 'startDate' IS NOT NULL\n",
        "    AND data -> 'budget' -> 'amount' -> 'currency' IS NOT NULL\n",
        "    AND data -> 'budget' -> 'amount' -> 'amount' IS NOT NULL\n",
        "    AND data -> 'budget' -> 'amount' ->> 'currency' != 'USD'\n",
        "    AND coalesce(data -> 'budget' ->> 'approvalDate', data -> 'period' ->> 'startDate')::date NOT IN ( SELECT DISTINCT\n",
        "            date\n",
        "        FROM\n",
        "            exchange_rates)\n",
        "ORDER BY\n",
        "    date ASC;\n",
        "\n"
      ],
      "metadata": {
        "id": "wHUbfK1oO51B"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Add exchange rates to database:"
      ],
      "metadata": {
        "id": "0qwH3VGYdaNr"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "if len(dates) > 0:\n",
        "  add_exchange_rates(dates)"
      ],
      "metadata": {
        "id": "9sjLJls4T5xu"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Run check:"
      ],
      "metadata": {
        "id": "ok3YrS9_dyPt"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%sql\n",
        "\n",
        "WITH numeric_budgets AS (\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        project_id,\n",
        "        coalesce(data -> 'budget' ->> 'approvalDate', data -> 'period' ->> 'startDate')::date AS date,\n",
        "        (data -> 'budget' -> 'amount' -> 'amount')::numeric AS amount,\n",
        "        data -> 'budget' -> 'amount' ->> 'currency' AS currency\n",
        "    FROM\n",
        "        projects\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "        AND jsonb_typeof(DATA -> 'budget' -> 'amount' -> 'amount') = 'number'\n",
        "),\n",
        "usd_budgets AS (\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        project_id,\n",
        "        amount / exchange_rates.rate AS amount_usd\n",
        "    FROM\n",
        "        numeric_budgets\n",
        "        JOIN exchange_rates ON numeric_budgets.date = exchange_rates.date\n",
        "            AND numeric_budgets.currency = exchange_rates.currency\n",
        "),\n",
        "failures AS (\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        json_object_agg(project_id, amount_usd) AS output\n",
        "    FROM\n",
        "        usd_budgets\n",
        "    WHERE\n",
        "        amount_usd <= 0\n",
        "        OR amount_usd >= 5000000000\n",
        "    GROUP BY\n",
        "        collection_id)\n",
        "    insert into\n",
        "      check_results (run_id, check_id, collection_id, result, output)\n",
        "    SELECT\n",
        "        :run_id AS run_id,\n",
        "        'semantics_budgets' AS check_id,\n",
        "        collection.id AS collection_id,\n",
        "        CASE WHEN output IS NOT NULL THEN\n",
        "            FALSE\n",
        "        ELSE\n",
        "            TRUE\n",
        "        END AS result,\n",
        "        output\n",
        "    FROM\n",
        "        failures\n",
        "    RIGHT JOIN collection ON failures.collection_id = collection.id\n",
        "WHERE\n",
        "    collection.id IN :collection_ids;\n",
        "\n"
      ],
      "metadata": {
        "id": "8Px2NkuZJcLk",
        "collapsed": true
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### semantics_contract_values"
      ],
      "metadata": {
        "id": "m_n3JtMNJzmt"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Get previously unseen dates:"
      ],
      "metadata": {
        "id": "_2Q1FSFYLhXH"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%sql dates <<\n",
        "\n",
        "SELECT DISTINCT\n",
        "    coalesce(contracting_processes -> 'summary' -> 'contractPeriod' ->> 'startDate', contracting_processes -> 'summary' -> 'tender' ->> 'datePublished')::date AS date\n",
        "FROM\n",
        "    projects\n",
        "    CROSS JOIN jsonb_array_elements(data -> 'contractingProcesses') AS contracting_processes\n",
        "WHERE\n",
        "    collection_id IN :collection_ids\n",
        "    AND contracting_processes -> 'summary' -> 'contractValue' -> 'currency' IS NOT NULL\n",
        "    AND contracting_processes -> 'summary' -> 'contractValue' -> 'amount' IS NOT NULL\n",
        "    AND contracting_processes -> 'summary' -> 'contractValue' ->> 'currency' != 'USD'\n",
        "    AND coalesce(contracting_processes -> 'summary' -> 'contractPeriod' ->> 'startDate', contracting_processes -> 'summary' -> 'tender' ->> 'datePublished')::date NOT IN ( SELECT DISTINCT\n",
        "            date\n",
        "        FROM\n",
        "            exchange_rates)\n",
        "ORDER BY\n",
        "    date ASC;\n",
        "\n"
      ],
      "metadata": {
        "id": "rUsu-cedJ6s5"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Add exchange rates to database:"
      ],
      "metadata": {
        "id": "J-Yu3jFZMZXY"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "if len(dates) > 0:\n",
        "  add_exchange_rates(dates)"
      ],
      "metadata": {
        "id": "UCpOyJRMMW9z"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Run check:"
      ],
      "metadata": {
        "id": "VfmWWujhMdC1"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%sql\n",
        "\n",
        "WITH numeric_values AS (\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        project_id,\n",
        "        coalesce(contracting_processes -> 'summary' -> 'contractPeriod' ->> 'startDate', contracting_processes -> 'summary' -> 'tender' ->> 'datePublished')::date AS date,\n",
        "        (contracting_processes -> 'summary' -> 'contractValue' -> 'amount')::numeric AS amount,\n",
        "        contracting_processes -> 'summary' -> 'contractValue' ->> 'currency' AS currency\n",
        "    FROM\n",
        "        projects\n",
        "        CROSS JOIN jsonb_array_elements(data -> 'contractingProcesses') AS contracting_processes\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "        AND jsonb_typeof(contracting_processes -> 'summary' -> 'contractValue' -> 'amount') = 'number'\n",
        "),\n",
        "usd_values AS (\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        project_id,\n",
        "        amount / exchange_rates.rate AS amount_usd\n",
        "    FROM\n",
        "        numeric_values\n",
        "        JOIN exchange_rates ON numeric_values.date = exchange_rates.date\n",
        "            AND numeric_values.currency = exchange_rates.currency\n",
        "),\n",
        "failures AS (\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        json_object_agg(project_id, amount_usd) AS output\n",
        "FROM\n",
        "    usd_values\n",
        "WHERE\n",
        "    amount_usd <= 0\n",
        "    OR amount_usd >= 5000000000\n",
        "GROUP BY\n",
        "    collection_id)\n",
        "    INSERT INTO check_results (run_id, check_id, collection_id, result, output)\n",
        "    SELECT\n",
        "        :run_id AS run_id,\n",
        "        'semantics_contract_values' AS check_id,\n",
        "        collection.id AS collection_id,\n",
        "        CASE WHEN output IS NOT NULL THEN\n",
        "            FALSE\n",
        "        ELSE\n",
        "            TRUE\n",
        "        END AS result,\n",
        "        output\n",
        "    FROM\n",
        "        failures\n",
        "    RIGHT JOIN collection ON failures.collection_id = collection.id\n",
        "WHERE\n",
        "    collection.id IN :collection_ids;\n",
        "\n"
      ],
      "metadata": {
        "id": "b8OjyCRkMd4c"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### semantics_funder_names"
      ],
      "metadata": {
        "id": "IfPNw_zv8UpR"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%sql\n",
        "\n",
        "WITH names AS (\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        project_id,\n",
        "        parties -> 'name' AS name,\n",
        "        row_number() OVER (PARTITION BY collection_id ORDER BY random()) AS rank\n",
        "    FROM\n",
        "        projects\n",
        "        CROSS JOIN jsonb_array_elements(data -> 'parties') AS parties\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "        AND data -> 'parties' IS NOT NULL\n",
        "        AND parties -> 'name' IS NOT NULL\n",
        "        AND parties -> 'roles' ? 'funder'\n",
        "),\n",
        "output AS (\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        json_object_agg(project_id, name) AS output\n",
        "    FROM\n",
        "        names\n",
        "    WHERE\n",
        "        rank <= 10\n",
        "    GROUP BY\n",
        "        collection_id)\n",
        "INSERT INTO check_results (run_id, check_id, collection_id, result, output)\n",
        "SELECT\n",
        "    :run_id AS run_id,\n",
        "    'semantics_funder_names' AS check_id,\n",
        "    collection.id,\n",
        "    NULL AS result,\n",
        "    output\n",
        "FROM\n",
        "    output\n",
        "    RIGHT JOIN collection ON output.collection_id = collection.id\n",
        "WHERE\n",
        "    collection.id IN :collection_ids;\n",
        "\n"
      ],
      "metadata": {
        "id": "S6QRUXRM8WQf"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### semantics_dates"
      ],
      "metadata": {
        "id": "B5cCODLI9_mZ"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%sql\n",
        "\n",
        "WITH dates AS (\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        project_id,\n",
        "        'updated' AS path,\n",
        "        data ->> 'updated' AS value\n",
        "    FROM\n",
        "        projects\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "        AND data -> 'updated' IS NOT NULL\n",
        "        AND data ->> 'updated' != ''\n",
        "    UNION\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        project_id,\n",
        "        'period/startDate' AS path,\n",
        "        data -> 'period' ->> 'startDate' AS value\n",
        "    FROM\n",
        "        projects\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "        AND data -> 'period' -> 'startDate' IS NOT NULL\n",
        "        AND data -> 'period' ->> 'startDate' != ''\n",
        "    UNION\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        project_id,\n",
        "        'period/endDate' AS path,\n",
        "        data -> 'period' ->> 'endDate' AS value\n",
        "    FROM\n",
        "        projects\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "        AND data -> 'period' -> 'endDate' IS NOT NULL\n",
        "        AND data -> 'period' ->> 'endDate' != ''\n",
        "    UNION\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        project_id,\n",
        "        'completion/endDate' AS path,\n",
        "        data -> 'completion' ->> 'endDate' AS value\n",
        "    FROM\n",
        "        projects\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "        AND data -> 'completion' IS NOT NULL\n",
        "        AND data -> 'completion' ->> 'endDate' != ''\n",
        "),\n",
        "failures AS (\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        json_object_agg(project_id, value) AS output\n",
        "FROM\n",
        "    dates\n",
        "    WHERE\n",
        "        value::date <= '1970-01-01'::date\n",
        "        OR value::date >= '2050-01-01'::date\n",
        "    GROUP BY\n",
        "        collection_id)\n",
        "INSERT INTO check_results (run_id, check_id, collection_id, result, output)\n",
        "SELECT\n",
        "    :run_id AS run_id,\n",
        "    'semantics_dates' AS check_id,\n",
        "    collection.id,\n",
        "    CASE WHEN output IS NOT NULL THEN\n",
        "        FALSE\n",
        "    ELSE\n",
        "        TRUE\n",
        "    END AS result,\n",
        "    output\n",
        "FROM\n",
        "    failures\n",
        "    RIGHT JOIN collection ON failures.collection_id = collection.id\n",
        "WHERE\n",
        "    collection.id IN :collection_ids;\n",
        "\n"
      ],
      "metadata": {
        "id": "cURSKya5-BlE"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### semantics_role_coherence"
      ],
      "metadata": {
        "id": "V5WbhUvgPOFW"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%sql\n",
        "\n",
        "WITH missing_roles AS (\n",
        "    -- publicAuthority\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        project_id,\n",
        "        'publicAuthority' AS role\n",
        "    FROM\n",
        "        projects\n",
        "        JOIN jsonb_array_elements(data -> 'parties') AS parties ON parties -> 'id' = data -> 'publicAuthority' -> 'id'\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "        AND NOT parties -> 'roles' ? 'publicAuthority'\n",
        "    UNION ALL\n",
        "    -- budget/sourceParty\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        project_id,\n",
        "        'sourceParty' AS role\n",
        "    FROM\n",
        "        projects\n",
        "        JOIN jsonb_array_elements(data -> 'parties') AS parties ON parties -> 'id' = data -> 'budget' -> 'sourceParty' -> 'id'\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "        AND NOT parties -> 'roles' ? 'sourceParty'\n",
        "    UNION ALL\n",
        "    -- contractingProcesses/summary/tender/tenderers\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        project_id,\n",
        "        'tenderer' AS role\n",
        "    FROM\n",
        "        projects\n",
        "        JOIN jsonb_array_elements(data -> 'parties') AS parties\n",
        "        CROSS JOIN jsonb_array_elements(data -> 'contractingProcesses') AS contracting_processes\n",
        "        CROSS JOIN jsonb_array_elements(contracting_processes -> 'summary' -> 'tender' -> 'tenderers') AS tenderers ON parties -> 'id' = tenderers -> 'id'\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "        AND NOT parties -> 'roles' ? 'tenderer'\n",
        "    UNION ALL\n",
        "    -- contractingProcesses/summary/tender/procuringEntity\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        project_id,\n",
        "        'procuringEntity' AS role\n",
        "    FROM\n",
        "        projects\n",
        "        JOIN jsonb_array_elements(data -> 'parties') AS parties\n",
        "        CROSS JOIN jsonb_array_elements(data -> 'contractingProcesses') AS contracting_processes ON parties -> 'id' = contracting_processes -> 'summary' -> 'tender' -> 'procuringEntity' -> 'id'\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "        AND NOT parties -> 'roles' ? 'procuringEntity'\n",
        "    UNION ALL\n",
        "    -- contractingProcesses/summary/tender/administrativeEntity\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        project_id,\n",
        "        'administrativeEntity' AS role\n",
        "    FROM\n",
        "        projects\n",
        "        JOIN jsonb_array_elements(data -> 'parties') AS parties\n",
        "        CROSS JOIN jsonb_array_elements(data -> 'contractingProcesses') AS contracting_processes ON parties -> 'id' = contracting_processes -> 'summary' -> 'tender' -> 'administrativeEntity' -> 'id'\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "        AND NOT parties -> 'roles' ? 'administrativeEntity'\n",
        "    UNION ALL\n",
        "    -- contractingProcesses/summary/suppliers\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        project_id,\n",
        "        'supplier' AS role\n",
        "    FROM\n",
        "        projects\n",
        "        JOIN jsonb_array_elements(data -> 'parties') AS parties\n",
        "        CROSS JOIN jsonb_array_elements(data -> 'contractingProcesses') AS contracting_processes\n",
        "        CROSS JOIN jsonb_array_elements(contracting_processes -> 'summary' -> 'suppliers') AS suppliers ON parties -> 'id' = suppliers -> 'id'\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "        AND NOT parties -> 'roles' ? 'supplier'\n",
        "),\n",
        "failures AS (\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        json_object_agg(project_id, ROLE) AS output\n",
        "    FROM\n",
        "        missing_roles\n",
        "    GROUP BY\n",
        "        collection_id)\n",
        "    INSERT INTO check_results (run_id, check_id, collection_id, result, output)\n",
        "    SELECT\n",
        "        :run_id AS run_id,\n",
        "        'semantics_role_coherence' AS check_id,\n",
        "        collection.id AS collection_id,\n",
        "        CASE WHEN output IS NOT NULL THEN\n",
        "            FALSE\n",
        "        ELSE\n",
        "            TRUE\n",
        "        END AS result,\n",
        "        output\n",
        "    FROM\n",
        "        failures\n",
        "    RIGHT JOIN collection ON failures.collection_id = collection.id\n",
        "WHERE\n",
        "    collection.id IN :collection_ids;\n",
        "\n"
      ],
      "metadata": {
        "id": "7De7a4a5PRRx"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### semantics_coordinates"
      ],
      "metadata": {
        "id": "Eq5kiK6uRIHx"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%sql\n",
        "\n",
        "WITH invalid_coordinates AS (\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        project_id,\n",
        "        locations -> 'geometry' -> 'coordinates' AS coordinates\n",
        "    FROM\n",
        "        projects\n",
        "        CROSS JOIN jsonb_array_elements(data -> 'locations') AS locations\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "        AND locations -> 'geometry' ->> 'type' = 'Point'\n",
        "        AND ((locations -> 'geometry' -> 'coordinates' ->> 0)::numeric NOT BETWEEN -90 AND 90\n",
        "            OR (locations -> 'geometry' -> 'coordinates' ->> 1)::numeric NOT BETWEEN -180 AND 180)\n",
        "    UNION ALL\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        project_id,\n",
        "        locations -> 'geometry' -> 'coordinates' AS coordinates\n",
        "    FROM\n",
        "        projects\n",
        "        CROSS JOIN jsonb_array_elements(data -> 'locations') AS locations\n",
        "        CROSS JOIN jsonb_array_elements(locations -> 'geometry' -> 'coordinates') AS coordinates\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "        AND locations -> 'geometry' -> 'type' ?| ARRAY['Multipoint',\n",
        "        'LineString']\n",
        "        AND ((coordinates ->> 0)::numeric NOT BETWEEN -90 AND 90\n",
        "            OR (coordinates ->> 1)::numeric NOT BETWEEN -180 AND 180)\n",
        "    UNION ALL\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        project_id,\n",
        "        locations -> 'geometry' -> 'coordinates' AS coordinates\n",
        "    FROM\n",
        "        projects\n",
        "        CROSS JOIN jsonb_array_elements(data -> 'locations') AS locations\n",
        "        CROSS JOIN jsonb_array_elements(locations -> 'geometry' -> 'coordinates') AS linear_rings\n",
        "        CROSS JOIN jsonb_array_elements(linear_rings) AS coordinates\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "        AND locations -> 'geometry' -> 'type' ?| ARRAY['Polygon',\n",
        "        'MultiLineString']\n",
        "        AND ((coordinates ->> 0)::numeric NOT BETWEEN -90 AND 90\n",
        "            OR (coordinates ->> 1)::numeric NOT BETWEEN -180 AND 180)\n",
        "    UNION ALL\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        project_id,\n",
        "        locations -> 'geometry' -> 'coordinates' AS coordinates\n",
        "    FROM\n",
        "        projects\n",
        "        CROSS JOIN jsonb_array_elements(data -> 'locations') AS locations\n",
        "        CROSS JOIN jsonb_array_elements(locations -> 'geometry' -> 'coordinates') AS polygons\n",
        "        CROSS JOIN jsonb_array_elements(polygons) AS linear_rings\n",
        "        CROSS JOIN jsonb_array_elements(linear_rings) AS coordinates\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "        AND locations -> 'geometry' ->> 'type' = 'Multipolygon'\n",
        "        AND ((coordinates ->> 0)::numeric BETWEEN -90 AND 90\n",
        "            OR (coordinates ->> 1)::numeric NOT BETWEEN -180 AND 180)\n",
        "),\n",
        "failures AS (\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        json_object_agg(project_id, coordinates) AS output\n",
        "    FROM\n",
        "        invalid_coordinates\n",
        "    GROUP BY\n",
        "        collection_id)\n",
        "    INSERT INTO check_results (run_id, check_id, collection_id, result, output)\n",
        "    SELECT\n",
        "        :run_id AS run_id,\n",
        "        'semantics_coordinates' AS check_id,\n",
        "        collection.id AS collection_id,\n",
        "        CASE WHEN output IS NOT NULL THEN\n",
        "            FALSE\n",
        "        ELSE\n",
        "            TRUE\n",
        "        END AS result,\n",
        "        output\n",
        "    FROM\n",
        "        failures\n",
        "    RIGHT JOIN collection ON failures.collection_id = collection.id\n",
        "WHERE\n",
        "    collection.id IN :collection_ids;\n",
        "\n"
      ],
      "metadata": {
        "id": "j4kNWBM2RKRU"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### coverage_cost_ids"
      ],
      "metadata": {
        "id": "4_4bCVjiir4Y"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "base_url = 'https://standard.open-contracting.org/staging/infrastructure/0.9-dev/en/_static/project-level/'\n",
        "\n",
        "csv_files = [\n",
        "  'process-level-implementation.csv',\n",
        "  'process-level-procurement.csv',\n",
        "  'project-level-completion.csv',\n",
        "  'project-level-identification.csv',\n",
        "  'project-level-preparation.csv',\n",
        "  'reactive-process-level-contract.csv',\n",
        "  'reactive-process-level-implementation.csv',\n",
        "  'reactive-process-level-procurement.csv',\n",
        "  'reactive-project-level-completion.csv',\n",
        "  'reactive-project-level-identification-preparation.csv'\n",
        "        ]\n",
        "\n",
        "for url in [f'{base_url}{filename}' for filename in csv_files]:\n",
        "  mapping_reader = get_csv(url)\n",
        "\n",
        "  for row in mapping_reader:\n",
        "\n",
        "    if len(row['OC4IDS Fields']) > 0:\n",
        "\n",
        "      calculate_indicator_coverage(\n",
        "        run_id = run_id,\n",
        "        collection_ids = collection_ids,\n",
        "        source = 'cost_ids',\n",
        "        indicator = row['CoST IDS element'],\n",
        "        fields = row['OC4IDS Fields'].split(',')\n",
        "      )"
      ],
      "metadata": {
        "id": "e7rWEvfzzuMP"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### coverage_sustainability_modules"
      ],
      "metadata": {
        "id": "ZgjXdgla1uVa"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import yaml\n",
        "\n",
        "url = 'https://standard.open-contracting.org/staging/infrastructure/0.9-dev/en/_static/sustainability.yaml'\n",
        "\n",
        "response = requests.get(url, allow_redirects=True)\n",
        "content = response.content.decode(\"utf-8\")\n",
        "indicators = yaml.safe_load(content)\n",
        "\n",
        "for indicator in indicators:\n",
        "\n",
        "  calculate_indicator_coverage(\n",
        "    run_id = run_id,\n",
        "    collection_ids = collection_ids,\n",
        "    source = 'sustainability_modules',\n",
        "    indicator = f\"{indicator['id']}: {indicator['title']}\",\n",
        "    fields = indicator['fields']\n",
        "  )\n"
      ],
      "metadata": {
        "id": "bAILGlF61xOE"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "CHEePbpgqz0r"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}