discourse/plugins/discourse-adplugin/app/models/concerns/reports/ad_plugin.rb

# frozen_string_literal: true
module Reports::AdPlugin
  extend ActiveSupport::Concern

  class_methods do
    # Ad Impressions Report - All ads by type and placement
    def report_ad_plugin_ad_impressions(report)
      report.icon = "rectangle-ad"
      report.modes = [:table]

      report.labels = [
        { type: :text, property: :ad_name, title: "Ad Name" },
        { type: :text, property: :ad_type, title: "Ad Type" },
        { type: :text, property: :placement, title: "Placement" },
        { type: :number, property: :impressions, title: "Impressions" },
      ]

      start_date = report.start_date
      end_date = report.end_date
      limit = report.limit || 50

      sql = <<~SQL
        SELECT
          COALESCE(ha.name,
      CASE ai.ad_type
        WHEN 1 THEN 'Google AdSense'
        WHEN 2 THEN 'Google DFP'
        WHEN 3 THEN 'Amazon Product Links'
        WHEN 4 THEN 'Carbon Ads'
        WHEN 5 THEN 'AdButler'
      END
    ) as ad_name,
          CASE ai.ad_type
            WHEN 0 THEN 'House Ad'
            WHEN 1 THEN 'AdSense'
            WHEN 2 THEN 'DFP'
            WHEN 3 THEN 'Amazon'
            WHEN 4 THEN 'Carbon'
            WHEN 5 THEN 'AdButler'
          END as ad_type,
          ai.placement,
          COUNT(*) as impressions
        FROM ad_plugin_impressions ai
        LEFT JOIN ad_plugin_house_ads ha ON ai.ad_plugin_house_ad_id = ha.id
        WHERE ai.created_at >= :start_date
          AND ai.created_at <= :end_date
        GROUP BY ha.name, ai.ad_type, ai.placement
        ORDER BY impressions DESC
        LIMIT :limit
      SQL

      results = DB.query(sql, start_date: start_date, end_date: end_date, limit: limit)

      report.data =
        results.map do |row|
          {
            ad_name: row.ad_name || "Unknown",
            ad_type: row.ad_type,
            placement: row.placement,
            impressions: row.impressions,
          }
        end
    end

    # House Ads Performance Report - Only house ads with detailed metrics
    def report_ad_plugin_house_ads_performance(report)
      report.icon = "rectangle-ad"
      report.modes = [:table]

      report.labels = [
        { type: :text, property: :ad_name, title: "House Ad" },
        { type: :text, property: :placement, title: "Placement" },
        { type: :number, property: :impressions, title: "Impressions" },
        { type: :number, property: :unique_users, title: "Unique Users" },
      ]

      house_ad_name_filter = report.filters.dig(:house_ad_name)

      house_ad_choices =
        ::AdPlugin::HouseAd.order(:name).pluck(:name).map { |name| { id: name, name: name } }
      house_ad_choices.unshift({ id: "any", name: "All House Ads" })

      placement_filter = report.filters.dig(:ad_placement)

      placement_choices =
        AdPlugin::HouseAdSetting::DEFAULTS.keys.map do |name|
          name = name.to_s.split("_").join("-")
          { id: name, name: name }
        end

      placement_choices.unshift({ id: "any", name: "All" })

      report.add_filter(
        "house_ad_name",
        type: "list",
        default: house_ad_name_filter || "any",
        choices: house_ad_choices,
        allow_any: false,
        auto_insert_none_item: false,
      )

      report.add_filter(
        "ad_placement",
        type: "list",
        default: placement_filter || "any",
        choices: placement_choices,
        allow_any: false,
        auto_insert_none_item: false,
      )

      user_type_filter = report.filters.dig(:user_type)

      user_type_choices = [
        { id: "all", name: "All Users" },
        { id: "logged_in", name: "Logged In Users" },
        { id: "anonymous", name: "Anonymous Users" },
      ]

      report.add_filter(
        "user_type",
        type: "list",
        default: user_type_filter || "all",
        choices: user_type_choices,
        allow_any: false,
        auto_insert_none_item: false,
      )

      start_date = report.start_date
      end_date = report.end_date
      limit = report.limit || 50

      user_type_condition =
        case user_type_filter
        when "logged_in"
          "AND ai.user_id IS NOT NULL"
        when "anonymous"
          "AND ai.user_id IS NULL"
        else
          "" # "all" - no filter
        end

      sql = <<~SQL
        SELECT
          ha.name as ad_name,
          ai.placement,
          COUNT(*) as impressions,
          COUNT(DISTINCT ai.user_id) as unique_users
        FROM ad_plugin_impressions ai
        INNER JOIN ad_plugin_house_ads ha ON ai.ad_plugin_house_ad_id = ha.id
        WHERE ai.created_at >= :start_date
          AND ai.created_at <= :end_date
          AND ai.ad_type = 0
          #{user_type_condition}
          #{house_ad_name_filter && house_ad_name_filter != "any" ? "AND ha.name = :house_ad_name" : ""}
          #{placement_filter && placement_filter != "any" ? "AND ai.placement = :ad_placement" : ""}
        GROUP BY ha.id, ha.name, ai.placement
        ORDER BY impressions DESC
        LIMIT :limit
      SQL

      query_params = { start_date: start_date, end_date: end_date, limit: limit }

      query_params[:house_ad_name] = house_ad_name_filter if house_ad_name_filter &&
        house_ad_name_filter != "any"

      query_params[:ad_placement] = placement_filter if placement_filter &&
        placement_filter != "any"

      results = DB.query(sql, query_params)

      report.data =
        results.map do |row|
          {
            ad_name: row.ad_name,
            placement: row.placement,
            impressions: row.impressions,
            unique_users: row.unique_users,
          }
        end
    end

    def report_ad_plugin_impressions_by_user(report)
      report.icon = "rectangle-ad"
      report.modes = [:table]

      report.labels = [
        {
          type: :user,
          properties: {
            username: :username,
            id: :user_id,
            avatar: :avatar_template,
          },
          title: "User",
        },
        { type: :number, property: :impressions, title: "Impressions" },
      ]

      # Add ad_type filter
      ad_type_filter = report.filters.dig(:ad_type)

      ad_type_choices =
        ::AdPlugin::AdType.types.map { |key, value| { id: value.to_s, name: key.to_s.titleize } }
      ad_type_choices.unshift({ id: "any", name: "All Ad Types" })

      report.add_filter(
        "ad_type",
        type: "list",
        default: ad_type_filter || "any",
        choices: ad_type_choices,
        allow_any: false,
        auto_insert_none_item: false,
      )
      start_date = report.start_date
      end_date = report.end_date
      limit = report.limit || 50

      # Build SQL with optional ad_type filter
      sql = <<~SQL
        SELECT
          u.username,
          u.id as user_id,
          u.uploaded_avatar_id,
          COUNT(*) as impressions
        FROM ad_plugin_impressions ai
        INNER JOIN users u ON u.id = ai.user_id
        WHERE ai.created_at >= :start_date
          AND ai.created_at <= :end_date
          #{ad_type_filter && ad_type_filter != "any" ? "AND ai.ad_type = :ad_type" : ""}
        GROUP BY u.id, u.username, u.uploaded_avatar_id
        ORDER BY impressions DESC
        LIMIT :limit
      SQL

      # Build query params
      query_params = { start_date: start_date, end_date: end_date, limit: limit }
      query_params[:ad_type] = ad_type_filter.to_i if ad_type_filter && ad_type_filter != "any"

      results = DB.query(sql, query_params)

      report.data =
        results.map do |row|
          {
            username: row.username,
            user_id: row.user_id,
            avatar_template: User.avatar_template(row.username, row.uploaded_avatar_id),
            impressions: row.impressions,
          }
        end
    end

    def report_ad_plugin_click_through_rate(report)
      report.icon = "rectangle-ad"
      report.modes = [:table]

      report.labels = [
        { type: :text, property: :ad_name, title: "Ad Name" },
        { type: :text, property: :ad_type, title: "Ad Type" },
        { type: :text, property: :placement, title: "Placement" },
        { type: :number, property: :impressions, title: "Impressions" },
        { type: :number, property: :clicks, title: "Clicks" },
        { type: :percent, property: :ctr, title: "CTR" },
      ]

      start_date = report.start_date
      end_date = report.end_date
      limit = report.limit || 50

      sql = <<~SQL
        WITH agg AS (
          SELECT
            ai.ad_plugin_house_ad_id,
            ai.ad_type,
            ai.placement,
            COUNT(*) AS impressions,
            SUM(CASE WHEN ai.clicked_at IS NOT NULL THEN 1 ELSE 0 END) AS clicks
          FROM ad_plugin_impressions ai
          WHERE ai.created_at BETWEEN :start_date AND :end_date
          GROUP BY ai.ad_plugin_house_ad_id, ai.ad_type, ai.placement
        )
        SELECT
          COALESCE(
            ha.name,
            CASE agg.ad_type
              WHEN 1 THEN 'Google AdSense'
              WHEN 2 THEN 'Google DFP'
              WHEN 3 THEN 'Amazon Product Links'
              WHEN 4 THEN 'Carbon Ads'
              WHEN 5 THEN 'AdButler'
            END
          ) AS ad_name,
          CASE agg.ad_type
            WHEN 0 THEN 'House Ad'
            WHEN 1 THEN 'AdSense'
            WHEN 2 THEN 'DFP'
            WHEN 3 THEN 'Amazon'
            WHEN 4 THEN 'Carbon'
            WHEN 5 THEN 'AdButler'
          END AS ad_type,
          agg.placement,
          agg.impressions,
          agg.clicks,
          ROUND(
            agg.clicks::numeric / NULLIF(agg.impressions, 0) * 100,
            2
          ) AS ctr
        FROM agg
        LEFT JOIN ad_plugin_house_ads ha ON agg.ad_plugin_house_ad_id = ha.id
        ORDER BY agg.impressions DESC
        LIMIT :limit
      SQL

      results = DB.query(sql, start_date: start_date, end_date: end_date, limit: limit)

      report.data =
        results.map do |row|
          {
            ad_name: row.ad_name || "Unknown",
            ad_type: row.ad_type,
            placement: row.placement,
            impressions: row.impressions,
            clicks: row.clicks,
            ctr: row.ctr.round(2),
          }
        end
    end

    def report_ad_plugin_click_through_rate_by_placement(report)
      report.icon = "rectangle-ad"
      report.modes = [:table]

      report.labels = [
        { type: :text, property: :placement, title: "Placement" },
        { type: :number, property: :impressions, title: "Impressions" },
        { type: :number, property: :clicks, title: "Clicks" },
        { type: :percent, property: :ctr, title: "CTR" },
      ]

      start_date = report.start_date
      end_date = report.end_date

      sql = <<~SQL
        SELECT
          ai.placement,
          COUNT(*) as impressions,
          SUM(CASE WHEN ai.clicked_at IS NOT NULL THEN 1 ELSE 0 END) as clicks,
          ROUND(
            SUM(CASE WHEN ai.clicked_at IS NOT NULL THEN 1 ELSE 0 END)::numeric /
            NULLIF(COUNT(*), 0) * 100,
            2
          ) as ctr
        FROM ad_plugin_impressions ai
        WHERE ai.created_at BETWEEN :start_date AND :end_date
        GROUP BY ai.placement
        ORDER BY impressions DESC
      SQL

      results = DB.query(sql, start_date: start_date, end_date: end_date)

      report.data =
        results.map do |row|
          {
            placement: row.placement,
            impressions: row.impressions,
            clicks: row.clicks,
            ctr: row.ctr || 0,
          }
        end
    end

    def report_ad_plugin_click_through_rate_by_ad_type(report)
      report.icon = "rectangle-ad"
      report.modes = [:table]

      report.labels = [
        { type: :text, property: :ad_name, title: "Ad Name" },
        { type: :text, property: :ad_type, title: "Ad Type" },
        { type: :number, property: :impressions, title: "Impressions" },
        { type: :number, property: :clicks, title: "Clicks" },
        { type: :percent, property: :ctr, title: "CTR" },
      ]

      start_date = report.start_date
      end_date = report.end_date
      limit = report.limit || 50

      sql = <<~SQL
        WITH agg AS (
          SELECT
            ai.ad_plugin_house_ad_id,
            ai.ad_type,
            COUNT(*) AS impressions,
            SUM(CASE WHEN ai.clicked_at IS NOT NULL THEN 1 ELSE 0 END) AS clicks
          FROM ad_plugin_impressions ai
          WHERE ai.created_at BETWEEN :start_date AND :end_date
          GROUP BY ai.ad_plugin_house_ad_id, ai.ad_type
        )
        SELECT
          COALESCE(
            ha.name,
            CASE agg.ad_type
              WHEN 1 THEN 'Google AdSense'
              WHEN 2 THEN 'Google DFP'
              WHEN 3 THEN 'Amazon Product Links'
              WHEN 4 THEN 'Carbon Ads'
              WHEN 5 THEN 'AdButler'
            END
          ) AS ad_name,
          CASE agg.ad_type
            WHEN 0 THEN 'House Ad'
            WHEN 1 THEN 'AdSense'
            WHEN 2 THEN 'DFP'
            WHEN 3 THEN 'Amazon'
            WHEN 4 THEN 'Carbon'
            WHEN 5 THEN 'AdButler'
          END AS ad_type,
          agg.impressions,
          agg.clicks,
          ROUND(
            agg.clicks::numeric / NULLIF(agg.impressions, 0) * 100,
            2
          ) AS ctr
        FROM agg
        LEFT JOIN ad_plugin_house_ads ha ON agg.ad_plugin_house_ad_id = ha.id
        ORDER BY agg.impressions DESC
        LIMIT :limit
      SQL

      results = DB.query(sql, start_date: start_date, end_date: end_date, limit: limit)

      report.data =
        results.map do |row|
          {
            ad_name: row.ad_name || "Unknown",
            ad_type: row.ad_type,
            impressions: row.impressions,
            clicks: row.clicks,
            ctr: row.ctr || 0,
          }
        end
    end
  end
end