---
version: 42
data:

- type: "TaskStageHook"
  variation: null
  lastChangedDate: "2025-10-07T14:38:52"
  lastChangedBy: "admin"
  replacePermissions: false
  replaceObject: true
  attributes:
    code: |-
      import de.ubs.xdm.utils.databasemodel.ObjectSerializer
      import org.apache.commons.lang3.math.NumberUtils

      // -------------------------
      // Utility Functions
      // -------------------------

      recommendationList = [] // To collect for summary and CSV

      def escapeCsvField(val) {
          // Very simple: If ; in field, wrap in quotes (adapt if needed for edge cases)
          (val && val.contains(';')) ? "\"${val}\"" : (val ?: "")
      }

      def printReferenceExamples(connection, sql, infoMessage, referencedTableDesc, rowLabel) {
          println("Executing query on ${infoMessage}: ${sql}")
          def stmt = connection.createStatement()
          def rs = stmt.executeQuery(sql)
          def found = false
          while (rs.next()) {
              def meta = rs.metaData
              def columns = meta.columnCount
              def rowMap = (1..columns).collectEntries { i ->
                  [(meta.getColumnName(i)): rs.getObject(i)]
              }
              println "${rowLabel} in ${referencedTableDesc}:\n\t${rowMap.collect { k, v -> "${k}: ${v}" }.join(', ')}"
              found = true
          }
          rs.close()
          stmt.close()
          return found
      }

      def getExampleRows(connection, schema, tableName, columns) {
          def colNames = columns.collect { "\"${it.getName()}\"" }
          def sql = "SELECT ${colNames.join(', ')} FROM \"${schema}\".\"${tableName}\" LIMIT 5"
          println("Querying example data: ${sql}")
          def stmt = connection.createStatement()
          def rs = stmt.executeQuery(sql)
          def examples = []
          while (rs.next()) {
              def row = []
              columns.eachWithIndex { c, i ->
                  row << rs.getObject(c.getName())
              }
              examples << row
          }
          rs.close()
          stmt.close()
          return examples
      }

      def printRecommendation(baseSchema, baseTable, baseCols, refSchema, refTable, refCols) {
          def baseColList = baseCols.collect{ it.getName() }.join(", ")
          def refColList = refCols.collect{ it.getName() }.join(", ")
          def relationDisplay = "${baseTable}(${baseColList}) -> ${refTable}(${refColList})"
          def summary = "Base: ${baseSchema}.${baseTable} (${baseColList}) => Reference: ${refSchema}.${refTable} (${refColList})"
          println """Recommended Data Relation Rule:
      ---
      ${summary}
      ---
      """
          // Add one recommendation record for summary/CSV at the end
          recommendationList << [
              baseSchema: baseSchema,
              baseTable: baseTable,
              baseCols: baseCols.collect{ it.getName() },
              refSchema: refSchema,
              refTable: refTable,
              refCols: refCols.collect{ it.getName() },
              displayName: relationDisplay,
              description: summary
          ]
      }

      def checkFKComplete = { baseSchema, baseTable, baseCols, refSchema, refTable, refCols ->
          if (!refSchema || !refTable || baseCols.size() != refCols.size() || baseCols.empty) {
              println "Cannot perform FK completeness check - incomplete table or column information."
              return []
          }
          def baseColNames = baseCols.collect { "\"${it.getName()}\"" }
          def refColNames = refCols.collect { "\"${it.getName()}\"" }
          def tupleBase = baseColNames.join(", ")
          def tupleRef = refColNames.join(", ")
          def sql = """
              SELECT DISTINCT ${tupleBase}
              FROM "${baseSchema}"."${baseTable}"
              WHERE (${tupleBase}) NOT IN (
                  SELECT ${tupleRef}
                  FROM "${refSchema}"."${refTable}"
              )
              LIMIT 20
          """.stripIndent().trim()
          println "Checking for missing referenced rows with: ${sql}"
          def stmt = dataConnection.createStatement()
          def rs = stmt.executeQuery(sql)
          def missingRows = []
          while (rs.next()) {
              def row = [:]
              baseCols.eachWithIndex { c, idx ->
                  row[c.getName()] = rs.getObject(idx + 1)
              }
              missingRows << row
          }
          rs.close()
          stmt.close()
          return missingRows
      }

      // -------------------------
      // Main Logic
      // -------------------------

      if (dataConnection) {
          def file = properties.get("root_dir") + "/raw/sourceObjectDefinitions.oc"
          def objectContainer = ObjectSerializer.getInstance().deserialize(file)
          println("Total number of tables: ${objectContainer.countTables()}")

          objectContainer.getTableList().each { table ->
              def outgoingFks = table.getExternalForeignKey()
              def ingoingFks = table.getExternalReferencingForeignKey()
              println("Table \"${table}\" has ${outgoingFks.size()} outgoing and ${ingoingFks.size()} incoming external foreign keys.")

              // ---- External FK ----
              def processExternalForeignKey = { fk, direction ->
                  def examples = getExampleRows(dataConnection, table.getSchema(), table.getName(), fk.getReferencedColumnList())
                  if (examples && !examples.isEmpty()) {
                      def externalConnection = task.sourceEnvironment.installedApplications[table.getContainer().getContainerIndex()].connection.jdbcConnection
                      def (targetSchema, targetTable, targetColumns, rowLabel, dataRelationSource, dataRelationTarget) = (
                          direction == "outgoing"
                              ? [
                                  fk.getExternalReferencedTable().getSchema(),
                                  fk.getExternalReferencedTable().getName(),
                                  fk.getReferencedColumnList(),
                                  "Referenced row",
                                  "${table.getSchema()}.${table.getName()} (${fk.getReferencedColumnList().collect{ it.getName() }.join(', ')})",
                                  "${fk.getExternalReferencedTable().getSchema()}.${fk.getExternalReferencedTable().getName()} (${fk.getColumnList().collect{ it.getName() }.join(', ')})"
                              ]
                              : [
                                  fk.getBaseExternalTable().getSchema(),
                                  fk.getBaseExternalTable().getName(),
                                  fk.getColumnList(),
                                  "Referencing row",
                                  "${table.getSchema()}.${table.getName()} (${fk.getColumnList().collect{ it.getName() }.join(', ')})",
                                  "${fk.getBaseExternalTable().getSchema()}.${fk.getBaseExternalTable().getName()} (${fk.getReferencedColumnList().collect{ it.getName() }.join(', ')})"
                              ]
                      )
                      def whereClauses = targetColumns.withIndex().collect { col, idx ->
                          def val = examples[0][idx]      
                          if (val == null) {
                              "\"${col.getName()}\" is null"
                          } else {
                              def quotedVal = (NumberUtils.isNumber(val?.toString()) ? val : "'${val}'")
                              "\"${col.getName()}\" = ${quotedVal}"
                          } 
                      }
                      def refSQL = "SELECT * FROM \"${targetSchema}\".\"${targetTable}\" WHERE ${whereClauses.join(' AND ')} LIMIT 5"
                      def found = printReferenceExamples(
                          externalConnection,
                          refSQL,
                          "external connection",
                          "\"${targetSchema}\".\"${targetTable}\"",
                          rowLabel
                      )
                      if (!found) {
                          println "No matching entry found on external connection for the selected example keys."
                      }
                      printRecommendation(table.getSchema(), table.getName(), targetColumns, targetSchema, targetTable, fk.getColumnList())
                  } else {
                      println("No example data found for foreign key ${fk}.")
                  }
              }

              // ---- Internal FK ----
              def processInternalForeignKey = { fk, direction ->
                  def (baseSchema, baseTable, baseCols, refSchema, refTable, refCols, rowLabel) = (
                      direction == "outgoing"
                          ? [
                              table.getSchema(),
                              table.getName(),
                              fk.getColumnList(),
                              fk.getReferencedTable().getSchema(),
                              fk.getReferencedTable().getName(),
                              fk.getReferencedColumnList(),
                              "Referenced row"
                            ]
                          : [
                              table.getSchema(),
                              table.getName(),
                              fk.getReferencedColumnList(),
                              fk.getTable().getSchema(),
                              fk.getTable().getName(),
                              fk.getColumnList(),
                              "Referencing row"
                            ]
                  )
                  def missingRows = checkFKComplete(baseSchema, baseTable, baseCols, refSchema, refTable, refCols)
                  if (!missingRows.isEmpty()) {
                      def whereClauses = refCols.withIndex().collect { col, idx ->
                          def val = missingRows[0][col.getName()]
                          if (val == null) {
                              "\"${col.getName()}\" is null"
                          } else {
                              def quotedVal = (NumberUtils.isNumber(val?.toString()) ? val : "'${val}'")
                              "\"${col.getName()}\" = ${quotedVal}"
                          }
                      }
                      def refSQL = "SELECT * FROM \"${refSchema}\".\"${refTable}\" WHERE ${whereClauses.join(' AND ')} LIMIT 5"
                      def externalConnection = task.sourceEnvironment.installedApplications[table.getContainer().getContainerIndex()].connection.jdbcConnection
                      def foundExternally = printReferenceExamples(
                          externalConnection,
                          refSQL,
                          "external connection",
                          "\"${refSchema}\".\"${refTable}\"",
                          rowLabel
                      )
                      if (!foundExternally) {
                          println "No matching entry found on external connection for at least one missing referenced key."
                      } else {
                        println("Missing referenced rows detected! It is recommended to establish a data relation rule to fill these missing rows in the table ${refSchema}.${refTable}.")
                        missingRows.eachWithIndex { row, idx ->
                            println "Missing referenced row #${idx + 1}: " + row.collect { k, v -> "${k}: ${v}" }.join(', ')
                        }
                        printRecommendation(baseSchema, baseTable, baseCols, refSchema, refTable, refCols)
                      }
                  } else {
                      println "No missing referenced rows detected between ${baseSchema}.${baseTable} and ${refSchema}.${refTable}."
                  }
              }

              // -- External FKs
              outgoingFks.each { fk ->
                  println("Processing outgoing external foreign key: ${fk}")
                  processExternalForeignKey(fk, "outgoing")
              }
              ingoingFks.each { fk ->
                  println("Processing incoming external foreign key: ${fk}")
                  processExternalForeignKey(fk, "ingoing")
              }
              // -- Internal FKs
              def outgoingInternalFks = table.getForeignKeyConstraints()
              def ingoingInternalFks = table.getForeignKeyReferences()
              println("Table \"${table}\" has ${outgoingInternalFks.size()} outgoing and ${ingoingInternalFks.size()} incoming internal foreign keys between task tables.")
              outgoingInternalFks.each { fk ->
                  println("Processing outgoing internal foreign key: ${fk}")
                  processInternalForeignKey(fk, "outgoing")
              }
              ingoingInternalFks.each { fk ->
                  println("Processing incoming internal foreign key: ${fk}")
                  processInternalForeignKey(fk, "ingoing")
              }
          }

          // ------------------------------
          // Final Summary & CSV Output
          // ------------------------------

          // -------- Human-readable Summary
          println "\nSummary of Recommended Data Relation Rules:"
          if (recommendationList.size() == 0) {
              println "(No recommendations found.)"
          } else {
              recommendationList.eachWithIndex { rec, idx ->
                  println "${idx + 1}. [${rec.displayName}] -- ${rec.description}"
              }
          }

          // -------- CSV Export
          println "\nCopy & Paste the following block for CSV import:"
          println "active;columns;conditionScript;description;displayName;libraries;lookupScript;namePattern;referencedApplicationModel;referencedColumns;referencedModellingVersion;referencedTableName;referencedTableSchema;schemaPattern;scriptLanguage;sqlLookupExpression;tags;traversal"
          recommendationList.each { rec ->
              // CSV columns as per your sample
              def csvRow = [
                  "true",                                 // active
                  rec.baseCols.join(","),                 // columns
                  "",                                     // conditionScript
                  rec.description,                        // description
                  rec.displayName,                        // displayName
                  "",                                     // libraries
                  "",                                     // lookupScript
                  rec.baseTable,                          // namePattern
                  "",                                     // referencedApplicationModel
                  rec.refCols.join(","),                  // referencedColumns
                  "",                                     // referencedModellingVersion
                  rec.refTable,                           // referencedTableName
                  rec.refSchema,                          // referencedTableSchema
                  "%",                                    // schemaPattern
                  "JAVA_SCRIPT",                          // scriptLanguage
                  "",                                     // sqlLookupExpression
                  "",                                     // tags
                  "-1"                                    // traversal
              ].collect { escapeCsvField(it) }
              println csvRow.join(";")
          }
      }
    codeType: "GROOVY"
    description: |-
      This script analyzes table relationships in a database extraction or migration scenario. It scans all tables defined in the imported data model, checks their foreign key relationships (both between internal/extracted tables and external/system tables), and tries to detect missing or incomplete references. For each potential or detected missing relation, the script generates a recommended "Data Relation Rule".

      How to Read the Output

      Detail Section (per table, at the top):
      As the script runs, it prints detailed information about each table's outgoing and incoming foreign key relations.
      For each foreign key, the script shows example queries and if missing references are found, it lists those specific missing rows.
      For every data relation issue found, it prints a clear "Recommended Data Relation Rule" block, which shows which table, schema, and columns should be linked.
      Summary Section (at the end):
      After all tables are processed, a compact summary of all recommended data relation rules is presented.
      Each recommendation from above is listed with a simple display name and a human-readable description, making it easy to see all issues at a glance.
      Directly below, you’ll find a "Copy & Paste" CSV output block. This block can be imported into internal tools; each line corresponds to one rule found, including all necessary metadata as columns.
      Relation Between Details and Summary

      Whenever a "Recommended Data Relation Rule" is printed in the detail section, that same rule will be included in the summary and in the CSV output at the end.
      The summary gives you a quick overview of all relations needing attention, while the detailed section tells you exactly where and why each rule was recommended, with live data examples and queries shown for full transparency.
      This approach helps you both understand where relational problems exist and quickly import the solution (data relation rules) into your system for remediation.
    displayName: "Explore table references"
    libraries: []
    mappingTableAccessMode: "READ"
    mappingTableContainers: []
    parameters: []
    tags: []