Parameters:
  AllocatedStorage:
    Description: The amount of storage in gibibytes (GiB) to be initially allocated for the database instance.
    Type: String
  CustomIAMInstanceProfile:
    Description: The instance profile associated with the underlying Amazon EC2 instance of an RDS Custom DB instance.
    Type: String
    Default: "AWSRDSCustomInstanceProfile-<region>"
  DBInstanceIdentifier:
    Description: A name for the DB instance
    Type: String
  DBInstanceClass:
    Description: The compute and memory capacity of the DB instance
    Type: String
    Default: "db.m5.xlarge"
  DBName:
    Type: String
    Description: Oracle Database Name
  DBSubnetGroupName:
    Description: A DB subnet group to associate with the DB instance
    Type: String
  Engine:
    Type: String
    Description: The name of the database engine that you want to use for this DB instance.
    Default: "custom-oracle-ee"
  EngineVersion:
    Type: String
    Description: RDS Custom CEV name
    Default: "19.<CEV-Name>"
  KMSKeyId:
    Type: String
    Description: KMS Key ID for RDS Custom for Oracle
  MasterUsername:
    Type: String
    Description: RDS Database Username
    NoEcho: true
  MasterUserPassword:
    Type: String
    Description: RDS Database password
    NoEcho: true
  JDEDBSecGrp:
    Type: String
    Description: Security Group for JDE RDS DB Server
  JDEPubKey:
    Description: The pub key used by JDE One-Click to connect to the hosts
    Type: String
  CEVCreation:
    Description: Please select the option "yes" for creating the CEV , if not leave it default for creating one for you
    Type: String
    Default: "No"
    AllowedValues: ["Yes","No"]
  DatabaseInstallationFilesS3BucketName:
    Description: S3 bucket with Database installation files for CEV creation
    Type: String
  S3Prefix:
    Description: Does S3 prefix present in the S3 bucket?
    Type: String
    Default: "No"
    AllowedValues: ["Yes","No"]
  DatabaseInstallationFilesS3Prefix:
    Description: S3 bucket prefix with Database installation files for CEV creation , leave it empty if you do not have any prefix.
    Type: String

Metadata:
  AWS::CloudFormation::Interface:
    ParameterGroups:
      - Label:
          default: "CEV Creation Requirements"
        Parameters:
          - CEVCreation
          - DatabaseInstallationFilesS3BucketName
          - S3Prefix
          - DatabaseInstallationFilesS3Prefix
      - Label:
          default: "RDS Custom Creation"
        Parameters:
          - DBInstanceIdentifier
          - DBInstanceClass
          - DBName
          - CustomIAMInstanceProfile
          - AllocatedStorage
          - JDEDBSecGrp
          - DBSubnetGroupName
          - Engine
          - EngineVersion
          - KMSKeyId
          - MasterUsername
          - MasterUserPassword
      - Label:
          default: "JDE Public Key"
        Parameters:
          - JDEPubKey


Conditions:
  ShouldAddProperty: !Equals [!Ref S3Prefix, "Yes"]
  ShouldAddCEV: !Equals [ !Ref CEVCreation, "Yes" ]

Resources:

  CEVWaithandle:
    Condition: ShouldAddCEV
    DependsOn: CustomOracleJDEOneClickCEV
    Type: "AWS::CloudFormation::WaitConditionHandle"
  
  WaitHandle:
    Type: "AWS::CloudFormation::WaitConditionHandle"
  
  WaitCondition:
    Type: "AWS::CloudFormation::WaitCondition"
    Properties:
      Handle: !If [ShouldAddCEV,!Ref CEVWaithandle , !Ref WaitHandle ]
      Timeout: "1"
      Count: 0

  CustomOracleJDEOneClickCEV:
    Condition: ShouldAddCEV
    Type: AWS::RDS::CustomDBEngineVersion
    Properties:
      DatabaseInstallationFilesS3BucketName: !Ref DatabaseInstallationFilesS3BucketName
      DatabaseInstallationFilesS3Prefix:
        Fn::If:
          - ShouldAddProperty
          - !Ref DatabaseInstallationFilesS3Prefix
          - Ref: "AWS::NoValue"
      Description: CEV for RDS custom for Oracle
      Engine: !Ref Engine
      EngineVersion: !Ref EngineVersion
      KMSKeyId: !Ref KMSKeyId
      Manifest: '{"mediaImportTemplateVersion":"2020-08-14","databaseInstallationFileNames":["V982063-01.zip"],"opatchFileNames":["p6880880_190000_Linux-x86-64.zip"],"installationParameters": {"unixGroupName": "dba","unixGroupId": 10001,"unixUname": "oracle","unixUid": 10001,"oracleHome": "/u01/oracle19c","oracleBase": "/u01"}}'

  RDSDBInstance:
    DependsOn: WaitCondition
    Type: AWS::RDS::DBInstance
    Properties:
      AllocatedStorage: !Ref AllocatedStorage
      DBInstanceIdentifier: !Ref DBInstanceIdentifier
      AutoMinorVersionUpgrade: false
      CustomIAMInstanceProfile: !Ref CustomIAMInstanceProfile
      DBInstanceClass: !Ref DBInstanceClass
      DBSubnetGroupName: !Ref DBSubnetGroupName
      Engine: !Ref Engine
      EngineVersion: !Ref EngineVersion
      KmsKeyId: !Ref KMSKeyId
      MasterUsername: !Ref MasterUsername
      MasterUserPassword: !Ref MasterUserPassword
      StorageEncrypted: 'true'
      VPCSecurityGroups: 
        - !Ref JDEDBSecGrp
      DBName: !Ref DBName
      PreferredBackupWindow: '03:00-04:00'
      PreferredMaintenanceWindow: 'Mon:05:00-Mon:06:00'
      PubliclyAccessible: false
      Tags:
        - Key: Environment
          Value: Production

  ssmJDEsetup:
    Type: "AWS::SSM::Document"
    Properties:
      DocumentType: Automation
      Name: !Join ['-', ['JDESetup-OS-SSM', !Select [4, !Split ['-', !Select [2, !Split ['/', !Ref AWS::StackId]]]],!Sub "${AWS::Region}"]]
      Content:
        schemaVersion: '0.3'
        description: "JDE OneClick Pre-reqs"
        assumeRole: '{{ AutomationAssumeRole }}'
        parameters:
          AutomationAssumeRole:
            type: String
            description: (Optional) The ARN of the role that allows Automation to perform the actions on your behalf.
            default: ''
        mainSteps:
          - name: GetDBId
            action: 'aws:executeAwsApi'
            onFailure: Abort
            inputs:
              Service: rds
              Api: DescribeDBInstances
              DBInstanceIdentifier: !Ref DBInstanceIdentifier
            outputs:
              - Name: DbiResourceId
                Selector: '$.DBInstances[0].DbiResourceId'
                Type: String
          - name: GetInstId
            action: 'aws:executeAwsApi'
            onFailure: Abort
            inputs:
              Service: ec2
              Api: DescribeInstances
              Filters:
                - Name: 'tag:Name'
                  Values: 
                    - '{{GetDBId.DbiResourceId}}'
            outputs:
              - Name: InstanceId
                Selector: '$.Reservations[0].Instances[0].InstanceId'
                Type: String
          - name: AddOSuser
            action: 'aws:runCommand'
            inputs:
              DocumentName: AWS-RunShellScript
              InstanceIds: 
                - '{{GetInstId.InstanceId}}'
              Parameters:
                commands:
                  - useradd -d /home/opc -m -s /bin/bash opc
                  - chmod 770 /u01
                  - groupadd oracle
                  - usermod -a -G oracle opc
                  - usermod -a -G oracle oracle
                  - chgrp oracle /u01
                  - mkdir /home/opc/.ssh
                  - !Sub echo "${JDEPubKey}" > /home/opc/.ssh/authorized_keys
                  - chown -R opc:opc /home/opc/
                  - chmod -R 600 /home/opc/.ssh
                  - echo "umask 0022" >> /home/opc/.bashrc
                  - echo "opc ALL="\(ALL\)" "NOPASSWD\:" ALL" >>/etc/sudoers
                  - sed -i.bak s/^SELINUX=.*$/SELINUX=disabled/ /etc/selinux/config
                  - sed -i.bak 's/.*ClientAliveInterval.*/ClientAliveInterval 3600/g; s/.*AddressFamily.*/AddressFamily inet/g' /etc/ssh/sshd_config
                  - echo "net.ipv6.conf.default.disable_ipv6=1" >> /etc/sysctl.conf
                  - echo "net.ipv6.conf.all.disable_ipv6=1" >> /etc/sysctl.conf
                  - echo "net.ipv4.conf.all.rp_filter=2" >> /etc/sysctl.conf
            isEnd: true

  ssmJDERDSsetup:
    Type: "AWS::SSM::Document"
    Properties:
      DocumentType: Automation
      Name: !Join ['-', ['JDESetup-RDS-SSM', !Select [4, !Split ['-', !Select [2, !Split ['/', !Ref AWS::StackId]]]],!Sub "${AWS::Region}"]]
      Content:
        schemaVersion: '0.3'
        description: "RDS JDE OneClick steps"
        assumeRole: '{{ AutomationAssumeRole }}'
        parameters:
          AutomationAssumeRole:
            type: String
            description: (Optional) The ARN of the role that allows Automation to perform the actions on your behalf.
            default: arn:aws:iam::040148700404:role/ssm-automation-execution-role
        mainSteps:
          - name: WaitForRDSAvailableState
            action: 'aws:waitForAwsResourceProperty'
            maxAttempts: 10
            timeoutSeconds: 120
            onFailure: Abort
            inputs:
              Service: rds
              Api: DescribeDBInstances
              DBInstanceIdentifier: !Ref DBInstanceIdentifier
              PropertySelector: '$.DBInstances[0].DBInstanceStatus'
              DesiredValues:
                - available
          - name: PauseAutomation
            action: 'aws:executeAwsApi'
            onFailure: Abort
            inputs:
              Service: rds
              Api: ModifyDBInstance
              DBInstanceIdentifier: !Ref DBInstanceIdentifier
              AutomationMode: all-paused
          - name: WaitForAvailableState
            action: 'aws:waitForAwsResourceProperty'
            maxAttempts: 10
            timeoutSeconds: 120
            onFailure: Abort
            inputs:
              Service: rds
              Api: DescribeDBInstances
              DBInstanceIdentifier: !Ref DBInstanceIdentifier
              PropertySelector: '$.DBInstances[0].DBInstanceStatus'
              DesiredValues:
                - automation-paused
          - name: GetDBId
            action: 'aws:executeAwsApi'
            onFailure: Abort
            inputs:
              Service: rds
              Api: DescribeDBInstances
              DBInstanceIdentifier: !Ref DBInstanceIdentifier
            outputs:
              - Name: DbiResourceId
                Selector: '$.DBInstances[0].DbiResourceId'
                Type: String
          - name: GetInstId
            action: 'aws:executeAwsApi'
            onFailure: Abort
            inputs:
              Service: ec2
              Api: DescribeInstances
              Filters:
                - Name: 'tag:Name'
                  Values: 
                    - '{{GetDBId.DbiResourceId}}'
            outputs:
              - Name: InstanceId
                Selector: '$.Reservations[0].Instances[0].InstanceId'
                Type: String
          - name: ChangeDBParam
            action: 'aws:runCommand'
            inputs:
              DocumentName: AWS-RunShellScript
              InstanceIds: 
                - '{{GetInstId.InstanceId}}'
              Parameters:
                commands:
                  - export OSUSER=`ps -ef | grep pmon | grep -v grep | cut -d " " -f1`
                  - echo shutdown immediate | sudo su - $OSUSER -c "sqlplus '/ as sysdba'"
                  - echo startup restrict | sudo su - $OSUSER -c "sqlplus '/ as sysdba'"
                  - sudo su - $OSUSER -c "sqlplus '/ as sysdba'"<<EOF
                  - SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_CHARACTERSET';
                  - exit
                  - EOF
                  - sudo su - $OSUSER -c "sqlplus '/ as sysdba'"<<EOF
                  - ALTER DATABASE CHARACTER SET INTERNAL_CONVERT AL32UTF8;
                  - exit
                  - EOF
                  - echo shutdown immediate | sudo su - $OSUSER -c "sqlplus '/ as sysdba'"
                  - echo startup | sudo su - $OSUSER -c "sqlplus '/ as sysdba'"
                  - sudo su - $OSUSER -c "sqlplus '/ as sysdba'"<<EOF
                  - SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_CHARACTERSET';
                  - exit
                  - EOF
          - name: ResumeAutomation
            action: 'aws:executeAwsApi'
            inputs:
              Service: rds
              Api: ModifyDBInstance
              DBInstanceIdentifier: !Ref DBInstanceIdentifier
              AutomationMode: full
            isEnd: true

  SSMExecutionRole: 
    Type : AWS::IAM::Role
    Properties:
      RoleName: !Join ['-', ['SSM-JDEOneClick-Role', !Select [4, !Split ['-', !Select [2, !Split ['/', !Ref AWS::StackId]]]],!Sub "${AWS::Region}"]]
      ManagedPolicyArns:
        - !Sub 'arn:${AWS::Partition}:iam::aws:policy/service-role/AmazonSSMAutomationRole'
        - arn:aws:iam::aws:policy/AmazonRDSFullAccess
        - arn:aws:iam::aws:policy/AmazonEC2FullAccess
        - arn:aws:iam::aws:policy/AmazonSSMManagedInstanceCore
      AssumeRolePolicyDocument:
        Version: "2012-10-17"
        Statement:
        - Effect: "Allow"
          Principal:
            Service:
            - "ec2.amazonaws.com"
            - "ssm.amazonaws.com"
          Action: "sts:AssumeRole"

  RunthessmJDERDSsetup:
    Type: AWS::SSM::Association
    DependsOn: [ssmJDERDSsetup,ssmJDEsetup,RDSDBInstance,RunthessmJDEsetup]
    Properties:
      AssociationName: !Join ['-', ['JDESetup-RDS-SSM-Association', !Select [4, !Split ['-', !Select [2, !Split ['/', !Ref AWS::StackId]]]],!Sub "${AWS::Region}"]]
      Name: !Ref ssmJDERDSsetup
      Parameters:
        AutomationAssumeRole: 
          - !GetAtt 'SSMExecutionRole.Arn'

  RunthessmJDEsetup:
    Type: AWS::SSM::Association
    DependsOn: [ssmJDERDSsetup,ssmJDEsetup,RDSDBInstance]
    Properties:
      AssociationName: !Join ['-', ['JDESetup-OS-SSM-Association', !Select [4, !Split ['-', !Select [2, !Split ['/', !Ref AWS::StackId]]]],!Sub "${AWS::Region}"]]
      Name: !Ref ssmJDEsetup
      Parameters:
        AutomationAssumeRole: 
          - !GetAtt 'SSMExecutionRole.Arn'
          
