AWSTemplateFormatVersion: '2010-09-09'
Description: CloudFormation template to create a VPC, subnets, and necessary resources for AWS Glue jobs with PrivateLink to Snowflake.

Parameters:
  PrivateLinkAccountURL:
    Type: String
    Description: PrivateLink account URL (privatelink-account-url).
    Default: ""

  PrivateLinkVpceId:
    Type: String
    Description: PrivateLink VPC endpoint ID (privatelink-vpce-id).
    Default: ""

  PrivateLinkOcspURL:
    Type: String
    Description: PrivateLink OCSP URL (privatelink_ocsp-url).
    Default: ""

  RegionlessSnowsightPrivateLinkURL:
    Type: String
    Description: Regionless Snowsight PrivateLink URL (regionless-snowsight-privatelink-url).
    Default: ""

  VpcCIDR:
    Type: String
    Description: VPC CIDR (e.g. 10.0.0.0/16).
    Default: ""

  PublicSubnet1CIDR:
    Type: String
    Description: Public Subnet 1 CIDR (e.g. 10.0.1.0/24).
    Default: ""

  PrivateSubnet1CIDR:
    Type: String
    Description: Private Subnet 1 CIDR (e.g. 10.0.2.0/24).
    Default: ""

  PrivateSubnet2CIDR:
    Type: String
    Description: Private Subnet 2 CIDR (e.g. 10.0.3.0/24).
    Default: ""

  PrivateSubnet3CIDR:
    Type: String
    Description: Private Subnet 3 CIDR (e.g. 10.0.4.0/24).
    Default: ""

Resources:
  #Glue S3 Bucket
  S3GlueBucket:
    Type: "AWS::S3::Bucket"
    Properties:
      BucketName: 
        Fn::Join:
          - "-"
          - - "blog-glue-snowflake"
            - Ref: "AWS::Region"
            - Ref: "AWS::AccountId"
      BucketEncryption:
        ServerSideEncryptionConfiguration:
          - ServerSideEncryptionByDefault:
              SSEAlgorithm: AES256

  #Glue Database
  GlueDb:
    Type: AWS::Glue::Database
    Properties:
      CatalogId: !Sub "${AWS::AccountId}"
      DatabaseInput: 
        Name: db_blog_glue_snowflake


  #AthenaWorkgroup
  AthenaWorkGroup:
    Type: AWS::Athena::WorkGroup
    Properties:
      Name: blog-workgroup
      State: ENABLED
      WorkGroupConfiguration: 
        ResultConfiguration:
          OutputLocation: !Sub "s3://blog-glue-snowflake-${AWS::Region}-${AWS::AccountId}/athena_results/"

  # VPC
  MyVPC:
    Type: AWS::EC2::VPC
    Properties:
      CidrBlock: !Ref VpcCIDR
      EnableDnsSupport: true
      EnableDnsHostnames: true
      Tags:
        - Key: Name
          Value: vpc-blog-glue-snowflake

  # Subnets
  PublicSubnet1:
    Type: AWS::EC2::Subnet
    Properties:
      VpcId: !Ref MyVPC
      CidrBlock: !Ref PublicSubnet1CIDR
      MapPublicIpOnLaunch: false
      AvailabilityZone: !Select [0, !GetAZs ""]

  PrivateSubnet1:
    Type: AWS::EC2::Subnet
    Properties:
      VpcId: !Ref MyVPC
      CidrBlock: !Ref PrivateSubnet1CIDR
      MapPublicIpOnLaunch: false
      AvailabilityZone: !Select [0, !GetAZs ""]

  PrivateSubnet2:
    Type: AWS::EC2::Subnet
    Properties:
      VpcId: !Ref MyVPC
      CidrBlock: !Ref PrivateSubnet2CIDR
      MapPublicIpOnLaunch: false
      AvailabilityZone: !Select [1, !GetAZs ""]

  PrivateSubnet3:
    Type: AWS::EC2::Subnet
    Properties:
      VpcId: !Ref MyVPC
      CidrBlock: !Ref PrivateSubnet3CIDR
      MapPublicIpOnLaunch: false
      AvailabilityZone: !Select [2, !GetAZs ""]

  # Route Tables
  PublicRouteTable:
    Type: AWS::EC2::RouteTable
    Properties:
      VpcId: !Ref MyVPC

  PrivateRouteTable1:
    Type: AWS::EC2::RouteTable
    Properties:
      VpcId: !Ref MyVPC

  PrivateRouteTable2:
    Type: AWS::EC2::RouteTable
    Properties:
      VpcId: !Ref MyVPC

  PrivateRouteTable3:
    Type: AWS::EC2::RouteTable
    Properties:
      VpcId: !Ref MyVPC

  # Route Table Associations
  PublicRouteTableAssociation:
    Type: AWS::EC2::SubnetRouteTableAssociation
    Properties:
      SubnetId: !Ref PublicSubnet1
      RouteTableId: !Ref PublicRouteTable

  PrivateRouteTableAssociation1:
    Type: AWS::EC2::SubnetRouteTableAssociation
    Properties:
      SubnetId: !Ref PrivateSubnet1
      RouteTableId: !Ref PrivateRouteTable1

  PrivateRouteTableAssociation2:
    Type: AWS::EC2::SubnetRouteTableAssociation
    Properties:
      SubnetId: !Ref PrivateSubnet2
      RouteTableId: !Ref PrivateRouteTable2

  PrivateRouteTableAssociation3:
    Type: AWS::EC2::SubnetRouteTableAssociation
    Properties:
      SubnetId: !Ref PrivateSubnet3
      RouteTableId: !Ref PrivateRouteTable3

  # Security Groups
  GlueSecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      VpcId: !Ref MyVPC
      GroupDescription: Security group for AWS Glue network traffic.

  GlueSecurityGroupIngress:
    Type: AWS::EC2::SecurityGroupIngress
    DependsOn: GlueSecurityGroup
    Properties:
      Description: Glue Security Group Inbound traffic
      GroupId: !Ref GlueSecurityGroup
      IpProtocol: tcp
      FromPort: 0
      ToPort: 65535
      SourceSecurityGroupId: !Ref GlueSecurityGroup

  GlueSecurityGroupEgress:
    Type: AWS::EC2::SecurityGroupEgress
    DependsOn: GlueSecurityGroup
    Properties:
      Description: Glue Security Group Outbound traffic
      GroupId: !Ref GlueSecurityGroup
      IpProtocol: tcp
      FromPort: 0
      ToPort: 65535
      CidrIp: 0.0.0.0/0

  SecretsManagerSecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      VpcId: !Ref MyVPC
      GroupDescription: Security group for AWS Secrets Manager network traffic
      SecurityGroupIngress:
        - IpProtocol: tcp
          FromPort: 443
          ToPort: 443
          SourceSecurityGroupId: !Ref GlueSecurityGroup
          Description: Secrets Manager Group Inbound traffic
      SecurityGroupEgress:
        - IpProtocol: tcp
          FromPort: 443
          ToPort: 443
          CidrIp: 0.0.0.0/0
          Description: Secrets Manager Group Outbound traffic

  SnowflakeSecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      VpcId: !Ref MyVPC
      GroupDescription: Security group for Snowflake network traffic
      SecurityGroupIngress:
        - IpProtocol: tcp
          FromPort: 443
          ToPort: 443
          SourceSecurityGroupId: !Ref GlueSecurityGroup
          Description: Snowflake Group Inbound traffic
      SecurityGroupEgress:
        - IpProtocol: tcp
          FromPort: 443
          ToPort: 443
          CidrIp: 0.0.0.0/0
          Description: Snowflake Group Outbound traffic

  # VPC Endpoints
  S3GatewayEndpoint:
    Type: AWS::EC2::VPCEndpoint
    Properties:
      VpcId: !Ref MyVPC
      ServiceName: !Sub "com.amazonaws.${AWS::Region}.s3"
      VpcEndpointType: Gateway
      RouteTableIds:
        - !Ref PublicRouteTable
        - !Ref PrivateRouteTable1
        - !Ref PrivateRouteTable2
        - !Ref PrivateRouteTable3

  SecretsManagerEndpoint:
    Type: AWS::EC2::VPCEndpoint
    Properties:
      VpcId: !Ref MyVPC
      PrivateDnsEnabled: true
      ServiceName: !Sub "com.amazonaws.${AWS::Region}.secretsmanager"
      VpcEndpointType: Interface
      SubnetIds:
        - !Ref PrivateSubnet1
        - !Ref PrivateSubnet2
        - !Ref PrivateSubnet3
      SecurityGroupIds:
        - !Ref SecretsManagerSecurityGroup

  SnowflakeEndpoint:
    Type: AWS::EC2::VPCEndpoint
    Properties:
      VpcId: !Ref MyVPC
      ServiceName: !Ref PrivateLinkVpceId
      VpcEndpointType: Interface
      SubnetIds:
        - !Ref PrivateSubnet1
        - !Ref PrivateSubnet2
        - !Ref PrivateSubnet3
      SecurityGroupIds:
        - !Ref SnowflakeSecurityGroup

  # Route 53 Hosted Zone
  SnowflakeHostedZone:
    Type: AWS::Route53::HostedZone
    Properties:
      Name: privatelink.snowflakecomputing.com
      VPCs:
        - VPCId: !Ref MyVPC
          VPCRegion: !Ref AWS::Region
      HostedZoneConfig:
        Comment: Private hosted zone for Snowflake PrivateLink

  # Route 53 Records

  SnowflakePrivateLinkAccountURLRecord:
    Type: AWS::Route53::RecordSet
    DependsOn: SnowflakeEndpoint
    Properties:
      HostedZoneId: !Ref SnowflakeHostedZone
      Name: !Ref PrivateLinkAccountURL
      Type: CNAME
      TTL: 300
      ResourceRecords:
        - !Select ['1', !Split [':', !Select ['0', !GetAtt SnowflakeEndpoint.DnsEntries]]]

  SnowflakePrivateLinkOcspURLRecord:
    Type: AWS::Route53::RecordSet
    Properties:
      HostedZoneId: !Ref SnowflakeHostedZone
      Name: !Ref PrivateLinkOcspURL
      Type: CNAME
      TTL: 300
      ResourceRecords:
        - !Select ['1', !Split [':', !Select ['0', !GetAtt SnowflakeEndpoint.DnsEntries]]]

  SnowflakeRegionlessSnowsightPrivateLinkURLRecord:
    Type: AWS::Route53::RecordSet
    Properties:
      HostedZoneId: !Ref SnowflakeHostedZone
      Name: !Ref RegionlessSnowsightPrivateLinkURL
      Type: CNAME
      TTL: 300
      ResourceRecords:
        - !Select ['1', !Split [':', !Select ['0', !GetAtt SnowflakeEndpoint.DnsEntries]]]

  # Secrets Manager Secret for Snowflake Credentials
  SnowflakeSecret:
    Type: AWS::SecretsManager::Secret
    Properties:
      Name: blog-glue-snowflake-credentials
      Description: Snowflake credentials for Glue connection
      SecretString: !Sub |
        {
          "sfUser": "-",
          "sfPassword": "-",
          "sfWarehouse": "-"
        }

  # IAM Role for AWS Glue
  GlueServiceRole:
    Type: AWS::IAM::Role
    Properties:
      AssumeRolePolicyDocument:
        Version: "2012-10-17"
        Statement:
          - Effect: "Allow"
            Principal:
              Service: "glue.amazonaws.com"
            Action: "sts:AssumeRole"
      Path: "/"
      ManagedPolicyArns:
        - arn:aws:iam::aws:policy/service-role/AWSGlueServiceRole
      Policies:
        - PolicyName: "GlueS3Access"
          PolicyDocument:
            Version: "2012-10-17"
            Statement:
              - Effect: "Allow"
                Action:
                  - "s3:GetObject"
                  - "s3:PutObject"
                  - "s3:ListBucket"
                Resource: !Sub "arn:aws:s3:::blog-glue-snowflake-${AWS::Region}-${AWS::AccountId}/*"
        - PolicyName: "SecretsManagerAccess"
          PolicyDocument:
            Version: "2012-10-17"
            Statement:
              - Effect: "Allow"
                Action:
                  - "secretsmanager:GetSecretValue"
                Resource: !Ref SnowflakeSecret

Outputs:
  VPCId:
    Description: The ID of the VPC
    Value: !Ref MyVPC
  PublicSubnet1Id:
    Description: The ID of the public subnet
    Value: !Ref PublicSubnet1
  PrivateSubnet1Id:
    Description: The ID of the private subnet
    Value: !Ref PrivateSubnet1
  PrivateSubnet2Id:
    Description: The ID of the private subnet
    Value: !Ref PrivateSubnet2
  PrivateSubnet3Id:
    Description: The ID of the private subnet
    Value: !Ref PrivateSubnet3
  GlueSecurityGroupId:
    Description: The ID of the Glue security group
    Value: !Ref GlueSecurityGroup
  SecretsManagerSecurityGroupId:
    Description: The ID of the Secrets Manager security group
    Value: !Ref SecretsManagerSecurityGroup
  SnowflakeSecurityGroupId:
    Description: The ID of the Snowflake security group
    Value: !Ref SnowflakeSecurityGroup
  S3GatewayEndpointId:
    Description: The ID of the S3 VPC endpoint
    Value: !Ref S3GatewayEndpoint
  SecretsManagerEndpointId:
    Description: The ID of the Secrets Manager VPC endpoint
    Value: !Ref SecretsManagerEndpoint
  SnowflakeEndpointId:
    Description: The ID of the Snowflake VPC endpoint
    Value: !Ref SnowflakeEndpoint
  GlueServiceRoleArn:
    Description: The ARN of the Glue service role
    Value: !GetAtt GlueServiceRole.Arn
  S3GlueBucket:
    Description: The S3 bucket for AWS Glue
    Value: !Ref S3GlueBucket
  SecretsManagerSnowflakeSecret:
    Description: The Secret Manager secret for Snowflake
    Value: !Ref SnowflakeSecret
