
jbe@520: BEGIN;
jbe@520: 
jbe@520: CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@520:   SELECT * FROM (VALUES ('3.2.2', 3, 2, 2))
jbe@520:   AS "subquery"("string", "major", "minor", "revision");
jbe@520: 
jbe@520: CREATE OR REPLACE FUNCTION "featured_initiative"
jbe@520:   ( "recipient_id_p" "member"."id"%TYPE,
jbe@520:     "area_id_p"      "area"."id"%TYPE )
jbe@520:   RETURNS SETOF "initiative"."id"%TYPE
jbe@520:   LANGUAGE 'plpgsql' STABLE AS $$
jbe@520:     DECLARE
jbe@520:       "counter_v"         "member"."notification_counter"%TYPE;
jbe@520:       "sample_size_v"     "member"."notification_sample_size"%TYPE;
jbe@520:       "initiative_id_ary" INT4[];  --"initiative"."id"%TYPE[]
jbe@520:       "match_v"           BOOLEAN;
jbe@520:       "member_id_v"       "member"."id"%TYPE;
jbe@520:       "seed_v"            TEXT;
jbe@520:       "initiative_id_v"   "initiative"."id"%TYPE;
jbe@520:     BEGIN
jbe@520:       SELECT "notification_counter", "notification_sample_size"
jbe@520:         INTO "counter_v", "sample_size_v"
jbe@520:         FROM "member" WHERE "id" = "recipient_id_p";
jbe@520:       IF COALESCE("sample_size_v" <= 0, TRUE) THEN
jbe@520:         RETURN;
jbe@520:       END IF;
jbe@520:       "initiative_id_ary" := '{}';
jbe@520:       LOOP
jbe@520:         "match_v" := FALSE;
jbe@520:         FOR "member_id_v", "seed_v" IN
jbe@520:           SELECT * FROM (
jbe@520:             SELECT DISTINCT
jbe@520:               "supporter"."member_id",
jbe@520:               md5(
jbe@520:                 "recipient_id_p" || '-' ||
jbe@520:                 "counter_v"      || '-' ||
jbe@520:                 "area_id_p"      || '-' ||
jbe@520:                 "supporter"."member_id"
jbe@520:               ) AS "seed"
jbe@520:             FROM "supporter"
jbe@520:             JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"
jbe@520:             JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@520:             WHERE "supporter"."member_id" != "recipient_id_p"
jbe@520:             AND "issue"."area_id" = "area_id_p"
jbe@520:             AND "issue"."state" IN ('admission', 'discussion', 'verification')
jbe@520:           ) AS "subquery"
jbe@520:           ORDER BY "seed"
jbe@520:         LOOP
jbe@520:           SELECT "initiative"."id" INTO "initiative_id_v"
jbe@520:             FROM "initiative"
jbe@520:             JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@520:             JOIN "area" ON "area"."id" = "issue"."area_id"
jbe@520:             JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id"
jbe@520:             LEFT JOIN "supporter" AS "self_support" ON
jbe@520:               "self_support"."initiative_id" = "initiative"."id" AND
jbe@520:               "self_support"."member_id" = "recipient_id_p"
jbe@520:             LEFT JOIN "privilege" ON
jbe@520:               "privilege"."member_id" = "recipient_id_p" AND
jbe@520:               "privilege"."unit_id" = "area"."unit_id" AND
jbe@520:               "privilege"."voting_right" = TRUE
jbe@520:             LEFT JOIN "subscription" ON
jbe@520:               "subscription"."member_id" = "recipient_id_p" AND
jbe@520:               "subscription"."unit_id" = "area"."unit_id"
jbe@520:             LEFT JOIN "ignored_initiative" ON
jbe@520:               "ignored_initiative"."member_id" = "recipient_id_p" AND
jbe@520:               "ignored_initiative"."initiative_id" = "initiative"."id"
jbe@520:             WHERE "supporter"."member_id" = "member_id_v"
jbe@520:             AND "issue"."area_id" = "area_id_p"
jbe@520:             AND "issue"."state" IN ('admission', 'discussion', 'verification')
jbe@520:             AND "initiative"."revoked" ISNULL
jbe@520:             AND "self_support"."member_id" ISNULL
jbe@520:             AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
jbe@520:             AND (
jbe@520:               "privilege"."member_id" NOTNULL OR
jbe@520:               "subscription"."member_id" NOTNULL )
jbe@520:             AND "ignored_initiative"."member_id" ISNULL
jbe@520:             AND NOT EXISTS (
jbe@520:               SELECT NULL FROM "draft"
jbe@520:               JOIN "ignored_member" ON
jbe@520:                 "ignored_member"."member_id" = "recipient_id_p" AND
jbe@520:                 "ignored_member"."other_member_id" = "draft"."author_id"
jbe@520:               WHERE "draft"."initiative_id" = "initiative"."id"
jbe@520:             )
jbe@520:             ORDER BY md5("seed_v" || '-' || "initiative"."id")
jbe@520:             LIMIT 1;
jbe@520:           IF FOUND THEN
jbe@520:             "match_v" := TRUE;
jbe@520:             RETURN NEXT "initiative_id_v";
jbe@520:             IF array_length("initiative_id_ary", 1) + 1 >= "sample_size_v" THEN
jbe@520:               RETURN;
jbe@520:             END IF;
jbe@520:             "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v";
jbe@520:           END IF;
jbe@520:         END LOOP;
jbe@520:         EXIT WHEN NOT "match_v";
jbe@520:       END LOOP;
jbe@520:       RETURN;
jbe@520:     END;
jbe@520:   $$;
jbe@520: 
jbe@520: COMMIT;




