---
AWSTemplateFormatVersion: '2010-09-09'
Description: >

  This Cloudformation Template deploys the Database Cluster (Amazon Aurora) for the BigBlueButton application infrastructure.

  Disclaimer: Not for production use. Demo and testing purposes only.

  Author: David Surey <suredavi@amazon.com>

Parameters:
  BBBNotificationTopic:
    Description: Topic to be used for alarm notifications
    Type: String
  BBBDBPort:
    Description: TCP/IP Port for the Database Instance
    Type: Number
    Default: 5432
  BBBDBInstanceType:
    Description: DB RDS instance type
    Type: String
    Default: db.serverless
  BBBServerlessAuroraMinCapacity:
    Description: The minimum capacity for the Amazon Aurora Serverless Cluster.
    Type: Number
    Default: 2
  BBBServerlessAuroraMaxCapacity:
    Description: The maximum capacity for the Amazon Aurora Serverless Cluster
    Type: Number
    Default: 4
  BBBPrivateDBSubnets:
    Description: Comma separated list of the private database subnets
    Type: CommaDelimitedList
  BBBDBEngineVersion:
    Description: Database engine version for Aurora PostgreSQL
    Type: String
    Default: 14.3
  BBBDBSecurityGroup:
    Description: Security Group that should be assigned for the database
    Type: String
  BBBEnvironmentStage:
    Type: String
    Description: Select the appropriate environment
    AllowedValues:
      - stage
      - prod
      - dev

Conditions:
  BBBProdEnvironment: !Equals [ !Ref BBBEnvironmentStage, prod ]
  BBBNonProdEnvironment: !Not [ Condition: BBBProdEnvironment ]
  BBBServerlessAurora: !Equals [ !Ref BBBDBInstanceType, db.serverless ]

Resources:
  BBBDBSubnetGroup:
    Type: AWS::RDS::DBSubnetGroup
    Properties:
      DBSubnetGroupDescription: Subnet Group for RDS Deployment
      SubnetIds:
        Ref: BBBPrivateDBSubnets

  BBBRDSDBSecret:
    Type: AWS::SecretsManager::Secret
    Properties:
      Description: 'This is the BBB Database instance secret'
      GenerateSecretString:
        SecretStringTemplate: '{"username": "BBBDBUsr"}'
        GenerateStringKey: 'password'
        PasswordLength: 16
        ExcludePunctuation: 'true'

  BBBRDSDBConnectionSecret:
    Type: AWS::SecretsManager::Secret
    Properties:
      Description: 'This is the BBB Database cluster url secret'
      SecretString: !Sub
        - '{"hostname": "${HOSTNAME}", "port": ${PORT}, "connectionString": "postgresql://${BBBDBUser}:${BBBDBPassword}@${HOSTNAME}:${PORT}"}'
        - HOSTNAME:
            Fn::GetAtt:
              - BBBRDSCluster
              - Endpoint.Address
          PORT:
            Fn::GetAtt:
              - BBBRDSCluster
              - Endpoint.Port
          BBBDBUser: !Join [ '', [ '{{resolve:secretsmanager:', !Ref BBBRDSDBSecret, ':SecretString:username}}' ] ]
          BBBDBPassword: !Join [ '', [ '{{resolve:secretsmanager:', !Ref BBBRDSDBSecret, ':SecretString:password}}' ] ]

  BBBDatabaseName:
    Type: AWS::SecretsManager::Secret
    Properties:
      Description: 'This is the BBB DBName as secret'      
      GenerateSecretString:
        SecretStringTemplate: '{"database": "BBBDB"}'
        GenerateStringKey: 'DBName'
        PasswordLength: 16
        ExcludePunctuation: true

  BBBRDSCluster:
    Type: AWS::RDS::DBCluster
    Properties:
      MasterUsername: !Join [ '', [ '{{resolve:secretsmanager:', !Ref BBBRDSDBSecret, ':SecretString:username}}' ] ]
      MasterUserPassword: !Join [ '', [ '{{resolve:secretsmanager:', !Ref BBBRDSDBSecret, ':SecretString:password}}' ] ]
      DatabaseName: !Join [ '', [ 'BBB', '{{resolve:secretsmanager:', !Ref BBBDatabaseName, ':SecretString:DBName}}' ] ]
      Port:
        Ref: BBBDBPort
      Engine: aurora-postgresql
      EngineVersion:
        Ref: BBBDBEngineVersion
      StorageEncrypted: 'true'
      DBSubnetGroupName:
        Ref: BBBDBSubnetGroup
      DBClusterParameterGroupName:
        Ref: "BBBRDSDBClusterParameterGroupPostgres"
      VpcSecurityGroupIds:
        - Ref: BBBDBSecurityGroup
      ServerlessV2ScalingConfiguration:
        !If
        - BBBServerlessAurora
        - MinCapacity: !Ref BBBServerlessAuroraMinCapacity
          MaxCapacity: !Ref BBBServerlessAuroraMaxCapacity
        - !Ref "AWS::NoValue"

  BBBRDSDBInstance1:
    Type: AWS::RDS::DBInstance
    Properties:
      AllowMajorVersionUpgrade: 'false'
      AutoMinorVersionUpgrade: 'true'
      CopyTagsToSnapshot: 'true'
      DBSubnetGroupName:
        Ref: BBBDBSubnetGroup
      Engine: aurora-postgresql
      EngineVersion:
        Ref: BBBDBEngineVersion
      DBClusterIdentifier:
        Ref: BBBRDSCluster
      DBParameterGroupName:
        Ref: BBBRDSDBParameterGroupPostgres
      AvailabilityZone:
        Fn::Select:
          - '0'
          - Fn::GetAZs:
              Ref: AWS::Region
      DBInstanceClass:
        Ref: BBBDBInstanceType

  BBBRDSDBInstance2:
    Type: AWS::RDS::DBInstance
    Condition: BBBProdEnvironment
    Properties:
      AllowMajorVersionUpgrade: 'false'
      AutoMinorVersionUpgrade: 'true'
      CopyTagsToSnapshot: 'true'
      DBSubnetGroupName:
        Ref: BBBDBSubnetGroup
      Engine: aurora-postgresql
      EngineVersion:
        Ref: BBBDBEngineVersion
      DBClusterIdentifier:
        Ref: BBBRDSCluster
      AvailabilityZone:
        Fn::Select:
          - '1'
          - Fn::GetAZs:
              Ref: AWS::Region
      DBInstanceClass:
        Ref: BBBDBInstanceType

  BBBRDSDBParameterGroupPostgres:
    Type: AWS::RDS::DBParameterGroup
    Properties:
      Description: Aurora PG 14 Database Instance Parameter Group for Cloudformation Stack
      Family: !If [ BBBServerlessAurora, aurora-postgresql14, aurora-postgresql14 ]
      Parameters:
        shared_preload_libraries: auto_explain,pg_stat_statements,pg_hint_plan,pgaudit
        log_statement: "ddl"
        log_connections: 1
        log_disconnections: 1
        log_lock_waits: 1
        log_min_duration_statement: 5000
        auto_explain.log_min_duration: 5000
        auto_explain.log_verbose: 1
        log_rotation_age: 1440
        log_rotation_size: 102400
        rds.log_retention_period: 10080
        random_page_cost: 1
        track_activity_query_size: 16384
        idle_in_transaction_session_timeout: 7200000
        statement_timeout: 7200000
        search_path: '"$user",public'

  BBBRDSDBClusterParameterGroupPostgres:
    Type: AWS::RDS::DBClusterParameterGroup
    Properties:
      Description: CloudFormation Sample Aurora Cluster PG Parameter Group
      Family: !If [ BBBServerlessAurora, aurora-postgresql14, aurora-postgresql14 ]
      Parameters:
        timezone: Europe/Berlin

  DBClusterEventSubscription:
    Type: 'AWS::RDS::EventSubscription'
    Properties:
      EventCategories:
        - failover
        - failure
        - notification
      SnsTopicArn:
        Ref: BBBNotificationTopic
      SourceIds:
        - Ref: BBBRDSCluster
      SourceType: 'db-cluster'

Outputs:
  BBBDBName: 
    Value: 
      !Join [ '_', [ 'APPDB', !Ref AWS::StackName ] ]
  BBBDB:
    Description: The Big Blue Button Database Created
    Value:
      Ref: BBBRDSCluster
  BBBRDSDBConnectionSecret:
    Description: The Big Blue Button DB Connection Data
    Value:
      Ref: BBBRDSDBConnectionSecret
  BBBRDSDBClusterParameterGroup:
    Description: The Big Blue Button DB Cluster Parameter Group
    Value:
      Ref: "BBBRDSDBClusterParameterGroupPostgres"
  BBBDBSubnetGroup:
    Description: The Big Blue Button DB Subnet Group
    Value:
      Ref: BBBDBSubnetGroup
  BBBRDSCluster:
    Description: The Big Blue Button DB Cluster
    Value:
      Ref: BBBRDSCluster
  BBBRDSDBInstance1:
    Description: The Big Blue Button DB Instance 1
    Value:
      Ref: BBBRDSDBInstance1
  BBBRDSDBInstance2:
    Condition: BBBProdEnvironment
    Description: The Big Blue Button DB Instance 2
    Value:
      Ref: BBBRDSDBInstance2
  BBBRDSDBParameterGroup:
    Description: The Big Blue Button DB Parameter Group
    Value:
      Ref: "BBBRDSDBParameterGroupPostgres"