AWSTemplateFormatVersion: "2010-09-09"
Metadata:
    Generator: "former2"
Description: ""
Resources:
    EC2RouteTable:
        Type: "AWS::EC2::RouteTable"
        Properties:
            VpcId: !Ref EC2VPC
            Tags: 
              - 
                Key: "Name"
                Value: !Sub "adxdemo-rtb-private2-${EC2Subnet3.AvailabilityZone}"

    EC2RouteTable2:
        Type: "AWS::EC2::RouteTable"
        Properties:
            VpcId: !Ref EC2VPC
            Tags: 
              - 
                Key: "Name"
                Value: !Sub "adxdemo-rtb-private1-${EC2Subnet.AvailabilityZone}"

    EC2RouteTable3:
        Type: "AWS::EC2::RouteTable"
        Properties:
            VpcId: !Ref EC2VPC
            Tags: 
              - 
                Key: "Name"
                Value: !Sub "adxdemo-rtb-private3-${EC2Subnet2.AvailabilityZone}"

    IAMManagedPolicy:
        Type: "AWS::IAM::ManagedPolicy"
        Properties:
            ManagedPolicyName: "AmazonRedshift-CommandsAccessPolicy-20240109T104250"
            Path: "/service-role/"
            PolicyDocument: |
                {
                    "Version": "2012-10-17",
                    "Statement": [
                        {
                            "Action": [
                                "s3:GetObject",
                                "s3:GetBucketAcl",
                                "s3:GetBucketCors",
                                "s3:GetEncryptionConfiguration",
                                "s3:GetBucketLocation",
                                "s3:ListBucket",
                                "s3:ListAllMyBuckets",
                                "s3:ListMultipartUploadParts",
                                "s3:ListBucketMultipartUploads",
                                "s3:PutObject",
                                "s3:PutBucketAcl",
                                "s3:PutBucketCors",
                                "s3:DeleteObject",
                                "s3:AbortMultipartUpload",
                                "s3:CreateBucket"
                            ],
                            "Effect": "Allow",
                            "Resource": "arn:aws:s3:::*"
                        }
                    ]
                }

    RedshiftServerlessNamespace:
        Type: "AWS::RedshiftServerless::Namespace"
        Properties:
            AdminUsername: "admin"
            AdminUserPassword: "D2EAdmin!"
            DbName: "dev"
            IamRoles: 
              - !Sub "arn:aws:iam::${AWS::AccountId}:role/${IAMRole3}"
              - !Sub "arn:aws:iam::${AWS::AccountId}:role/service-role/${IAMRole}"
            NamespaceName: "adx-demo"

    RedshiftServerlessWorkgroup:
        Type: "AWS::RedshiftServerless::Workgroup"
        Properties:
            BaseCapacity: 64
            ConfigParameters: 
              - 
                ParameterKey: "auto_mv"
                ParameterValue: "true"
              - 
                ParameterKey: "datestyle"
                ParameterValue: "ISO, MDY"
              - 
                ParameterKey: "enable_case_sensitive_identifier"
                ParameterValue: "false"
              - 
                ParameterKey: "enable_user_activity_logging"
                ParameterValue: "true"
              - 
                ParameterKey: "query_group"
                ParameterValue: "default"
              - 
                ParameterKey: "require_ssl"
                ParameterValue: "false"
              - 
                ParameterKey: "search_path"
                ParameterValue: "$user, public"
              - 
                ParameterKey: "use_fips_ssl"
                ParameterValue: "false"
              - 
                ParameterKey: "max_query_execution_time"
                ParameterValue: "14400"
            EnhancedVpcRouting: false
            NamespaceName: !Ref RedshiftServerlessNamespace
            PubliclyAccessible: false
            SecurityGroupIds: 
              - !Ref EC2SecurityGroup2
            SubnetIds: 
              - !Ref EC2Subnet
              - !Ref EC2Subnet2
              - !Ref EC2Subnet3
            WorkgroupName: "sandbox"

    EC2VPC:
        Type: "AWS::EC2::VPC"
        Properties:
            CidrBlock: "10.0.0.0/16"
            EnableDnsSupport: true
            EnableDnsHostnames: true
            InstanceTenancy: "default"
            Tags: 
              - 
                Key: "Name"
                Value: "adxdemo-vpc"

    EC2VPCEndpoint:
        Type: "AWS::EC2::VPCEndpoint"
        Properties:
            VpcEndpointType: "Gateway"
            VpcId: !Ref EC2VPC
            ServiceName: !Sub "com.amazonaws.${AWS::Region}.s3"
            PolicyDocument: "{\"Version\":\"2008-10-17\",\"Statement\":[{\"Effect\":\"Allow\",\"Principal\":\"*\",\"Action\":\"*\",\"Resource\":\"*\"}]}"
            RouteTableIds: 
              - !Ref EC2RouteTable3
              - !Ref EC2RouteTable2
              - !Ref EC2RouteTable
            PrivateDnsEnabled: false

    EC2SecurityGroup2:
        Type: "AWS::EC2::SecurityGroup"
        Properties:
            GroupDescription: "redshift-security-group"
            GroupName: "redshift-security-group"
            VpcId: !Ref EC2VPC

    EC2SecurityGroup3:
        Type: "AWS::EC2::SecurityGroup"
        Properties:
            GroupDescription: "quicksight-security-group"
            GroupName: "quicksight-security-group"
            VpcId: !Ref EC2VPC

    EC2SecurityGroup2Ingress:
        Type: "AWS::EC2::SecurityGroupIngress"
        Properties:
            GroupId: !Ref EC2SecurityGroup2
            SourceSecurityGroupId:
              Fn::GetAtt:
                - EC2SecurityGroup3
                - GroupId
            FromPort: 5439
            IpProtocol: "tcp"
            ToPort: 5439

    EC2SecurityGroup3Ingress:
        Type: "AWS::EC2::SecurityGroupIngress"
        Properties:
            GroupId: !Ref EC2SecurityGroup3
            SourceSecurityGroupId: 
              Fn::GetAtt:
                - EC2SecurityGroup2
                - GroupId
            FromPort: -1
            IpProtocol: -1
            ToPort: -1

    EC2Subnet:
        Type: "AWS::EC2::Subnet"
        Properties:
            AvailabilityZone: !Sub "${AWS::Region}a"
            CidrBlock: "10.0.128.0/20"
            VpcId: !Ref EC2VPC
            MapPublicIpOnLaunch: false
            Tags: 
              - 
                Key: "Name"
                Value: !Sub "adxdemo-subnet-private1-${AWS::Region}a"

    EC2Subnet2:
        Type: "AWS::EC2::Subnet"
        Properties:
            AvailabilityZone: !Sub "${AWS::Region}c"
            CidrBlock: "10.0.160.0/20"
            VpcId: !Ref EC2VPC
            MapPublicIpOnLaunch: false
            Tags: 
              - 
                Key: "Name"
                Value: !Sub "adxdemo-subnet-private3-${AWS::Region}c"

    EC2Subnet3:
        Type: "AWS::EC2::Subnet"
        Properties:
            AvailabilityZone: !Sub "${AWS::Region}b"
            CidrBlock: "10.0.144.0/20"
            VpcId: !Ref EC2VPC
            MapPublicIpOnLaunch: false
            Tags: 
              - 
                Key: "Name"
                Value: !Sub "adxdemo-subnet-private2-${AWS::Region}b"

    EC2SubnetNetworkAclAssociation:
        Type: "AWS::EC2::SubnetNetworkAclAssociation"
        Properties:
            SubnetId: !Ref EC2Subnet
            NetworkAclId: !Ref EC2NetworkAcl

    EC2SubnetNetworkAclAssociation2:
        Type: "AWS::EC2::SubnetNetworkAclAssociation"
        Properties:
            SubnetId: !Ref EC2Subnet3
            NetworkAclId: !Ref EC2NetworkAcl

    EC2SubnetNetworkAclAssociation3:
        Type: "AWS::EC2::SubnetNetworkAclAssociation"
        Properties:
            SubnetId: !Ref EC2Subnet2
            NetworkAclId: !Ref EC2NetworkAcl

    IAMRole:
        Type: "AWS::IAM::Role"
        Properties:
            Path: "/service-role/"
            RoleName: "AmazonRedshift-CommandsAccessRole-20240109T104250"
            AssumeRolePolicyDocument: "{\"Version\":\"2012-10-17\",\"Statement\":[{\"Effect\":\"Allow\",\"Principal\":{\"Service\":[\"sagemaker.amazonaws.com\",\"redshift-serverless.amazonaws.com\",\"redshift.amazonaws.com\"]},\"Action\":\"sts:AssumeRole\"}]}"
            MaxSessionDuration: 3600
            ManagedPolicyArns: 
              - !Ref IAMManagedPolicy
              - "arn:aws:iam::aws:policy/AmazonRedshiftAllCommandsFullAccess"
              - "arn:aws:iam::aws:policy/AWSDataExchangeSubscriberFullAccess"

    IAMRole2:
        Type: "AWS::IAM::Role"
        Properties:
            Path: "/"
            RoleName: "LambdaBasicExecutionRole"
            AssumeRolePolicyDocument: "{\"Version\":\"2012-10-17\",\"Statement\":[{\"Effect\":\"Allow\",\"Principal\":{\"Service\":\"lambda.amazonaws.com\"},\"Action\":\"sts:AssumeRole\"}]}"
            MaxSessionDuration: 3600
            ManagedPolicyArns: 
              - "arn:aws:iam::aws:policy/service-role/AWSLambdaBasicExecutionRole"
            Description: "Allows Lambda functions to call AWS services on your behalf."

    IAMRole3:
        Type: "AWS::IAM::Role"
        Properties:
            Path: "/"
            RoleName: "Redshift-Lambda-role"
            AssumeRolePolicyDocument: "{\"Version\":\"2012-10-17\",\"Statement\":[{\"Effect\":\"Allow\",\"Principal\":{\"Service\":\"redshift.amazonaws.com\"},\"Action\":\"sts:AssumeRole\"}]}"
            MaxSessionDuration: 3600
            Description: "Allows Redshift clusters to call AWS services on your behalf."

    IAMPolicy:
        Type: "AWS::IAM::Policy"
        Properties:
            PolicyDocument: !Sub |
                {
                    "Version": "2012-10-17",
                    "Statement": [
                        {
                            "Action": [
                                "geo:SearchPlaceIndexForText"
                            ],
                            "Resource": "arn:aws:geo:${AWS::Region}:${AWS::AccountId}:place-index/placeindex.redshift",
                            "Effect": "Allow"
                        }
                    ]
                }
            Roles: 
              - !Ref IAMRole2
            PolicyName: "place-index-policy"

    IAMPolicy2:
        Type: "AWS::IAM::Policy"
        Properties:
            PolicyDocument: !Sub |
                {
                	"Version": "2012-10-17",
                	"Statement": [
                		{
                			"Effect": "Allow",
                			"Action": "lambda:InvokeFunction",
                			"Resource": [
                				"arn:aws:lambda:${AWS::Region}:${AWS::AccountId}:function:${LambdaFunction}"
                			]
                		}
                	]
                }
            Roles: 
              - !Ref IAMRole3
            PolicyName: "redshift-execute-lambda-udf"

    LambdaFunction:
        Type: "AWS::Lambda::Function"
        Properties:
            Description: ""
            Environment: 
                Variables: 
                    PLACE_INDEX: "placeindex.redshift"
            FunctionName: "GeocodeAddresses"
            Handler: "lambda_function.lambda_handler"
            Architectures: 
              - "x86_64"
            Code: 
                S3Bucket: "d2e-demos-published"
                S3Key: adx-demo/GeocodeAddresses-b2b787e7-6b28-48bf-a982-ef3ce4715028.zip
                S3ObjectVersion: "foY76gW5tiWfLKl2J2r53lWlcbvlbqje"
            MemorySize: 128
            Role: !GetAtt IAMRole2.Arn
            Runtime: "python3.10"
            Timeout: 3
            TracingConfig: 
                Mode: "PassThrough"
            EphemeralStorage: 
                Size: 512

    LocationPlaceIndex:
        Type: "AWS::Location::PlaceIndex"
        Properties:
            IndexName: "placeindex.redshift"
            Description: ""
            PricingPlan: "RequestBasedUsage"
            DataSource: "Esri"
            DataSourceConfiguration: 
                IntendedUse: "Storage"

    EC2NetworkAcl:
        Type: "AWS::EC2::NetworkAcl"
        Properties:
            VpcId: !Ref EC2VPC

    #IAMRoleQuicksight:
    #    Type: "AWS::IAM::Role"
    #    Properties:
    #        Path: "/service-role/"
    #        RoleName: "Quicksight-DataAccess-Role"
    #        AssumeRolePolicyDocument: "{\"Version\":\"2012-10-17\",\"Statement\":[{\"Effect\":\"Allow\",\"Principal\":{\"Service\":[\"sagemaker.amazonaws.com\",\"redshift-serverless.amazonaws.com\",\"redshift.amazonaws.com\"]},\"Action\":\"sts:AssumeRole\"}]}"
    #        MaxSessionDuration: 3600
    #        ManagedPolicyArns:
    #          - !Sub "arn:aws:iam::${AWS::AccountId}:policy/service-role/AWSQuickSightIAMPolicy"
    #          - !Sub "arn:aws:iam::${AWS::AccountId}:policy/service-role/AWSQuickSightRedshiftPolicy"

    QuicksightIAMPolicy:
        Type: "AWS::IAM::Policy"
        Properties:
            PolicyDocument:
                {
                    "Version": "2012-10-17",
                    "Statement": [
                        {
                            "Action": [
                                "ec2:CreateNetworkInterface",
                                "ec2:ModifyNetworkInterfaceAttribute",
                                "ec2:DeleteNetworkInterface",
                                "ec2:DescribeSubnets",
                                "ec2:DescribeSecurityGroups"
                            ],
                            "Resource": "*",
                            "Effect": "Allow"
                        }
                    ]
                }
            Roles: 
              - "aws-quicksight-service-role-v0"
              #- !GetAtt IAMRoleQuicksight.RoleName
            PolicyName: "quicksight-vpn-connection"

    QuicksightVPCConnection:     
        Type: "AWS::QuickSight::VPCConnection"
        Properties:
            Name: "adx-demo-vpc-connection"
            VPCConnectionId: !Join
              - "_"
              - - "adx_VPCConnection"
                - !Ref QuicksightIAMPolicy
            AwsAccountId: !Sub "${AWS::AccountId}"
            RoleArn: !Sub "arn:aws:iam::${AWS::AccountId}:role/service-role/aws-quicksight-service-role-v0"
            SecurityGroupIds: 
              - !Ref EC2SecurityGroup3
            SubnetIds:
              - !Ref EC2Subnet
              - !Ref EC2Subnet2
              - !Ref EC2Subnet3