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

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

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

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

  mariadbPort:
    description: Port of the MariaDB server
    type: integer
    default: 3306

  mariadbUsername:
    description: Database user that Connectware uses to connect
    type: string
    default: connectware

  mariadbPassword:
    description: Password of the database user
    type: string

  mariadbDatabase:
    description: Database that contains the machine_states table
    type: string
    default: factory

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

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

resources:
  # Connection to the MariaDB database
  mariadbConnection:
    type: Cybus::Connection
    properties:
      protocol: Sql
      connection:
        url: !sub 'mariadb://${mariadbUsername}:${mariadbPassword}@${mariadbHost}:${mariadbPort}/${mariadbDatabase}'

  # Inserts one row into the machine_states table per state change
  machineStateWrite:
    type: Cybus::Endpoint
    properties:
      protocol: Sql
      connection: !ref mariadbConnection
      write:
        query: >-
          INSERT INTO machine_states
          (site, area, line, cell, machine_state, duration_seconds)
          VALUES ($site, $area, $line, $cell, $machine_state, $duration_seconds)

  # 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.
  machineStateMapping:
    type: Cybus::Mapping
    properties:
      mappings:
        - subscribe:
            topic: !sub '${topicRoot}/+site/+area/+line/+cell/machine-state'
          publish:
            endpoint: !ref machineStateWrite
          rules:
            - transform:
                expression: >-
                  {
                    "site": $context.vars.site,
                    "area": $context.vars.area,
                    "line": $context.vars.line,
                    "cell": $context.vars.cell,
                    "machine_state": machine_state,
                    "duration_seconds": duration_seconds
                  }

  # Aggregates the machine states of the last hour per line, cell, and state
  stateSummaryQuery:
    type: Cybus::Endpoint
    properties:
      protocol: Sql
      connection: !ref mariadbConnection
      subscribe:
        query: >-
          SELECT line, cell, machine_state,
          COUNT(*) AS state_changes,
          SUM(duration_seconds) AS total_seconds
          FROM machine_states
          WHERE state_time > NOW() - INTERVAL 1 HOUR
          GROUP BY line, cell, machine_state
          ORDER BY line, cell, machine_state
        # Be careful with low values, they can overload the database
        # (the unit is milliseconds).
        interval: !ref pollingInterval

  stateSummaryMapping:
    type: Cybus::Mapping
    properties:
      mappings:
        - subscribe:
            endpoint: !ref stateSummaryQuery
          publish:
            topic: !sub '${topicRoot}/oee/state-summary'
