AWSTemplateFormatVersion: 2010-09-09

Parameters:
  vpccidr:
    Type: String
    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
    AllowedPattern: ^(([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])\.){3}([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])(\/([0-9]|[1-2][0-9]|3[0-2]))$
  DBSubnetCIDR:
    Type: String
    MinLength: 9
    MaxLength: 18
    ConstraintDescription: Must be a valid CIDR range in the form x.x.x.x/22
    Default: 10.0.1.0/24
    AllowedPattern: ^(([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])\.){3}([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])(\/([0-9]|[1-2][0-9]|3[0-2]))$
  DbMasterUsername:
    Description: The datbase master user name
    Type: String
    Default: dbuser
  DbMasterPassword:
    Description: The database master password
    Type: String
    Default: Password@123
  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, r5.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.
  DBSSHLocation:
    Description: The IP address range that can be used to SSH to the EC2 instances
    Type: String
    MinLength: 9
    MaxLength: 18
    Default: 0.0.0.0/0
    AllowedPattern: ^(([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])\.){3}([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])(\/([0-9]|[1-2][0-9]|3[0-2]))$
    ConstraintDescription: must be a valid IP CIDR range of the form x.x.x.x/x.
  DBLatestAmiId:
    Type:  'AWS::SSM::Parameter::Value<AWS::EC2::Image::Id>'
    Default: '/aws/service/ami-amazon-linux-latest/amzn2-ami-hvm-x86_64-gp2'
Resources:
  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
  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
  DbSubnet:
    Type: AWS::EC2::Subnet
    Properties:
      CidrBlock: !Ref DBSubnetCIDR
      AvailabilityZone:
        Fn::Select:
        - 0
        - Fn::GetAZs: !Ref 'AWS::Region'
      MapPublicIpOnLaunch: true
      VpcId: !Ref Vpc
      Tags:
        - Key: Name
          Value: DB-PUB01
  DbSubnetRouteTableAssociation:
    Type: AWS::EC2::SubnetRouteTableAssociation
    Properties:
      RouteTableId: !Ref PublicRouteTable
      SubnetId: !Ref DbSubnet
  DbSecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupDescription: MySQL security group
      VpcId: !Ref Vpc
      SecurityGroupIngress:
        - CidrIp: !Ref DBSSHLocation
          IpProtocol: tcp
          FromPort: 3306
          ToPort: 3306
        - CidrIp: !Ref DBSSHLocation
          IpProtocol: tcp
          FromPort: 22
          ToPort: 22
      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: /
      ManagedPolicyArns:
        - arn:aws:iam::aws:policy/AmazonS3FullAccess
        - arn:aws:iam::aws:policy/AmazonSSMManagedInstanceCore
  DbInstance:
    Type: AWS::EC2::Instance
    Properties:
      ImageId: !Ref DBLatestAmiId
      InstanceType: !Ref DBInstanceType
      SecurityGroupIds:
        - !GetAtt DbSecurityGroup.GroupId
      SubnetId: !Ref DbSubnet
      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-2022
          rpm -Uvh https://repo.mysql.com/mysql80-community-release-el7-3.noarch.rpm
          yum install -y mysql-community-server
          systemctl enable mysqld
          systemctl start mysqld
          export DbMasterPassword=Password@123
          export DbMasterUsername=dbuser
          mysql -u root "-p$(grep -oP '(?<=root@localhost\: )\S+' /var/log/mysqld.log)" -e "ALTER USER 'root'@'localhost' IDENTIFIED BY '${DbMasterPassword}'" --connect-expired-password
          mysql -u root "-p${DbMasterPassword}" -e "CREATE USER '${DbMasterUsername}' IDENTIFIED BY '${DbMasterPassword}'"
          mysql -u root "-p${DbMasterPassword}" -e "GRANT ALL PRIVILEGES ON *.* TO '${DbMasterUsername}'"
          mysql -u root "-p${DbMasterPassword}" -e "FLUSH PRIVILEGES"
          cd /var/lib/mysql-files/
          #wget https://datasets.imdbws.com/name.basics.tsv.gz
          #wget https://datasets.imdbws.com/title.akas.tsv.gz
          #wget https://datasets.imdbws.com/title.basics.tsv.gz
          #wget https://datasets.imdbws.com/title.crew.tsv.gz
          #wget https://datasets.imdbws.com/title.episode.tsv.gz
          #wget https://datasets.imdbws.com/title.principals.tsv.gz
          #wget https://datasets.imdbws.com/title.ratings.tsv.gz
          #gzip -d *.gz
          curl -O https://www.amazondynamodblabs.com/static/rdbms-migration/rdbms-migration.zip
          unzip -q rdbms-migration.zip
          chmod 775 *.*
          mysql -u root "-p${DbMasterPassword}" -e "CREATE DATABASE imdb;"
          mysql -u root "-p${DbMasterPassword}" -e "CREATE TABLE imdb.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${DbMasterPassword}" -e "CREATE TABLE imdb.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${DbMasterPassword}" -e "CREATE TABLE imdb.title_crew (tconst  VARCHAR(200), directors  VARCHAR(1000),writers  VARCHAR(1000),primary key (tconst));"
          mysql -u root "-p${DbMasterPassword}" -e "CREATE TABLE imdb.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${DbMasterPassword}" -e "CREATE TABLE imdb.title_ratings (tconst  VARCHAR(200), averageRating float,numVotes  integer,primary key (tconst));"
          mysql -u root "-p${DbMasterPassword}" -e "CREATE TABLE imdb.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${DbMasterPassword}" -e "LOAD DATA INFILE '/var/lib/mysql-files/title_ratings.tsv' IGNORE INTO TABLE imdb.title_ratings FIELDS TERMINATED BY '\t';"
          mysql -u root "-p${DbMasterPassword}" -e "LOAD DATA INFILE '/var/lib/mysql-files/title_basics.tsv'  IGNORE INTO TABLE imdb.title_basics FIELDS TERMINATED BY '\t';"
          mysql -u root "-p${DbMasterPassword}" -e "LOAD DATA INFILE '/var/lib/mysql-files/title_crew.tsv' IGNORE INTO TABLE imdb.title_crew FIELDS TERMINATED BY '\t';"
          mysql -u root "-p${DbMasterPassword}" -e "LOAD DATA INFILE '/var/lib/mysql-files/title_principals.tsv' IGNORE INTO TABLE imdb.title_principals FIELDS TERMINATED BY '\t';"
          mysql -u root "-p${DbMasterPassword}" -e "LOAD DATA INFILE '/var/lib/mysql-files/name_basics.tsv' IGNORE INTO TABLE imdb.name_basics FIELDS TERMINATED BY '\t';"
          mysql -u root "-p${DbMasterPassword}" -e "LOAD DATA INFILE '/var/lib/mysql-files/title_akas.tsv' IGNORE INTO  TABLE imdb.title_akas FIELDS TERMINATED BY '\t';"
      Tags:
        - Key: Name
          Value: MySQL-Instance

Outputs:
  DbPublicIP:
    Description: Public IP address of the database EC2 instance
    Value: !GetAtt DbInstance.PublicIp