#!/bin/sh -e
# NTSB CAROL database query script
# Outputs all investigation reports from the last 500 days as JSON.
# About CAROL: https://data.ntsb.gov/carol-main-public/
# CAROL help: https://www.ntsb.gov/pages/carol.aspx
# CAROL API documentation: https://data.ntsb.gov/carol-main-public/api-docs/
# CAROL Report Generator API documentation: https://data.ntsb.gov/carol-main-public/api-docs/

tmpfile=$(mktemp /tmp/itsb.XXXXXXXXXX)
trap "rm \"$tmpfile\"" EXIT

session_id="$(curl --fail -s https://data.ntsb.gov/carol-main-public/api/Session/CreateSession -H 'Content-Type: application/json' --data 'null')"
curl --fail -s 'https://data.ntsb.gov/carol-main-public/api/Query/FileExport' -H 'content-type: application/json' --data '{
  "QueryGroups": [
    {
      "QueryRules": [
        {
          "RuleType": "Simple",
          "Values": [
            "true"
          ],
          "Columns": [
            "Case.OpenClosedStatus"
          ],
          "Operator": "is",
          "selectedOption": {
            "FieldName": "OpenCloseStatus",
            "DisplayText": "Status",
            "Columns": [
              "Case.OpenClosedStatus"
            ],
            "Selectable": true,
            "InputType": "Dropdown",
            "RuleType": 0,
            "TargetCollection": "cases"
          }
        },
        {
          "RuleType": "Simple",
          "Values": [
            "'"$(date -Idate -d "@$(($(date +%s) - 86400 * 366))")"'"
          ],
          "Columns": [
            "Event.OriginalPublishedDate"
          ],
          "Operator": "is after",
          "selectedOption": {
            "FieldName": "OriginalPublishedDate",
            "DisplayText": "Original published date",
            "Columns": [
              "Event.OriginalPublishedDate"
            ],
            "Selectable": true,
            "InputType": "Date",
            "RuleType": 0,
            "Options": null,
            "TargetCollection": "cases",
          }
        }
      ],
      "AndOr": "and"
    }
  ],
  "AndOr": "and",
  "TargetCollection": "cases",
  "ExportFormat": "data",
  "SessionId": '"$session_id"',
  "ResultSetSize": 500,
  "SortDescending": false
}' > "$tmpfile"
unzip -p "$tmpfile" -x readme.txt