-- Before starting the transaction: drop some constraints that
-- may or may not exist.

CREATE OR REPLACE FUNCTION oils_text_as_bytea (TEXT) RETURNS BYTEA AS $_$
    SELECT CAST(REGEXP_REPLACE(UPPER($1), $$\\$$, $$\\\\$$, 'g') AS BYTEA);
$_$ LANGUAGE SQL IMMUTABLE;

DROP INDEX asset.asset_call_number_upper_label_id_owning_lib_idx;
CREATE INDEX asset_call_number_upper_label_id_owning_lib_idx ON asset.call_number (oils_text_as_bytea(label),id,owning_lib);

\qecho Before starting the transaction: drop some constraints.
\qecho If a DROP fails because the constraint doesn't exist, ignore the failure.

ALTER TABLE permission.grp_perm_map        DROP CONSTRAINT grp_perm_map_perm_fkey;
ALTER TABLE permission.usr_perm_map        DROP CONSTRAINT usr_perm_map_perm_fkey;
ALTER TABLE permission.usr_object_perm_map DROP CONSTRAINT usr_object_perm_map_perm_fkey;
ALTER TABLE booking.resource_type          DROP CONSTRAINT brt_name_or_record_once_per_owner;
ALTER TABLE booking.resource_type          DROP CONSTRAINT brt_name_once_per_owner;

\qecho Beginning the transaction now

BEGIN;

-- Highest-numbered individual upgrade script incorporated herein:

INSERT INTO config.upgrade_log (version) VALUES ('0445');

-- Remove some uses of the connectby() function from the tablefunc contrib module
CREATE OR REPLACE FUNCTION actor.org_unit_descendants( INT, INT ) RETURNS SETOF actor.org_unit AS $$
    WITH RECURSIVE descendant_depth AS (
        SELECT  ou.id,
                ou.parent_ou,
                out.depth
          FROM  actor.org_unit ou
                JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
                JOIN anscestor_depth ad ON (ad.id = ou.id)
          WHERE ad.depth = $2
            UNION ALL
        SELECT  ou.id,
                ou.parent_ou,
                out.depth
          FROM  actor.org_unit ou
                JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
                JOIN descendant_depth ot ON (ot.id = ou.parent_ou)
    ), anscestor_depth AS (
        SELECT  ou.id,
                ou.parent_ou,
                out.depth
          FROM  actor.org_unit ou
                JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
          WHERE ou.id = $1
            UNION ALL
        SELECT  ou.id,
                ou.parent_ou,
                out.depth
          FROM  actor.org_unit ou
                JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
                JOIN anscestor_depth ot ON (ot.parent_ou = ou.id)
    ) SELECT ou.* FROM actor.org_unit ou JOIN descendant_depth USING (id);
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION actor.org_unit_descendants( INT ) RETURNS SETOF actor.org_unit AS $$
    WITH RECURSIVE descendant_depth AS (
        SELECT  ou.id,
                ou.parent_ou,
                out.depth
          FROM  actor.org_unit ou
                JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
          WHERE ou.id = $1
            UNION ALL
        SELECT  ou.id,
                ou.parent_ou,
                out.depth
          FROM  actor.org_unit ou
                JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
                JOIN descendant_depth ot ON (ot.id = ou.parent_ou)
    ) SELECT ou.* FROM actor.org_unit ou JOIN descendant_depth USING (id);
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION actor.org_unit_ancestors( INT ) RETURNS SETOF actor.org_unit AS $$
    WITH RECURSIVE anscestor_depth AS (
        SELECT  ou.id,
                ou.parent_ou
          FROM  actor.org_unit ou
          WHERE ou.id = $1
            UNION ALL
        SELECT  ou.id,
                ou.parent_ou
          FROM  actor.org_unit ou
                JOIN anscestor_depth ot ON (ot.parent_ou = ou.id)
    ) SELECT ou.* FROM actor.org_unit ou JOIN anscestor_depth USING (id);
$$ LANGUAGE SQL;

-- Support merge template buckets
INSERT INTO container.biblio_record_entry_bucket_type (code,label) VALUES ('template_merge','Template Merge Container');

-- Recreate one of the constraints that we just dropped,
-- under a different name:

ALTER TABLE booking.resource_type
	ADD CONSTRAINT brt_name_and_record_once_per_owner UNIQUE(owner, name, record);

-- Now upgrade permission.perm_list.  This is fairly complicated.

-- Add ON UPDATE CASCADE to some foreign keys so that, when we renumber the
-- permissions, the dependents will follow and stay in sync:

ALTER TABLE permission.grp_perm_map ADD CONSTRAINT grp_perm_map_perm_fkey FOREIGN KEY (perm)
    REFERENCES permission.perm_list (id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE permission.usr_perm_map ADD CONSTRAINT usr_perm_map_perm_fkey FOREIGN KEY (perm)
    REFERENCES permission.perm_list (id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE permission.usr_object_perm_map ADD CONSTRAINT usr_object_perm_map_perm_fkey FOREIGN KEY (perm)
    REFERENCES permission.perm_list (id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED;

UPDATE permission.perm_list
    SET code = 'UPDATE_ORG_UNIT_SETTING.credit.payments.allow'
    WHERE code = 'UPDATE_ORG_UNIT_SETTING.global.credit.allow';

-- The following UPDATES were originally in an individual upgrade script, but should
-- no longer be necessary now that the foreign key has an ON UPDATE CASCADE clause.
-- We retain the UPDATES here, commented out, as historical relics.

-- UPDATE permission.grp_perm_map SET perm = perm + 1000 WHERE perm NOT IN ( SELECT id FROM permission.perm_list );
-- UPDATE permission.usr_perm_map SET perm = perm + 1000 WHERE perm NOT IN ( SELECT id FROM permission.perm_list );

-- Spelling correction
UPDATE permission.perm_list SET code = 'ADMIN_RECURRING_FINE_RULE' WHERE code = 'ADMIN_RECURING_FINE_RULE';

-- Now we engage in a Great Renumbering of the permissions in permission.perm_list,
-- in order to clean up accumulated cruft.

-- The first step is to establish some triggers so that, when we change the id of a permission,
-- the associated translations are updated accordingly.

CREATE OR REPLACE FUNCTION oils_i18n_update_apply(old_ident TEXT, new_ident TEXT, hint TEXT) RETURNS VOID AS $_$
BEGIN

    EXECUTE $$
        UPDATE  config.i18n_core
          SET   identity_value = $$ || quote_literal( new_ident ) || $$ 
          WHERE fq_field LIKE '$$ || hint || $$.%' 
                AND identity_value = $$ || quote_literal( old_ident ) || $$;$$;

    RETURN;

END;
$_$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION oils_i18n_id_tracking(/* hint */) RETURNS TRIGGER AS $_$
BEGIN
    PERFORM oils_i18n_update_apply( OLD.id::TEXT, NEW.id::TEXT, TG_ARGV[0]::TEXT );
    RETURN NEW;
END;
$_$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION oils_i18n_code_tracking(/* hint */) RETURNS TRIGGER AS $_$
BEGIN
    PERFORM oils_i18n_update_apply( OLD.code::TEXT, NEW.code::TEXT, TG_ARGV[0]::TEXT );
    RETURN NEW;
END;
$_$ LANGUAGE PLPGSQL;


CREATE TRIGGER maintain_perm_i18n_tgr
    AFTER UPDATE ON permission.perm_list
    FOR EACH ROW EXECUTE PROCEDURE oils_i18n_id_tracking('ppl');

-- Next, create a new table as a convenience for sloshing data back and forth,
-- and for recording which permission went where.  It looks just like
-- permission.perm_list, but with two extra columns: one for the old id, and one to
-- distinguish between predefined permissions and non-predefined permissions.

-- This table is, in effect, a temporary table, because we can drop it once the
-- upgrade is complete.  It is not technically temporary as far as PostgreSQL is
-- concerned, because we don't want it to disappear at the end of the session.
-- We keep it around so that we have a map showing the old id and the new id for
-- each permission.  However there is no IDL entry for it, nor is it defined
-- in the base sql files.

CREATE TABLE permission.temp_perm (
	id          INT        PRIMARY KEY,
	code        TEXT       UNIQUE,
	description TEXT,
	old_id      INT,
	predefined  BOOL       NOT NULL DEFAULT TRUE
);

-- Populate the temp table with a definitive set of predefined permissions,
-- hard-coding the ids.

-- The first set of permissions is derived from the database, as loaded in a
-- loaded 1.6.1 database, plus a few changes previously applied in this upgrade
-- script.  The second set is derived from the IDL -- permissions that are referenced
-- in <permacrud> elements but not defined in the database.

INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( -1, 'EVERYTHING',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 1, 'OPAC_LOGIN',
     'Allow a user to log in to the OPAC' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 2, 'STAFF_LOGIN',
     'Allow a user to log in to the staff client' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 3, 'MR_HOLDS',
     'Allow a user to create a metarecord holds' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 4, 'TITLE_HOLDS',
     'Allow a user to place a hold at the title level' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 5, 'VOLUME_HOLDS',
     'Allow a user to place a volume level hold' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 6, 'COPY_HOLDS',
     'Allow a user to place a hold on a specific copy' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 7, 'REQUEST_HOLDS',
     'Allow a user to create holds for another user (if true, we still check to make sure they have permission to make the type of hold they are requesting, for example, COPY_HOLDS)' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 8, 'REQUEST_HOLDS_OVERRIDE',
     '* no longer applicable' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 9, 'VIEW_HOLD',
     'Allow a user to view another user''s holds' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 10, 'DELETE_HOLDS',
     '* no longer applicable' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 11, 'UPDATE_HOLD',
     'Allow a user to update another user''s hold' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 12, 'RENEW_CIRC',
     'Allow a user to renew items' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 13, 'VIEW_USER_FINES_SUMMARY',
     'Allow a user to view bill details' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 14, 'VIEW_USER_TRANSACTIONS',
     'Allow a user to see another user''s grocery or circulation transactions in the Bills Interface; duplicate of VIEW_TRANSACTION' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 15, 'UPDATE_MARC',
     'Allow a user to edit a MARC record' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 16, 'CREATE_MARC',
     'Allow a user to create new MARC records' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 17, 'IMPORT_MARC',
     'Allow a user to import a MARC record via the Z39.50 interface' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 18, 'CREATE_VOLUME',
     'Allow a user to create a volume' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 19, 'UPDATE_VOLUME',
     'Allow a user to edit volumes - needed for merging records. This is a duplicate of VOLUME_UPDATE; user must have both permissions at appropriate level to merge records.' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 20, 'DELETE_VOLUME',
     'Allow a user to delete a volume' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 21, 'CREATE_COPY',
     'Allow a user to create a new copy object' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 22, 'UPDATE_COPY',
     'Allow a user to edit a copy' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 23, 'DELETE_COPY',
     'Allow a user to delete a copy' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 24, 'RENEW_HOLD_OVERRIDE',
     'Allow a user to continue to renew an item even if it is required for a hold' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 25, 'CREATE_USER',
     'Allow a user to create another user' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 26, 'UPDATE_USER',
     'Allow a user to edit a user''s record' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 27, 'DELETE_USER',
     'Allow a user to mark a user as deleted' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 28, 'VIEW_USER',
     'Allow a user to view another user''s Patron Record' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 29, 'COPY_CHECKIN',
     'Allow a user to check in a copy' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 30, 'CREATE_TRANSIT',
     'Allow a user to place an item in transit' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 31, 'VIEW_PERMISSION',
     'Allow a user to view user permissions within the user permissions editor' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 32, 'CHECKIN_BYPASS_HOLD_FULFILL',
     '* no longer applicable' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 33, 'CREATE_PAYMENT',
     'Allow a user to record payments in the Billing Interface' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 34, 'SET_CIRC_LOST',
     'Allow a user to mark an item as ''lost''' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 35, 'SET_CIRC_MISSING',
     'Allow a user to mark an item as ''missing''' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 36, 'SET_CIRC_CLAIMS_RETURNED',
     'Allow a user to mark an item as ''claims returned''' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 37, 'CREATE_TRANSACTION',
     'Allow a user to create a new billable transaction' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 38, 'VIEW_TRANSACTION',
     'Allow a user may view another user''s transactions' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 39, 'CREATE_BILL',
     'Allow a user to create a new bill on a transaction' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 40, 'VIEW_CONTAINER',
     'Allow a user to view another user''s containers (buckets)' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 41, 'CREATE_CONTAINER',
     'Allow a user to create a new container for another user' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 42, 'UPDATE_ORG_UNIT',
     'Allow a user to change the settings for an organization unit' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 43, 'VIEW_CIRCULATIONS',
     'Allow a user to see what another user has checked out' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 44, 'DELETE_CONTAINER',
     'Allow a user to delete another user''s container' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 45, 'CREATE_CONTAINER_ITEM',
     'Allow a user to create a container item for another user' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 46, 'CREATE_USER_GROUP_LINK',
     'Allow a user to add other users to permission groups' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 47, 'REMOVE_USER_GROUP_LINK',
     'Allow a user to remove other users from permission groups' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 48, 'VIEW_PERM_GROUPS',
     'Allow a user to view other users'' permission groups' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 49, 'VIEW_PERMIT_CHECKOUT',
     'Allow a user to determine whether another user can check out an item' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 50, 'UPDATE_BATCH_COPY',
     'Allow a user to edit copies in batch' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 51, 'CREATE_PATRON_STAT_CAT',
     'User may create a new patron statistical category' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 52, 'CREATE_COPY_STAT_CAT',
     'User may create a copy statistical category' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 53, 'CREATE_PATRON_STAT_CAT_ENTRY',
     'User may create an entry in a patron statistical category' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 54, 'CREATE_COPY_STAT_CAT_ENTRY',
     'User may create an entry in a copy statistical category' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 55, 'UPDATE_PATRON_STAT_CAT',
     'User may update a patron statistical category' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 56, 'UPDATE_COPY_STAT_CAT',
     'User may update a copy statistical category' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 57, 'UPDATE_PATRON_STAT_CAT_ENTRY',
     'User may update an entry in a patron statistical category' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 58, 'UPDATE_COPY_STAT_CAT_ENTRY',
     'User may update an entry in a copy statistical category' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 59, 'CREATE_PATRON_STAT_CAT_ENTRY_MAP',
     'User may link another user to an entry in a statistical category' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 60, 'CREATE_COPY_STAT_CAT_ENTRY_MAP',
     'User may link a copy to an entry in a statistical category' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 61, 'DELETE_PATRON_STAT_CAT',
     'User may delete a patron statistical category' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 62, 'DELETE_COPY_STAT_CAT',
     'User may delete a copy statistical category' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 63, 'DELETE_PATRON_STAT_CAT_ENTRY',
     'User may delete an entry from a patron statistical category' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 64, 'DELETE_COPY_STAT_CAT_ENTRY',
     'User may delete an entry from a copy statistical category' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 65, 'DELETE_PATRON_STAT_CAT_ENTRY_MAP',
     'User may delete a patron statistical category entry map' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 66, 'DELETE_COPY_STAT_CAT_ENTRY_MAP',
     'User may delete a copy statistical category entry map' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 67, 'CREATE_NON_CAT_TYPE',
     'Allow a user to create a new non-cataloged item type' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 68, 'UPDATE_NON_CAT_TYPE',
     'Allow a user to update a non-cataloged item type' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 69, 'CREATE_IN_HOUSE_USE',
     'Allow a user to create a new in-house-use ' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 70, 'COPY_CHECKOUT',
     'Allow a user to check out a copy' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 71, 'CREATE_COPY_LOCATION',
     'Allow a user to create a new copy location' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 72, 'UPDATE_COPY_LOCATION',
     'Allow a user to update a copy location' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 73, 'DELETE_COPY_LOCATION',
     'Allow a user to delete a copy location' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 74, 'CREATE_COPY_TRANSIT',
     'Allow a user to create a transit_copy object for transiting a copy' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 75, 'COPY_TRANSIT_RECEIVE',
     'Allow a user to close out a transit on a copy' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 76, 'VIEW_HOLD_PERMIT',
     'Allow a user to see if another user has permission to place a hold on a given copy' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 77, 'VIEW_COPY_CHECKOUT_HISTORY',
     'Allow a user to view which users have checked out a given copy' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 78, 'REMOTE_Z3950_QUERY',
     'Allow a user to perform Z39.50 queries against remote servers' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 79, 'REGISTER_WORKSTATION',
     'Allow a user to register a new workstation' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 80, 'VIEW_COPY_NOTES',
     'Allow a user to view all notes attached to a copy' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 81, 'VIEW_VOLUME_NOTES',
     'Allow a user to view all notes attached to a volume' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 82, 'VIEW_TITLE_NOTES',
     'Allow a user to view all notes attached to a title' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 83, 'CREATE_COPY_NOTE',
     'Allow a user to create a new copy note' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 84, 'CREATE_VOLUME_NOTE',
     'Allow a user to create a new volume note' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 85, 'CREATE_TITLE_NOTE',
     'Allow a user to create a new title note' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 86, 'DELETE_COPY_NOTE',
     'Allow a user to delete another user''s copy notes' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 87, 'DELETE_VOLUME_NOTE',
     'Allow a user to delete another user''s volume note' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 88, 'DELETE_TITLE_NOTE',
     'Allow a user to delete another user''s title note' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 89, 'UPDATE_CONTAINER',
     'Allow a user to update another user''s container' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 90, 'CREATE_MY_CONTAINER',
     'Allow a user to create a container for themselves' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 91, 'VIEW_HOLD_NOTIFICATION',
     'Allow a user to view notifications attached to a hold' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 92, 'CREATE_HOLD_NOTIFICATION',
     'Allow a user to create new hold notifications' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 93, 'UPDATE_ORG_SETTING',
     'Allow a user to update an organization unit setting' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 94, 'OFFLINE_UPLOAD',
     'Allow a user to upload an offline script' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 95, 'OFFLINE_VIEW',
     'Allow a user to view uploaded offline script information' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 96, 'OFFLINE_EXECUTE',
     'Allow a user to execute an offline script batch' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 97, 'CIRC_OVERRIDE_DUE_DATE',
     'Allow a user to change the due date on an item to any date' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 98, 'CIRC_PERMIT_OVERRIDE',
     'Allow a user to bypass the circulation permit call for check out' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 99, 'COPY_IS_REFERENCE.override',
     'Allow a user to override the copy_is_reference event' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 100, 'VOID_BILLING',
     'Allow a user to void a bill' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 101, 'CIRC_CLAIMS_RETURNED.override',
     'Allow a user to check in or check out an item that has a status of ''claims returned''' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 102, 'COPY_BAD_STATUS.override',
     'Allow a user to check out an item in a non-circulatable status' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 103, 'COPY_ALERT_MESSAGE.override',
     'Allow a user to check in/out an item that has an alert message' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 104, 'COPY_STATUS_LOST.override',
     'Allow a user to remove the lost status from a copy' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 105, 'COPY_STATUS_MISSING.override',
     'Allow a user to change the missing status on a copy' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 106, 'ABORT_TRANSIT',
     'Allow a user to abort a copy transit if the user is at the transit destination or source' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 107, 'ABORT_REMOTE_TRANSIT',
     'Allow a user to abort a copy transit if the user is not at the transit source or dest' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 108, 'VIEW_ZIP_DATA',
     'Allow a user to query the ZIP code data method' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 109, 'CANCEL_HOLDS',
     'Allow a user to cancel holds' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 110, 'CREATE_DUPLICATE_HOLDS',
     'Allow a user to create duplicate holds (two or more holds on the same title)' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 111, 'actor.org_unit.closed_date.delete',
     'Allow a user to remove a closed date interval for a given location' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 112, 'actor.org_unit.closed_date.update',
     'Allow a user to update a closed date interval for a given location' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 113, 'actor.org_unit.closed_date.create',
     'Allow a user to create a new closed date for a location' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 114, 'DELETE_NON_CAT_TYPE',
     'Allow a user to delete a non cataloged type' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 115, 'money.collections_tracker.create',
     'Allow a user to put someone into collections' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 116, 'money.collections_tracker.delete',
     'Allow a user to remove someone from collections' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 117, 'BAR_PATRON',
     'Allow a user to bar a patron' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 118, 'UNBAR_PATRON',
     'Allow a user to un-bar a patron' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 119, 'DELETE_WORKSTATION',
     'Allow a user to remove an existing workstation so a new one can replace it' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 120, 'group_application.user',
     'Allow a user to add/remove users to/from the "User" group' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 121, 'group_application.user.patron',
     'Allow a user to add/remove users to/from the "Patron" group' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 122, 'group_application.user.staff',
     'Allow a user to add/remove users to/from the "Staff" group' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 123, 'group_application.user.staff.circ',
     'Allow a user to add/remove users to/from the "Circulator" group' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 124, 'group_application.user.staff.cat',
     'Allow a user to add/remove users to/from the "Cataloger" group' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 125, 'group_application.user.staff.admin.global_admin',
     'Allow a user to add/remove users to/from the "GlobalAdmin" group' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 126, 'group_application.user.staff.admin.local_admin',
     'Allow a user to add/remove users to/from the "LocalAdmin" group' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 127, 'group_application.user.staff.admin.lib_manager',
     'Allow a user to add/remove users to/from the "LibraryManager" group' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 128, 'group_application.user.staff.cat.cat1',
     'Allow a user to add/remove users to/from the "Cat1" group' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 129, 'group_application.user.staff.supercat',
     'Allow a user to add/remove users to/from the "Supercat" group' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 130, 'group_application.user.sip_client',
     'Allow a user to add/remove users to/from the "SIP-Client" group' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 131, 'group_application.user.vendor',
     'Allow a user to add/remove users to/from the "Vendor" group' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 132, 'ITEM_AGE_PROTECTED.override',
     'Allow a user to place a hold on an age-protected item' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 133, 'MAX_RENEWALS_REACHED.override',
     'Allow a user to renew an item past the maximum renewal count' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 134, 'PATRON_EXCEEDS_CHECKOUT_COUNT.override',
     'Allow staff to override checkout count failure' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 135, 'PATRON_EXCEEDS_OVERDUE_COUNT.override',
     'Allow staff to override overdue count failure' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 136, 'PATRON_EXCEEDS_FINES.override',
     'Allow staff to override fine amount checkout failure' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 137, 'CIRC_EXCEEDS_COPY_RANGE.override',
     'Allow staff to override circulation copy range failure' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 138, 'ITEM_ON_HOLDS_SHELF.override',
     'Allow staff to override item on holds shelf failure' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 139, 'COPY_NOT_AVAILABLE.override',
     'Allow staff to force checkout of Missing/Lost type items' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 140, 'HOLD_EXISTS.override',
     'Allow a user to place multiple holds on a single title' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 141, 'RUN_REPORTS',
     'Allow a user to run reports' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 142, 'SHARE_REPORT_FOLDER',
     'Allow a user to share report his own folders' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 143, 'VIEW_REPORT_OUTPUT',
     'Allow a user to view report output' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 144, 'COPY_CIRC_NOT_ALLOWED.override',
     'Allow a user to checkout an item that is marked as non-circ' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 145, 'DELETE_CONTAINER_ITEM',
     'Allow a user to delete an item out of another user''s container' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 146, 'ASSIGN_WORK_ORG_UNIT',
     'Allow a staff member to define where another staff member has their permissions' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 147, 'CREATE_FUNDING_SOURCE',
     'Allow a user to create a new funding source' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 148, 'DELETE_FUNDING_SOURCE',
     'Allow a user to delete a funding source' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 149, 'VIEW_FUNDING_SOURCE',
     'Allow a user to view a funding source' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 150, 'UPDATE_FUNDING_SOURCE',
     'Allow a user to update a funding source' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 151, 'CREATE_FUND',
     'Allow a user to create a new fund' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 152, 'DELETE_FUND',
     'Allow a user to delete a fund' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 153, 'VIEW_FUND',
     'Allow a user to view a fund' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 154, 'UPDATE_FUND',
     'Allow a user to update a fund' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 155, 'CREATE_FUND_ALLOCATION',
     'Allow a user to create a new fund allocation' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 156, 'DELETE_FUND_ALLOCATION',
     'Allow a user to delete a fund allocation' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 157, 'VIEW_FUND_ALLOCATION',
     'Allow a user to view a fund allocation' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 158, 'UPDATE_FUND_ALLOCATION',
     'Allow a user to update a fund allocation' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 159, 'GENERAL_ACQ',
     'Lowest level permission required to access the ACQ interface' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 160, 'CREATE_PROVIDER',
     'Allow a user to create a new provider' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 161, 'DELETE_PROVIDER',
     'Allow a user to delate a provider' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 162, 'VIEW_PROVIDER',
     'Allow a user to view a provider' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 163, 'UPDATE_PROVIDER',
     'Allow a user to update a provider' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 164, 'ADMIN_FUNDING_SOURCE',
     'Allow a user to create/view/update/delete a funding source' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 165, 'ADMIN_FUND',
     '(Deprecated) Allow a user to create/view/update/delete a fund' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 166, 'MANAGE_FUNDING_SOURCE',
     'Allow a user to view/credit/debit a funding source' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 167, 'MANAGE_FUND',
     'Allow a user to view/credit/debit a fund' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 168, 'CREATE_PICKLIST',
     'Allows a user to create a picklist' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 169, 'ADMIN_PROVIDER',
     'Allow a user to create/view/update/delete a provider' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 170, 'MANAGE_PROVIDER',
     'Allow a user to view and purchase from a provider' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 171, 'VIEW_PICKLIST',
     'Allow a user to view another users picklist' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 172, 'DELETE_RECORD',
     'Allow a staff member to directly remove a bibliographic record' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 173, 'ADMIN_CURRENCY_TYPE',
     'Allow a user to create/view/update/delete a currency_type' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 174, 'MARK_BAD_DEBT',
     'Allow a user to mark a transaction as bad (unrecoverable) debt' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 175, 'VIEW_BILLING_TYPE',
     'Allow a user to view billing types' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 176, 'MARK_ITEM_AVAILABLE',
     'Allow a user to mark an item status as ''available''' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 177, 'MARK_ITEM_CHECKED_OUT',
     'Allow a user to mark an item status as ''checked out''' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 178, 'MARK_ITEM_BINDERY',
     'Allow a user to mark an item status as ''bindery''' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 179, 'MARK_ITEM_LOST',
     'Allow a user to mark an item status as ''lost''' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 180, 'MARK_ITEM_MISSING',
     'Allow a user to mark an item status as ''missing''' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 181, 'MARK_ITEM_IN_PROCESS',
     'Allow a user to mark an item status as ''in process''' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 182, 'MARK_ITEM_IN_TRANSIT',
     'Allow a user to mark an item status as ''in transit''' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 183, 'MARK_ITEM_RESHELVING',
     'Allow a user to mark an item status as ''reshelving''' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 184, 'MARK_ITEM_ON_HOLDS_SHELF',
     'Allow a user to mark an item status as ''on holds shelf''' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 185, 'MARK_ITEM_ON_ORDER',
     'Allow a user to mark an item status as ''on order''' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 186, 'MARK_ITEM_ILL',
     'Allow a user to mark an item status as ''inter-library loan''' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 187, 'group_application.user.staff.acq',
     'Allows a user to add/remove/edit users in the "ACQ" group' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 188, 'CREATE_PURCHASE_ORDER',
     'Allows a user to create a purchase order' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 189, 'VIEW_PURCHASE_ORDER',
     'Allows a user to view a purchase order' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 190, 'IMPORT_ACQ_LINEITEM_BIB_RECORD',
     'Allows a user to import a bib record from the acq staging area (on-order record) into the ILS bib data set' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 191, 'RECEIVE_PURCHASE_ORDER',
     'Allows a user to mark a purchase order, lineitem, or individual copy as received' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 192, 'VIEW_ORG_SETTINGS',
     'Allows a user to view all org settings at the specified level' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 193, 'CREATE_MFHD_RECORD',
     'Allows a user to create a new MFHD record' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 194, 'UPDATE_MFHD_RECORD',
     'Allows a user to update an MFHD record' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 195, 'DELETE_MFHD_RECORD',
     'Allows a user to delete an MFHD record' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 196, 'ADMIN_ACQ_FUND',
     'Allow a user to create/view/update/delete a fund' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 197, 'group_application.user.staff.acq_admin',
     'Allows a user to add/remove/edit users in the "Acquisitions Administrators" group' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 198, 'SET_CIRC_CLAIMS_RETURNED.override',
     'Allows staff to override the max claims returned value for a patron' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 199, 'UPDATE_PATRON_CLAIM_RETURN_COUNT',
     'Allows staff to manually change a patron''s claims returned count' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 200, 'UPDATE_BILL_NOTE',
     'Allows staff to edit the note for a bill on a transaction' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 201, 'UPDATE_PAYMENT_NOTE',
     'Allows staff to edit the note for a payment on a transaction' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 202, 'UPDATE_PATRON_CLAIM_NEVER_CHECKED_OUT_COUNT',
     'Allows staff to manually change a patron''s claims never checkout out count' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 203, 'ADMIN_COPY_LOCATION_ORDER',
     'Allow a user to create/view/update/delete a copy location order' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 204, 'ASSIGN_GROUP_PERM',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 205, 'CREATE_AUDIENCE',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 206, 'CREATE_BIB_LEVEL',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 207, 'CREATE_CIRC_DURATION',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 208, 'CREATE_CIRC_MOD',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 209, 'CREATE_COPY_STATUS',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 210, 'CREATE_HOURS_OF_OPERATION',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 211, 'CREATE_ITEM_FORM',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 212, 'CREATE_ITEM_TYPE',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 213, 'CREATE_LANGUAGE',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 214, 'CREATE_LASSO',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 215, 'CREATE_LASSO_MAP',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 216, 'CREATE_LIT_FORM',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 217, 'CREATE_METABIB_FIELD',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 218, 'CREATE_NET_ACCESS_LEVEL',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 219, 'CREATE_ORG_ADDRESS',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 220, 'CREATE_ORG_TYPE',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 221, 'CREATE_ORG_UNIT',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 222, 'CREATE_ORG_UNIT_CLOSING',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 223, 'CREATE_PERM',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 224, 'CREATE_RELEVANCE_ADJUSTMENT',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 225, 'CREATE_SURVEY',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 226, 'CREATE_VR_FORMAT',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 227, 'CREATE_XML_TRANSFORM',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 228, 'DELETE_AUDIENCE',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 229, 'DELETE_BIB_LEVEL',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 230, 'DELETE_CIRC_DURATION',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 231, 'DELETE_CIRC_MOD',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 232, 'DELETE_COPY_STATUS',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 233, 'DELETE_HOURS_OF_OPERATION',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 234, 'DELETE_ITEM_FORM',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 235, 'DELETE_ITEM_TYPE',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 236, 'DELETE_LANGUAGE',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 237, 'DELETE_LASSO',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 238, 'DELETE_LASSO_MAP',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 239, 'DELETE_LIT_FORM',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 240, 'DELETE_METABIB_FIELD',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 241, 'DELETE_NET_ACCESS_LEVEL',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 242, 'DELETE_ORG_ADDRESS',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 243, 'DELETE_ORG_TYPE',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 244, 'DELETE_ORG_UNIT',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 245, 'DELETE_ORG_UNIT_CLOSING',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 246, 'DELETE_PERM',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 247, 'DELETE_RELEVANCE_ADJUSTMENT',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 248, 'DELETE_SURVEY',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 249, 'DELETE_TRANSIT',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 250, 'DELETE_VR_FORMAT',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 251, 'DELETE_XML_TRANSFORM',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 252, 'REMOVE_GROUP_PERM',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 253, 'TRANSIT_COPY',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 254, 'UPDATE_AUDIENCE',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 255, 'UPDATE_BIB_LEVEL',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 256, 'UPDATE_CIRC_DURATION',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 257, 'UPDATE_CIRC_MOD',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 258, 'UPDATE_COPY_NOTE',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 259, 'UPDATE_COPY_STATUS',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 260, 'UPDATE_GROUP_PERM',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 261, 'UPDATE_HOURS_OF_OPERATION',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 262, 'UPDATE_ITEM_FORM',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 263, 'UPDATE_ITEM_TYPE',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 264, 'UPDATE_LANGUAGE',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 265, 'UPDATE_LASSO',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 266, 'UPDATE_LASSO_MAP',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 267, 'UPDATE_LIT_FORM',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 268, 'UPDATE_METABIB_FIELD',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 269, 'UPDATE_NET_ACCESS_LEVEL',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 270, 'UPDATE_ORG_ADDRESS',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 271, 'UPDATE_ORG_TYPE',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 272, 'UPDATE_ORG_UNIT_CLOSING',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 273, 'UPDATE_PERM',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 274, 'UPDATE_RELEVANCE_ADJUSTMENT',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 275, 'UPDATE_SURVEY',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 276, 'UPDATE_TRANSIT',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 277, 'UPDATE_VOLUME_NOTE',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 278, 'UPDATE_VR_FORMAT',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 279, 'UPDATE_XML_TRANSFORM',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 280, 'MERGE_BIB_RECORDS',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 281, 'UPDATE_PICKUP_LIB_FROM_HOLDS_SHELF',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 282, 'CREATE_ACQ_FUNDING_SOURCE',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 283, 'CREATE_AUTHORITY_IMPORT_IMPORT_FIELD_DEF',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 284, 'CREATE_AUTHORITY_IMPORT_QUEUE',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 285, 'CREATE_AUTHORITY_RECORD_NOTE',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 286, 'CREATE_BIB_IMPORT_FIELD_DEF',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 287, 'CREATE_BIB_IMPORT_QUEUE',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 288, 'CREATE_LOCALE',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 289, 'CREATE_MARC_CODE',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 290, 'CREATE_TRANSLATION',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 291, 'DELETE_ACQ_FUNDING_SOURCE',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 292, 'DELETE_AUTHORITY_IMPORT_IMPORT_FIELD_DEF',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 293, 'DELETE_AUTHORITY_IMPORT_QUEUE',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 294, 'DELETE_AUTHORITY_RECORD_NOTE',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 295, 'DELETE_BIB_IMPORT_IMPORT_FIELD_DEF',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 296, 'DELETE_BIB_IMPORT_QUEUE',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 297, 'DELETE_LOCALE',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 298, 'DELETE_MARC_CODE',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 299, 'DELETE_TRANSLATION',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 300, 'UPDATE_ACQ_FUNDING_SOURCE',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 301, 'UPDATE_AUTHORITY_IMPORT_IMPORT_FIELD_DEF',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 302, 'UPDATE_AUTHORITY_IMPORT_QUEUE',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 303, 'UPDATE_AUTHORITY_RECORD_NOTE',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 304, 'UPDATE_BIB_IMPORT_IMPORT_FIELD_DEF',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 305, 'UPDATE_BIB_IMPORT_QUEUE',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 306, 'UPDATE_LOCALE',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 307, 'UPDATE_MARC_CODE',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 308, 'UPDATE_TRANSLATION',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 309, 'VIEW_ACQ_FUNDING_SOURCE',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 310, 'VIEW_AUTHORITY_RECORD_NOTES',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 311, 'CREATE_IMPORT_ITEM',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 312, 'CREATE_IMPORT_ITEM_ATTR_DEF',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 313, 'CREATE_IMPORT_TRASH_FIELD',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 314, 'DELETE_IMPORT_ITEM',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 315, 'DELETE_IMPORT_ITEM_ATTR_DEF',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 316, 'DELETE_IMPORT_TRASH_FIELD',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 317, 'UPDATE_IMPORT_ITEM',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 318, 'UPDATE_IMPORT_ITEM_ATTR_DEF',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 319, 'UPDATE_IMPORT_TRASH_FIELD',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 320, 'UPDATE_ORG_UNIT_SETTING_ALL',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 321, 'UPDATE_ORG_UNIT_SETTING.circ.lost_materials_processing_fee',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 322, 'UPDATE_ORG_UNIT_SETTING.cat.default_item_price',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 323, 'UPDATE_ORG_UNIT_SETTING.auth.opac_timeout',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 324, 'UPDATE_ORG_UNIT_SETTING.auth.staff_timeout',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 325, 'UPDATE_ORG_UNIT_SETTING.org.bounced_emails',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 326, 'UPDATE_ORG_UNIT_SETTING.circ.hold_expire_alert_interval',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 327, 'UPDATE_ORG_UNIT_SETTING.circ.hold_expire_interval',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 328, 'UPDATE_ORG_UNIT_SETTING.credit.payments.allow',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 329, 'UPDATE_ORG_UNIT_SETTING.circ.void_overdue_on_lost',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 330, 'UPDATE_ORG_UNIT_SETTING.circ.hold_stalling.soft',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 331, 'UPDATE_ORG_UNIT_SETTING.circ.hold_boundary.hard',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 332, 'UPDATE_ORG_UNIT_SETTING.circ.hold_boundary.soft',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 333, 'UPDATE_ORG_UNIT_SETTING.opac.barcode_regex',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 334, 'UPDATE_ORG_UNIT_SETTING.global.password_regex',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 335, 'UPDATE_ORG_UNIT_SETTING.circ.item_checkout_history.max',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 336, 'UPDATE_ORG_UNIT_SETTING.circ.reshelving_complete.interval',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 337, 'UPDATE_ORG_UNIT_SETTING.circ.selfcheck.patron_login_timeout',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 338, 'UPDATE_ORG_UNIT_SETTING.circ.selfcheck.alert_on_checkout_event',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 339, 'UPDATE_ORG_UNIT_SETTING.circ.selfcheck.require_patron_password',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 340, 'UPDATE_ORG_UNIT_SETTING.global.juvenile_age_threshold',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 341, 'UPDATE_ORG_UNIT_SETTING.cat.bib.keep_on_empty',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 342, 'UPDATE_ORG_UNIT_SETTING.cat.bib.alert_on_empty',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 343, 'UPDATE_ORG_UNIT_SETTING.patron.password.use_phone',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 344, 'HOLD_ITEM_CHECKED_OUT.override',
     'Allows a user to place a hold on an item that they already have checked out' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 345, 'ADMIN_ACQ_CANCEL_CAUSE',
     'Allow a user to create/update/delete reasons for order cancellations' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 346, 'ACQ_XFER_MANUAL_DFUND_AMOUNT',
     'Allow a user to transfer different amounts of money out of one fund and into another' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 347, 'OVERRIDE_HOLD_HAS_LOCAL_COPY',
     'Allow a user to override the circ.holds.hold_has_copy_at.block setting' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 348, 'UPDATE_PICKUP_LIB_FROM_TRANSIT',
     'Allow a user to change the pickup and transit destination for a captured hold item already in transit' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 349, 'COPY_NEEDED_FOR_HOLD.override',
     'Allow a user to force renewal of an item that could fulfill a hold request' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 350, 'MERGE_AUTH_RECORDS',
     'Allow a user to merge authority records together' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 351, 'ALLOW_ALT_TCN',
     'Allows staff to import a record using an alternate TCN to avoid conflicts' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 352, 'ADMIN_TRIGGER_EVENT_DEF',
     'Allow a user to administer trigger event definitions' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 353, 'ADMIN_TRIGGER_CLEANUP',
     'Allow a user to create, delete, and update trigger cleanup entries' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 354, 'CREATE_TRIGGER_CLEANUP',
     'Allow a user to create trigger cleanup entries' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 355, 'DELETE_TRIGGER_CLEANUP',
     'Allow a user to delete trigger cleanup entries' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 356, 'UPDATE_TRIGGER_CLEANUP',
     'Allow a user to update trigger cleanup entries' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 357, 'CREATE_TRIGGER_EVENT_DEF',
     'Allow a user to create trigger event definitions' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 358, 'DELETE_TRIGGER_EVENT_DEF',
     'Allow a user to delete trigger event definitions' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 359, 'UPDATE_TRIGGER_EVENT_DEF',
     'Allow a user to update trigger event definitions' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 360, 'VIEW_TRIGGER_EVENT_DEF',
     'Allow a user to view trigger event definitions' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 361, 'ADMIN_TRIGGER_HOOK',
     'Allow a user to create, update, and delete trigger hooks' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 362, 'CREATE_TRIGGER_HOOK',
     'Allow a user to create trigger hooks' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 363, 'DELETE_TRIGGER_HOOK',
     'Allow a user to delete trigger hooks' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 364, 'UPDATE_TRIGGER_HOOK',
     'Allow a user to update trigger hooks' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 365, 'ADMIN_TRIGGER_REACTOR',
     'Allow a user to create, update, and delete trigger reactors' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 366, 'CREATE_TRIGGER_REACTOR',
     'Allow a user to create trigger reactors' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 367, 'DELETE_TRIGGER_REACTOR',
     'Allow a user to delete trigger reactors' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 368, 'UPDATE_TRIGGER_REACTOR',
     'Allow a user to update trigger reactors' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 369, 'ADMIN_TRIGGER_TEMPLATE_OUTPUT',
     'Allow a user to delete trigger template output' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 370, 'DELETE_TRIGGER_TEMPLATE_OUTPUT',
     'Allow a user to delete trigger template output' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 371, 'ADMIN_TRIGGER_VALIDATOR',
     'Allow a user to create, update, and delete trigger validators' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 372, 'CREATE_TRIGGER_VALIDATOR',
     'Allow a user to create trigger validators' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 373, 'DELETE_TRIGGER_VALIDATOR',
     'Allow a user to delete trigger validators' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 374, 'UPDATE_TRIGGER_VALIDATOR',
     'Allow a user to update trigger validators' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 375, 'HOLD_LOCAL_AVAIL_OVERRIDE',
     'Allow a user to place a hold despite the availability of a local copy' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 376, 'ADMIN_BOOKING_RESOURCE',
     'Enables the user to create/update/delete booking resources' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 377, 'ADMIN_BOOKING_RESOURCE_TYPE',
     'Enables the user to create/update/delete booking resource types' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 378, 'ADMIN_BOOKING_RESOURCE_ATTR',
     'Enables the user to create/update/delete booking resource attributes' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 379, 'ADMIN_BOOKING_RESOURCE_ATTR_MAP',
     'Enables the user to create/update/delete booking resource attribute maps' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 380, 'ADMIN_BOOKING_RESOURCE_ATTR_VALUE',
     'Enables the user to create/update/delete booking resource attribute values' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 381, 'ADMIN_BOOKING_RESERVATION',
     'Enables the user to create/update/delete booking reservations' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 382, 'ADMIN_BOOKING_RESERVATION_ATTR_VALUE_MAP',
     'Enables the user to create/update/delete booking reservation attribute value maps' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 383, 'RETRIEVE_RESERVATION_PULL_LIST',
     'Allows a user to retrieve a booking reservation pull list' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 384, 'CAPTURE_RESERVATION',
     'Allows a user to capture booking reservations' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 385, 'UPDATE_RECORD',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 386, 'UPDATE_ORG_UNIT_SETTING.circ.block_renews_for_holds',
     '' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 387, 'MERGE_USERS',
     'Allows user records to be merged' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 388, 'ISSUANCE_HOLDS',
     'Allow a user to place holds on serials issuances' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 389, 'VIEW_CREDIT_CARD_PROCESSING',
     'View org unit settings related to credit card processing' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 390, 'ADMIN_CREDIT_CARD_PROCESSING',
     'Update org unit settings related to credit card processing' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 391, 'ADMIN_SERIAL_CAPTION_PATTERN',
	'Create/update/delete serial caption and pattern objects' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 392, 'ADMIN_SERIAL_SUBSCRIPTION',
	'Create/update/delete serial subscription objects' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 393, 'ADMIN_SERIAL_DISTRIBUTION',
	'Create/update/delete serial distribution objects' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 394, 'ADMIN_SERIAL_STREAM',
	'Create/update/delete serial stream objects' );
INSERT INTO permission.temp_perm ( id, code, description ) VALUES ( 395, 'RECEIVE_SERIAL',
	'Receive serial items' );

-- Now for the permissions from the IDL.  We don't have descriptions for them.

INSERT INTO permission.temp_perm ( id, code ) VALUES ( 396, 'ADMIN_ACQ_CLAIM' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 397, 'ADMIN_ACQ_CLAIM_EVENT_TYPE' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 398, 'ADMIN_ACQ_CLAIM_TYPE' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 399, 'ADMIN_ACQ_DISTRIB_FORMULA' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 400, 'ADMIN_ACQ_FISCAL_YEAR' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 401, 'ADMIN_ACQ_FUND_ALLOCATION_PERCENT' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 402, 'ADMIN_ACQ_FUND_TAG' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 403, 'ADMIN_ACQ_LINEITEM_ALERT_TEXT' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 404, 'ADMIN_AGE_PROTECT_RULE' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 405, 'ADMIN_ASSET_COPY_TEMPLATE' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 406, 'ADMIN_BOOKING_RESERVATION_ATTR_MAP' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 407, 'ADMIN_CIRC_MATRIX_MATCHPOINT' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 408, 'ADMIN_CIRC_MOD' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 409, 'ADMIN_CLAIM_POLICY' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 410, 'ADMIN_CONFIG_REMOTE_ACCOUNT' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 411, 'ADMIN_FIELD_DOC' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 412, 'ADMIN_GLOBAL_FLAG' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 413, 'ADMIN_GROUP_PENALTY_THRESHOLD' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 414, 'ADMIN_HOLD_CANCEL_CAUSE' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 415, 'ADMIN_HOLD_MATRIX_MATCHPOINT' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 416, 'ADMIN_IDENT_TYPE' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 417, 'ADMIN_IMPORT_ITEM_ATTR_DEF' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 418, 'ADMIN_INDEX_NORMALIZER' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 419, 'ADMIN_INVOICE' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 420, 'ADMIN_INVOICE_METHOD' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 421, 'ADMIN_INVOICE_PAYMENT_METHOD' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 422, 'ADMIN_LINEITEM_MARC_ATTR_DEF' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 423, 'ADMIN_MARC_CODE' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 424, 'ADMIN_MAX_FINE_RULE' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 425, 'ADMIN_MERGE_PROFILE' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 426, 'ADMIN_ORG_UNIT_SETTING_TYPE' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 427, 'ADMIN_RECURRING_FINE_RULE' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 428, 'ADMIN_STANDING_PENALTY' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 429, 'ADMIN_SURVEY' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 430, 'ADMIN_USER_REQUEST_TYPE' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 431, 'ADMIN_USER_SETTING_GROUP' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 432, 'ADMIN_USER_SETTING_TYPE' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 433, 'ADMIN_Z3950_SOURCE' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 434, 'CREATE_BIB_BTYPE' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 435, 'CREATE_BIBLIO_FINGERPRINT' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 436, 'CREATE_BIB_SOURCE' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 437, 'CREATE_BILLING_TYPE' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 438, 'CREATE_CN_BTYPE' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 439, 'CREATE_COPY_BTYPE' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 440, 'CREATE_INVOICE' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 441, 'CREATE_INVOICE_ITEM_TYPE' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 442, 'CREATE_INVOICE_METHOD' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 443, 'CREATE_MERGE_PROFILE' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 444, 'CREATE_METABIB_CLASS' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 445, 'CREATE_METABIB_SEARCH_ALIAS' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 446, 'CREATE_USER_BTYPE' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 447, 'DELETE_BIB_BTYPE' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 448, 'DELETE_BIBLIO_FINGERPRINT' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 449, 'DELETE_BIB_SOURCE' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 450, 'DELETE_BILLING_TYPE' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 451, 'DELETE_CN_BTYPE' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 452, 'DELETE_COPY_BTYPE' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 453, 'DELETE_INVOICE_ITEM_TYPE' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 454, 'DELETE_INVOICE_METHOD' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 455, 'DELETE_MERGE_PROFILE' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 456, 'DELETE_METABIB_CLASS' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 457, 'DELETE_METABIB_SEARCH_ALIAS' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 458, 'DELETE_USER_BTYPE' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 459, 'MANAGE_CLAIM' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 460, 'UPDATE_BIB_BTYPE' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 461, 'UPDATE_BIBLIO_FINGERPRINT' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 462, 'UPDATE_BIB_SOURCE' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 463, 'UPDATE_BILLING_TYPE' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 464, 'UPDATE_CN_BTYPE' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 465, 'UPDATE_COPY_BTYPE' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 466, 'UPDATE_INVOICE_ITEM_TYPE' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 467, 'UPDATE_INVOICE_METHOD' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 468, 'UPDATE_MERGE_PROFILE' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 469, 'UPDATE_METABIB_CLASS' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 470, 'UPDATE_METABIB_SEARCH_ALIAS' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 471, 'UPDATE_USER_BTYPE' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 472, 'user_request.create' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 473, 'user_request.delete' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 474, 'user_request.update' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 475, 'user_request.view' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 476, 'VIEW_ACQ_FUND_ALLOCATION_PERCENT' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 477, 'VIEW_CIRC_MATRIX_MATCHPOINT' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 478, 'VIEW_CLAIM' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 479, 'VIEW_GROUP_PENALTY_THRESHOLD' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 480, 'VIEW_HOLD_MATRIX_MATCHPOINT' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 481, 'VIEW_INVOICE' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 482, 'VIEW_MERGE_PROFILE' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 483, 'VIEW_SERIAL_SUBSCRIPTION' );
INSERT INTO permission.temp_perm ( id, code ) VALUES ( 484, 'VIEW_STANDING_PENALTY' );

-- For every permission in the temp_perm table that has a matching
-- permission in the real table: record the original id.

UPDATE permission.temp_perm AS tp
SET old_id =
	(
		SELECT id
		FROM permission.perm_list AS ppl
		WHERE ppl.code = tp.code
	)
WHERE code IN ( SELECT code FROM permission.perm_list );

-- Start juggling ids.

-- If any permissions have negative ids (with the special exception of -1),
-- we need to move them into the positive range in order to avoid duplicate
-- key problems (since we are going to use the negative range as a temporary
-- staging area).

-- First, move any predefined permissions that have negative ids (again with
-- the special exception of -1).  Temporarily give them positive ids based on
-- the sequence.

UPDATE permission.perm_list
SET id = NEXTVAL('permission.perm_list_id_seq'::regclass)
WHERE id < -1
  AND code IN (SELECT code FROM permission.temp_perm);

-- Identify any non-predefined permissions whose ids are either negative
-- or within the range (0-1000) reserved for predefined permissions.
-- Assign them ids above 1000, based on the sequence.  Record the new
-- ids in the temp_perm table.

INSERT INTO permission.temp_perm ( id, code, description, old_id, predefined )
(
	SELECT NEXTVAL('permission.perm_list_id_seq'::regclass),
		code, description, id, false
	FROM permission.perm_list
	WHERE  ( id < -1 OR id BETWEEN 0 AND 1000 )
	AND code NOT IN (SELECT code FROM permission.temp_perm)
);

-- Now update the ids of those non-predefined permissions, using the
-- values assigned in the previous step.

UPDATE permission.perm_list AS ppl
SET id = (
		SELECT id
		FROM permission.temp_perm AS tp
		WHERE tp.code = ppl.code
	)
WHERE id IN ( SELECT old_id FROM permission.temp_perm WHERE NOT predefined );

-- Now the negative ids have been eliminated, except for -1.  Move all the
-- predefined permissions temporarily into the negative range.

UPDATE permission.perm_list
SET id = -1 - id
WHERE id <> -1
AND code IN ( SELECT code from permission.temp_perm WHERE predefined );

-- Apply the final ids to the existing predefined permissions.

UPDATE permission.perm_list AS ppl
SET id =
	(
		SELECT id
		FROM permission.temp_perm AS tp
		WHERE tp.code = ppl.code
	)
WHERE
	id <> -1
	AND ppl.code IN
	(
		SELECT code from permission.temp_perm
		WHERE predefined
		AND old_id IS NOT NULL
	);

-- If there are any predefined permissions that don't exist yet in
-- permission.perm_list, insert them now.

INSERT INTO permission.perm_list ( id, code, description )
(
	SELECT id, code, description
	FROM permission.temp_perm
	WHERE old_id IS NULL
);

-- Reset the sequence to the lowest feasible value.  This may or may not
-- accomplish anything, but it will do no harm.

SELECT SETVAL('permission.perm_list_id_seq'::TEXT, GREATEST( 
	(SELECT MAX(id) FROM permission.perm_list), 1000 ));

-- If any permission lacks a description, use the code as a description.
-- It's better than nothing.

UPDATE permission.perm_list
SET description = code
WHERE description IS NULL
   OR description = '';

-- Thus endeth the Great Renumbering.

-- Having massaged the permissions, massage the way they are assigned, by inserting
-- rows into permission.grp_perm_map.  Some of these permissions may have already
-- been assigned, so we insert the rows only if they aren't already there.

-- for backwards compat, give everyone the permission
INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable)
    SELECT 1, id, 0, false FROM permission.perm_list AS perm
	WHERE code = 'HOLD_ITEM_CHECKED_OUT.override'
		AND NOT EXISTS (
			SELECT 1
			FROM permission.grp_perm_map AS map
			WHERE
				grp = 1
				AND map.perm = perm.id
		);

-- Add trigger administration permissions to the Local System Administrator group.
INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable)
    SELECT 10, id, 1, false FROM permission.perm_list AS perm
    WHERE (
		perm.code LIKE 'ADMIN_TRIGGER%'
        OR perm.code LIKE 'CREATE_TRIGGER%'
        OR perm.code LIKE 'DELETE_TRIGGER%'
        OR perm.code LIKE 'UPDATE_TRIGGER%'
	) AND NOT EXISTS (
		SELECT 1
		FROM permission.grp_perm_map AS map
		WHERE
			grp = 10
			AND map.perm = perm.id
	);

-- View trigger permissions are required at a consortial level for initial setup
-- (as before, only if the row doesn't already exist)
INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable)
    SELECT 10, id, 0, false FROM permission.perm_list AS perm
	WHERE code LIKE 'VIEW_TRIGGER%'
		AND NOT EXISTS (
			SELECT 1
			FROM permission.grp_perm_map AS map
			WHERE
				grp = 10
				AND map.perm = perm.id
		);

-- Permission for merging auth records may already be defined,
-- so add it only if it isn't there.
INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable)
    SELECT 4, id, 1, false FROM permission.perm_list AS perm
	WHERE code = 'MERGE_AUTH_RECORDS'
		AND NOT EXISTS (
			SELECT 1
			FROM permission.grp_perm_map AS map
			WHERE
				grp = 4
				AND map.perm = perm.id
		);

-- Create a reference table as parent to both
-- config.org_unit_setting_type and config_usr_setting_type

CREATE TABLE config.settings_group (
    name    TEXT PRIMARY KEY,
    label   TEXT UNIQUE NOT NULL -- I18N
);

-- org_unit setting types
CREATE TABLE config.org_unit_setting_type (
    name            TEXT    PRIMARY KEY,
    label           TEXT    UNIQUE NOT NULL,
    grp             TEXT    REFERENCES config.settings_group (name),
    description     TEXT,
    datatype        TEXT    NOT NULL DEFAULT 'string',
    fm_class        TEXT,
    view_perm       INT,
    update_perm     INT,
    --
    -- define valid datatypes
    --
    CONSTRAINT coust_valid_datatype CHECK ( datatype IN
    ( 'bool', 'integer', 'float', 'currency', 'interval',
      'date', 'string', 'object', 'array', 'link' ) ),
    --
    -- fm_class is meaningful only for 'link' datatype
    --
    CONSTRAINT coust_no_empty_link CHECK
    ( ( datatype =  'link' AND fm_class IS NOT NULL ) OR
      ( datatype <> 'link' AND fm_class IS NULL ) ),
	CONSTRAINT view_perm_fkey FOREIGN KEY (view_perm) REFERENCES permission.perm_list (id)
		ON UPDATE CASCADE
		ON DELETE RESTRICT
		DEFERRABLE INITIALLY DEFERRED,
	CONSTRAINT update_perm_fkey FOREIGN KEY (update_perm) REFERENCES permission.perm_list (id)
		ON UPDATE CASCADE
		DEFERRABLE INITIALLY DEFERRED
);

CREATE TABLE config.usr_setting_type (

    name TEXT PRIMARY KEY,
    opac_visible BOOL NOT NULL DEFAULT FALSE,
    label TEXT UNIQUE NOT NULL,
    description TEXT,
    grp             TEXT    REFERENCES config.settings_group (name),
    datatype TEXT NOT NULL DEFAULT 'string',
    fm_class TEXT,

    --
    -- define valid datatypes
    --
    CONSTRAINT coust_valid_datatype CHECK ( datatype IN
    ( 'bool', 'integer', 'float', 'currency', 'interval',
        'date', 'string', 'object', 'array', 'link' ) ),

    --
    -- fm_class is meaningful only for 'link' datatype
    --
    CONSTRAINT coust_no_empty_link CHECK
    ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
        ( datatype <> 'link' AND fm_class IS NULL ) )

);

--------------------------------------
-- Seed data for org_unit_setting_type
--------------------------------------

INSERT into config.org_unit_setting_type
( name, label, description, datatype ) VALUES

( 'auth.opac_timeout',
  'OPAC Inactivity Timeout (in seconds)',
  null,
  'integer' ),

( 'auth.staff_timeout',
  'Staff Login Inactivity Timeout (in seconds)',
  null,
  'integer' ),

( 'circ.lost_materials_processing_fee',
  'Lost Materials Processing Fee',
  null,
  'currency' ),

( 'cat.default_item_price',
  'Default Item Price',
  null,
  'currency' ),

( 'org.bounced_emails',
  'Sending email address for patron notices',
  null,
  'string' ),

( 'circ.hold_expire_alert_interval',
  'Holds: Expire Alert Interval',
  'Amount of time before a hold expires at which point the patron should be alerted',
  'interval' ),

( 'circ.hold_expire_interval',
  'Holds: Expire Interval',
  'Amount of time after a hold is placed before the hold expires.  Example "100 days"',
  'interval' ),

( 'credit.payments.allow',
  'Allow Credit Card Payments',
  'If enabled, patrons will be able to pay fines accrued at this location via credit card',
  'bool' ),

( 'global.default_locale',
  'Global Default Locale',
  null,
  'string' ),

( 'circ.void_overdue_on_lost',
  'Void overdue fines when items are marked lost',
  null,
  'bool' ),

( 'circ.hold_stalling.soft',
  'Holds: Soft stalling interval',
  'How long to wait before allowing remote items to be opportunistically captured for a hold.  Example "5 days"',
  'interval' ),

( 'circ.hold_stalling_hard',
  'Holds: Hard stalling interval',
  '',
  'interval' ),

( 'circ.hold_boundary.hard',
  'Holds: Hard boundary',
  null,
  'integer' ),

( 'circ.hold_boundary.soft',
  'Holds: Soft boundary',
  null,
  'integer' ),

( 'opac.barcode_regex',
  'Patron barcode format',
  'Regular expression defining the patron barcode format',
  'string' ),

( 'global.password_regex',
  'Password format',
  'Regular expression defining the password format',
  'string' ),

( 'circ.item_checkout_history.max',
  'Maximum previous checkouts displayed',
  'This is the maximum number of previous circulations the staff client will display when investigating item details',
  'integer' ),

( 'circ.reshelving_complete.interval',
  'Change reshelving status interval',
  'Amount of time to wait before changing an item from "reshelving" status to "available".  Examples: "1 day", "6 hours"',
  'interval' ),

( 'circ.holds.default_estimated_wait_interval',
  'Holds: Default Estimated Wait',
  'When predicting the amount of time a patron will be waiting for a hold to be fulfilled, this is the default estimated length of time to assume an item will be checked out.',
  'interval' ),

( 'circ.holds.min_estimated_wait_interval',
  'Holds: Minimum Estimated Wait',
  'When predicting the amount of time a patron will be waiting for a hold to be fulfilled, this is the minimum estimated length of time to assume an item will be checked out.',
  'interval' ),

( 'circ.selfcheck.patron_login_timeout',
  'Selfcheck: Patron Login Timeout (in seconds)',
  'Number of seconds of inactivity before the patron is logged out of the selfcheck interface',
  'integer' ),

( 'circ.selfcheck.alert.popup',
  'Selfcheck: Pop-up alert for errors',
  'If true, checkout/renewal errors will cause a pop-up window in addition to the on-screen message',
  'bool' ),

( 'circ.selfcheck.require_patron_password',
  'Selfcheck: Require patron password',
  'If true, patrons will be required to enter their password in addition to their username/barcode to log into the selfcheck interface',
  'bool' ),

( 'global.juvenile_age_threshold',
  'Juvenile Age Threshold',
  'The age at which a user is no long considered a juvenile.  For example, "18 years".',
  'interval' ),

( 'cat.bib.keep_on_empty',
  'Retain empty bib records',
  'Retain a bib record even when all attached copies are deleted',
  'bool' ),

( 'cat.bib.alert_on_empty',
  'Alert on empty bib records',
  'Alert staff when the last copy for a record is being deleted',
  'bool' ),

( 'patron.password.use_phone',
  'Patron: password from phone #',
  'Use the last 4 digits of the patrons phone number as the default password when creating new users',
  'bool' ),

( 'circ.charge_on_damaged',
  'Charge item price when marked damaged',
  'Charge item price when marked damaged',
  'bool' ),

( 'circ.charge_lost_on_zero',
  'Charge lost on zero',
  '',
  'bool' ),

( 'circ.damaged_item_processing_fee',
  'Charge processing fee for damaged items',
  'Charge processing fee for damaged items',
  'currency' ),

( 'circ.void_lost_on_checkin',
  'Circ: Void lost item billing when returned',
  'Void lost item billing when returned',
  'bool' ),

( 'circ.max_accept_return_of_lost',
  'Circ: Void lost max interval',
  'Items that have been lost this long will not result in voided billings when returned.  E.g. ''6 months''',
  'interval' ),

( 'circ.void_lost_proc_fee_on_checkin',
  'Circ: Void processing fee on lost item return',
  'Void processing fee when lost item returned',
  'bool' ),

( 'circ.restore_overdue_on_lost_return',
  'Circ: Restore overdues on lost item return',
  'Restore overdue fines on lost item return',
  'bool' ),

( 'circ.lost_immediately_available',
  'Circ: Lost items usable on checkin',
  'Lost items are usable on checkin instead of going ''home'' first',
  'bool' ),

( 'circ.holds_fifo',
  'Holds: FIFO',
  'Force holds to a more strict First-In, First-Out capture',
  'bool' ),

( 'opac.allow_pending_address',
  'OPAC: Allow pending addresses',
  'If enabled, patrons can create and edit existing addresses.  Addresses are kept in a pending state until staff approves the changes',
  'bool' ),

( 'ui.circ.show_billing_tab_on_bills',
  'Show billing tab first when bills are present',
  'If enabled and a patron has outstanding bills and the alert page is not required, show the billing tab by default, instead of the checkout tab, when a patron is loaded',
  'bool' ),

( 'ui.general.idle_timeout',
    'GUI: Idle timeout',
    'If you want staff client windows to be minimized after a certain amount of system idle time, set this to the number of seconds of idle time that you want to allow before minimizing (requires staff client restart).',
    'integer' ),

( 'ui.circ.in_house_use.entry_cap',
  'GUI: Record In-House Use: Maximum # of uses allowed per entry.',
  'The # of uses entry in the Record In-House Use interface may not exceed the value of this setting.',
  'integer' ),

( 'ui.circ.in_house_use.entry_warn',
  'GUI: Record In-House Use: # of uses threshold for Are You Sure? dialog.',
  'In the Record In-House Use interface, a submission attempt will warn if the # of uses field exceeds the value of this setting.',
  'integer' ),

( 'acq.default_circ_modifier',
  'Default circulation modifier',
  null,
  'string' ),

( 'acq.tmp_barcode_prefix',
  'Temporary barcode prefix',
  null,
  'string' ),

( 'acq.tmp_callnumber_prefix',
  'Temporary call number prefix',
  null,
  'string' ),

( 'ui.circ.patron_summary.horizontal',
  'Patron circulation summary is horizontal',
  null,
  'bool' ),

( 'ui.staff.require_initials',
  oils_i18n_gettext('ui.staff.require_initials', 'GUI: Require staff initials for entry/edit of item/patron/penalty notes/messages.', 'coust', 'label'),
  oils_i18n_gettext('ui.staff.require_initials', 'Appends staff initials and edit date into note content.', 'coust', 'description'),
  'bool' ),

( 'ui.general.button_bar',
  'Button bar',
  null,
  'bool' ),

( 'circ.hold_shelf_status_delay',
  'Hold Shelf Status Delay',
  'The purpose is to provide an interval of time after an item goes into the on-holds-shelf status before it appears to patrons that it is actually on the holds shelf.  This gives staff time to process the item before it shows as ready-for-pickup.',
  'interval' ),

( 'circ.patron_invalid_address_apply_penalty',
  'Invalid patron address penalty',
  'When set, if a patron address is set to invalid, a penalty is applied.',
  'bool' ),

( 'circ.checkout_fills_related_hold',
  'Checkout Fills Related Hold',
  'When a patron checks out an item and they have no holds that directly target the item, the system will attempt to find a hold for the patron that could be fulfilled by the checked out item and fulfills it',
  'bool'),

( 'circ.selfcheck.auto_override_checkout_events',
  'Selfcheck override events list',
  'List of checkout/renewal events that the selfcheck interface should automatically override instead instead of alerting and stopping the transaction',
  'array' ),

( 'circ.staff_client.do_not_auto_attempt_print',
  'Disable Automatic Print Attempt Type List',
  'Disable automatic print attempts from staff client interfaces for the receipt types in this list.  Possible values: "Checkout", "Bill Pay", "Hold Slip", "Transit Slip", and "Hold/Transit Slip".  This is different from the Auto-Print checkbox in the pertinent interfaces in that it disables automatic print attempts altogether, rather than encouraging silent printing by suppressing the print dialog.  The Auto-Print checkbox in these interfaces have no effect on the behavior for this setting.  In the case of the Hold, Transit, and Hold/Transit slips, this also suppresses the alert dialogs that precede the print dialog (the ones that offer Print and Do Not Print as options).',
  'array' ),

( 'ui.patron.default_inet_access_level',
  'Default level of patrons'' internet access',
  null,
  'integer' ),

( 'circ.max_patron_claim_return_count',
    'Max Patron Claims Returned Count',
    'When this count is exceeded, a staff override is required to mark the item as claims returned',
    'integer' ),

( 'circ.obscure_dob',
    'Obscure the Date of Birth field',
    'When true, the Date of Birth column in patron lists will default to Not Visible, and in the Patron Summary sidebar the value will display as <Hidden> unless the field label is clicked.',
    'bool' ),

( 'circ.auto_hide_patron_summary',
    'GUI: Toggle off the patron summary sidebar after first view.',
    'When true, the patron summary sidebar will collapse after a new patron sub-interface is selected.',
    'bool' ),

( 'credit.processor.default',
    'Credit card processing: Name default credit processor',
    'This can be "AuthorizeNet", "PayPal" (for the Website Payment Pro API), or "PayflowPro".',
    'string' ),

( 'credit.processor.authorizenet.enabled',
    'Credit card processing: AuthorizeNet enabled',
    '',
    'bool' ),

( 'credit.processor.authorizenet.login',
    'Credit card processing: AuthorizeNet login',
    '',
    'string' ),

( 'credit.processor.authorizenet.password',
    'Credit card processing: AuthorizeNet password',
    '',
    'string' ),

( 'credit.processor.authorizenet.server',
    'Credit card processing: AuthorizeNet server',
    'Required if using a developer/test account with AuthorizeNet',
    'string' ),

( 'credit.processor.authorizenet.testmode',
    'Credit card processing: AuthorizeNet test mode',
    '',
    'bool' ),

( 'credit.processor.paypal.enabled',
    'Credit card processing: PayPal enabled',
    '',
    'bool' ),
( 'credit.processor.paypal.login',
    'Credit card processing: PayPal login',
    '',
    'string' ),
( 'credit.processor.paypal.password',
    'Credit card processing: PayPal password',
    '',
    'string' ),
( 'credit.processor.paypal.signature',
    'Credit card processing: PayPal signature',
    '',
    'string' ),
( 'credit.processor.paypal.testmode',
    'Credit card processing: PayPal test mode',
    '',
    'bool' ),

( 'ui.admin.work_log.max_entries',
    oils_i18n_gettext('ui.admin.work_log.max_entries', 'GUI: Work Log: Maximum Actions Logged', 'coust', 'label'),
    oils_i18n_gettext('ui.admin.work_log.max_entries', 'Maximum entries for "Most Recent Staff Actions" section of the Work Log interface.', 'coust', 'description'),
  'interval' ),

( 'ui.admin.patron_log.max_entries',
    oils_i18n_gettext('ui.admin.patron_log.max_entries', 'GUI: Work Log: Maximum Patrons Logged', 'coust', 'label'),
    oils_i18n_gettext('ui.admin.patron_log.max_entries', 'Maximum entries for "Most Recently Affected Patrons..." section of the Work Log interface.', 'coust', 'description'),
  'interval' ),

( 'lib.courier_code',
    oils_i18n_gettext('lib.courier_code', 'Courier Code', 'coust', 'label'),
    oils_i18n_gettext('lib.courier_code', 'Courier Code for the library.  Available in transit slip templates as the %courier_code% macro.', 'coust', 'description'),
    'string'),

( 'circ.block_renews_for_holds',
    oils_i18n_gettext('circ.block_renews_for_holds', 'Holds: Block Renewal of Items Needed for Holds', 'coust', 'label'),
    oils_i18n_gettext('circ.block_renews_for_holds', 'When an item could fulfill a hold, do not allow the current patron to renew', 'coust', 'description'),
    'bool' ),

( 'circ.password_reset_request_per_user_limit',
    oils_i18n_gettext('circ.password_reset_request_per_user_limit', 'Circulation: Maximum concurrently active self-serve password reset requests per user', 'coust', 'label'),
    oils_i18n_gettext('circ.password_reset_request_per_user_limit', 'When a user has more than this number of concurrently active self-serve password reset requests for their account, prevent the user from creating any new self-serve password reset requests until the number of active requests for the user drops back below this number.', 'coust', 'description'),
    'string'),

( 'circ.password_reset_request_time_to_live',
    oils_i18n_gettext('circ.password_reset_request_time_to_live', 'Circulation: Self-serve password reset request time-to-live', 'coust', 'label'),
    oils_i18n_gettext('circ.password_reset_request_time_to_live', 'Length of time (in seconds) a self-serve password reset request should remain active.', 'coust', 'description'),
    'string'),

( 'circ.password_reset_request_throttle',
    oils_i18n_gettext('circ.password_reset_request_throttle', 'Circulation: Maximum concurrently active self-serve password reset requests', 'coust', 'label'),
    oils_i18n_gettext('circ.password_reset_request_throttle', 'Prevent the creation of new self-serve password reset requests until the number of active requests drops back below this number.', 'coust', 'description'),
    'string')
;

INSERT INTO config.org_unit_setting_type ( name, label, description, datatype ) VALUES (
        'ui.circ.suppress_checkin_popups',
        oils_i18n_gettext(
            'ui.circ.suppress_checkin_popups', 
            'Circ: Suppress popup-dialogs during check-in.', 
            'coust', 
            'label'),
        oils_i18n_gettext(
            'ui.circ.suppress_checkin_popups', 
            'Circ: Suppress popup-dialogs during check-in.', 
            'coust', 
            'description'),
        'bool'
);

INSERT INTO config.org_unit_setting_type ( name, label, description, datatype ) VALUES (
        'format.date',
        oils_i18n_gettext(
            'format.date',
            'GUI: Format Dates with this pattern.', 
            'coust', 
            'label'),
        oils_i18n_gettext(
            'format.date',
            'GUI: Format Dates with this pattern (examples: "yyyy-MM-dd" for "2010-04-26", "MMM d, yyyy" for "Apr 26, 2010")', 
            'coust', 
            'description'),
        'string'
), (
        'format.time',
        oils_i18n_gettext(
            'format.time',
            'GUI: Format Times with this pattern.', 
            'coust', 
            'label'),
        oils_i18n_gettext(
            'format.time',
            'GUI: Format Times with this pattern (examples: "h:m:s.SSS a z" for "2:07:20.666 PM Eastern Daylight Time", "HH:mm" for "14:07")', 
            'coust', 
            'description'),
        'string'
);

INSERT INTO config.org_unit_setting_type ( name, label, description, datatype ) VALUES (
        'cat.bib.delete_on_no_copy_via_acq_lineitem_cancel',
        oils_i18n_gettext(
            'cat.bib.delete_on_no_copy_via_acq_lineitem_cancel',
            'CAT: Delete bib if all copies are deleted via Acquisitions lineitem cancellation.', 
            'coust', 
            'label'),
        oils_i18n_gettext(
            'cat.bib.delete_on_no_copy_via_acq_lineitem_cancel',
            'CAT: Delete bib if all copies are deleted via Acquisitions lineitem cancellation.', 
            'coust', 
            'description'),
        'bool'
);

INSERT INTO config.org_unit_setting_type ( name, label, description, datatype ) VALUES (
        'url.remote_column_settings',
        oils_i18n_gettext(
            'url.remote_column_settings',
            'GUI: URL for remote directory containing list column settings.', 
            'coust', 
            'label'),
        oils_i18n_gettext(
            'url.remote_column_settings',
            'GUI: URL for remote directory containing list column settings.  The format and naming convention for the files found in this directory match those in the local settings directory for a given workstation.  An administrator could create the desired settings locally and then copy all the tree_columns_for_* files to the remote directory.', 
            'coust', 
            'description'),
        'string'
);

INSERT INTO config.org_unit_setting_type ( name, label, description, datatype ) VALUES (
        'gui.disable_local_save_columns',
        oils_i18n_gettext(
            'gui.disable_local_save_columns',
            'GUI: Disable the ability to save list column configurations locally.', 
            'coust', 
            'label'),
        oils_i18n_gettext(
            'gui.disable_local_save_columns',
            'GUI: Disable the ability to save list column configurations locally.  If set, columns may still be manipulated, however, the changes do not persist.  Also, existing local configurations are ignored if this setting is true.', 
            'coust', 
            'description'),
        'bool'
);

INSERT INTO config.org_unit_setting_type ( name, label, description, datatype ) VALUES (
        'circ.password_reset_request_requires_matching_email',
        oils_i18n_gettext(
            'circ.password_reset_request_requires_matching_email',
            'Circulation: Require matching email address for password reset requests', 
            'coust', 
            'label'),
        oils_i18n_gettext(
            'circ.password_reset_request_requires_matching_email',
            'Circulation: Require matching email address for password reset requests', 
            'coust', 
            'description'),
        'bool'
);

INSERT INTO config.org_unit_setting_type ( name, label, description, datatype ) VALUES (
        'circ.holds.expired_patron_block',
        oils_i18n_gettext(
            'circ.holds.expired_patron_block',
            'Circulation: Block hold request if hold recipient privileges have expired', 
            'coust', 
            'label'),
        oils_i18n_gettext(
            'circ.holds.expired_patron_block',
            'Circulation: Block hold request if hold recipient privileges have expired', 
            'coust', 
            'description'),
        'bool'
);

INSERT INTO config.org_unit_setting_type
    (name, label, description, datatype) VALUES (
        'circ.booking_reservation.default_elbow_room',
        oils_i18n_gettext(
            'circ.booking_reservation.default_elbow_room',
            'Booking: Elbow room',
            'coust',
            'label'
        ),
        oils_i18n_gettext(
            'circ.booking_reservation.default_elbow_room',
            'Elbow room specifies how far in the future you must make a reservation on an item if that item will have to transit to reach its pickup location.  It secondarily defines how soon a reservation on a given item must start before the check-in process will opportunistically capture it for the reservation shelf.',
            'coust',
            'label'
        ),
        'interval'
    );

-- Org_unit_setting_type(s) that need an fm_class:
INSERT into config.org_unit_setting_type
( name, label, description, datatype, fm_class ) VALUES
( 'acq.default_copy_location',
  'Default copy location',
  null,
  'link',
  'acpl' );

INSERT INTO config.org_unit_setting_type (name, label, description, datatype) VALUES (
    'circ.holds.org_unit_target_weight',
    'Holds: Org Unit Target Weight',
    'Org Units can be organized into hold target groups based on a weight.  Potential copies from org units with the same weight are chosen at random.',
    'integer'
);

INSERT INTO config.org_unit_setting_type (name, label, description, datatype) VALUES (
    'circ.holds.target_holds_by_org_unit_weight',
    'Holds: Use weight-based hold targeting',
    'Use library weight based hold targeting',
    'bool'
);

INSERT INTO config.org_unit_setting_type (name, label, description, datatype) VALUES (
    'circ.holds.max_org_unit_target_loops',
    'Holds: Maximum library target attempts',
    'When this value is set and greater than 0, the system will only attempt to find a copy at each possible branch the configured number of times',
    'integer'
);


-- Org setting for overriding the circ lib of a precat copy
INSERT INTO config.org_unit_setting_type (name, label, description, datatype) VALUES (
    'circ.pre_cat_copy_circ_lib',
    'Pre-cat Item Circ Lib',
    'Override the default circ lib of "here" with a pre-configured circ lib for pre-cat items.  The value should be the "shortname" (aka policy name) of the org unit',
    'string'
);

-- Circ auto-renew interval setting
INSERT INTO config.org_unit_setting_type (name, label, description, datatype) VALUES (
    'circ.checkout_auto_renew_age',
    'Checkout auto renew age',
    'When an item has been checked out for at least this amount of time, an attempt to check out the item to the patron that it is already checked out to will simply renew the circulation',
    'interval'
);

-- Setting for behind the desk hold pickups
INSERT INTO config.org_unit_setting_type (name, label, description, datatype) VALUES (
    'circ.holds.behind_desk_pickup_supported',
    'Holds: Behind Desk Pickup Supported',
    'If a branch supports both a public holds shelf and behind-the-desk pickups, set this value to true.  This gives the patron the option to enable behind-the-desk pickups for their holds',
    'bool'
);

INSERT INTO config.org_unit_setting_type ( name, label, description, datatype ) VALUES (
        'acq.holds.allow_holds_from_purchase_request',
        oils_i18n_gettext(
            'acq.holds.allow_holds_from_purchase_request', 
            'Allows patrons to create automatic holds from purchase requests.', 
            'coust', 
            'label'),
        oils_i18n_gettext(
            'acq.holds.allow_holds_from_purchase_request', 
            'Allows patrons to create automatic holds from purchase requests.', 
            'coust', 
            'description'),
        'bool'
);

INSERT INTO config.org_unit_setting_type (name, label, description, datatype) VALUES (
    'circ.holds.target_skip_me',
    'Skip For Hold Targeting',
    'When true, don''t target any copies at this org unit for holds',
    'bool'
);

-- claims returned mark item missing 
INSERT INTO
    config.org_unit_setting_type ( name, label, description, datatype )
    VALUES (
        'circ.claim_return.mark_missing',
        'Claim Return: Mark copy as missing', 
        'When a circ is marked as claims-returned, also mark the copy as missing',
        'bool'
    );

-- claims never checked out mark item missing 
INSERT INTO
    config.org_unit_setting_type ( name, label, description, datatype )
    VALUES (
        'circ.claim_never_checked_out.mark_missing',
        'Claim Never Checked Out: Mark copy as missing', 
        'When a circ is marked as claims-never-checked-out, mark the copy as missing',
        'bool'
    );

-- mark damaged void overdue setting
INSERT INTO
    config.org_unit_setting_type ( name, label, description, datatype )
    VALUES (
        'circ.damaged.void_ovedue',
        'Mark item damaged voids overdues',
        'When an item is marked damaged, overdue fines on the most recent circulation are voided.',
        'bool'
    );

-- hold cancel display limits
INSERT INTO config.org_unit_setting_type ( name, label, description, datatype )
    VALUES (
        'circ.holds.canceled.display_count',
        'Holds: Canceled holds display count',
        'How many canceled holds to show in patron holds interfaces',
        'integer'
    );

INSERT INTO config.org_unit_setting_type ( name, label, description, datatype )
    VALUES (
        'circ.holds.canceled.display_age',
        'Holds: Canceled holds display age',
        'Show all canceled holds that were canceled within this amount of time',
        'interval'
    );

INSERT INTO config.org_unit_setting_type ( name, label, description, datatype )
    VALUES (
        'circ.holds.uncancel.reset_request_time',
        'Holds: Reset request time on un-cancel',
        'When a hold is uncanceled, reset the request time to push it to the end of the queue',
        'bool'
    );

INSERT INTO config.org_unit_setting_type (name, label, description, datatype)
    VALUES (
        'circ.holds.default_shelf_expire_interval',
        'Default hold shelf expire interval',
        '',
        'interval'
);

INSERT INTO config.org_unit_setting_type (name, label, description, datatype, fm_class)
    VALUES (
        'circ.claim_return.copy_status', 
        'Claim Return Copy Status', 
        'Claims returned copies are put into this status.  Default is to leave the copy in the Checked Out status',
        'link', 
        'ccs' 
    );

INSERT INTO config.org_unit_setting_type ( name, label, description, datatype ) 
    VALUES ( 
        'circ.max_fine.cap_at_price',
        oils_i18n_gettext('circ.max_fine.cap_at_price', 'Circ: Cap Max Fine at Item Price', 'coust', 'label'),
        oils_i18n_gettext('circ.max_fine.cap_at_price', 'This prevents the system from charging more than the item price in overdue fines', 'coust', 'description'),
        'bool' 
    );

INSERT INTO config.org_unit_setting_type ( name, label, description, datatype, fm_class ) 
    VALUES ( 
        'circ.holds.clear_shelf.copy_status',
        oils_i18n_gettext('circ.holds.clear_shelf.copy_status', 'Holds: Clear shelf copy status', 'coust', 'label'),
        oils_i18n_gettext('circ.holds.clear_shelf.copy_status', 'Any copies that have not been put into reshelving, in-transit, or on-holds-shelf (for a new hold) during the clear shelf process will be put into this status.  This is basically a purgatory status for copies waiting to be pulled from the shelf and processed by hand', 'coust', 'description'),
        'link',
        'ccs'
    );

INSERT INTO config.org_unit_setting_type ( name, label, description, datatype )
    VALUES ( 
        'circ.selfcheck.workstation_required',
        oils_i18n_gettext('circ.selfcheck.workstation_required', 'Selfcheck: Workstation Required', 'coust', 'label'),
        oils_i18n_gettext('circ.selfcheck.workstation_required', 'All selfcheck stations must use a workstation', 'coust', 'description'),
        'bool'
    ), (
        'circ.selfcheck.patron_password_required',
        oils_i18n_gettext('circ.selfcheck.patron_password_required', 'Selfcheck: Require Patron Password', 'coust', 'label'),
        oils_i18n_gettext('circ.selfcheck.patron_password_required', 'Patron must log in with barcode and password at selfcheck station', 'coust', 'description'),
        'bool'
    );

INSERT INTO config.org_unit_setting_type ( name, label, description, datatype )
    VALUES ( 
        'circ.selfcheck.alert.sound',
        oils_i18n_gettext('circ.selfcheck.alert.sound', 'Selfcheck: Audio Alerts', 'coust', 'label'),
        oils_i18n_gettext('circ.selfcheck.alert.sound', 'Use audio alerts for selfcheck events', 'coust', 'description'),
        'bool'
    );

INSERT INTO
    config.org_unit_setting_type (name, label, description, datatype)
    VALUES (
        'notice.telephony.callfile_lines',
        'Telephony: Arbitrary line(s) to include in each notice callfile',
        $$
        This overrides lines from opensrf.xml.
        Line(s) must be valid for your target server and platform
        (e.g. Asterisk 1.4).
        $$,
        'string'
    );

INSERT INTO config.org_unit_setting_type ( name, label, description, datatype )
    VALUES ( 
        'circ.offline.username_allowed',
        oils_i18n_gettext('circ.offline.username_allowed', 'Offline: Patron Usernames Allowed', 'coust', 'label'),
        oils_i18n_gettext('circ.offline.username_allowed', 'During offline circulations, allow patrons to identify themselves with usernames in addition to barcode.  For this setting to work, a barcode format must also be defined', 'coust', 'description'),
        'bool'
    );

INSERT INTO config.org_unit_setting_type ( name, label, description, datatype )
VALUES (
    'acq.fund.balance_limit.warn',
    oils_i18n_gettext('acq.fund.balance_limit.warn', 'Fund Spending Limit for Warning', 'coust', 'label'),
    oils_i18n_gettext('acq.fund.balance_limit.warn', 'When the amount remaining in the fund, including spent money and encumbrances, goes below this percentage, attempts to spend from the fund will result in a warning to the staff.', 'coust', 'descripton'),
    'integer'
);

INSERT INTO config.org_unit_setting_type ( name, label, description, datatype )
VALUES (
    'acq.fund.balance_limit.block',
    oils_i18n_gettext('acq.fund.balance_limit.block', 'Fund Spending Limit for Block', 'coust', 'label'),
    oils_i18n_gettext('acq.fund.balance_limit.block', 'When the amount remaining in the fund, including spent money and encumbrances, goes below this percentage, attempts to spend from the fund will be blocked.', 'coust', 'description'),
    'integer'
);

INSERT INTO config.org_unit_setting_type ( name, label, description, datatype )
    VALUES (
        'circ.holds.hold_has_copy_at.alert',
        oils_i18n_gettext('circ.holds.hold_has_copy_at.alert', 'Holds: Has Local Copy Alert', 'coust', 'label'),
        oils_i18n_gettext('circ.holds.hold_has_copy_at.alert', 'If there is an available copy at the requesting library that could fulfill a hold during hold placement time, alert the patron', 'coust', 'description'),
        'bool'
    ),(
        'circ.holds.hold_has_copy_at.block',
        oils_i18n_gettext('circ.holds.hold_has_copy_at.block', 'Holds: Has Local Copy Block', 'coust', 'label'),
        oils_i18n_gettext('circ.holds.hold_has_copy_at.block', 'If there is an available copy at the requesting library that could fulfill a hold during hold placement time, do not allow the hold to be placed', 'coust', 'description'),
        'bool'
    );

INSERT INTO config.org_unit_setting_type ( name, label, description, datatype )
VALUES (
    'auth.persistent_login_interval',
    oils_i18n_gettext('auth.persistent_login_interval', 'Persistent Login Duration', 'coust', 'label'),
    oils_i18n_gettext('auth.persistent_login_interval', 'How long a persistent login lasts.  E.g. ''2 weeks''', 'coust', 'description'),
    'interval'
);

INSERT INTO config.org_unit_setting_type ( name, label, description, datatype ) VALUES (
        'cat.marc_control_number_identifier',
        oils_i18n_gettext(
            'cat.marc_control_number_identifier', 
            'Cat: Defines the control number identifier used in 003 and 035 fields.', 
            'coust', 
            'label'),
        oils_i18n_gettext(
            'cat.marc_control_number_identifier', 
            'Cat: Defines the control number identifier used in 003 and 035 fields.', 
            'coust', 
            'description'),
        'string'
);

INSERT INTO config.org_unit_setting_type (name, label, description, datatype) 
    VALUES (
        'circ.selfcheck.block_checkout_on_copy_status',
        oils_i18n_gettext(
            'circ.selfcheck.block_checkout_on_copy_status',
            'Selfcheck: Block copy checkout status',
            'coust',
            'label'
        ),
        oils_i18n_gettext(
            'circ.selfcheck.block_checkout_on_copy_status',
            'List of copy status IDs that will block checkout even if the generic COPY_NOT_AVAILABLE event is overridden',
            'coust',
            'description'
        ),
        'array'
    );

INSERT INTO config.org_unit_setting_type ( name, label, description, datatype, fm_class )
VALUES (
    'serial.prev_issuance_copy_location',
    oils_i18n_gettext(
        'serial.prev_issuance_copy_location',
        'Serials: Previous Issuance Copy Location',
        'coust',
        'label'
    ),
    oils_i18n_gettext(
        'serial.prev_issuance_copy_location',
        'When a serial issuance is received, copies (units) of the previous issuance will be automatically moved into the configured shelving location',
        'coust',
        'descripton'
        ),
    'link',
    'acpl'
);

INSERT INTO config.org_unit_setting_type ( name, label, description, datatype, fm_class )
VALUES (
    'cat.default_classification_scheme',
    oils_i18n_gettext(
        'cat.default_classification_scheme',
        'Cataloging: Default Classification Scheme',
        'coust',
        'label'
    ),
    oils_i18n_gettext(
        'cat.default_classification_scheme',
        'Defines the default classification scheme for new call numbers: 1 = Generic; 2 = Dewey; 3 = LC',
        'coust',
        'descripton'
        ),
    'link',
    'acnc'
);

INSERT INTO config.org_unit_setting_type ( name, label, description, datatype ) VALUES (
        'opac.org_unit_hiding.depth',
        oils_i18n_gettext(
            'opac.org_unit_hiding.depth',
            'OPAC: Org Unit Hiding Depth', 
            'coust', 
            'label'),
        oils_i18n_gettext(
            'opac.org_unit_hiding.depth',
            'This will hide certain org units in the public OPAC if the Original Location (url param "ol") for the OPAC inherits this setting.  This setting specifies an org unit depth, that together with the OPAC Original Location determines which section of the Org Hierarchy should be visible in the OPAC.  For example, a stock Evergreen installation will have a 3-tier hierarchy (Consortium/System/Branch), where System has a depth of 1 and Branch has a depth of 2.  If this setting contains a depth of 1 in such an installation, then every library in the System in which the Original Location belongs will be visible, and everything else will be hidden.  A depth of 0 will effectively make every org visible.  The embedded OPAC in the staff client ignores this setting.', 
            'coust', 
            'description'),
        'integer'
);

INSERT INTO config.org_unit_setting_type (name, label, description, datatype)
    VALUES 
        ('circ.holds.alert_if_local_avail',
         'Holds: Local available alert',
         'If local copy is available, alert the person making the hold',
         'bool'),

        ('circ.holds.deny_if_local_avail',
         'Holds: Local available block',
         'If local copy is available, deny the creation of the hold',
         'bool')
    ;

INSERT INTO config.org_unit_setting_type ( name, label, description, datatype )
    VALUES ( 
        'circ.holds.clear_shelf.no_capture_holds',
        oils_i18n_gettext('circ.holds.clear_shelf.no_capture_holds', 'Holds: Bypass hold capture during clear shelf process', 'coust', 'label'),
        oils_i18n_gettext('circ.holds.clear_shelf.no_capture_holds', 'During the clear shelf process, avoid capturing new holds on cleared items.', 'coust', 'description'),
        'bool'
    );

INSERT INTO config.org_unit_setting_type (name, label, description, datatype) VALUES (
    'circ.booking_reservation.stop_circ',
    'Disallow circulation of items when they are on booking reserve and that reserve overlaps with the checkout period',
    'When true, items on booking reserve during the proposed checkout period will not be allowed to circulate unless overridden with the COPY_RESERVED.override permission.',
    'bool'
);

---------------------------------
-- Seed data for usr_setting_type
----------------------------------

INSERT INTO config.usr_setting_type (name,opac_visible,label,description,datatype)
    VALUES ('opac.default_font', TRUE, 'OPAC Font Size', 'OPAC Font Size', 'string');

INSERT INTO config.usr_setting_type (name,opac_visible,label,description,datatype)
    VALUES ('opac.default_search_depth', TRUE, 'OPAC Search Depth', 'OPAC Search Depth', 'integer');

INSERT INTO config.usr_setting_type (name,opac_visible,label,description,datatype)
    VALUES ('opac.default_search_location', TRUE, 'OPAC Search Location', 'OPAC Search Location', 'integer');

INSERT INTO config.usr_setting_type (name,opac_visible,label,description,datatype)
    VALUES ('opac.hits_per_page', TRUE, 'Hits per Page', 'Hits per Page', 'string');

INSERT INTO config.usr_setting_type (name,opac_visible,label,description,datatype)
    VALUES ('opac.hold_notify', TRUE, 'Hold Notification Format', 'Hold Notification Format', 'string');

INSERT INTO config.usr_setting_type (name,opac_visible,label,description,datatype)
    VALUES ('staff_client.catalog.record_view.default', TRUE, 'Default Record View', 'Default Record View', 'string');

INSERT INTO config.usr_setting_type (name,opac_visible,label,description,datatype)
    VALUES ('staff_client.copy_editor.templates', TRUE, 'Copy Editor Template', 'Copy Editor Template', 'object');

INSERT INTO config.usr_setting_type (name,opac_visible,label,description,datatype)
    VALUES ('circ.holds_behind_desk', FALSE, 'Hold is behind Circ Desk', 'Hold is behind Circ Desk', 'bool');

INSERT INTO config.usr_setting_type (name,opac_visible,label,description,datatype)
    VALUES (
        'history.circ.retention_age',
        TRUE,
        oils_i18n_gettext('history.circ.retention_age','Historical Circulation Retention Age','cust','label'),
        oils_i18n_gettext('history.circ.retention_age','Historical Circulation Retention Age','cust','description'),
        'interval'
    ),(
        'history.circ.retention_start',
        FALSE,
        oils_i18n_gettext('history.circ.retention_start','Historical Circulation Retention Start Date','cust','label'),
        oils_i18n_gettext('history.circ.retention_start','Historical Circulation Retention Start Date','cust','description'),
        'date'
    );

INSERT INTO config.usr_setting_type (name,opac_visible,label,description,datatype)
    VALUES (
        'history.hold.retention_age',
        TRUE,
        oils_i18n_gettext('history.hold.retention_age','Historical Hold Retention Age','cust','label'),
        oils_i18n_gettext('history.hold.retention_age','Historical Hold Retention Age','cust','description'),
        'interval'
    ),(
        'history.hold.retention_start',
        TRUE,
        oils_i18n_gettext('history.hold.retention_start','Historical Hold Retention Start Date','cust','label'),
        oils_i18n_gettext('history.hold.retention_start','Historical Hold Retention Start Date','cust','description'),
        'interval'
    ),(
        'history.hold.retention_count',
        TRUE,
        oils_i18n_gettext('history.hold.retention_count','Historical Hold Retention Count','cust','label'),
        oils_i18n_gettext('history.hold.retention_count','Historical Hold Retention Count','cust','description'),
        'integer'
    );

INSERT INTO config.usr_setting_type (name, opac_visible, label, description, datatype)
    VALUES (
        'opac.default_sort',
        TRUE,
        oils_i18n_gettext(
            'opac.default_sort',
            'OPAC Default Search Sort',
            'cust',
            'label'
        ),
        oils_i18n_gettext(
            'opac.default_sort',
            'OPAC Default Search Sort',
            'cust',
            'description'
        ),
        'string'
    );

INSERT INTO config.org_unit_setting_type ( name, label, description, datatype, fm_class ) VALUES (
        'circ.missing_pieces.copy_status',
        oils_i18n_gettext(
            'circ.missing_pieces.copy_status',
            'Circulation: Item Status for Missing Pieces',
            'coust',
            'label'),
        oils_i18n_gettext(
            'circ.missing_pieces.copy_status',
            'This is the Item Status to use for items that have been marked or scanned as having Missing Pieces.  In the absence of this setting, the Damaged status is used.',
            'coust',
            'description'),
        'link',
        'ccs'
);

INSERT INTO config.org_unit_setting_type ( name, label, description, datatype ) VALUES (
        'circ.do_not_tally_claims_returned',
        oils_i18n_gettext(
            'circ.do_not_tally_claims_returned',
            'Circulation: Do not include outstanding Claims Returned circulations in lump sum tallies in Patron Display.',
            'coust',
            'label'),
        oils_i18n_gettext(
            'circ.do_not_tally_claims_returned',
            'In the Patron Display interface, the number of total active circulations for a given patron is presented in the Summary sidebar and underneath the Items Out navigation button.  This setting will prevent Claims Returned circulations from counting toward these tallies.',
            'coust',
            'description'),
        'bool'
);

INSERT INTO config.org_unit_setting_type (name, label, description, datatype)
    VALUES
        ('cat.label.font.size',
            oils_i18n_gettext('cat.label.font.size',
                'Cataloging: Spine and pocket label font size', 'coust', 'label'),
            oils_i18n_gettext('cat.label.font.size',
                'Set the default font size for spine and pocket labels', 'coust', 'description'),
            'integer'
        )
        ,('cat.label.font.family',
            oils_i18n_gettext('cat.label.font.family',
                'Cataloging: Spine and pocket label font family', 'coust', 'label'),
            oils_i18n_gettext('cat.label.font.family',
                'Set the preferred font family for spine and pocket labels. You can specify a list of fonts, separated by commas, in order of preference; the system will use the first font it finds with a matching name. For example, "Arial, Helvetica, serif".',
                'coust', 'description'),
            'string'
        )
        ,('cat.spine.line.width',
            oils_i18n_gettext('cat.spine.line.width',
                'Cataloging: Spine label line width', 'coust', 'label'),
            oils_i18n_gettext('cat.spine.line.width',
                'Set the default line width for spine labels in number of characters. This specifies the boundary at which lines must be wrapped.',
                'coust', 'description'),
            'integer'
        )
        ,('cat.spine.line.height',
            oils_i18n_gettext('cat.spine.line.height',
                'Cataloging: Spine label maximum lines', 'coust', 'label'),
            oils_i18n_gettext('cat.spine.line.height',
                'Set the default maximum number of lines for spine labels.',
                'coust', 'description'),
            'integer'
        )
        ,('cat.spine.line.margin',
            oils_i18n_gettext('cat.spine.line.margin',
                'Cataloging: Spine label left margin', 'coust', 'label'),
            oils_i18n_gettext('cat.spine.line.margin',
                'Set the left margin for spine labels in number of characters.',
                'coust', 'description'),
            'integer'
        )
;

INSERT INTO config.org_unit_setting_type (name, label, description, datatype)
    VALUES
        ('cat.label.font.weight',
            oils_i18n_gettext('cat.label.font.weight',
                'Cataloging: Spine and pocket label font weight', 'coust', 'label'),
            oils_i18n_gettext('cat.label.font.weight',
                'Set the preferred font weight for spine and pocket labels. You can specify "normal", "bold", "bolder", or "lighter".',
                'coust', 'description'),
            'string'
        )
;

INSERT INTO config.org_unit_setting_type ( name, label, description, datatype ) VALUES (
        'circ.patron_edit.clone.copy_address',
        oils_i18n_gettext(
            'circ.patron_edit.clone.copy_address',
            'Patron Registration: Cloned patrons get address copy',
            'coust',
            'label'
        ),
        oils_i18n_gettext(
            'circ.patron_edit.clone.copy_address',
            'In the Patron editor, copy addresses from the cloned user instead of linking directly to the address',
            'coust',
            'description'
        ),
        'bool'
);

INSERT INTO config.org_unit_setting_type ( name, label, description, datatype, fm_class ) VALUES (
        'ui.patron.default_ident_type',
        oils_i18n_gettext(
            'ui.patron.default_ident_type',
            'GUI: Default Ident Type for Patron Registration',
            'coust',
            'label'),
        oils_i18n_gettext(
            'ui.patron.default_ident_type',
            'This is the default Ident Type for new users in the patron editor.',
            'coust',
            'description'),
        'link',
        'cit'
);

INSERT INTO config.org_unit_setting_type ( name, label, description, datatype ) VALUES (
        'ui.patron.default_country',
        oils_i18n_gettext(
            'ui.patron.default_country',
            'GUI: Default Country for New Addresses in Patron Editor',
            'coust',
            'label'),
        oils_i18n_gettext(
            'ui.patron.default_country',
            'This is the default Country for new addresses in the patron editor.',
            'coust',
            'description'),
        'string'
);

INSERT INTO config.org_unit_setting_type ( name, label, description, datatype ) VALUES (
        'ui.patron.registration.require_address',
        oils_i18n_gettext(
            'ui.patron.registration.require_address',
            'GUI: Require at least one address for Patron Registration',
            'coust',
            'label'),
        oils_i18n_gettext(
            'ui.patron.registration.require_address',
            'Enforces a requirement for having at least one address for a patron during registration.',
            'coust',
            'description'),
        'bool'
);

INSERT INTO config.org_unit_setting_type (
    name, label, description, datatype
) VALUES
    ('credit.processor.payflowpro.enabled',
        'Credit card processing: Enable PayflowPro payments',
        'This is NOT the same thing as the settings labeled with just "PayPal."',
        'bool'
    ),
    ('credit.processor.payflowpro.login',
        'Credit card processing: PayflowPro login/merchant ID',
        'Often the same thing as the PayPal manager login',
        'string'
    ),
    ('credit.processor.payflowpro.password',
        'Credit card processing: PayflowPro password',
        'PayflowPro password',
        'string'
    ),
    ('credit.processor.payflowpro.testmode',
        'Credit card processing: PayflowPro test mode',
        'Do not really process transactions, but stay in test mode - uses pilot-payflowpro.paypal.com instead of the usual host',
        'bool'
    ),
    ('credit.processor.payflowpro.vendor',
        'Credit card processing: PayflowPro vendor',
        'Often the same thing as the login',
        'string'
    ),
    ('credit.processor.payflowpro.partner',
        'Credit card processing: PayflowPro partner',
        'Often "PayPal" or "VeriSign", sometimes others',
        'string'
    );

-- Patch the name of an old selfcheck setting
UPDATE actor.org_unit_setting
    SET name = 'circ.selfcheck.alert.popup'
    WHERE name = 'circ.selfcheck.alert_on_checkout_event';

-- Rename certain existing org_unit settings, if present,
-- and make sure their values are JSON
UPDATE actor.org_unit_setting SET
    name = 'circ.holds.default_estimated_wait_interval',
    --
    -- The value column should be JSON.  The old value should be a number,
    -- but it may or may not be quoted.  The following CASE behaves
    -- differently depending on whether value is quoted.  It is simplistic,
    -- and will be defeated by leading or trailing white space, or various
    -- malformations.
    --
    value = CASE WHEN SUBSTR( value, 1, 1 ) = '"'
                THEN '"' || SUBSTR( value, 2, LENGTH(value) - 2 ) || ' days"'
                ELSE '"' || value || ' days"'
            END
WHERE name = 'circ.hold_estimate_wait_interval';

-- Create types for existing org unit settings
-- not otherwise accounted for

INSERT INTO config.org_unit_setting_type(
 name,
 label,
 description
)
SELECT DISTINCT
	name,
	name,
	'FIXME'
FROM
	actor.org_unit_setting
WHERE
	name NOT IN (
		SELECT name
		FROM config.org_unit_setting_type
	);

-- Add foreign key to org_unit_setting

ALTER TABLE actor.org_unit_setting
	ADD FOREIGN KEY (name) REFERENCES config.org_unit_setting_type (name)
		DEFERRABLE INITIALLY DEFERRED;

-- Create types for existing user settings
-- not otherwise accounted for

INSERT INTO config.usr_setting_type (
	name,
	label,
	description
)
SELECT DISTINCT
	name,
	name,
	'FIXME'
FROM
	actor.usr_setting
WHERE
	name NOT IN (
		SELECT name
		FROM config.usr_setting_type
	);

-- Add foreign key to user_setting_type

ALTER TABLE actor.usr_setting
	ADD FOREIGN KEY (name) REFERENCES config.usr_setting_type (name)
		ON DELETE CASCADE ON UPDATE CASCADE
		DEFERRABLE INITIALLY DEFERRED;

INSERT INTO actor.org_unit_setting (org_unit, name, value) VALUES
    (1, 'cat.spine.line.margin', 0)
    ,(1, 'cat.spine.line.height', 9)
    ,(1, 'cat.spine.line.width', 8)
    ,(1, 'cat.label.font.family', '"monospace"')
    ,(1, 'cat.label.font.size', 10)
    ,(1, 'cat.label.font.weight', '"normal"')
;

ALTER TABLE action_trigger.event_definition ADD COLUMN granularity TEXT;
ALTER TABLE action_trigger.event ADD COLUMN async_output BIGINT REFERENCES action_trigger.event_output (id);
ALTER TABLE action_trigger.event_definition ADD COLUMN usr_field TEXT;
ALTER TABLE action_trigger.event_definition ADD COLUMN opt_in_setting TEXT REFERENCES config.usr_setting_type (name) DEFERRABLE INITIALLY DEFERRED;

CREATE OR REPLACE FUNCTION is_json( TEXT ) RETURNS BOOL AS $f$
    use JSON::XS;
    my $json = shift();
    eval { JSON::XS->new->allow_nonref->decode( $json ) };
    return $@ ? 0 : 1;
$f$ LANGUAGE PLPERLU;

ALTER TABLE action_trigger.event ADD COLUMN user_data TEXT CHECK (user_data IS NULL OR is_json( user_data ));

INSERT INTO action_trigger.hook (key,core_type,description) VALUES (
    'hold_request.cancel.expire_no_target',
    'ahr',
    'A hold is cancelled because no copies were found'
);

INSERT INTO action_trigger.hook (key,core_type,description) VALUES (
    'hold_request.cancel.expire_holds_shelf',
    'ahr',
    'A hold is cancelled because it was on the holds shelf too long'
);

INSERT INTO action_trigger.hook (key,core_type,description) VALUES (
    'hold_request.cancel.staff',
    'ahr',
    'A hold is cancelled because it was cancelled by staff'
);

INSERT INTO action_trigger.hook (key,core_type,description) VALUES (
    'hold_request.cancel.patron',
    'ahr',
    'A hold is cancelled by the patron'
);

-- Fix typos in descriptions
UPDATE action_trigger.hook SET description = 'A hold is successfully placed' WHERE key = 'hold_request.success';
UPDATE action_trigger.hook SET description = 'A hold is attempted but not successfully placed' WHERE key = 'hold_request.failure';

-- Add a hook for renewals
INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('renewal','circ','Item renewed to user');

INSERT INTO action_trigger.validator (module,description) VALUES ('MaxPassiveDelayAge','Check that the event is not too far past the delay_field time -- requires a max_delay_age interval parameter');

-- Sample Pre-due Notice --

INSERT INTO action_trigger.event_definition (id, active, owner, name, hook, validator, reactor, delay, delay_field, group_field, template) 
    VALUES (6, 'f', 1, '3 Day Courtesy Notice', 'checkout.due', 'CircIsOpen', 'SendEmail', '-3 days', 'due_date', 'usr', 
$$
[%- USE date -%]
[%- user = target.0.usr -%]
To: [%- params.recipient_email || user.email %]
From: [%- params.sender_email || default_sender %]
Subject: Courtesy Notice

Dear [% user.family_name %], [% user.first_given_name %]
As a reminder, the following items are due in 3 days.

[% FOR circ IN target %]
    Title: [% circ.target_copy.call_number.record.simple_record.title %] 
    Barcode: [% circ.target_copy.barcode %] 
    Due: [% date.format(helpers.format_date(circ.due_date), '%Y-%m-%d') %]
    Item Cost: [% helpers.get_copy_price(circ.target_copy) %]
    Library: [% circ.circ_lib.name %]
    Library Phone: [% circ.circ_lib.phone %]
[% END %]

$$);

INSERT INTO action_trigger.environment (event_def, path) VALUES 
    (6, 'target_copy.call_number.record.simple_record'),
    (6, 'usr'),
    (6, 'circ_lib.billing_address');

INSERT INTO action_trigger.event_params (event_def, param, value) VALUES
    (6, 'max_delay_age', '"1 day"');

-- also add the max delay age to the default overdue notice event def
INSERT INTO action_trigger.event_params (event_def, param, value) VALUES
    (1, 'max_delay_age', '"1 day"');
  
INSERT INTO action_trigger.validator (module,description) VALUES ('MinPassiveTargetAge','Check that the target is old enough to be used by this event -- requires a min_target_age interval parameter, and accepts an optional target_age_field to specify what time to use for offsetting');

INSERT INTO action_trigger.reactor (module,description) VALUES ('ApplyPatronPenalty','Applies the configured penalty to a patron.  Required named environment variables are "user", which refers to the user object, and "context_org", which refers to the org_unit object that acts as the focus for the penalty.');

INSERT INTO action_trigger.hook (
        key,
        core_type,
        description,
        passive
    ) VALUES (
        'hold_request.shelf_expires_soon',
        'ahr',
        'A hold on the shelf will expire there soon.',
        TRUE
    );

INSERT INTO action_trigger.event_definition (
        id,
        active,
        owner,
        name,
        hook,
        validator,
        reactor,
        delay,
        delay_field,
        group_field,
        template
    ) VALUES (
        7,
        FALSE,
        1,
        'Hold Expires from Shelf Soon',
        'hold_request.shelf_expires_soon',
        'HoldIsAvailable',
        'SendEmail',
        '- 1 DAY',
        'shelf_expire_time',
        'usr',
$$
[%- USE date -%]
[%- user = target.0.usr -%]
To: [%- params.recipient_email || user.email %]
From: [%- params.sender_email || default_sender %]
Subject: Hold Available Notification

Dear [% user.family_name %], [% user.first_given_name %]
You requested holds on the following item(s), which are available for
pickup, but these holds will soon expire.

[% FOR hold IN target %]
    [%- data = helpers.get_copy_bib_basics(hold.current_copy.id) -%]
    Title: [% data.title %]
    Author: [% data.author %]
    Library: [% hold.pickup_lib.name %]
[% END %]
$$
    );

INSERT INTO action_trigger.environment (
        event_def,
        path
    ) VALUES
    ( 7, 'current_copy'),
    ( 7, 'pickup_lib.billing_address'),
    ( 7, 'usr');

INSERT INTO action_trigger.hook (
        key,
        core_type,
        description,
        passive
    ) VALUES (
        'hold_request.long_wait',
        'ahr',
        'A patron has been waiting on a hold to be fulfilled for a long time.',
        TRUE
    );

INSERT INTO action_trigger.event_definition (
        id,
        active,
        owner,
        name,
        hook,
        validator,
        reactor,
        delay,
        delay_field,
        group_field,
        template
    ) VALUES (
        9,
        FALSE,
        1,
        'Hold waiting for pickup for long time',
        'hold_request.long_wait',
        'NOOP_True',
        'SendEmail',
        '6 MONTHS',
        'request_time',
        'usr',
$$
[%- USE date -%]
[%- user = target.0.usr -%]
To: [%- params.recipient_email || user.email %]
From: [%- params.sender_email || default_sender %]
Subject: Long Wait Hold Notification

Dear [% user.family_name %], [% user.first_given_name %]

You requested hold(s) on the following item(s), but unfortunately
we have not been able to fulfill your request after a considerable
length of time.  If you would still like to receive these items,
no action is required.

[% FOR hold IN target %]
    Title: [% hold.bib_rec.bib_record.simple_record.title %]
    Author: [% hold.bib_rec.bib_record.simple_record.author %]
[% END %]
$$
);

INSERT INTO action_trigger.environment (
        event_def,
        path
    ) VALUES
    (9, 'pickup_lib'),
    (9, 'usr'),
    (9, 'bib_rec.bib_record.simple_record');

INSERT INTO action_trigger.hook (key, core_type, description, passive) 
    VALUES (
        'format.selfcheck.checkout',
        'circ',
        'Formats circ objects for self-checkout receipt',
        TRUE
    );

INSERT INTO action_trigger.event_definition (id, active, owner, name, hook, validator, reactor, group_field, granularity, template )
    VALUES (
        10,
        TRUE,
        1,
        'Self-Checkout Receipt',
        'format.selfcheck.checkout',
        'NOOP_True',
        'ProcessTemplate',
        'usr',
        'print-on-demand',
$$
[%- USE date -%]
[%- SET user = target.0.usr -%]
[%- SET lib = target.0.circ_lib -%]
[%- SET lib_addr = target.0.circ_lib.billing_address -%]
[%- SET hours = lib.hours_of_operation -%]
<div>
    <style> li { padding: 8px; margin 5px; }</style>
    <div>[% date.format %]</div>
    <div>[% lib.name %]</div>
    <div>[% lib_addr.street1 %] [% lib_addr.street2 %]</div>
    <div>[% lib_addr.city %], [% lib_addr.state %] [% lb_addr.post_code %]</div>
    <div>[% lib.phone %]</div>
    <br/>

    [% user.family_name %], [% user.first_given_name %]
    <ol>
    [% FOR circ IN target %]
        [%-
            SET idx = loop.count - 1;
            SET udata =  user_data.$idx
        -%]
        <li>
            <div>[% helpers.get_copy_bib_basics(circ.target_copy.id).title %]</div>
            <div>Barcode: [% circ.target_copy.barcode %]</div>
            [% IF udata.renewal_failure %]
                <div style='color:red;'>Renewal Failed</div>
            [% ELSE %]
                <div>Due Date: [% date.format(helpers.format_date(circ.due_date), '%Y-%m-%d') %]</div>
            [% END %]
        </li>
    [% END %]
    </ol>
    
    <div>
        Library Hours
        [%- BLOCK format_time; date.format(time _ ' 1/1/1000', format='%I:%M %p'); END -%]
        <div>
            Monday 
            [% PROCESS format_time time = hours.dow_0_open %] 
            [% PROCESS format_time time = hours.dow_0_close %] 
        </div>
        <div>
            Tuesday 
            [% PROCESS format_time time = hours.dow_1_open %] 
            [% PROCESS format_time time = hours.dow_1_close %] 
        </div>
        <div>
            Wednesday 
            [% PROCESS format_time time = hours.dow_2_open %] 
            [% PROCESS format_time time = hours.dow_2_close %] 
        </div>
        <div>
            Thursday
            [% PROCESS format_time time = hours.dow_3_open %] 
            [% PROCESS format_time time = hours.dow_3_close %] 
        </div>
        <div>
            Friday
            [% PROCESS format_time time = hours.dow_4_open %] 
            [% PROCESS format_time time = hours.dow_4_close %] 
        </div>
        <div>
            Saturday
            [% PROCESS format_time time = hours.dow_5_open %] 
            [% PROCESS format_time time = hours.dow_5_close %] 
        </div>
        <div>
            Sunday 
            [% PROCESS format_time time = hours.dow_6_open %] 
            [% PROCESS format_time time = hours.dow_6_close %] 
        </div>
    </div>
</div>
$$
);

INSERT INTO action_trigger.environment ( event_def, path) VALUES
    ( 10, 'target_copy'),
    ( 10, 'circ_lib.billing_address'),
    ( 10, 'circ_lib.hours_of_operation'),
    ( 10, 'usr');

INSERT INTO action_trigger.hook (key, core_type, description, passive) 
    VALUES (
        'format.selfcheck.items_out',
        'circ',
        'Formats items out for self-checkout receipt',
        TRUE
    );

INSERT INTO action_trigger.event_definition (id, active, owner, name, hook, validator, reactor, group_field, granularity, template )
    VALUES (
        11,
        TRUE,
        1,
        'Self-Checkout Items Out Receipt',
        'format.selfcheck.items_out',
        'NOOP_True',
        'ProcessTemplate',
        'usr',
        'print-on-demand',
$$
[%- USE date -%]
[%- SET user = target.0.usr -%]
<div>
    <style> li { padding: 8px; margin 5px; }</style>
    <div>[% date.format %]</div>
    <br/>

    [% user.family_name %], [% user.first_given_name %]
    <ol>
    [% FOR circ IN target %]
        <li>
            <div>[% helpers.get_copy_bib_basics(circ.target_copy.id).title %]</div>
            <div>Barcode: [% circ.target_copy.barcode %]</div>
            <div>Due Date: [% date.format(helpers.format_date(circ.due_date), '%Y-%m-%d') %]</div>
        </li>
    [% END %]
    </ol>
</div>
$$
);


INSERT INTO action_trigger.environment ( event_def, path) VALUES
    ( 11, 'target_copy'),
    ( 11, 'circ_lib.billing_address'),
    ( 11, 'circ_lib.hours_of_operation'),
    ( 11, 'usr');

INSERT INTO action_trigger.hook (key, core_type, description, passive) 
    VALUES (
        'format.selfcheck.holds',
        'ahr',
        'Formats holds for self-checkout receipt',
        TRUE
    );

INSERT INTO action_trigger.event_definition (id, active, owner, name, hook, validator, reactor, group_field, granularity, template )
    VALUES (
        12,
        TRUE,
        1,
        'Self-Checkout Holds Receipt',
        'format.selfcheck.holds',
        'NOOP_True',
        'ProcessTemplate',
        'usr',
        'print-on-demand',
$$
[%- USE date -%]
[%- SET user = target.0.usr -%]
<div>
    <style> li { padding: 8px; margin 5px; }</style>
    <div>[% date.format %]</div>
    <br/>

    [% user.family_name %], [% user.first_given_name %]
    <ol>
    [% FOR hold IN target %]
        [%-
            SET idx = loop.count - 1;
            SET udata =  user_data.$idx
        -%]
        <li>
            <div>Title: [% hold.bib_rec.bib_record.simple_record.title %]</div>
            <div>Author: [% hold.bib_rec.bib_record.simple_record.author %]</div>
            <div>Pickup Location: [% hold.pickup_lib.name %]</div>
            <div>Status: 
                [%- IF udata.ready -%]
                    Ready for pickup
                [% ELSE %]
                    #[% udata.queue_position %] of [% udata.potential_copies %] copies.
                [% END %]
            </div>
        </li>
    [% END %]
    </ol>
</div>
$$
);


INSERT INTO action_trigger.environment ( event_def, path) VALUES
    ( 12, 'bib_rec.bib_record.simple_record'),
    ( 12, 'pickup_lib'),
    ( 12, 'usr');

INSERT INTO action_trigger.hook (key, core_type, description, passive) 
    VALUES (
        'format.selfcheck.fines',
        'au',
        'Formats fines for self-checkout receipt',
        TRUE
    );

INSERT INTO action_trigger.event_definition (id, active, owner, name, hook, validator, reactor, granularity, template )
    VALUES (
        13,
        TRUE,
        1,
        'Self-Checkout Fines Receipt',
        'format.selfcheck.fines',
        'NOOP_True',
        'ProcessTemplate',
        'print-on-demand',
$$
[%- USE date -%]
[%- SET user = target -%]
<div>
    <style> li { padding: 8px; margin 5px; }</style>
    <div>[% date.format %]</div>
    <br/>

    [% user.family_name %], [% user.first_given_name %]
    <ol>
    [% FOR xact IN user.open_billable_transactions_summary %]
        <li>
            <div>Details: 
                [% IF xact.xact_type == 'circulation' %]
                    [%- helpers.get_copy_bib_basics(xact.circulation.target_copy).title -%]
                [% ELSE %]
                    [%- xact.last_billing_type -%]
                [% END %]
            </div>
            <div>Total Billed: [% xact.total_owed %]</div>
            <div>Total Paid: [% xact.total_paid %]</div>
            <div>Balance Owed : [% xact.balance_owed %]</div>
        </li>
    [% END %]
    </ol>
</div>
$$
);

INSERT INTO action_trigger.environment ( event_def, path) VALUES
    ( 13, 'open_billable_transactions_summary.circulation' );

INSERT INTO action_trigger.reactor (module,description) VALUES
(   'SendFile',
    oils_i18n_gettext(
        'SendFile',
        'Build and transfer a file to a remote server.  Required parameter "remote_host" specifying target server.  Optional parameters: remote_user, remote_password, remote_account, port, type (FTP, SFTP or SCP), and debug.',
        'atreact',
        'description'
    )
);

INSERT INTO action_trigger.hook (key, core_type, description, passive) 
    VALUES (
        'format.acqli.html',
        'jub',
        'Formats lineitem worksheet for titles received',
        TRUE
    );

INSERT INTO action_trigger.event_definition (id, active, owner, name, hook, validator, reactor, granularity, template)
    VALUES (
        14,
        TRUE,
        1,
        'Lineitem Worksheet',
        'format.acqli.html',
        'NOOP_True',
        'ProcessTemplate',
        'print-on-demand',
$$
[%- USE date -%]
[%- SET li = target; -%]
<div class="wrapper">
    <div class="summary" style='font-size:110%; font-weight:bold;'>

        <div>Title: [% helpers.get_li_attr("title", "", li.attributes) %]</div>
        <div>Author: [% helpers.get_li_attr("author", "", li.attributes) %]</div>
        <div class="count">Item Count: [% li.lineitem_details.size %]</div>
        <div class="lineid">Lineitem ID: [% li.id %]</div>

        [% IF li.distribution_formulas.size > 0 %]
            [% SET forms = [] %]
            [% FOREACH form IN li.distribution_formulas; forms.push(form.formula.name); END %]
            <div>Distribution Formulas: [% forms.join(',') %]</div>
        [% END %]

        [% IF li.lineitem_notes.size > 0 %]
            Lineitem Notes:
            <ul>
                [%- FOR note IN li.lineitem_notes -%]
                    <li>
                    [% IF note.alert_text %]
                        [% note.alert_text.code -%] 
                        [% IF note.value -%]
                            : [% note.value %]
                        [% END %]
                    [% ELSE %]
                        [% note.value -%] 
                    [% END %]
                    </li>
                [% END %]
            </ul>
        [% END %]
    </div>
    <br/>
    <table>
        <thead>
            <tr>
                <th>Branch</th>
                <th>Barcode</th>
                <th>Call Number</th>
                <th>Fund</th>
                <th>Recd.</th>
                <th>Notes</th>
            </tr>
        </thead>
        <tbody>
        [% FOREACH detail IN li.lineitem_details.sort('owning_lib') %]
            [% 
                IF copy.eg_copy_id;
                    SET copy = copy.eg_copy_id;
                    SET cn_label = copy.call_number.label;
                ELSE; 
                    SET copy = detail; 
                    SET cn_label = detail.cn_label;
                END 
            %]
            <tr>
                <!-- acq.lineitem_detail.id = [%- detail.id -%] -->
                <td style='padding:5px;'>[% detail.owning_lib.shortname %]</td>
                <td style='padding:5px;'>[% IF copy.barcode   %]<span class="barcode"  >[% detail.barcode   %]</span>[% END %]</td>
                <td style='padding:5px;'>[% IF cn_label %]<span class="cn_label" >[% cn_label  %]</span>[% END %]</td>
                <td style='padding:5px;'>[% IF detail.fund %]<span class="fund">[% detail.fund.code %] ([% detail.fund.year %])</span>[% END %]</td>
                <td style='padding:5px;'>[% IF detail.recv_time %]<span class="recv_time">[% detail.recv_time %]</span>[% END %]</td>
                <td style='padding:5px;'>[% detail.note %]</td>
            </tr>
        [% END %]
        </tbody>
    </table>
</div>
$$
);


INSERT INTO action_trigger.environment (event_def, path) VALUES
    ( 14, 'attributes' ),
    ( 14, 'lineitem_details' ),
    ( 14, 'lineitem_details.owning_lib' ),
    ( 14, 'lineitem_notes' )
;

INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES (
        'aur.ordered',
        'aur', 
        oils_i18n_gettext(
            'aur.ordered',
            'A patron acquisition request has been marked On-Order.',
            'ath',
            'description'
        ), 
        TRUE
    ), (
        'aur.received', 
        'aur', 
        oils_i18n_gettext(
            'aur.received', 
            'A patron acquisition request has been marked Received.',
            'ath',
            'description'
        ),
        TRUE
    ), (
        'aur.cancelled',
        'aur',
        oils_i18n_gettext(
            'aur.cancelled',
            'A patron acquisition request has been marked Cancelled.',
            'ath',
            'description'
        ),
        TRUE
    )
;

INSERT INTO action_trigger.validator (module,description) VALUES (
        'Acq::UserRequestOrdered',
        oils_i18n_gettext(
            'Acq::UserRequestOrdered',
            'Tests to see if the corresponding Line Item has a state of "on-order".',
            'atval',
            'description'
        )
    ), (
        'Acq::UserRequestReceived',
        oils_i18n_gettext(
            'Acq::UserRequestReceived',
            'Tests to see if the corresponding Line Item has a state of "received".',
            'atval',
            'description'
        )
    ), (
        'Acq::UserRequestCancelled',
        oils_i18n_gettext(
            'Acq::UserRequestCancelled',
            'Tests to see if the corresponding Line Item has a state of "cancelled".',
            'atval',
            'description'
        )
    )
;

-- What was event_definition #15 in v1.6.1 will be recreated as #20.  This
-- renumbering requires some juggling:
--
-- 1. Update any child rows to point to #20.  These updates will temporarily
-- violate foreign key constraints, but that's okay as long as we create
-- #20 before committing.
--
-- 2. Delete the old #15.
--
-- 3. Insert the new #15.
--
-- 4. Insert #20.
--
-- We could combine steps 2 and 3 into a single update, but that would create
-- additional opportunities for typos, since we already have the insert from
-- an upgrade script.

UPDATE action_trigger.environment
SET event_def = 20
WHERE event_def = 15;

UPDATE action_trigger.event
SET event_def = 20
WHERE event_def = 15;

UPDATE action_trigger.event_params
SET event_def = 20
WHERE event_def = 15;

DELETE FROM action_trigger.event_definition
WHERE id = 15;

INSERT INTO action_trigger.event_definition (
        id,
        active,
        owner,
        name,
        hook,
        validator,
        reactor,
        template
    ) VALUES (
        15,
        FALSE,
        1,
        'Email Notice: Patron Acquisition Request marked On-Order.',
        'aur.ordered',
        'Acq::UserRequestOrdered',
        'SendEmail',
$$
[%- USE date -%]
[%- SET li = target.lineitem; -%]
[%- SET user = target.usr -%]
[%- SET title = helpers.get_li_attr("title", "", li.attributes) -%]
[%- SET author = helpers.get_li_attr("author", "", li.attributes) -%]
[%- SET edition = helpers.get_li_attr("edition", "", li.attributes) -%]
[%- SET isbn = helpers.get_li_attr("isbn", "", li.attributes) -%]
[%- SET publisher = helpers.get_li_attr("publisher", "", li.attributes) -%]
[%- SET pubdate = helpers.get_li_attr("pubdate", "", li.attributes) -%]

To: [%- params.recipient_email || user.email %]
From: [%- params.sender_email || default_sender %]
Subject: Acquisition Request Notification

Dear [% user.family_name %], [% user.first_given_name %]
Our records indicate the following acquisition request has been placed on order.

Title: [% title %]
[% IF author %]Author: [% author %][% END %]
[% IF edition %]Edition: [% edition %][% END %]
[% IF isbn %]ISBN: [% isbn %][% END %]
[% IF publisher %]Publisher: [% publisher %][% END %]
[% IF pubdate %]Publication Date: [% pubdate %][% END %]
Lineitem ID: [% li.id %]
$$
    ), (
        16,
        FALSE,
        1,
        'Email Notice: Patron Acquisition Request marked Received.',
        'aur.received',
        'Acq::UserRequestReceived',
        'SendEmail',
$$
[%- USE date -%]
[%- SET li = target.lineitem; -%]
[%- SET user = target.usr -%]
[%- SET title = helpers.get_li_attr("title", "", li.attributes) %]
[%- SET author = helpers.get_li_attr("author", "", li.attributes) %]
[%- SET edition = helpers.get_li_attr("edition", "", li.attributes) %]
[%- SET isbn = helpers.get_li_attr("isbn", "", li.attributes) %]
[%- SET publisher = helpers.get_li_attr("publisher", "", li.attributes) -%]
[%- SET pubdate = helpers.get_li_attr("pubdate", "", li.attributes) -%]

To: [%- params.recipient_email || user.email %]
From: [%- params.sender_email || default_sender %]
Subject: Acquisition Request Notification

Dear [% user.family_name %], [% user.first_given_name %]
Our records indicate the materials for the following acquisition request have been received.

Title: [% title %]
[% IF author %]Author: [% author %][% END %]
[% IF edition %]Edition: [% edition %][% END %]
[% IF isbn %]ISBN: [% isbn %][% END %]
[% IF publisher %]Publisher: [% publisher %][% END %]
[% IF pubdate %]Publication Date: [% pubdate %][% END %]
Lineitem ID: [% li.id %]
$$
    ), (
        17,
        FALSE,
        1,
        'Email Notice: Patron Acquisition Request marked Cancelled.',
        'aur.cancelled',
        'Acq::UserRequestCancelled',
        'SendEmail',
$$
[%- USE date -%]
[%- SET li = target.lineitem; -%]
[%- SET user = target.usr -%]
[%- SET title = helpers.get_li_attr("title", "", li.attributes) %]
[%- SET author = helpers.get_li_attr("author", "", li.attributes) %]
[%- SET edition = helpers.get_li_attr("edition", "", li.attributes) %]
[%- SET isbn = helpers.get_li_attr("isbn", "", li.attributes) %]
[%- SET publisher = helpers.get_li_attr("publisher", "", li.attributes) -%]
[%- SET pubdate = helpers.get_li_attr("pubdate", "", li.attributes) -%]

To: [%- params.recipient_email || user.email %]
From: [%- params.sender_email || default_sender %]
Subject: Acquisition Request Notification

Dear [% user.family_name %], [% user.first_given_name %]
Our records indicate the following acquisition request has been cancelled.

Title: [% title %]
[% IF author %]Author: [% author %][% END %]
[% IF edition %]Edition: [% edition %][% END %]
[% IF isbn %]ISBN: [% isbn %][% END %]
[% IF publisher %]Publisher: [% publisher %][% END %]
[% IF pubdate %]Publication Date: [% pubdate %][% END %]
Lineitem ID: [% li.id %]
$$
    );

INSERT INTO action_trigger.environment (
        event_def,
        path
    ) VALUES 
        ( 15, 'lineitem' ),
        ( 15, 'lineitem.attributes' ),
        ( 15, 'usr' ),

        ( 16, 'lineitem' ),
        ( 16, 'lineitem.attributes' ),
        ( 16, 'usr' ),

        ( 17, 'lineitem' ),
        ( 17, 'lineitem.attributes' ),
        ( 17, 'usr' )
    ;

INSERT INTO action_trigger.event_definition
(id, active, owner, name, hook, validator, reactor, cleanup_success, cleanup_failure, delay, delay_field, group_field, template) VALUES
(23, true, 1, 'PO JEDI', 'acqpo.activated', 'Acq::PurchaseOrderEDIRequired', 'GeneratePurchaseOrderJEDI', NULL, NULL, '00:05:00', NULL, NULL,
$$
[%- USE date -%]
[%# start JEDI document 
  # Vendor specific kludges:
  # BT      - vendcode goes to NAD/BY *suffix*  w/ 91 qualifier
  # INGRAM  - vendcode goes to NAD/BY *segment* w/ 91 qualifier (separately)
  # BRODART - vendcode goes to FTX segment (lineitem level)
-%]
[%- 
IF target.provider.edi_default.vendcode && target.provider.code == 'BRODART';
    xtra_ftx = target.provider.edi_default.vendcode;
END;
-%]
[%- BLOCK big_block -%]
{
   "recipient":"[% target.provider.san %]",
   "sender":"[% target.ordering_agency.mailing_address.san %]",
   "body": [{
     "ORDERS":[ "order", {
        "po_number":[% target.id %],
        "date":"[% date.format(date.now, '%Y%m%d') %]",
        "buyer":[
            [%   IF   target.provider.edi_default.vendcode && (target.provider.code == 'BT' || target.provider.name.match('(?i)^BAKER & TAYLOR'))  -%]
                {"id-qualifier": 91, "id":"[% target.ordering_agency.mailing_address.san _ ' ' _ target.provider.edi_default.vendcode %]"}
            [%- ELSIF target.provider.edi_default.vendcode && target.provider.code == 'INGRAM' -%]
                {"id":"[% target.ordering_agency.mailing_address.san %]"},
                {"id-qualifier": 91, "id":"[% target.provider.edi_default.vendcode %]"}
            [%- ELSE -%]
                {"id":"[% target.ordering_agency.mailing_address.san %]"}
            [%- END -%]
        ],
        "vendor":[
            [%- # target.provider.name (target.provider.id) -%]
            "[% target.provider.san %]",
            {"id-qualifier": 92, "id":"[% target.provider.id %]"}
        ],
        "currency":"[% target.provider.currency_type %]",
                
        "items":[
        [%- FOR li IN target.lineitems %]
        {
            "line_index":"[% li.id %]",
            "identifiers":[   [%-# li.isbns = helpers.get_li_isbns(li.attributes) %]
            [% FOR isbn IN helpers.get_li_isbns(li.attributes) -%]
                [% IF isbn.length == 13 -%]
                {"id-qualifier":"EN","id":"[% isbn %]"},
                [% ELSE -%]
                {"id-qualifier":"IB","id":"[% isbn %]"},
                [%- END %]
            [% END %]
                {"id-qualifier":"IN","id":"[% li.id %]"}
            ],
            "price":[% li.estimated_unit_price || '0.00' %],
            "desc":[
                {"BTI":"[% helpers.get_li_attr('title',     '', li.attributes) %]"},
                {"BPU":"[% helpers.get_li_attr('publisher', '', li.attributes) %]"},
                {"BPD":"[% helpers.get_li_attr('pubdate',   '', li.attributes) %]"},
                {"BPH":"[% helpers.get_li_attr('pagination','', li.attributes) %]"}
            ],
            [%- ftx_vals = []; 
                FOR note IN li.lineitem_notes; 
                    NEXT UNLESS note.vendor_public == 't'; 
                    ftx_vals.push(note.value); 
                END; 
                IF xtra_ftx;           ftx_vals.unshift(xtra_ftx); END; 
                IF ftx_vals.size == 0; ftx_vals.unshift('');       END;  # BT needs FTX+LIN for every LI, even if it is an empty one
            -%]

            "free-text":[ 
                [% FOR note IN ftx_vals -%] "[% note %]"[% UNLESS loop.last %], [% END %][% END %] 
            ],            
            "quantity":[% li.lineitem_details.size %]
        }[% UNLESS loop.last %],[% END %]
        [%-# TODO: lineitem details (later) -%]
        [% END %]
        ],
        "line_items":[% target.lineitems.size %]
     }]  [%# close ORDERS array %]
   }]    [%# close  body  array %]
}
[% END %]
[% tempo = PROCESS big_block; helpers.escape_json(tempo) %]

$$);

INSERT INTO action_trigger.environment (event_def, path) VALUES 
  (23, 'lineitems.attributes'), 
  (23, 'lineitems.lineitem_details'), 
  (23, 'lineitems.lineitem_notes'), 
  (23, 'ordering_agency.mailing_address'), 
  (23, 'provider');

UPDATE action_trigger.event_definition SET template = 
$$
[%- USE date -%]
[%-
    # find a lineitem attribute by name and optional type
    BLOCK get_li_attr;
        FOR attr IN li.attributes;
            IF attr.attr_name == attr_name;
                IF !attr_type OR attr_type == attr.attr_type;
                    attr.attr_value;
                    LAST;
                END;
            END;
        END;
    END
-%]

<h2>Purchase Order [% target.id %]</h2>
<br/>
date <b>[% date.format(date.now, '%Y%m%d') %]</b>
<br/>

<style>
    table td { padding:5px; border:1px solid #aaa;}
    table { width:95%; border-collapse:collapse; }
    #vendor-notes { padding:5px; border:1px solid #aaa; }
</style>
<table id='vendor-table'>
  <tr>
    <td valign='top'>Vendor</td>
    <td>
      <div>[% target.provider.name %]</div>
      <div>[% target.provider.addresses.0.street1 %]</div>
      <div>[% target.provider.addresses.0.street2 %]</div>
      <div>[% target.provider.addresses.0.city %]</div>
      <div>[% target.provider.addresses.0.state %]</div>
      <div>[% target.provider.addresses.0.country %]</div>
      <div>[% target.provider.addresses.0.post_code %]</div>
    </td>
    <td valign='top'>Ship to / Bill to</td>
    <td>
      <div>[% target.ordering_agency.name %]</div>
      <div>[% target.ordering_agency.billing_address.street1 %]</div>
      <div>[% target.ordering_agency.billing_address.street2 %]</div>
      <div>[% target.ordering_agency.billing_address.city %]</div>
      <div>[% target.ordering_agency.billing_address.state %]</div>
      <div>[% target.ordering_agency.billing_address.country %]</div>
      <div>[% target.ordering_agency.billing_address.post_code %]</div>
    </td>
  </tr>
</table>

<br/><br/>
<fieldset id='vendor-notes'>
    <legend>Notes to the Vendor</legend>
    <ul>
    [% FOR note IN target.notes %]
        [% IF note.vendor_public == 't' %]
            <li>[% note.value %]</li>
        [% END %]
    [% END %]
    </ul>
</fieldset>
<br/><br/>

<table>
  <thead>
    <tr>
      <th>PO#</th>
      <th>ISBN or Item #</th>
      <th>Title</th>
      <th>Quantity</th>
      <th>Unit Price</th>
      <th>Line Total</th>
      <th>Notes</th>
    </tr>
  </thead>
  <tbody>

  [% subtotal = 0 %]
  [% FOR li IN target.lineitems %]

  <tr>
    [% count = li.lineitem_details.size %]
    [% price = li.estimated_unit_price %]
    [% litotal = (price * count) %]
    [% subtotal = subtotal + litotal %]
    [% isbn = PROCESS get_li_attr attr_name = 'isbn' %]
    [% ident = PROCESS get_li_attr attr_name = 'identifier' %]

    <td>[% target.id %]</td>
    <td>[% isbn || ident %]</td>
    <td>[% PROCESS get_li_attr attr_name = 'title' %]</td>
    <td>[% count %]</td>
    <td>[% price %]</td>
    <td>[% litotal %]</td>
    <td>
        <ul>
        [% FOR note IN li.lineitem_notes %]
            [% IF note.vendor_public == 't' %]
                <li>[% note.value %]</li>
            [% END %]
        [% END %]
        </ul>
    </td>
  </tr>
  [% END %]
  <tr>
    <td/><td/><td/><td/>
    <td>Subtotal</td>
    <td>[% subtotal %]</td>
  </tr>
  </tbody>
</table>

<br/>

Total Line Item Count: [% target.lineitems.size %]
$$
WHERE id = 4;

INSERT INTO action_trigger.environment (event_def, path) VALUES 
    (4, 'lineitems.lineitem_notes'),
    (4, 'notes');

INSERT INTO action_trigger.environment (event_def, path) VALUES
    ( 14, 'lineitem_notes.alert_text' ),
    ( 14, 'distribution_formulas.formula' ),
    ( 14, 'lineitem_details.fund' ),
    ( 14, 'lineitem_details.eg_copy_id' ),
    ( 14, 'lineitem_details.eg_copy_id.call_number' )
;

INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES (
        'aur.created',
        'aur',
        oils_i18n_gettext(
            'aur.created',
            'A patron has made an acquisitions request.',
            'ath',
            'description'
        ),
        TRUE
    ), (
        'aur.rejected',
        'aur',
        oils_i18n_gettext(
            'aur.rejected',
            'A patron acquisition request has been rejected.',
            'ath',
            'description'
        ),
        TRUE
    )
;

INSERT INTO action_trigger.event_definition (
        id,
        active,
        owner,
        name,
        hook,
        validator,
        reactor,
        template
    ) VALUES (
        18,
        FALSE,
        1,
        'Email Notice: Acquisition Request created.',
        'aur.created',
        'NOOP_True',
        'SendEmail',
$$
[%- USE date -%]
[%- SET user = target.usr -%]
[%- SET title = target.title -%]
[%- SET author = target.author -%]
[%- SET isxn = target.isxn -%]
[%- SET publisher = target.publisher -%]
[%- SET pubdate = target.pubdate -%]

To: [%- params.recipient_email || user.email %]
From: [%- params.sender_email || default_sender %]
Subject: Acquisition Request Notification

Dear [% user.family_name %], [% user.first_given_name %]
Our records indicate that you have made the following acquisition request:

Title: [% title %]
[% IF author %]Author: [% author %][% END %]
[% IF edition %]Edition: [% edition %][% END %]
[% IF isbn %]ISXN: [% isxn %][% END %]
[% IF publisher %]Publisher: [% publisher %][% END %]
[% IF pubdate %]Publication Date: [% pubdate %][% END %]
$$
    ), (
        19,
        FALSE,
        1,
        'Email Notice: Acquisition Request Rejected.',
        'aur.rejected',
        'NOOP_True',
        'SendEmail',
$$
[%- USE date -%]
[%- SET user = target.usr -%]
[%- SET title = target.title -%]
[%- SET author = target.author -%]
[%- SET isxn = target.isxn -%]
[%- SET publisher = target.publisher -%]
[%- SET pubdate = target.pubdate -%]
[%- SET cancel_reason = target.cancel_reason.description -%]

To: [%- params.recipient_email || user.email %]
From: [%- params.sender_email || default_sender %]
Subject: Acquisition Request Notification

Dear [% user.family_name %], [% user.first_given_name %]
Our records indicate the following acquisition request has been rejected for this reason: [% cancel_reason %]

Title: [% title %]
[% IF author %]Author: [% author %][% END %]
[% IF edition %]Edition: [% edition %][% END %]
[% IF isbn %]ISBN: [% isbn %][% END %]
[% IF publisher %]Publisher: [% publisher %][% END %]
[% IF pubdate %]Publication Date: [% pubdate %][% END %]
$$
    );

INSERT INTO action_trigger.environment (
        event_def,
        path
    ) VALUES 
        ( 18, 'usr' ),
        ( 19, 'usr' ),
        ( 19, 'cancel_reason' )
    ;

INSERT INTO action_trigger.event_definition (id, active, owner, name, hook, validator, reactor, delay, template) 
    VALUES (20, 'f', 1, 'Password reset request notification', 'password.reset_request', 'NOOP_True', 'SendEmail', '00:00:01',
$$
[%- USE date -%]
[%- user = target.usr -%]
To: [%- params.recipient_email || user.email %]
From: [%- params.sender_email || user.home_ou.email || default_sender %]
Subject: [% user.home_ou.name %]: library account password reset request
  
You have received this message because you, or somebody else, requested a reset
of your library system password. If you did not request a reset of your library
system password, just ignore this message and your current password will
continue to work.

If you did request a reset of your library system password, please perform
the following steps to continue the process of resetting your password:

1. Open the following link in a web browser: https://[% params.hostname %]/opac/password/[% params.locale || 'en-US' %]/[% target.uuid %]
The browser displays a password reset form.

2. Enter your new password in the password reset form in the browser. You must
enter the password twice to ensure that you do not make a mistake. If the
passwords match, you will then be able to log in to your library system account
with the new password.

$$);

INSERT INTO action_trigger.hook (key, core_type, description, passive)
    VALUES (
        'format.acqcle.html',
        'acqcle',
        'Formats claim events into a voucher',
        TRUE
    );

INSERT INTO action_trigger.event_definition (
        id, active, owner, name, hook, group_field,
        validator, reactor, granularity, template
    ) VALUES (
        21,
        TRUE,
        1,
        'Claim Voucher',
        'format.acqcle.html',
        'claim',
        'NOOP_True',
        'ProcessTemplate',
        'print-on-demand',
$$
[%- USE date -%]
[%- SET claim = target.0.claim -%]
<!-- This will need refined/prettified. -->
<div class="acq-claim-voucher">
    <h2>Claim: [% claim.id %] ([% claim.type.code %])</h2>
    <h3>Against: [%- helpers.get_li_attr("title", "", claim.lineitem_detail.lineitem.attributes) -%]</h3>
    <ul>
        [% FOR event IN target %]
        <li>
            Event type: [% event.type.code %]
            [% IF event.type.library_initiated %](Library initiated)[% END %]
            <br />
            Event date: [% event.event_date %]<br />
            Order date: [% event.claim.lineitem_detail.lineitem.purchase_order.order_date %]<br />
            Expected receive date: [% event.claim.lineitem_detail.lineitem.expected_recv_time %]<br />
            Initiated by: [% event.creator.family_name %], [% event.creator.first_given_name %] [% event.creator.second_given_name %]<br />
            Barcode: [% event.claim.lineitem_detail.barcode %]; Fund:
            [% event.claim.lineitem_detail.fund.code %]
            ([% event.claim.lineitem_detail.fund.year %])
        </li>
        [% END %]
    </ul>
</div>
$$
);

INSERT INTO action_trigger.environment (event_def, path) VALUES
    (21, 'claim'),
    (21, 'claim.type'),
    (21, 'claim.lineitem_detail'),
    (21, 'claim.lineitem_detail.fund'),
    (21, 'claim.lineitem_detail.lineitem.attributes'),
    (21, 'claim.lineitem_detail.lineitem.purchase_order'),
    (21, 'creator'),
    (21, 'type')
;

INSERT INTO action_trigger.hook (key, core_type, description, passive)
    VALUES (
        'format.acqinv.html',
        'acqinv',
        'Formats invoices into a voucher',
        TRUE
    );

INSERT INTO action_trigger.event_definition (
        id, active, owner, name, hook,
        validator, reactor, granularity, template
    ) VALUES (
        22,
        TRUE,
        1,
        'Invoice',
        'format.acqinv.html',
        'NOOP_True',
        'ProcessTemplate',
        'print-on-demand',
$$
[% FILTER collapse %]
[%- SET invoice = target -%]
<!-- This lacks totals, info about funds (for invoice entries,
    funds are per-LID!), and general refinement -->
<div class="acq-invoice-voucher">
    <h1>Invoice</h1>
    <div>
        <strong>No.</strong> [% invoice.inv_ident %]
        [% IF invoice.inv_type %]
            / <strong>Type:</strong>[% invoice.inv_type %]
        [% END %]
    </div>
    <div>
        <dl>
            [% BLOCK ent_with_address %]
            <dt>[% ent_label %]: [% ent.name %] ([% ent.code %])</dt>
            <dd>
                [% IF ent.addresses.0 %]
                    [% SET addr = ent.addresses.0 %]
                    [% addr.street1 %]<br />
                    [% IF addr.street2 %][% addr.street2 %]<br />[% END %]
                    [% addr.city %],
                    [% IF addr.county %] [% addr.county %], [% END %]
                    [% IF addr.state %] [% addr.state %] [% END %]
                    [% IF addr.post_code %][% addr.post_code %][% END %]<br />
                    [% IF addr.country %] [% addr.country %] [% END %]
                [% END %]
                <p>
                    [% IF ent.phone %] Phone: [% ent.phone %]<br />[% END %]
                    [% IF ent.fax_phone %] Fax: [% ent.fax_phone %]<br />[% END %]
                    [% IF ent.url %] URL: [% ent.url %]<br />[% END %]
                    [% IF ent.email %] E-mail: [% ent.email %] [% END %]
                </p>
            </dd>
            [% END %]
            [% INCLUDE ent_with_address
                ent = invoice.provider
                ent_label = "Provider" %]
            [% INCLUDE ent_with_address
                ent = invoice.shipper
                ent_label = "Shipper" %]
            <dt>Receiver</dt>
            <dd>
                [% invoice.receiver.name %] ([% invoice.receiver.shortname %])
            </dd>
            <dt>Received</dt>
            <dd>
                [% helpers.format_date(invoice.recv_date) %] by
                [% invoice.recv_method %]
            </dd>
            [% IF invoice.note %]
                <dt>Note</dt>
                <dd>
                    [% invoice.note %]
                </dd>
            [% END %]
        </dl>
    </div>
    <ul>
        [% FOR entry IN invoice.entries %]
            <li>
                [% IF entry.lineitem %]
                    Title: [% helpers.get_li_attr(
                        "title", "", entry.lineitem.attributes
                    ) %]<br />
                    Author: [% helpers.get_li_attr(
                        "author", "", entry.lineitem.attributes
                    ) %]
                [% END %]
                [% IF entry.purchase_order %]
                    (PO: [% entry.purchase_order.name %])
                [% END %]<br />
                Invoice item count: [% entry.inv_item_count %]
                [% IF entry.phys_item_count %]
                    / Physical item count: [% entry.phys_item_count %]
                [% END %]
                <br />
                [% IF entry.cost_billed %]
                    Cost billed: [% entry.cost_billed %]
                    [% IF entry.billed_per_item %](per item)[% END %]
                    <br />
                [% END %]
                [% IF entry.actual_cost %]
                    Actual cost: [% entry.actual_cost %]<br />
                [% END %]
                [% IF entry.amount_paid %]
                    Amount paid: [% entry.amount_paid %]<br />
                [% END %]
                [% IF entry.note %]Note: [% entry.note %][% END %]
            </li>
        [% END %]
        [% FOR item IN invoice.items %]
            <li>
                [% IF item.inv_item_type %]
                    Item Type: [% item.inv_item_type %]<br />
                [% END %]
                [% IF item.title %]Title/Description:
                    [% item.title %]<br />
                [% END %]
                [% IF item.author %]Author: [% item.author %]<br />[% END %]
                [% IF item.purchase_order %]PO: [% item.purchase_order %]<br />[% END %]
                [% IF item.note %]Note: [% item.note %]<br />[% END %]
                [% IF item.cost_billed %]
                    Cost billed: [% item.cost_billed %]<br />
                [% END %]
                [% IF item.actual_cost %]
                    Actual cost: [% item.actual_cost %]<br />
                [% END %]
                [% IF item.amount_paid %]
                    Amount paid: [% item.amount_paid %]<br />
                [% END %]
            </li>
        [% END %]
    </ul>
</div>
[% END %]
$$
);

INSERT INTO action_trigger.environment (event_def, path) VALUES
    (22, 'provider'),
    (22, 'provider.addresses'),
    (22, 'shipper'),
    (22, 'shipper.addresses'),
    (22, 'receiver'),
    (22, 'entries'),
    (22, 'entries.purchase_order'),
    (22, 'entries.lineitem'),
    (22, 'entries.lineitem.attributes'),
    (22, 'items')
;

INSERT INTO action_trigger.environment (event_def, path) VALUES 
  (23, 'provider.edi_default');

INSERT INTO action_trigger.validator (module, description) 
    VALUES (
        'Acq::PurchaseOrderEDIRequired',
        oils_i18n_gettext(
            'Acq::PurchaseOrderEDIRequired',
            'Purchase order is delivered via EDI',
            'atval',
            'description'
        )
    );

INSERT INTO action_trigger.reactor (module, description)
    VALUES (
        'GeneratePurchaseOrderJEDI',
        oils_i18n_gettext(
            'GeneratePurchaseOrderJEDI',
            'Creates purchase order JEDI (JSON EDI) for subsequent EDI processing',
            'atreact',
            'description'
        )
    );

UPDATE action_trigger.hook 
    SET 
        key = 'acqpo.activated', 
        passive = FALSE,
        description = oils_i18n_gettext(
            'acqpo.activated',
            'Purchase order was activated',
            'ath',
            'description'
        )
    WHERE key = 'format.po.jedi';

-- We just changed a key in action_trigger.hook.  Now redirect any
-- child rows to point to the new key.  (There probably aren't any;
-- this is just a precaution against possible local modifications.)

UPDATE action_trigger.event_definition
SET hook = 'acqpo.activated'
WHERE hook = 'format.po.jedi';

INSERT INTO action_trigger.reactor (module, description) VALUES (
    'AstCall', 'Possibly place a phone call with Asterisk'
);

INSERT INTO
    action_trigger.event_definition (
        id, active, owner, name, hook, validator, reactor,
        cleanup_success, cleanup_failure, delay, delay_field, group_field,
        max_delay, granularity, usr_field, opt_in_setting, template
    ) VALUES (
        24,
        FALSE,
        1,
        'Telephone Overdue Notice',
        'checkout.due', 'NOOP_True', 'AstCall',
        DEFAULT, DEFAULT, '5 seconds', 'due_date', 'usr',
        DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        $$
[% phone = target.0.usr.day_phone | replace('[\s\-\(\)]', '') -%]
[% IF phone.match('^[2-9]') %][% country = 1 %][% ELSE %][% country = '' %][% END -%]
Channel: [% channel_prefix %]/[% country %][% phone %]
Context: overdue-test
MaxRetries: 1
RetryTime: 60
WaitTime: 30
Extension: 10
Archive: 1
Set: eg_user_id=[% target.0.usr.id %]
Set: items=[% target.size %]
Set: titlestring=[% titles = [] %][% FOR circ IN target %][% titles.push(circ.target_copy.call_number.record.simple_record.title) %][% END %][% titles.join(". ") %]
$$
    );

INSERT INTO
    action_trigger.environment (id, event_def, path)
    VALUES
        (DEFAULT, 24, 'target_copy.call_number.record.simple_record'),
        (DEFAULT, 24, 'usr')
    ;

INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES (
        'circ.format.history.email',
        'circ', 
        oils_i18n_gettext(
            'circ.format.history.email',
            'An email has been requested for a circ history.',
            'ath',
            'description'
        ), 
        FALSE
    )
    ,(
        'circ.format.history.print',
        'circ', 
        oils_i18n_gettext(
            'circ.format.history.print',
            'A circ history needs to be formatted for printing.',
            'ath',
            'description'
        ), 
        FALSE
    )
    ,(
        'ahr.format.history.email',
        'ahr', 
        oils_i18n_gettext(
            'ahr.format.history.email',
            'An email has been requested for a hold request history.',
            'ath',
            'description'
        ), 
        FALSE
    )
    ,(
        'ahr.format.history.print',
        'ahr', 
        oils_i18n_gettext(
            'ahr.format.history.print',
            'A hold request history needs to be formatted for printing.',
            'ath',
            'description'
        ), 
        FALSE
    )

;

INSERT INTO action_trigger.event_definition (
        id,
        active,
        owner,
        name,
        hook,
        validator,
        reactor,
        group_field,
        granularity,
        template
    ) VALUES (
        25,
        TRUE,
        1,
        'circ.history.email',
        'circ.format.history.email',
        'NOOP_True',
        'SendEmail',
        'usr',
        NULL,
$$
[%- USE date -%]
[%- SET user = target.0.usr -%]
To: [%- params.recipient_email || user.email %]
From: [%- params.sender_email || default_sender %]
Subject: Circulation History

    [% FOR circ IN target %]
            [% helpers.get_copy_bib_basics(circ.target_copy.id).title %]
            Barcode: [% circ.target_copy.barcode %]
            Checked Out: [% date.format(helpers.format_date(circ.xact_start), '%Y-%m-%d') %]
            Due Date: [% date.format(helpers.format_date(circ.due_date), '%Y-%m-%d') %]
            Returned: [% date.format(helpers.format_date(circ.checkin_time), '%Y-%m-%d') %]
    [% END %]
$$
    )
    ,(
        26,
        TRUE,
        1,
        'circ.history.print',
        'circ.format.history.print',
        'NOOP_True',
        'ProcessTemplate',
        'usr',
        'print-on-demand',
$$
[%- USE date -%]
<div>
    <style> li { padding: 8px; margin 5px; }</style>
    <div>[% date.format %]</div>
    <br/>

    [% user.family_name %], [% user.first_given_name %]
    <ol>
    [% FOR circ IN target %]
        <li>
            <div>[% helpers.get_copy_bib_basics(circ.target_copy.id).title %]</div>
            <div>Barcode: [% circ.target_copy.barcode %]</div>
            <div>Checked Out: [% date.format(helpers.format_date(circ.xact_start), '%Y-%m-%d') %]</div>
            <div>Due Date: [% date.format(helpers.format_date(circ.due_date), '%Y-%m-%d') %]</div>
            <div>Returned: [% date.format(helpers.format_date(circ.checkin_time), '%Y-%m-%d') %]</div>
        </li>
    [% END %]
    </ol>
</div>
$$
    )
    ,(
        27,
        TRUE,
        1,
        'ahr.history.email',
        'ahr.format.history.email',
        'NOOP_True',
        'SendEmail',
        'usr',
        NULL,
$$
[%- USE date -%]
[%- SET user = target.0.usr -%]
To: [%- params.recipient_email || user.email %]
From: [%- params.sender_email || default_sender %]
Subject: Hold Request History

    [% FOR hold IN target %]
            [% helpers.get_copy_bib_basics(hold.current_copy.id).title %]
            Requested: [% date.format(helpers.format_date(hold.request_time), '%Y-%m-%d') %]
            [% IF hold.fulfillment_time %]Fulfilled: [% date.format(helpers.format_date(hold.fulfillment_time), '%Y-%m-%d') %][% END %]
    [% END %]
$$
    )
    ,(
        28,
        TRUE,
        1,
        'ahr.history.print',
        'ahr.format.history.print',
        'NOOP_True',
        'ProcessTemplate',
        'usr',
        'print-on-demand',
$$
[%- USE date -%]
<div>
    <style> li { padding: 8px; margin 5px; }</style>
    <div>[% date.format %]</div>
    <br/>

    [% user.family_name %], [% user.first_given_name %]
    <ol>
    [% FOR hold IN target %]
        <li>
            <div>[% helpers.get_copy_bib_basics(hold.current_copy.id).title %]</div>
            <div>Requested: [% date.format(helpers.format_date(hold.request_time), '%Y-%m-%d') %]</div>
            [% IF hold.fulfillment_time %]<div>Fulfilled: [% date.format(helpers.format_date(hold.fulfillment_time), '%Y-%m-%d') %]</div>[% END %]
        </li>
    [% END %]
    </ol>
</div>
$$
    )

;

INSERT INTO action_trigger.environment (
        event_def,
        path
    ) VALUES 
         ( 25, 'target_copy')
        ,( 25, 'usr' )
        ,( 26, 'target_copy' )
        ,( 26, 'usr' )
        ,( 27, 'current_copy' )
        ,( 27, 'usr' )
        ,( 28, 'current_copy' )
        ,( 28, 'usr' )
;

INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES (
        'money.format.payment_receipt.email',
        'mp', 
        oils_i18n_gettext(
            'money.format.payment_receipt.email',
            'An email has been requested for a payment receipt.',
            'ath',
            'description'
        ), 
        FALSE
    )
    ,(
        'money.format.payment_receipt.print',
        'mp', 
        oils_i18n_gettext(
            'money.format.payment_receipt.print',
            'A payment receipt needs to be formatted for printing.',
            'ath',
            'description'
        ), 
        FALSE
    )
;

INSERT INTO action_trigger.event_definition (
        id,
        active,
        owner,
        name,
        hook,
        validator,
        reactor,
        group_field,
        granularity,
        template
    ) VALUES (
        29,
        TRUE,
        1,
        'money.payment_receipt.email',
        'money.format.payment_receipt.email',
        'NOOP_True',
        'SendEmail',
        'xact.usr',
        NULL,
$$
[%- USE date -%]
[%- SET user = target.0.xact.usr -%]
To: [%- params.recipient_email || user.email %]
From: [%- params.sender_email || default_sender %]
Subject: Payment Receipt

[% date.format -%]
[%- SET xact_mp_hash = {} -%]
[%- FOR mp IN target %][%# Template is hooked around payments, but let us make the receipt focused on transactions -%]
    [%- SET xact_id = mp.xact.id -%]
    [%- IF ! xact_mp_hash.defined( xact_id ) -%][%- xact_mp_hash.$xact_id = { 'xact' => mp.xact, 'payments' => [] } -%][%- END -%]
    [%- xact_mp_hash.$xact_id.payments.push(mp) -%]
[%- END -%]
[%- FOR xact_id IN xact_mp_hash.keys.sort -%]
    [%- SET xact = xact_mp_hash.$xact_id.xact %]
Transaction ID: [% xact_id %]
    [% IF xact.circulation %][% helpers.get_copy_bib_basics(xact.circulation.target_copy).title %]
    [% ELSE %]Miscellaneous
    [% END %]
    Line item billings:
        [%- SET mb_type_hash = {} -%]
        [%- FOR mb IN xact.billings %][%# Group billings by their btype -%]
            [%- IF mb.voided == 'f' -%]
                [%- SET mb_type = mb.btype.id -%]
                [%- IF ! mb_type_hash.defined( mb_type ) -%][%- mb_type_hash.$mb_type = { 'sum' => 0.00, 'billings' => [] } -%][%- END -%]
                [%- IF ! mb_type_hash.$mb_type.defined( 'first_ts' ) -%][%- mb_type_hash.$mb_type.first_ts = mb.billing_ts -%][%- END -%]
                [%- mb_type_hash.$mb_type.last_ts = mb.billing_ts -%]
                [%- mb_type_hash.$mb_type.sum = mb_type_hash.$mb_type.sum + mb.amount -%]
                [%- mb_type_hash.$mb_type.billings.push( mb ) -%]
            [%- END -%]
        [%- END -%]
        [%- FOR mb_type IN mb_type_hash.keys.sort -%]
            [%- IF mb_type == 1 %][%-# Consolidated view of overdue billings -%]
                $[% mb_type_hash.$mb_type.sum %] for [% mb_type_hash.$mb_type.billings.0.btype.name %] 
                    on [% mb_type_hash.$mb_type.first_ts %] through [% mb_type_hash.$mb_type.last_ts %]
            [%- ELSE -%][%# all other billings show individually %]
                [% FOR mb IN mb_type_hash.$mb_type.billings %]
                    $[% mb.amount %] for [% mb.btype.name %] on [% mb.billing_ts %] [% mb.note %]
                [% END %]
            [% END %]
        [% END %]
    Line item payments:
        [% FOR mp IN xact_mp_hash.$xact_id.payments %]
            Payment ID: [% mp.id %]
                Paid [% mp.amount %] via [% SWITCH mp.payment_type -%]
                    [% CASE "cash_payment" %]cash
                    [% CASE "check_payment" %]check
                    [% CASE "credit_card_payment" %]credit card (
                        [%- SET cc_chunks = mp.credit_card_payment.cc_number.replace(' ','').chunk(4); -%]
                        [%- cc_chunks.slice(0, -1+cc_chunks.max).join.replace('\S','X') -%] 
                        [% cc_chunks.last -%]
                        exp [% mp.credit_card_payment.expire_month %]/[% mp.credit_card_payment.expire_year -%]
                    )
                    [% CASE "credit_payment" %]credit
                    [% CASE "forgive_payment" %]forgiveness
                    [% CASE "goods_payment" %]goods
                    [% CASE "work_payment" %]work
                [%- END %] on [% mp.payment_ts %] [% mp.note %]
        [% END %]
[% END %]
$$
    )
    ,(
        30,
        TRUE,
        1,
        'money.payment_receipt.print',
        'money.format.payment_receipt.print',
        'NOOP_True',
        'ProcessTemplate',
        'xact.usr',
        'print-on-demand',
$$
[%- USE date -%][%- SET user = target.0.xact.usr -%]
<div style="li { padding: 8px; margin 5px; }">
    <div>[% date.format %]</div><br/>
    <ol>
    [% SET xact_mp_hash = {} %]
    [% FOR mp IN target %][%# Template is hooked around payments, but let us make the receipt focused on transactions %]
        [% SET xact_id = mp.xact.id %]
        [% IF ! xact_mp_hash.defined( xact_id ) %][% xact_mp_hash.$xact_id = { 'xact' => mp.xact, 'payments' => [] } %][% END %]
        [% xact_mp_hash.$xact_id.payments.push(mp) %]
    [% END %]
    [% FOR xact_id IN xact_mp_hash.keys.sort %]
        [% SET xact = xact_mp_hash.$xact_id.xact %]
        <li>Transaction ID: [% xact_id %]
            [% IF xact.circulation %][% helpers.get_copy_bib_basics(xact.circulation.target_copy).title %]
            [% ELSE %]Miscellaneous
            [% END %]
            Line item billings:<ol>
                [% SET mb_type_hash = {} %]
                [% FOR mb IN xact.billings %][%# Group billings by their btype %]
                    [% IF mb.voided == 'f' %]
                        [% SET mb_type = mb.btype.id %]
                        [% IF ! mb_type_hash.defined( mb_type ) %][% mb_type_hash.$mb_type = { 'sum' => 0.00, 'billings' => [] } %][% END %]
                        [% IF ! mb_type_hash.$mb_type.defined( 'first_ts' ) %][% mb_type_hash.$mb_type.first_ts = mb.billing_ts %][% END %]
                        [% mb_type_hash.$mb_type.last_ts = mb.billing_ts %]
                        [% mb_type_hash.$mb_type.sum = mb_type_hash.$mb_type.sum + mb.amount %]
                        [% mb_type_hash.$mb_type.billings.push( mb ) %]
                    [% END %]
                [% END %]
                [% FOR mb_type IN mb_type_hash.keys.sort %]
                    <li>[% IF mb_type == 1 %][%# Consolidated view of overdue billings %]
                        $[% mb_type_hash.$mb_type.sum %] for [% mb_type_hash.$mb_type.billings.0.btype.name %] 
                            on [% mb_type_hash.$mb_type.first_ts %] through [% mb_type_hash.$mb_type.last_ts %]
                    [% ELSE %][%# all other billings show individually %]
                        [% FOR mb IN mb_type_hash.$mb_type.billings %]
                            $[% mb.amount %] for [% mb.btype.name %] on [% mb.billing_ts %] [% mb.note %]
                        [% END %]
                    [% END %]</li>
                [% END %]
            </ol>
            Line item payments:<ol>
                [% FOR mp IN xact_mp_hash.$xact_id.payments %]
                    <li>Payment ID: [% mp.id %]
                        Paid [% mp.amount %] via [% SWITCH mp.payment_type -%]
                            [% CASE "cash_payment" %]cash
                            [% CASE "check_payment" %]check
                            [% CASE "credit_card_payment" %]credit card (
                                [%- SET cc_chunks = mp.credit_card_payment.cc_number.replace(' ','').chunk(4); -%]
                                [%- cc_chunks.slice(0, -1+cc_chunks.max).join.replace('\S','X') -%] 
                                [% cc_chunks.last -%]
                                exp [% mp.credit_card_payment.expire_month %]/[% mp.credit_card_payment.expire_year -%]
                            )
                            [% CASE "credit_payment" %]credit
                            [% CASE "forgive_payment" %]forgiveness
                            [% CASE "goods_payment" %]goods
                            [% CASE "work_payment" %]work
                        [%- END %] on [% mp.payment_ts %] [% mp.note %]
                    </li>
                [% END %]
            </ol>
        </li>
    [% END %]
    </ol>
</div>
$$
    )
;

INSERT INTO action_trigger.environment (
        event_def,
        path
    ) VALUES -- for fleshing mp objects
         ( 29, 'xact')
        ,( 29, 'xact.usr')
        ,( 29, 'xact.grocery' )
        ,( 29, 'xact.circulation' )
        ,( 29, 'xact.summary' )
        ,( 30, 'xact')
        ,( 30, 'xact.usr')
        ,( 30, 'xact.grocery' )
        ,( 30, 'xact.circulation' )
        ,( 30, 'xact.summary' )
;

INSERT INTO action_trigger.cleanup ( module, description ) VALUES (
    'DeleteTempBiblioBucket',
    oils_i18n_gettext(
        'DeleteTempBiblioBucket',
        'Deletes a cbreb object used as a target if it has a btype of "temp"',
        'atclean',
        'description'
    )
);

INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES (
        'biblio.format.record_entry.email',
        'cbreb', 
        oils_i18n_gettext(
            'biblio.format.record_entry.email',
            'An email has been requested for one or more biblio record entries.',
            'ath',
            'description'
        ), 
        FALSE
    )
    ,(
        'biblio.format.record_entry.print',
        'cbreb', 
        oils_i18n_gettext(
            'biblio.format.record_entry.print',
            'One or more biblio record entries need to be formatted for printing.',
            'ath',
            'description'
        ), 
        FALSE
    )
;

INSERT INTO action_trigger.event_definition (
        id,
        active,
        owner,
        name,
        hook,
        validator,
        reactor,
        cleanup_success,
        cleanup_failure,
        group_field,
        granularity,
        template
    ) VALUES (
        31,
        TRUE,
        1,
        'biblio.record_entry.email',
        'biblio.format.record_entry.email',
        'NOOP_True',
        'SendEmail',
        'DeleteTempBiblioBucket',
        'DeleteTempBiblioBucket',
        'owner',
        NULL,
$$
[%- USE date -%]
[%- SET user = target.0.owner -%]
To: [%- params.recipient_email || user.email %]
From: [%- params.sender_email || default_sender %]
Subject: Bibliographic Records

    [% FOR cbreb IN target %]
    [% FOR cbrebi IN cbreb.items %]
        Bib ID# [% cbrebi.target_biblio_record_entry.id %] ISBN: [% crebi.target_biblio_record_entry.simple_record.isbn %]
        Title: [% cbrebi.target_biblio_record_entry.simple_record.title %]
        Author: [% cbrebi.target_biblio_record_entry.simple_record.author %]
        Publication Year: [% cbrebi.target_biblio_record_entry.simple_record.pubdate %]

    [% END %]
    [% END %]
$$
    )
    ,(
        32,
        TRUE,
        1,
        'biblio.record_entry.print',
        'biblio.format.record_entry.print',
        'NOOP_True',
        'ProcessTemplate',
        'DeleteTempBiblioBucket',
        'DeleteTempBiblioBucket',
        'owner',
        'print-on-demand',
$$
[%- USE date -%]
<div>
    <style> li { padding: 8px; margin 5px; }</style>
    <ol>
    [% FOR cbreb IN target %]
    [% FOR cbrebi IN cbreb.items %]
        <li>Bib ID# [% cbrebi.target_biblio_record_entry.id %] ISBN: [% crebi.target_biblio_record_entry.simple_record.isbn %]<br />
            Title: [% cbrebi.target_biblio_record_entry.simple_record.title %]<br />
            Author: [% cbrebi.target_biblio_record_entry.simple_record.author %]<br />
            Publication Year: [% cbrebi.target_biblio_record_entry.simple_record.pubdate %]
        </li>
    [% END %]
    [% END %]
    </ol>
</div>
$$
    )
;

INSERT INTO action_trigger.environment (
        event_def,
        path
    ) VALUES -- for fleshing cbreb objects
         ( 31, 'owner' )
        ,( 31, 'items' )
        ,( 31, 'items.target_biblio_record_entry' )
        ,( 31, 'items.target_biblio_record_entry.simple_record' )
        ,( 31, 'items.target_biblio_record_entry.call_numbers' )
        ,( 31, 'items.target_biblio_record_entry.fixed_fields' )
        ,( 31, 'items.target_biblio_record_entry.notes' )
        ,( 31, 'items.target_biblio_record_entry.full_record_entries' )
        ,( 32, 'owner' )
        ,( 32, 'items' )
        ,( 32, 'items.target_biblio_record_entry' )
        ,( 32, 'items.target_biblio_record_entry.simple_record' )
        ,( 32, 'items.target_biblio_record_entry.call_numbers' )
        ,( 32, 'items.target_biblio_record_entry.fixed_fields' )
        ,( 32, 'items.target_biblio_record_entry.notes' )
        ,( 32, 'items.target_biblio_record_entry.full_record_entries' )
;

INSERT INTO action_trigger.environment (
        event_def,
        path
    ) VALUES -- for fleshing mp objects
         ( 29, 'credit_card_payment')
        ,( 29, 'xact.billings')
        ,( 29, 'xact.billings.btype')
        ,( 30, 'credit_card_payment')
        ,( 30, 'xact.billings')
        ,( 30, 'xact.billings.btype')
;

INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES 
    (   'circ.format.missing_pieces.slip.print',
        'circ', 
        oils_i18n_gettext(
            'circ.format.missing_pieces.slip.print',
            'A missing pieces slip needs to be formatted for printing.',
            'ath',
            'description'
        ), 
        FALSE
    )
    ,(  'circ.format.missing_pieces.letter.print',
        'circ', 
        oils_i18n_gettext(
            'circ.format.missing_pieces.letter.print',
            'A missing pieces patron letter needs to be formatted for printing.',
            'ath',
            'description'
        ), 
        FALSE
    )
;

INSERT INTO action_trigger.event_definition (
        id,
        active,
        owner,
        name,
        hook,
        validator,
        reactor,
        group_field,
        granularity,
        template
    ) VALUES (
        33,
        TRUE,
        1,
        'circ.missing_pieces.slip.print',
        'circ.format.missing_pieces.slip.print',
        'NOOP_True',
        'ProcessTemplate',
        'usr',
        'print-on-demand',
$$
[%- USE date -%]
[%- SET user = target.0.usr -%]
<div style="li { padding: 8px; margin 5px; }">
    <div>[% date.format %]</div><br/>
    Missing pieces for:
    <ol>
    [% FOR circ IN target %]
        <li>Barcode: [% circ.target_copy.barcode %] Transaction ID: [% circ.id %] Due: [% circ.due_date.format %]<br />
            [% helpers.get_copy_bib_basics(circ.target_copy.id).title %]
        </li>
    [% END %]
    </ol>
</div>
$$
    )
    ,(
        34,
        TRUE,
        1,
        'circ.missing_pieces.letter.print',
        'circ.format.missing_pieces.letter.print',
        'NOOP_True',
        'ProcessTemplate',
        'usr',
        'print-on-demand',
$$
[%- USE date -%]
[%- SET user = target.0.usr -%]
[% date.format %]
Dear [% user.prefix %] [% user.first_given_name %] [% user.family_name %],

We are missing pieces for the following returned items:
[% FOR circ IN target %]
Barcode: [% circ.target_copy.barcode %] Transaction ID: [% circ.id %] Due: [% circ.due_date.format %]
[% helpers.get_copy_bib_basics(circ.target_copy.id).title %]
[% END %]

Please return these pieces as soon as possible.

Thanks!

Library Staff
$$
    )
;

INSERT INTO action_trigger.environment (
        event_def,
        path
    ) VALUES -- for fleshing circ objects
         ( 33, 'usr')
        ,( 33, 'target_copy')
        ,( 33, 'target_copy.circ_lib')
        ,( 33, 'target_copy.circ_lib.mailing_address')
        ,( 33, 'target_copy.circ_lib.billing_address')
        ,( 33, 'target_copy.call_number')
        ,( 33, 'target_copy.call_number.owning_lib')
        ,( 33, 'target_copy.call_number.owning_lib.mailing_address')
        ,( 33, 'target_copy.call_number.owning_lib.billing_address')
        ,( 33, 'circ_lib')
        ,( 33, 'circ_lib.mailing_address')
        ,( 33, 'circ_lib.billing_address')
        ,( 34, 'usr')
        ,( 34, 'target_copy')
        ,( 34, 'target_copy.circ_lib')
        ,( 34, 'target_copy.circ_lib.mailing_address')
        ,( 34, 'target_copy.circ_lib.billing_address')
        ,( 34, 'target_copy.call_number')
        ,( 34, 'target_copy.call_number.owning_lib')
        ,( 34, 'target_copy.call_number.owning_lib.mailing_address')
        ,( 34, 'target_copy.call_number.owning_lib.billing_address')
        ,( 34, 'circ_lib')
        ,( 34, 'circ_lib.mailing_address')
        ,( 34, 'circ_lib.billing_address')
;

INSERT INTO action_trigger.hook (key,core_type,description,passive) 
    VALUES (   
        'ahr.format.pull_list',
        'ahr', 
        oils_i18n_gettext(
            'ahr.format.pull_list',
            'Format holds pull list for printing',
            'ath',
            'description'
        ), 
        FALSE
    );

INSERT INTO action_trigger.event_definition (
        id,
        active,
        owner,
        name,
        hook,
        validator,
        reactor,
        group_field,
        granularity,
        template
    ) VALUES (
        35,
        TRUE,
        1,
        'Holds Pull List',
        'ahr.format.pull_list',
        'NOOP_True',
        'ProcessTemplate',
        'pickup_lib',
        'print-on-demand',
$$
[%- USE date -%]
<style>
    table { border-collapse: collapse; } 
    td { padding: 5px; border-bottom: 1px solid #888; } 
    th { font-weight: bold; }
</style>
[% 
    # Sort the holds into copy-location buckets
    # In the main print loop, sort each bucket by callnumber before printing
    SET holds_list = [];
    SET loc_data = [];
    SET current_location = target.0.current_copy.location.id;
    FOR hold IN target;
        IF current_location != hold.current_copy.location.id;
            SET current_location = hold.current_copy.location.id;
            holds_list.push(loc_data);
            SET loc_data = [];
        END;
        SET hold_data = {
            'hold' => hold,
            'callnumber' => hold.current_copy.call_number.label
        };
        loc_data.push(hold_data);
    END;
    holds_list.push(loc_data)
%]
<table>
    <thead>
        <tr>
            <th>Title</th>
            <th>Author</th>
            <th>Shelving Location</th>
            <th>Call Number</th>
            <th>Barcode</th>
            <th>Patron</th>
        </tr>
    </thead>
    <tbody>
    [% FOR loc_data IN holds_list  %]
        [% FOR hold_data IN loc_data.sort('callnumber') %]
            [% 
                SET hold = hold_data.hold;
                SET copy_data = helpers.get_copy_bib_basics(hold.current_copy.id);
            %]
            <tr>
                <td>[% copy_data.title | truncate %]</td>
                <td>[% copy_data.author | truncate %]</td>
                <td>[% hold.current_copy.location.name %]</td>
                <td>[% hold.current_copy.call_number.label %]</td>
                <td>[% hold.current_copy.barcode %]</td>
                <td>[% hold.usr.card.barcode %]</td>
            </tr>
        [% END %]
    [% END %]
    <tbody>
</table>
$$
);

INSERT INTO action_trigger.environment (
        event_def,
        path
    ) VALUES
        (35, 'current_copy.location'),
        (35, 'current_copy.call_number'),
        (35, 'usr.card'),
        (35, 'pickup_lib')
;

INSERT INTO action_trigger.validator (module, description) VALUES ( 
    'HoldIsCancelled', 
    oils_i18n_gettext( 
        'HoldIsCancelled', 
        'Check whether a hold request is cancelled.', 
        'atval', 
        'description' 
    ) 
);

-- Create the query schema, and the tables and views therein

DROP SCHEMA IF EXISTS sql CASCADE;
DROP SCHEMA IF EXISTS query CASCADE;

CREATE SCHEMA query;

CREATE TABLE query.datatype (
	id              SERIAL            PRIMARY KEY,
	datatype_name   TEXT              NOT NULL UNIQUE,
	is_numeric      BOOL              NOT NULL DEFAULT FALSE,
	is_composite    BOOL              NOT NULL DEFAULT FALSE,
	CONSTRAINT qdt_comp_not_num CHECK
	( is_numeric IS FALSE OR is_composite IS FALSE )
);

-- Define the most common datatypes in query.datatype.  Note that none of
-- these stock datatypes specifies a width or precision.

-- Also: set the sequence for query.datatype to 1000, leaving plenty of
-- room for more stock datatypes if we ever want to add them.

SELECT setval( 'query.datatype_id_seq', 1000 );

INSERT INTO query.datatype (id, datatype_name, is_numeric )
  VALUES (1, 'SMALLINT', true);
 
INSERT INTO query.datatype (id, datatype_name, is_numeric )
  VALUES (2, 'INTEGER', true);
 
INSERT INTO query.datatype (id, datatype_name, is_numeric )
  VALUES (3, 'BIGINT', true);
 
INSERT INTO query.datatype (id, datatype_name, is_numeric )
  VALUES (4, 'DECIMAL', true);
 
INSERT INTO query.datatype (id, datatype_name, is_numeric )
  VALUES (5, 'NUMERIC', true);
 
INSERT INTO query.datatype (id, datatype_name, is_numeric )
  VALUES (6, 'REAL', true);
 
INSERT INTO query.datatype (id, datatype_name, is_numeric )
  VALUES (7, 'DOUBLE PRECISION', true);
 
INSERT INTO query.datatype (id, datatype_name, is_numeric )
  VALUES (8, 'SERIAL', true);
 
INSERT INTO query.datatype (id, datatype_name, is_numeric )
  VALUES (9, 'BIGSERIAL', true);
 
INSERT INTO query.datatype (id, datatype_name, is_numeric )
  VALUES (10, 'MONEY', false);
 
INSERT INTO query.datatype (id, datatype_name, is_numeric )
  VALUES (11, 'VARCHAR', false);
 
INSERT INTO query.datatype (id, datatype_name, is_numeric )
  VALUES (12, 'CHAR', false);
 
INSERT INTO query.datatype (id, datatype_name, is_numeric )
  VALUES (13, 'TEXT', false);
 
INSERT INTO query.datatype (id, datatype_name, is_numeric )
  VALUES (14, '"char"', false);
 
INSERT INTO query.datatype (id, datatype_name, is_numeric )
  VALUES (15, 'NAME', false);
 
INSERT INTO query.datatype (id, datatype_name, is_numeric )
  VALUES (16, 'BYTEA', false);
 
INSERT INTO query.datatype (id, datatype_name, is_numeric )
  VALUES (17, 'TIMESTAMP WITHOUT TIME ZONE', false);
 
INSERT INTO query.datatype (id, datatype_name, is_numeric )
  VALUES (18, 'TIMESTAMP WITH TIME ZONE', false);
 
INSERT INTO query.datatype (id, datatype_name, is_numeric )
  VALUES (19, 'DATE', false);
 
INSERT INTO query.datatype (id, datatype_name, is_numeric )
  VALUES (20, 'TIME WITHOUT TIME ZONE', false);
 
INSERT INTO query.datatype (id, datatype_name, is_numeric )
  VALUES (21, 'TIME WITH TIME ZONE', false);
 
INSERT INTO query.datatype (id, datatype_name, is_numeric )
  VALUES (22, 'INTERVAL', false);
 
INSERT INTO query.datatype (id, datatype_name, is_numeric )
  VALUES (23, 'BOOLEAN', false);
 
CREATE TABLE query.subfield (
	id              SERIAL            PRIMARY KEY,
	composite_type  INT               NOT NULL
	                                  REFERENCES query.datatype(id)
	                                  ON DELETE CASCADE
	                                  DEFERRABLE INITIALLY DEFERRED,
	seq_no          INT               NOT NULL
	                                  CONSTRAINT qsf_pos_seq_no
	                                  CHECK( seq_no > 0 ),
	subfield_type   INT               NOT NULL
	                                  REFERENCES query.datatype(id)
	                                  DEFERRABLE INITIALLY DEFERRED,
	CONSTRAINT qsf_datatype_seq_no UNIQUE (composite_type, seq_no)
);

CREATE TABLE query.function_sig (
	id              SERIAL            PRIMARY KEY,
	function_name   TEXT              NOT NULL,
	return_type     INT               REFERENCES query.datatype(id)
	                                  DEFERRABLE INITIALLY DEFERRED,
	is_aggregate    BOOL              NOT NULL DEFAULT FALSE,
	CONSTRAINT qfd_rtn_or_aggr CHECK
	( return_type IS NULL OR is_aggregate = FALSE )
);

CREATE INDEX query_function_sig_name_idx 
	ON query.function_sig (function_name);

CREATE TABLE query.function_param_def (
	id              SERIAL            PRIMARY KEY,
	function_id     INT               NOT NULL
	                                  REFERENCES query.function_sig( id )
	                                  ON DELETE CASCADE
	                                  DEFERRABLE INITIALLY DEFERRED,
	seq_no          INT               NOT NULL
	                                  CONSTRAINT qfpd_pos_seq_no CHECK
	                                  ( seq_no > 0 ),
	datatype        INT               NOT NULL
	                                  REFERENCES query.datatype( id )
	                                  DEFERRABLE INITIALLY DEFERRED,
	CONSTRAINT qfpd_function_param_seq UNIQUE (function_id, seq_no)
);

CREATE TABLE  query.stored_query (
	id            SERIAL         PRIMARY KEY,
	type          TEXT           NOT NULL CONSTRAINT query_type CHECK
	                             ( type IN ( 'SELECT', 'UNION', 'INTERSECT', 'EXCEPT' ) ),
	use_all       BOOLEAN        NOT NULL DEFAULT FALSE,
	use_distinct  BOOLEAN        NOT NULL DEFAULT FALSE,
	from_clause   INT            , --REFERENCES query.from_clause
	                             --DEFERRABLE INITIALLY DEFERRED,
	where_clause  INT            , --REFERENCES query.expression
	                             --DEFERRABLE INITIALLY DEFERRED,
	having_clause INT            , --REFERENCES query.expression
	                             --DEFERRABLE INITIALLY DEFERRED
	limit_count   INT            , --REFERENCES query.expression( id )
	                             --DEFERRABLE INITIALLY DEFERRED,
	offset_count  INT            --REFERENCES query.expression( id )
	                             --DEFERRABLE INITIALLY DEFERRED
);

-- (Foreign keys to be defined later after other tables are created)

CREATE TABLE query.query_sequence (
	id              SERIAL            PRIMARY KEY,
	parent_query    INT               NOT NULL
	                                  REFERENCES query.stored_query
									  ON DELETE CASCADE
									  DEFERRABLE INITIALLY DEFERRED,
	seq_no          INT               NOT NULL,
	child_query     INT               NOT NULL
	                                  REFERENCES query.stored_query
									  ON DELETE CASCADE
									  DEFERRABLE INITIALLY DEFERRED,
	CONSTRAINT query_query_seq UNIQUE( parent_query, seq_no )
);

CREATE TABLE query.bind_variable (
	name          TEXT             PRIMARY KEY,
	type          TEXT             NOT NULL
		                           CONSTRAINT bind_variable_type CHECK
		                           ( type in ( 'string', 'number', 'string_list', 'number_list' )),
	description   TEXT             NOT NULL,
	default_value TEXT,            -- to be encoded in JSON
	label         TEXT             NOT NULL
);

CREATE TABLE query.expression (
	id            SERIAL        PRIMARY KEY,
	type          TEXT          NOT NULL CONSTRAINT expression_type CHECK
	                            ( type IN (
                                    'xbet',    -- between
                                    'xbind',   -- bind variable
                                    'xbool',   -- boolean
                                    'xcase',   -- case
                                    'xcast',   -- cast
                                    'xcol',    -- column
                                    'xex',     -- exists
                                    'xfunc',   -- function
                                    'xin',     -- in
                                    'xisnull', -- is null
                                    'xnull',   -- null
                                    'xnum',    -- number
                                    'xop',     -- operator
                                    'xser',    -- series
                                    'xstr',    -- string
                                    'xsubq'    -- subquery
								) ),
	parenthesize  BOOL          NOT NULL DEFAULT FALSE,
	parent_expr   INT           REFERENCES query.expression
	                            ON DELETE CASCADE
	                            DEFERRABLE INITIALLY DEFERRED,
	seq_no        INT           NOT NULL DEFAULT 1,
	literal       TEXT,
	table_alias   TEXT,
	column_name   TEXT,
	left_operand  INT           REFERENCES query.expression
	                            DEFERRABLE INITIALLY DEFERRED,
	operator      TEXT,
	right_operand INT           REFERENCES query.expression
	                            DEFERRABLE INITIALLY DEFERRED,
	function_id   INT           REFERENCES query.function_sig
	                            DEFERRABLE INITIALLY DEFERRED,
	subquery      INT           REFERENCES query.stored_query
	                            DEFERRABLE INITIALLY DEFERRED,
	cast_type     INT           REFERENCES query.datatype
	                            DEFERRABLE INITIALLY DEFERRED,
	negate        BOOL          NOT NULL DEFAULT FALSE,
	bind_variable TEXT          REFERENCES query.bind_variable
		                        DEFERRABLE INITIALLY DEFERRED
);

CREATE UNIQUE INDEX query_expr_parent_seq
	ON query.expression( parent_expr, seq_no )
	WHERE parent_expr IS NOT NULL;

-- Due to some circular references, the following foreign key definitions
-- had to be deferred until query.expression existed:

ALTER TABLE query.stored_query
	ADD FOREIGN KEY ( where_clause )
	REFERENCES query.expression( id )
	DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE query.stored_query
	ADD FOREIGN KEY ( having_clause )
	REFERENCES query.expression( id )
	DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE query.stored_query
    ADD FOREIGN KEY ( limit_count )
    REFERENCES query.expression( id )
    DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE query.stored_query
    ADD FOREIGN KEY ( offset_count )
    REFERENCES query.expression( id )
    DEFERRABLE INITIALLY DEFERRED;

CREATE TABLE query.case_branch (
	id            SERIAL        PRIMARY KEY,
	parent_expr   INT           NOT NULL REFERENCES query.expression
	                            ON DELETE CASCADE
	                            DEFERRABLE INITIALLY DEFERRED,
	seq_no        INT           NOT NULL,
	condition     INT           REFERENCES query.expression
	                            DEFERRABLE INITIALLY DEFERRED,
	result        INT           NOT NULL REFERENCES query.expression
	                            DEFERRABLE INITIALLY DEFERRED,
	CONSTRAINT case_branch_parent_seq UNIQUE (parent_expr, seq_no)
);

CREATE TABLE query.from_relation (
	id               SERIAL        PRIMARY KEY,
	type             TEXT          NOT NULL CONSTRAINT relation_type CHECK (
	                                   type IN ( 'RELATION', 'SUBQUERY', 'FUNCTION' ) ),
	table_name       TEXT,
	class_name       TEXT,
	subquery         INT           REFERENCES query.stored_query,
	function_call    INT           REFERENCES query.expression,
	table_alias      TEXT,
	parent_relation  INT           REFERENCES query.from_relation
	                               ON DELETE CASCADE
	                               DEFERRABLE INITIALLY DEFERRED,
	seq_no           INT           NOT NULL DEFAULT 1,
	join_type        TEXT          CONSTRAINT good_join_type CHECK (
	                                   join_type IS NULL OR join_type IN
	                                   ( 'INNER', 'LEFT', 'RIGHT', 'FULL' )
	                               ),
	on_clause        INT           REFERENCES query.expression
	                               DEFERRABLE INITIALLY DEFERRED,
	CONSTRAINT join_or_core CHECK (
        ( parent_relation IS NULL AND join_type IS NULL
          AND on_clause IS NULL )
        OR
        ( parent_relation IS NOT NULL AND join_type IS NOT NULL
          AND on_clause IS NOT NULL )
	)
);

CREATE UNIQUE INDEX from_parent_seq
	ON query.from_relation( parent_relation, seq_no )
	WHERE parent_relation IS NOT NULL;

-- The following foreign key had to be deferred until
-- query.from_relation existed

ALTER TABLE query.stored_query
	ADD FOREIGN KEY (from_clause)
	REFERENCES query.from_relation
	DEFERRABLE INITIALLY DEFERRED;

CREATE TABLE query.record_column (
	id            SERIAL            PRIMARY KEY,
	from_relation INT               NOT NULL REFERENCES query.from_relation
	                                ON DELETE CASCADE
	                                DEFERRABLE INITIALLY DEFERRED,
	seq_no        INT               NOT NULL,
	column_name   TEXT              NOT NULL,
	column_type   INT               NOT NULL REFERENCES query.datatype
	                                ON DELETE CASCADE
									DEFERRABLE INITIALLY DEFERRED,
	CONSTRAINT column_sequence UNIQUE (from_relation, seq_no)
);

CREATE TABLE query.select_item (
	id               SERIAL         PRIMARY KEY,
	stored_query     INT            NOT NULL REFERENCES query.stored_query
	                                ON DELETE CASCADE
	                                DEFERRABLE INITIALLY DEFERRED,
	seq_no           INT            NOT NULL,
	expression       INT            NOT NULL REFERENCES query.expression
	                                DEFERRABLE INITIALLY DEFERRED,
	column_alias     TEXT,
	grouped_by       BOOL           NOT NULL DEFAULT FALSE,
	CONSTRAINT select_sequence UNIQUE( stored_query, seq_no )
);

CREATE TABLE query.order_by_item (
	id               SERIAL         PRIMARY KEY,
	stored_query     INT            NOT NULL REFERENCES query.stored_query
	                                ON DELETE CASCADE
	                                DEFERRABLE INITIALLY DEFERRED,
	seq_no           INT            NOT NULL,
	expression       INT            NOT NULL REFERENCES query.expression
	                                ON DELETE CASCADE
	                                DEFERRABLE INITIALLY DEFERRED,
	CONSTRAINT order_by_sequence UNIQUE( stored_query, seq_no )
);

------------------------------------------------------------
-- Create updatable views for different kinds of expressions
------------------------------------------------------------

-- Create updatable view for BETWEEN expressions

CREATE OR REPLACE VIEW query.expr_xbet AS
    SELECT
		id,
		parenthesize,
		parent_expr,
		seq_no,
		left_operand,
		negate
    FROM
        query.expression
    WHERE
        type = 'xbet';

CREATE OR REPLACE RULE query_expr_xbet_insert_rule AS
    ON INSERT TO query.expr_xbet
    DO INSTEAD
    INSERT INTO query.expression (
		id,
		type,
		parenthesize,
		parent_expr,
		seq_no,
		left_operand,
		negate
    ) VALUES (
        COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
        'xbet',
        COALESCE(NEW.parenthesize, FALSE),
        NEW.parent_expr,
        COALESCE(NEW.seq_no, 1),
		NEW.left_operand,
		COALESCE(NEW.negate, false)
    );

CREATE OR REPLACE RULE query_expr_xbet_update_rule AS
    ON UPDATE TO query.expr_xbet
    DO INSTEAD
    UPDATE query.expression SET
        id = NEW.id,
        parenthesize = NEW.parenthesize,
        parent_expr = NEW.parent_expr,
        seq_no = NEW.seq_no,
		left_operand = NEW.left_operand,
		negate = NEW.negate
    WHERE
        id = OLD.id;

CREATE OR REPLACE RULE query_expr_xbet_delete_rule AS
    ON DELETE TO query.expr_xbet
    DO INSTEAD
    DELETE FROM query.expression WHERE id = OLD.id;

-- Create updatable view for bind variable expressions

CREATE OR REPLACE VIEW query.expr_xbind AS
    SELECT
		id,
		parenthesize,
		parent_expr,
		seq_no,
		bind_variable
    FROM
        query.expression
    WHERE
        type = 'xbind';

CREATE OR REPLACE RULE query_expr_xbind_insert_rule AS
    ON INSERT TO query.expr_xbind
    DO INSTEAD
    INSERT INTO query.expression (
		id,
		type,
		parenthesize,
		parent_expr,
		seq_no,
		bind_variable
    ) VALUES (
        COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
        'xbind',
        COALESCE(NEW.parenthesize, FALSE),
        NEW.parent_expr,
        COALESCE(NEW.seq_no, 1),
		NEW.bind_variable
    );

CREATE OR REPLACE RULE query_expr_xbind_update_rule AS
    ON UPDATE TO query.expr_xbind
    DO INSTEAD
    UPDATE query.expression SET
        id = NEW.id,
        parenthesize = NEW.parenthesize,
        parent_expr = NEW.parent_expr,
        seq_no = NEW.seq_no,
		bind_variable = NEW.bind_variable
    WHERE
        id = OLD.id;

CREATE OR REPLACE RULE query_expr_xbind_delete_rule AS
    ON DELETE TO query.expr_xbind
    DO INSTEAD
    DELETE FROM query.expression WHERE id = OLD.id;

-- Create updatable view for boolean expressions

CREATE OR REPLACE VIEW query.expr_xbool AS
    SELECT
		id,
		parenthesize,
		parent_expr,
		seq_no,
		literal,
		negate
    FROM
        query.expression
    WHERE
        type = 'xbool';

CREATE OR REPLACE RULE query_expr_xbool_insert_rule AS
    ON INSERT TO query.expr_xbool
    DO INSTEAD
    INSERT INTO query.expression (
		id,
		type,
		parenthesize,
		parent_expr,
		seq_no,
		literal,
		negate
    ) VALUES (
        COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
        'xbool',
        COALESCE(NEW.parenthesize, FALSE),
        NEW.parent_expr,
        COALESCE(NEW.seq_no, 1),
        NEW.literal,
		COALESCE(NEW.negate, false)
    );

CREATE OR REPLACE RULE query_expr_xbool_update_rule AS
    ON UPDATE TO query.expr_xbool
    DO INSTEAD
    UPDATE query.expression SET
        id = NEW.id,
        parenthesize = NEW.parenthesize,
        parent_expr = NEW.parent_expr,
        seq_no = NEW.seq_no,
        literal = NEW.literal,
		negate = NEW.negate
    WHERE
        id = OLD.id;

CREATE OR REPLACE RULE query_expr_xbool_delete_rule AS
    ON DELETE TO query.expr_xbool
    DO INSTEAD
    DELETE FROM query.expression WHERE id = OLD.id;

-- Create updatable view for CASE expressions

CREATE OR REPLACE VIEW query.expr_xcase AS
    SELECT
		id,
		parenthesize,
		parent_expr,
		seq_no,
		left_operand,
		negate
    FROM
        query.expression
    WHERE
        type = 'xcase';

CREATE OR REPLACE RULE query_expr_xcase_insert_rule AS
    ON INSERT TO query.expr_xcase
    DO INSTEAD
    INSERT INTO query.expression (
		id,
		type,
		parenthesize,
		parent_expr,
		seq_no,
		left_operand,
		negate
    ) VALUES (
        COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
        'xcase',
        COALESCE(NEW.parenthesize, FALSE),
        NEW.parent_expr,
        COALESCE(NEW.seq_no, 1),
		NEW.left_operand,
		COALESCE(NEW.negate, false)
    );

CREATE OR REPLACE RULE query_expr_xcase_update_rule AS
    ON UPDATE TO query.expr_xcase
    DO INSTEAD
    UPDATE query.expression SET
        id = NEW.id,
        parenthesize = NEW.parenthesize,
        parent_expr = NEW.parent_expr,
        seq_no = NEW.seq_no,
		left_operand = NEW.left_operand,
		negate = NEW.negate
    WHERE
        id = OLD.id;

CREATE OR REPLACE RULE query_expr_xcase_delete_rule AS
    ON DELETE TO query.expr_xcase
    DO INSTEAD
    DELETE FROM query.expression WHERE id = OLD.id;

-- Create updatable view for cast expressions

CREATE OR REPLACE VIEW query.expr_xcast AS
    SELECT
		id,
		parenthesize,
		parent_expr,
		seq_no,
		left_operand,
		cast_type,
		negate
    FROM
        query.expression
    WHERE
        type = 'xcast';

CREATE OR REPLACE RULE query_expr_xcast_insert_rule AS
    ON INSERT TO query.expr_xcast
    DO INSTEAD
    INSERT INTO query.expression (
        id,
        type,
        parenthesize,
        parent_expr,
        seq_no,
        left_operand,
        cast_type,
        negate
    ) VALUES (
        COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
        'xcast',
        COALESCE(NEW.parenthesize, FALSE),
        NEW.parent_expr,
        COALESCE(NEW.seq_no, 1),
        NEW.left_operand,
        NEW.cast_type,
        COALESCE(NEW.negate, false)
    );

CREATE OR REPLACE RULE query_expr_xcast_update_rule AS
    ON UPDATE TO query.expr_xcast
    DO INSTEAD
    UPDATE query.expression SET
        id = NEW.id,
        parenthesize = NEW.parenthesize,
        parent_expr = NEW.parent_expr,
        seq_no = NEW.seq_no,
		left_operand = NEW.left_operand,
		cast_type = NEW.cast_type,
		negate = NEW.negate
    WHERE
        id = OLD.id;

CREATE OR REPLACE RULE query_expr_xcast_delete_rule AS
    ON DELETE TO query.expr_xcast
    DO INSTEAD
    DELETE FROM query.expression WHERE id = OLD.id;

-- Create updatable view for column expressions

CREATE OR REPLACE VIEW query.expr_xcol AS
    SELECT
		id,
		parenthesize,
		parent_expr,
		seq_no,
		table_alias,
		column_name,
		negate
    FROM
        query.expression
    WHERE
        type = 'xcol';

CREATE OR REPLACE RULE query_expr_xcol_insert_rule AS
    ON INSERT TO query.expr_xcol
    DO INSTEAD
    INSERT INTO query.expression (
		id,
		type,
		parenthesize,
		parent_expr,
		seq_no,
		table_alias,
		column_name,
		negate
    ) VALUES (
        COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
        'xcol',
        COALESCE(NEW.parenthesize, FALSE),
        NEW.parent_expr,
        COALESCE(NEW.seq_no, 1),
		NEW.table_alias,
		NEW.column_name,
		COALESCE(NEW.negate, false)
    );

CREATE OR REPLACE RULE query_expr_xcol_update_rule AS
    ON UPDATE TO query.expr_xcol
    DO INSTEAD
    UPDATE query.expression SET
        id = NEW.id,
        parenthesize = NEW.parenthesize,
        parent_expr = NEW.parent_expr,
        seq_no = NEW.seq_no,
		table_alias = NEW.table_alias,
		column_name = NEW.column_name,
		negate = NEW.negate
    WHERE
        id = OLD.id;

CREATE OR REPLACE RULE query_expr_xcol_delete_rule AS
    ON DELETE TO query.expr_xcol
    DO INSTEAD
    DELETE FROM query.expression WHERE id = OLD.id;

-- Create updatable view for EXISTS expressions

CREATE OR REPLACE VIEW query.expr_xex AS
    SELECT
		id,
		parenthesize,
		parent_expr,
		seq_no,
		subquery,
		negate
    FROM
        query.expression
    WHERE
        type = 'xex';

CREATE OR REPLACE RULE query_expr_xex_insert_rule AS
    ON INSERT TO query.expr_xex
    DO INSTEAD
    INSERT INTO query.expression (
		id,
		type,
		parenthesize,
		parent_expr,
		seq_no,
		subquery,
		negate
    ) VALUES (
        COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
        'xex',
        COALESCE(NEW.parenthesize, FALSE),
        NEW.parent_expr,
        COALESCE(NEW.seq_no, 1),
		NEW.subquery,
		COALESCE(NEW.negate, false)
    );

CREATE OR REPLACE RULE query_expr_xex_update_rule AS
    ON UPDATE TO query.expr_xex
    DO INSTEAD
    UPDATE query.expression SET
        id = NEW.id,
        parenthesize = NEW.parenthesize,
        parent_expr = NEW.parent_expr,
        seq_no = NEW.seq_no,
		subquery = NEW.subquery,
		negate = NEW.negate
    WHERE
        id = OLD.id;

CREATE OR REPLACE RULE query_expr_xex_delete_rule AS
    ON DELETE TO query.expr_xex
    DO INSTEAD
    DELETE FROM query.expression WHERE id = OLD.id;

-- Create updatable view for function call expressions

CREATE OR REPLACE VIEW query.expr_xfunc AS
    SELECT
        id,
        parenthesize,
        parent_expr,
        seq_no,
        column_name,
        function_id,
        negate
    FROM
        query.expression
    WHERE
        type = 'xfunc';

CREATE OR REPLACE RULE query_expr_xfunc_insert_rule AS
    ON INSERT TO query.expr_xfunc
    DO INSTEAD
    INSERT INTO query.expression (
        id,
        type,
        parenthesize,
        parent_expr,
        seq_no,
        column_name,
        function_id,
        negate
    ) VALUES (
        COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
        'xfunc',
        COALESCE(NEW.parenthesize, FALSE),
        NEW.parent_expr,
        COALESCE(NEW.seq_no, 1),
        NEW.column_name,
        NEW.function_id,
        COALESCE(NEW.negate, false)
    );

CREATE OR REPLACE RULE query_expr_xfunc_update_rule AS
    ON UPDATE TO query.expr_xfunc
    DO INSTEAD
    UPDATE query.expression SET
        id = NEW.id,
        parenthesize = NEW.parenthesize,
        parent_expr = NEW.parent_expr,
        seq_no = NEW.seq_no,
        column_name = NEW.column_name,
        function_id = NEW.function_id,
        negate = NEW.negate
    WHERE
        id = OLD.id;

CREATE OR REPLACE RULE query_expr_xfunc_delete_rule AS
    ON DELETE TO query.expr_xfunc
    DO INSTEAD
    DELETE FROM query.expression WHERE id = OLD.id;

-- Create updatable view for IN expressions

CREATE OR REPLACE VIEW query.expr_xin AS
    SELECT
		id,
		parenthesize,
		parent_expr,
		seq_no,
		left_operand,
		subquery,
		negate
    FROM
        query.expression
    WHERE
        type = 'xin';

CREATE OR REPLACE RULE query_expr_xin_insert_rule AS
    ON INSERT TO query.expr_xin
    DO INSTEAD
    INSERT INTO query.expression (
		id,
		type,
		parenthesize,
		parent_expr,
		seq_no,
		left_operand,
		subquery,
		negate
    ) VALUES (
        COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
        'xin',
        COALESCE(NEW.parenthesize, FALSE),
        NEW.parent_expr,
        COALESCE(NEW.seq_no, 1),
		NEW.left_operand,
		NEW.subquery,
		COALESCE(NEW.negate, false)
    );

CREATE OR REPLACE RULE query_expr_xin_update_rule AS
    ON UPDATE TO query.expr_xin
    DO INSTEAD
    UPDATE query.expression SET
        id = NEW.id,
        parenthesize = NEW.parenthesize,
        parent_expr = NEW.parent_expr,
        seq_no = NEW.seq_no,
		left_operand = NEW.left_operand,
		subquery = NEW.subquery,
		negate = NEW.negate
    WHERE
        id = OLD.id;

CREATE OR REPLACE RULE query_expr_xin_delete_rule AS
    ON DELETE TO query.expr_xin
    DO INSTEAD
    DELETE FROM query.expression WHERE id = OLD.id;

-- Create updatable view for IS NULL expressions

CREATE OR REPLACE VIEW query.expr_xisnull AS
    SELECT
		id,
		parenthesize,
		parent_expr,
		seq_no,
		left_operand,
		negate
    FROM
        query.expression
    WHERE
        type = 'xisnull';

CREATE OR REPLACE RULE query_expr_xisnull_insert_rule AS
    ON INSERT TO query.expr_xisnull
    DO INSTEAD
    INSERT INTO query.expression (
		id,
		type,
		parenthesize,
		parent_expr,
		seq_no,
		left_operand,
		negate
    ) VALUES (
        COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
        'xisnull',
        COALESCE(NEW.parenthesize, FALSE),
        NEW.parent_expr,
        COALESCE(NEW.seq_no, 1),
		NEW.left_operand,
		COALESCE(NEW.negate, false)
    );

CREATE OR REPLACE RULE query_expr_xisnull_update_rule AS
    ON UPDATE TO query.expr_xisnull
    DO INSTEAD
    UPDATE query.expression SET
        id = NEW.id,
        parenthesize = NEW.parenthesize,
        parent_expr = NEW.parent_expr,
        seq_no = NEW.seq_no,
		left_operand = NEW.left_operand,
		negate = NEW.negate
    WHERE
        id = OLD.id;

CREATE OR REPLACE RULE query_expr_xisnull_delete_rule AS
    ON DELETE TO query.expr_xisnull
    DO INSTEAD
    DELETE FROM query.expression WHERE id = OLD.id;

-- Create updatable view for NULL expressions

CREATE OR REPLACE VIEW query.expr_xnull AS
    SELECT
		id,
		parenthesize,
		parent_expr,
		seq_no,
		negate
    FROM
        query.expression
    WHERE
        type = 'xnull';

CREATE OR REPLACE RULE query_expr_xnull_insert_rule AS
    ON INSERT TO query.expr_xnull
    DO INSTEAD
    INSERT INTO query.expression (
		id,
		type,
		parenthesize,
		parent_expr,
		seq_no,
		negate
    ) VALUES (
        COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
        'xnull',
        COALESCE(NEW.parenthesize, FALSE),
        NEW.parent_expr,
        COALESCE(NEW.seq_no, 1),
		COALESCE(NEW.negate, false)
    );

CREATE OR REPLACE RULE query_expr_xnull_update_rule AS
    ON UPDATE TO query.expr_xnull
    DO INSTEAD
    UPDATE query.expression SET
        id = NEW.id,
        parenthesize = NEW.parenthesize,
        parent_expr = NEW.parent_expr,
        seq_no = NEW.seq_no,
		negate = NEW.negate
    WHERE
        id = OLD.id;

CREATE OR REPLACE RULE query_expr_xnull_delete_rule AS
    ON DELETE TO query.expr_xnull
    DO INSTEAD
    DELETE FROM query.expression WHERE id = OLD.id;

-- Create updatable view for numeric literal expressions

CREATE OR REPLACE VIEW query.expr_xnum AS
    SELECT
		id,
		parenthesize,
		parent_expr,
		seq_no,
		literal
    FROM
        query.expression
    WHERE
        type = 'xnum';

CREATE OR REPLACE RULE query_expr_xnum_insert_rule AS
    ON INSERT TO query.expr_xnum
    DO INSTEAD
    INSERT INTO query.expression (
		id,
		type,
		parenthesize,
		parent_expr,
		seq_no,
		literal
    ) VALUES (
        COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
        'xnum',
        COALESCE(NEW.parenthesize, FALSE),
        NEW.parent_expr,
        COALESCE(NEW.seq_no, 1),
        NEW.literal
    );

CREATE OR REPLACE RULE query_expr_xnum_update_rule AS
    ON UPDATE TO query.expr_xnum
    DO INSTEAD
    UPDATE query.expression SET
        id = NEW.id,
        parenthesize = NEW.parenthesize,
        parent_expr = NEW.parent_expr,
        seq_no = NEW.seq_no,
        literal = NEW.literal
    WHERE
        id = OLD.id;

CREATE OR REPLACE RULE query_expr_xnum_delete_rule AS
    ON DELETE TO query.expr_xnum
    DO INSTEAD
    DELETE FROM query.expression WHERE id = OLD.id;

-- Create updatable view for operator expressions

CREATE OR REPLACE VIEW query.expr_xop AS
    SELECT
		id,
		parenthesize,
		parent_expr,
		seq_no,
		left_operand,
		operator,
		right_operand,
		negate
    FROM
        query.expression
    WHERE
        type = 'xop';

CREATE OR REPLACE RULE query_expr_xop_insert_rule AS
    ON INSERT TO query.expr_xop
    DO INSTEAD
    INSERT INTO query.expression (
		id,
		type,
		parenthesize,
		parent_expr,
		seq_no,
		left_operand,
		operator,
		right_operand,
		negate
    ) VALUES (
        COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
        'xop',
        COALESCE(NEW.parenthesize, FALSE),
        NEW.parent_expr,
        COALESCE(NEW.seq_no, 1),
		NEW.left_operand,
		NEW.operator,
		NEW.right_operand,
		COALESCE(NEW.negate, false)
    );

CREATE OR REPLACE RULE query_expr_xop_update_rule AS
    ON UPDATE TO query.expr_xop
    DO INSTEAD
    UPDATE query.expression SET
        id = NEW.id,
        parenthesize = NEW.parenthesize,
        parent_expr = NEW.parent_expr,
        seq_no = NEW.seq_no,
		left_operand = NEW.left_operand,
		operator = NEW.operator,
		right_operand = NEW.right_operand,
		negate = NEW.negate
    WHERE
        id = OLD.id;

CREATE OR REPLACE RULE query_expr_xop_delete_rule AS
    ON DELETE TO query.expr_xop
    DO INSTEAD
    DELETE FROM query.expression WHERE id = OLD.id;

-- Create updatable view for series expressions
-- i.e. series of expressions separated by operators

CREATE OR REPLACE VIEW query.expr_xser AS
    SELECT
		id,
		parenthesize,
		parent_expr,
		seq_no,
		operator,
		negate
    FROM
        query.expression
    WHERE
        type = 'xser';

CREATE OR REPLACE RULE query_expr_xser_insert_rule AS
    ON INSERT TO query.expr_xser
    DO INSTEAD
    INSERT INTO query.expression (
		id,
		type,
		parenthesize,
		parent_expr,
		seq_no,
		operator,
		negate
    ) VALUES (
        COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
        'xser',
        COALESCE(NEW.parenthesize, FALSE),
        NEW.parent_expr,
        COALESCE(NEW.seq_no, 1),
		NEW.operator,
		COALESCE(NEW.negate, false)
    );

CREATE OR REPLACE RULE query_expr_xser_update_rule AS
    ON UPDATE TO query.expr_xser
    DO INSTEAD
    UPDATE query.expression SET
        id = NEW.id,
        parenthesize = NEW.parenthesize,
        parent_expr = NEW.parent_expr,
        seq_no = NEW.seq_no,
		operator = NEW.operator,
		negate = NEW.negate
    WHERE
        id = OLD.id;

CREATE OR REPLACE RULE query_expr_xser_delete_rule AS
    ON DELETE TO query.expr_xser
    DO INSTEAD
    DELETE FROM query.expression WHERE id = OLD.id;

-- Create updatable view for string literal expressions

CREATE OR REPLACE VIEW query.expr_xstr AS
    SELECT
        id,
        parenthesize,
        parent_expr,
        seq_no,
        literal
    FROM
        query.expression
    WHERE
        type = 'xstr';

CREATE OR REPLACE RULE query_expr_string_insert_rule AS
    ON INSERT TO query.expr_xstr
    DO INSTEAD
    INSERT INTO query.expression (
        id,
        type,
        parenthesize,
        parent_expr,
        seq_no,
        literal
    ) VALUES (
        COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
        'xstr',
        COALESCE(NEW.parenthesize, FALSE),
        NEW.parent_expr,
        COALESCE(NEW.seq_no, 1),
        NEW.literal
    );

CREATE OR REPLACE RULE query_expr_string_update_rule AS
    ON UPDATE TO query.expr_xstr
    DO INSTEAD
    UPDATE query.expression SET
        id = NEW.id,
        parenthesize = NEW.parenthesize,
        parent_expr = NEW.parent_expr,
        seq_no = NEW.seq_no,
        literal = NEW.literal
    WHERE
        id = OLD.id;

CREATE OR REPLACE RULE query_expr_string_delete_rule AS
    ON DELETE TO query.expr_xstr
    DO INSTEAD
    DELETE FROM query.expression WHERE id = OLD.id;

-- Create updatable view for subquery expressions

CREATE OR REPLACE VIEW query.expr_xsubq AS
    SELECT
		id,
		parenthesize,
		parent_expr,
		seq_no,
		subquery,
		negate
    FROM
        query.expression
    WHERE
        type = 'xsubq';

CREATE OR REPLACE RULE query_expr_xsubq_insert_rule AS
    ON INSERT TO query.expr_xsubq
    DO INSTEAD
    INSERT INTO query.expression (
		id,
		type,
		parenthesize,
		parent_expr,
		seq_no,
		subquery,
		negate
    ) VALUES (
        COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
        'xsubq',
        COALESCE(NEW.parenthesize, FALSE),
        NEW.parent_expr,
        COALESCE(NEW.seq_no, 1),
		NEW.subquery,
		COALESCE(NEW.negate, false)
    );

CREATE OR REPLACE RULE query_expr_xsubq_update_rule AS
    ON UPDATE TO query.expr_xsubq
    DO INSTEAD
    UPDATE query.expression SET
        id = NEW.id,
        parenthesize = NEW.parenthesize,
        parent_expr = NEW.parent_expr,
        seq_no = NEW.seq_no,
		subquery = NEW.subquery,
		negate = NEW.negate
    WHERE
        id = OLD.id;

CREATE OR REPLACE RULE query_expr_xsubq_delete_rule AS
    ON DELETE TO query.expr_xsubq
    DO INSTEAD
    DELETE FROM query.expression WHERE id = OLD.id;

CREATE TABLE action.fieldset (
    id              SERIAL          PRIMARY KEY,
    owner           INT             NOT NULL REFERENCES actor.usr (id)
                                    DEFERRABLE INITIALLY DEFERRED,
    owning_lib      INT             NOT NULL REFERENCES actor.org_unit (id)
                                    DEFERRABLE INITIALLY DEFERRED,
    status          TEXT            NOT NULL
                                    CONSTRAINT valid_status CHECK ( status in
                                    ( 'PENDING', 'APPLIED', 'ERROR' )),
    creation_time   TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
    scheduled_time  TIMESTAMPTZ,
    applied_time    TIMESTAMPTZ,
    classname       TEXT            NOT NULL, -- an IDL class name
    name            TEXT            NOT NULL,
    stored_query    INT             REFERENCES query.stored_query (id)
                                    DEFERRABLE INITIALLY DEFERRED,
    pkey_value      TEXT,
    CONSTRAINT lib_name_unique UNIQUE (owning_lib, name),
    CONSTRAINT fieldset_one_or_the_other CHECK (
        (stored_query IS NOT NULL AND pkey_value IS NULL) OR
        (pkey_value IS NOT NULL AND stored_query IS NULL)
    )
    -- the CHECK constraint means we can update the fields for a single
    -- row without all the extra overhead involved in a query
);

CREATE INDEX action_fieldset_sched_time_idx ON action.fieldset( scheduled_time );
CREATE INDEX action_owner_idx               ON action.fieldset( owner );

CREATE TABLE action.fieldset_col_val (
    id              SERIAL  PRIMARY KEY,
    fieldset        INT     NOT NULL REFERENCES action.fieldset
                                         ON DELETE CASCADE
                                         DEFERRABLE INITIALLY DEFERRED,
    col             TEXT    NOT NULL,  -- "field" from the idl ... the column on the table
    val             TEXT,              -- value for the column ... NULL means, well, NULL
    CONSTRAINT fieldset_col_once_per_set UNIQUE (fieldset, col)
);

CREATE OR REPLACE FUNCTION action.apply_fieldset(
	fieldset_id IN INT,        -- id from action.fieldset
	table_name  IN TEXT,       -- table to be updated
	pkey_name   IN TEXT,       -- name of primary key column in that table
	query       IN TEXT        -- query constructed by qstore (for query-based
	                           --    fieldsets only; otherwise null
)
RETURNS TEXT AS $$
DECLARE
	statement TEXT;
	fs_status TEXT;
	fs_pkey_value TEXT;
	fs_query TEXT;
	sep CHAR;
	status_code TEXT;
	msg TEXT;
	update_count INT;
	cv RECORD;
BEGIN
	-- Sanity checks
	IF fieldset_id IS NULL THEN
		RETURN 'Fieldset ID parameter is NULL';
	END IF;
	IF table_name IS NULL THEN
		RETURN 'Table name parameter is NULL';
	END IF;
	IF pkey_name IS NULL THEN
		RETURN 'Primary key name parameter is NULL';
	END IF;
	--
	statement := 'UPDATE ' || table_name || ' SET';
	--
	SELECT
		status,
		quote_literal( pkey_value )
	INTO
		fs_status,
		fs_pkey_value
	FROM
		action.fieldset
	WHERE
		id = fieldset_id;
	--
	IF fs_status IS NULL THEN
		RETURN 'No fieldset found for id = ' || fieldset_id;
	ELSIF fs_status = 'APPLIED' THEN
		RETURN 'Fieldset ' || fieldset_id || ' has already been applied';
	END IF;
	--
	sep := '';
	FOR cv IN
		SELECT  col,
				val
		FROM    action.fieldset_col_val
		WHERE   fieldset = fieldset_id
	LOOP
		statement := statement || sep || ' ' || cv.col
					 || ' = ' || coalesce( quote_literal( cv.val ), 'NULL' );
		sep := ',';
	END LOOP;
	--
	IF sep = '' THEN
		RETURN 'Fieldset ' || fieldset_id || ' has no column values defined';
	END IF;
	--
	-- Add the WHERE clause.  This differs according to whether it's a
	-- single-row fieldset or a query-based fieldset.
	--
	IF query IS NULL        AND fs_pkey_value IS NULL THEN
		RETURN 'Incomplete fieldset: neither a primary key nor a query available';
	ELSIF query IS NOT NULL AND fs_pkey_value IS NULL THEN
	    fs_query := rtrim( query, ';' );
	    statement := statement || ' WHERE ' || pkey_name || ' IN ( '
	                 || fs_query || ' );';
	ELSIF query IS NULL     AND fs_pkey_value IS NOT NULL THEN
		statement := statement || ' WHERE ' || pkey_name || ' = '
				     || fs_pkey_value || ';';
	ELSE  -- both are not null
		RETURN 'Ambiguous fieldset: both a primary key and a query provided';
	END IF;
	--
	-- Execute the update
	--
	BEGIN
		EXECUTE statement;
		GET DIAGNOSTICS update_count = ROW_COUNT;
		--
		IF UPDATE_COUNT > 0 THEN
			status_code := 'APPLIED';
			msg := NULL;
		ELSE
			status_code := 'ERROR';
			msg := 'No eligible rows found for fieldset ' || fieldset_id;
    	END IF;
	EXCEPTION WHEN OTHERS THEN
		status_code := 'ERROR';
		msg := 'Unable to apply fieldset ' || fieldset_id
			   || ': ' || sqlerrm;
	END;
	--
	-- Update fieldset status
	--
	UPDATE action.fieldset
	SET status       = status_code,
	    applied_time = now()
	WHERE id = fieldset_id;
	--
	RETURN msg;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION action.apply_fieldset( INT, TEXT, TEXT, TEXT ) IS $$
/**
 * Applies a specified fieldset, using a supplied table name and primary
 * key name.  The query parameter should be non-null only for
 * query-based fieldsets.
 *
 * Returns NULL if successful, or an error message if not.
 */
$$;

CREATE INDEX uhr_hold_idx ON action.unfulfilled_hold_list (hold);

CREATE OR REPLACE VIEW action.unfulfilled_hold_loops AS
    SELECT  u.hold,
            c.circ_lib,
            count(*)
      FROM  action.unfulfilled_hold_list u
            JOIN asset.copy c ON (c.id = u.current_copy)
      GROUP BY 1,2;

CREATE OR REPLACE VIEW action.unfulfilled_hold_min_loop AS
    SELECT  hold,
            min(count)
      FROM  action.unfulfilled_hold_loops
      GROUP BY 1;

CREATE OR REPLACE VIEW action.unfulfilled_hold_innermost_loop AS
    SELECT  DISTINCT l.*
      FROM  action.unfulfilled_hold_loops l
            JOIN action.unfulfilled_hold_min_loop m USING (hold)
      WHERE l.count = m.min;

ALTER TABLE asset.copy
ADD COLUMN dummy_isbn TEXT;

ALTER TABLE auditor.asset_copy_history
ADD COLUMN dummy_isbn TEXT;

-- Add new column status_changed_date to asset.copy, with trigger to maintain it
-- Add corresponding new column to auditor.asset_copy_history

ALTER TABLE asset.copy
	ADD COLUMN status_changed_time TIMESTAMPTZ;

ALTER TABLE auditor.asset_copy_history
	ADD COLUMN status_changed_time TIMESTAMPTZ;

CREATE OR REPLACE FUNCTION asset.acp_status_changed()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.status <> OLD.status THEN
        NEW.status_changed_time := now();
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER acp_status_changed_trig
	BEFORE UPDATE ON asset.copy
	FOR EACH ROW EXECUTE PROCEDURE asset.acp_status_changed();

ALTER TABLE asset.copy
ADD COLUMN mint_condition boolean NOT NULL DEFAULT TRUE;

ALTER TABLE auditor.asset_copy_history
ADD COLUMN mint_condition boolean NOT NULL DEFAULT TRUE;

ALTER TABLE asset.copy ADD COLUMN floating BOOL NOT NULL DEFAULT FALSE;
ALTER TABLE auditor.asset_copy_history ADD COLUMN floating BOOL;

DROP INDEX IF EXISTS asset.copy_barcode_key;
CREATE UNIQUE INDEX copy_barcode_key ON asset.copy (barcode) WHERE deleted = FALSE OR deleted IS FALSE;

-- Note: later we create a trigger a_opac_vis_mat_view_tgr
-- AFTER INSERT OR UPDATE ON asset.copy

ALTER TABLE asset.copy ADD COLUMN cost NUMERIC(8,2);
ALTER TABLE auditor.asset_copy_history ADD COLUMN cost NUMERIC(8,2);

-- Moke mostly parallel changes to action.circulation
-- and action.aged_circulation

ALTER TABLE action.circulation
ADD COLUMN workstation INT
    REFERENCES actor.workstation
	ON DELETE SET NULL
	DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE action.aged_circulation
ADD COLUMN workstation INT;

ALTER TABLE action.circulation
ADD COLUMN parent_circ BIGINT
	REFERENCES action.circulation(id)
	DEFERRABLE INITIALLY DEFERRED;

CREATE UNIQUE INDEX circ_parent_idx
ON action.circulation( parent_circ )
WHERE parent_circ IS NOT NULL;

ALTER TABLE action.aged_circulation
ADD COLUMN parent_circ BIGINT;

ALTER TABLE action.circulation
ADD COLUMN checkin_workstation INT
	REFERENCES actor.workstation(id)
	ON DELETE SET NULL
	DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE action.aged_circulation
ADD COLUMN checkin_workstation INT;

ALTER TABLE action.circulation
ADD COLUMN checkin_scan_time TIMESTAMPTZ;

ALTER TABLE action.aged_circulation
ADD COLUMN checkin_scan_time TIMESTAMPTZ;

CREATE INDEX action_circulation_target_copy_idx
ON action.circulation (target_copy);

CREATE INDEX action_aged_circulation_target_copy_idx
ON action.aged_circulation (target_copy);

ALTER TABLE action.circulation
DROP CONSTRAINT circulation_stop_fines_check;

ALTER TABLE action.circulation
	ADD CONSTRAINT circulation_stop_fines_check
	CHECK (stop_fines IN (
        'CHECKIN','CLAIMSRETURNED','LOST','MAXFINES','RENEW','LONGOVERDUE','CLAIMSNEVERCHECKEDOUT'));

-- Hard due-date functionality
CREATE TABLE config.hard_due_date (
        id          SERIAL      PRIMARY KEY,
        name        TEXT        NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
        ceiling_date    TIMESTAMPTZ NOT NULL,
        forceto     BOOL        NOT NULL,
        owner       INT         NOT NULL
);

CREATE TABLE config.hard_due_date_values (
    id                  SERIAL      PRIMARY KEY,
    hard_due_date       INT         NOT NULL REFERENCES config.hard_due_date (id)
                                    DEFERRABLE INITIALLY DEFERRED,
    ceiling_date        TIMESTAMPTZ NOT NULL,
    active_date         TIMESTAMPTZ NOT NULL
);

ALTER TABLE config.circ_matrix_matchpoint ADD COLUMN hard_due_date INT REFERENCES config.hard_due_date (id);

CREATE OR REPLACE FUNCTION config.update_hard_due_dates () RETURNS INT AS $func$
DECLARE
    temp_value  config.hard_due_date_values%ROWTYPE;
    updated     INT := 0;
BEGIN
    FOR temp_value IN
      SELECT  DISTINCT ON (hard_due_date) *
        FROM  config.hard_due_date_values
        WHERE active_date <= NOW() -- We've passed (or are at) the rollover time
        ORDER BY active_date DESC -- Latest (nearest to us) active time
   LOOP
        UPDATE  config.hard_due_date
          SET   ceiling_date = temp_value.ceiling_date
          WHERE id = temp_value.hard_due_date
                AND ceiling_date <> temp_value.ceiling_date; -- Time is equal if we've already updated the chdd

        IF FOUND THEN
            updated := updated + 1;
        END IF;
    END LOOP;

    RETURN updated;
END;
$func$ LANGUAGE plpgsql;

-- Correct some long-standing misspellings involving variations of "recur"

ALTER TABLE action.circulation RENAME COLUMN recuring_fine TO recurring_fine;
ALTER TABLE action.circulation RENAME COLUMN recuring_fine_rule TO recurring_fine_rule;

ALTER TABLE action.aged_circulation RENAME COLUMN recuring_fine TO recurring_fine;
ALTER TABLE action.aged_circulation RENAME COLUMN recuring_fine_rule TO recurring_fine_rule;

ALTER TABLE config.rule_recuring_fine RENAME TO rule_recurring_fine;
ALTER TABLE config.rule_recuring_fine_id_seq RENAME TO rule_recurring_fine_id_seq;

ALTER TABLE config.rule_recurring_fine RENAME COLUMN recurance_interval TO recurrence_interval;

-- Might as well keep the comment in sync as well
COMMENT ON TABLE config.rule_recurring_fine IS $$
/*
 * Copyright (C) 2005  Georgia Public Library Service 
 * Mike Rylander <mrylander@gmail.com>
 *
 * Circulation Recurring Fine rules
 *
 * Each circulation is given a recurring fine amount based on one of
 * these rules.  The recurrence_interval should not be any shorter
 * than the interval between runs of the fine_processor.pl script
 * (which is run from CRON), or you could miss fines.
 * 
 *
 * ****
 *
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 */
$$;

-- Extend the name change to some related views:

DROP VIEW IF EXISTS reporter.overdue_circs;

CREATE OR REPLACE VIEW reporter.overdue_circs AS
SELECT  *
  FROM  action.circulation
    WHERE checkin_time is null
	        AND (stop_fines NOT IN ('LOST','CLAIMSRETURNED') OR stop_fines IS NULL)
			        AND due_date < now();

DROP VIEW IF EXISTS stats.fleshed_circulation;

DROP VIEW IF EXISTS stats.fleshed_copy;

CREATE VIEW stats.fleshed_copy AS
        SELECT  cp.*,
        CAST(cp.create_date AS DATE) AS create_date_day,
        CAST(cp.edit_date AS DATE) AS edit_date_day,
        DATE_TRUNC('hour', cp.create_date) AS create_date_hour,
        DATE_TRUNC('hour', cp.edit_date) AS edit_date_hour,
                cn.label AS call_number_label,
                cn.owning_lib,
                rd.item_lang,
                rd.item_type,
                rd.item_form
        FROM    asset.copy cp
                JOIN asset.call_number cn ON (cp.call_number = cn.id)
                JOIN metabib.rec_descriptor rd ON (rd.record = cn.record);

CREATE VIEW stats.fleshed_circulation AS
        SELECT  c.*,
                CAST(c.xact_start AS DATE) AS start_date_day,
                CAST(c.xact_finish AS DATE) AS finish_date_day,
                DATE_TRUNC('hour', c.xact_start) AS start_date_hour,
                DATE_TRUNC('hour', c.xact_finish) AS finish_date_hour,
                cp.call_number_label,
                cp.owning_lib,
                cp.item_lang,
                cp.item_type,
                cp.item_form
        FROM    action.circulation c
                JOIN stats.fleshed_copy cp ON (cp.id = c.target_copy);

-- Drop a view temporarily in order to alter action.all_circulation, upon
-- which it is dependent.  We will recreate the view later.

DROP VIEW IF EXISTS extend_reporter.full_circ_count;

-- You would think that CREATE OR REPLACE would be enough, but in testing
-- PostgreSQL complained about renaming the columns in the view. So we
-- drop the view first.
DROP VIEW IF EXISTS action.all_circulation;

CREATE OR REPLACE VIEW action.all_circulation AS
    SELECT  id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
        copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
        circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, due_date,
        stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
        max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
        max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ
      FROM  action.aged_circulation
            UNION ALL
    SELECT  DISTINCT circ.id,COALESCE(a.post_code,b.post_code) AS usr_post_code, p.home_ou AS usr_home_ou, p.profile AS usr_profile, EXTRACT(YEAR FROM p.dob)::INT AS usr_birth_year,
        cp.call_number AS copy_call_number, cp.location AS copy_location, cn.owning_lib AS copy_owning_lib, cp.circ_lib AS copy_circ_lib,
        cn.record AS copy_bib_record, circ.xact_start, circ.xact_finish, circ.target_copy, circ.circ_lib, circ.circ_staff, circ.checkin_staff,
        circ.checkin_lib, circ.renewal_remaining, circ.due_date, circ.stop_fines_time, circ.checkin_time, circ.create_time, circ.duration,
        circ.fine_interval, circ.recurring_fine, circ.max_fine, circ.phone_renewal, circ.desk_renewal, circ.opac_renewal, circ.duration_rule,
        circ.recurring_fine_rule, circ.max_fine_rule, circ.stop_fines, circ.workstation, circ.checkin_workstation, circ.checkin_scan_time,
        circ.parent_circ
      FROM  action.circulation circ
        JOIN asset.copy cp ON (circ.target_copy = cp.id)
        JOIN asset.call_number cn ON (cp.call_number = cn.id)
        JOIN actor.usr p ON (circ.usr = p.id)
        LEFT JOIN actor.usr_address a ON (p.mailing_address = a.id)
        LEFT JOIN actor.usr_address b ON (p.billing_address = a.id);

-- Recreate the temporarily dropped view, having altered the action.all_circulation view:

CREATE OR REPLACE VIEW extend_reporter.full_circ_count AS
 SELECT cp.id, COALESCE(sum(c.circ_count), 0::bigint) + COALESCE(count(circ.id), 0::bigint) + COALESCE(count(acirc.id), 0::bigint) AS circ_count
   FROM asset."copy" cp
   LEFT JOIN extend_reporter.legacy_circ_count c USING (id)
   LEFT JOIN "action".circulation circ ON circ.target_copy = cp.id
   LEFT JOIN "action".aged_circulation acirc ON acirc.target_copy = cp.id
  GROUP BY cp.id;

CREATE UNIQUE INDEX only_one_concurrent_checkout_per_copy ON action.circulation(target_copy) WHERE checkin_time IS NULL;

ALTER TABLE action.circulation DROP CONSTRAINT action_circulation_target_copy_fkey;

-- Rebuild dependent views

DROP VIEW IF EXISTS action.billable_circulations;

CREATE OR REPLACE VIEW action.billable_circulations AS
    SELECT  *
      FROM  action.circulation
      WHERE xact_finish IS NULL;

DROP VIEW IF EXISTS action.open_circulation;

CREATE OR REPLACE VIEW action.open_circulation AS
    SELECT  *
      FROM  action.circulation
      WHERE checkin_time IS NULL
      ORDER BY due_date;

CREATE OR REPLACE FUNCTION action.age_circ_on_delete () RETURNS TRIGGER AS $$
DECLARE
found char := 'N';
BEGIN

    -- If there are any renewals for this circulation, don't archive or delete
    -- it yet.   We'll do so later, when we archive and delete the renewals.

    SELECT 'Y' INTO found
    FROM action.circulation
    WHERE parent_circ = OLD.id
    LIMIT 1;

    IF found = 'Y' THEN
        RETURN NULL;  -- don't delete
        END IF;

    -- Archive a copy of the old row to action.aged_circulation

    INSERT INTO action.aged_circulation
        (id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
        copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
        circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, due_date,
        stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
        max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
        max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ)
      SELECT
        id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
        copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
        circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, due_date,
        stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
        max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
        max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ
        FROM action.all_circulation WHERE id = OLD.id;

    RETURN OLD;
END;
$$ LANGUAGE 'plpgsql';

UPDATE config.z3950_attr SET truncation = 1 WHERE source = 'biblios' AND name = 'title';

UPDATE config.z3950_attr SET truncation = 1 WHERE source = 'biblios' AND truncation = 0;

-- Adding circ.holds.target_skip_me OU setting logic to the pre-matchpoint tests

CREATE OR REPLACE FUNCTION action.find_hold_matrix_matchpoint( pickup_ou INT, request_ou INT, match_item BIGINT, match_user INT, match_requestor INT ) RETURNS INT AS $func$
DECLARE
    current_requestor_group    permission.grp_tree%ROWTYPE;
    requestor_object    actor.usr%ROWTYPE;
    user_object        actor.usr%ROWTYPE;
    item_object        asset.copy%ROWTYPE;
    item_cn_object        asset.call_number%ROWTYPE;
    rec_descriptor        metabib.rec_descriptor%ROWTYPE;
    current_mp_weight    FLOAT;
    matchpoint_weight    FLOAT;
    tmp_weight        FLOAT;
    current_mp        config.hold_matrix_matchpoint%ROWTYPE;
    matchpoint        config.hold_matrix_matchpoint%ROWTYPE;
BEGIN
    SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
    SELECT INTO requestor_object * FROM actor.usr WHERE id = match_requestor;
    SELECT INTO item_object * FROM asset.copy WHERE id = match_item;
    SELECT INTO item_cn_object * FROM asset.call_number WHERE id = item_object.call_number;
    SELECT INTO rec_descriptor r.* FROM metabib.rec_descriptor r WHERE r.record = item_cn_object.record;

    PERFORM * FROM config.internal_flag WHERE name = 'circ.holds.usr_not_requestor' AND enabled;

    IF NOT FOUND THEN
        SELECT INTO current_requestor_group * FROM permission.grp_tree WHERE id = requestor_object.profile;
    ELSE
        SELECT INTO current_requestor_group * FROM permission.grp_tree WHERE id = user_object.profile;
    END IF;

    LOOP 
        -- for each potential matchpoint for this ou and group ...
        FOR current_mp IN
            SELECT    m.*
              FROM    config.hold_matrix_matchpoint m
              WHERE    m.requestor_grp = current_requestor_group.id AND m.active
              ORDER BY    CASE WHEN m.circ_modifier    IS NOT NULL THEN 16 ELSE 0 END +
                    CASE WHEN m.juvenile_flag    IS NOT NULL THEN 16 ELSE 0 END +
                    CASE WHEN m.marc_type        IS NOT NULL THEN 8 ELSE 0 END +
                    CASE WHEN m.marc_form        IS NOT NULL THEN 4 ELSE 0 END +
                    CASE WHEN m.marc_vr_format    IS NOT NULL THEN 2 ELSE 0 END +
                    CASE WHEN m.ref_flag        IS NOT NULL THEN 1 ELSE 0 END DESC LOOP

            current_mp_weight := 5.0;

            IF current_mp.circ_modifier IS NOT NULL THEN
                CONTINUE WHEN current_mp.circ_modifier <> item_object.circ_modifier OR item_object.circ_modifier IS NULL;
            END IF;

            IF current_mp.marc_type IS NOT NULL THEN
                IF item_object.circ_as_type IS NOT NULL THEN
                    CONTINUE WHEN current_mp.marc_type <> item_object.circ_as_type;
                ELSE
                    CONTINUE WHEN current_mp.marc_type <> rec_descriptor.item_type;
                END IF;
            END IF;

            IF current_mp.marc_form IS NOT NULL THEN
                CONTINUE WHEN current_mp.marc_form <> rec_descriptor.item_form;
            END IF;

            IF current_mp.marc_vr_format IS NOT NULL THEN
                CONTINUE WHEN current_mp.marc_vr_format <> rec_descriptor.vr_format;
            END IF;

            IF current_mp.juvenile_flag IS NOT NULL THEN
                CONTINUE WHEN current_mp.juvenile_flag <> user_object.juvenile;
            END IF;

            IF current_mp.ref_flag IS NOT NULL THEN
                CONTINUE WHEN current_mp.ref_flag <> item_object.ref;
            END IF;


            -- caclulate the rule match weight
            IF current_mp.item_owning_ou IS NOT NULL THEN
                SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.item_owning_ou, item_cn_object.owning_lib)::FLOAT + 1.0)::FLOAT;
                current_mp_weight := current_mp_weight - tmp_weight;
            END IF; 

            IF current_mp.item_circ_ou IS NOT NULL THEN
                SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.item_circ_ou, item_object.circ_lib)::FLOAT + 1.0)::FLOAT;
                current_mp_weight := current_mp_weight - tmp_weight;
            END IF; 

            IF current_mp.pickup_ou IS NOT NULL THEN
                SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.pickup_ou, pickup_ou)::FLOAT + 1.0)::FLOAT;
                current_mp_weight := current_mp_weight - tmp_weight;
            END IF; 

            IF current_mp.request_ou IS NOT NULL THEN
                SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.request_ou, request_ou)::FLOAT + 1.0)::FLOAT;
                current_mp_weight := current_mp_weight - tmp_weight;
            END IF; 

            IF current_mp.user_home_ou IS NOT NULL THEN
                SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.user_home_ou, user_object.home_ou)::FLOAT + 1.0)::FLOAT;
                current_mp_weight := current_mp_weight - tmp_weight;
            END IF; 

            -- set the matchpoint if we found the best one
            IF matchpoint_weight IS NULL OR matchpoint_weight > current_mp_weight THEN
                matchpoint = current_mp;
                matchpoint_weight = current_mp_weight;
            END IF;

        END LOOP;

        EXIT WHEN current_requestor_group.parent IS NULL OR matchpoint.id IS NOT NULL;

        SELECT INTO current_requestor_group * FROM permission.grp_tree WHERE id = current_requestor_group.parent;
    END LOOP;

    RETURN matchpoint.id;
END;
$func$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION action.hold_request_permit_test( pickup_ou INT, request_ou INT, match_item BIGINT, match_user INT, match_requestor INT, retargetting BOOL ) RETURNS SETOF action.matrix_test_result AS $func$
DECLARE
    matchpoint_id        INT;
    user_object        actor.usr%ROWTYPE;
    age_protect_object    config.rule_age_hold_protect%ROWTYPE;
    standing_penalty    config.standing_penalty%ROWTYPE;
    transit_range_ou_type    actor.org_unit_type%ROWTYPE;
    transit_source        actor.org_unit%ROWTYPE;
    item_object        asset.copy%ROWTYPE;
    ou_skip              actor.org_unit_setting%ROWTYPE;
    result            action.matrix_test_result;
    hold_test        config.hold_matrix_matchpoint%ROWTYPE;
    hold_count        INT;
    hold_transit_prox    INT;
    frozen_hold_count    INT;
    context_org_list    INT[];
    done            BOOL := FALSE;
BEGIN
    SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
    SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( pickup_ou );

    result.success := TRUE;

    -- Fail if we couldn't find a user
    IF user_object.id IS NULL THEN
        result.fail_part := 'no_user';
        result.success := FALSE;
        done := TRUE;
        RETURN NEXT result;
        RETURN;
    END IF;

    SELECT INTO item_object * FROM asset.copy WHERE id = match_item;

    -- Fail if we couldn't find a copy
    IF item_object.id IS NULL THEN
        result.fail_part := 'no_item';
        result.success := FALSE;
        done := TRUE;
        RETURN NEXT result;
        RETURN;
    END IF;

    SELECT INTO matchpoint_id action.find_hold_matrix_matchpoint(pickup_ou, request_ou, match_item, match_user, match_requestor);
    result.matchpoint := matchpoint_id;

    SELECT INTO ou_skip * FROM actor.org_unit_setting WHERE name = 'circ.holds.target_skip_me' AND org_unit = item_object.circ_lib;

    -- Fail if the circ_lib for the item has circ.holds.target_skip_me set to true
    IF ou_skip.id IS NOT NULL AND ou_skip.value = 'true' THEN
        result.fail_part := 'circ.holds.target_skip_me';
        result.success := FALSE;
        done := TRUE;
        RETURN NEXT result;
        RETURN;
    END IF;

    -- Fail if user is barred
    IF user_object.barred IS TRUE THEN
        result.fail_part := 'actor.usr.barred';
        result.success := FALSE;
        done := TRUE;
        RETURN NEXT result;
        RETURN;
    END IF;

    -- Fail if we couldn't find any matchpoint (requires a default)
    IF matchpoint_id IS NULL THEN
        result.fail_part := 'no_matchpoint';
        result.success := FALSE;
        done := TRUE;
        RETURN NEXT result;
        RETURN;
    END IF;

    SELECT INTO hold_test * FROM config.hold_matrix_matchpoint WHERE id = matchpoint_id;

    IF hold_test.holdable IS FALSE THEN
        result.fail_part := 'config.hold_matrix_test.holdable';
        result.success := FALSE;
        done := TRUE;
        RETURN NEXT result;
    END IF;

    IF hold_test.transit_range IS NOT NULL THEN
        SELECT INTO transit_range_ou_type * FROM actor.org_unit_type WHERE id = hold_test.transit_range;
        IF hold_test.distance_is_from_owner THEN
            SELECT INTO transit_source ou.* FROM actor.org_unit ou JOIN asset.call_number cn ON (cn.owning_lib = ou.id) WHERE cn.id = item_object.call_number;
        ELSE
            SELECT INTO transit_source * FROM actor.org_unit WHERE id = item_object.circ_lib;
        END IF;

        PERFORM * FROM actor.org_unit_descendants( transit_source.id, transit_range_ou_type.depth ) WHERE id = pickup_ou;

        IF NOT FOUND THEN
            result.fail_part := 'transit_range';
            result.success := FALSE;
            done := TRUE;
            RETURN NEXT result;
        END IF;
    END IF;
 
    IF NOT retargetting THEN
        FOR standing_penalty IN
            SELECT  DISTINCT csp.*
              FROM  actor.usr_standing_penalty usp
                    JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
              WHERE usr = match_user
                    AND usp.org_unit IN ( SELECT * FROM explode_array(context_org_list) )
                    AND (usp.stop_date IS NULL or usp.stop_date > NOW())
                    AND csp.block_list LIKE '%HOLD%' LOOP
    
            result.fail_part := standing_penalty.name;
            result.success := FALSE;
            done := TRUE;
            RETURN NEXT result;
        END LOOP;
    
        IF hold_test.stop_blocked_user IS TRUE THEN
            FOR standing_penalty IN
                SELECT  DISTINCT csp.*
                  FROM  actor.usr_standing_penalty usp
                        JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
                  WHERE usr = match_user
                        AND usp.org_unit IN ( SELECT * FROM explode_array(context_org_list) )
                        AND (usp.stop_date IS NULL or usp.stop_date > NOW())
                        AND csp.block_list LIKE '%CIRC%' LOOP
        
                result.fail_part := standing_penalty.name;
                result.success := FALSE;
                done := TRUE;
                RETURN NEXT result;
            END LOOP;
        END IF;
    
        IF hold_test.max_holds IS NOT NULL THEN
            SELECT    INTO hold_count COUNT(*)
              FROM    action.hold_request
              WHERE    usr = match_user
                AND fulfillment_time IS NULL
                AND cancel_time IS NULL
                AND CASE WHEN hold_test.include_frozen_holds THEN TRUE ELSE frozen IS FALSE END;
    
            IF hold_count >= hold_test.max_holds THEN
                result.fail_part := 'config.hold_matrix_test.max_holds';
                result.success := FALSE;
                done := TRUE;
                RETURN NEXT result;
            END IF;
        END IF;
    END IF;

    IF item_object.age_protect IS NOT NULL THEN
        SELECT INTO age_protect_object * FROM config.rule_age_hold_protect WHERE id = item_object.age_protect;

        IF item_object.create_date + age_protect_object.age > NOW() THEN
            IF hold_test.distance_is_from_owner THEN
                SELECT INTO hold_transit_prox prox FROM actor.org_unit_proximity WHERE from_org = item_cn_object.owning_lib AND to_org = pickup_ou;
            ELSE
                SELECT INTO hold_transit_prox prox FROM actor.org_unit_proximity WHERE from_org = item_object.circ_lib AND to_org = pickup_ou;
            END IF;

            IF hold_transit_prox > age_protect_object.prox THEN
                result.fail_part := 'config.rule_age_hold_protect.prox';
                result.success := FALSE;
                done := TRUE;
                RETURN NEXT result;
            END IF;
        END IF;
    END IF;

    IF NOT done THEN
        RETURN NEXT result;
    END IF;

    RETURN;
END;
$func$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION action.hold_request_permit_test( pickup_ou INT, request_ou INT, match_item BIGINT, match_user INT, match_requestor INT ) RETURNS SETOF action.matrix_test_result AS $func$
    SELECT * FROM action.hold_request_permit_test( $1, $2, $3, $4, $5, FALSE);
$func$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION action.hold_retarget_permit_test( pickup_ou INT, request_ou INT, match_item BIGINT, match_user INT, match_requestor INT ) RETURNS SETOF action.matrix_test_result AS $func$
    SELECT * FROM action.hold_request_permit_test( $1, $2, $3, $4, $5, TRUE );
$func$ LANGUAGE SQL;

-- New post-delete trigger to propagate deletions to parent(s)

CREATE OR REPLACE FUNCTION action.age_parent_circ_on_delete () RETURNS TRIGGER AS $$
BEGIN

    -- Having deleted a renewal, we can delete the original circulation (or a previous
    -- renewal, if that's what parent_circ is pointing to).  That deletion will trigger
    -- deletion of any prior parents, etc. recursively.

    IF OLD.parent_circ IS NOT NULL THEN
        DELETE FROM action.circulation
        WHERE id = OLD.parent_circ;
    END IF;

    RETURN OLD;
END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER age_parent_circ AFTER DELETE ON action.circulation
FOR EACH ROW EXECUTE PROCEDURE action.age_parent_circ_on_delete ();

-- This only gets inserted if there are no other id > 100 billing types
INSERT INTO config.billing_type (id, name, owner) SELECT DISTINCT 101, oils_i18n_gettext(101, 'Misc', 'cbt', 'name'), 1 FROM config.billing_type_id_seq WHERE last_value < 101;
SELECT SETVAL('config.billing_type_id_seq'::TEXT, 101) FROM config.billing_type_id_seq WHERE last_value < 101;

-- Populate xact_type column in the materialized version of billable_xact_summary

CREATE OR REPLACE FUNCTION money.mat_summary_create () RETURNS TRIGGER AS $$
BEGIN
	INSERT INTO money.materialized_billable_xact_summary (id, usr, xact_start, xact_finish, total_paid, total_owed, balance_owed, xact_type)
		VALUES ( NEW.id, NEW.usr, NEW.xact_start, NEW.xact_finish, 0.0, 0.0, 0.0, TG_ARGV[0]);
	RETURN NEW;
END;
$$ LANGUAGE PLPGSQL;
 
DROP TRIGGER IF EXISTS mat_summary_create_tgr ON action.circulation;
CREATE TRIGGER mat_summary_create_tgr AFTER INSERT ON action.circulation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_create ('circulation');
 
DROP TRIGGER IF EXISTS mat_summary_create_tgr ON money.grocery;
CREATE TRIGGER mat_summary_create_tgr AFTER INSERT ON money.grocery FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_create ('grocery');

CREATE RULE money_payment_view_update AS ON UPDATE TO money.payment_view DO INSTEAD 
    UPDATE money.payment SET xact = NEW.xact, payment_ts = NEW.payment_ts, voided = NEW.voided, amount = NEW.amount, note = NEW.note WHERE id = NEW.id;

-- Generate the equivalent of compound subject entries from the existing rows
-- so that we don't have to laboriously reindex them

--INSERT INTO config.metabib_field (field_class, name, format, xpath ) VALUES
--    ( 'subject', 'complete', 'mods32', $$//mods32:mods/mods32:subject//text()$$ );
--
--CREATE INDEX metabib_subject_field_entry_source_idx ON metabib.subject_field_entry (source);
--
--INSERT INTO metabib.subject_field_entry (source, field, value)
--    SELECT source, (
--            SELECT id 
--            FROM config.metabib_field
--            WHERE field_class = 'subject' AND name = 'complete'
--        ), 
--        ARRAY_TO_STRING ( 
--            ARRAY (
--                SELECT value 
--                FROM metabib.subject_field_entry msfe
--                WHERE msfe.source = groupee.source
--                ORDER BY source 
--            ), ' ' 
--        ) AS grouped
--    FROM ( 
--        SELECT source
--        FROM metabib.subject_field_entry
--        GROUP BY source
--    ) AS groupee;

CREATE OR REPLACE FUNCTION money.materialized_summary_billing_del () RETURNS TRIGGER AS $$
DECLARE
        prev_billing    money.billing%ROWTYPE;
        old_billing     money.billing%ROWTYPE;
BEGIN
        SELECT * INTO prev_billing FROM money.billing WHERE xact = OLD.xact AND NOT voided ORDER BY billing_ts DESC LIMIT 1 OFFSET 1;
        SELECT * INTO old_billing FROM money.billing WHERE xact = OLD.xact AND NOT voided ORDER BY billing_ts DESC LIMIT 1;

        IF OLD.id = old_billing.id THEN
                UPDATE  money.materialized_billable_xact_summary
                  SET   last_billing_ts = prev_billing.billing_ts,
                        last_billing_note = prev_billing.note,
                        last_billing_type = prev_billing.billing_type
                  WHERE id = OLD.xact;
        END IF;

        IF NOT OLD.voided THEN
                UPDATE  money.materialized_billable_xact_summary
                  SET   total_owed = total_owed - OLD.amount,
                        balance_owed = balance_owed + OLD.amount
                  WHERE id = OLD.xact;
        END IF;

        RETURN OLD;
END;
$$ LANGUAGE PLPGSQL;

-- ARG! need to rid ourselves of the broken table definition ... this mechanism is not ideal, sorry.
