#!/usr/bin/env python3
import uuid
import mysql.connector

MYSQL_PASS = 'XXXXXXXXX'

cnx = mysql.connector.connect(password=MYSQL_PASS, user='root', database='cloud')

cursor = cnx.cursor()

cursor.execute("SELECT id FROM security_group")

security_groups = []
for row in cursor.fetchall():
    security_groups.append(row[0])

cursor.close()

for security_group_id in security_groups:
    add_rule_query = "INSERT INTO security_group_rule (security_group_id, uuid, type, start_port, end_port, protocol, allowed_ip_cidr) VALUES (%s, %s, 'ingress', %s, %s, %s, '::/0')"
    protocol_query = "SELECT id FROM security_group_rule WHERE start_port = 0 AND end_port = 65535 AND protocol = %s AND allowed_ip_cidr = '::/0' AND type = 'ingress' AND security_group_id = %s"
    for protocol in ['tcp', 'udp']:
        cursor = cnx.cursor()
        cursor.execute(protocol_query, (protocol, security_group_id))

        if len(cursor.fetchall()) == 0:
            rule_uuid = str(uuid.uuid4())
            cursor.execute(add_rule_query, (security_group_id, rule_uuid, 0, 65535, protocol))

        cursor.close()

    icmp_query = "SELECT id FROM security_group_rule WHERE start_port = 128 AND end_port = 0 AND protocol = 'icmp' AND allowed_ip_cidr = '::/0' AND type = 'ingress' AND security_group_id = %s"
    cursor = cnx.cursor()
    cursor.execute(icmp_query, (security_group_id,))
    if len(cursor.fetchall()) == 0:
        rule_uuid = str(uuid.uuid4())
        cursor.execute(add_rule_query, (security_group_id, rule_uuid, 128, 0, 'icmp'))

    cursor.close()

cnx.commit()
cnx.close()