rating Functions

DECLARE
    badge   rating.badge_with_orgs%ROWTYPE;
BEGIN

    SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;

    PERFORM rating.precalc_bibs_by_copy_or_uri(badge_id);

    SET LOCAL client_min_messages = error;
    DROP TABLE IF EXISTS precalc_bib_list;
    CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
        SELECT id FROM precalc_filter_bib_list
            INTERSECT
        SELECT id FROM precalc_bibs_by_copy_or_uri_list;

    RETURN QUERY
     SELECT pop.id AS bib,
            s.value::NUMERIC
      FROM  precalc_bib_list pop
            JOIN metabib.record_sorter s ON (
                s.source = pop.id
                AND s.attr = 'pubdate'
                AND s.value ~ '^\d+$'
            )
      WHERE s.value::INT <= EXTRACT(YEAR FROM NOW())::INT;
END;

DECLARE
    badge   rating.badge_with_orgs%ROWTYPE;
BEGIN

    SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;

    PERFORM rating.precalc_bibs_by_copy_or_uri(badge_id);

    SET LOCAL client_min_messages = error;
    DROP TABLE IF EXISTS precalc_bib_list;
    CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
        SELECT id FROM precalc_filter_bib_list
            INTERSECT
        SELECT id FROM precalc_bibs_by_copy_or_uri_list;

    RETURN QUERY
     SELECT b.id,
            1.0 / EXTRACT(EPOCH FROM AGE(b.create_date))::NUMERIC + 1.0
      FROM  precalc_bib_list pop
            JOIN biblio.record_entry b ON (b.id = pop.id);
END;

DECLARE
    badge   rating.badge_with_orgs%ROWTYPE;
BEGIN

    SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;

    PERFORM rating.precalc_bibs_by_copy(badge_id);

    DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
        SELECT id FROM precalc_filter_bib_list
            INTERSECT
        SELECT id FROM precalc_bibs_by_copy_list
    );

    ANALYZE precalc_copy_filter_bib_list;

    RETURN QUERY
     SELECT bib,
            SUM(checked_out)::NUMERIC / SUM(total)::NUMERIC
      FROM  (SELECT cn.record AS bib,
                    (cp.status = 1)::INT AS checked_out,
                    1 AS total
              FROM  asset.copy cp
                    JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
                    JOIN asset.call_number cn ON (cn.id = cp.call_number)
              WHERE cn.owning_lib = ANY (badge.orgs)
            ) x
      GROUP BY 1;
END;

DECLARE
    badge   rating.badge_with_orgs%ROWTYPE;
    iage    INT     := 1;
    iint    INT     := NULL;
    iscale  NUMERIC := NULL;
BEGIN

    SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;

    IF badge.horizon_age IS NULL THEN
        RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.',
            badge.name,
            badge.id;
    END IF;

    PERFORM rating.precalc_bibs_by_copy(badge_id);

    DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
        SELECT id FROM precalc_filter_bib_list
            INTERSECT
        SELECT id FROM precalc_bibs_by_copy_list
    );

    ANALYZE precalc_copy_filter_bib_list;

    iint := EXTRACT(EPOCH FROM badge.importance_interval);
    IF badge.importance_age IS NOT NULL THEN
        iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT;
    END IF;

    -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE!
    iscale := COALESCE(badge.importance_scale, 1.0);

    RETURN QUERY
     SELECT bib,
            SUM( circs * GREATEST( iscale * (iage - cage), 1.0 ))
      FROM (
         SELECT cn.record AS bib,
                (1 + EXTRACT(EPOCH FROM AGE(c.xact_start)) / iint)::INT AS cage,
                COUNT(c.id)::INT AS circs
          FROM  action.circulation c
                JOIN precalc_copy_filter_bib_list cf ON (c.target_copy = cf.copy)
                JOIN asset.copy cp ON (cp.id = c.target_copy)
                JOIN asset.call_number cn ON (cn.id = cp.call_number)
          WHERE c.xact_start >= NOW() - badge.horizon_age
                AND cn.owning_lib = ANY (badge.orgs)
                AND c.phone_renewal IS FALSE  -- we don't count renewals
                AND c.desk_renewal IS FALSE
                AND c.opac_renewal IS FALSE
          GROUP BY 1, 2
      ) x
      GROUP BY 1;
END;

DECLARE
    badge   rating.badge_with_orgs%ROWTYPE;
BEGIN

    SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;

    PERFORM rating.precalc_bibs_by_copy(badge_id);

    DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
        SELECT id FROM precalc_filter_bib_list
            INTERSECT
        SELECT id FROM precalc_bibs_by_copy_list
    );
    ANALYZE precalc_copy_filter_bib_list;

    RETURN QUERY
     SELECT f.id::INT AS bib,
            COUNT(f.copy)::NUMERIC
      FROM  precalc_copy_filter_bib_list f
            JOIN asset.copy cp ON (f.copy = cp.id)
            JOIN asset.call_number cn ON (cn.id = cp.call_number)
      WHERE cn.owning_lib = ANY (badge.orgs) GROUP BY 1;

END;

DECLARE
    badge   rating.badge_with_orgs%ROWTYPE;
BEGIN

    SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;

    PERFORM rating.precalc_bibs_by_copy(badge_id);

    DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
        SELECT id FROM precalc_filter_bib_list
            INTERSECT
        SELECT id FROM precalc_bibs_by_copy_list
    );

    ANALYZE precalc_copy_filter_bib_list;

    RETURN QUERY
     SELECT cn.record AS bib,
            COUNT(c.id)::NUMERIC AS circs
      FROM  action.circulation c
            JOIN precalc_copy_filter_bib_list cf ON (c.target_copy = cf.copy)
            JOIN asset.copy cp ON (cp.id = c.target_copy)
            JOIN asset.call_number cn ON (cn.id = cp.call_number)
      WHERE c.checkin_time IS NULL
            AND cn.owning_lib = ANY (badge.orgs)
      GROUP BY 1;

END;

DECLARE
    badge   rating.badge_with_orgs%ROWTYPE;
BEGIN

    SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;

    PERFORM rating.precalc_bibs_by_copy(badge_id);

    DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
        SELECT id FROM precalc_filter_bib_list
            INTERSECT
        SELECT id FROM precalc_bibs_by_copy_list
    );

    ANALYZE precalc_copy_filter_bib_list;

    RETURN QUERY
     SELECT rhrr.bib_record AS bib,
            COUNT(DISTINCT h.id)::NUMERIC AS holds
      FROM  action.hold_request h
            JOIN reporter.hold_request_record rhrr ON (rhrr.id = h.id)
            JOIN action.hold_copy_map m ON (m.hold = h.id)
            JOIN precalc_copy_filter_bib_list cf ON (rhrr.bib_record = cf.id AND m.target_copy = cf.copy)
      WHERE h.fulfillment_time IS NULL
            AND h.request_lib = ANY (badge.orgs)
      GROUP BY 1;
END;

BEGIN
    PERFORM rating.precalc_bibs_by_copy(badge_id);
    RETURN QUERY
        SELECT id, 1.0 FROM precalc_filter_bib_list
            INTERSECT
        SELECT id, 1.0 FROM precalc_bibs_by_copy_list;
END;

BEGIN
    PERFORM rating.precalc_bibs_by_copy_or_uri(badge_id);
    RETURN QUERY
        (SELECT id, 1.0 FROM precalc_filter_bib_list
            INTERSECT
        SELECT id, 1.0 FROM precalc_bibs_by_copy_list)
            UNION
        (SELECT id, 1.0 FROM precalc_bib_filter_bib_list
            INTERSECT
        SELECT id, 1.0 FROM precalc_bibs_by_uri_list);
END;

BEGIN
    PERFORM rating.precalc_bibs_by_uri(badge_id);
    RETURN QUERY
        SELECT id, 1.0 FROM precalc_bib_filter_bib_list
            INTERSECT
        SELECT id, 1.0 FROM precalc_bibs_by_uri_list;
END;

BEGIN
    RETURN QUERY
        SELECT id, 1.0 FROM precalc_bib_filter_bib_list;
END;

DECLARE
    badge   rating.badge_with_orgs%ROWTYPE;
    iage    INT     := 1;
    iint    INT     := NULL;
    iscale  NUMERIC := NULL;
BEGIN

    SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;

    IF badge.horizon_age IS NULL THEN
        RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.',
            badge.name,
            badge.id;
    END IF;

    PERFORM rating.precalc_bibs_by_copy(badge_id);

    SET LOCAL client_min_messages = error;
    DROP TABLE IF EXISTS precalc_bib_list;
    CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
        SELECT id FROM precalc_filter_bib_list
            INTERSECT
        SELECT id FROM precalc_bibs_by_copy_list;

    iint := EXTRACT(EPOCH FROM badge.importance_interval);
    IF badge.importance_age IS NOT NULL THEN
        iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT;
    END IF;

    -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE!
    iscale := COALESCE(badge.importance_scale, 1.0);

    RETURN QUERY
     SELECT bib,
            SUM( holds * GREATEST( iscale * (iage - hage), 1.0 ))
      FROM (
         SELECT f.id AS bib,
                (1 + EXTRACT(EPOCH FROM AGE(h.fulfillment_time)) / iint)::INT AS hage,
                COUNT(h.id)::INT AS holds
          FROM  action.hold_request h
                JOIN reporter.hold_request_record rhrr ON (rhrr.id = h.id)
                JOIN precalc_bib_list f ON (f.id = rhrr.bib_record)
          WHERE h.fulfillment_time >= NOW() - badge.horizon_age
                AND h.request_lib = ANY (badge.orgs)
          GROUP BY 1, 2
      ) x
      GROUP BY 1;
END;

DECLARE
    badge   rating.badge_with_orgs%ROWTYPE;
BEGIN

    SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;

    PERFORM rating.precalc_bibs_by_copy(badge_id);

    DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
        SELECT id FROM precalc_filter_bib_list
            INTERSECT
        SELECT id FROM precalc_bibs_by_copy_list
    );

    ANALYZE precalc_copy_filter_bib_list;

    RETURN QUERY
     SELECT cn.record AS bib,
            COUNT(DISTINCT m.hold)::NUMERIC / COUNT(DISTINCT cp.id)::NUMERIC
      FROM  asset.copy cp
            JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
            JOIN asset.copy_location cl ON (cl.id = cp.location)
            JOIN config.copy_status cs ON (cs.id = cp.status)
            JOIN asset.call_number cn ON (cn.id = cp.call_number)
            JOIN action.hold_copy_map m ON (m.target_copy = cp.id)
      WHERE cn.owning_lib = ANY (badge.orgs)
            AND cp.holdable IS TRUE
            AND cl.holdable IS TRUE
            AND cs.holdable IS TRUE
      GROUP BY 1;
END;

DECLARE
    badge   rating.badge_with_orgs%ROWTYPE;
    iage    INT     := 1;
    iint    INT     := NULL;
    iscale  NUMERIC := NULL;
BEGIN

    SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;

    IF badge.horizon_age IS NULL THEN
        RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.',
            badge.name,
            badge.id;
    END IF;

    PERFORM rating.precalc_bibs_by_copy(badge_id);

    SET LOCAL client_min_messages = error;
    DROP TABLE IF EXISTS precalc_bib_list;
    CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
        SELECT id FROM precalc_filter_bib_list
            INTERSECT
        SELECT id FROM precalc_bibs_by_copy_list;

    iint := EXTRACT(EPOCH FROM badge.importance_interval);
    IF badge.importance_age IS NOT NULL THEN
        iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT;
    END IF;

    -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE!
    iscale := COALESCE(badge.importance_scale, 1.0);

    RETURN QUERY
     SELECT bib,
            SUM( holds * GREATEST( iscale * (iage - hage), 1.0 ))
      FROM (
         SELECT f.id AS bib,
                (1 + EXTRACT(EPOCH FROM AGE(h.request_time)) / iint)::INT AS hage,
                COUNT(h.id)::INT AS holds
          FROM  action.hold_request h
                JOIN reporter.hold_request_record rhrr ON (rhrr.id = h.id)
                JOIN precalc_bib_list f ON (f.id = rhrr.bib_record)
          WHERE h.request_time >= NOW() - badge.horizon_age
                AND h.request_lib = ANY (badge.orgs)
          GROUP BY 1, 2
      ) x
      GROUP BY 1;
END;

DECLARE
    badge   rating.badge_with_orgs%ROWTYPE;
BEGIN

    SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;

    PERFORM rating.precalc_bibs_by_copy(badge_id);

    DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
        SELECT id FROM precalc_filter_bib_list
            INTERSECT
        SELECT id FROM precalc_bibs_by_copy_list
    );

    ANALYZE precalc_copy_filter_bib_list;

    RETURN QUERY
     SELECT cn.record AS bib,
            COUNT(DISTINCT m.hold)::NUMERIC / COUNT(DISTINCT cp.id)::NUMERIC
      FROM  asset.copy cp
            JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
            JOIN asset.call_number cn ON (cn.id = cp.call_number)
            JOIN action.hold_copy_map m ON (m.target_copy = cp.id)
      WHERE cn.owning_lib = ANY (badge.orgs)
      GROUP BY 1;
END;

DECLARE
    badge   rating.badge_with_orgs%ROWTYPE;
    iage    INT     := 1;
    iint    INT     := NULL;
    iscale  NUMERIC := NULL;
BEGIN

    SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;

    IF badge.horizon_age IS NULL THEN
        RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.',
            badge.name,
            badge.id;
    END IF;

    PERFORM rating.precalc_bibs_by_copy(badge_id);

    DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
        SELECT id FROM precalc_filter_bib_list
            INTERSECT
        SELECT id FROM precalc_bibs_by_copy_list
    );

    ANALYZE precalc_copy_filter_bib_list;

    iint := EXTRACT(EPOCH FROM badge.importance_interval);
    IF badge.importance_age IS NOT NULL THEN
        iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT;
    END IF;

    -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE!
    iscale := COALESCE(badge.importance_scale, 1.0);

    RETURN QUERY
     SELECT bib,
            SUM( uses * GREATEST( iscale * (iage - cage), 1.0 ))
      FROM (
         SELECT cn.record AS bib,
                (1 + EXTRACT(EPOCH FROM AGE(u.use_time)) / iint)::INT AS cage,
                COUNT(u.id)::INT AS uses
          FROM  action.in_house_use u
                JOIN precalc_copy_filter_bib_list cf ON (u.item = cf.copy)
                JOIN asset.copy cp ON (cp.id = u.item)
                JOIN asset.call_number cn ON (cn.id = cp.call_number)
          WHERE u.use_time >= NOW() - badge.horizon_age
                AND cn.owning_lib = ANY (badge.orgs)
          GROUP BY 1, 2
      ) x
      GROUP BY 1;
END;

DECLARE
    badge   rating.badge_with_orgs%ROWTYPE;
BEGIN

    SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;

    PERFORM rating.precalc_bibs_by_copy(badge_id);

    DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
        SELECT id FROM precalc_filter_bib_list
            INTERSECT
        SELECT id FROM precalc_bibs_by_copy_list
    );
    ANALYZE precalc_copy_filter_bib_list;

    -- Use circ rather than owning lib here as that means "on the shelf at..."
    RETURN QUERY
     SELECT f.id::INT AS bib,
            COUNT(DISTINCT cp.circ_lib)::NUMERIC
     FROM asset.copy cp
          JOIN precalc_copy_filter_bib_list f ON (cp.id = f.copy)
     WHERE cp.circ_lib = ANY (badge.orgs) GROUP BY 1;

END;

DECLARE
    badge   rating.badge_with_orgs%ROWTYPE;
BEGIN

    SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;

    PERFORM rating.precalc_bibs_by_copy(badge_id);

    DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
        SELECT id FROM precalc_filter_bib_list
            INTERSECT
        SELECT id FROM precalc_bibs_by_copy_list
    );

    ANALYZE precalc_copy_filter_bib_list;

    RETURN QUERY
     SELECT bib,
            SUM(COALESCE(circ_time,0))::NUMERIC / SUM(age)::NUMERIC
      FROM  (SELECT cn.record AS bib,
                    cp.id,
                    EXTRACT( EPOCH FROM AGE(cp.active_date) ) + 1 AS age,
                    SUM(  -- time copy spent circulating
                        EXTRACT(
                            EPOCH FROM
                            AGE(
                                COALESCE(circ.checkin_time, circ.stop_fines_time, NOW()),
                                circ.xact_start
                            )
                        )
                    )::NUMERIC AS circ_time
              FROM  asset.copy cp
                    JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
                    JOIN asset.call_number cn ON (cn.id = cp.call_number)
                    LEFT JOIN action.all_circulation_slim circ ON (
                        circ.target_copy = cp.id
                        AND stop_fines NOT IN (
                            'LOST',
                            'LONGOVERDUE',
                            'CLAIMSRETURNED',
                            'LONGOVERDUE'
                        )
                        AND NOT (
                            checkin_time IS NULL AND
                            stop_fines = 'MAXFINES'
                        )
                    )
              WHERE cn.owning_lib = ANY (badge.orgs)
                    AND cp.active_date IS NOT NULL
                    -- Next line requires that copies with no circs (circ.id IS NULL) also not be deleted
                    AND ((circ.id IS NULL AND NOT cp.deleted) OR circ.id IS NOT NULL)
              GROUP BY 1,2,3
            ) x
      GROUP BY 1;
END;

DECLARE
    cnt     INT := 0;
    afilter TEXT;
BEGIN

    SET LOCAL client_min_messages = error;
    DROP TABLE IF EXISTS precalc_attr_filter_bib_list;
    IF attr_filter IS NOT NULL THEN
        afilter := metabib.compile_composite_attr(attr_filter);
        CREATE TEMP TABLE precalc_attr_filter_bib_list ON COMMIT DROP AS
            SELECT source AS id FROM metabib.record_attr_vector_list
            WHERE vlist @@ metabib.compile_composite_attr(attr_filter);
    ELSE
        CREATE TEMP TABLE precalc_attr_filter_bib_list ON COMMIT DROP AS
            SELECT source AS id FROM metabib.record_attr_vector_list;
    END IF;

    SELECT count(*) INTO cnt FROM precalc_attr_filter_bib_list;
    RETURN cnt;
END;

DECLARE
    cnt         INT     := 0;
    badge_row   rating.badge_with_orgs%ROWTYPE;
    base        TEXT;
    whr         TEXT;
BEGIN

    SELECT * INTO badge_row FROM rating.badge_with_orgs WHERE id = badge_id;

    SET LOCAL client_min_messages = error;
    DROP TABLE IF EXISTS precalc_bibs_by_copy_list;
    CREATE TEMP TABLE precalc_bibs_by_copy_list ON COMMIT DROP AS
        SELECT  DISTINCT cn.record AS id
          FROM  asset.call_number cn
                JOIN asset.copy cp ON (cp.call_number = cn.id AND NOT cp.deleted)
                JOIN precalc_copy_filter_bib_list f ON (cp.id = f.copy)
          WHERE cn.owning_lib = ANY (badge_row.orgs)
                AND NOT cn.deleted;

    SELECT count(*) INTO cnt FROM precalc_bibs_by_copy_list;
    RETURN cnt;
END;

DECLARE
    cnt         INT     := 0;
BEGIN

    PERFORM rating.precalc_bibs_by_copy(badge_id);
    PERFORM rating.precalc_bibs_by_uri(badge_id);

    SET LOCAL client_min_messages = error;
    DROP TABLE IF EXISTS precalc_bibs_by_copy_or_uri_list;
    CREATE TEMP TABLE precalc_bibs_by_copy_or_uri_list ON COMMIT DROP AS
        SELECT id FROM precalc_bibs_by_copy_list
            UNION
        SELECT id FROM precalc_bibs_by_uri_list;

    SELECT count(*) INTO cnt FROM precalc_bibs_by_copy_or_uri_list;
    RETURN cnt;
END;

DECLARE
    cnt         INT     := 0;
    badge_row   rating.badge_with_orgs%ROWTYPE;
BEGIN

    SELECT * INTO badge_row FROM rating.badge_with_orgs WHERE id = badge_id;

    SET LOCAL client_min_messages = error;
    DROP TABLE IF EXISTS precalc_bibs_by_uri_list;
    CREATE TEMP TABLE precalc_bibs_by_uri_list ON COMMIT DROP AS
        SELECT  DISTINCT record AS id
          FROM  asset.call_number cn
                JOIN asset.uri_call_number_map urim ON (urim.call_number = cn.id)
                JOIN asset.uri uri ON (urim.uri = uri.id AND uri.active)
          WHERE cn.owning_lib = ANY (badge_row.orgs)
                AND cn.label = '##URI##'
                AND NOT cn.deleted;

    SELECT count(*) INTO cnt FROM precalc_bibs_by_uri_list;
    RETURN cnt;
END;

DECLARE
    cnt     INT     := 0;
BEGIN

    SET LOCAL client_min_messages = error;
    DROP TABLE IF EXISTS precalc_circ_mod_filter_bib_list;
    IF cm IS NOT NULL THEN
        CREATE TEMP TABLE precalc_circ_mod_filter_bib_list ON COMMIT DROP AS
            SELECT  cn.record AS id,
                    cp.id AS copy
              FROM  asset.call_number cn
                    JOIN asset.copy cp ON (cn.id = cp.call_number)
              WHERE cp.circ_modifier = cm
                    AND NOT cp.deleted;
    ELSE
        CREATE TEMP TABLE precalc_circ_mod_filter_bib_list ON COMMIT DROP AS
            SELECT  cn.record AS id,
                    cp.id AS copy
              FROM  asset.call_number cn
                    JOIN asset.copy cp ON (cn.id = cp.call_number)
              WHERE NOT cp.deleted;
    END IF;

    SELECT count(*) INTO cnt FROM precalc_circ_mod_filter_bib_list;
    RETURN cnt;
END;

DECLARE
    cnt     INT     := 0;
BEGIN

    SET LOCAL client_min_messages = error;
    DROP TABLE IF EXISTS precalc_location_filter_bib_list;
    IF loc IS NOT NULL THEN
        CREATE TEMP TABLE precalc_location_filter_bib_list ON COMMIT DROP AS
            SELECT  cn.record AS id,
                    cp.id AS copy
              FROM  asset.call_number cn
                    JOIN asset.copy cp ON (cn.id = cp.call_number)
                    JOIN asset.copy_location_group_map lg ON (cp.location = lg.location)
              WHERE lg.lgroup = loc
                    AND NOT cp.deleted;
    ELSE
        CREATE TEMP TABLE precalc_location_filter_bib_list ON COMMIT DROP AS
            SELECT  cn.record AS id,
                    cp.id AS copy
              FROM  asset.call_number cn
                    JOIN asset.copy cp ON (cn.id = cp.call_number)
              WHERE NOT cp.deleted;
    END IF;

    SELECT count(*) INTO cnt FROM precalc_location_filter_bib_list;
    RETURN cnt;
END;

DECLARE
    cnt     INT     := 0;
BEGIN

    SET LOCAL client_min_messages = error;
    DROP TABLE IF EXISTS precalc_src_filter_bib_list;
    IF src IS NOT NULL THEN
        CREATE TEMP TABLE precalc_src_filter_bib_list ON COMMIT DROP AS
            SELECT id FROM biblio.record_entry
            WHERE source = src AND NOT deleted;
    ELSE
        CREATE TEMP TABLE precalc_src_filter_bib_list ON COMMIT DROP AS
            SELECT id FROM biblio.record_entry
            WHERE id > 0 AND NOT deleted;
    END IF;

    SELECT count(*) INTO cnt FROM precalc_src_filter_bib_list;
    RETURN cnt;
END;

DECLARE
    badge_row           rating.badge%ROWTYPE;
    param           rating.popularity_parameter%ROWTYPE;
BEGIN
    SET LOCAL client_min_messages = error;

    -- Find what we're doing
    SELECT * INTO badge_row FROM rating.badge WHERE id = badge_id;
    SELECT * INTO param FROM rating.popularity_parameter WHERE id = badge_row.popularity_parameter;

    -- Calculate the filtered bib set, or all bibs if none
    PERFORM rating.precalc_attr_filter(badge_row.attr_filter);
    PERFORM rating.precalc_src_filter(badge_row.src_filter);
    PERFORM rating.precalc_circ_mod_filter(badge_row.circ_mod_filter);
    PERFORM rating.precalc_location_filter(badge_row.loc_grp_filter);

    -- Bring the bib-level filter lists together
    DROP TABLE IF EXISTS precalc_bib_filter_bib_list;
    CREATE TEMP TABLE precalc_bib_filter_bib_list ON COMMIT DROP AS
        SELECT id FROM precalc_attr_filter_bib_list
            INTERSECT
        SELECT id FROM precalc_src_filter_bib_list;

    -- Bring the copy-level filter lists together. We're keeping this for bib_by_copy filtering later.
    DROP TABLE IF EXISTS precalc_copy_filter_bib_list;
    CREATE TEMP TABLE precalc_copy_filter_bib_list ON COMMIT DROP AS
        SELECT id, copy FROM precalc_circ_mod_filter_bib_list
            INTERSECT
        SELECT id, copy FROM precalc_location_filter_bib_list;

    -- Bring the collapsed filter lists together
    DROP TABLE IF EXISTS precalc_filter_bib_list;
    CREATE TEMP TABLE precalc_filter_bib_list ON COMMIT DROP AS
        SELECT id FROM precalc_bib_filter_bib_list
            INTERSECT
        SELECT id FROM precalc_copy_filter_bib_list;

    CREATE INDEX precalc_filter_bib_list_idx
        ON precalc_filter_bib_list (id);

    IF setup_only THEN
        RETURN;
    END IF;

    -- If it's a fixed-rating badge, just do it ...
    IF badge_row.fixed_rating IS NOT NULL THEN
        DELETE FROM rating.record_badge_score WHERE badge = badge_id;
        EXECUTE $e$
            INSERT INTO rating.record_badge_score (record, badge, score)
                SELECT record, $1, $2 FROM $e$ || param.func || $e$($1)$e$
        USING badge_id, badge_row.fixed_rating;

        UPDATE rating.badge SET last_calc = NOW() WHERE id = badge_id;

        RETURN;
    END IF;
    -- else, calculate!

    -- Make a session-local scratchpad for calculating scores
    CREATE TEMP TABLE record_score_scratchpad (
        bib     BIGINT,
        value   NUMERIC
    ) ON COMMIT DROP;

    -- Gather raw values
    EXECUTE $e$
        INSERT INTO record_score_scratchpad (bib, value)
            SELECT * FROM $e$ || param.func || $e$($1)$e$
    USING badge_id;

    IF badge_row.discard > 0 OR badge_row.percentile IS NOT NULL THEN
        -- To speed up discard-common
        CREATE INDEX record_score_scratchpad_score_idx ON record_score_scratchpad (value);
        ANALYZE record_score_scratchpad;
    END IF;

    IF badge_row.discard > 0 THEN -- Remove common low values (trim the long tail)
        DELETE FROM record_score_scratchpad WHERE value IN (
            SELECT DISTINCT value FROM record_score_scratchpad ORDER BY value LIMIT badge_row.discard
        );
    END IF;

    IF badge_row.percentile IS NOT NULL THEN -- Cut population down to exceptional records
        DELETE FROM record_score_scratchpad WHERE value <= (
            SELECT value FROM (
                SELECT  value,
                        CUME_DIST() OVER (ORDER BY value) AS p
                  FROM  record_score_scratchpad
            ) x WHERE p < badge_row.percentile / 100.0 ORDER BY p DESC LIMIT 1
        );
    END IF;


    -- And, finally, push new data in
    DELETE FROM rating.record_badge_score WHERE badge = badge_id;
    INSERT INTO rating.record_badge_score (badge, record, score)
        SELECT  badge_id,
                bib,
                GREATEST(ROUND((CUME_DIST() OVER (ORDER BY value)) * 5), 1) AS value
          FROM  record_score_scratchpad;

    DROP TABLE record_score_scratchpad;

    -- Now, finally-finally, mark the badge as recalculated
    UPDATE rating.badge SET last_calc = NOW() WHERE id = badge_id;

    RETURN;
END;