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

description: >
  Service commissioning file for the integration between Connectware and
  Microsoft SQL Server (Example)

metadata:
  name: Microsoft SQL Server Integration
  provider: cybus
  homepage: https://www.cybus.io
  version: 1.0.0

parameters:
  mssqlHost:
    description: Hostname or IP address of the Microsoft SQL Server instance
    type: string
    default: example.com

  mssqlPort:
    description: Port of the Microsoft SQL Server instance
    type: integer
    default: 1433

  mssqlUsername:
    description: SQL login that Connectware uses to connect
    type: string
    default: connectware

  mssqlPassword:
    description: Password of the SQL login
    type: string

  mssqlDatabase:
    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 Microsoft SQL Server database
  mssqlConnection:
    type: Cybus::Connection
    properties:
      protocol: Mssql
      connection:
        host: !ref mssqlHost
        port: !ref mssqlPort
        username: !ref mssqlUsername
        password: !ref mssqlPassword
        database: !ref mssqlDatabase

  # Inserts one row into the production_events table per message
  productionEventWrite:
    type: Cybus::Endpoint
    properties:
      protocol: Mssql
      connection: !ref mssqlConnection
      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: Mssql
      connection: !ref mssqlConnection
      subscribe:
        query: >-
          SELECT TOP 50 id, event_time, site, area, line, cell,
          event_type, event_value
          FROM production_events
          WHERE event_time > DATEADD(minute, -5, SYSUTCDATETIME())
          ORDER BY event_time DESC
        # 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'
