WITH post_metrics AS (
    SELECT
        p.id AS post_id,
        p.commentary AS post_text,
        FROM_UNIXTIME(p."publishedAt" / 1000) AS published_date,
        p."lifecycleState" AS status,
        CASE
            WHEN p.content.media IS NOT NULL THEN 'MEDIA'
            WHEN p.content.article IS NOT NULL THEN 'ARTICLE'
            WHEN p.content.poll IS NOT NULL THEN 'POLL'
            WHEN p.content."multiImage" IS NOT NULL THEN 'MULTI_IMAGE'
            WHEN p.content.carousel IS NOT NULL THEN 'CAROUSEL'
            ELSE 'TEXT_ONLY'
        END AS content_type,
        ps."totalShareStatistics"."impressionCount" AS impressions,
        ps."totalShareStatistics"."uniqueImpressionsCount" AS unique_impressions,
        ps."totalShareStatistics"."clickCount" AS clicks,
        ps."totalShareStatistics"."likeCount" AS likes,
        ps."totalShareStatistics"."commentCount" AS comments,
        ps."totalShareStatistics"."shareCount" AS shares,
        ps."totalShareStatistics".engagement AS engagement_rate
    FROM
        nekt_raw.linkedin_organization_posts p
        LEFT JOIN nekt_raw.linkedin_post_statistics ps 
            ON p.id = COALESCE(ps."ugcPost", ps.share)
    WHERE
        p."lifecycleState" = 'PUBLISHED'
)
SELECT
    post_id,
    SUBSTRING(post_text, 1, 100) AS post_preview,
    published_date,
    content_type,
    impressions,
    unique_impressions,
    clicks,
    likes,
    comments,
    shares,
    ROUND(engagement_rate * 100, 2) AS engagement_rate_pct,
    (likes + comments + shares) AS total_engagements
FROM
    post_metrics
ORDER BY
    total_engagements DESC
LIMIT 20

WITH daily_followers AS (
    SELECT
        DATE(date) AS report_date,
        "followerGains"."organicFollowerGain" AS organic_gained,
        "followerGains"."paidFollowerGain" AS paid_gained,
        ("followerGains"."organicFollowerGain" + "followerGains"."paidFollowerGain") AS total_gained
    FROM
        nekt_raw.linkedin_follower_statistics
    WHERE
        date >= CURRENT_DATE - INTERVAL '30' DAY
),
running_totals AS (
    SELECT
        report_date,
        organic_gained,
        paid_gained,
        total_gained,
        SUM(total_gained) OVER (ORDER BY report_date) AS cumulative_growth,
        AVG(total_gained) OVER (
            ORDER BY report_date 
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ) AS seven_day_avg
    FROM
        daily_followers
)
SELECT
    report_date,
    organic_gained,
    paid_gained,
    total_gained,
    cumulative_growth,
    ROUND(seven_day_avg, 1) AS seven_day_moving_avg,
    ROUND(organic_gained * 100.0 / NULLIF(total_gained, 0), 1) AS organic_pct
FROM
    running_totals
ORDER BY
    report_date DESC

WITH seniority_stats AS (
    SELECT
        s.seniority,
        SUM(s."pageStatistics".views."allPageViews"."pageViews") AS total_views,
        SUM(s."pageStatistics".views."allPageViews"."uniquePageViews") AS unique_views
    FROM
        nekt_raw.linkedin_page_statistics ps
        CROSS JOIN UNNEST(ps."pageStatisticsBySeniority") AS t(s)
    WHERE
        DATE(ps.date) >= CURRENT_DATE - INTERVAL '30' DAY
    GROUP BY
        s.seniority
),
total AS (
    SELECT SUM(total_views) AS grand_total FROM seniority_stats
)
SELECT
    seniority,
    total_views,
    unique_views,
    ROUND(total_views * 100.0 / grand_total, 1) AS pct_of_total
FROM
    seniority_stats, total
ORDER BY
    total_views DESC