Enhance Support Stats reporting - using an S3 bucket to store and append .csv file

[andyrogers1973]

Replaces https://github.com/ministryofjustice/operations-engineering/issues/4209 which has been broken down into smaller tickets.

Related to https://github.com/ministryofjustice/operations-engineering/issues/4350 and https://github.com/ministryofjustice/operations-engineering/issues/4353 which change and test the data format Support Request stats are reported in.

This ticket is to look at the possibility of storing the Support Requests stats reporting data in an S3 bucket.

To achieve this:

• create an S3 bucket to store the .csv file in
• set up a GitHub workflow action to read/write from this .csv file
• create a method for appending data to the .csv file to record the previous days Support Requests
• update the stats reporting script to use the new file instead of the one currently stored in the repo
• test that this new process works and we can still report data to the KPI dashboard

Considered done when:

• S3 bucket exists and is accessible to GitHub workflow action
• .csv file can be appended on a daily basis
• data can be pulled from the new .csv file to provide a breakdown of yesterdays Support Requests
• Breakdown of stats is reported into the Slack channel

[andyrogers1973]

Investigating how to create an s3 bucket via code.

Started writing script to pull csv file from s3, upload amended csv to s3 from repo.

Figuring out the workflow - best way to update the csv file with new data, is this via the Google sheet (current method) or find a new way (maybe with web front end and db, edit csv in the repo?)

[andyrogers1973]

Example of how data can be added as a new file and Athena is used to create a table from all the files: https://github.com/moj-analytical-services/lookup_offence/blob/main/.github/scripts/deploy.py

[andyrogers1973]

Understanding the method used in the example above and how to implement this for support stats

[andyrogers1973]

Draft PR for first version of the s3 upload script #72

[andyrogers1973]

Have now updated the script with changes:

• check file path to the csv file
• check if db and table exist
• push csv file to s3 bucket amended
• update table from new csv file upload

[andyrogers1973]

Added a timestamp variable. Use this variable to create in the write_path for the latest csv update

[andyrogers1973]

Create s3 bucket for support stats:

operations-engineering-support-request-stats

[andyrogers1973]

Added support_request_stats.json to create the table schema