Query: dns_parent_report

with domain_list as (
  select distinct domain, substring( domain from '^(?:[^/:]*:[^/@]*@)?(?:[^/:.]*\.)+([^:/]+)' ) as tld from net_dns_record where domain = $1
),
domain_parent_server as (
  select l.domain, d.domain as tld, d.target as parent_server from net_dns_record as d inner join domain_list as l on d.domain = l.tld where d.type = 'SOA'
),
domain_parent_server_ip as (
  select * from net_dns_record where domain in (select parent_server from domain_parent_server)
),
domain_parent_server_with_ip as (
  select domain_parent_server.domain, host(domain_parent_server_ip.ip) as ip_text from domain_parent_server inner join domain_parent_server_ip on domain_parent_server.parent_server = domain_parent_server_ip.domain where domain_parent_server_ip.type = 'A' order by domain_parent_server.domain
),
domain_parent_server_ns_list as (
  select net_dns_record.domain, string_agg(net_dns_record.target, ', ') as ns_records from net_dns_record inner join domain_parent_server_with_ip on net_dns_record.domain = domain_parent_server_with_ip.domain and net_dns_record.dns_server = domain_parent_server_with_ip.ip_text and net_dns_record.type = 'NS' group by net_dns_record.domain
),
domain_parent_server_ns_info as (
  select net_dns_record.domain, net_dns_record.target from net_dns_record inner join domain_parent_server_with_ip on net_dns_record.domain = domain_parent_server_with_ip.domain and net_dns_record.dns_server = domain_parent_server_with_ip.ip_text and net_dns_record.type = 'NS' order by net_dns_record.domain
),
ns_ips as (
  select domain, type, ip from net_dns_record where domain in (select target from domain_parent_server_ns_info) and type = 'A' order by domain
),
ns_with_type_a_record as (
  select domain_parent_server_ns_info.domain, ns_ips.type, domain_parent_server_ns_info.target, ns_ips.ip from domain_parent_server_ns_info left join ns_ips on domain_parent_server_ns_info.target = ns_ips.domain
)
select
  'Name servers listed at parent' as "Recommendation",
  case
    when (select ns_records from domain_parent_server_ns_list where domain = domain_list.domain) is not null then '✅'
    else '❌'
  end as "Status",
  case
    when (select ns_records from domain_parent_server_ns_list where domain = domain_list.domain) is not null then 'Parent server has name server information.'
    else 'Parent server do not have information for name servers.'
  end
    || ' It is highly recommended that the parent server should have information for all your name server, so that if anyone want your domain information and does not know DNS server can ask parent server for information.' as "Result"
from
  domain_list
UNION
select
  'Every name server listed at parent must have A records' as "Recommendation",
  case
    when (select target from ns_with_type_a_record where domain = domain_list.domain and type is null) is not null then '❌'
    else '✅'
  end as "Status",
  case
    when (select target from ns_with_type_a_record where domain = domain_list.domain and type is null) is not null then 'Some name servers [' || (select string_agg(target, ', ') from ns_with_type_a_record where domain = domain_list.domain and type is null) || '] do not have A records.'
    else 'Every name server listed at parent has A records.'
  end
    || ' It is highly recommended that every name server listed at parent should have A record.' as "Result"
from
  domain_list