WITH base_data AS (
    SELECT
        asn_no,
        asn_org,
        cert_is_expired,
        cert_is_match,
        cert_is_valid,
        cert_iss_sub_eq,
        cert_issuer_cn_one,
        cert_issuer_orgs,
        cert_not_after,
        cert_not_before,
        cert_sn,
        cert_subject_cn,
        cert_subject_org_one,
        cert_subject_root_domains,
        geoip_country_name,
        ip,
        lastupdatetime,
        port,
        protocol
    FROM fofa_data
    WHERE  (array_contains(cert_subject_root_domains, 'stackoverflow.com')) OR  (array_contains(cert_subject_root_domains, 'cypress.com')) AND lastupdatetime > DATE_SUB(NOW(), INTERVAL 1 YEAR) AND (is_fraud = false OR is_fraud IS NULL)
),

total_count_cte AS (
    SELECT
        COUNT(*) AS total_count
    FROM base_data
),

unique_ip_count_cte AS (
    SELECT
        COUNT(DISTINCT ip) AS unique_ip_count
    FROM base_data
    WHERE ip IS NOT NULL
),

top_geoip_country_name AS (
    SELECT
        geoip_country_name,
        COUNT(*) AS total_count
    FROM base_data t1
    WHERE geoip_country_name IS NOT NULL
    GROUP BY geoip_country_name
    ORDER BY total_count DESC
    LIMIT 10
),

top_port AS (
    SELECT
        port,
        COUNT(*) AS total_count
    FROM base_data t1
    WHERE port IS NOT NULL
    GROUP BY port
    ORDER BY total_count DESC
    LIMIT 10
),

top_protocol AS (
    SELECT
        protocol,
        COUNT(*) AS total_count
    FROM base_data t1
    WHERE protocol IS NOT NULL
    GROUP BY protocol
    ORDER BY total_count DESC
    LIMIT 10
),

top_cert_subject_org_one AS (
    SELECT
        cert_subject_org_one,
        COUNT(*) AS total_count
    FROM base_data t1
    WHERE cert_subject_org_one IS NOT NULL
    GROUP BY cert_subject_org_one
    ORDER BY total_count DESC
    LIMIT 10
),

top_cert_sn AS (
    SELECT
        cert_sn,
        COUNT(*) AS total_count,
        any(cert_is_valid) as cert_is_valid,
        any(cert_is_match) as cert_is_match,
        any(cert_iss_sub_eq) as cert_iss_sub_eq,
        any(cert_is_expired) as cert_is_expired,
        any(CAST(CAST(cert_subject_root_domains AS JSON) AS STRING)) as cert_subject_root_domains,
        any(cert_subject_org_one) as cert_subject_org_one,
        any(cert_subject_cn) as cert_subject_cn,
        any(CAST(CAST(cert_issuer_orgs AS JSON) AS STRING)) as cert_issuer_orgs,
        any(cert_issuer_cn_one) as cert_issuer_cn_one,
        any(cert_not_before) as cert_not_before,
        any(cert_not_after) as cert_not_after
    FROM base_data t1
    WHERE cert_sn IS NOT NULL
    GROUP BY cert_sn
    ORDER BY total_count DESC
    LIMIT 10
),

top_asn_no AS (
    SELECT
        asn_no,
        COUNT(*) AS total_count,
        any(asn_org) as asn_org
    FROM base_data t1
    WHERE asn_no IS NOT NULL
    GROUP BY asn_no
    ORDER BY total_count DESC
    LIMIT 10
),

unique_geoip_country_name AS (
    SELECT
        COUNT(DISTINCT geoip_country_name) AS unique_count
    FROM base_data
    WHERE geoip_country_name IS NOT NULL
),

unique_port AS (
    SELECT
        COUNT(DISTINCT port) AS unique_count
    FROM base_data
    WHERE port IS NOT NULL
),

unique_protocol AS (
    SELECT
        COUNT(DISTINCT protocol) AS unique_count
    FROM base_data
    WHERE protocol IS NOT NULL
),

unique_cert_subject_org_one AS (
    SELECT
        COUNT(DISTINCT cert_subject_org_one) AS unique_count
    FROM base_data
    WHERE cert_subject_org_one IS NOT NULL
),

unique_cert_sn AS (
    SELECT
        COUNT(DISTINCT cert_sn) AS unique_count
    FROM base_data
    WHERE cert_sn IS NOT NULL
),

unique_asn_no AS (
    SELECT
        COUNT(DISTINCT asn_no) AS unique_count
    FROM base_data
    WHERE asn_no IS NOT NULL
)

SELECT
    'country' AS field_name,
    geoip_country_name AS field_value,
    total_count AS count,
NULL AS detail,
NULL AS uniq_json
FROM top_geoip_country_name

UNION ALL

SELECT
    'uniq' AS field_name,
    'country' AS field_value,
    unique_count AS count,
    NULL AS detail,
    NULL AS uniq_json
FROM unique_geoip_country_name

UNION ALL

SELECT
    'port' AS field_name,
    port AS field_value,
    total_count AS count,
NULL AS detail,
NULL AS uniq_json
FROM top_port

UNION ALL

SELECT
    'uniq' AS field_name,
    'port' AS field_value,
    unique_count AS count,
    NULL AS detail,
    NULL AS uniq_json
FROM unique_port

UNION ALL

SELECT
    'protocol' AS field_name,
    protocol AS field_value,
    total_count AS count,
NULL AS detail,
NULL AS uniq_json
FROM top_protocol

UNION ALL

SELECT
    'uniq' AS field_name,
    'protocol' AS field_value,
    unique_count AS count,
    NULL AS detail,
    NULL AS uniq_json
FROM unique_protocol

UNION ALL

SELECT
    'cert.subject.org' AS field_name,
    cert_subject_org_one AS field_value,
    total_count AS count,
NULL AS detail,
NULL AS uniq_json
FROM top_cert_subject_org_one

UNION ALL

SELECT
    'uniq' AS field_name,
    'cert.subject.org' AS field_value,
    unique_count AS count,
    NULL AS detail,
    NULL AS uniq_json
FROM unique_cert_subject_org_one

UNION ALL

SELECT
    'cert.sn' AS field_name,
    cert_sn AS field_value,
    total_count AS count,
JSON_OBJECT(
    'cert.sn', cert_sn,
            'cert.is_expired', cert_is_expired,
            'cert.is_match', cert_is_match,
            'cert.is_valid', cert_is_valid,
            'cert.is_equal', cert_iss_sub_eq,
            'cert.issuer.cn', cert_issuer_cn_one,
            'cert.issuer.org', CAST(cert_issuer_orgs AS JSONB),
            'cert.not_after', cert_not_after,
            'cert.not_before', cert_not_before,
            'cert.subject.cn', cert_subject_cn,
            'cert.subject.org', cert_subject_org_one,
            'cert.domain', CAST(cert_subject_root_domains AS JSONB)
) AS detail,
NULL AS uniq_json
FROM top_cert_sn

UNION ALL

SELECT
    'uniq' AS field_name,
    'cert.sn' AS field_value,
    unique_count AS count,
    NULL AS detail,
    NULL AS uniq_json
FROM unique_cert_sn

UNION ALL

SELECT
    'asn' AS field_name,
    asn_no AS field_value,
    total_count AS count,
JSON_OBJECT(
    'asn', asn_no,
            'org', asn_org
) AS detail,
NULL AS uniq_json
FROM top_asn_no

UNION ALL

SELECT
    'uniq' AS field_name,
    'asn' AS field_value,
    unique_count AS count,
    NULL AS detail,
    NULL AS uniq_json
FROM unique_asn_no

UNION ALL

SELECT
    'total' AS field_name,
    'count' AS field_value,
    total_count AS count,
    NULL AS detail,
    NULL AS uniq_json
FROM total_count_cte

UNION ALL

SELECT
    'uniq' AS field_name,
    'ip' AS field_value,
    unique_ip_count AS count,
    NULL AS detail,
    NULL AS uniq_json
FROM unique_ip_count_cte