---
# ----------------------------------------------------------------------------
# Service Commissioning File
# ----------------------------------------------------------------------------
# Copyright: Cybus GmbH
# Contact: support@cybus.io
# ----------------------------------------------------------------------------
# PostgreSQL Integration (Example)
# ----------------------------------------------------------------------------

description: >
  Service commissioning file for the integration between Connectware and
  PostgreSQL (Example)

metadata:
  name: PostgreSQL Integration
  provider: cybus
  homepage: https://www.cybus.io
  version: 1.0.0

parameters:
  postgresHost:
    description: Hostname or IP address of the PostgreSQL server
    type: string
    default: example.com

  postgresPort:
    description: Port of the PostgreSQL server
    type: integer
    default: 5432

  postgresUsername:
    description: Database role that Connectware uses to connect
    type: string
    default: connectware

  postgresPassword:
    description: Password of the database role
    type: string

  postgresDatabase:
    description: Database that contains the production_events table
    type: string
    default: factory

  pollingInterval:
    description: Interval in milliseconds between polling queries
    type: integer
    default: 10000

  topicRoot:
    description: Root of the MQTT topic hierarchy
    type: string
    default: enterprise

resources:
  # Connection to the PostgreSQL database
  postgresConnection:
    type: Cybus::Connection
    properties:
      protocol: Sql
      connection:
        url: !sub 'postgres://${postgresUsername}:${postgresPassword}@${postgresHost}:${postgresPort}/${postgresDatabase}'

  # Inserts one row into the production_events table per message
  productionEventWrite:
    type: Cybus::Endpoint
    properties:
      protocol: Sql
      connection: !ref postgresConnection
      write:
        query: >-
          INSERT INTO production_events
          (site, area, line, cell, event_type, event_value)
          VALUES ($site, $area, $line, $cell, $event_type, $event_value)

  # Feeds the write endpoint from the ISA-95 topic hierarchy. The named
  # wildcards provide the topic levels in $context.vars, so the transform
  # rule can build one flat object whose keys match the query placeholders.
  productionEventMapping:
    type: Cybus::Mapping
    properties:
      mappings:
        - subscribe:
            topic: !sub '${topicRoot}/+site/+area/+line/+cell/production-events'
          publish:
            endpoint: !ref productionEventWrite
          rules:
            - transform:
                expression: >-
                  {
                    "site": $context.vars.site,
                    "area": $context.vars.area,
                    "line": $context.vars.line,
                    "cell": $context.vars.cell,
                    "event_type": event_type,
                    "event_value": value
                  }

  # Polls the production events of the last five minutes
  recentEventsQuery:
    type: Cybus::Endpoint
    properties:
      protocol: Sql
      connection: !ref postgresConnection
      subscribe:
        query: >-
          SELECT id, event_time, site, area, line, cell,
          event_type, event_value
          FROM production_events
          WHERE event_time > now() - interval '5 minutes'
          ORDER BY event_time DESC
          LIMIT 50
        # Be careful with low values, they can overload the database
        # (the unit is milliseconds).
        interval: !ref pollingInterval

  recentEventsMapping:
    type: Cybus::Mapping
    properties:
      mappings:
        - subscribe:
            endpoint: !ref recentEventsQuery
          publish:
            topic: !sub '${topicRoot}/production-events/recent'
