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