databaseChangeLog:
  - logicalFilePath: db-changelog-001018-remove-mining-investigation.yaml
  - changeSet:
      author: appian
      id: 'tag-pre-001018'
      tagDatabase:
        tag: 'pre-001018'
  # ----- Remove Invalid Insights and KPIs
  - changeSet:
      author: appian
      id: '001018.1.0'
      comment: (MariaDB Only) Remove orphaned mining_insights (those without a mining_investigation, mining_kpi, or mining_scope)
      dbms: 'mariadb'
      delete:
        tableName: mining_insight
        where: mining_investigation_id IS NULL OR mining_kpi_id IS NULL OR mining_scope_id IS NULL
  - changeSet:
      author: appian
      id: '001018.1.1'
      comment: (MariaDB Only) Remove soft-deleted mining_kpis and dependent mining_filter_groups and mining_insights
      dbms: 'mariadb'
      changes:
        - sql: DELETE ins FROM mining_insight ins WHERE EXISTS (SELECT kpi.id FROM mining_kpi kpi WHERE kpi.is_deleted = TRUE AND kpi.id = ins.mining_kpi_id)
        - sql: DELETE mfg FROM mining_filter_group mfg WHERE EXISTS (SELECT kpi.id FROM mining_kpi kpi WHERE kpi.is_deleted = TRUE AND kpi.id = mfg.mining_kpi_id)
        - delete:
            tableName: mining_kpi
            where: is_deleted = TRUE
  - changeSet:
      author: appian
      id: '001018.1.2'
      comment: Remove is_deleted column from mining_kpi
      dropColumn:
        tableName: mining_kpi
        columnName: is_deleted
  # ----- Create a mining_analysis_timeframe for each Insight's Investigation Timeframe
  - changeSet:
      author: appian
      id: '001018.2.0'
      comment: (MariaDB Only) Copy over mining_investigation timeframe information into mining_analysis_timeframe per mining_insight
      dbms: 'mariadb'
      sql: INSERT INTO mining_analysis_timeframe (mining_insight_id, time_period, start_ts, end_ts)
        SELECT ins.id, inv.analysis_time_period, inv.analysis_start_ts, inv.analysis_end_ts
        FROM mining_insight ins
        JOIN mining_investigation inv ON ins.mining_investigation_id = inv.id;
  # ----- Ensure that mining_kpi's mining_scope_id pointer is populated, delete any still orphaned
  - changeSet:
      author: appian
      id: '001018.3.0'
      dbms: 'mariadb'
      comment: (MariaDB Only) Populate mining_scope_id for every mining_kpi
      sql: UPDATE mining_kpi kpi JOIN mining_scope_kpis skpi ON kpi.id = skpi.mining_kpi_id SET kpi.mining_scope_id = skpi.mining_scope_id WHERE kpi.mining_scope_id IS NULL;
  - changeSet:
      author: appian
      id: '001018.3.1'
      dbms: 'mariadb'
      comment: (MariaDB Only) Remove any remaining orphaned mining_kpis
      changes:
        - sql: DELETE ins FROM mining_insight ins WHERE EXISTS (SELECT kpi.id FROM mining_kpi kpi WHERE kpi.mining_scope_id IS NULL AND kpi.id = ins.mining_kpi_id)
        - sql: DELETE mfg FROM mining_filter_group mfg WHERE EXISTS (SELECT kpi.id FROM mining_kpi kpi WHERE kpi.mining_scope_id IS NULL AND kpi.id = mfg.mining_kpi_id)
        - delete:
            tableName: mining_kpi
            where: mining_scope_id IS NULL
  # ----- Remove mining_scope_kpis
  - changeSet:
      author: appian
      id: '001018.4.0'
      comment: Disconnect mining_insight from mining_scope_kpis
      changes:
        - dropForeignKeyConstraint:
            baseTableName: mining_insight
            constraintName: mining_insight_scope_kpi_fk
        - dropIndex:
            tableName: mining_insight
            indexName: mining_insight_kpi_id_idx
        - dropIndex:
            tableName: mining_insight
            indexName: mining_insight_scope_id_idx
        - dropColumn:
            tableName: mining_insight
            columnName: mining_scope_id
  - changeSet:
      author: appian
      id: '001018.4.1'
      comment: Drop mining_scope_kpis table
      dropTable:
        tableName: mining_scope_kpis
        cascadeConstraints: true
  # ----- Remove mining_investigation
  - changeSet:
      author: appian
      id: '001018.5.0'
      comment: Disconnect mining_insight from mining_investigation
      changes:
        - dropForeignKeyConstraint:
            baseTableName: mining_insight
            constraintName: insight_investigation_fk
        - dropIndex:
            tableName: mining_insight
            indexName: insight_invest_id_idx
        - dropColumn:
            tableName: mining_insight
            columnName: mining_investigation_id
  - changeSet:
      author: appian
      id: '001018.5.1'
      comment: Drop mining_investigation table
      dropTable:
        tableName: mining_investigation
        cascadeConstraints: true
  # ----- Finalize Single-Parent Hierarchy (Process -> Scope -> KPI -> Insight)
  - changeSet:
      author: appian
      id: '001018.6.0'
      comment: Disconnect mining_kpi from mining_process and remove the column
      changes:
        - dropForeignKeyConstraint:
            baseTableName: mining_kpi
            constraintName: kpi_process_id_fk
        - dropIndex:
            tableName: mining_kpi
            indexName: mining_kpi_process_id_idx
        - dropColumn:
            tableName: mining_kpi
            columnName: mining_process_id
  - changeSet:
      author: appian
      id: '001018.6.1'
      comment: Reconnect mining_insight to mining_kpi
      changes:
        - addNotNullConstraint:
            tableName: mining_insight
            columnName: mining_kpi_id
            columnDataType: ${longType}
        - createIndex:
            tableName: mining_insight
            columns:
              - column:
                  name: mining_kpi_id
            indexName: mining_insight_kpi_id_idx
        - addForeignKeyConstraint:
            constraintName: mining_insight_mining_kpi_fk
            baseTableName: mining_insight
            baseColumnNames: mining_kpi_id
            referencedTableName: mining_kpi
            referencedColumnNames: id
            onDelete: CASCADE
  - changeSet:
      author: appian
      id: '001018.6.2'
      comment: Recreate FK from mining_kpi to mining_scope
      changes:
        - dropForeignKeyConstraint:
            baseTableName: mining_kpi
            constraintName: mining_kpi_scope_id_fk
        - dropIndex:
            tableName: mining_kpi
            indexName: mining_kpi_scope_id_idx
        - addNotNullConstraint:
            tableName: mining_kpi
            columnName: mining_scope_id
            columnDataType: ${longType}
        - createIndex:
            tableName: mining_kpi
            columns:
              - column:
                  name: mining_scope_id
            indexName: mining_kpi_scope_id_idx
        - addForeignKeyConstraint:
            constraintName: mining_kpi_scope_id_fk
            baseTableName: mining_kpi
            baseColumnNames: mining_scope_id
            referencedTableName: mining_scope
            referencedColumnNames: id
            onDelete: CASCADE
  # ----- Invalidate all existing Duration Insights
  - changeSet:
      author: appian
      id: '001018.7.0'
      comment: (MariaDB Only) Mark all existing Duration type Insights as Invalid
      dbms: 'mariadb'
      sql: UPDATE mining_insight ins
        INNER JOIN mining_kpi kpi ON ins.mining_kpi_id = kpi.id
        INNER JOIN mining_kpi_type kpi_type ON kpi_type.mining_kpi_id = kpi.id
        INNER JOIN mining_kpi_type_duration dur ON dur.kpi_type_id = kpi_type.id
        SET ins.warn_option = 3, ins.warn_ts = UNIX_TIMESTAMP(CURRENT_TIMESTAMP) * 1000;
