from pystackql import StackQL
stackql = StackQL()
stackql.executeStmt("REGISTRY PULL awscc")

def ensure_one_or_zero(resource_list, resource_name, region):
    if len(resource_list) > 1:
        raise RuntimeError(f"ah snap! multiple default {resource_name} resources found in {region}")
    elif len(resource_list) == 0:
        print(f"/* no default {resource_name} found in {region} */\n")
        return False
    return True

def get_resource(region, resource, identifier):
    # fetch the full properties of a single Cloud Control resource by its identifier
    rows = stackql.execute(f"""
        SELECT *
        FROM awscc.ec2.{resource}
        WHERE region = '{region}'
        AND Identifier = '{identifier}'
    """)
    return rows[0] if len(rows) else None

def find_in_vpc(region, resource, id_col, vpc_id):
    # list all identifiers for a resource in a region, then keep those attached to the target VPC
    matches = []
    for row in stackql.execute(f"SELECT {id_col} FROM awscc.ec2.{resource}_list_only WHERE region = '{region}'"):
        detail = get_resource(region, resource, row[id_col])
        if detail and detail.get('vpc_id') == vpc_id:
            matches.append(detail)
    return matches

regions = [
    'us-east-1', 'us-east-2', 'us-west-1', 'us-west-2',
    'eu-central-1', 'eu-central-2', 'eu-west-1', 'eu-west-2', 'eu-west-3', 'eu-north-1', 'eu-south-1',
    'ap-east-1', 'ap-south-1', 'ap-south-2', 'ap-northeast-1', 'ap-northeast-2', 'ap-northeast-3',
    'ap-southeast-1', 'ap-southeast-2', 'ap-southeast-3', 'ap-southeast-4', 'af-south-1',
    'ca-central-1',  'me-south-1', 'me-central-1', 'sa-east-1'
]

for region in regions:
    # find the default VPC (CIDR 172.31.0.0/16, untagged) by listing VPCs then inspecting each
    default_vpcs = []
    for row in stackql.execute(f"SELECT vpc_id FROM awscc.ec2.vpcs_list_only WHERE region = '{region}'"):
        vpc = get_resource(region, 'vpcs', row['vpc_id'])
        if vpc and vpc.get('cidr_block') == '172.31.0.0/16' and not vpc.get('tags'):
            default_vpcs.append(vpc)
    if not ensure_one_or_zero(default_vpcs, 'VPC', region): continue
    vpc_id = default_vpcs[0]['vpc_id']

    # check if the VPC is in use
    network_interfaces = find_in_vpc(region, 'network_interfaces', 'id', vpc_id)
    if len(network_interfaces) > 0:
        print(f"/* skipping deletion of default VPC ({vpc_id}) in {region} because it is in use */\n")
        continue

    print(f"/* deleting resources for default VPC ({vpc_id}) in {region} */\n")

    # get the default route table for the VPC
    route_tables = find_in_vpc(region, 'route_tables', 'route_table_id', vpc_id)
    ensure_one_or_zero(route_tables, 'route table', region)
    route_table_id = route_tables[0]['route_table_id']

    # get the internet gateway from the default (0.0.0.0/0) route
    default_route = get_resource(region, 'routes', f'{route_table_id}|0.0.0.0/0')
    inet_gateway_id = default_route['gateway_id'] if default_route else None

    # delete the default route
    print(f"/* deleting default VPC routes in route table ({route_table_id}) in {region} */")
    print(f"""
DELETE FROM awscc.ec2.routes
WHERE data__Identifier = '{route_table_id}|0.0.0.0/0'
AND region = '{region}';
    """)

    # detach the internet gateway
    print(f"/* detaching default VPC internet gateway ({inet_gateway_id}) in {region} */")
    print(f"""
DELETE FROM awscc.ec2.vpc_gateway_attachments
WHERE data__Identifier = 'IGW|{vpc_id}'
AND region = '{region}';
    """)

    # delete the internet gateway
    print(f"/* deleting default VPC internet gateway ({inet_gateway_id}) in {region} */")
    print(f"""
DELETE FROM awscc.ec2.internet_gateways
WHERE data__Identifier = '{inet_gateway_id}'
AND region = '{region}';
    """)

    # delete the network ACL
    nacls = find_in_vpc(region, 'network_acls', 'id', vpc_id)
    ensure_one_or_zero(nacls, 'network acl', region)
    nacl_id = nacls[0]['id']
    print(f"/* deleting default VPC NACL ({nacl_id}) in {region} */")
    print(f"""
DELETE FROM awscc.ec2.network_acls
WHERE data__Identifier = '{nacl_id}'
AND region = '{region}';
    """)

    # delete the subnets
    subnets = find_in_vpc(region, 'subnets', 'subnet_id', vpc_id)
    for subnet in subnets:
        print(f"/* deleting default VPC subnet ({subnet['subnet_id']}) in {region} */")
        print(f"""
DELETE FROM awscc.ec2.subnets
WHERE data__Identifier = '{subnet['subnet_id']}'
AND region = '{region}';
        """)

    # delete the VPC
    print(f"/* deleting default VPC ({vpc_id}) in {region} */")
    print(f"""
DELETE FROM awscc.ec2.vpcs
WHERE data__Identifier = '{vpc_id}'
AND region = '{region}';
    """)