-----------------------------------------------------------------------------
-- fn_available_campaigns(site_id, ad_type, action_type, address)

CREATE OR REPLACE FUNCTION fn_available_campaigns(INTEGER, TEXT, TEXT, INET)
RETURNS SETOF campaigns AS $PROC$

  SELECT campaigns.*
    FROM campaigns
    INNER JOIN subscriptions
      ON subscriptions.campaign_id = campaigns.id
     AND subscriptions.site_id = $1
    WHERE campaigns.ad_type = $2

  -- exclude banned campaigns
  EXCEPT
  SELECT campaigns.*
    FROM campaigns
    INNER JOIN actions
      ON campaigns.id = actions.campaign_id
     AND campaigns.pay_per = $3
     AND actions.compensated
     AND actions.address = $4
     AND now() - actions.created_at < campaigns.reload_ban;

$PROC$ LANGUAGE SQL;

-----------------------------------------------------------------------------
-- fn_action()

CREATE OR REPLACE FUNCTION fn_action(
  v_action_id IN INTEGER,
  v_site_id   IN INTEGER,
  v_action    IN TEXT,    -- (view, click, lead, sale)
  v_ad_type   IN TEXT,    -- (banner, textlink, ...)
  v_format_id IN INTEGER, -- may be NULL
  v_address   IN INET
) RETURNS TEXT AS $PROC$

DECLARE
  a_compensated BOOLEAN;
  a_pay_per TEXT;
  a_campaign_id INTEGER;
  a_charge NUMERIC(12,6);
  a_return TEXT;

BEGIN
  SELECT actions.campaign_id
    INTO a_campaign_id
    FROM actions
    WHERE v_action_id = id;

  IF FOUND THEN

    -- verify given action

    PERFORM * FROM actions
      INNER JOIN campaigns
        ON actions.campaign_id = campaigns.id
       AND campaigns.ad_type = v_ad_type
      WHERE v_action_id = actions.id
        AND v_site_id = site_id;

    IF NOT FOUND THEN
      RAISE EXCEPTION 'bad action id';
    END IF;

  ELSE

    IF v_ad_type = 'banner' THEN

      SELECT campaigns.id
        INTO a_campaign_id
        FROM (
          SELECT campaigns.id
            FROM fn_available_campaigns(v_site_id, v_ad_type, v_action, v_address)
              AS campaigns

          -- include only campaigns with banners of the given format
          INTERSECT
          SELECT DISTINCT ON(banners.campaign_id) campaigns.id
            FROM campaigns
            INNER JOIN subscriptions
              ON subscriptions.campaign_id = campaigns.id
             AND subscriptions.site_id = v_site_id
            INNER JOIN banners
              ON banners.campaign_id = campaigns.id
             AND banners.format_id = v_format_id
        ) AS campaigns
        ORDER BY random() LIMIT 1;

    ELSE

      SELECT id
        INTO a_campaign_id
        FROM fn_available_campaigns(v_site_id, v_ad_type, v_action, v_address)
        ORDER BY random() LIMIT 1;

    END IF;

    IF NOT FOUND THEN
      -- XXX: Display our banners or error messages here
      RAISE NOTICE 'found no campaign';
      RETURN 'nothing';
    END IF;

    INSERT INTO actions (id, site_id, campaign_id, address, compensated)
      VALUES (v_action_id, v_site_id, a_campaign_id, v_address, FALSE);

  END IF;

  -- fetch necessary data for compensation
  IF a_pay_per = v_action AND NOT a_compensated THEN
    PERFORM fn_compensate(v_site_id, a_campaign_id, a_charge);
    UPDATE actions SET compensated = 'true' WHERE id = v_action_id;
  END IF;

  -- fetch url for given action / ad_type

  IF v_action = 'view' THEN
    IF v_ad_type = 'banner' THEN
      SELECT banners.img_href
        INTO a_return
        FROM banners
        WHERE banners.campaign_id = a_campaign_id
          AND banners.format_id = v_format_id
        ORDER BY random() LIMIT 1;
      RETURN a_return;
    ELSIF v_ad_type = 'text_link' THEN
      SELECT text_links.content
        INTO a_return
        FROM text_links
        WHERE text_links.campaign_id = a_campaign_id
        ORDER BY random() LIMIT 1;
    END IF;
  ELSIF v_action = 'click' THEN
    SELECT campaigns.href
      INTO a_return
      FROM campaigns
     WHERE campaigns.id = a_campaign_id;
  END IF;

  RETURN a_return;
END;

$PROC$ LANGUAGE plpgsql;