AWSTemplateFormatVersion: '2010-09-09'
Description: (AWS Heidi)QuickSight stack to create DataSet 
Parameters:
  AWSHealthEventDataCatalog:
    Type: String
    Description: AWSHealthEventDataCatalog
  EventHealthBucket:
    Type: String
    Description: EventHealthBucket
  QuickSightUser:
    Type: String
    Description: The QuickSight User that is allowed configure and manage the QS dashboard.
  DynamoDBName:
    Type: String
    Description: DynamoDB name to store the 
  DDBConnectorARN:
    Type: String
    Description: DDBConnectorARN
  QuicksightServiceRole:
    Type: String
    Description: The Quicksight Service role attached to QS, Default is aws-quicksight-service-role-v0
  
Resources:
  QuicksightFederatedPolicy:
    Type: AWS::IAM::ManagedPolicy
    Properties:
      ManagedPolicyName: !Sub AWSQuickSightHealthEventPolicy-${AWS::Region}
      Description: "Grants Amazon QuickSight to run awshealthevent federated query"
      PolicyDocument:
        Version: "2012-10-17"
        Statement:
          - Effect: "Allow"
            Action:
              - lambda:ListFunctions
              - s3:ListAllMyBuckets
            Resource: "*"
          - Effect: "Allow"
            Action:
              - "lambda:InvokeFunction"
            Resource: !Ref DDBConnectorARN
          - Effect: "Allow"
            Action:
              - "s3:GetBucketLocation"
              - "s3:GetObject"
              - "s3:ListBucket"
              - "s3:ListBucketMultipartUploads"
              - "s3:AbortMultipartUpload"
              - "s3:PutObject"
              - "s3:ListMultipartUploadParts"
              - "s3:CreateBucket"
            Resource: 
              - !Sub arn:aws:s3:::${EventHealthBucket}
              - !Sub arn:aws:s3:::${EventHealthBucket}/*
              - arn:aws:s3:::aws-athena-query-results-*
              - arn:aws:s3:::aws-athena-query-results-*/*
          - Effect: "Allow"
            Action:
              - athena:BatchGetQueryExecution
              - athena:CancelQueryExecution
              - athena:GetCatalogs
              - athena:GetExecutionEngine
              - athena:GetExecutionEngines
              - athena:GetNamespace
              - athena:GetNamespaces
              - athena:GetQueryExecution
              - athena:GetQueryExecutions
              - athena:GetQueryResults
              - athena:GetQueryResultsStream
              - athena:GetTable
              - athena:GetTables
              - athena:ListQueryExecutions
              - athena:RunQuery
              - athena:StartQueryExecution
              - athena:StopQueryExecution
              - athena:ListWorkGroups
              - athena:ListEngineVersions
              - athena:GetWorkGroup
              - athena:GetDataCatalog
              - athena:GetDatabase
              - athena:GetTableMetadata
              - athena:ListDataCatalogs
              - athena:ListDatabases
              - athena:ListTableMetadata
            Resource: "*"
      Roles: 
       - !Ref QuicksightServiceRole

  AWSHealthEventQSDataSource:
    Type: AWS::QuickSight::DataSource
    DependsOn: QuicksightFederatedPolicy
    Properties:
      DataSourceId: !Sub "EventHealth-${AWS::AccountId}-${AWS::Region}"
      AwsAccountId: !Sub ${AWS::AccountId}
      Name: !Sub "EventHealth-${AWS::AccountId}-${AWS::Region}"
      Type: ATHENA
      DataSourceParameters:
        AthenaParameters:
          WorkGroup: primary
      SslProperties:
        DisableSsl: true

  AWSHealthEventQSDataSet:
    Type: AWS::QuickSight::DataSet
    Properties:
      AwsAccountId: !Sub ${AWS::AccountId}
      ImportMode: SPICE
      DataSetId: !Sub "EventHealth-${AWS::AccountId}-${AWS::Region}"
      Name: !Sub "EventHealth-${AWS::AccountId}-${AWS::Region}"
      PhysicalTableMap:
        "AWSHealthQSPT":
          RelationalTable:
            DataSourceArn: !GetAtt AWSHealthEventQSDataSource.Arn
            Catalog: !Ref AWSHealthEventDataCatalog
            Schema: "default"
            Name: !Ref DynamoDBName
            InputColumns:
            - Name: eventTypeCode
              Type: STRING
            - Name: eventSummary
              Type: STRING
            - Name: accountName
              Type: STRING
            - Name: monthlySpend
              Type: STRING
            - Name: eventSource
              Type: STRING
            - Name: eventScopeCode
              Type: STRING
            - Name: eventTypeCategory
              Type: STRING
            - Name: affectedEntities
              Type: STRING
            - Name: eventArn
              Type: STRING
            - Name: ingestionTime
              Type: STRING
            - Name: service
              Type: STRING
            - Name: eventDescription
              Type: STRING
            - Name: lastUpdatedTime
              Type: STRING
            - Name: startTime
              Type: STRING
            - Name: eventRegion
              Type: STRING
            - Name: endTime
              Type: STRING
            - Name: region
              Type: STRING
            - Name: arn
              Type: STRING
            - Name: account
              Type: STRING
            - Name: statusCode
              Type: STRING
      LogicalTableMap:
        "AWSHealthQSLT":
          Alias: !Sub "EventHealth-${AWS::AccountId}-${AWS::Region}"
          DataTransforms:
          - CastColumnTypeOperation:
              ColumnName: ingestionTime
              NewColumnType: DATETIME
              Format: dd/MM/yyyy HH:mm:ss
          - CastColumnTypeOperation:
              ColumnName: lastUpdatedTime
              NewColumnType: DATETIME
              Format: dd/MM/yyyy HH:mm:ss
          - CastColumnTypeOperation:
              ColumnName: startTime
              NewColumnType: DATETIME
              Format: dd/MM/yyyy HH:mm:ss
          - CreateColumnsOperation:
              Columns:
              - ColumnName: endTimeC
                ColumnId: 933a5d30-3922-496d-bc77-171eba9f0710
                Expression: ifelse( strlen({endTime})>0,parseDate(endTime,'dd/MM/yyyy HH:mm:ss'),NULL)
          - CreateColumnsOperation:
              Columns:
              - ColumnName: Status
                ColumnId: Status
                Expression: |
                  ifelse(endTimeC>now(), "Upcoming",
                  isNull(endTimeC) and {eventTypeCategory}="scheduledChange", "Upcoming",
                  addDateTime(7, 'DD', {startTime})<now() and {eventTypeCategory}="accountNotification" and isNull(endTimeC), "Archived",
                  isNull(endTimeC), "Active", "Archived")
          - CreateColumnsOperation:
              Columns:
              - ColumnName: eventDuration
                ColumnId: bcd2978c-5b1a-4eb4-829d-5ad5c66bf06e
                Expression: dateDiff({startTime},endTimeC,'HH')
          - ProjectOperation:
              ProjectedColumns:
              - eventTypeCode
              - eventSummary
              - accountName
              - monthlySpend
              - eventSource
              - eventScopeCode
              - eventTypeCategory
              - affectedEntities
              - eventArn
              - ingestionTime
              - service
              - eventDescription
              - lastUpdatedTime
              - startTime
              - eventRegion
              - account
              - statusCode
              - Status
              - eventDuration
              - endTime
              - endTimeC
          Source:
            PhysicalTableId: AWSHealthQSPT
      Permissions:
      - Principal: !Sub "${QuickSightUser}"
        Actions:
          - quicksight:DescribeDataSet
          - quicksight:DescribeDataSetPermissions
          - quicksight:PassDataSet
          - quicksight:DescribeIngestion
          - quicksight:ListIngestions
          - quicksight:UpdateDataSet
          - quicksight:DeleteDataSet
          - quicksight:CreateIngestion
          - quicksight:CancelIngestion
          - quicksight:UpdateDataSetPermissions

  AWSHealthEventQSDataSetRefresh:
    Type: AWS::QuickSight::RefreshSchedule
    DependsOn: AWSHealthEventQSDataSet
    Properties: 
      AwsAccountId: !Sub ${AWS::AccountId}
      DataSetId: !Sub "EventHealth-${AWS::AccountId}-${AWS::Region}"
      Schedule:
        RefreshType: FULL_REFRESH
        ScheduleFrequency:
          Interval: HOURLY
        ScheduleId: !Sub EventHealth-${AWS::AccountId}-${AWS::Region}-datarefresh

Outputs:
  AWSHealthEventQSDataSetArn:
    Value: !GetAtt AWSHealthEventQSDataSet.Arn
    Export:
      Name: AWSHealthEventQSDataSetArn