Certificate Management - enhance db attributes and API details

[jjeroch]

TASK 1

Description:
As part of the backend change for the certificate function, the following new attributes need to be added to the companies_certificate table inside the portal database.

• external_certificate_number ?: This attribute will store the external certificate number associated with the certificate.
• sites``?: This attribute will store the list of sites where the certificate is applicable. (multi value field) // due to the need to have multiple values added, we need to create a new table (mapping table to store those data it cant be directly located inside companies_certificate - details see below (sites are defined by BPN* - business partner number)
• validFrom``?: This attribute will store the date from which the certificate is valid.
• validTill``?: This attribute will store the date until which the certificate is valid.
• issuer``?: This attribute will store the name of the certificate issuer; this is usually a company name such as "Deloitte", "Accenture", "ADAC", etc.
• validator``?: This attribute will store the name of the validator who verified the certificate. The validator is given by the company ID. For the current moment this field will stay empty. No user input or backend logic to be implemented
  - trustlevel``?: This attribute will store the trust level of the certificate.

Note - all fields which have a ?added are nullable

• Modify the companies_certificate table in the portal database to include the new attributes.
• Add the new table companies_certificate_assigned_sites table; FK companies_certificate.id and a new attribute site
• each companies_certificate.id can be multiple times included inside companies_certificate_assigned_sites but always with another site attribute

Acceptance Criteria:

1. The companies_certificate table should have the new attributes: external_certificate_number, validFrom, validUntil, issuer, trustlevel, and validator.
2. New table companies_certificate_assigned_sites created and linked to the existing table companies_certificate

---

TASK 2

Description: enhance GET and POST endpoints with those new attributes

• Update the POST endpoint of /certificates to accept the new attributes in the request payload and store them in the database.
  • external_certificate_number ?
  • sites``?
  • validFrom``?
  • validTill``?
  • issuer``?
    - validator``?
    - trustlevel``?
• Update the GET endpoint of /certificates to include the new attributes in the response payload.
  • external_certificate_number ?
  • sites``?
  • validFrom``?
  • validTill``?
  • issuer``?
  • validator``?
    - trustlevel``?

Acceptance Criteria:

1. The POST endpoint of /certificates should accept the new attributes and store them in the database.
2. The GET endpoint of /certificates should include the new attributes in the response payload.

Endpoint: POST /api/administration/companydata/companyCertificate

enhance the request body by adding the optional new fields:

• external_certificate_number - any number between 0 and 36 digits; optional
• sites - BPNS numbers; optional. If submitted validate if BPNS belongs to the BPNL of the submittor by using the bpdm supported endpoint of hirarchy (same as used adding bpn numbers to a user)
• validFrom - date can not be in future
• validUntil - date can not be in the past
• issuer - issuer authority at the moment to be implemented as free text field of a company name such as "Deloitte", "Accenture", "ADAC", etc. - @jjeroch
  - trustlevel- always empty
  - validator- always empty

Endpoint: GET /api/administration/companydata/companyCertificate

enhance the response body by adding the new fields:

• external_certificate_number
• sites
• validFrom
• validUntil
• issuer
  - trustlevel
• validator

[jjeroch]

Refinement still ongoing, but implementation started in parallel (local - no PR planned for now)

[jjeroch]

@AnuragNagpure can you please adjust the validation for the sites to only allow BPNS numbers.

Test Examples

Success

"BPNS0003726HZL98"

Failing

"BPNL0003726HZL98"
"BPNA0003726HZL98"
"BPNS0003726HZL9 "
"BPNS0003726HZL9"
"BPNS0003726HZL999"
"BPNS0003 726HZL9"
"BPNS0003-726HZL9"
"8BPNL0003726HZL9"

[ntruchsess]

1. there is no pre-existing table 'companies_certificate'. In compliance with the naming-conventions the existing table is 'company_certificates' (as it is referring to a single entry within table 'companies' which is 'company')
   The new tables should be named accordingly:
   so instead of 'companies_certificate_assigned_sites' it's 'company_certificate_assigned_sites', the corresponding Entity than has the name 'CompanyCertificateAssignedSite'.
   (in the existing PR feat(certificate): enhance company certificate db attributes and API details #823 the names are already adjusted)