AWSTemplateFormatVersion: '2010-09-09'
Transform: AWS::SecretsManager-2020-07-23
Description: 'CloudFormation Template to create RDS MySQL DB Instance'

###############################################################################
# Parameters 
###############################################################################   

Parameters:

  ParentVPCStack:
    Description: 'Provide Stack name of parent VPC stack based on VPC-3AZs yaml template. Refer Cloudformation dashboard in AWS Console to get this.'
    Type: String
    MinLength: '1'
    MaxLength: '128'
    AllowedPattern: '^[a-zA-Z]+[0-9a-zA-Z\-]*$'
    
  ParentSSHBastionStack:
    Description: 'Provide Stack name of parent Amazon Linux bastion host stack based on VPC-SSH-Bastion yaml template. Refer Cloudformation dashboard in AWS Console to get this.'
    Type: String
    MinLength: '1'
    MaxLength: '128'
    AllowedPattern: '^[a-zA-Z]+[0-9a-zA-Z\-]*$'

  ParentDMSStack:
    Description: 'Optional. Provide DMS stack used to create replication instance. Refer Cloudformation dashboard in AWS Console to get this.'
    Type: String
    Default: ""
    
  DBName:
    Description: 'Database Name - must start with a letter. Only numbers, letters, and _ accepted. max length 64 characters'
    Type: String
    MinLength: '1'
    MaxLength: '64'
    AllowedPattern: "^[a-zA-Z]+[0-9a-zA-Z_]*$" 
    ConstraintDescription: Must start with a letter. Only numbers, letters, and _ accepted. max length 64 characters 

  DBPort:
    Description: TCP/IP Port for the Database Instance
    Type: Number
    Default: 3306
    ConstraintDescription: 'Must be in the range [1115-65535]'
    MinValue: 1115
    MaxValue: 65535
    
  DBUsername:
    Description: 'Database admin username'
    Type: String
    Default: 'dbadmin'
    MinLength: '1'
    MaxLength: '16'
    AllowedPattern: "^[a-zA-Z]+[0-9a-zA-Z_]*$"
    ConstraintDescription: Must start with a letter. Only numbers, letters, and _ accepted. max length 16 characters
     
  DBEngineVersion:
    Description: 'Select Database Engine Version'
    Type: String
    Default: '8.0.28'
    AllowedValues: ['8.0.28', '5.7.36']
     
  DBInstanceClass:
    Description: 'Database Instance Class'
    Type: String
    Default: db.r6g.large
    AllowedValues:
    - db.r6g.large
    - db.r6g.xlarge
    - db.r6g.2xlarge
    - db.r6g.4xlarge
    - db.r6g.8xlarge
    - db.r6g.12xlarge
    - db.r5.16xlarge

  DBAllocatedStorage:
    Description: 'The allocated storage size, specified in GB'
    Type: Number
    Default: 100
    MinValue: 5
    MaxValue: 16384

  DBSnapshotName:
    Description: Optional. DB Snapshot ID to restore database. Leave this blank if you are not restoring from a snapshot.
    Type: String
    Default: ""

  EnvironmentStage:
    Type: String
    Description: 'The environment tag is used to designate the Environment Stage of the associated AWS resource. Pre-prod and Prod will create read-replicas.'
    AllowedValues:
      - dev
      - test
      - pre-prod
      - prod
    Default: dev

  NotificationList:
    Type: String
    Description: 'The Email notification list is used to configure a SNS topic for sending cloudwatch alarm and RDS Event notifications'
    AllowedPattern: '^(([^<>()\[\]\\.,;:\s@"]+(\.[^<>()\[\]\\.,;:\s@"]+)*)|(".+"))@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}])|(([a-zA-Z\-0-9]+\.)+[a-zA-Z]{2,}))$'
    ConstraintDescription: provide a valid email address.
  
###########################################################################
# Optional tags that will be added to all resources that support tags
###########################################################################
  
  Application:
    Type: String
    Default: ''
    Description: 'Optional. The Application tag is used to designate the application of the associated AWS resource. In this capacity application does not refer to an installed software component, but rather the overall business application that the resource supports.'
    #AllowedPattern: "^[a-zA-Z]+[a-zA-Z ]+[a-zA-Z]+$"
    #ConstraintDescription: provide a valid application name containing only letters and spaces

  ApplicationVersion:
    Type: String
    Description: 'Optional. The ApplicationVersion tag is used to designate the specific version of the application. Format should be in the Pattern - "#.#.#"'
    Default: ''
    #AllowedPattern: '^[a-zA-Z0-9\.\-]+$'
    #ConstraintDescription: provide a valid application version
     
  ProjectCostCenter:
    Type: String
    Default: ''
    Description: 'Optional. The ProjectCostCenter tag is used to designate the cost center associated with the project of the given AWS resource.'
    #AllowedPattern: "^[a-zA-Z0-9]+$"
    #ConstraintDescription:  provide a valid cost center
 
  ServiceOwnersEmailContact:
    Type: String
    Default: ''
    Description: 'Optional. The ServiceOwnersEmailContact tag is used to designate business owner(s) email address associated with the given AWS resource for sending outage or maintenance notifications.'
    #AllowedPattern: '^(([^<>()\[\]\\.,;:\s@"]+(\.[^<>()\[\]\\.,;:\s@"]+)*)|(".+"))@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}])|(([a-zA-Z\-0-9]+\.)+[a-zA-Z]{2,}))$' !or ''
    #ConstraintDescription: provide a valid email address.
    
  Confidentiality:
    Type: String
    Default: ''
    Description: 'Optional. The Confidentiality tag is used to designate the confidentiality classification of the data that is associated with the resource.'
    #AllowedValues:
    #  - public
    #  - private
    #  - confidential
    #  - pii/phi
    
  Compliance:
    Type: String
    Default: ''
    Description: 'Optional. The Compliance tag is used to specify the Compliance level for the AWS resource.'
    #AllowedValues:
    #  - hipaa
    #  - sox
    #  - fips
    #  - other
    #  - none
    
###############################################################################
# Parameter groups
###############################################################################

Metadata:
  AWS::CloudFormation::Interface:
    ParameterGroups:
      -
        Label:
          default: 'Environment'
        Parameters:
          - EnvironmentStage
      -
        Label:
          default: 'DB Parameters'
        Parameters:
          - DBName
          - DBPort
          - DBUsername
          - DBInstanceClass
          - DBAllocatedStorage
          - DBEngineVersion
          - DBSnapshotName
          - NotificationList
      -
        Label:
          default: 'Networking'
        Parameters:
          - ParentVPCStack
          - ParentSSHBastionStack
          - ParentDMSStack
      -
        Label:
          default: 'Optional Tags'
        Parameters:
          - Application
          - ApplicationVersion
          - ProjectCostCenter          
          - ServiceOwnersEmailContact
          - Confidentiality
          - Compliance
          
###############################################################################
# Mappings
###############################################################################
 
Mappings: 
  DBFamilyMap: 
    "5.7.36":
      "family": "mysql5.7"
    "8.0.28":
      "family": "mysql8.0"

###############################################################################
# Conditions
############################################################################### 

Conditions:
  IsUseDBSnapshot: !Not [!Equals [!Ref DBSnapshotName, ""]]
  IsNotUseDBSnapshot: !Not [Condition: IsUseDBSnapshot]
  IsProd: !Equals [!Ref EnvironmentStage, 'prod']
  DBMultiAZ: !Or [!Equals [!Ref EnvironmentStage, 'pre-prod'], Condition: IsProd]
  IsDMS: !Not [!Equals [!Ref ParentDMSStack, '']]
  IsApplication: !Not [!Equals [!Ref Application, '']]
  IsApplicationVersion: !Not [!Equals [!Ref ApplicationVersion, '']]
  IsProjectCostCenter: !Not [!Equals [!Ref ProjectCostCenter, '']]
  IsServiceOwnersEmailContact: !Not [!Equals [!Ref ServiceOwnersEmailContact, '']]
  IsConfidentiality: !Not [!Equals [!Ref Confidentiality, '']]
  IsCompliance: !Not [!Equals [!Ref Compliance, '']]
  
###############################################################################
# Resources 
###############################################################################   
    
Resources:

  MonitoringIAMRole: 
    Type: AWS::IAM::Role
    Condition: IsProd
    Properties: 
      AssumeRolePolicyDocument: 
        Version: "2012-10-17"
        Statement: 
          - 
            Effect: "Allow"
            Principal: 
              Service: 
                - "monitoring.rds.amazonaws.com"
            Action: 
              - "sts:AssumeRole"
      Path: "/"
      ManagedPolicyArns: 
        - arn:aws:iam::aws:policy/service-role/AmazonRDSEnhancedMonitoringRole
        
  DBSNSTopic:
    Type: AWS::SNS::Topic
    Properties:
      KmsMasterKeyId: 'alias/aws/sns'
      Subscription:
      - Endpoint: !Ref NotificationList
        Protocol: email
        
  DBSubnetGroup:
    Type: 'AWS::RDS::DBSubnetGroup'
    Properties:
      DBSubnetGroupDescription: !Ref 'AWS::StackName'
      SubnetIds: !Split [',', {'Fn::ImportValue': !Sub '${ParentVPCStack}-SubnetsPrivate'}]  
      
  InstanceSecurityGroup:
    Type: 'AWS::EC2::SecurityGroup'
    Properties:
      GroupDescription: !Ref 'AWS::StackName'
      SecurityGroupIngress:
      - IpProtocol: tcp
        FromPort: !Ref DBPort
        ToPort: !Ref DBPort
        SourceSecurityGroupId: {'Fn::ImportValue': !Sub '${ParentSSHBastionStack}-BastionSecurityGroupID'}
        Description: 'Access to Bastion Host Security Group'
      - IpProtocol: tcp
        FromPort: !Ref DBPort
        ToPort: !Ref DBPort
        SourceSecurityGroupId: {'Fn::ImportValue': !Sub '${ParentVPCStack}-SecretRotationLambdaSecurityGroup'}
        Description: 'Access to Lambda Security Group'
      VpcId: {'Fn::ImportValue': !Sub '${ParentVPCStack}-VPC'}
      Tags:
      - Key: Name
        Value: !Sub '${AWS::StackName}-InstanceSecurityGroup'
        
  InstanceSecurityGroupIngress:
    Type: 'AWS::EC2::SecurityGroupIngress'
    Properties:
      GroupId: !GetAtt 'InstanceSecurityGroup.GroupId'
      IpProtocol: -1
      SourceSecurityGroupId: !Ref InstanceSecurityGroup
      Description: 'Self Reference'        

  InstanceSecurityGroupIngressDMS:
    Condition: IsDMS
    Type: 'AWS::EC2::SecurityGroupIngress'
    Properties:
      GroupId: !GetAtt 'InstanceSecurityGroup.GroupId'
      IpProtocol: tcp
      FromPort: !Ref DBPort
      ToPort: !Ref DBPort
      SourceSecurityGroupId: {'Fn::ImportValue': !Sub '${ParentDMSStack}-DMSSecurityGroupID'}
      Description: 'Access to DMS Security Group' 
        
  DBParamGroup:
    Type: AWS::RDS::DBParameterGroup
    Properties:
      Description: !Join [ "- ", [ "RDS MySQL Database Instance Parameter Group for Cloudformation Stack ", !Ref DBName ] ]
      Family: !FindInMap [DBFamilyMap, !Ref DBEngineVersion, "family"] 
      Parameters:
        slow_query_log: 1
        long_query_time: 5
        log_output: 'FILE'
        innodb_print_all_deadlocks: 1

  RDSMySQLMasterSecret:
    Condition: IsNotUseDBSnapshot
    Type: AWS::SecretsManager::Secret
    Properties:
      Name: !Join ['/', [!Ref EnvironmentStage, 'rds-mysql', !Ref 'AWS::StackName']]
      Description: !Join ['', ['RDS MySQL Master User Secret ', 'for CloudFormation Stack ', !Ref 'AWS::StackName']]
      Tags:
        -
          Key: EnvironmentStage
          Value: !Ref EnvironmentStage
        -
          Key: DatabaseEngine
          Value: 'RDS MySQL'
        -
          Key: StackID
          Value: !Ref 'AWS::StackId'
      GenerateSecretString:
        SecretStringTemplate: !Join ['', ['{"username": "', !Ref DBUsername, '"}']]
        GenerateStringKey: "password"
        ExcludeCharacters: '"@/\;+%'

  SecretRDSMySQLAttachment:
    Condition: IsNotUseDBSnapshot
    Type: AWS::SecretsManager::SecretTargetAttachment
    Properties:
      SecretId: !Ref RDSMySQLMasterSecret
      TargetId: !Ref DBInstance
      TargetType: AWS::RDS::DBInstance

  SecretResourcePolicy:
    Condition: IsNotUseDBSnapshot
    Type: AWS::SecretsManager::ResourcePolicy
    Properties:
      SecretId: !Ref RDSMySQLMasterSecret
      ResourcePolicy:
        Version: "2012-10-17"
        Statement:
          -
            Effect: "Deny"
            Principal:
              AWS: !Sub "arn:aws:iam::${AWS::AccountId}:root"
            Action: "secretsmanager:DeleteSecret"
            Resource: "*"
    
  SecretRotationLambda:
    Condition: IsNotUseDBSnapshot
    Type: AWS::SecretsManager::RotationSchedule
    DependsOn: 
      - SecretRDSMySQLAttachment
      - DBInstance
    Properties:
      SecretId: !Ref RDSMySQLMasterSecret
      HostedRotationLambda:
        RotationLambdaName: !Sub 'SecretsManager-SecretRotationFn-${AWS::StackName}'
        RotationType: 'MySQLSingleUser'
      RotationRules: 
        AutomaticallyAfterDays: 30  
       
  DBInstance:
    Type: AWS::RDS::DBInstance
    Properties:
      AllocatedStorage: !If [IsUseDBSnapshot, !Ref 'AWS::NoValue', !Ref DBAllocatedStorage]
      AllowMajorVersionUpgrade: false
      AutoMinorVersionUpgrade: !If [IsProd, 'false', 'true']
      BackupRetentionPeriod: !If [IsProd, 35, 7]
      CopyTagsToSnapshot: true
      DBInstanceClass: !Ref DBInstanceClass
      DBName: !If [IsUseDBSnapshot, !Ref "AWS::NoValue", !Ref DBName]
      DBParameterGroupName: !Ref DBParamGroup
      DBSubnetGroupName: !Ref DBSubnetGroup
      EnableIAMDatabaseAuthentication: true
      EnablePerformanceInsights: true
      Engine: mysql
      EngineVersion: !Ref DBEngineVersion
      Iops: 1000
      #KmsKeyId: !If [IsNotUseDBSnapshot, !Ref RDSMySQLKMSCMK, !Ref 'AWS::NoValue']
      MaxAllocatedStorage: 500 # Iops (1,000) * .5 = 500
      MasterUsername:
        !If [IsUseDBSnapshot, !Ref "AWS::NoValue", !Join ['', ['{{resolve:secretsmanager:', !Ref RDSMySQLMasterSecret, ':SecretString:username}}' ]]]
      MasterUserPassword:
        !If [IsUseDBSnapshot, !Ref "AWS::NoValue", !Join ['', ['{{resolve:secretsmanager:', !Ref RDSMySQLMasterSecret, ':SecretString:password}}' ]]]
      MonitoringInterval: !If [IsProd, 1, 0]
      MonitoringRoleArn: !If [IsProd, !GetAtt MonitoringIAMRole.Arn, !Ref "AWS::NoValue"]
      MultiAZ: !If [DBMultiAZ, true, false]
      #PerformanceInsightsKMSKeyId: !Ref RDSMySQLKMSCMK
      PerformanceInsightsRetentionPeriod: !If [IsProd, 731, 7]
      Port: !Ref DBPort
      PubliclyAccessible: false
      StorageEncrypted:  !If [IsUseDBSnapshot, !Ref "AWS::NoValue", true]
      StorageType: io1
      Tags:
        -
          Key: EnvironmentStage
          Value: !Ref EnvironmentStage
        -
          Key: Application
          Value: !If [IsApplication, !Ref Application, !Ref "AWS::NoValue"]
        -
          Key: ApplicationVersion
          Value: !If [IsApplicationVersion, !Ref ApplicationVersion, !Ref "AWS::NoValue"]
        -
          Key: ProjectCostCenter
          Value: !If [IsProjectCostCenter, !Ref ProjectCostCenter, !Ref "AWS::NoValue"]
        -
          Key: ServiceOwnersEmailContact
          Value: !If [IsServiceOwnersEmailContact, !Ref ServiceOwnersEmailContact, !Ref "AWS::NoValue"]          
        -
          Key: Confidentiality
          Value: !If [IsConfidentiality, !Ref Confidentiality, !Ref "AWS::NoValue"]
        -
          Key: Compliance
          Value: !If [IsCompliance, !Ref Compliance, !Ref "AWS::NoValue"]
      VPCSecurityGroups: 
        - !Ref InstanceSecurityGroup 
          
  CPUUtilizationAlarm1:
    Type: "AWS::CloudWatch::Alarm"
    Properties:
      ActionsEnabled: true
      AlarmActions:
      - Ref: DBSNSTopic
      AlarmDescription: 'CPU_Utilization'
      Dimensions:
      - Name: DBInstanceIdentifier
        Value:
          Ref: DBInstance
      MetricName: CPUUtilization
      Statistic: Maximum
      Namespace: 'AWS/RDS'
      Threshold: '80'
      Unit: Percent
      ComparisonOperator: 'GreaterThanOrEqualToThreshold'
      Period: '60'
      EvaluationPeriods: '5'
      TreatMissingData: 'notBreaching'

  FreeLocalStorageAlarm1:
    Type: "AWS::CloudWatch::Alarm"
    Properties:
      ActionsEnabled: true
      AlarmActions:
      - Ref: DBSNSTopic
      AlarmDescription: 'Free Local Storage'
      Dimensions:
      - Name: DBInstanceIdentifier
        Value:
          Ref: DBInstance
      MetricName: 'FreeLocalStorage'
      Statistic: Average
      Namespace: 'AWS/RDS'
      Threshold: '5368709120'
      Unit: Bytes
      ComparisonOperator: 'LessThanOrEqualToThreshold'
      Period: '60'
      EvaluationPeriods: '5'
      TreatMissingData: 'notBreaching' 

  DatabaseInstanceEventSubscription:
    Type: 'AWS::RDS::EventSubscription'
    Properties:
      EventCategories:
      - availability
      - configuration change
      - deletion
      - failover
      - failure
      - maintenance
      - notification
      - recovery
      SnsTopicArn: !Ref DBSNSTopic
      SourceIds: 
      - !Ref DBInstance
      SourceType: 'db-instance'
      
  DBParameterGroupEventSubscription:
    Type: 'AWS::RDS::EventSubscription'
    Properties:
      EventCategories:
      - configuration change
      SnsTopicArn: !Ref DBSNSTopic
      SourceIds: [!Ref DBParamGroup]
      SourceType: 'db-parameter-group'

###############################################################################
# Outputs 
###############################################################################  

Outputs:
  Endpoint:
    Description: 'The connection endpoint for the database'
    Value: !GetAtt 'DBInstance.Endpoint.Address'
  Port:
    Description: 'Endpoint Port'
    Value: !GetAtt 'DBInstance.Endpoint.Port'
  DBUsername:
    Description: 'Database admin username'
    Value: !Ref DBUsername
  DBName:
    Description: 'Database Name'
    Value: !Ref DBName
  MySQLCommandLine:    
    Description: MySQL Command Line
    Value: !Join
             - ''
             - - 'mysql -h '
               - !GetAtt 'DBInstance.Endpoint.Address' 
               - ' -P '
               - !GetAtt 'DBInstance.Endpoint.Port'
               - ' -u '
               - !Ref DBUsername 
               - ' -p '
               - !Ref DBName