AWSTemplateFormatVersion: 2010-09-09

Parameters:
  vpccidr:
    Type: String
    Description: VPC hosting IMDb source application
    MinLength: 9
    MaxLength: 18
    ConstraintDescription: Must be a valid CIDR range in the form x.x.x.x/16
    Default: 10.0.0.0/16
  DBPrivateSubnetCIDR:
    Type: String
    Description: Enter CIDR range for DB Private Subnet
    MinLength: 9
    MaxLength: 18
    ConstraintDescription: Must be a valid CIDR range in the form x.x.x.x/x
    Default: 10.0.1.0/24
  DBPublicSubnetCIDR:
    Type: String
    Description: Enter CIDR range for DB Public Subnet
    MinLength: 9
    MaxLength: 18
    ConstraintDescription: Must be a valid CIDR range in the form x.x.x.x/x
    Default: 10.0.2.0/24
  DBMasterUsername:
    Description: IMDb MySQL database user name
    Type: String
    Default: dbuser
  DBMasterPassword:
    Description: IMDb MySQL database user password
    Type: String
    NoEcho: true
    MinLength: 8
    MaxLength: 64
  SecretName:
    Description: Secret Manager secret name for IMDb database
    Type: String
    Default: MySQLDBPassword
  DBInstanceType:
    Description: WebServer EC2 instance type
    Type: String
    Default: m5.large
    AllowedValues: [t2.nano, t2.micro, t2.small, t2.medium, t2.large, t2.xlarge, t2.2xlarge,
      t3.nano, t3.micro, t3.small, t3.medium, t3.large, t3.xlarge, t3.2xlarge,
      m4.large, m4.xlarge, m4.2xlarge, m4.4xlarge, m4.10xlarge,
      m5.large, m5.xlarge, m5.2xlarge, m5.4xlarge,
      c5.large, c5.xlarge, c5.2xlarge, c5.4xlarge, c5.9xlarge,
      g3.8xlarge,
      r5.large, r5.xlarge, r5.2xlarge, r5.4xlarge, r3.12xlarge,
      i3.xlarge, i3.2xlarge, i3.4xlarge, i3.8xlarge,
      d2.xlarge, d2.2xlarge, d2.4xlarge, d2.8xlarge]
    ConstraintDescription: must be a valid EC2 instance type.
  DBLatestAmiId:
    Type:  'AWS::SSM::Parameter::Value<AWS::EC2::Image::Id>'
    Default: '/aws/service/ami-amazon-linux-latest/amzn2-ami-hvm-x86_64-gp2'
Resources:
  DBSecret:
    Type: 'AWS::SecretsManager::Secret'
    Properties:
      Name: !Ref SecretName
      Description: Database password for migration blog
      SecretString: !Ref DBMasterPassword
  Vpc:
    Type: AWS::EC2::VPC
    Properties:
      CidrBlock: !Ref vpccidr
      EnableDnsSupport: true
      EnableDnsHostnames: true
      InstanceTenancy: default
      Tags:
        - Key: Name
          Value: DB-VPC
  InternetGateway:
    Type: AWS::EC2::InternetGateway
    Properties:
      Tags:
        - Key: Name
          Value: DB-IGW
  AttachGateway:
    Type: AWS::EC2::VPCGatewayAttachment
    Properties:
      VpcId: !Ref Vpc
      InternetGatewayId: !Ref InternetGateway
  PrivateRouteTable:
    Type: AWS::EC2::RouteTable
    Properties:
      VpcId: !Ref Vpc
      Tags:
        - Key: Name
          Value: DB-PRIRT
  DbPrivateSubnet:
    Type: AWS::EC2::Subnet
    Properties:
      CidrBlock: !Ref DBPrivateSubnetCIDR
      AvailabilityZone:
        Fn::Select:
        - 0
        - Fn::GetAZs: !Ref 'AWS::Region'
      MapPublicIpOnLaunch: false
      VpcId: !Ref Vpc
      Tags:
        - Key: Name
          Value: DB-PRI01
  DbPublicSubnet:
    Type: AWS::EC2::Subnet
    Properties:
      CidrBlock: !Ref DBPublicSubnetCIDR
      AvailabilityZone:
        Fn::Select:
        - 0
        - Fn::GetAZs: !Ref 'AWS::Region'
      MapPublicIpOnLaunch: false
      VpcId: !Ref Vpc
      Tags:
        - Key: Name
          Value: DB-PUB01
  PrivateRouteTable:
    Type: AWS::EC2::RouteTable
    Properties:
      VpcId: !Ref Vpc
      Tags:
        - Key: Name
          Value: DB-PRIRT
  PublicRouteTable:
    Type: AWS::EC2::RouteTable
    Properties:
      VpcId: !Ref Vpc
      Tags:
        - Key: Name
          Value: DB-PUBRT
  PublicRoute:
    Type: AWS::EC2::Route
    Properties:
      RouteTableId: !Ref PublicRouteTable
      DestinationCidrBlock: 0.0.0.0/0
      GatewayId: !Ref InternetGateway
  DbSubnetRouteTableAssociation:
    Type: AWS::EC2::SubnetRouteTableAssociation
    Properties:
      RouteTableId: !Ref PrivateRouteTable
      SubnetId: !Ref DbPrivateSubnet
  DbPublicSubnetRouteTableAssociation:
    Type: AWS::EC2::SubnetRouteTableAssociation
    Properties:
      RouteTableId: !Ref PublicRouteTable
      SubnetId: !Ref DbPublicSubnet
#NAT Gateway configuration starts here
  NatGateway:
    Type: AWS::EC2::NatGateway
    DependsOn: NatEIP
    Properties:
      AllocationId:
        Fn::GetAtt:
        - NatEIP
        - AllocationId
      SubnetId:
        Ref: DbPublicSubnet
  NatEIP:
    Type: AWS::EC2::EIP
    Properties:
      Domain: vpc
  NatRoute:
    Type: AWS::EC2::Route
    DependsOn: NatGateway
    Properties:
      RouteTableId:
        Ref: PrivateRouteTable
      DestinationCidrBlock: 0.0.0.0/0
      NatGatewayId:
        Ref: NatGateway
  #SSM Endpoints Starts Here
  EndpointSecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupDescription: Security Group
      SecurityGroupIngress:
      - IpProtocol: tcp
        FromPort: 443
        ToPort: 443
        CidrIp: !Ref vpccidr
      SecurityGroupEgress:
      - IpProtocol: tcp
        FromPort: 443
        ToPort: 443
        CidrIp: !Ref vpccidr
      VpcId: !Ref Vpc
  SSMEndpoint:
    Type: "AWS::EC2::VPCEndpoint"
    Properties:
      SecurityGroupIds:
        - !Ref EndpointSecurityGroup
      ServiceName: !Sub com.amazonaws.${AWS::Region}.ssm
      SubnetIds:
        - !Ref DbPrivateSubnet
      VpcEndpointType: Interface
      PrivateDnsEnabled: true
      VpcId: !Ref Vpc

  Ec2MsgEndpoint:
    Type: "AWS::EC2::VPCEndpoint"
    Properties:
      SecurityGroupIds:
        - !Ref EndpointSecurityGroup
      ServiceName: !Sub com.amazonaws.${AWS::Region}.ec2messages
      SubnetIds:
        - !Ref DbPrivateSubnet
      VpcEndpointType: Interface
      PrivateDnsEnabled: true
      VpcId: !Ref Vpc

  SSMMsgEndpoint:
    Type: "AWS::EC2::VPCEndpoint"
    Properties:
      SecurityGroupIds:
        - !Ref EndpointSecurityGroup
      ServiceName: !Sub com.amazonaws.${AWS::Region}.ssmmessages
      SubnetIds:
        - !Ref DbPrivateSubnet
      VpcEndpointType: Interface
      PrivateDnsEnabled: true
      VpcId: !Ref Vpc
  #SSM Endpoints Close Here
  DbSecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupDescription: MySQL security group
      VpcId: !Ref Vpc
      SecurityGroupIngress:
        - CidrIp: !Ref vpccidr
          Description: MySQL Access
          IpProtocol: tcp
          FromPort: 3306
          ToPort: 3306
      SecurityGroupEgress:
        - IpProtocol: tcp
          Description: Outbound HTTP Access
          FromPort: 80
          ToPort: 80
          CidrIp: 0.0.0.0/0
        - IpProtocol: tcp
          Description: Outbound HTTPS Access
          FromPort: 443
          ToPort: 443
          CidrIp: 0.0.0.0/0
      Tags:
        - Key: Name
          Value: MySQL-SecurityGroup
  DBInstanceProfile:
    Type: AWS::IAM::InstanceProfile
    Properties:
      InstanceProfileName: DBInstanceProfile
      Path: /
      Roles:
       - !Ref DBInstanceRole
  DBInstanceRole:
    Type: AWS::IAM::Role
    Properties:
      RoleName: DBInstanceRole
      AssumeRolePolicyDocument:
        Version: 2012-10-17
        Statement:
          -
            Effect: Allow
            Principal:
              Service:
                - ec2.amazonaws.com
            Action:
              - sts:AssumeRole
      Path: /
      Policies:
        - PolicyName: DatabaseSecret
          PolicyDocument:
            Version: "2012-10-17"
            Statement:
              - Effect: Allow
                Action:
                  - secretsmanager:GetSecretValue
                  - secretsmanager:DescribeSecret
                  - secretsmanager:ListSecretVersionIds
                Resource: !Ref DBSecret
      ManagedPolicyArns:
        - arn:aws:iam::aws:policy/AmazonSSMManagedInstanceCore
  DbInstance:
    Type: AWS::EC2::Instance
    Properties:
      ImageId: !Ref DBLatestAmiId
      InstanceType: !Ref DBInstanceType
      NetworkInterfaces:
        - AssociatePublicIpAddress: false
          DeviceIndex: 0
          SubnetId: !Ref DbPrivateSubnet
          GroupSet:
          - !Ref DbSecurityGroup
      IamInstanceProfile: !Ref DBInstanceProfile
      BlockDeviceMappings:
        - DeviceName: /dev/xvda
          Ebs:
            VolumeType: gp2
            VolumeSize: '200'
            DeleteOnTermination: 'true'
            Encrypted: 'true'
      UserData:
        Fn::Base64: !Sub |
          #!/bin/bash -ex
          sudo su
          rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql
          #yum localinstall -y https://dev.mysql.com/get/mysql80-community-release-el7-7.noarch.rpm
          wget https://dev.mysql.com/get/mysql80-community-release-el7-7.noarch.rpm
          rpm -Uvh mysql80-community-release-el7-7.noarch.rpm
          yum install -y mysql-community-server
          systemctl enable mysqld
          systemctl start mysqld
          aws configure set default.region ${AWS::Region}
          export DBPASSWORD=$(aws secretsmanager get-secret-value --secret-id ${SecretName} --query SecretString --output text)
          mysql -u root "-p$(grep -oP '(?<=root@localhost\: )\S+' /var/log/mysqld.log)" -e "ALTER USER 'root'@'localhost' IDENTIFIED BY '$DBPASSWORD'" --connect-expired-password
          mysql -u root "-p$DBPASSWORD" -e "CREATE USER '${DBMasterUsername}' IDENTIFIED BY '$DBPASSWORD'"
          mysql -u root "-p$DBPASSWORD" -e "GRANT ALL PRIVILEGES ON *.* TO '${DBMasterUsername}'"
          mysql -u root "-p$DBPASSWORD" -e "FLUSH PRIVILEGES"
          cd /var/lib/mysql-files/
          curl -O https://aws-blogs-artifacts-public.s3.amazonaws.com/artifacts/DBBLOG-2250/rdbms-migration.zip
          unzip -q rdbms-migration.zip
          chmod 775 *.*
          mysql -u root "-p$DBPASSWORD" -e "CREATE DATABASE imdbdata;"
          mysql -u root "-p$DBPASSWORD" -e "CREATE TABLE imdbdata.title_akas (titleId VARCHAR(200), ordering VARCHAR(200),title VARCHAR(1000), region VARCHAR(1000), language VARCHAR(1000), types VARCHAR(1000),attributes VARCHAR(1000),isOriginalTitle VARCHAR(5),primary key (titleId, ordering));"
          mysql -u root "-p$DBPASSWORD" -e "CREATE TABLE imdbdata.title_basics (tconst  VARCHAR(200), titleType  VARCHAR(1000),primaryTitle  VARCHAR(1000), originalTitle  VARCHAR(1000), isAdult  VARCHAR(1000), startYear  VARCHAR(1000),endYear  VARCHAR(1000),runtimeMinutes  VARCHAR(1000),genres  VARCHAR(1000),primary key (tconst));"
          mysql -u root "-p$DBPASSWORD" -e "CREATE TABLE imdbdata.title_crew (tconst  VARCHAR(200), directors  VARCHAR(1000),writers  VARCHAR(1000),primary key (tconst));"
          mysql -u root "-p$DBPASSWORD" -e "CREATE TABLE imdbdata.title_principals (tconst  VARCHAR(200), ordering  VARCHAR(200),nconst  VARCHAR(200), category  VARCHAR(1000), job  VARCHAR(1000), characters  VARCHAR(1000),primary key (tconst,ordering,nconst));"
          mysql -u root "-p$DBPASSWORD" -e "CREATE TABLE imdbdata.title_ratings (tconst  VARCHAR(200), averageRating float,numVotes  integer,primary key (tconst));"
          mysql -u root "-p$DBPASSWORD" -e "CREATE TABLE imdbdata.name_basics (nconst  VARCHAR(200), primaryName  VARCHAR(1000),birthYear  VARCHAR(1000), deathYear  VARCHAR(1000), primaryProfession  VARCHAR(1000), knownForTitles VARCHAR(1000),primary key (nconst));"
          mysql -u root "-p$DBPASSWORD" -e "LOAD DATA INFILE '/var/lib/mysql-files/title_ratings.tsv' IGNORE INTO TABLE imdbdata.title_ratings FIELDS TERMINATED BY '\t';"
          mysql -u root "-p$DBPASSWORD" -e "LOAD DATA INFILE '/var/lib/mysql-files/title_basics.tsv'  IGNORE INTO TABLE imdbdata.title_basics FIELDS TERMINATED BY '\t';"
          mysql -u root "-p$DBPASSWORD" -e "LOAD DATA INFILE '/var/lib/mysql-files/title_crew.tsv' IGNORE INTO TABLE imdbdata.title_crew FIELDS TERMINATED BY '\t';"
          mysql -u root "-p$DBPASSWORD" -e "LOAD DATA INFILE '/var/lib/mysql-files/title_principals.tsv' IGNORE INTO TABLE imdbdata.title_principals FIELDS TERMINATED BY '\t';"
          mysql -u root "-p$DBPASSWORD" -e "LOAD DATA INFILE '/var/lib/mysql-files/name_basics.tsv' IGNORE INTO TABLE imdbdata.name_basics FIELDS TERMINATED BY '\t';"
          mysql -u root "-p$DBPASSWORD" -e "LOAD DATA INFILE '/var/lib/mysql-files/title_akas.tsv' IGNORE INTO  TABLE imdbdata.title_akas FIELDS TERMINATED BY '\t';"
      Tags:
        - Key: Name
          Value: MySQL-Instance

Outputs:
  DBServerIP:
    Description: IMDb MySQL Server Private IP
    Value: !GetAtt DbInstance.PrivateIp
  DBMasterUsername:
    Description: IMDb MySQL database user name
    Value: !Ref DBMasterUsername
  NatGateway:
    Description: NatGateway ID
    Value: !GetAtt NatGateway.NatGatewayId
