create table REQUEST (	ID NUMBER(10), FORWARDEDIP VARCHAR2(20) )
/

-- import data from ACC: select id, forwardedip from request where status = 'Checkuser' and forwardedip not like '%,%'

commit
/

create table ASN (	NETWORK VARCHAR2(18) not null, ASN NUMBER(30) not null, ORG VARCHAR2(4000) )
/

-- import data from ASN: https://dev.maxmind.com/geoip/geoip2/geolite2/
--  network = CIDR mask; ASN = AS Number; Org = organisation name
/
commit
/

alter table asn add baseip varchar2(15)
/
alter table asn add cidr varchar2(2)
/

update asn set baseip = regexp_substr(network, '^[0-9.]+', 1, 1), cidr = regexp_substr(network, '[0-9.]+$', 1, 1) where 1=1
/
commit
/

alter table asn add ipbin varchar2(32)
/
update asn set ipbin = lpad(to_bin(to_number(regexp_substr(BASEIP, '\d+', 1, 1))), 8, '0')
             || lpad(to_bin(to_number(regexp_substr(BASEIP, '\d+', 1, 2))), 8, '0')
             || lpad(to_bin(to_number(regexp_substr(BASEIP, '\d+', 1, 3))), 8, '0')
             || lpad(to_bin(to_number(regexp_substr(BASEIP, '\d+', 1, 4))), 8, '0')
where 1=1
/
commit
/


WITH
reqipbin AS
(
     SELECT
           r.ID
         , r.FORWARDEDIP
         , lpad(to_bin(to_number(regexp_substr(r.FORWARDEDIP, '\d+', 1, 1))), 8, '0')
             || lpad(to_bin(to_number(regexp_substr(r.FORWARDEDIP, '\d+', 1, 2))), 8, '0')
             || lpad(to_bin(to_number(regexp_substr(r.FORWARDEDIP, '\d+', 1, 3))), 8, '0')
             || lpad(to_bin(to_number(regexp_substr(r.FORWARDEDIP, '\d+', 1, 4))), 8, '0') AS IP_BIN
     FROM request r
),
reqprefixes as (
  SELECT ipb.id, ipb.FORWARDEDIP,ipb.IP_BIN, rpad(substr(ipb.IP_BIN, 0, pf.column_value), 32, '0') binprefix, pf.column_value as cidr
  FROM reqipbin ipb CROSS JOIN table(sys.odcinumberlist(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31)) pf
),
data as (
select id, FORWARDEDIP, NETWORK, count(*) over (partition by NETWORK) as number_in_range, org
from reqprefixes rp
inner join asn a on a.ipbin = rp.binprefix and a.CIDR = rp.cidr
and rp.cidr >= 16
)
select
     id, FORWARDEDIP, NETWORK, number_in_range, org,
     '<tr><td>' || id ||
     '</td><td>' || FORWARDEDIP ||
     '</td><td>' || network||
     '</td><td>' || number_in_range||
     '</td><td>' || org||
     '</td><td>' || '<a href="https://accounts.wmflabs.org/acc.php?action=zoom&amp;id=' || id || '">' || id || '</a>' ||
     '</td></tr>' as html
from data
where number_in_range > 1
order by number_in_range desc, FORWARDEDIP asc
/