
BEGIN;

-- Create these so that the queries in the UDFs will validate
CREATE TEMP TABLE precalc_filter_bib_list (
    id  BIGINT
) ON COMMIT DROP;

CREATE TEMP TABLE precalc_bib_filter_bib_list (
    id  BIGINT
) ON COMMIT DROP;

CREATE TEMP TABLE precalc_src_filter_bib_list (
    id  BIGINT
) ON COMMIT DROP;

CREATE TEMP TABLE precalc_copy_filter_bib_list (
    id  BIGINT,
    copy  BIGINT
) ON COMMIT DROP;

CREATE TEMP TABLE precalc_circ_mod_filter_bib_list (
    id  BIGINT,
    copy  BIGINT
) ON COMMIT DROP;

CREATE TEMP TABLE precalc_location_filter_bib_list (
    id  BIGINT,
    copy  BIGINT
) ON COMMIT DROP;

CREATE TEMP TABLE precalc_attr_filter_bib_list (
    id  BIGINT
) ON COMMIT DROP;

CREATE TEMP TABLE precalc_bibs_by_copy_list (
    id  BIGINT
) ON COMMIT DROP;

CREATE TEMP TABLE precalc_bibs_by_uri_list (
    id  BIGINT
) ON COMMIT DROP;

CREATE TEMP TABLE precalc_bibs_by_copy_or_uri_list (
    id  BIGINT
) ON COMMIT DROP;

CREATE TEMP TABLE precalc_bib_list (
    id  BIGINT
) ON COMMIT DROP;

-- rhrr needs to be a real table, so it can be fast. To that end, we use
-- a materialized view updated via a trigger.

DROP VIEW reporter.hold_request_record;

CREATE TABLE reporter.hold_request_record  AS
SELECT  id,
        target,
        hold_type,
        CASE
                WHEN hold_type = 'T'
                        THEN target
                WHEN hold_type = 'I'
                        THEN (SELECT ssub.record_entry FROM serial.subscription ssub JOIN serial.issuance si ON (si.subscription = ssub.id) WHERE si.id = ahr.target)
                WHEN hold_type = 'V'
                        THEN (SELECT cn.record FROM asset.call_number cn WHERE cn.id = ahr.target)
                WHEN hold_type IN ('C','R','F')
                        THEN (SELECT cn.record FROM asset.call_number cn JOIN asset.copy cp ON (cn.id = cp.call_number) WHERE cp.id = ahr.target)
                WHEN hold_type = 'M'
                        THEN (SELECT mr.master_record FROM metabib.metarecord mr WHERE mr.id = ahr.target)
                WHEN hold_type = 'P'
                        THEN (SELECT bmp.record FROM biblio.monograph_part bmp WHERE bmp.id = ahr.target)
        END AS bib_record
  FROM  action.hold_request ahr;

CREATE UNIQUE INDEX reporter_hold_request_record_pkey_idx ON reporter.hold_request_record (id);
CREATE INDEX reporter_hold_request_record_bib_record_idx ON reporter.hold_request_record (bib_record);

ALTER TABLE reporter.hold_request_record ADD PRIMARY KEY USING INDEX reporter_hold_request_record_pkey_idx;

CREATE FUNCTION reporter.hold_request_record_mapper () RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO reporter.hold_request_record (id, target, hold_type, bib_record)
        SELECT  NEW.id,
                NEW.target,
                NEW.hold_type,
                CASE
                    WHEN NEW.hold_type = 'T'
                        THEN NEW.target
                    WHEN NEW.hold_type = 'I'
                        THEN (SELECT ssub.record_entry FROM serial.subscription ssub JOIN serial.issuance si ON (si.subscription = ssub.id) WHERE si.id = NEW.target)
                    WHEN NEW.hold_type = 'V'
                        THEN (SELECT cn.record FROM asset.call_number cn WHERE cn.id = NEW.target)
                    WHEN NEW.hold_type IN ('C','R','F')
                        THEN (SELECT cn.record FROM asset.call_number cn JOIN asset.copy cp ON (cn.id = cp.call_number) WHERE cp.id = NEW.target)
                    WHEN NEW.hold_type = 'M'
                        THEN (SELECT mr.master_record FROM metabib.metarecord mr WHERE mr.id = NEW.target)
                    WHEN NEW.hold_type = 'P'
                        THEN (SELECT bmp.record FROM biblio.monograph_part bmp WHERE bmp.id = NEW.target)
                END AS bib_record;
    ELSIF TG_OP = 'UPDATE' AND (OLD.target <> NEW.target OR OLD.hold_type <> NEW.hold_type) THEN
        UPDATE  reporter.hold_request_record
          SET   target = NEW.target,
                hold_type = NEW.hold_type,
                bib_record = CASE
                    WHEN NEW.hold_type = 'T'
                        THEN NEW.target
                    WHEN NEW.hold_type = 'I'
                        THEN (SELECT ssub.record_entry FROM serial.subscription ssub JOIN serial.issuance si ON (si.subscription = ssub.id) WHERE si.id = NEW.target)
                    WHEN NEW.hold_type = 'V'
                        THEN (SELECT cn.record FROM asset.call_number cn WHERE cn.id = NEW.target)
                    WHEN NEW.hold_type IN ('C','R','F')
                        THEN (SELECT cn.record FROM asset.call_number cn JOIN asset.copy cp ON (cn.id = cp.call_number) WHERE cp.id = NEW.target)
                    WHEN NEW.hold_type = 'M'
                        THEN (SELECT mr.master_record FROM metabib.metarecord mr WHERE mr.id = NEW.target)
                    WHEN NEW.hold_type = 'P'
                        THEN (SELECT bmp.record FROM biblio.monograph_part bmp WHERE bmp.id = NEW.target)
                END;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER reporter_hold_request_record_trigger AFTER INSERT OR UPDATE ON action.hold_request
    FOR EACH ROW EXECUTE PROCEDURE reporter.hold_request_record_mapper();

CREATE SCHEMA rating;

CREATE TABLE rating.popularity_parameter (
    id          INT     PRIMARY KEY,
    name        TEXT    NOT NULL UNIQUE, -- i18n
    description TEXT,
    func        TEXT,
    require_horizon     BOOL    NOT NULL DEFAULT FALSE,
    require_importance  BOOL    NOT NULL DEFAULT FALSE,
    require_percentile  BOOL    NOT NULL DEFAULT FALSE
);

INSERT INTO rating.popularity_parameter (id,name,func,require_horizon,require_importance,require_percentile) VALUES
    (1,'Holds Filled Over Time','rating.holds_filled_over_time',TRUE,FALSE,TRUE),
    (2,'Holds Requested Over Time','rating.holds_placed_over_time',TRUE,FALSE,TRUE),
    (3,'Current Hold Count','rating.current_hold_count',FALSE,FALSE,TRUE),
    (4,'Circulations Over Time','rating.circs_over_time',TRUE,FALSE,TRUE),
    (5,'Current Circulation Count','rating.current_circ_count',FALSE,FALSE,TRUE),
    (6,'Out/Total Ratio','rating.checked_out_total_ratio',FALSE,FALSE,TRUE),
    (7,'Holds/Total Ratio','rating.holds_total_ratio',FALSE,FALSE,TRUE),
    (8,'Holds/Holdable Ratio','rating.holds_holdable_ratio',FALSE,FALSE,TRUE),
    (9,'Percent of Time Circulating','rating.percent_time_circulating',FALSE,FALSE,TRUE),
    (10,'Bibliographic Record Age (days, newer is better)','rating.bib_record_age',FALSE,FALSE,TRUE),
    (11,'Publication Age (days, newer is better)','rating.bib_pub_age',FALSE,FALSE,TRUE),
    (12,'On-line Bib has attributes','rating.generic_fixed_rating_by_uri',FALSE,FALSE,FALSE),
    (13,'Bib has attributes and copies','rating.generic_fixed_rating_by_copy',FALSE,FALSE,FALSE),
    (14,'Bib has attributes and copies or URIs','rating.generic_fixed_rating_by_copy_or_uri',FALSE,FALSE,FALSE),
    (15,'Bib has attributes','rating.generic_fixed_rating_global',FALSE,FALSE,FALSE);

CREATE TABLE rating.badge (
    id                      SERIAL      PRIMARY KEY,
    name                    TEXT        NOT NULL,
    description             TEXT,
    scope                   INT         NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
    weight                  INT         NOT NULL DEFAULT 1,
    horizon_age             INTERVAL,
    importance_age          INTERVAL,
    importance_interval     INTERVAL    NOT NULL DEFAULT '1 day',
    importance_scale        NUMERIC     CHECK (importance_scale IS NULL OR importance_scale > 0.0),
    recalc_interval         INTERVAL    NOT NULL DEFAULT '1 month',
    attr_filter             TEXT,
    src_filter              INT         REFERENCES config.bib_source (id) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
    circ_mod_filter         TEXT        REFERENCES config.circ_modifier (code) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
    loc_grp_filter          INT         REFERENCES asset.copy_location_group (id) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
    popularity_parameter    INT         NOT NULL REFERENCES rating.popularity_parameter (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
    fixed_rating            INT         CHECK (fixed_rating IS NULL OR fixed_rating BETWEEN -5 AND 5),
    percentile              NUMERIC     CHECK (percentile IS NULL OR (percentile >= 50.0 AND percentile < 100.0)),
    discard                 INT         NOT NULL DEFAULT 0, 
    last_calc               TIMESTAMPTZ,
    CONSTRAINT unique_name_scope UNIQUE (name,scope)
);

CREATE TABLE rating.record_badge_score (
    id          BIGSERIAL   PRIMARY KEY,
    record      BIGINT      NOT NULL REFERENCES biblio.record_entry (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
    badge       INT         NOT NULL REFERENCES rating.badge (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
    score       INT         NOT NULL CHECK (score BETWEEN -5 AND 5),
    CONSTRAINT unique_record_badge UNIQUE (record,badge)
);
CREATE INDEX record_badge_score_badge_idx ON rating.record_badge_score (badge);
CREATE INDEX record_badge_score_record_idx ON rating.record_badge_score (record);

CREATE OR REPLACE VIEW rating.badge_with_orgs AS
    WITH    org_scope AS (
                SELECT  id,
                        array_agg(tree) AS orgs
                  FROM  (SELECT id,
                                (actor.org_unit_descendants(id)).id AS tree
                          FROM  actor.org_unit
                        ) x
                  GROUP BY 1
            )
    SELECT  b.*,
            s.orgs
      FROM  rating.badge b
            JOIN org_scope s ON (b.scope = s.id);

CREATE OR REPLACE FUNCTION rating.precalc_src_filter(src INT)
    RETURNS INT AS $f$
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;
$f$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION rating.precalc_circ_mod_filter(cm TEXT)
    RETURNS INT AS $f$
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;
$f$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION rating.precalc_location_filter(loc INT)
    RETURNS INT AS $f$
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;
$f$ LANGUAGE PLPGSQL;

-- all or limited...
CREATE OR REPLACE FUNCTION rating.precalc_attr_filter(attr_filter TEXT)
    RETURNS INT AS $f$
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;
$f$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION rating.precalc_bibs_by_copy(badge_id INT)
    RETURNS INT AS $f$
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;
$f$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION rating.precalc_bibs_by_uri(badge_id INT)
    RETURNS INT AS $f$
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;
$f$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION rating.precalc_bibs_by_copy_or_uri(badge_id INT)
    RETURNS INT AS $f$
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;
$f$ LANGUAGE PLPGSQL;


CREATE OR REPLACE FUNCTION rating.recalculate_badge_score ( badge_id INT, setup_only BOOL DEFAULT FALSE ) RETURNS VOID AS $f$
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;
$f$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION rating.holds_filled_over_time(badge_id INT)
    RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
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;
$f$ LANGUAGE PLPGSQL STRICT;

CREATE OR REPLACE FUNCTION rating.holds_placed_over_time(badge_id INT)
    RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
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;
$f$ LANGUAGE PLPGSQL STRICT;

CREATE OR REPLACE FUNCTION rating.current_hold_count(badge_id INT)
    RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
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;
$f$ LANGUAGE PLPGSQL STRICT;

CREATE OR REPLACE FUNCTION rating.circs_over_time(badge_id INT)
    RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
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;
$f$ LANGUAGE PLPGSQL STRICT;

CREATE OR REPLACE FUNCTION rating.current_circ_count(badge_id INT)
    RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
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;
$f$ LANGUAGE PLPGSQL STRICT;

CREATE OR REPLACE FUNCTION rating.checked_out_total_ratio(badge_id INT)
    RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
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;
$f$ LANGUAGE PLPGSQL STRICT;

CREATE OR REPLACE FUNCTION rating.holds_total_ratio(badge_id INT)
    RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
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;
$f$ LANGUAGE PLPGSQL STRICT;

CREATE OR REPLACE FUNCTION rating.holds_holdable_ratio(badge_id INT)
    RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
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;
$f$ LANGUAGE PLPGSQL STRICT;

CREATE OR REPLACE FUNCTION rating.bib_record_age(badge_id INT)
    RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
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;
$f$ LANGUAGE PLPGSQL STRICT;

CREATE OR REPLACE FUNCTION rating.bib_pub_age(badge_id INT)
    RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
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;
$f$ LANGUAGE PLPGSQL STRICT;

CREATE OR REPLACE FUNCTION rating.percent_time_circulating(badge_id INT)
    RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
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 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;
$f$ LANGUAGE PLPGSQL STRICT;

CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_by_copy(badge_id INT)
    RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
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;
$f$ LANGUAGE PLPGSQL STRICT;

CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_by_uri(badge_id INT)
    RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
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;
$f$ LANGUAGE PLPGSQL STRICT;

CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_by_copy_or_uri(badge_id INT)
    RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
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;
$f$ LANGUAGE PLPGSQL STRICT;

CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_global(badge_id INT)
    RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
BEGIN
    RETURN QUERY
        SELECT id, 1.0 FROM precalc_bib_filter_bib_list;
END;
$f$ LANGUAGE PLPGSQL STRICT;

CREATE INDEX hold_fulfillment_time_idx ON action.hold_request (fulfillment_time) WHERE fulfillment_time IS NOT NULL;
CREATE INDEX hold_request_time_idx ON action.hold_request (request_time);

COMMIT;

