databaseChangeLog:
  - logicalFilePath: db-changelog-000438-update-investigation-table.yaml
  - changeSet:
      author: appian
      id: tag-pre-000438
      tagDatabase:
        tag: 'pre-000438'

  - changeSet:
      author: appian
      id: '000438.1.0'
      comment: Remove investigation_process_id_idx index
      changes:
        # Indexes in some databases (ex. db2) need to be dropped before the column that they reference can be renamed
        - dropIndex:
            tableName: investigation
            indexName: investigation_process_id_idx

  - changeSet:
      author: appian
      id: '000438.1.1'
      comment: Rename process_id column to log_id
      changes:
        - renameColumn:
            tableName: investigation
            columnDataType: ${uuidType}
            oldColumnName: process_id
            newColumnName: log_id

  - changeSet:
      author: appian
      id: '000438.1.2'
      comment: Add index on investigation.log_id
      createIndex:
        tableName: investigation
        columns:
          - column:
              name: log_id
        indexName: investigation_log_id_idx

  - changeSet:
      author: appian
      id: '000438.1.3'
      comment: modify goal column data type to byteType
      changes:
        - modifyDataType:
            tableName: investigation
            columnName: goal
            newDataType: ${byteType}

  - changeSet:
      author: appian
      id: '000438.1.4'
      comment: Make the num_cases column non-nullable
      addNotNullConstraint:
        tableName: investigation
        columnName: num_cases
        columnDataType: ${longType}
        defaultNullValue: 0

  - changeSet:
      author: appian
      id: '000438.1.5'
      comment: Drop the measure column since it is no longer needed
      dropColumn:
        tableName: investigation
        columnName: measure

  - changeSet:
      author: appian
      id: '000438.1.6'
      comment: modify analysis_time_period column data type to byteType, defaulting to LAST_7_DAYS (byte code 1)
      changes:
        - modifyDataType:
            tableName: investigation
            columnName: analysis_time_period
            newDataType: ${byteType}
            defaultValueNumeric: 1

  - changeSet:
      author: appian
      id: '000438.1.7'
      comment: modify compare_to_function column data type to byteType
      changes:
        - modifyDataType:
            tableName: investigation
            columnName: compare_to_function
            newDataType: ${byteType}

  - changeSet:
      author: appian
      id: '000438.1.8'
      comment: modify compare_to_function to be non-nullable, defaulting to AVERAGE (byte code 1)
      addNotNullConstraint:
        tableName: investigation
        columnName: compare_to_function
        columnDataType: ${byteType}
        defaultNullValue: 1

  - changeSet:
      author: appian
      id: '000438.1.9'
      comment: modify duration_unit column data type from shortStringType to byteType
      changes:
        - modifyDataType:
            tableName: investigation
            columnName: duration_unit
            newDataType: ${byteType}

  - changeSet:
      author: appian
      id: '000438.1.10'
      comment: make creator_uuid non-nullable
      addNotNullConstraint:
        tableName: investigation
        columnName: creator_uuid
        columnDataType: ${uuidType}

  - changeSet:
      author: appian
      id: '000438.1.11'
      comment: Add modify_ts column to investigation table
      addColumn:
        tableName: investigation
        column:
          name: modify_ts
          type: ${longType}
          defaultValueNumeric: -1
          constraints:
            nullable: false
