SELECT
    i.instance_id,
    i.public_ip_address,
    i.region,
    sg.group_id,
    sg.group_name,
    perm->>'FromPort' AS from_port,
    perm->>'ToPort' AS to_port,
    perm->>'IpProtocol' AS protocol,
    ip_range->>'CidrIp' AS cidr
FROM aws_ec2_instances AS i
CROSS JOIN JSONB_ARRAY_ELEMENTS(i.security_groups) AS instance_sg
JOIN aws_ec2_security_groups AS sg
    ON sg.group_id = instance_sg->>'GroupId'
CROSS JOIN JSONB_ARRAY_ELEMENTS(sg.ip_permissions) AS perm
CROSS JOIN JSONB_ARRAY_ELEMENTS(perm->'IpRanges') AS ip_range
WHERE i.public_ip_address IS NOT NULL
    AND (ip_range->>'CidrIp' = '0.0.0.0/0' OR ip_range->>'CidrIp' = '::/0');

SELECT
    b.name AS bucket_name,
    b.arn,
    b.region,
    p.policy
FROM aws_s3_buckets AS b
LEFT JOIN aws_s3_bucket_policies AS p ON b.arn = p.bucket_arn
LEFT JOIN aws_s3_bucket_public_access_blocks AS pab ON pab._cq_parent_id = b._cq_id
WHERE (
    p.policy::text LIKE '%"Principal":"*"%'
    OR p.policy::text LIKE '%"Principal":{"AWS":"*"}%'
)
AND (
    (pab.public_access_block_configuration -> 'BlockPublicAcls')::boolean = false
    OR (pab.public_access_block_configuration -> 'BlockPublicPolicy')::boolean = false
    OR (pab.public_access_block_configuration -> 'IgnorePublicAcls')::boolean = false
    OR (pab.public_access_block_configuration -> 'RestrictPublicBuckets')::boolean = false
);

SELECT
    db_instance_identifier,
    endpoint_address,
    endpoint_port,
    engine,
    region,
    vpc_security_groups
FROM aws_rds_instances
WHERE publicly_accessible = true;

SELECT
    load_balancer_name,
    dns_name,
    scheme,
    type,
    region,
    availability_zones
FROM aws_elbv2_load_balancers
WHERE scheme = 'internet-facing';

SELECT DISTINCT
    vm.name AS vm_name,
    vm.location,
    pip.properties->>'ipAddress' AS public_ip,
    nsg.name AS nsg_name,
    rule->>'destinationPortRange' AS port_range,
    rule->>'protocol' AS protocol
FROM azure_compute_virtual_machines AS vm
CROSS JOIN JSONB_ARRAY_ELEMENTS(vm.network_interfaces) AS vm_nic
JOIN azure_network_interfaces AS nic
    ON nic.id = vm_nic->>'id'
CROSS JOIN JSONB_ARRAY_ELEMENTS(nic.ip_configurations) AS ip_config
JOIN azure_network_public_ip_addresses AS pip
    ON pip.id = ip_config->'properties'->'publicIPAddress'->>'id'
JOIN azure_network_security_groups AS nsg
    ON nsg.id = nic.properties->'networkSecurityGroup'->>'id'
CROSS JOIN JSONB_ARRAY_ELEMENTS(nsg.security_rules) AS rule
WHERE pip.properties->>'ipAddress' IS NOT NULL
    AND rule->>'access' = 'Allow'
    AND rule->>'direction' = 'Inbound'
    AND (rule->>'sourceAddressPrefix' = '*'
         OR rule->>'sourceAddressPrefix' = '0.0.0.0/0'
         OR rule->>'sourceAddressPrefix' = 'Internet');

SELECT
    sa.name AS storage_account_name,
    sa.location,
    sa.allow_blob_public_access,
    c.name AS container_name,
    c.public_access
FROM azure_storage_accounts AS sa
JOIN azure_storage_blob_containers AS c
    ON c.storage_account_id = sa.id
WHERE sa.allow_blob_public_access = true
    AND c.public_access != 'None';

SELECT
    name,
    fully_qualified_domain_name,
    location,
    public_network_access,
    administrator_login
FROM azure_sql_servers
WHERE public_network_access = 'Enabled';

SELECT
    lb.name AS load_balancer_name,
    lb.location,
    pip.ip_address AS public_ip,
    fip->>'name' AS frontend_name
FROM azure_network_load_balancers AS lb
CROSS JOIN JSONB_ARRAY_ELEMENTS(lb.frontend_ip_configurations) AS fip
JOIN azure_network_public_ip_addresses AS pip
    ON pip.id = fip->>'publicIPAddress'
WHERE pip.ip_address IS NOT NULL;

SELECT DISTINCT
    i.name AS instance_name,
    i.zone,
    nic->>'networkIP' AS internal_ip,
    access_config->>'natIP' AS external_ip,
    fw.name AS firewall_name,
    fw.allowed AS allowed_ports,
    fw.source_ranges
FROM gcp_compute_instances AS i
CROSS JOIN JSONB_ARRAY_ELEMENTS(i.network_interfaces) AS nic
CROSS JOIN JSONB_ARRAY_ELEMENTS(nic->'accessConfigs') AS access_config
JOIN gcp_compute_firewalls AS fw
    ON SPLIT_PART(fw.network, '/networks/', 2) = SPLIT_PART(nic->>'network', '/networks/', 2)
WHERE access_config->>'natIP' IS NOT NULL
    AND fw.direction = 'INGRESS'
    AND fw.source_ranges @> '["0.0.0.0/0"]'::jsonb;

SELECT
    b.name AS bucket_name,
    b.location,
    binding->>'role' AS role,
    member AS member
FROM gcp_storage_buckets AS b
CROSS JOIN JSONB_ARRAY_ELEMENTS(b.iam_policy->'bindings') AS binding
CROSS JOIN JSONB_ARRAY_ELEMENTS_TEXT(binding->'members') AS member
WHERE member IN ('allUsers', 'allAuthenticatedUsers');

SELECT
    name,
    database_version,
    region,
    ip_addresses,
    settings->'ipConfiguration'->>'ipv4Enabled' AS ipv4_enabled,
    settings->'ipConfiguration'->'authorizedNetworks' AS authorized_networks
FROM gcp_sql_instances
WHERE (settings->'ipConfiguration'->>'ipv4Enabled')::boolean = true;

SELECT
    name,
    ip_address,
    ip_protocol,
    port_range,
    region,
    target,
    load_balancing_scheme
FROM gcp_compute_forwarding_rules
WHERE load_balancing_scheme = 'EXTERNAL';

SELECT
    'AWS' AS cloud_provider,
    'EC2' AS resource_type,
    instance_id AS resource_id,
    region AS location,
    public_ip_address AS public_ip
FROM aws_ec2_instances
WHERE public_ip_address IS NOT NULL

UNION ALL

SELECT
    'Azure' AS cloud_provider,
    'VM' AS resource_type,
    vm.name AS resource_id,
    vm.location,
    pip.properties->>'ipAddress' AS public_ip
FROM azure_compute_virtual_machines AS vm
CROSS JOIN JSONB_ARRAY_ELEMENTS(vm.network_interfaces) AS vm_nic
JOIN azure_network_interfaces AS nic
    ON nic.id = vm_nic->>'id'
CROSS JOIN JSONB_ARRAY_ELEMENTS(nic.ip_configurations) AS ip_config
JOIN azure_network_public_ip_addresses AS pip
    ON pip.id = ip_config->'properties'->'publicIPAddress'->>'id'
WHERE pip.properties->>'ipAddress' IS NOT NULL

UNION ALL

SELECT
    'GCP' AS cloud_provider,
    'Compute Engine' AS resource_type,
    name AS resource_id,
    zone AS location,
    access_config->>'natIP' AS public_ip
FROM gcp_compute_instances
CROSS JOIN JSONB_ARRAY_ELEMENTS(network_interfaces) AS nic
CROSS JOIN JSONB_ARRAY_ELEMENTS(nic->'accessConfigs') AS access_config
WHERE access_config->>'natIP' IS NOT NULL;