        CREATE TABLE acq.$$ || sch || $$_$$ || tbl || $$_history (
            audit_id	BIGINT				PRIMARY KEY,
            audit_time	TIMESTAMP WITH TIME ZONE	NOT NULL,
            audit_action	TEXT				NOT NULL,
            LIKE $$ || sch || $$.$$ || tbl || $$
        );
    $$;
	RETURN TRUE;
END;
$creator$ LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION acq.create_acq_func    ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
BEGIN
    EXECUTE $$
        CREATE OR REPLACE FUNCTION acq.audit_$$ || sch || $$_$$ || tbl || $$_func ()
        RETURNS TRIGGER AS $func$
        BEGIN
            INSERT INTO acq.$$ || sch || $$_$$ || tbl || $$_history
                SELECT	nextval('acq.$$ || sch || $$_$$ || tbl || $$_pkey_seq'),
                    now(),
                    SUBSTR(TG_OP,1,1),
                    OLD.*;
            RETURN NULL;
        END;
        $func$ LANGUAGE 'plpgsql';
    $$;
	RETURN TRUE;
END;
$creator$ LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION acq.create_acq_update_trigger ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
BEGIN
    EXECUTE $$
        CREATE TRIGGER audit_$$ || sch || $$_$$ || tbl || $$_update_trigger
            AFTER UPDATE OR DELETE ON $$ || sch || $$.$$ || tbl || $$ FOR EACH ROW
            EXECUTE PROCEDURE acq.audit_$$ || sch || $$_$$ || tbl || $$_func ();
    $$;
	RETURN TRUE;
END;
$creator$ LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION acq.create_acq_lifecycle     ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
BEGIN
    EXECUTE $$
        CREATE OR REPLACE VIEW acq.$$ || sch || $$_$$ || tbl || $$_lifecycle AS
            SELECT	-1, now() as audit_time, '-' as audit_action, *
              FROM	$$ || sch || $$.$$ || tbl || $$
                UNION ALL
            SELECT	*
              FROM	acq.$$ || sch || $$_$$ || tbl || $$_history;
    $$;
	RETURN TRUE;
END;
$creator$ LANGUAGE 'plpgsql';

-- The main event

CREATE OR REPLACE FUNCTION acq.create_acq_auditor ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
BEGIN
    PERFORM acq.create_acq_seq(sch, tbl);
    PERFORM acq.create_acq_history(sch, tbl);
    PERFORM acq.create_acq_func(sch, tbl);
    PERFORM acq.create_acq_update_trigger(sch, tbl);
    PERFORM acq.create_acq_lifecycle(sch, tbl);
    RETURN TRUE;
END;
$creator$ LANGUAGE 'plpgsql';

ALTER TABLE acq.lineitem DROP COLUMN item_count;

CREATE OR REPLACE VIEW acq.fund_debit_total AS
    SELECT  fund.id AS fund,
            fund_debit.encumbrance AS encumbrance,
            SUM( COALESCE( fund_debit.amount, 0 ) ) AS amount
      FROM acq.fund AS fund
			LEFT JOIN acq.fund_debit AS fund_debit
				ON ( fund.id = fund_debit.fund )
      GROUP BY 1,2;

CREATE TABLE acq.debit_attribution (
	id                     INT         NOT NULL PRIMARY KEY,
	fund_debit             INT         NOT NULL
	                                   REFERENCES acq.fund_debit
	                                   DEFERRABLE INITIALLY DEFERRED,
    debit_amount           NUMERIC     NOT NULL,
	funding_source_credit  INT         REFERENCES acq.funding_source_credit
	                                   DEFERRABLE INITIALLY DEFERRED,
    credit_amount          NUMERIC
);

CREATE INDEX acq_attribution_debit_idx
	ON acq.debit_attribution( fund_debit );

CREATE INDEX acq_attribution_credit_idx
	ON acq.debit_attribution( funding_source_credit );

CREATE OR REPLACE FUNCTION acq.attribute_debits() RETURNS VOID AS $$
/*
Function to attribute expenditures and encumbrances to funding source credits,
and thereby to funding sources.

Read the debits in chonological order, attributing each one to one or
more funding source credits.  Constraints:

1. Don't attribute more to a credit than the amount of the credit.

2. For a given fund, don't attribute more to a funding source than the
source has allocated to that fund.

3. Attribute debits to credits with deadlines before attributing them to
credits without deadlines.  Otherwise attribute to the earliest credits
first, based on the deadline date when present, or on the effective date
when there is no deadline.  Use funding_source_credit.id as a tie-breaker.
This ordering is defined by an ORDER BY clause on the view
acq.ordered_funding_source_credit.

Start by truncating the table acq.debit_attribution.  Then insert a row
into that table for each attribution.  If a debit cannot be fully
attributed, insert a row for the unattributable balance, with the 
funding_source_credit and credit_amount columns NULL.
*/
DECLARE
	curr_fund_source_bal RECORD;
	seqno                INT;     -- sequence num for credits applicable to a fund
	fund_credit          RECORD;  -- current row in temp t_fund_credit table
	fc                   RECORD;  -- used for loading t_fund_credit table
	sc                   RECORD;  -- used for loading t_fund_credit table
	--
	-- Used exclusively in the main loop:
	--
	deb                 RECORD;   -- current row from acq.fund_debit table
	curr_credit_bal     RECORD;   -- current row from temp t_credit table
	debit_balance       NUMERIC;  -- amount left to attribute for current debit
	conv_debit_balance  NUMERIC;  -- debit balance in currency of the fund
	attr_amount         NUMERIC;  -- amount being attributed, in currency of debit
	conv_attr_amount    NUMERIC;  -- amount being attributed, in currency of source
	conv_cred_balance   NUMERIC;  -- credit_balance in the currency of the fund
	conv_alloc_balance  NUMERIC;  -- allocated balance in the currency of the fund
	attrib_count        INT;      -- populates id of acq.debit_attribution
BEGIN
	--
	-- Load a temporary table.  For each combination of fund and funding source,
	-- load an entry with the total amount allocated to that fund by that source.
	-- This sum may reflect transfers as well as original allocations.  We will
	-- reduce this balance whenever we attribute debits to it.
	--
	CREATE TEMP TABLE t_fund_source_bal
	ON COMMIT DROP AS
		SELECT
			fund AS fund,
			funding_source AS source,
			sum( amount ) AS balance
		FROM
			acq.fund_allocation
		GROUP BY
			fund,
			funding_source
		HAVING
			sum( amount ) > 0;
	--
	CREATE INDEX t_fund_source_bal_idx
		ON t_fund_source_bal( fund, source );
	-------------------------------------------------------------------------------
	--
	-- Load another temporary table.  For each fund, load zero or more
	-- funding source credits from which that fund can get money.
	--
	CREATE TEMP TABLE t_fund_credit (
		fund        INT,
		seq         INT,
		credit      INT
	) ON COMMIT DROP;
	--
	FOR fc IN
		SELECT DISTINCT fund
		FROM acq.fund_allocation
		ORDER BY fund
	LOOP                  -- Loop over the funds
		seqno := 1;
		FOR sc IN
			SELECT
				ofsc.id
			FROM
				acq.ordered_funding_source_credit AS ofsc
			WHERE
				ofsc.funding_source IN
				(
					SELECT funding_source
					FROM acq.fund_allocation
					WHERE fund = fc.fund
				)
    		ORDER BY
    		    ofsc.sort_priority,
    		    ofsc.sort_date,
    		    ofsc.id
		LOOP                        -- Add each credit to the list
			INSERT INTO t_fund_credit (
				fund,
				seq,
				credit
			) VALUES (
				fc.fund,
				seqno,
				sc.id
			);
			--RAISE NOTICE 'Fund % credit %', fc.fund, sc.id;
			seqno := seqno + 1;
		END LOOP;     -- Loop over credits for a given fund
	END LOOP;         -- Loop over funds
	--
	CREATE INDEX t_fund_credit_idx
		ON t_fund_credit( fund, seq );
	-------------------------------------------------------------------------------
	--
	-- Load yet another temporary table.  This one is a list of funding source
	-- credits, with their balances.  We shall reduce those balances as we
	-- attribute debits to them.
	--
	CREATE TEMP TABLE t_credit
	ON COMMIT DROP AS
        SELECT
            fsc.id AS credit,
            fsc.funding_source AS source,
            fsc.amount AS balance,
            fs.currency_type AS currency_type
        FROM
            acq.funding_source_credit AS fsc,
            acq.funding_source fs
        WHERE
            fsc.funding_source = fs.id
			AND fsc.amount > 0;
	--
	CREATE INDEX t_credit_idx
		ON t_credit( credit );
	--
	-------------------------------------------------------------------------------
	--
	-- Now that we have loaded the lookup tables: loop through the debits,
	-- attributing each one to one or more funding source credits.
	-- 
	truncate table acq.debit_attribution;
	--
	attrib_count := 0;
	FOR deb in
		SELECT
			fd.id,
			fd.fund,
			fd.amount,
			f.currency_type,
			fd.encumbrance
		FROM
			acq.fund_debit fd,
			acq.fund f
		WHERE
			fd.fund = f.id
		ORDER BY
			fd.id
	LOOP
		--RAISE NOTICE 'Debit %, fund %', deb.id, deb.fund;
		--
		debit_balance := deb.amount;
		--
		-- Loop over the funding source credits that are eligible
		-- to pay for this debit
		--
		FOR fund_credit IN
			SELECT
				credit
			FROM
				t_fund_credit
			WHERE
				fund = deb.fund
			ORDER BY
				seq
		LOOP
			--RAISE NOTICE '   Examining credit %', fund_credit.credit;
			--
			-- Look up the balance for this credit.  If it's zero, then
			-- it's not useful, so treat it as if you didn't find it.
			-- (Actually there shouldn't be any zero balances in the table,
			-- but we check just to make sure.)
			--
			SELECT *
			INTO curr_credit_bal
			FROM t_credit
			WHERE
				credit = fund_credit.credit
				AND balance > 0;
			--
			IF curr_credit_bal IS NULL THEN
				--
				-- This credit is exhausted; try the next one.
				--
				CONTINUE;
			END IF;
			--
			--
			-- At this point we have an applicable credit with some money left.
			-- Now see if the relevant funding_source has any money left.
			--
			-- Look up the balance of the allocation for this combination of
			-- fund and source.  If you find such an entry, but it has a zero
			-- balance, then it's not useful, so treat it as unfound.
			-- (Actually there shouldn't be any zero balances in the table,
			-- but we check just to make sure.)
			--
			SELECT *
			INTO curr_fund_source_bal
			FROM t_fund_source_bal
			WHERE
				fund = deb.fund
				AND source = curr_credit_bal.source
				AND balance > 0;
			--
			IF curr_fund_source_bal IS NULL THEN
				--
				-- This fund/source doesn't exist or is already exhausted,
				-- so we can't use this credit.  Go on to the next one.
				--
				CONTINUE;
			END IF;
			--
			-- Convert the available balances to the currency of the fund
			--
			conv_alloc_balance := curr_fund_source_bal.balance * acq.exchange_ratio(
				curr_credit_bal.currency_type, deb.currency_type );
			conv_cred_balance := curr_credit_bal.balance * acq.exchange_ratio(
				curr_credit_bal.currency_type, deb.currency_type );
			--
			-- Determine how much we can attribute to this credit: the minimum
			-- of the debit amount, the fund/source balance, and the
			-- credit balance
			--
			--RAISE NOTICE '   deb bal %', debit_balance;
			--RAISE NOTICE '      source % balance %', curr_credit_bal.source, conv_alloc_balance;
			--RAISE NOTICE '      credit % balance %', curr_credit_bal.credit, conv_cred_balance;
			--
			conv_attr_amount := NULL;
			attr_amount := debit_balance;
			--
			IF attr_amount > conv_alloc_balance THEN
				attr_amount := conv_alloc_balance;
				conv_attr_amount := curr_fund_source_bal.balance;
			END IF;
			IF attr_amount > conv_cred_balance THEN
				attr_amount := conv_cred_balance;
				conv_attr_amount := curr_credit_bal.balance;
			END IF;
			--
			-- If we're attributing all of one of the balances, then that's how
			-- much we will deduct from the balances, and we already captured
			-- that amount above.  Otherwise we must convert the amount of the
			-- attribution from the currency of the fund back to the currency of
			-- the funding source.
			--
			IF conv_attr_amount IS NULL THEN
				conv_attr_amount := attr_amount * acq.exchange_ratio(
					deb.currency_type, curr_credit_bal.currency_type );
			END IF;
			--
			-- Insert a row to record the attribution
			--
			attrib_count := attrib_count + 1;
			INSERT INTO acq.debit_attribution (
				id,
				fund_debit,
				debit_amount,
				funding_source_credit,
				credit_amount
			) VALUES (
				attrib_count,
				deb.id,
				attr_amount,
				curr_credit_bal.credit,
				conv_attr_amount
			);
			--
			-- Subtract the attributed amount from the various balances
			--
			debit_balance := debit_balance - attr_amount;
			curr_fund_source_bal.balance := curr_fund_source_bal.balance - conv_attr_amount;
			--
			IF curr_fund_source_bal.balance <= 0 THEN
				--
				-- This allocation is exhausted.  Delete it so
				-- that we don't waste time looking at it again.
				--
				DELETE FROM t_fund_source_bal
				WHERE
					fund = curr_fund_source_bal.fund
					AND source = curr_fund_source_bal.source;
			ELSE
				UPDATE t_fund_source_bal
				SET balance = balance - conv_attr_amount
				WHERE
					fund = curr_fund_source_bal.fund
					AND source = curr_fund_source_bal.source;
			END IF;
			--
			IF curr_credit_bal.balance <= 0 THEN
				--
				-- This funding source credit is exhausted.  Delete it
				-- so that we don't waste time looking at it again.
				--
				--DELETE FROM t_credit
				--WHERE
				--	credit = curr_credit_bal.credit;
				--
				DELETE FROM t_fund_credit
				WHERE
					credit = curr_credit_bal.credit;
			ELSE
				UPDATE t_credit
				SET balance = curr_credit_bal.balance
				WHERE
					credit = curr_credit_bal.credit;
			END IF;
			--
			-- Are we done with this debit yet?
			--
			IF debit_balance <= 0 THEN
				EXIT;       -- We've fully attributed this debit; stop looking at credits.
			END IF;
		END LOOP;       -- End loop over credits
		--
		IF debit_balance <> 0 THEN
			--
			-- We weren't able to attribute this debit, or at least not
			-- all of it.  Insert a row for the unattributed balance.
			--
			attrib_count := attrib_count + 1;
			INSERT INTO acq.debit_attribution (
				id,
				fund_debit,
				debit_amount,
				funding_source_credit,
				credit_amount
			) VALUES (
				attrib_count,
				deb.id,
				debit_balance,
				NULL,
				NULL
			);
		END IF;
	END LOOP;   -- End of loop over debits
END;
$$ LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION extract_marc_field ( TEXT, BIGINT, TEXT, TEXT ) RETURNS TEXT AS $$
DECLARE
    query TEXT;
    output TEXT;
BEGIN
    query := $q$
        SELECT  regexp_replace(
                    oils_xpath_string(
                        $q$ || quote_literal($3) || $q$,
                        marc,
                        ' '
                    ),
                    $q$ || quote_literal($4) || $q$,
                    '',
                    'g')
          FROM  $q$ || $1 || $q$
          WHERE id = $q$ || $2;

    EXECUTE query INTO output;

    -- RAISE NOTICE 'query: %, output; %', query, output;

    RETURN output;
END;
$$ LANGUAGE PLPGSQL IMMUTABLE;

CREATE OR REPLACE FUNCTION extract_marc_field ( TEXT, BIGINT, TEXT ) RETURNS TEXT AS $$
    SELECT extract_marc_field($1,$2,$3,'');
$$ LANGUAGE SQL IMMUTABLE;

CREATE OR REPLACE FUNCTION asset.merge_record_assets( target_record BIGINT, source_record BIGINT ) RETURNS INT AS $func$
DECLARE
    moved_objects INT := 0;
    source_cn     asset.call_number%ROWTYPE;
    target_cn     asset.call_number%ROWTYPE;
    metarec       metabib.metarecord%ROWTYPE;
    hold          action.hold_request%ROWTYPE;
    ser_rec       serial.record_entry%ROWTYPE;
    uri_count     INT := 0;
    counter       INT := 0;
    uri_datafield TEXT;
    uri_text      TEXT := '';
BEGIN

    -- move any 856 entries on records that have at least one MARC-mapped URI entry
    SELECT  INTO uri_count COUNT(*)
      FROM  asset.uri_call_number_map m
            JOIN asset.call_number cn ON (m.call_number = cn.id)
      WHERE cn.record = source_record;

    IF uri_count > 0 THEN

        SELECT  COUNT(*) INTO counter
          FROM  oils_xpath_table(
                    'id',
                    'marc',
                    'biblio.record_entry',
                    '//*[@tag="856"]',
                    'id=' || source_record
                ) as t(i int,c text);

        FOR i IN 1 .. counter LOOP
            SELECT  '<datafield xmlns="http://www.loc.gov/MARC21/slim"' ||
                        ' tag="856"' || 
                        ' ind1="' || FIRST(ind1) || '"'  || 
                        ' ind2="' || FIRST(ind2) || '">' || 
                        array_to_string(
                            array_accum(
                                '<subfield code="' || subfield || '">' ||
                                regexp_replace(
                                    regexp_replace(
                                        regexp_replace(data,'&','&amp;','g'),
                                        '>', '&gt;', 'g'
                                    ),
                                    '<', '&lt;', 'g'
                                ) || '</subfield>'
                            ), ''
                        ) || '</datafield>' INTO uri_datafield
              FROM  oils_xpath_table(
                        'id',
                        'marc',
                        'biblio.record_entry',
                        '//*[@tag="856"][position()=' || i || ']/@ind1|' || 
                        '//*[@tag="856"][position()=' || i || ']/@ind2|' || 
                        '//*[@tag="856"][position()=' || i || ']/*/@code|' ||
                        '//*[@tag="856"][position()=' || i || ']/*[@code]',
                        'id=' || source_record
                    ) as t(id int,ind1 text, ind2 text,subfield text,data text);

            uri_text := uri_text || uri_datafield;
        END LOOP;

        IF uri_text <> '' THEN
            UPDATE  biblio.record_entry
              SET   marc = regexp_replace(marc,'(</[^>]*record>)', uri_text || E'\\1')
              WHERE id = target_record;
        END IF;

    END IF;

    -- Find and move metarecords to the target record
    SELECT  INTO metarec *
      FROM  metabib.metarecord
      WHERE master_record = source_record;

    IF FOUND THEN
        UPDATE  metabib.metarecord
          SET   master_record = target_record,
            mods = NULL
          WHERE id = metarec.id;

        moved_objects := moved_objects + 1;
    END IF;

    -- Find call numbers attached to the source ...
    FOR source_cn IN SELECT * FROM asset.call_number WHERE record = source_record LOOP

        SELECT  INTO target_cn *
          FROM  asset.call_number
          WHERE label = source_cn.label
            AND owning_lib = source_cn.owning_lib
            AND record = target_record;

        -- ... and if there's a conflicting one on the target ...
        IF FOUND THEN

            -- ... move the copies to that, and ...
            UPDATE  asset.copy
              SET   call_number = target_cn.id
              WHERE call_number = source_cn.id;

            -- ... move V holds to the move-target call number
            FOR hold IN SELECT * FROM action.hold_request WHERE target = source_cn.id AND hold_type = 'V' LOOP

                UPDATE  action.hold_request
                  SET   target = target_cn.id
                  WHERE id = hold.id;

                moved_objects := moved_objects + 1;
            END LOOP;

        -- ... if not ...
        ELSE
            -- ... just move the call number to the target record
            UPDATE  asset.call_number
              SET   record = target_record
              WHERE id = source_cn.id;
        END IF;

        moved_objects := moved_objects + 1;
    END LOOP;

    -- Find T holds targeting the source record ...
    FOR hold IN SELECT * FROM action.hold_request WHERE target = source_record AND hold_type = 'T' LOOP

        -- ... and move them to the target record
        UPDATE  action.hold_request
          SET   target = target_record
          WHERE id = hold.id;

        moved_objects := moved_objects + 1;
    END LOOP;

    -- Find serial records targeting the source record ...
    FOR ser_rec IN SELECT * FROM serial.record_entry WHERE record = source_record LOOP
        -- ... and move them to the target record
        UPDATE  serial.record_entry
          SET   record = target_record
          WHERE id = ser_rec.id;

        moved_objects := moved_objects + 1;
    END LOOP;

    -- Finally, "delete" the source record
    DELETE FROM biblio.record_entry WHERE id = source_record;

    -- That's all, folks!
    RETURN moved_objects;
END;
$func$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION acq.transfer_fund(
	old_fund   IN INT,
	old_amount IN NUMERIC,     -- in currency of old fund
	new_fund   IN INT,
	new_amount IN NUMERIC,     -- in currency of new fund
	user_id    IN INT,
	xfer_note  IN TEXT         -- to be recorded in acq.fund_transfer
	-- ,funding_source_in IN INT  -- if user wants to specify a funding source (see notes)
) RETURNS VOID AS $$
/* -------------------------------------------------------------------------------

Function to transfer money from one fund to another.

A transfer is represented as a pair of entries in acq.fund_allocation, with a
negative amount for the old (losing) fund and a positive amount for the new
(gaining) fund.  In some cases there may be more than one such pair of entries
in order to pull the money from different funding sources, or more specifically
from different funding source credits.  For each such pair there is also an
entry in acq.fund_transfer.

Since funding_source is a non-nullable column in acq.fund_allocation, we must
choose a funding source for the transferred money to come from.  This choice
must meet two constraints, so far as possible:

1. The amount transferred from a given funding source must not exceed the
amount allocated to the old fund by the funding source.  To that end we
compare the amount being transferred to the amount allocated.

2. We shouldn't transfer money that has already been spent or encumbered, as
defined by the funding attribution process.  We attribute expenses to the
oldest funding source credits first.  In order to avoid transferring that
attributed money, we reverse the priority, transferring from the newest funding
source credits first.  There can be no guarantee that this approach will
avoid overcommitting a fund, but no other approach can do any better.

In this context the age of a funding source credit is defined by the
deadline_date for credits with deadline_dates, and by the effective_date for
credits without deadline_dates, with the proviso that credits with deadline_dates
are all considered "older" than those without.

----------

In the signature for this function, there is one last parameter commented out,
named "funding_source_in".  Correspondingly, the WHERE clause for the query
driving the main loop has an OR clause commented out, which references the
funding_source_in parameter.

If these lines are uncommented, this function will allow the user optionally to
restrict a fund transfer to a specified funding source.  If the source
parameter is left NULL, then there will be no such restriction.

------------------------------------------------------------------------------- */ 
DECLARE
	same_currency      BOOLEAN;
	currency_ratio     NUMERIC;
	old_fund_currency  TEXT;
	old_remaining      NUMERIC;  -- in currency of old fund
	new_fund_currency  TEXT;
	new_fund_active    BOOLEAN;
	new_remaining      NUMERIC;  -- in currency of new fund
	curr_old_amt       NUMERIC;  -- in currency of old fund
	curr_new_amt       NUMERIC;  -- in currency of new fund
	source_addition    NUMERIC;  -- in currency of funding source
	source_deduction   NUMERIC;  -- in currency of funding source
	orig_allocated_amt NUMERIC;  -- in currency of funding source
	allocated_amt      NUMERIC;  -- in currency of fund
	source             RECORD;
BEGIN
	--
	-- Sanity checks
	--
	IF old_fund IS NULL THEN
		RAISE EXCEPTION 'acq.transfer_fund: old fund id is NULL';
	END IF;
	--
	IF old_amount IS NULL THEN
		RAISE EXCEPTION 'acq.transfer_fund: amount to transfer is NULL';
	END IF;
	--
	-- The new fund and its amount must be both NULL or both not NULL.
	--
	IF new_fund IS NOT NULL AND new_amount IS NULL THEN
		RAISE EXCEPTION 'acq.transfer_fund: amount to transfer to receiving fund is NULL';
	END IF;
	--
	IF new_fund IS NULL AND new_amount IS NOT NULL THEN
		RAISE EXCEPTION 'acq.transfer_fund: receiving fund is NULL, its amount is not NULL';
	END IF;
	--
	IF user_id IS NULL THEN
		RAISE EXCEPTION 'acq.transfer_fund: user id is NULL';
	END IF;
	--
	-- Initialize the amounts to be transferred, each denominated
	-- in the currency of its respective fund.  They will be
	-- reduced on each iteration of the loop.
	--
	old_remaining := old_amount;
	new_remaining := new_amount;
	--
	-- RAISE NOTICE 'Transferring % in fund % to % in fund %',
	--	old_amount, old_fund, new_amount, new_fund;
	--
	-- Get the currency types of the old and new funds.
	--
	SELECT
		currency_type
	INTO
		old_fund_currency
	FROM
		acq.fund
	WHERE
		id = old_fund;
	--
	IF old_fund_currency IS NULL THEN
		RAISE EXCEPTION 'acq.transfer_fund: old fund id % is not defined', old_fund;
	END IF;
	--
	IF new_fund IS NOT NULL THEN
		SELECT
			currency_type,
			active
		INTO
			new_fund_currency,
			new_fund_active
		FROM
			acq.fund
		WHERE
			id = new_fund;
		--
		IF new_fund_currency IS NULL THEN
			RAISE EXCEPTION 'acq.transfer_fund: new fund id % is not defined', new_fund;
		ELSIF NOT new_fund_active THEN
			--
			-- No point in putting money into a fund from whence you can't spend it
			--
			RAISE EXCEPTION 'acq.transfer_fund: new fund id % is inactive', new_fund;
		END IF;
		--
		IF new_amount = old_amount THEN
			same_currency := true;
			currency_ratio := 1;
		ELSE
			--
			-- We'll have to translate currency between funds.  We presume that
			-- the calling code has already applied an appropriate exchange rate,
			-- so we'll apply the same conversion to each sub-transfer.
			--
			same_currency := false;
			currency_ratio := new_amount / old_amount;
		END IF;
	END IF;
	--
	-- Identify the funding source(s) from which we want to transfer the money.
	-- The principle is that we want to transfer the newest money first, because
	-- we spend the oldest money first.  The priority for spending is defined
	-- by a sort of the view acq.ordered_funding_source_credit.
	--
	FOR source in
		SELECT
			ofsc.id,
			ofsc.funding_source,
			ofsc.amount,
			ofsc.amount * acq.exchange_ratio( fs.currency_type, old_fund_currency )
				AS converted_amt,
			fs.currency_type
		FROM
			acq.ordered_funding_source_credit AS ofsc,
			acq.funding_source fs
		WHERE
			ofsc.funding_source = fs.id
			and ofsc.funding_source IN
			(
				SELECT funding_source
				FROM acq.fund_allocation
				WHERE fund = old_fund
			)
			-- and
			-- (
			-- 	ofsc.funding_source = funding_source_in
			-- 	OR funding_source_in IS NULL
			-- )
		ORDER BY
			ofsc.sort_priority desc,
			ofsc.sort_date desc,
			ofsc.id desc
	LOOP
		--
		-- Determine how much money the old fund got from this funding source,
		-- denominated in the currency types of the source and of the fund.
		-- This result may reflect transfers from previous iterations.
		--
		SELECT
			COALESCE( sum( amount ), 0 ),
			COALESCE( sum( amount )
				* acq.exchange_ratio( source.currency_type, old_fund_currency ), 0 )
		INTO
			orig_allocated_amt,     -- in currency of the source
			allocated_amt           -- in currency of the old fund
		FROM
			acq.fund_allocation
		WHERE
			fund = old_fund
			and funding_source = source.funding_source;
		--	
		-- Determine how much to transfer from this credit, in the currency
		-- of the fund.   Begin with the amount remaining to be attributed:
		--
		curr_old_amt := old_remaining;
		--
		-- Can't attribute more than was allocated from the fund:
		--
		IF curr_old_amt > allocated_amt THEN
			curr_old_amt := allocated_amt;
		END IF;
		--
		-- Can't attribute more than the amount of the current credit:
		--
		IF curr_old_amt > source.converted_amt THEN
			curr_old_amt := source.converted_amt;
		END IF;
		--
		curr_old_amt := trunc( curr_old_amt, 2 );
		--
		old_remaining := old_remaining - curr_old_amt;
		--
		-- Determine the amount to be deducted, if any,
		-- from the old allocation.
		--
		IF old_remaining > 0 THEN
			--
			-- In this case we're using the whole allocation, so use that
			-- amount directly instead of applying a currency translation
			-- and thereby inviting round-off errors.
			--
			source_deduction := - orig_allocated_amt;
		ELSE 
			source_deduction := trunc(
				( - curr_old_amt ) *
					acq.exchange_ratio( old_fund_currency, source.currency_type ),
				2 );
		END IF;
		--
		IF source_deduction <> 0 THEN
			--
			-- Insert negative allocation for old fund in fund_allocation,
			-- converted into the currency of the funding source
			--
			INSERT INTO acq.fund_allocation (
				funding_source,
				fund,
				amount,
				allocator,
				note
			) VALUES (
				source.funding_source,
				old_fund,
				source_deduction,
				user_id,
				'Transfer to fund ' || new_fund
			);
		END IF;
		--
		IF new_fund IS NOT NULL THEN
			--
			-- Determine how much to add to the new fund, in
			-- its currency, and how much remains to be added:
			--
			IF same_currency THEN
				curr_new_amt := curr_old_amt;
			ELSE
				IF old_remaining = 0 THEN
					--
					-- This is the last iteration, so nothing should be left
					--
					curr_new_amt := new_remaining;
					new_remaining := 0;
				ELSE
					curr_new_amt := trunc( curr_old_amt * currency_ratio, 2 );
					new_remaining := new_remaining - curr_new_amt;
				END IF;
			END IF;
			--
			-- Determine how much to add, if any,
			-- to the new fund's allocation.
			--
			IF old_remaining > 0 THEN
				--
				-- In this case we're using the whole allocation, so use that amount
				-- amount directly instead of applying a currency translation and
				-- thereby inviting round-off errors.
				--
				source_addition := orig_allocated_amt;
			ELSIF source.currency_type = old_fund_currency THEN
				--
				-- In this case we don't need a round trip currency translation,
				-- thereby inviting round-off errors:
				--
				source_addition := curr_old_amt;
			ELSE 
				source_addition := trunc(
					curr_new_amt *
						acq.exchange_ratio( new_fund_currency, source.currency_type ),
					2 );
			END IF;
			--
			IF source_addition <> 0 THEN
				--
				-- Insert positive allocation for new fund in fund_allocation,
				-- converted to the currency of the founding source
				--
				INSERT INTO acq.fund_allocation (
					funding_source,
					fund,
					amount,
					allocator,
					note
				) VALUES (
					source.funding_source,
					new_fund,
					source_addition,
					user_id,
					'Transfer from fund ' || old_fund
				);
			END IF;
		END IF;
		--
		IF trunc( curr_old_amt, 2 ) <> 0
		OR trunc( curr_new_amt, 2 ) <> 0 THEN
			--
			-- Insert row in fund_transfer, using amounts in the currency of the funds
			--
			INSERT INTO acq.fund_transfer (
				src_fund,
				src_amount,
				dest_fund,
				dest_amount,
				transfer_user,
				note,
				funding_source_credit
			) VALUES (
				old_fund,
				trunc( curr_old_amt, 2 ),
				new_fund,
				trunc( curr_new_amt, 2 ),
				user_id,
				xfer_note,
				source.id
			);
		END IF;
		--
		if old_remaining <= 0 THEN
			EXIT;                   -- Nothing more to be transferred
		END IF;
	END LOOP;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION acq.propagate_funds_by_org_unit(
	old_year INTEGER,
	user_id INTEGER,
	org_unit_id INTEGER
) RETURNS VOID AS $$
DECLARE
--
new_id      INT;
old_fund    RECORD;
org_found   BOOLEAN;
--
BEGIN
	--
	-- Sanity checks
	--
	IF old_year IS NULL THEN
		RAISE EXCEPTION 'Input year argument is NULL';
	ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
		RAISE EXCEPTION 'Input year is out of range';
	END IF;
	--
	IF user_id IS NULL THEN
		RAISE EXCEPTION 'Input user id argument is NULL';
	END IF;
	--
	IF org_unit_id IS NULL THEN
		RAISE EXCEPTION 'Org unit id argument is NULL';
	ELSE
		SELECT TRUE INTO org_found
		FROM actor.org_unit
		WHERE id = org_unit_id;
		--
		IF org_found IS NULL THEN
			RAISE EXCEPTION 'Org unit id is invalid';
		END IF;
	END IF;
	--
	-- Loop over the applicable funds
	--
	FOR old_fund in SELECT * FROM acq.fund
	WHERE
		year = old_year
		AND propagate
		AND org = org_unit_id
	LOOP
		BEGIN
			INSERT INTO acq.fund (
				org,
				name,
				year,
				currency_type,
				code,
				rollover,
				propagate,
				balance_warning_percent,
				balance_stop_percent
			) VALUES (
				old_fund.org,
				old_fund.name,
				old_year + 1,
				old_fund.currency_type,
				old_fund.code,
				old_fund.rollover,
				true,
				old_fund.balance_warning_percent,
				old_fund.balance_stop_percent
			)
			RETURNING id INTO new_id;
		EXCEPTION
			WHEN unique_violation THEN
				--RAISE NOTICE 'Fund % already propagated', old_fund.id;
				CONTINUE;
		END;
		--RAISE NOTICE 'Propagating fund % to fund %',
		--	old_fund.code, new_id;
	END LOOP;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION acq.propagate_funds_by_org_tree(
	old_year INTEGER,
	user_id INTEGER,
	org_unit_id INTEGER
) RETURNS VOID AS $$
DECLARE
--
new_id      INT;
old_fund    RECORD;
org_found   BOOLEAN;
--
BEGIN
	--
	-- Sanity checks
	--
	IF old_year IS NULL THEN
		RAISE EXCEPTION 'Input year argument is NULL';
	ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
		RAISE EXCEPTION 'Input year is out of range';
	END IF;
	--
	IF user_id IS NULL THEN
		RAISE EXCEPTION 'Input user id argument is NULL';
	END IF;
	--
	IF org_unit_id IS NULL THEN
		RAISE EXCEPTION 'Org unit id argument is NULL';
	ELSE
		SELECT TRUE INTO org_found
		FROM actor.org_unit
		WHERE id = org_unit_id;
		--
		IF org_found IS NULL THEN
			RAISE EXCEPTION 'Org unit id is invalid';
		END IF;
	END IF;
	--
	-- Loop over the applicable funds
	--
	FOR old_fund in SELECT * FROM acq.fund
	WHERE
		year = old_year
		AND propagate
		AND org in (
			SELECT id FROM actor.org_unit_descendants( org_unit_id )
		)
	LOOP
		BEGIN
			INSERT INTO acq.fund (
				org,
				name,
				year,
				currency_type,
				code,
				rollover,
				propagate,
				balance_warning_percent,
				balance_stop_percent
			) VALUES (
				old_fund.org,
				old_fund.name,
				old_year + 1,
				old_fund.currency_type,
				old_fund.code,
				old_fund.rollover,
				true,
				old_fund.balance_warning_percent,
				old_fund.balance_stop_percent
			)
			RETURNING id INTO new_id;
		EXCEPTION
			WHEN unique_violation THEN
				--RAISE NOTICE 'Fund % already propagated', old_fund.id;
				CONTINUE;
		END;
		--RAISE NOTICE 'Propagating fund % to fund %',
		--	old_fund.code, new_id;
	END LOOP;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION acq.rollover_funds_by_org_unit(
	old_year INTEGER,
	user_id INTEGER,
	org_unit_id INTEGER
) RETURNS VOID AS $$
DECLARE
--
new_fund    INT;
new_year    INT := old_year + 1;
org_found   BOOL;
xfer_amount NUMERIC;
roll_fund   RECORD;
deb         RECORD;
detail      RECORD;
--
BEGIN
	--
	-- Sanity checks
	--
	IF old_year IS NULL THEN
		RAISE EXCEPTION 'Input year argument is NULL';
    ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
        RAISE EXCEPTION 'Input year is out of range';
	END IF;
	--
	IF user_id IS NULL THEN
		RAISE EXCEPTION 'Input user id argument is NULL';
	END IF;
	--
	IF org_unit_id IS NULL THEN
		RAISE EXCEPTION 'Org unit id argument is NULL';
	ELSE
		--
		-- Validate the org unit
		--
		SELECT TRUE
		INTO org_found
		FROM actor.org_unit
		WHERE id = org_unit_id;
		--
		IF org_found IS NULL THEN
			RAISE EXCEPTION 'Org unit id % is invalid', org_unit_id;
		END IF;
	END IF;
	--
	-- Loop over the propagable funds to identify the details
	-- from the old fund plus the id of the new one, if it exists.
	--
	FOR roll_fund in
	SELECT
	    oldf.id AS old_fund,
	    oldf.org,
	    oldf.name,
	    oldf.currency_type,
	    oldf.code,
		oldf.rollover,
	    newf.id AS new_fund_id
	FROM
    	acq.fund AS oldf
    	LEFT JOIN acq.fund AS newf
        	ON ( oldf.code = newf.code )
	WHERE
		    oldf.org = org_unit_id
 		and oldf.year = old_year
		and oldf.propagate
        and newf.year = new_year
	LOOP
		--RAISE NOTICE 'Processing fund %', roll_fund.old_fund;
		--
		IF roll_fund.new_fund_id IS NULL THEN
			--
			-- The old fund hasn't been propagated yet.  Propagate it now.
			--
			INSERT INTO acq.fund (
				org,
				name,
				year,
				currency_type,
				code,
				rollover,
				propagate,
				balance_warning_percent,
				balance_stop_percent
			) VALUES (
				roll_fund.org,
				roll_fund.name,
				new_year,
				roll_fund.currency_type,
				roll_fund.code,
				true,
				true,
				roll_fund.balance_warning_percent,
				roll_fund.balance_stop_percent
			)
			RETURNING id INTO new_fund;
		ELSE
			new_fund = roll_fund.new_fund_id;
		END IF;
		--
		-- Determine the amount to transfer
		--
		SELECT amount
		INTO xfer_amount
		FROM acq.fund_spent_balance
		WHERE fund = roll_fund.old_fund;
		--
		IF xfer_amount <> 0 THEN
			IF roll_fund.rollover THEN
				--
				-- Transfer balance from old fund to new
				--
				--RAISE NOTICE 'Transferring % from fund % to %', xfer_amount, roll_fund.old_fund, new_fund;
				--
				PERFORM acq.transfer_fund(
					roll_fund.old_fund,
					xfer_amount,
					new_fund,
					xfer_amount,
					user_id,
					'Rollover'
				);
			ELSE
				--
				-- Transfer balance from old fund to the void
				--
				-- RAISE NOTICE 'Transferring % from fund % to the void', xfer_amount, roll_fund.old_fund;
				--
				PERFORM acq.transfer_fund(
					roll_fund.old_fund,
					xfer_amount,
					NULL,
					NULL,
					user_id,
					'Rollover'
				);
			END IF;
		END IF;
		--
		IF roll_fund.rollover THEN
			--
			-- Move any lineitems from the old fund to the new one
			-- where the associated debit is an encumbrance.
			--
			-- Any other tables tying expenditure details to funds should
			-- receive similar treatment.  At this writing there are none.
			--
			UPDATE acq.lineitem_detail
			SET fund = new_fund
			WHERE
    			fund = roll_fund.old_fund -- this condition may be redundant
    			AND fund_debit in
    			(
        			SELECT id
        			FROM acq.fund_debit
        			WHERE
            			fund = roll_fund.old_fund
            			AND encumbrance
    			);
			--
			-- Move encumbrance debits from the old fund to the new fund
			--
			UPDATE acq.fund_debit
			SET fund = new_fund
			wHERE
				fund = roll_fund.old_fund
				AND encumbrance;
		END IF;
		--
		-- Mark old fund as inactive, now that we've closed it
		--
		UPDATE acq.fund
		SET active = FALSE
		WHERE id = roll_fund.old_fund;
	END LOOP;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION acq.rollover_funds_by_org_tree(
	old_year INTEGER,
	user_id INTEGER,
	org_unit_id INTEGER
) RETURNS VOID AS $$
DECLARE
--
new_fund    INT;
new_year    INT := old_year + 1;
org_found   BOOL;
xfer_amount NUMERIC;
roll_fund   RECORD;
deb         RECORD;
detail      RECORD;
--
BEGIN
	--
	-- Sanity checks
	--
	IF old_year IS NULL THEN
		RAISE EXCEPTION 'Input year argument is NULL';
    ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
        RAISE EXCEPTION 'Input year is out of range';
	END IF;
	--
	IF user_id IS NULL THEN
		RAISE EXCEPTION 'Input user id argument is NULL';
	END IF;
	--
	IF org_unit_id IS NULL THEN
		RAISE EXCEPTION 'Org unit id argument is NULL';
	ELSE
		--
		-- Validate the org unit
		--
		SELECT TRUE
		INTO org_found
		FROM actor.org_unit
		WHERE id = org_unit_id;
		--
		IF org_found IS NULL THEN
			RAISE EXCEPTION 'Org unit id % is invalid', org_unit_id;
		END IF;
	END IF;
	--
	-- Loop over the propagable funds to identify the details
	-- from the old fund plus the id of the new one, if it exists.
	--
	FOR roll_fund in
	SELECT
	    oldf.id AS old_fund,
	    oldf.org,
	    oldf.name,
	    oldf.currency_type,
	    oldf.code,
		oldf.rollover,
	    newf.id AS new_fund_id
	FROM
    	acq.fund AS oldf
    	LEFT JOIN acq.fund AS newf
        	ON ( oldf.code = newf.code )
	WHERE
 		    oldf.year = old_year
		AND oldf.propagate
        AND newf.year = new_year
		AND oldf.org in (
			SELECT id FROM actor.org_unit_descendants( org_unit_id )
		)
	LOOP
		--RAISE NOTICE 'Processing fund %', roll_fund.old_fund;
		--
		IF roll_fund.new_fund_id IS NULL THEN
			--
			-- The old fund hasn't been propagated yet.  Propagate it now.
			--
			INSERT INTO acq.fund (
				org,
				name,
				year,
				currency_type,
				code,
				rollover,
				propagate,
				balance_warning_percent,
				balance_stop_percent
			) VALUES (
				roll_fund.org,
				roll_fund.name,
				new_year,
				roll_fund.currency_type,
				roll_fund.code,
				true,
				true,
				roll_fund.balance_warning_percent,
				roll_fund.balance_stop_percent
			)
			RETURNING id INTO new_fund;
		ELSE
			new_fund = roll_fund.new_fund_id;
		END IF;
		--
		-- Determine the amount to transfer
		--
		SELECT amount
		INTO xfer_amount
		FROM acq.fund_spent_balance
		WHERE fund = roll_fund.old_fund;
		--
		IF xfer_amount <> 0 THEN
			IF roll_fund.rollover THEN
				--
				-- Transfer balance from old fund to new
				--
				--RAISE NOTICE 'Transferring % from fund % to %', xfer_amount, roll_fund.old_fund, new_fund;
				--
				PERFORM acq.transfer_fund(
					roll_fund.old_fund,
					xfer_amount,
					new_fund,
					xfer_amount,
					user_id,
					'Rollover'
				);
			ELSE
				--
				-- Transfer balance from old fund to the void
				--
				-- RAISE NOTICE 'Transferring % from fund % to the void', xfer_amount, roll_fund.old_fund;
				--
				PERFORM acq.transfer_fund(
					roll_fund.old_fund,
					xfer_amount,
					NULL,
					NULL,
					user_id,
					'Rollover'
				);
			END IF;
		END IF;
		--
		IF roll_fund.rollover THEN
			--
			-- Move any lineitems from the old fund to the new one
			-- where the associated debit is an encumbrance.
			--
			-- Any other tables tying expenditure details to funds should
			-- receive similar treatment.  At this writing there are none.
			--
			UPDATE acq.lineitem_detail
			SET fund = new_fund
			WHERE
    			fund = roll_fund.old_fund -- this condition may be redundant
    			AND fund_debit in
    			(
        			SELECT id
        			FROM acq.fund_debit
        			WHERE
            			fund = roll_fund.old_fund
            			AND encumbrance
    			);
			--
			-- Move encumbrance debits from the old fund to the new fund
			--
			UPDATE acq.fund_debit
			SET fund = new_fund
			wHERE
				fund = roll_fund.old_fund
				AND encumbrance;
		END IF;
		--
		-- Mark old fund as inactive, now that we've closed it
		--
		UPDATE acq.fund
		SET active = FALSE
		WHERE id = roll_fund.old_fund;
	END LOOP;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION public.remove_commas( TEXT ) RETURNS TEXT AS $$
    SELECT regexp_replace($1, ',', '', 'g');
$$ LANGUAGE SQL STRICT IMMUTABLE;

CREATE OR REPLACE FUNCTION public.remove_whitespace( TEXT ) RETURNS TEXT AS $$
    SELECT regexp_replace(normalize_space($1), E'\\s+', '', 'g');
$$ LANGUAGE SQL STRICT IMMUTABLE;

CREATE TABLE acq.distribution_formula_application (
    id BIGSERIAL PRIMARY KEY,
    creator INT NOT NULL REFERENCES actor.usr(id) DEFERRABLE INITIALLY DEFERRED,
    create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    formula INT NOT NULL
        REFERENCES acq.distribution_formula(id) DEFERRABLE INITIALLY DEFERRED,
    lineitem INT NOT NULL
        REFERENCES acq.lineitem( id )
		ON DELETE CASCADE
		DEFERRABLE INITIALLY DEFERRED
);

CREATE INDEX acqdfa_df_idx
    ON acq.distribution_formula_application(formula);
CREATE INDEX acqdfa_li_idx
    ON acq.distribution_formula_application(lineitem);
CREATE INDEX acqdfa_creator_idx
    ON acq.distribution_formula_application(creator);

CREATE TABLE acq.user_request_type (
    id      SERIAL  PRIMARY KEY,
    label   TEXT    NOT NULL UNIQUE -- i18n-ize
);

INSERT INTO acq.user_request_type (id,label) VALUES (1, oils_i18n_gettext('1', 'Books', 'aurt', 'label'));
INSERT INTO acq.user_request_type (id,label) VALUES (2, oils_i18n_gettext('2', 'Journal/Magazine & Newspaper Articles', 'aurt', 'label'));
INSERT INTO acq.user_request_type (id,label) VALUES (3, oils_i18n_gettext('3', 'Audiobooks', 'aurt', 'label'));
INSERT INTO acq.user_request_type (id,label) VALUES (4, oils_i18n_gettext('4', 'Music', 'aurt', 'label'));
INSERT INTO acq.user_request_type (id,label) VALUES (5, oils_i18n_gettext('5', 'DVDs', 'aurt', 'label'));

SELECT SETVAL('acq.user_request_type_id_seq'::TEXT, 6);

CREATE TABLE acq.cancel_reason (
	id            SERIAL            PRIMARY KEY,
	org_unit      INTEGER           NOT NULL REFERENCES actor.org_unit( id )
	                                DEFERRABLE INITIALLY DEFERRED,
	label         TEXT              NOT NULL,
	description   TEXT              NOT NULL,
	keep_debits   BOOL              NOT NULL DEFAULT FALSE,
	CONSTRAINT acq_cancel_reason_one_per_org_unit UNIQUE( org_unit, label )
);

-- Reserve ids 1-999 for stock reasons
-- Reserve ids 1000-1999 for EDI reasons
-- 2000+ are available for staff to create

SELECT SETVAL('acq.cancel_reason_id_seq'::TEXT, 2000);

CREATE TABLE acq.user_request (
    id                  SERIAL  PRIMARY KEY,
    usr                 INT     NOT NULL REFERENCES actor.usr (id), -- requesting user
    hold                BOOL    NOT NULL DEFAULT TRUE,

    pickup_lib          INT     NOT NULL REFERENCES actor.org_unit (id), -- pickup lib
    holdable_formats    TEXT,           -- nullable, for use in hold creation
    phone_notify        TEXT,
    email_notify        BOOL    NOT NULL DEFAULT TRUE,
    lineitem            INT     REFERENCES acq.lineitem (id) ON DELETE CASCADE,
    eg_bib              BIGINT  REFERENCES biblio.record_entry (id) ON DELETE CASCADE,
    request_date        TIMESTAMPTZ NOT NULL DEFAULT NOW(), -- when they requested it
    need_before         TIMESTAMPTZ,    -- don't create holds after this
    max_fee             TEXT,

    request_type        INT     NOT NULL REFERENCES acq.user_request_type (id), 
    isxn                TEXT,
    title               TEXT,
    volume              TEXT,
    author              TEXT,
    article_title       TEXT,
    article_pages       TEXT,
    publisher           TEXT,
    location            TEXT,
    pubdate             TEXT,
    mentioned           TEXT,
    other_info          TEXT,
	cancel_reason       INT              REFERENCES acq.cancel_reason( id )
	                                     DEFERRABLE INITIALLY DEFERRED
);

CREATE TABLE acq.lineitem_alert_text (
	id               SERIAL         PRIMARY KEY,
	code             TEXT           NOT NULL,
	description      TEXT,
	owning_lib       INT            NOT NULL
	                                REFERENCES actor.org_unit(id)
	                                DEFERRABLE INITIALLY DEFERRED,
	CONSTRAINT alert_one_code_per_org UNIQUE (code, owning_lib)
);

ALTER TABLE acq.lineitem_note
	ADD COLUMN alert_text    INT     REFERENCES acq.lineitem_alert_text(id)
	                                 DEFERRABLE INITIALLY DEFERRED;

-- add ON DELETE CASCADE clause

ALTER TABLE acq.lineitem_note
	DROP CONSTRAINT lineitem_note_lineitem_fkey;

ALTER TABLE acq.lineitem_note
	ADD FOREIGN KEY (lineitem) REFERENCES acq.lineitem( id )
		ON DELETE CASCADE
		DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE acq.lineitem_note
	ADD COLUMN vendor_public BOOLEAN NOT NULL DEFAULT FALSE;

CREATE TABLE acq.invoice_method (
    code    TEXT    PRIMARY KEY,
    name    TEXT    NOT NULL -- i18n-ize
);
INSERT INTO acq.invoice_method (code,name) VALUES ('EDI',oils_i18n_gettext('EDI', 'EDI', 'acqim', 'name'));
INSERT INTO acq.invoice_method (code,name) VALUES ('PPR',oils_i18n_gettext('PPR', 'Paper', 'acqit', 'name'));

CREATE TABLE acq.invoice_payment_method (
	code      TEXT     PRIMARY KEY,
	name      TEXT     NOT NULL
);

CREATE TABLE acq.invoice (
    id             SERIAL      PRIMARY KEY,
    receiver       INT         NOT NULL REFERENCES actor.org_unit (id),
    provider       INT         NOT NULL REFERENCES acq.provider (id),
    shipper        INT         NOT NULL REFERENCES acq.provider (id),
    recv_date      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    recv_method    TEXT        NOT NULL REFERENCES acq.invoice_method (code) DEFAULT 'EDI',
    inv_type       TEXT,       -- A "type" field is desired, but no idea what goes here
    inv_ident      TEXT        NOT NULL, -- vendor-supplied invoice id/number
	payment_auth   TEXT,
	payment_method TEXT        REFERENCES acq.invoice_payment_method (code)
	                           DEFERRABLE INITIALLY DEFERRED,
	note           TEXT,
    complete       BOOL        NOT NULL DEFAULT FALSE,
    CONSTRAINT inv_ident_once_per_provider UNIQUE(provider, inv_ident)
);

CREATE TABLE acq.invoice_entry (
    id              SERIAL      PRIMARY KEY,
    invoice         INT         NOT NULL REFERENCES acq.invoice (id) ON DELETE CASCADE,
    purchase_order  INT         REFERENCES acq.purchase_order (id) ON UPDATE CASCADE ON DELETE SET NULL,
    lineitem        INT         REFERENCES acq.lineitem (id) ON UPDATE CASCADE ON DELETE SET NULL,
    inv_item_count  INT         NOT NULL, -- How many acqlids did they say they sent
    phys_item_count INT, -- and how many did staff count
    note            TEXT,
    billed_per_item BOOL,
    cost_billed     NUMERIC(8,2),
    actual_cost     NUMERIC(8,2),
	amount_paid     NUMERIC (8,2)
);

CREATE TABLE acq.invoice_item_type (
    code    TEXT    PRIMARY KEY,
    name    TEXT    NOT NULL, -- i18n-ize
	prorate BOOL    NOT NULL DEFAULT FALSE
);

INSERT INTO acq.invoice_item_type (code,name) VALUES ('TAX',oils_i18n_gettext('TAX', 'Tax', 'aiit', 'name'));
INSERT INTO acq.invoice_item_type (code,name) VALUES ('PRO',oils_i18n_gettext('PRO', 'Processing Fee', 'aiit', 'name'));
INSERT INTO acq.invoice_item_type (code,name) VALUES ('SHP',oils_i18n_gettext('SHP', 'Shipping Charge', 'aiit', 'name'));
INSERT INTO acq.invoice_item_type (code,name) VALUES ('HND',oils_i18n_gettext('HND', 'Handling Charge', 'aiit', 'name'));
INSERT INTO acq.invoice_item_type (code,name) VALUES ('ITM',oils_i18n_gettext('ITM', 'Non-library Item', 'aiit', 'name'));
INSERT INTO acq.invoice_item_type (code,name) VALUES ('SUB',oils_i18n_gettext('SUB', 'Serial Subscription', 'aiit', 'name'));

CREATE TABLE acq.po_item (
	id              SERIAL      PRIMARY KEY,
	purchase_order  INT         REFERENCES acq.purchase_order (id)
	                            ON UPDATE CASCADE ON DELETE SET NULL
	                            DEFERRABLE INITIALLY DEFERRED,
	fund_debit      INT         REFERENCES acq.fund_debit (id)
	                            DEFERRABLE INITIALLY DEFERRED,
	inv_item_type   TEXT        NOT NULL
	                            REFERENCES acq.invoice_item_type (code)
	                            DEFERRABLE INITIALLY DEFERRED,
	title           TEXT,
	author          TEXT,
	note            TEXT,
	estimated_cost  NUMERIC(8,2),
	fund            INT         REFERENCES acq.fund (id)
	                            DEFERRABLE INITIALLY DEFERRED,
	target          BIGINT
);

CREATE TABLE acq.invoice_item ( -- for invoice-only debits: taxes/fees/non-bib items/etc
    id              SERIAL      PRIMARY KEY,
    invoice         INT         NOT NULL REFERENCES acq.invoice (id) ON UPDATE CASCADE ON DELETE CASCADE,
    purchase_order  INT         REFERENCES acq.purchase_order (id) ON UPDATE CASCADE ON DELETE SET NULL,
    fund_debit      INT         REFERENCES acq.fund_debit (id),
    inv_item_type   TEXT        NOT NULL REFERENCES acq.invoice_item_type (code),
    title           TEXT,
    author          TEXT,
    note            TEXT,
    cost_billed     NUMERIC(8,2),
    actual_cost     NUMERIC(8,2),
    fund            INT         REFERENCES acq.fund (id)
                                DEFERRABLE INITIALLY DEFERRED,
    amount_paid     NUMERIC (8,2),
    po_item         INT         REFERENCES acq.po_item (id)
                                DEFERRABLE INITIALLY DEFERRED,
    target          BIGINT
);

CREATE TABLE acq.edi_message (
    id               SERIAL          PRIMARY KEY,
    account          INTEGER         REFERENCES acq.edi_account(id)
                                     DEFERRABLE INITIALLY DEFERRED,
    remote_file      TEXT,
    create_time      TIMESTAMPTZ     NOT NULL DEFAULT now(),
    translate_time   TIMESTAMPTZ,
    process_time     TIMESTAMPTZ,
    error_time       TIMESTAMPTZ,
    status           TEXT            NOT NULL DEFAULT 'new'
                                     CONSTRAINT status_value CHECK
                                     ( status IN (
                                        'new',          -- needs to be translated
                                        'translated',   -- needs to be processed
                                        'trans_error',  -- error in translation step
                                        'processed',    -- needs to have remote_file deleted
                                        'proc_error',   -- error in processing step
                                        'delete_error', -- error in deletion
                                        'retry',        -- need to retry
                                        'complete'      -- done
                                     )),
    edi              TEXT,
    jedi             TEXT,
    error            TEXT,
    purchase_order   INT             REFERENCES acq.purchase_order
                                     DEFERRABLE INITIALLY DEFERRED,
    message_type     TEXT            NOT NULL CONSTRAINT valid_message_type
                                     CHECK ( message_type IN (
                                        'ORDERS',
                                        'ORDRSP',
                                        'INVOIC',
                                        'OSTENQ',
                                        'OSTRPT'
                                     ))
);

ALTER TABLE actor.org_address ADD COLUMN san TEXT;

ALTER TABLE acq.provider_address
	ADD COLUMN fax_phone TEXT;

ALTER TABLE acq.provider_contact_address
	ADD COLUMN fax_phone TEXT;

CREATE TABLE acq.provider_note (
    id      SERIAL              PRIMARY KEY,
    provider    INT             NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
    creator     INT             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
    editor      INT             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
    create_time TIMESTAMP WITH TIME ZONE    NOT NULL DEFAULT NOW(),
    edit_time   TIMESTAMP WITH TIME ZONE    NOT NULL DEFAULT NOW(),
    value       TEXT            NOT NULL
);
CREATE INDEX acq_pro_note_pro_idx      ON acq.provider_note ( provider );
CREATE INDEX acq_pro_note_creator_idx  ON acq.provider_note ( creator );
CREATE INDEX acq_pro_note_editor_idx   ON acq.provider_note ( editor );

-- For each fund: the total allocation from all sources, in the
-- currency of the fund (or 0 if there are no allocations)

CREATE VIEW acq.all_fund_allocation_total AS
SELECT
    f.id AS fund,
    COALESCE( SUM( a.amount * acq.exchange_ratio(
        s.currency_type, f.currency_type))::numeric(100,2), 0 )
    AS amount
FROM
    acq.fund f
        LEFT JOIN acq.fund_allocation a
            ON a.fund = f.id
        LEFT JOIN acq.funding_source s
            ON a.funding_source = s.id
GROUP BY
    f.id;

-- For every fund: the total encumbrances (or 0 if none),
-- in the currency of the fund.

CREATE VIEW acq.all_fund_encumbrance_total AS
SELECT
	f.id AS fund,
	COALESCE( encumb.amount, 0 ) AS amount
FROM
	acq.fund AS f
		LEFT JOIN (
			SELECT
				fund,
				sum( amount ) AS amount
			FROM
				acq.fund_debit
			WHERE
				encumbrance
			GROUP BY fund
		) AS encumb
			ON f.id = encumb.fund;

-- For every fund: the total spent (or 0 if none),
-- in the currency of the fund.

CREATE VIEW acq.all_fund_spent_total AS
SELECT
    f.id AS fund,
    COALESCE( spent.amount, 0 ) AS amount
FROM
    acq.fund AS f
        LEFT JOIN (
            SELECT
                fund,
                sum( amount ) AS amount
            FROM
                acq.fund_debit
            WHERE
                NOT encumbrance
            GROUP BY fund
        ) AS spent
            ON f.id = spent.fund;

-- For each fund: the amount not yet spent, in the currency
-- of the fund.  May include encumbrances.

CREATE VIEW acq.all_fund_spent_balance AS
SELECT
	c.fund,
	c.amount - d.amount AS amount
FROM acq.all_fund_allocation_total c
    LEFT JOIN acq.all_fund_spent_total d USING (fund);

-- For each fund: the amount neither spent nor encumbered,
-- in the currency of the fund

CREATE VIEW acq.all_fund_combined_balance AS
SELECT
     a.fund,
     a.amount - COALESCE( c.amount, 0 ) AS amount
FROM
     acq.all_fund_allocation_total a
        LEFT OUTER JOIN (
            SELECT
                fund,
                SUM( amount ) AS amount
            FROM
                acq.fund_debit
            GROUP BY
                fund
        ) AS c USING ( fund );

CREATE OR REPLACE FUNCTION actor.usr_merge( src_usr INT, dest_usr INT, del_addrs BOOLEAN, del_cards BOOLEAN, deactivate_cards BOOLEAN ) RETURNS VOID AS $$
DECLARE
	suffix TEXT;
	bucket_row RECORD;
	picklist_row RECORD;
	queue_row RECORD;
	folder_row RECORD;
BEGIN

    -- do some initial cleanup 
    UPDATE actor.usr SET card = NULL WHERE id = src_usr;
    UPDATE actor.usr SET mailing_address = NULL WHERE id = src_usr;
    UPDATE actor.usr SET billing_address = NULL WHERE id = src_usr;

    -- actor.*
    IF del_cards THEN
        DELETE FROM actor.card where usr = src_usr;
    ELSE
        IF deactivate_cards THEN
            UPDATE actor.card SET active = 'f' WHERE usr = src_usr;
        END IF;
        UPDATE actor.card SET usr = dest_usr WHERE usr = src_usr;
    END IF;


    IF del_addrs THEN
        DELETE FROM actor.usr_address WHERE usr = src_usr;
    ELSE
        UPDATE actor.usr_address SET usr = dest_usr WHERE usr = src_usr;
    END IF;

    UPDATE actor.usr_note SET usr = dest_usr WHERE usr = src_usr;
    -- dupes are technically OK in actor.usr_standing_penalty, should manually delete them...
    UPDATE actor.usr_standing_penalty SET usr = dest_usr WHERE usr = src_usr;
    PERFORM actor.usr_merge_rows('actor.usr_org_unit_opt_in', 'usr', src_usr, dest_usr);
    PERFORM actor.usr_merge_rows('actor.usr_setting', 'usr', src_usr, dest_usr);

    -- permission.*
    PERFORM actor.usr_merge_rows('permission.usr_perm_map', 'usr', src_usr, dest_usr);
    PERFORM actor.usr_merge_rows('permission.usr_object_perm_map', 'usr', src_usr, dest_usr);
    PERFORM actor.usr_merge_rows('permission.usr_grp_map', 'usr', src_usr, dest_usr);
    PERFORM actor.usr_merge_rows('permission.usr_work_ou_map', 'usr', src_usr, dest_usr);


    -- container.*
	
	-- For each *_bucket table: transfer every bucket belonging to src_usr
	-- into the custody of dest_usr.
	--
	-- In order to avoid colliding with an existing bucket owned by
	-- the destination user, append the source user's id (in parenthesese)
	-- to the name.  If you still get a collision, add successive
	-- spaces to the name and keep trying until you succeed.
	--
	FOR bucket_row in
		SELECT id, name
		FROM   container.biblio_record_entry_bucket
		WHERE  owner = src_usr
	LOOP
		suffix := ' (' || src_usr || ')';
		LOOP
			BEGIN
				UPDATE  container.biblio_record_entry_bucket
				SET     owner = dest_usr, name = name || suffix
				WHERE   id = bucket_row.id;
			EXCEPTION WHEN unique_violation THEN
				suffix := suffix || ' ';
				CONTINUE;
			END;
			EXIT;
		END LOOP;
	END LOOP;

	FOR bucket_row in
		SELECT id, name
		FROM   container.call_number_bucket
		WHERE  owner = src_usr
	LOOP
		suffix := ' (' || src_usr || ')';
		LOOP
			BEGIN
				UPDATE  container.call_number_bucket
				SET     owner = dest_usr, name = name || suffix
				WHERE   id = bucket_row.id;
			EXCEPTION WHEN unique_violation THEN
				suffix := suffix || ' ';
				CONTINUE;
			END;
			EXIT;
		END LOOP;
	END LOOP;

	FOR bucket_row in
		SELECT id, name
		FROM   container.copy_bucket
		WHERE  owner = src_usr
	LOOP
		suffix := ' (' || src_usr || ')';
		LOOP
			BEGIN
				UPDATE  container.copy_bucket
				SET     owner = dest_usr, name = name || suffix
				WHERE   id = bucket_row.id;
			EXCEPTION WHEN unique_violation THEN
				suffix := suffix || ' ';
				CONTINUE;
			END;
			EXIT;
		END LOOP;
	END LOOP;

	FOR bucket_row in
		SELECT id, name
		FROM   container.user_bucket
		WHERE  owner = src_usr
	LOOP
		suffix := ' (' || src_usr || ')';
		LOOP
			BEGIN
				UPDATE  container.user_bucket
				SET     owner = dest_usr, name = name || suffix
				WHERE   id = bucket_row.id;
			EXCEPTION WHEN unique_violation THEN
				suffix := suffix || ' ';
				CONTINUE;
			END;
			EXIT;
		END LOOP;
	END LOOP;

	UPDATE container.user_bucket_item SET target_user = dest_usr WHERE target_user = src_usr;

    -- vandelay.*
	-- transfer queues the same way we transfer buckets (see above)
	FOR queue_row in
		SELECT id, name
		FROM   vandelay.queue
		WHERE  owner = src_usr
	LOOP
		suffix := ' (' || src_usr || ')';
		LOOP
			BEGIN
				UPDATE  vandelay.queue
				SET     owner = dest_usr, name = name || suffix
				WHERE   id = queue_row.id;
			EXCEPTION WHEN unique_violation THEN
				suffix := suffix || ' ';
				CONTINUE;
			END;
			EXIT;
		END LOOP;
	END LOOP;

    -- money.*
    PERFORM actor.usr_merge_rows('money.collections_tracker', 'usr', src_usr, dest_usr);
    PERFORM actor.usr_merge_rows('money.collections_tracker', 'collector', src_usr, dest_usr);
    UPDATE money.billable_xact SET usr = dest_usr WHERE usr = src_usr;
    UPDATE money.billing SET voider = dest_usr WHERE voider = src_usr;
    UPDATE money.bnm_payment SET accepting_usr = dest_usr WHERE accepting_usr = src_usr;

    -- action.*
    UPDATE action.circulation SET usr = dest_usr WHERE usr = src_usr;
    UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
    UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;

    UPDATE action.hold_request SET usr = dest_usr WHERE usr = src_usr;
    UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
    UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
    UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;

    UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
    UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
    UPDATE action.non_cataloged_circulation SET patron = dest_usr WHERE patron = src_usr;
    UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
    UPDATE action.survey_response SET usr = dest_usr WHERE usr = src_usr;

    -- acq.*
    UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
	UPDATE acq.fund_transfer SET transfer_user = dest_usr WHERE transfer_user = src_usr;

	-- transfer picklists the same way we transfer buckets (see above)
	FOR picklist_row in
		SELECT id, name
		FROM   acq.picklist
		WHERE  owner = src_usr
	LOOP
		suffix := ' (' || src_usr || ')';
		LOOP
			BEGIN
				UPDATE  acq.picklist
				SET     owner = dest_usr, name = name || suffix
				WHERE   id = picklist_row.id;
			EXCEPTION WHEN unique_violation THEN
				suffix := suffix || ' ';
				CONTINUE;
			END;
			EXIT;
		END LOOP;
	END LOOP;

    UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
    UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
    UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
    UPDATE acq.provider_note SET creator = dest_usr WHERE creator = src_usr;
    UPDATE acq.provider_note SET editor = dest_usr WHERE editor = src_usr;
    UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
    UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
    UPDATE acq.lineitem_usr_attr_definition SET usr = dest_usr WHERE usr = src_usr;

    -- asset.*
    UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
    UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
    UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
    UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
    UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
    UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;

    -- serial.*
    UPDATE serial.record_entry SET creator = dest_usr WHERE creator = src_usr;
    UPDATE serial.record_entry SET editor = dest_usr WHERE editor = src_usr;

    -- reporter.*
    -- It's not uncommon to define the reporter schema in a replica 
    -- DB only, so don't assume these tables exist in the write DB.
    BEGIN
    	UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
    EXCEPTION WHEN undefined_table THEN
        -- do nothing
    END;
    BEGIN
    	UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
    EXCEPTION WHEN undefined_table THEN
        -- do nothing
    END;
    BEGIN
    	UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
    EXCEPTION WHEN undefined_table THEN
        -- do nothing
    END;
    BEGIN
		-- transfer folders the same way we transfer buckets (see above)
		FOR folder_row in
			SELECT id, name
			FROM   reporter.template_folder
			WHERE  owner = src_usr
		LOOP
			suffix := ' (' || src_usr || ')';
			LOOP
				BEGIN
					UPDATE  reporter.template_folder
					SET     owner = dest_usr, name = name || suffix
					WHERE   id = folder_row.id;
				EXCEPTION WHEN unique_violation THEN
					suffix := suffix || ' ';
					CONTINUE;
				END;
				EXIT;
			END LOOP;
		END LOOP;
    EXCEPTION WHEN undefined_table THEN
        -- do nothing
    END;
    BEGIN
		-- transfer folders the same way we transfer buckets (see above)
		FOR folder_row in
			SELECT id, name
			FROM   reporter.report_folder
			WHERE  owner = src_usr
		LOOP
			suffix := ' (' || src_usr || ')';
			LOOP
				BEGIN
					UPDATE  reporter.report_folder
					SET     owner = dest_usr, name = name || suffix
					WHERE   id = folder_row.id;
				EXCEPTION WHEN unique_violation THEN
					suffix := suffix || ' ';
					CONTINUE;
				END;
				EXIT;
			END LOOP;
		END LOOP;
    EXCEPTION WHEN undefined_table THEN
        -- do nothing
    END;
    BEGIN
		-- transfer folders the same way we transfer buckets (see above)
		FOR folder_row in
			SELECT id, name
			FROM   reporter.output_folder
			WHERE  owner = src_usr
		LOOP
			suffix := ' (' || src_usr || ')';
			LOOP
				BEGIN
					UPDATE  reporter.output_folder
					SET     owner = dest_usr, name = name || suffix
					WHERE   id = folder_row.id;
				EXCEPTION WHEN unique_violation THEN
					suffix := suffix || ' ';
					CONTINUE;
				END;
				EXIT;
			END LOOP;
		END LOOP;
    EXCEPTION WHEN undefined_table THEN
        -- do nothing
    END;

    -- Finally, delete the source user
    DELETE FROM actor.usr WHERE id = src_usr;

END;
$$ LANGUAGE plpgsql;

-- The "add" trigger functions should protect against existing NULLed values, just in case
CREATE OR REPLACE FUNCTION money.materialized_summary_billing_add () RETURNS TRIGGER AS $$
BEGIN
    IF NOT NEW.voided THEN
        UPDATE  money.materialized_billable_xact_summary
          SET   total_owed = COALESCE(total_owed, 0.0::numeric) + NEW.amount,
            last_billing_ts = NEW.billing_ts,
            last_billing_note = NEW.note,
            last_billing_type = NEW.billing_type,
            balance_owed = balance_owed + NEW.amount
          WHERE id = NEW.xact;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION money.materialized_summary_payment_add () RETURNS TRIGGER AS $$
BEGIN
    IF NOT NEW.voided THEN
        UPDATE  money.materialized_billable_xact_summary
          SET   total_paid = COALESCE(total_paid, 0.0::numeric) + NEW.amount,
            last_payment_ts = NEW.payment_ts,
            last_payment_note = NEW.note,
            last_payment_type = TG_ARGV[0],
            balance_owed = balance_owed - NEW.amount
          WHERE id = NEW.xact;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE PLPGSQL;

-- Refresh the mat view with the corrected underlying view
TRUNCATE money.materialized_billable_xact_summary;
INSERT INTO money.materialized_billable_xact_summary SELECT * FROM money.billable_xact_summary;

-- Now redefine the view as a window onto the materialized view
CREATE OR REPLACE VIEW money.billable_xact_summary AS
    SELECT * FROM money.materialized_billable_xact_summary;

CREATE OR REPLACE FUNCTION permission.usr_has_perm_at_nd(
    user_id    IN INTEGER,
    perm_code  IN TEXT
)
RETURNS SETOF INTEGER AS $$
--
-- Return a set of all the org units for which a given user has a given
-- permission, granted directly (not through inheritance from a parent
-- org unit).
--
-- The permissions apply to a minimum depth of the org unit hierarchy,
-- for the org unit(s) to which the user is assigned.  (They also apply
-- to the subordinates of those org units, but we don't report the
-- subordinates here.)
--
-- For purposes of this function, the permission.usr_work_ou_map table
-- defines which users belong to which org units.  I.e. we ignore the
-- home_ou column of actor.usr.
--
-- The result set may contain duplicates, which should be eliminated
-- by a DISTINCT clause.
--
DECLARE
    b_super       BOOLEAN;
    n_perm        INTEGER;
    n_min_depth   INTEGER;
    n_work_ou     INTEGER;
    n_curr_ou     INTEGER;
    n_depth       INTEGER;
    n_curr_depth  INTEGER;
BEGIN
    --
    -- Check for superuser
    --
    SELECT INTO b_super
        super_user
    FROM
        actor.usr
    WHERE
        id = user_id;
    --
    IF NOT FOUND THEN
        return;             -- No user?  No permissions.
    ELSIF b_super THEN
        --
        -- Super user has all permissions everywhere
        --
        FOR n_work_ou IN
            SELECT
                id
            FROM
                actor.org_unit
            WHERE
                parent_ou IS NULL
        LOOP
            RETURN NEXT n_work_ou;
        END LOOP;
        RETURN;
    END IF;
    --
    -- Translate the permission name
    -- to a numeric permission id
    --
    SELECT INTO n_perm
        id
    FROM
        permission.perm_list
    WHERE
        code = perm_code;
    --
    IF NOT FOUND THEN
        RETURN;               -- No such permission
    END IF;
    --
    -- Find the highest-level org unit (i.e. the minimum depth)
    -- to which the permission is applied for this user
    --
    -- This query is modified from the one in permission.usr_perms().
    --
    SELECT INTO n_min_depth
        min( depth )
    FROM    (
        SELECT depth
          FROM permission.usr_perm_map upm
         WHERE upm.usr = user_id
           AND (upm.perm = n_perm OR upm.perm = -1)
                    UNION
        SELECT  gpm.depth
          FROM  permission.grp_perm_map gpm
          WHERE (gpm.perm = n_perm OR gpm.perm = -1)
            AND gpm.grp IN (
               SELECT   (permission.grp_ancestors(
                    (SELECT profile FROM actor.usr WHERE id = user_id)
                )).id
            )
                    UNION
        SELECT  p.depth
          FROM  permission.grp_perm_map p
          WHERE (p.perm = n_perm OR p.perm = -1)
            AND p.grp IN (
                SELECT (permission.grp_ancestors(m.grp)).id
                FROM   permission.usr_grp_map m
                WHERE  m.usr = user_id
            )
    ) AS x;
    --
    IF NOT FOUND THEN
        RETURN;                -- No such permission for this user
    END IF;
    --
    -- Identify the org units to which the user is assigned.  Note that
    -- we pay no attention to the home_ou column in actor.usr.
    --
    FOR n_work_ou IN
        SELECT
            work_ou
        FROM
            permission.usr_work_ou_map
        WHERE
            usr = user_id
    LOOP            -- For each org unit to which the user is assigned
        --
        -- Determine the level of the org unit by a lookup in actor.org_unit_type.
        -- We take it on faith that this depth agrees with the actual hierarchy
        -- defined in actor.org_unit.
        --
        SELECT INTO n_depth
            type.depth
        FROM
            actor.org_unit_type type
                INNER JOIN actor.org_unit ou
                    ON ( ou.ou_type = type.id )
        WHERE
            ou.id = n_work_ou;
        --
        IF NOT FOUND THEN
            CONTINUE;        -- Maybe raise exception?
        END IF;
        --
        -- Compare the depth of the work org unit to the
        -- minimum depth, and branch accordingly
        --
        IF n_depth = n_min_depth THEN
            --
            -- The org unit is at the right depth, so return it.
            --
            RETURN NEXT n_work_ou;
        ELSIF n_depth > n_min_depth THEN
            --
            -- Traverse the org unit tree toward the root,
            -- until you reach the minimum depth determined above
            --
            n_curr_depth := n_depth;
            n_curr_ou := n_work_ou;
            WHILE n_curr_depth > n_min_depth LOOP
                SELECT INTO n_curr_ou
                    parent_ou
                FROM
                    actor.org_unit
                WHERE
                    id = n_curr_ou;
                --
                IF FOUND THEN
                    n_curr_depth := n_curr_depth - 1;
                ELSE
                    --
                    -- This can happen only if the hierarchy defined in
                    -- actor.org_unit is corrupted, or out of sync with
                    -- the depths defined in actor.org_unit_type.
                    -- Maybe we should raise an exception here, instead
                    -- of silently ignoring the problem.
                    --
                    n_curr_ou = NULL;
                    EXIT;
                END IF;
            END LOOP;
            --
            IF n_curr_ou IS NOT NULL THEN
                RETURN NEXT n_curr_ou;
            END IF;
        ELSE
            --
            -- The permission applies only at a depth greater than the work org unit.
            -- Use connectby() to find all dependent org units at the specified depth.
            --
            FOR n_curr_ou IN
                SELECT ou::INTEGER
                FROM connectby(
                        'actor.org_unit',         -- table name
                        'id',                     -- key column
                        'parent_ou',              -- recursive foreign key
                        n_work_ou::TEXT,          -- id of starting point
                        (n_min_depth - n_depth)   -- max depth to search, relative
                    )                             --   to starting point
                    AS t(
                        ou text,            -- dependent org unit
                        parent_ou text,     -- (ignore)
                        level int           -- depth relative to starting point
                    )
                WHERE
                    level = n_min_depth - n_depth
            LOOP
                RETURN NEXT n_curr_ou;
            END LOOP;
        END IF;
        --
    END LOOP;
    --
    RETURN;
    --
END;
$$ LANGUAGE 'plpgsql';

ALTER TABLE acq.purchase_order
	ADD COLUMN cancel_reason INT
		REFERENCES acq.cancel_reason( id )
	    DEFERRABLE INITIALLY DEFERRED,
	ADD COLUMN prepayment_required BOOLEAN NOT NULL DEFAULT FALSE;

-- Build the history table and lifecycle view
-- for acq.purchase_order

SELECT acq.create_acq_auditor ( 'acq', 'purchase_order' );

CREATE INDEX acq_po_hist_id_idx            ON acq.acq_purchase_order_history( id );

ALTER TABLE acq.lineitem
	ADD COLUMN cancel_reason INT
		REFERENCES acq.cancel_reason( id )
	    DEFERRABLE INITIALLY DEFERRED,
	ADD COLUMN estimated_unit_price NUMERIC,
	ADD COLUMN claim_policy INT
		REFERENCES acq.claim_policy
		DEFERRABLE INITIALLY DEFERRED,
	ALTER COLUMN eg_bib_id SET DATA TYPE bigint;

-- Build the history table and lifecycle view
-- for acq.lineitem

SELECT acq.create_acq_auditor ( 'acq', 'lineitem' );
CREATE INDEX acq_lineitem_hist_id_idx            ON acq.acq_lineitem_history( id );

ALTER TABLE acq.lineitem_detail
	ADD COLUMN cancel_reason        INT REFERENCES acq.cancel_reason( id )
	                                    DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE acq.lineitem_detail
	DROP CONSTRAINT lineitem_detail_lineitem_fkey;

ALTER TABLE acq.lineitem_detail
	ADD FOREIGN KEY (lineitem) REFERENCES acq.lineitem( id )
		ON DELETE CASCADE
		DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE acq.lineitem_detail DROP CONSTRAINT lineitem_detail_eg_copy_id_fkey;

INSERT INTO acq.cancel_reason ( id, org_unit, label, description ) VALUES (
	1, 1, 'invalid_isbn', oils_i18n_gettext( 1, 'ISBN is unrecognizable', 'acqcr', 'label' ));

INSERT INTO acq.cancel_reason ( id, org_unit, label, description ) VALUES (
	2, 1, 'postpone', oils_i18n_gettext( 2, 'Title has been postponed', 'acqcr', 'label' ));

CREATE OR REPLACE FUNCTION vandelay.add_field ( target_xml TEXT, source_xml TEXT, field TEXT, force_add INT ) RETURNS TEXT AS $_$

    use MARC::Record;
    use MARC::File::XML (BinaryEncoding => 'UTF-8');
    use strict;

    my $target_xml = shift;
    my $source_xml = shift;
    my $field_spec = shift;
    my $force_add = shift || 0;

    my $target_r = MARC::Record->new_from_xml( $target_xml );
    my $source_r = MARC::Record->new_from_xml( $source_xml );

    return $target_xml unless ($target_r && $source_r);

    my @field_list = split(',', $field_spec);

    my %fields;
    for my $f (@field_list) {
        $f =~ s/^\s*//; $f =~ s/\s*$//;
        if ($f =~ /^(.{3})(\w*)(?:\[([^]]*)\])?$/) {
            my $field = $1;
            $field =~ s/\s+//;
            my $sf = $2;
            $sf =~ s/\s+//;
            my $match = $3;
            $match =~ s/^\s*//; $match =~ s/\s*$//;
            $fields{$field} = { sf => [ split('', $sf) ] };
            if ($match) {
                my ($msf,$mre) = split('~', $match);
                if (length($msf) > 0 and length($mre) > 0) {
                    $msf =~ s/^\s*//; $msf =~ s/\s*$//;
                    $mre =~ s/^\s*//; $mre =~ s/\s*$//;
                    $fields{$field}{match} = { sf => $msf, re => qr/$mre/ };
                }
            }
        }
    }

    for my $f ( keys %fields) {
        if ( @{$fields{$f}{sf}} ) {
            for my $from_field ($source_r->field( $f )) {
                my @tos = $target_r->field( $f );
                if (!@tos) {
                    next if (exists($fields{$f}{match}) and !$force_add);
                    my @new_fields = map { $_->clone } $source_r->field( $f );
                    $target_r->insert_fields_ordered( @new_fields );
                } else {
                    for my $to_field (@tos) {
                        if (exists($fields{$f}{match})) {
                            next unless (grep { $_ =~ $fields{$f}{match}{re} } $to_field->subfield($fields{$f}{match}{sf}));
                        }
                        my @new_sf = map { ($_ => $from_field->subfield($_)) } @{$fields{$f}{sf}};
                        $to_field->add_subfields( @new_sf );
                    }
                }
            }
        } else {
            my @new_fields = map { $_->clone } $source_r->field( $f );
            $target_r->insert_fields_ordered( @new_fields );
        }
    }

    $target_xml = $target_r->as_xml_record;
    $target_xml =~ s/^<\?.+?\?>$//mo;
    $target_xml =~ s/\n//sgo;
    $target_xml =~ s/>\s+</></sgo;

    return $target_xml;

$_$ LANGUAGE PLPERLU;

CREATE OR REPLACE FUNCTION vandelay.add_field ( target_xml TEXT, source_xml TEXT, field TEXT ) RETURNS TEXT AS $_$
    SELECT vandelay.add_field( $1, $2, $3, 0 );
$_$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION vandelay.strip_field ( xml TEXT, field TEXT ) RETURNS TEXT AS $_$

    use MARC::Record;
    use MARC::File::XML (BinaryEncoding => 'UTF-8');
    use strict;

    my $xml = shift;
    my $r = MARC::Record->new_from_xml( $xml );

    return $xml unless ($r);

    my $field_spec = shift;
    my @field_list = split(',', $field_spec);

    my %fields;
    for my $f (@field_list) {
        $f =~ s/^\s*//; $f =~ s/\s*$//;
        if ($f =~ /^(.{3})(\w*)(?:\[([^]]*)\])?$/) {
            my $field = $1;
            $field =~ s/\s+//;
            my $sf = $2;
            $sf =~ s/\s+//;
            my $match = $3;
            $match =~ s/^\s*//; $match =~ s/\s*$//;
            $fields{$field} = { sf => [ split('', $sf) ] };
            if ($match) {
                my ($msf,$mre) = split('~', $match);
                if (length($msf) > 0 and length($mre) > 0) {
                    $msf =~ s/^\s*//; $msf =~ s/\s*$//;
                    $mre =~ s/^\s*//; $mre =~ s/\s*$//;
                    $fields{$field}{match} = { sf => $msf, re => qr/$mre/ };
                }
            }
        }
    }

    for my $f ( keys %fields) {
        for my $to_field ($r->field( $f )) {
            if (exists($fields{$f}{match})) {
                next unless (grep { $_ =~ $fields{$f}{match}{re} } $to_field->subfield($fields{$f}{match}{sf}));
            }

            if ( @{$fields{$f}{sf}} ) {
                $to_field->delete_subfield(code => $fields{$f}{sf});
            } else {
                $r->delete_field( $to_field );
            }
        }
    }

    $xml = $r->as_xml_record;
    $xml =~ s/^<\?.+?\?>$//mo;
    $xml =~ s/\n//sgo;
    $xml =~ s/>\s+</></sgo;

    return $xml;

$_$ LANGUAGE PLPERLU;

CREATE OR REPLACE FUNCTION vandelay.replace_field ( target_xml TEXT, source_xml TEXT, field TEXT ) RETURNS TEXT AS $_$
DECLARE
    xml_output TEXT;
BEGIN
    xml_output := vandelay.strip_field( target_xml, field);

    IF xml_output <> target_xml  AND field ~ E'~' THEN
        -- we removed something, and there was a regexp restriction in the field definition, so proceed
        xml_output := vandelay.add_field( xml_output, source_xml, field, 1 );
    ELSIF field !~ E'~' THEN
        -- No regexp restriction, add the field
        xml_output := vandelay.add_field( xml_output, source_xml, field, 0 );
    END IF;

    RETURN xml_output;
END;
$_$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION vandelay.preserve_field ( incumbent_xml TEXT, incoming_xml TEXT, field TEXT ) RETURNS TEXT AS $_$
    SELECT vandelay.add_field( vandelay.strip_field( $2, $3), $1, $3 );
$_$ LANGUAGE SQL;

CREATE VIEW action.unfulfilled_hold_max_loop AS
	SELECT  hold,
	        max(count) AS max
	FROM    action.unfulfilled_hold_loops
	GROUP BY 1;

ALTER TABLE acq.lineitem_attr
	DROP CONSTRAINT lineitem_attr_lineitem_fkey;

ALTER TABLE acq.lineitem_attr
	ADD FOREIGN KEY (lineitem) REFERENCES acq.lineitem( id )
		ON DELETE CASCADE
		DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE acq.po_note
	ADD COLUMN vendor_public BOOLEAN NOT NULL DEFAULT FALSE;

CREATE TABLE vandelay.merge_profile (
    id              BIGSERIAL   PRIMARY KEY,
    owner           INT         NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
    name            TEXT        NOT NULL,
    add_spec        TEXT,
    replace_spec    TEXT,
    strip_spec      TEXT,
    preserve_spec   TEXT,
    CONSTRAINT vand_merge_prof_owner_name_idx UNIQUE (owner,name),
    CONSTRAINT add_replace_strip_or_preserve CHECK ((preserve_spec IS NOT NULL OR replace_spec IS NOT NULL) OR (preserve_spec IS NULL AND replace_spec IS NULL))
);

CREATE OR REPLACE FUNCTION vandelay.match_bib_record ( ) RETURNS TRIGGER AS $func$
DECLARE
    attr        RECORD;
    attr_def    RECORD;
    eg_rec      RECORD;
    id_value    TEXT;
    exact_id    BIGINT;
BEGIN

    DELETE FROM vandelay.bib_match WHERE queued_record = NEW.id;

    SELECT * INTO attr_def FROM vandelay.bib_attr_definition WHERE xpath = '//*[@tag="901"]/*[@code="c"]' ORDER BY id LIMIT 1;

    IF attr_def IS NOT NULL AND attr_def.id IS NOT NULL THEN
        id_value := extract_marc_field('vandelay.queued_bib_record', NEW.id, attr_def.xpath, attr_def.remove);

        IF id_value IS NOT NULL AND id_value <> '' AND id_value ~ $r$^\d+$$r$ THEN
            SELECT id INTO exact_id FROM biblio.record_entry WHERE id = id_value::BIGINT AND NOT deleted;
            SELECT * INTO attr FROM vandelay.queued_bib_record_attr WHERE record = NEW.id and field = attr_def.id LIMIT 1;
            IF exact_id IS NOT NULL THEN
                INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, exact_id);
            END IF;
        END IF;
    END IF;

    IF exact_id IS NULL THEN
        FOR attr IN SELECT a.* FROM vandelay.queued_bib_record_attr a JOIN vandelay.bib_attr_definition d ON (d.id = a.field) WHERE record = NEW.id AND d.ident IS TRUE LOOP

            -- All numbers? check for an id match
            IF (attr.attr_value ~ $r$^\d+$$r$) THEN
                FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE id = attr.attr_value::BIGINT AND deleted IS FALSE LOOP
                    INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id);
                END LOOP;
            END IF;

            -- Looks like an ISBN? check for an isbn match
            IF (attr.attr_value ~* $r$^[0-9x]+$$r$ AND character_length(attr.attr_value) IN (10,13)) THEN
                FOR eg_rec IN EXECUTE $$SELECT * FROM metabib.full_rec fr WHERE fr.value LIKE LOWER('$$ || attr.attr_value || $$%') AND fr.tag = '020' AND fr.subfield = 'a'$$ LOOP
                    PERFORM id FROM biblio.record_entry WHERE id = eg_rec.record AND deleted IS FALSE;
                    IF FOUND THEN
                        INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('isbn', attr.id, NEW.id, eg_rec.record);
                    END IF;
                END LOOP;

                -- subcheck for isbn-as-tcn
                FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = 'i' || attr.attr_value AND deleted IS FALSE LOOP
                    INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
                END LOOP;
            END IF;

            -- check for an OCLC tcn_value match
            IF (attr.attr_value ~ $r$^o\d+$$r$) THEN
                FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = regexp_replace(attr.attr_value,'^o','ocm') AND deleted IS FALSE LOOP
                    INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
                END LOOP;
            END IF;

            -- check for a direct tcn_value match
            FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = attr.attr_value AND deleted IS FALSE LOOP
                INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
            END LOOP;

            -- check for a direct item barcode match
            FOR eg_rec IN
                    SELECT  DISTINCT b.*
                      FROM  biblio.record_entry b
                            JOIN asset.call_number cn ON (cn.record = b.id)
                            JOIN asset.copy cp ON (cp.call_number = cn.id)
                      WHERE cp.barcode = attr.attr_value AND cp.deleted IS FALSE
            LOOP
                INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id);
            END LOOP;

        END LOOP;
    END IF;

    RETURN NULL;
END;
$func$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION vandelay.merge_record_xml ( target_xml TEXT, source_xml TEXT, add_rule TEXT, replace_preserve_rule TEXT, strip_rule TEXT ) RETURNS TEXT AS $_$
    SELECT vandelay.replace_field( vandelay.add_field( vandelay.strip_field( $1, $5) , $2, $3 ), $2, $4);
$_$ LANGUAGE SQL;

CREATE TYPE vandelay.compile_profile AS (add_rule TEXT, replace_rule TEXT, preserve_rule TEXT, strip_rule TEXT);
CREATE OR REPLACE FUNCTION vandelay.compile_profile ( incoming_xml TEXT ) RETURNS vandelay.compile_profile AS $_$
DECLARE
    output              vandelay.compile_profile%ROWTYPE;
    profile             vandelay.merge_profile%ROWTYPE;
    profile_tmpl        TEXT;
    profile_tmpl_owner  TEXT;
    add_rule            TEXT := '';
    strip_rule          TEXT := '';
    replace_rule        TEXT := '';
    preserve_rule       TEXT := '';

BEGIN

    profile_tmpl := (oils_xpath('//*[@tag="905"]/*[@code="t"]/text()',incoming_xml))[1];
    profile_tmpl_owner := (oils_xpath('//*[@tag="905"]/*[@code="o"]/text()',incoming_xml))[1];

    IF profile_tmpl IS NOT NULL AND profile_tmpl <> '' AND profile_tmpl_owner IS NOT NULL AND profile_tmpl_owner <> '' THEN
        SELECT  p.* INTO profile
          FROM  vandelay.merge_profile p
                JOIN actor.org_unit u ON (u.id = p.owner)
          WHERE p.name = profile_tmpl
                AND u.shortname = profile_tmpl_owner;

        IF profile.id IS NOT NULL THEN
            add_rule := COALESCE(profile.add_spec,'');
            strip_rule := COALESCE(profile.strip_spec,'');
            replace_rule := COALESCE(profile.replace_spec,'');
            preserve_rule := COALESCE(profile.preserve_spec,'');
        END IF;
    END IF;

    add_rule := add_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="a"]/text()',incoming_xml),','),'');
    strip_rule := strip_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="d"]/text()',incoming_xml),','),'');
    replace_rule := replace_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="r"]/text()',incoming_xml),','),'');
    preserve_rule := preserve_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="p"]/text()',incoming_xml),','),'');

    output.add_rule := BTRIM(add_rule,',');
    output.replace_rule := BTRIM(replace_rule,',');
    output.strip_rule := BTRIM(strip_rule,',');
    output.preserve_rule := BTRIM(preserve_rule,',');

    RETURN output;
END;
$_$ LANGUAGE PLPGSQL;

-- Template-based marc munging functions
CREATE OR REPLACE FUNCTION vandelay.template_overlay_bib_record ( v_marc TEXT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
DECLARE
    merge_profile   vandelay.merge_profile%ROWTYPE;
    dyn_profile     vandelay.compile_profile%ROWTYPE;
    editor_string   TEXT;
    editor_id       INT;
    source_marc     TEXT;
    target_marc     TEXT;
    eg_marc         TEXT;
    replace_rule    TEXT;
    match_count     INT;
BEGIN

    SELECT  b.marc INTO eg_marc
      FROM  biblio.record_entry b
      WHERE b.id = eg_id
      LIMIT 1;

    IF eg_marc IS NULL OR v_marc IS NULL THEN
        -- RAISE NOTICE 'no marc for template or bib record';
        RETURN FALSE;
    END IF;

    dyn_profile := vandelay.compile_profile( v_marc );

    IF merge_profile_id IS NOT NULL THEN
        SELECT * INTO merge_profile FROM vandelay.merge_profile WHERE id = merge_profile_id;
        IF FOUND THEN
            dyn_profile.add_rule := BTRIM( dyn_profile.add_rule || ',' || COALESCE(merge_profile.add_spec,''), ',');
            dyn_profile.strip_rule := BTRIM( dyn_profile.strip_rule || ',' || COALESCE(merge_profile.strip_spec,''), ',');
            dyn_profile.replace_rule := BTRIM( dyn_profile.replace_rule || ',' || COALESCE(merge_profile.replace_spec,''), ',');
            dyn_profile.preserve_rule := BTRIM( dyn_profile.preserve_rule || ',' || COALESCE(merge_profile.preserve_spec,''), ',');
        END IF;
    END IF;

    IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN
        -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule;
        RETURN FALSE;
    END IF;

    IF dyn_profile.replace_rule <> '' THEN
        source_marc = v_marc;
        target_marc = eg_marc;
        replace_rule = dyn_profile.replace_rule;
    ELSE
        source_marc = eg_marc;
        target_marc = v_marc;
        replace_rule = dyn_profile.preserve_rule;
    END IF;

    UPDATE  biblio.record_entry
      SET   marc = vandelay.merge_record_xml( target_marc, source_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule )
      WHERE id = eg_id;

    IF NOT FOUND THEN
        -- RAISE NOTICE 'update of biblio.record_entry failed';
        RETURN FALSE;
    END IF;

    RETURN TRUE;

END;
$$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION vandelay.template_overlay_bib_record ( v_marc TEXT, eg_id BIGINT) RETURNS BOOL AS $$
    SELECT vandelay.template_overlay_bib_record( $1, $2, NULL);
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION vandelay.overlay_bib_record ( import_id BIGINT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
DECLARE
    merge_profile   vandelay.merge_profile%ROWTYPE;
    dyn_profile     vandelay.compile_profile%ROWTYPE;
    editor_string   TEXT;
    editor_id       INT;
    source_marc     TEXT;
    target_marc     TEXT;
    eg_marc         TEXT;
    v_marc          TEXT;
    replace_rule    TEXT;
    match_count     INT;
BEGIN

    SELECT  q.marc INTO v_marc
      FROM  vandelay.queued_record q
            JOIN vandelay.bib_match m ON (m.queued_record = q.id AND q.id = import_id)
      LIMIT 1;

    IF v_marc IS NULL THEN
        -- RAISE NOTICE 'no marc for vandelay or bib record';
        RETURN FALSE;
    END IF;

    IF vandelay.template_overlay_bib_record( v_marc, eg_id, merge_profile_id) THEN
        UPDATE  vandelay.queued_bib_record
          SET   imported_as = eg_id,
                import_time = NOW()
          WHERE id = import_id;

        editor_string := (oils_xpath('//*[@tag="905"]/*[@code="u"]/text()',v_marc))[1];

        IF editor_string IS NOT NULL AND editor_string <> '' THEN
            SELECT usr INTO editor_id FROM actor.card WHERE barcode = editor_string;

            IF editor_id IS NULL THEN
                SELECT id INTO editor_id FROM actor.usr WHERE usrname = editor_string;
            END IF;

            IF editor_id IS NOT NULL THEN
                UPDATE biblio.record_entry SET editor = editor_id WHERE id = eg_id;
            END IF;
        END IF;

        RETURN TRUE;
    END IF;

    -- RAISE NOTICE 'update of biblio.record_entry failed';

    RETURN FALSE;

END;
$$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_record ( import_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
DECLARE
    eg_id           BIGINT;
    match_count     INT;
    match_attr      vandelay.bib_attr_definition%ROWTYPE;
BEGIN

    PERFORM * FROM vandelay.queued_bib_record WHERE import_time IS NOT NULL AND id = import_id;

    IF FOUND THEN
        -- RAISE NOTICE 'already imported, cannot auto-overlay'
        RETURN FALSE;
    END IF;

    SELECT COUNT(*) INTO match_count FROM vandelay.bib_match WHERE queued_record = import_id;

    IF match_count <> 1 THEN
        -- RAISE NOTICE 'not an exact match';
        RETURN FALSE;
    END IF;

    SELECT  d.* INTO match_attr
      FROM  vandelay.bib_attr_definition d
            JOIN vandelay.queued_bib_record_attr a ON (a.field = d.id)
            JOIN vandelay.bib_match m ON (m.matched_attr = a.id)
      WHERE m.queued_record = import_id;

    IF NOT (match_attr.xpath ~ '@tag="901"' AND match_attr.xpath ~ '@code="c"') THEN
        -- RAISE NOTICE 'not a 901c match: %', match_attr.xpath;
        RETURN FALSE;
    END IF;

    SELECT  m.eg_record INTO eg_id
      FROM  vandelay.bib_match m
      WHERE m.queued_record = import_id
      LIMIT 1;

    IF eg_id IS NULL THEN
        RETURN FALSE;
    END IF;

    RETURN vandelay.overlay_bib_record( import_id, eg_id, merge_profile_id );
END;
$$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_queue ( queue_id BIGINT, merge_profile_id INT ) RETURNS SETOF BIGINT AS $$
DECLARE
    queued_record   vandelay.queued_bib_record%ROWTYPE;
BEGIN

    FOR queued_record IN SELECT * FROM vandelay.queued_bib_record WHERE queue = queue_id AND import_time IS NULL LOOP

        IF vandelay.auto_overlay_bib_record( queued_record.id, merge_profile_id ) THEN
            RETURN NEXT queued_record.id;
        END IF;

    END LOOP;

    RETURN;

END;
$$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_queue ( queue_id BIGINT ) RETURNS SETOF BIGINT AS $$
    SELECT * FROM vandelay.auto_overlay_bib_queue( $1, NULL );
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION vandelay.overlay_authority_record ( import_id BIGINT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
DECLARE
    merge_profile   vandelay.merge_profile%ROWTYPE;
    dyn_profile     vandelay.compile_profile%ROWTYPE;
    source_marc     TEXT;
    target_marc     TEXT;
    eg_marc         TEXT;
    v_marc          TEXT;
    replace_rule    TEXT;
    match_count     INT;
BEGIN

    SELECT  b.marc INTO eg_marc
      FROM  authority.record_entry b
            JOIN vandelay.authority_match m ON (m.eg_record = b.id AND m.queued_record = import_id)
      LIMIT 1;

    SELECT  q.marc INTO v_marc
      FROM  vandelay.queued_record q
            JOIN vandelay.authority_match m ON (m.queued_record = q.id AND q.id = import_id)
      LIMIT 1;

    IF eg_marc IS NULL OR v_marc IS NULL THEN
        -- RAISE NOTICE 'no marc for vandelay or authority record';
        RETURN FALSE;
    END IF;

    dyn_profile := vandelay.compile_profile( v_marc );

    IF merge_profile_id IS NOT NULL THEN
        SELECT * INTO merge_profile FROM vandelay.merge_profile WHERE id = merge_profile_id;
        IF FOUND THEN
            dyn_profile.add_rule := BTRIM( dyn_profile.add_rule || ',' || COALESCE(merge_profile.add_spec,''), ',');
            dyn_profile.strip_rule := BTRIM( dyn_profile.strip_rule || ',' || COALESCE(merge_profile.strip_spec,''), ',');
            dyn_profile.replace_rule := BTRIM( dyn_profile.replace_rule || ',' || COALESCE(merge_profile.replace_spec,''), ',');
            dyn_profile.preserve_rule := BTRIM( dyn_profile.preserve_rule || ',' || COALESCE(merge_profile.preserve_spec,''), ',');
        END IF;
    END IF;

    IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN
        -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule;
        RETURN FALSE;
    END IF;

    IF dyn_profile.replace_rule <> '' THEN
        source_marc = v_marc;
        target_marc = eg_marc;
        replace_rule = dyn_profile.replace_rule;
    ELSE
        source_marc = eg_marc;
        target_marc = v_marc;
        replace_rule = dyn_profile.preserve_rule;
    END IF;

    UPDATE  authority.record_entry
      SET   marc = vandelay.merge_record_xml( target_marc, source_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule )
      WHERE id = eg_id;

    IF FOUND THEN
        UPDATE  vandelay.queued_authority_record
          SET   imported_as = eg_id,
                import_time = NOW()
          WHERE id = import_id;
        RETURN TRUE;
    END IF;

    -- RAISE NOTICE 'update of authority.record_entry failed';

    RETURN FALSE;

END;
$$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_record ( import_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
DECLARE
    eg_id           BIGINT;
    match_count     INT;
BEGIN
    SELECT COUNT(*) INTO match_count FROM vandelay.authority_match WHERE queued_record = import_id;

    IF match_count <> 1 THEN
        -- RAISE NOTICE 'not an exact match';
        RETURN FALSE;
    END IF;

    SELECT  m.eg_record INTO eg_id
      FROM  vandelay.authority_match m
      WHERE m.queued_record = import_id
      LIMIT 1;

    IF eg_id IS NULL THEN
        RETURN FALSE;
    END IF;

    RETURN vandelay.overlay_authority_record( import_id, eg_id, merge_profile_id );
END;
$$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_queue ( queue_id BIGINT, merge_profile_id INT ) RETURNS SETOF BIGINT AS $$
DECLARE
    queued_record   vandelay.queued_authority_record%ROWTYPE;
BEGIN

    FOR queued_record IN SELECT * FROM vandelay.queued_authority_record WHERE queue = queue_id AND import_time IS NULL LOOP

        IF vandelay.auto_overlay_authority_record( queued_record.id, merge_profile_id ) THEN
            RETURN NEXT queued_record.id;
        END IF;

    END LOOP;

    RETURN;

END;
$$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_queue ( queue_id BIGINT ) RETURNS SETOF BIGINT AS $$
    SELECT * FROM vandelay.auto_overlay_authority_queue( $1, NULL );
$$ LANGUAGE SQL;

CREATE TYPE vandelay.tcn_data AS (tcn TEXT, tcn_source TEXT, used BOOL);
CREATE OR REPLACE FUNCTION vandelay.find_bib_tcn_data ( xml TEXT ) RETURNS SETOF vandelay.tcn_data AS $_$
DECLARE
    eg_tcn          TEXT;
    eg_tcn_source   TEXT;
    output          vandelay.tcn_data%ROWTYPE;
BEGIN

    -- 001/003
    eg_tcn := BTRIM((oils_xpath('//*[@tag="001"]/text()',xml))[1]);
    IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN

        eg_tcn_source := BTRIM((oils_xpath('//*[@tag="003"]/text()',xml))[1]);
        IF eg_tcn_source IS NULL OR eg_tcn_source = '' THEN
            eg_tcn_source := 'System Local';
        END IF;

        PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn  AND NOT deleted;

        IF NOT FOUND THEN
            output.used := FALSE;
        ELSE
            output.used := TRUE;
        END IF;

        output.tcn := eg_tcn;
        output.tcn_source := eg_tcn_source;
        RETURN NEXT output;

    END IF;

    -- 901 ab
    eg_tcn := BTRIM((oils_xpath('//*[@tag="901"]/*[@code="a"]/text()',xml))[1]);
    IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN

        eg_tcn_source := BTRIM((oils_xpath('//*[@tag="901"]/*[@code="b"]/text()',xml))[1]);
        IF eg_tcn_source IS NULL OR eg_tcn_source = '' THEN
            eg_tcn_source := 'System Local';
        END IF;

        PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn  AND NOT deleted;

        IF NOT FOUND THEN
            output.used := FALSE;
        ELSE
            output.used := TRUE;
        END IF;

        output.tcn := eg_tcn;
        output.tcn_source := eg_tcn_source;
        RETURN NEXT output;

    END IF;

    -- 039 ab
    eg_tcn := BTRIM((oils_xpath('//*[@tag="039"]/*[@code="a"]/text()',xml))[1]);
    IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN

        eg_tcn_source := BTRIM((oils_xpath('//*[@tag="039"]/*[@code="b"]/text()',xml))[1]);
        IF eg_tcn_source IS NULL OR eg_tcn_source = '' THEN
            eg_tcn_source := 'System Local';
        END IF;

        PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn  AND NOT deleted;

        IF NOT FOUND THEN
            output.used := FALSE;
        ELSE
            output.used := TRUE;
        END IF;

        output.tcn := eg_tcn;
        output.tcn_source := eg_tcn_source;
        RETURN NEXT output;

    END IF;

    -- 020 a
    eg_tcn := REGEXP_REPLACE((oils_xpath('//*[@tag="020"]/*[@code="a"]/text()',xml))[1], $re$^(\w+).*?$$re$, $re$\1$re$);
    IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN

        eg_tcn_source := 'ISBN';

        PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn  AND NOT deleted;

        IF NOT FOUND THEN
            output.used := FALSE;
        ELSE
            output.used := TRUE;
        END IF;

        output.tcn := eg_tcn;
        output.tcn_source := eg_tcn_source;
        RETURN NEXT output;

    END IF;

    -- 022 a
    eg_tcn := REGEXP_REPLACE((oils_xpath('//*[@tag="022"]/*[@code="a"]/text()',xml))[1], $re$^(\w+).*?$$re$, $re$\1$re$);
    IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN

        eg_tcn_source := 'ISSN';

        PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn  AND NOT deleted;

        IF NOT FOUND THEN
            output.used := FALSE;
        ELSE
            output.used := TRUE;
        END IF;

        output.tcn := eg_tcn;
        output.tcn_source := eg_tcn_source;
        RETURN NEXT output;

    END IF;

    -- 010 a
    eg_tcn := REGEXP_REPLACE((oils_xpath('//*[@tag="010"]/*[@code="a"]/text()',xml))[1], $re$^(\w+).*?$$re$, $re$\1$re$);
    IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN

        eg_tcn_source := 'LCCN';

        PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn  AND NOT deleted;

        IF NOT FOUND THEN
            output.used := FALSE;
        ELSE
            output.used := TRUE;
        END IF;

        output.tcn := eg_tcn;
        output.tcn_source := eg_tcn_source;
        RETURN NEXT output;

    END IF;

    -- 035 a
    eg_tcn := REGEXP_REPLACE((oils_xpath('//*[@tag="035"]/*[@code="a"]/text()',xml))[1], $re$^.*?(\w+)$$re$, $re$\1$re$);
    IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN

        eg_tcn_source := 'System Legacy';

        PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn  AND NOT deleted;

        IF NOT FOUND THEN
            output.used := FALSE;
        ELSE
            output.used := TRUE;
        END IF;

        output.tcn := eg_tcn;
        output.tcn_source := eg_tcn_source;
        RETURN NEXT output;

    END IF;

    RETURN;
END;
$_$ LANGUAGE PLPGSQL;

CREATE INDEX claim_lid_idx ON acq.claim( lineitem_detail );

CREATE OR REPLACE RULE protect_bib_rec_delete AS ON DELETE TO biblio.record_entry DO INSTEAD (UPDATE biblio.record_entry SET deleted = TRUE WHERE OLD.id = biblio.record_entry.id; DELETE FROM metabib.metarecord_source_map WHERE source = OLD.id);

CREATE INDEX metabib_title_field_entry_value_idx ON metabib.title_field_entry (SUBSTRING(value,1,1024)) WHERE index_vector = ''::TSVECTOR;
CREATE INDEX metabib_author_field_entry_value_idx ON metabib.author_field_entry (SUBSTRING(value,1,1024)) WHERE index_vector = ''::TSVECTOR;
CREATE INDEX metabib_subject_field_entry_value_idx ON metabib.subject_field_entry (SUBSTRING(value,1,1024)) WHERE index_vector = ''::TSVECTOR;
CREATE INDEX metabib_keyword_field_entry_value_idx ON metabib.keyword_field_entry (SUBSTRING(value,1,1024)) WHERE index_vector = ''::TSVECTOR;
CREATE INDEX metabib_series_field_entry_value_idx ON metabib.series_field_entry (SUBSTRING(value,1,1024)) WHERE index_vector = ''::TSVECTOR;

CREATE INDEX metabib_author_field_entry_source_idx ON metabib.author_field_entry (source);
CREATE INDEX metabib_keyword_field_entry_source_idx ON metabib.keyword_field_entry (source);
CREATE INDEX metabib_title_field_entry_source_idx ON metabib.title_field_entry (source);
CREATE INDEX metabib_series_field_entry_source_idx ON metabib.series_field_entry (source);

ALTER TABLE metabib.series_field_entry
	ADD CONSTRAINT metabib_series_field_entry_source_pkey FOREIGN KEY (source)
		REFERENCES biblio.record_entry (id)
		ON DELETE CASCADE
		DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE metabib.series_field_entry
	ADD CONSTRAINT metabib_series_field_entry_field_pkey FOREIGN KEY (field)
		REFERENCES config.metabib_field (id)
		ON DELETE CASCADE
		DEFERRABLE INITIALLY DEFERRED;

CREATE TABLE acq.claim_policy_action (
	id              SERIAL       PRIMARY KEY,
	claim_policy    INT          NOT NULL REFERENCES acq.claim_policy
                                 ON DELETE CASCADE
	                             DEFERRABLE INITIALLY DEFERRED,
	action_interval INTERVAL     NOT NULL,
	action          INT          NOT NULL REFERENCES acq.claim_event_type
	                             DEFERRABLE INITIALLY DEFERRED,
	CONSTRAINT action_sequence UNIQUE (claim_policy, action_interval)
);

CREATE OR REPLACE FUNCTION public.ingest_acq_marc ( ) RETURNS TRIGGER AS $function$
DECLARE
    value       TEXT; 
    atype       TEXT; 
    prov        INT;
    pos         INT;
    adef        RECORD;
    xpath_string    TEXT;
BEGIN
    FOR adef IN SELECT *,tableoid FROM acq.lineitem_attr_definition LOOP
    
        SELECT relname::TEXT INTO atype FROM pg_class WHERE oid = adef.tableoid;
      
        IF (atype NOT IN ('lineitem_usr_attr_definition','lineitem_local_attr_definition')) THEN
            IF (atype = 'lineitem_provider_attr_definition') THEN
                SELECT provider INTO prov FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
                CONTINUE WHEN NEW.provider IS NULL OR prov <> NEW.provider;
            END IF;
            
            IF (atype = 'lineitem_provider_attr_definition') THEN
                SELECT xpath INTO xpath_string FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
            ELSIF (atype = 'lineitem_marc_attr_definition') THEN
                SELECT xpath INTO xpath_string FROM acq.lineitem_marc_attr_definition WHERE id = adef.id;
            ELSIF (atype = 'lineitem_generated_attr_definition') THEN
                SELECT xpath INTO xpath_string FROM acq.lineitem_generated_attr_definition WHERE id = adef.id;
            END IF;
      
            xpath_string := REGEXP_REPLACE(xpath_string,$re$//?text\(\)$$re$,'');

            IF (adef.code = 'title' OR adef.code = 'author') THEN
                -- title and author should not be split
                -- FIXME: once oils_xpath can grok XPATH 2.0 functions, we can use
                -- string-join in the xpath and remove this special case
                SELECT extract_acq_marc_field(id, xpath_string, adef.remove) INTO value FROM acq.lineitem WHERE id = NEW.id;
                IF (value IS NOT NULL AND value <> '') THEN
                    INSERT INTO acq.lineitem_attr (lineitem, definition, attr_type, attr_name, attr_value)
                        VALUES (NEW.id, adef.id, atype, adef.code, value);
                END IF;
            ELSE
                pos := 1;

                LOOP
                    SELECT extract_acq_marc_field(id, xpath_string || '[' || pos || ']', adef.remove) INTO value FROM acq.lineitem WHERE id = NEW.id;
      
                    IF (value IS NOT NULL AND value <> '') THEN
                        INSERT INTO acq.lineitem_attr (lineitem, definition, attr_type, attr_name, attr_value)
                            VALUES (NEW.id, adef.id, atype, adef.code, value);
                    ELSE
                        EXIT;
                    END IF;

                    pos := pos + 1;
                END LOOP;
            END IF;

        END IF;

    END LOOP;

    RETURN NULL;
END;
$function$ LANGUAGE PLPGSQL;

UPDATE config.metabib_field SET label = name;
ALTER TABLE config.metabib_field ALTER COLUMN label SET NOT NULL;

ALTER TABLE config.metabib_field ADD CONSTRAINT metabib_field_field_class_fkey
	 FOREIGN KEY (field_class) REFERENCES config.metabib_class (name);

ALTER TABLE config.metabib_field DROP CONSTRAINT metabib_field_field_class_check;

ALTER TABLE config.metabib_field ADD CONSTRAINT metabib_field_format_fkey FOREIGN KEY (format) REFERENCES config.xml_transform (name);

CREATE TABLE config.metabib_search_alias (
    alias       TEXT    PRIMARY KEY,
    field_class TEXT    NOT NULL REFERENCES config.metabib_class (name),
    field       INT     REFERENCES config.metabib_field (id)
);

INSERT INTO config.metabib_search_alias (alias,field_class) VALUES ('kw','keyword');
INSERT INTO config.metabib_search_alias (alias,field_class) VALUES ('eg.keyword','keyword');
INSERT INTO config.metabib_search_alias (alias,field_class) VALUES ('dc.publisher','keyword');
INSERT INTO config.metabib_search_alias (alias,field_class) VALUES ('dc.identifier','keyword');
INSERT INTO config.metabib_search_alias (alias,field_class) VALUES ('bib.subjecttitle','keyword');
INSERT INTO config.metabib_search_alias (alias,field_class) VALUES ('bib.genre','keyword');
INSERT INTO config.metabib_search_alias (alias,field_class) VALUES ('bib.edition','keyword');
INSERT INTO config.metabib_search_alias (alias,field_class) VALUES ('srw.serverchoice','keyword');

INSERT INTO config.metabib_search_alias (alias,field_class) VALUES ('au','author');
INSERT INTO config.metabib_search_alias (alias,field_class) VALUES ('name','author');
INSERT INTO config.metabib_search_alias (alias,field_class) VALUES ('creator','author');
INSERT INTO config.metabib_search_alias (alias,field_class) VALUES ('eg.author','author');
INSERT INTO config.metabib_search_alias (alias,field_class) VALUES ('eg.name','author');
INSERT INTO config.metabib_search_alias (alias,field_class) VALUES ('dc.creator','author');
INSERT INTO config.metabib_search_alias (alias,field_class) VALUES ('dc.contributor','author');
INSERT INTO config.metabib_search_alias (alias,field_class) VALUES ('bib.name','author');
INSERT INTO config.metabib_search_alias (alias,field_class,field) VALUES ('bib.namepersonal','author',8);
INSERT INTO config.metabib_search_alias (alias,field_class,field) VALUES ('bib.namepersonalfamily','author',8);
INSERT INTO config.metabib_search_alias (alias,field_class,field) VALUES ('bib.namepersonalgiven','author',8);
INSERT INTO config.metabib_search_alias (alias,field_class,field) VALUES ('bib.namecorporate','author',7);
INSERT INTO config.metabib_search_alias (alias,field_class,field) VALUES ('bib.nameconference','author',9);

INSERT INTO config.metabib_search_alias (alias,field_class) VALUES ('ti','title');
INSERT INTO config.metabib_search_alias (alias,field_class) VALUES ('eg.title','title');
INSERT INTO config.metabib_search_alias (alias,field_class) VALUES ('dc.title','title');
INSERT INTO config.metabib_search_alias (alias,field_class,field) VALUES ('bib.titleabbreviated','title',2);
INSERT INTO config.metabib_search_alias (alias,field_class,field) VALUES ('bib.titleuniform','title',5);
INSERT INTO config.metabib_search_alias (alias,field_class,field) VALUES ('bib.titletranslated','title',3);
INSERT INTO config.metabib_search_alias (alias,field_class,field) VALUES ('bib.titlealternative','title',4);
INSERT INTO config.metabib_search_alias (alias,field_class,field) VALUES ('bib.title','title',2);

INSERT INTO config.metabib_search_alias (alias,field_class) VALUES ('su','subject');
INSERT INTO config.metabib_search_alias (alias,field_class) VALUES ('eg.subject','subject');
INSERT INTO config.metabib_search_alias (alias,field_class) VALUES ('dc.subject','subject');
INSERT INTO config.metabib_search_alias (alias,field_class,field) VALUES ('bib.subjectplace','subject',11);
INSERT INTO config.metabib_search_alias (alias,field_class,field) VALUES ('bib.subjectname','subject',12);
INSERT INTO config.metabib_search_alias (alias,field_class,field) VALUES ('bib.subjectoccupation','subject',16);

INSERT INTO config.metabib_search_alias (alias,field_class) VALUES ('se','series');
INSERT INTO config.metabib_search_alias (alias,field_class) VALUES ('eg.series','series');
INSERT INTO config.metabib_search_alias (alias,field_class,field) VALUES ('bib.titleseries','series',1);

UPDATE config.metabib_field SET facet_field=TRUE WHERE id = 1;
UPDATE config.metabib_field SET xpath=$$//mods32:mods/mods32:name[@type='corporate' and mods32:role/mods32:roleTerm[text()='creator']]$$, facet_field=TRUE, facet_xpath=$$*[local-name()='namePart']$$ WHERE id = 7;
UPDATE config.metabib_field SET xpath=$$//mods32:mods/mods32:name[@type='personal' and mods32:role/mods32:roleTerm[text()='creator']]$$, facet_field=TRUE, facet_xpath=$$*[local-name()='namePart']$$ WHERE id = 8;
UPDATE config.metabib_field SET xpath=$$//mods32:mods/mods32:name[@type='conference' and mods32:role/mods32:roleTerm[text()='creator']]$$, facet_field=TRUE, facet_xpath=$$*[local-name()='namePart']$$ WHERE id = 9;
UPDATE config.metabib_field SET xpath=$$//mods32:mods/mods32:name[@type='personal' and not(mods32:role)]$$, facet_field=TRUE, facet_xpath=$$*[local-name()='namePart']$$ WHERE id = 10;

UPDATE config.metabib_field SET facet_field=TRUE WHERE id = 11;
UPDATE config.metabib_field SET facet_field=TRUE , facet_xpath=$$*[local-name()='namePart']$$ WHERE id = 12;
UPDATE config.metabib_field SET facet_field=TRUE WHERE id = 13;
UPDATE config.metabib_field SET facet_field=TRUE WHERE id = 14;

CREATE INDEX metabib_rec_descriptor_item_type_idx ON metabib.rec_descriptor (item_type);
CREATE INDEX metabib_rec_descriptor_item_form_idx ON metabib.rec_descriptor (item_form);
CREATE INDEX metabib_rec_descriptor_bib_level_idx ON metabib.rec_descriptor (bib_level);
CREATE INDEX metabib_rec_descriptor_control_type_idx ON metabib.rec_descriptor (control_type);
CREATE INDEX metabib_rec_descriptor_char_encoding_idx ON metabib.rec_descriptor (char_encoding);
CREATE INDEX metabib_rec_descriptor_enc_level_idx ON metabib.rec_descriptor (enc_level);
CREATE INDEX metabib_rec_descriptor_audience_idx ON metabib.rec_descriptor (audience);
CREATE INDEX metabib_rec_descriptor_lit_form_idx ON metabib.rec_descriptor (lit_form);
CREATE INDEX metabib_rec_descriptor_cat_form_idx ON metabib.rec_descriptor (cat_form);
CREATE INDEX metabib_rec_descriptor_pub_status_idx ON metabib.rec_descriptor (pub_status);
CREATE INDEX metabib_rec_descriptor_item_lang_idx ON metabib.rec_descriptor (item_lang);
CREATE INDEX metabib_rec_descriptor_vr_format_idx ON metabib.rec_descriptor (vr_format);
CREATE INDEX metabib_rec_descriptor_date1_idx ON metabib.rec_descriptor (date1);
CREATE INDEX metabib_rec_descriptor_dates_idx ON metabib.rec_descriptor (date1,date2);

CREATE TABLE asset.opac_visible_copies (
  id        BIGINT primary key, -- copy id
  record    BIGINT,
  circ_lib  INTEGER
);
COMMENT ON TABLE asset.opac_visible_copies IS $$
Materialized view of copies that are visible in the OPAC, used by
search.query_parser_fts() to speed up OPAC visibility checks on large
databases.  Contents are maintained by a set of triggers.
$$;
CREATE INDEX opac_visible_copies_idx1 on asset.opac_visible_copies (record, circ_lib);

CREATE OR REPLACE FUNCTION search.query_parser_fts (

    param_search_ou INT,
    param_depth     INT,
    param_query     TEXT,
    param_statuses  INT[],
    param_locations INT[],
    param_offset    INT,
    param_check     INT,
    param_limit     INT,
    metarecord      BOOL,
    staff           BOOL
 
) RETURNS SETOF search.search_result AS $func$
DECLARE

    current_res         search.search_result%ROWTYPE;
    search_org_list     INT[];

    check_limit         INT;
    core_limit          INT;
    core_offset         INT;
    tmp_int             INT;

    core_result         RECORD;
    core_cursor         REFCURSOR;
    core_rel_query      TEXT;

    total_count         INT := 0;
    check_count         INT := 0;
    deleted_count       INT := 0;
    visible_count       INT := 0;
    excluded_count      INT := 0;

BEGIN

    check_limit := COALESCE( param_check, 1000 );
    core_limit  := COALESCE( param_limit, 25000 );
    core_offset := COALESCE( param_offset, 0 );

    -- core_skip_chk := COALESCE( param_skip_chk, 1 );

    IF param_search_ou > 0 THEN
        IF param_depth IS NOT NULL THEN
            SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou, param_depth );
        ELSE
            SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou );
        END IF;
    ELSIF param_search_ou < 0 THEN
        SELECT array_accum(distinct org_unit) INTO search_org_list FROM actor.org_lasso_map WHERE lasso = -param_search_ou;
    ELSIF param_search_ou = 0 THEN
        -- reserved for user lassos (ou_buckets/type='lasso') with ID passed in depth ... hack? sure.
    END IF;

    OPEN core_cursor FOR EXECUTE param_query;

    LOOP

        FETCH core_cursor INTO core_result;
        EXIT WHEN NOT FOUND;
        EXIT WHEN total_count >= core_limit;

        total_count := total_count + 1;

        CONTINUE WHEN total_count NOT BETWEEN  core_offset + 1 AND check_limit + core_offset;

        check_count := check_count + 1;

        PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM search.explode_array( core_result.records ) );
        IF NOT FOUND THEN
            -- RAISE NOTICE ' % were all deleted ... ', core_result.records;
            deleted_count := deleted_count + 1;
            CONTINUE;
        END IF;

        PERFORM 1
          FROM  biblio.record_entry b
                JOIN config.bib_source s ON (b.source = s.id)
          WHERE s.transcendant
                AND b.id IN ( SELECT * FROM search.explode_array( core_result.records ) );

        IF FOUND THEN
            -- RAISE NOTICE ' % were all transcendant ... ', core_result.records;
            visible_count := visible_count + 1;

            current_res.id = core_result.id;
            current_res.rel = core_result.rel;

            tmp_int := 1;
            IF metarecord THEN
                SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
            END IF;

            IF tmp_int = 1 THEN
                current_res.record = core_result.records[1];
            ELSE
                current_res.record = NULL;
            END IF;

            RETURN NEXT current_res;

            CONTINUE;
        END IF;

        PERFORM 1
          FROM  asset.call_number cn
                JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
                JOIN asset.uri uri ON (map.uri = uri.id)
          WHERE NOT cn.deleted
                AND cn.label = '##URI##'
                AND uri.active
                AND ( param_locations IS NULL OR array_upper(param_locations, 1) IS NULL )
                AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
                AND cn.owning_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
          LIMIT 1;

        IF FOUND THEN
            -- RAISE NOTICE ' % have at least one URI ... ', core_result.records;
            visible_count := visible_count + 1;

            current_res.id = core_result.id;
            current_res.rel = core_result.rel;

            tmp_int := 1;
            IF metarecord THEN
                SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
            END IF;

            IF tmp_int = 1 THEN
                current_res.record = core_result.records[1];
            ELSE
                current_res.record = NULL;
            END IF;

            RETURN NEXT current_res;

            CONTINUE;
        END IF;

        IF param_statuses IS NOT NULL AND array_upper(param_statuses, 1) > 0 THEN

            PERFORM 1
              FROM  asset.call_number cn
                    JOIN asset.copy cp ON (cp.call_number = cn.id)
              WHERE NOT cn.deleted
                    AND NOT cp.deleted
                    AND cp.status IN ( SELECT * FROM search.explode_array( param_statuses ) )
                    AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
                    AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
              LIMIT 1;

            IF NOT FOUND THEN
                -- RAISE NOTICE ' % were all status-excluded ... ', core_result.records;
                excluded_count := excluded_count + 1;
                CONTINUE;
            END IF;

        END IF;

        IF param_locations IS NOT NULL AND array_upper(param_locations, 1) > 0 THEN

            PERFORM 1
              FROM  asset.call_number cn
                    JOIN asset.copy cp ON (cp.call_number = cn.id)
              WHERE NOT cn.deleted
                    AND NOT cp.deleted
                    AND cp.location IN ( SELECT * FROM search.explode_array( param_locations ) )
                    AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
                    AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
              LIMIT 1;

            IF NOT FOUND THEN
                -- RAISE NOTICE ' % were all copy_location-excluded ... ', core_result.records;
                excluded_count := excluded_count + 1;
                CONTINUE;
            END IF;

        END IF;

        IF staff IS NULL OR NOT staff THEN

            PERFORM 1
              FROM  asset.opac_visible_copies
              WHERE circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
                    AND record IN ( SELECT * FROM search.explode_array( core_result.records ) )
              LIMIT 1;

            IF NOT FOUND THEN
                -- RAISE NOTICE ' % were all visibility-excluded ... ', core_result.records;
                excluded_count := excluded_count + 1;
                CONTINUE;
            END IF;

        ELSE

            PERFORM 1
              FROM  asset.call_number cn
                    JOIN asset.copy cp ON (cp.call_number = cn.id)
                    JOIN actor.org_unit a ON (cp.circ_lib = a.id)
              WHERE NOT cn.deleted
                    AND NOT cp.deleted
                    AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
                    AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
              LIMIT 1;

            IF NOT FOUND THEN

                PERFORM 1
                  FROM  asset.call_number cn
                  WHERE cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
                  LIMIT 1;

                IF FOUND THEN
                    -- RAISE NOTICE ' % were all visibility-excluded ... ', core_result.records;
                    excluded_count := excluded_count + 1;
                    CONTINUE;
                END IF;

            END IF;

        END IF;

        visible_count := visible_count + 1;

        current_res.id = core_result.id;
        current_res.rel = core_result.rel;

        tmp_int := 1;
        IF metarecord THEN
            SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
        END IF;

        IF tmp_int = 1 THEN
            current_res.record = core_result.records[1];
        ELSE
            current_res.record = NULL;
        END IF;

        RETURN NEXT current_res;

        IF visible_count % 1000 = 0 THEN
            -- RAISE NOTICE ' % visible so far ... ', visible_count;
        END IF;

    END LOOP;

    current_res.id = NULL;
    current_res.rel = NULL;
    current_res.record = NULL;
    current_res.total = total_count;
    current_res.checked = check_count;
    current_res.deleted = deleted_count;
    current_res.visible = visible_count;
    current_res.excluded = excluded_count;

    CLOSE core_cursor;

    RETURN NEXT current_res;

END;
$func$ LANGUAGE PLPGSQL;

ALTER TABLE biblio.record_entry ADD COLUMN owner INT;
ALTER TABLE biblio.record_entry
	 ADD CONSTRAINT biblio_record_entry_owner_fkey FOREIGN KEY (owner)
	 REFERENCES actor.org_unit (id)
	 DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE biblio.record_entry ADD COLUMN share_depth INT;

ALTER TABLE auditor.biblio_record_entry_history ADD COLUMN owner INT;
ALTER TABLE auditor.biblio_record_entry_history ADD COLUMN share_depth INT;

DROP VIEW auditor.biblio_record_entry_lifecycle;

SELECT auditor.create_auditor_lifecycle( 'biblio', 'record_entry' );

CREATE OR REPLACE FUNCTION public.first_word ( TEXT ) RETURNS TEXT AS $$
        SELECT COALESCE(SUBSTRING( $1 FROM $_$^\S+$_$), '');
$$ LANGUAGE SQL STRICT IMMUTABLE;

CREATE OR REPLACE FUNCTION public.normalize_space( TEXT ) RETURNS TEXT AS $$
    SELECT regexp_replace(regexp_replace(regexp_replace($1, E'\\n', ' ', 'g'), E'(?:^\\s+)|(\\s+$)', '', 'g'), E'\\s+', ' ', 'g');
$$ LANGUAGE SQL STRICT IMMUTABLE;

CREATE OR REPLACE FUNCTION public.lowercase( TEXT ) RETURNS TEXT AS $$
    return lc(shift);
$$ LANGUAGE PLPERLU STRICT IMMUTABLE;

CREATE OR REPLACE FUNCTION public.uppercase( TEXT ) RETURNS TEXT AS $$
    return uc(shift);
$$ LANGUAGE PLPERLU STRICT IMMUTABLE;

CREATE OR REPLACE FUNCTION public.remove_diacritics( TEXT ) RETURNS TEXT AS $$
    use Unicode::Normalize;

    my $x = NFD(shift);
    $x =~ s/\pM+//go;
    return $x;

$$ LANGUAGE PLPERLU STRICT IMMUTABLE;

CREATE OR REPLACE FUNCTION public.entityize( TEXT ) RETURNS TEXT AS $$
    use Unicode::Normalize;

    my $x = NFC(shift);
    $x =~ s/([\x{0080}-\x{fffd}])/sprintf('&#x%X;',ord($1))/sgoe;
    return $x;

$$ LANGUAGE PLPERLU STRICT IMMUTABLE;

CREATE OR REPLACE FUNCTION actor.org_unit_ancestor_setting( setting_name TEXT, org_id INT ) RETURNS SETOF actor.org_unit_setting AS $$
DECLARE
    setting RECORD;
    cur_org INT;
BEGIN
    cur_org := org_id;
    LOOP
        SELECT INTO setting * FROM actor.org_unit_setting WHERE org_unit = cur_org AND name = setting_name;
        IF FOUND THEN
            RETURN NEXT setting;
        END IF;
        SELECT INTO cur_org parent_ou FROM actor.org_unit WHERE id = cur_org;
        EXIT WHEN cur_org IS NULL;
    END LOOP;
    RETURN;
END;
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION acq.extract_holding_attr_table (lineitem int, tag text) RETURNS SETOF acq.flat_lineitem_holding_subfield AS $$
DECLARE
    counter INT;
    lida    acq.flat_lineitem_holding_subfield%ROWTYPE;
BEGIN

    SELECT  COUNT(*) INTO counter
      FROM  oils_xpath_table(
                'id',
                'marc',
                'acq.lineitem',
                '//*[@tag="' || tag || '"]',
                'id=' || lineitem
            ) as t(i int,c text);

    FOR i IN 1 .. counter LOOP
        FOR lida IN
            SELECT  *
              FROM  (   SELECT  id,i,t,v
                          FROM  oils_xpath_table(
                                    'id',
                                    'marc',
                                    'acq.lineitem',
                                    '//*[@tag="' || tag || '"][position()=' || i || ']/*/@code|' ||
                                        '//*[@tag="' || tag || '"][position()=' || i || ']/*[@code]',
                                    'id=' || lineitem
                                ) as t(id int,t text,v text)
                    )x
        LOOP
            RETURN NEXT lida;
        END LOOP;
    END LOOP;

    RETURN;
END;
$$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION oils_i18n_xlate ( keytable TEXT, keyclass TEXT, keycol TEXT, identcol TEXT, keyvalue TEXT, raw_locale TEXT ) RETURNS TEXT AS $func$
DECLARE
    locale      TEXT := REGEXP_REPLACE( REGEXP_REPLACE( raw_locale, E'[;, ].+$', '' ), E'_', '-', 'g' );
    language    TEXT := REGEXP_REPLACE( locale, E'-.+$', '' );
    result      config.i18n_core%ROWTYPE;
    fallback    TEXT;
    keyfield    TEXT := keyclass || '.' || keycol;
BEGIN

    -- Try the full locale
    SELECT  * INTO result
      FROM  config.i18n_core
      WHERE fq_field = keyfield
            AND identity_value = keyvalue
            AND translation = locale;

    -- Try just the language
    IF NOT FOUND THEN
        SELECT  * INTO result
          FROM  config.i18n_core
          WHERE fq_field = keyfield
                AND identity_value = keyvalue
                AND translation = language;
    END IF;

    -- Fall back to the string we passed in in the first place
    IF NOT FOUND THEN
    EXECUTE
            'SELECT ' ||
                keycol ||
            ' FROM ' || keytable ||
            ' WHERE ' || identcol || ' = ' || quote_literal(keyvalue)
                INTO fallback;
        RETURN fallback;
    END IF;

    RETURN result.string;
END;
$func$ LANGUAGE PLPGSQL STABLE;

SELECT auditor.create_auditor ( 'acq', 'invoice' );

SELECT auditor.create_auditor ( 'acq', 'invoice_item' );

SELECT auditor.create_auditor ( 'acq', 'invoice_entry' );

INSERT INTO acq.cancel_reason ( id, org_unit, label, description, keep_debits ) VALUES (
    3, 1, 'delivered_but_lost',
    oils_i18n_gettext( 2, 'Delivered but not received; presumed lost', 'acqcr', 'label' ), TRUE );

CREATE TABLE config.global_flag (
    label   TEXT    NOT NULL
) INHERITS (config.internal_flag);
ALTER TABLE config.global_flag ADD PRIMARY KEY (name);

INSERT INTO config.global_flag (name, label, enabled)
    VALUES (
        'cat.bib.use_id_for_tcn',
        oils_i18n_gettext(
            'cat.bib.use_id_for_tcn',
            'Cat: Use Internal ID for TCN Value',
            'cgf', 
            'label'
        ),
        TRUE
    );

-- resolves performance issue noted by EG Indiana

CREATE INDEX scecm_owning_copy_idx ON asset.stat_cat_entry_copy_map(owning_copy);

INSERT INTO config.metabib_class ( name, label ) VALUES ( 'identifier', oils_i18n_gettext('identifier', 'Identifier', 'cmc', 'name') );

INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_field ) VALUES
    (17, 'identifier', 'accession', oils_i18n_gettext(17, 'Accession Number', 'cmf', 'label'), 'marcxml', $$//marcxml:datafield[tag="001"]/text()$$, TRUE );
INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_field ) VALUES
    (18, 'identifier', 'isbn', oils_i18n_gettext(18, 'ISBN', 'cmf', 'label'), 'marcxml', $$//marcxml:datafield[tag="020"]/marcxml:subfield[code="a" or code="z"]/text()$$, TRUE );
INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_field ) VALUES
    (19, 'identifier', 'issn', oils_i18n_gettext(19, 'ISSN', 'cmf', 'label'), 'marcxml', $$//marcxml:datafield[tag="022"]/marcxml:subfield[code="a" or code="z"]/text()$$, TRUE );
INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_field ) VALUES
    (20, 'identifier', 'upc', oils_i18n_gettext(20, 'UPC', 'cmf', 'label'), 'marcxml', $$//marcxml:datafield[tag="024" and ind1="1"]/marcxml:subfield[code="a" or code="z"]/text()$$, TRUE );
INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_field ) VALUES
    (21, 'identifier', 'ismn', oils_i18n_gettext(21, 'ISMN', 'cmf', 'label'), 'marcxml', $$//marcxml:datafield[tag="024" and ind1="2"]/marcxml:subfield[code="a" or code="z"]/text()$$, TRUE );
INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_field ) VALUES
    (22, 'identifier', 'ean', oils_i18n_gettext(22, 'EAN', 'cmf', 'label'), 'marcxml', $$//marcxml:datafield[tag="024" and ind1="3"]/marcxml:subfield[code="a" or code="z"]/text()$$, TRUE );
INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_field ) VALUES
    (23, 'identifier', 'isrc', oils_i18n_gettext(23, 'ISRC', 'cmf', 'label'), 'marcxml', $$//marcxml:datafield[tag="024" and ind1="0"]/marcxml:subfield[code="a" or code="z"]/text()$$, TRUE );
INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_field ) VALUES
    (24, 'identifier', 'sici', oils_i18n_gettext(24, 'SICI', 'cmf', 'label'), 'marcxml', $$//marcxml:datafield[tag="024" and ind1="4"]/marcxml:subfield[code="a" or code="z"]/text()$$, TRUE );
INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_field ) VALUES
    (25, 'identifier', 'bibcn', oils_i18n_gettext(25, 'Local Free-Text Call Number', 'cmf', 'label'), 'marcxml', $$//marcxml:datafield[tag="099"]//text()$$, TRUE );

SELECT SETVAL('config.metabib_field_id_seq'::TEXT, (SELECT MAX(id) FROM config.metabib_field), TRUE);
 

DELETE FROM config.metabib_search_alias WHERE alias = 'dc.identifier';

INSERT INTO config.metabib_search_alias (alias,field_class) VALUES ('id','identifier');
INSERT INTO config.metabib_search_alias (alias,field_class) VALUES ('dc.identifier','identifier');
INSERT INTO config.metabib_search_alias (alias,field_class,field) VALUES ('eg.isbn','identifier', 18);
INSERT INTO config.metabib_search_alias (alias,field_class,field) VALUES ('eg.issn','identifier', 19);
INSERT INTO config.metabib_search_alias (alias,field_class,field) VALUES ('eg.upc','identifier', 20);
INSERT INTO config.metabib_search_alias (alias,field_class,field) VALUES ('eg.callnumber','identifier', 25);

CREATE TABLE metabib.identifier_field_entry (
	id		BIGSERIAL	PRIMARY KEY,
	source		BIGINT		NOT NULL,
	field		INT		NOT NULL,
	value		TEXT		NOT NULL,
	index_vector	tsvector	NOT NULL
);
CREATE TRIGGER metabib_identifier_field_entry_fti_trigger
	BEFORE UPDATE OR INSERT ON metabib.identifier_field_entry
	FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword');

CREATE INDEX metabib_identifier_field_entry_index_vector_idx ON metabib.identifier_field_entry USING GIST (index_vector);
CREATE INDEX metabib_identifier_field_entry_value_idx ON metabib.identifier_field_entry
    (SUBSTRING(value,1,1024)) WHERE index_vector = ''::TSVECTOR;
CREATE INDEX metabib_identifier_field_entry_source_idx ON metabib.identifier_field_entry (source);

ALTER TABLE metabib.identifier_field_entry ADD CONSTRAINT metabib_identifier_field_entry_source_pkey
    FOREIGN KEY (source) REFERENCES biblio.record_entry (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE metabib.identifier_field_entry ADD CONSTRAINT metabib_identifier_field_entry_field_pkey
    FOREIGN KEY (field) REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;

CREATE OR REPLACE FUNCTION public.translate_isbn1013( TEXT ) RETURNS TEXT AS $func$
    use Business::ISBN;
    use strict;
    use warnings;

    # For each ISBN found in a single string containing a set of ISBNs:
    #   * Normalize an incoming ISBN to have the correct checksum and no hyphens
    #   * Convert an incoming ISBN10 or ISBN13 to its counterpart and return

    my $input = shift;
    my $output = '';

    foreach my $word (split(/\s/, $input)) {
        my $isbn = Business::ISBN->new($word);

        # First check the checksum; if it is not valid, fix it and add the original
        # bad-checksum ISBN to the output
        if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
            $output .= $isbn->isbn() . " ";
            $isbn->fix_checksum();
        }

        # If we now have a valid ISBN, convert it to its counterpart ISBN10/ISBN13
        # and add the normalized original ISBN to the output
        if ($isbn && $isbn->is_valid()) {
            my $isbn_xlated = ($isbn->type eq "ISBN13") ? $isbn->as_isbn10 : $isbn->as_isbn13;
            $output .= $isbn->isbn . " ";

            # If we successfully converted the ISBN to its counterpart, add the
            # converted ISBN to the output as well
            $output .= ($isbn_xlated->isbn . " ") if ($isbn_xlated);
        }
    }
    return $output if $output;

    # If there were no valid ISBNs, just return the raw input
    return $input;
$func$ LANGUAGE PLPERLU;

COMMENT ON FUNCTION public.translate_isbn1013(TEXT) IS $$
/*
 * Copyright (C) 2010 Merrimack Valley Library Consortium
 * Jason Stephenson <jstephenson@mvlc.org>
 * Copyright (C) 2010 Laurentian University
 * Dan Scott <dscott@laurentian.ca>
 *
 * The translate_isbn1013 function takes an input ISBN and returns the
 * following in a single space-delimited string if the input ISBN is valid:
 *   - The normalized input ISBN (hyphens stripped)
 *   - The normalized input ISBN with a fixed checksum if the checksum was bad
 *   - The ISBN converted to its ISBN10 or ISBN13 counterpart, if possible
 */
$$;

UPDATE config.metabib_field SET facet_field = FALSE WHERE id BETWEEN 17 AND 25;
UPDATE config.metabib_field SET xpath = REPLACE(xpath,'marcxml','marc') WHERE id BETWEEN 17 AND 25;
UPDATE config.metabib_field SET xpath = REPLACE(xpath,'tag','@tag') WHERE id BETWEEN 17 AND 25;
UPDATE config.metabib_field SET xpath = REPLACE(xpath,'code','@code') WHERE id BETWEEN 17 AND 25;
UPDATE config.metabib_field SET xpath = REPLACE(xpath,'"',E'\'') WHERE id BETWEEN 17 AND 25;
UPDATE config.metabib_field SET xpath = REPLACE(xpath,'/text()','') WHERE id BETWEEN 17 AND 24;

INSERT INTO config.index_normalizer (name, description, func, param_count) VALUES (
	'ISBN 10/13 conversion',
	'Translate ISBN10 to ISBN13, and vice versa, for indexing purposes.',
	'translate_isbn1013',
	0
);

INSERT INTO config.index_normalizer (name, description, func, param_count) VALUES (
	'Replace',
	'Replace all occurences of first parameter in the string with the second parameter.',
	'replace',
	2
);

INSERT INTO config.metabib_field_index_norm_map (field,norm,pos)
    SELECT  m.id, i.id, 1
      FROM  config.metabib_field m,
            config.index_normalizer i
      WHERE i.func IN ('first_word')
            AND m.id IN (18);

INSERT INTO config.metabib_field_index_norm_map (field,norm,pos)
    SELECT  m.id, i.id, 2
      FROM  config.metabib_field m,
            config.index_normalizer i
      WHERE i.func IN ('translate_isbn1013')
            AND m.id IN (18);

INSERT INTO config.metabib_field_index_norm_map (field,norm,params)
    SELECT  m.id, i.id, $$['-','']$$
      FROM  config.metabib_field m,
            config.index_normalizer i
      WHERE i.func IN ('replace')
            AND m.id IN (19);

INSERT INTO config.metabib_field_index_norm_map (field,norm,params)
    SELECT  m.id, i.id, $$[' ','']$$
      FROM  config.metabib_field m,
            config.index_normalizer i
      WHERE i.func IN ('replace')
            AND m.id IN (19);

DELETE FROM config.metabib_field_index_norm_map WHERE norm IN (1,2) and field > 16;

UPDATE  config.metabib_field_index_norm_map
  SET   params = REPLACE(params,E'\'','"')
  WHERE params IS NOT NULL AND params <> '';

DROP TRIGGER IF EXISTS metabib_identifier_field_entry_fti_trigger ON metabib.identifier_field_entry;

CREATE TEXT SEARCH CONFIGURATION identifier ( COPY = title );

ALTER TABLE config.circ_modifier
	ADD COLUMN avg_wait_time INTERVAL;

--CREATE TABLE actor.usr_password_reset (
--  id SERIAL PRIMARY KEY,
--  uuid TEXT NOT NULL, 
--  usr BIGINT NOT NULL REFERENCES actor.usr(id) DEFERRABLE INITIALLY DEFERRED, 
--  request_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), 
--  has_been_reset BOOL NOT NULL DEFAULT false
--);
--COMMENT ON TABLE actor.usr_password_reset IS $$
--/*
-- * Copyright (C) 2010 Laurentian University
-- * Dan Scott <dscott@laurentian.ca>
-- *
-- * Self-serve password reset requests
-- *
-- * ****
-- *
-- * 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.
-- */
--$$;
--CREATE UNIQUE INDEX actor_usr_password_reset_uuid_idx ON actor.usr_password_reset (uuid);
--CREATE INDEX actor_usr_password_reset_usr_idx ON actor.usr_password_reset (usr);
--CREATE INDEX actor_usr_password_reset_request_time_idx ON actor.usr_password_reset (request_time);
--CREATE INDEX actor_usr_password_reset_has_been_reset_idx ON actor.usr_password_reset (has_been_reset);

-- Use the identifier search class tsconfig
DROP TRIGGER IF EXISTS metabib_identifier_field_entry_fti_trigger ON metabib.identifier_field_entry;
CREATE TRIGGER metabib_identifier_field_entry_fti_trigger
    BEFORE INSERT OR UPDATE ON metabib.identifier_field_entry
    FOR EACH ROW
    EXECUTE PROCEDURE public.oils_tsearch2('identifier');

INSERT INTO config.global_flag (name,label,enabled)
    VALUES ('history.circ.retention_age',oils_i18n_gettext('history.circ.retention_age', 'Historical Circulation Retention Age', 'cgf', 'label'), TRUE);
INSERT INTO config.global_flag (name,label,enabled)
    VALUES ('history.circ.retention_count',oils_i18n_gettext('history.circ.retention_count', 'Historical Circulations per Copy', 'cgf', 'label'), TRUE);

-- turn a JSON scalar into an SQL TEXT value
CREATE OR REPLACE FUNCTION oils_json_to_text( TEXT ) RETURNS TEXT AS $f$
    use JSON::XS;                    
    my $json = shift();
    my $txt;
    eval { $txt = JSON::XS->new->allow_nonref->decode( $json ) };   
    return undef if ($@);
    return $txt
$f$ LANGUAGE PLPERLU;

-- Return the list of circ chain heads in xact_start order that the user has chosen to "retain"
CREATE OR REPLACE FUNCTION action.usr_visible_circs (usr_id INT) RETURNS SETOF action.circulation AS $func$
DECLARE
    c               action.circulation%ROWTYPE;
    view_age        INTERVAL;
    usr_view_age    actor.usr_setting%ROWTYPE;
    usr_view_start  actor.usr_setting%ROWTYPE;
BEGIN
    SELECT * INTO usr_view_age FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.circ.retention_age';
    SELECT * INTO usr_view_start FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.circ.retention_start';

    IF usr_view_age.value IS NOT NULL AND usr_view_start.value IS NOT NULL THEN
        -- User opted in and supplied a retention age
        IF oils_json_to_text(usr_view_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ) THEN
            view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
        ELSE
            view_age := oils_json_to_text(usr_view_age.value)::INTERVAL;
        END IF;
    ELSIF usr_view_start.value IS NOT NULL THEN
        -- User opted in
        view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
    ELSE
        -- User did not opt in
        RETURN;
    END IF;

    FOR c IN
        SELECT  *
          FROM  action.circulation
          WHERE usr = usr_id
                AND parent_circ IS NULL
                AND xact_start > NOW() - view_age
          ORDER BY xact_start
    LOOP
        RETURN NEXT c;
    END LOOP;

    RETURN;
END;
$func$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION action.purge_circulations () RETURNS INT AS $func$
DECLARE
    usr_keep_age    actor.usr_setting%ROWTYPE;
    usr_keep_start  actor.usr_setting%ROWTYPE;
    org_keep_age    INTERVAL;
    org_keep_count  INT;

    keep_age        INTERVAL;

    target_acp      RECORD;
    circ_chain_head action.circulation%ROWTYPE;
    circ_chain_tail action.circulation%ROWTYPE;

    purge_position  INT;
    count_purged    INT;
BEGIN

    count_purged := 0;

    SELECT value::INTERVAL INTO org_keep_age FROM config.global_flag WHERE name = 'history.circ.retention_age' AND enabled;

    SELECT value::INT INTO org_keep_count FROM config.global_flag WHERE name = 'history.circ.retention_count' AND enabled;
    IF org_keep_count IS NULL THEN
        RETURN count_purged; -- Gimme a count to keep, or I keep them all, forever
    END IF;

    -- First, find copies with more than keep_count non-renewal circs
    FOR target_acp IN
        SELECT  target_copy,
                COUNT(*) AS total_real_circs
          FROM  action.circulation
          WHERE parent_circ IS NULL
                AND xact_finish IS NOT NULL
          GROUP BY target_copy
          HAVING COUNT(*) > org_keep_count
    LOOP
        purge_position := 0;
        -- And, for those, select circs that are finished and older than keep_age
        FOR circ_chain_head IN
            SELECT  *
              FROM  action.circulation
              WHERE target_copy = target_acp.target_copy
                    AND parent_circ IS NULL
              ORDER BY xact_start
        LOOP

            -- Stop once we've purged enough circs to hit org_keep_count
            EXIT WHEN target_acp.total_real_circs - purge_position <= org_keep_count;

            SELECT * INTO circ_chain_tail FROM action.circ_chain(circ_chain_head.id) ORDER BY xact_start DESC LIMIT 1;
            EXIT WHEN circ_chain_tail.xact_finish IS NULL;

            -- Now get the user settings, if any, to block purging if the user wants to keep more circs
            usr_keep_age.value := NULL;
            SELECT * INTO usr_keep_age FROM actor.usr_setting WHERE usr = circ_chain_head.usr AND name = 'history.circ.retention_age';

            usr_keep_start.value := NULL;
            SELECT * INTO usr_keep_start FROM actor.usr_setting WHERE usr = circ_chain_head.usr AND name = 'history.circ.retention_start';

            IF usr_keep_age.value IS NOT NULL AND usr_keep_start.value IS NOT NULL THEN
                IF oils_json_to_text(usr_keep_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ) THEN
                    keep_age := AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ);
                ELSE
                    keep_age := oils_json_to_text(usr_keep_age.value)::INTERVAL;
                END IF;
            ELSIF usr_keep_start.value IS NOT NULL THEN
                keep_age := AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ);
            ELSE
                keep_age := COALESCE( org_keep_age::INTERVAL, '2000 years'::INTERVAL );
            END IF;

            EXIT WHEN AGE(NOW(), circ_chain_tail.xact_finish) < keep_age;

            -- We've passed the purging tests, purge the circ chain starting at the end
            DELETE FROM action.circulation WHERE id = circ_chain_tail.id;
            WHILE circ_chain_tail.parent_circ IS NOT NULL LOOP
                SELECT * INTO circ_chain_tail FROM action.circulation WHERE id = circ_chain_tail.parent_circ;
                DELETE FROM action.circulation WHERE id = circ_chain_tail.id;
            END LOOP;

            count_purged := count_purged + 1;
            purge_position := purge_position + 1;

        END LOOP;
    END LOOP;
END;
$func$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION action.usr_visible_holds (usr_id INT) RETURNS SETOF action.hold_request AS $func$
DECLARE
    h               action.hold_request%ROWTYPE;
    view_age        INTERVAL;
    view_count      INT;
    usr_view_count  actor.usr_setting%ROWTYPE;
    usr_view_age    actor.usr_setting%ROWTYPE;
    usr_view_start  actor.usr_setting%ROWTYPE;
BEGIN
    SELECT * INTO usr_view_count FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_count';
    SELECT * INTO usr_view_age FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_age';
    SELECT * INTO usr_view_start FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_start';

    FOR h IN
        SELECT  *
          FROM  action.hold_request
          WHERE usr = usr_id
                AND fulfillment_time IS NULL
                AND cancel_time IS NULL
          ORDER BY request_time DESC
    LOOP
        RETURN NEXT h;
    END LOOP;

    IF usr_view_start.value IS NULL THEN
        RETURN;
    END IF;

    IF usr_view_age.value IS NOT NULL THEN
        -- User opted in and supplied a retention age
        IF oils_json_to_string(usr_view_age.value)::INTERVAL > AGE(NOW(), oils_json_to_string(usr_view_start.value)::TIMESTAMPTZ) THEN
            view_age := AGE(NOW(), oils_json_to_string(usr_view_start.value)::TIMESTAMPTZ);
        ELSE
            view_age := oils_json_to_string(usr_view_age.value)::INTERVAL;
        END IF;
    ELSE
        -- User opted in
        view_age := AGE(NOW(), oils_json_to_string(usr_view_start.value)::TIMESTAMPTZ);
    END IF;

    IF usr_view_count.value IS NOT NULL THEN
        view_count := oils_json_to_text(usr_view_count.value)::INT;
    ELSE
        view_count := 1000;
    END IF;

    -- show some fulfilled/canceled holds
    FOR h IN
        SELECT  *
          FROM  action.hold_request
          WHERE usr = usr_id
                AND ( fulfillment_time IS NOT NULL OR cancel_time IS NOT NULL )
                AND request_time > NOW() - view_age
          ORDER BY request_time DESC
          LIMIT view_count
    LOOP
        RETURN NEXT h;
    END LOOP;

    RETURN;
END;
$func$ LANGUAGE PLPGSQL;







CREATE TABLE asset.copy_template (
	id             SERIAL   PRIMARY KEY,
	owning_lib     INT      NOT NULL
	                        REFERENCES actor.org_unit (id)
	                        DEFERRABLE INITIALLY DEFERRED,
	creator        BIGINT   NOT NULL
	                        REFERENCES actor.usr (id)
	                        DEFERRABLE INITIALLY DEFERRED,
	editor         BIGINT   NOT NULL
	                        REFERENCES actor.usr (id)
	                        DEFERRABLE INITIALLY DEFERRED,
	create_date    TIMESTAMP WITH TIME ZONE    DEFAULT NOW(),
	edit_date      TIMESTAMP WITH TIME ZONE    DEFAULT NOW(),
	name           TEXT     NOT NULL,
	-- columns above this point are attributes of the template itself
	-- columns after this point are attributes of the copy this template modifies/creates
	circ_lib       INT      REFERENCES actor.org_unit (id)
	                        DEFERRABLE INITIALLY DEFERRED,
	status         INT      REFERENCES config.copy_status (id)
	                        DEFERRABLE INITIALLY DEFERRED,
	location       INT      REFERENCES asset.copy_location (id)
	                        DEFERRABLE INITIALLY DEFERRED,
	loan_duration  INT      CONSTRAINT valid_loan_duration CHECK (
	                            loan_duration IS NULL OR loan_duration IN (1,2,3)),
	fine_level     INT      CONSTRAINT valid_fine_level CHECK (
	                            fine_level IS NULL OR loan_duration IN (1,2,3)),
	age_protect    INT,
	circulate      BOOL,
	deposit        BOOL,
	ref            BOOL,
	holdable       BOOL,
	deposit_amount NUMERIC(6,2),
	price          NUMERIC(8,2),
	circ_modifier  TEXT,
	circ_as_type   TEXT,
	alert_message  TEXT,
	opac_visible   BOOL,
	floating       BOOL,
	mint_condition BOOL
);

CREATE TABLE serial.subscription (
	id                     SERIAL       PRIMARY KEY,
	owning_lib             INT          NOT NULL DEFAULT 1
	                                    REFERENCES actor.org_unit (id)
	                                    ON DELETE SET NULL
	                                    DEFERRABLE INITIALLY DEFERRED,
	start_date             TIMESTAMP WITH TIME ZONE     NOT NULL,
	end_date               TIMESTAMP WITH TIME ZONE,    -- interpret NULL as current subscription
	record_entry           BIGINT       REFERENCES biblio.record_entry (id)
	                                    ON DELETE SET NULL
	                                    DEFERRABLE INITIALLY DEFERRED,
	expected_date_offset   INTERVAL
	-- acquisitions/business-side tables link to here
);
CREATE INDEX serial_subscription_record_idx ON serial.subscription (record_entry);
CREATE INDEX serial_subscription_owner_idx ON serial.subscription (owning_lib);

--at least one distribution per org_unit holding issues
CREATE TABLE serial.distribution (
	id                    SERIAL  PRIMARY KEY,
	record_entry          BIGINT  REFERENCES serial.record_entry (id)
	                              ON DELETE SET NULL
	                              DEFERRABLE INITIALLY DEFERRED,
	summary_method        TEXT    CONSTRAINT sdist_summary_method_check CHECK (
	                                  summary_method IS NULL
	                                  OR summary_method IN ( 'add_to_sre',
	                                  'merge_with_sre', 'use_sre_only',
	                                  'use_sdist_only')),
	subscription          INT     NOT NULL
	                              REFERENCES serial.subscription (id)
								  ON DELETE CASCADE
								  DEFERRABLE INITIALLY DEFERRED,
	holding_lib           INT     NOT NULL
	                              REFERENCES actor.org_unit (id)
								  DEFERRABLE INITIALLY DEFERRED,
	label                 TEXT    NOT NULL,
	receive_call_number   BIGINT  REFERENCES asset.call_number (id)
	                              DEFERRABLE INITIALLY DEFERRED,
	receive_unit_template INT     REFERENCES asset.copy_template (id)
	                              DEFERRABLE INITIALLY DEFERRED,
	bind_call_number      BIGINT  REFERENCES asset.call_number (id)
	                              DEFERRABLE INITIALLY DEFERRED,
	bind_unit_template    INT     REFERENCES asset.copy_template (id)
	                              DEFERRABLE INITIALLY DEFERRED,
	unit_label_prefix     TEXT,
	unit_label_suffix     TEXT
);
CREATE INDEX serial_distribution_sub_idx ON serial.distribution (subscription);
CREATE INDEX serial_distribution_holding_lib_idx ON serial.distribution (holding_lib);

CREATE UNIQUE INDEX one_dist_per_sre_idx ON serial.distribution (record_entry);

CREATE TABLE serial.stream (
	id              SERIAL  PRIMARY KEY,
	distribution    INT     NOT NULL
	                        REFERENCES serial.distribution (id)
	                        ON DELETE CASCADE
	                        DEFERRABLE INITIALLY DEFERRED,
	routing_label   TEXT
);
CREATE INDEX serial_stream_dist_idx ON serial.stream (distribution);

CREATE UNIQUE INDEX label_once_per_dist
	ON serial.stream (distribution, routing_label)
	WHERE routing_label IS NOT NULL;

CREATE TABLE serial.routing_list_user (
	id             SERIAL       PRIMARY KEY,
	stream         INT          NOT NULL
	                            REFERENCES serial.stream
	                            ON DELETE CASCADE
	                            DEFERRABLE INITIALLY DEFERRED,
	pos            INT          NOT NULL DEFAULT 1,
	reader         INT          REFERENCES actor.usr
	                            ON DELETE CASCADE
	                            DEFERRABLE INITIALLY DEFERRED,
	department     TEXT,
	note           TEXT,
	CONSTRAINT one_pos_per_routing_list UNIQUE ( stream, pos ),
	CONSTRAINT reader_or_dept CHECK
	(
	    -- Recipient is a person or a department, but not both
		(reader IS NOT NULL AND department IS NULL) OR
		(reader IS NULL AND department IS NOT NULL)
	)
);
CREATE INDEX serial_routing_list_user_stream_idx ON serial.routing_list_user (stream);
CREATE INDEX serial_routing_list_user_reader_idx ON serial.routing_list_user (reader);

CREATE TABLE serial.caption_and_pattern (
	id           SERIAL       PRIMARY KEY,
	subscription INT          NOT NULL REFERENCES serial.subscription (id)
	                          ON DELETE CASCADE
	                          DEFERRABLE INITIALLY DEFERRED,
	type         TEXT         NOT NULL
	                          CONSTRAINT cap_type CHECK ( type in
	                          ( 'basic', 'supplement', 'index' )),
	create_date  TIMESTAMPTZ  NOT NULL DEFAULT now(),
	start_date   TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
	end_date     TIMESTAMP WITH TIME ZONE,
	active       BOOL         NOT NULL DEFAULT FALSE,
	pattern_code TEXT         NOT NULL,       -- must contain JSON
	enum_1       TEXT,
	enum_2       TEXT,
	enum_3       TEXT,
	enum_4       TEXT,
	enum_5       TEXT,
	enum_6       TEXT,
	chron_1      TEXT,
	chron_2      TEXT,
	chron_3      TEXT,
	chron_4      TEXT,
	chron_5      TEXT
);
CREATE INDEX serial_caption_and_pattern_sub_idx ON serial.caption_and_pattern (subscription);

CREATE TABLE serial.issuance (
	id              SERIAL    PRIMARY KEY,
	creator         INT       NOT NULL
	                          REFERENCES actor.usr (id)
							  DEFERRABLE INITIALLY DEFERRED,
	editor          INT       NOT NULL
	                          REFERENCES actor.usr (id)
	                          DEFERRABLE INITIALLY DEFERRED,
	create_date     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
	edit_date       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
	subscription    INT       NOT NULL
	                          REFERENCES serial.subscription (id)
	                          ON DELETE CASCADE
	                          DEFERRABLE INITIALLY DEFERRED,
	label           TEXT,
	date_published  TIMESTAMP WITH TIME ZONE,
	caption_and_pattern  INT  REFERENCES serial.caption_and_pattern (id)
                              DEFERRABLE INITIALLY DEFERRED,
	holding_code    TEXT,
	holding_type    TEXT      CONSTRAINT valid_holding_type CHECK
	                          (
	                              holding_type IS NULL
	                              OR holding_type IN ('basic','supplement','index')
	                          ),
	holding_link_id INT
	-- TODO: add columns for separate enumeration/chronology values
);
CREATE INDEX serial_issuance_sub_idx ON serial.issuance (subscription);
CREATE INDEX serial_issuance_caption_and_pattern_idx ON serial.issuance (caption_and_pattern);
CREATE INDEX serial_issuance_date_published_idx ON serial.issuance (date_published);

CREATE TABLE serial.unit (
	label           TEXT,
	label_sort_key  TEXT,
	contents        TEXT    NOT NULL
) INHERITS (asset.copy);
CREATE UNIQUE INDEX unit_barcode_key ON serial.unit (barcode) WHERE deleted = FALSE OR deleted IS FALSE;
CREATE INDEX unit_cn_idx ON serial.unit (call_number);
CREATE INDEX unit_avail_cn_idx ON serial.unit (call_number);
CREATE INDEX unit_creator_idx  ON serial.unit ( creator );
CREATE INDEX unit_editor_idx   ON serial.unit ( editor );

ALTER TABLE serial.unit ADD PRIMARY KEY (id);

ALTER TABLE serial.unit ADD CONSTRAINT serial_unit_call_number_fkey FOREIGN KEY (call_number) REFERENCES asset.call_number (id) DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE serial.unit ADD CONSTRAINT serial_unit_creator_fkey FOREIGN KEY (creator) REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE serial.unit ADD CONSTRAINT serial_unit_editor_fkey FOREIGN KEY (editor) REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED;

CREATE TABLE serial.item (
	id              SERIAL  PRIMARY KEY,
	creator         INT     NOT NULL
	                        REFERENCES actor.usr (id)
	                        DEFERRABLE INITIALLY DEFERRED,
	editor          INT     NOT NULL
	                        REFERENCES actor.usr (id)
	                        DEFERRABLE INITIALLY DEFERRED,
	create_date     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
	edit_date       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
	issuance        INT     NOT NULL
	                        REFERENCES serial.issuance (id)
	                        ON DELETE CASCADE
	                        DEFERRABLE INITIALLY DEFERRED,
	stream          INT     NOT NULL
	                        REFERENCES serial.stream (id)
	                        ON DELETE CASCADE
	                        DEFERRABLE INITIALLY DEFERRED,
	unit            INT     REFERENCES serial.unit (id)
	                        ON DELETE SET NULL
	                        DEFERRABLE INITIALLY DEFERRED,
	uri             INT     REFERENCES asset.uri (id)
	                        ON DELETE SET NULL
	                        DEFERRABLE INITIALLY DEFERRED,
	date_expected   TIMESTAMP WITH TIME ZONE,
	date_received   TIMESTAMP WITH TIME ZONE,
	status          TEXT    CONSTRAINT valid_status CHECK (
                               status IN ( 'Bindery', 'Bound', 'Claimed', 'Discarded',
                               'Expected', 'Not Held', 'Not Published', 'Received'))
                            DEFAULT 'Expected',
	shadowed        BOOL    NOT NULL DEFAULT FALSE
);
CREATE INDEX serial_item_stream_idx ON serial.item (stream);
CREATE INDEX serial_item_issuance_idx ON serial.item (issuance);
CREATE INDEX serial_item_unit_idx ON serial.item (unit);
CREATE INDEX serial_item_uri_idx ON serial.item (uri);
CREATE INDEX serial_item_date_received_idx ON serial.item (date_received);
CREATE INDEX serial_item_status_idx ON serial.item (status);

CREATE TABLE serial.item_note (
	id          SERIAL  PRIMARY KEY,
	item        INT     NOT NULL
	                    REFERENCES serial.item (id)
	                    ON DELETE CASCADE
	                    DEFERRABLE INITIALLY DEFERRED,
	creator     INT     NOT NULL
	                    REFERENCES actor.usr (id)
	                    DEFERRABLE INITIALLY DEFERRED,
	create_date TIMESTAMP WITH TIME ZONE    DEFAULT NOW(),
	pub         BOOL    NOT NULL    DEFAULT FALSE,
	title       TEXT    NOT NULL,
	value       TEXT    NOT NULL
);
CREATE INDEX serial_item_note_item_idx ON serial.item_note (item);

CREATE TABLE serial.basic_summary (
	id                  SERIAL  PRIMARY KEY,
	distribution        INT     NOT NULL
	                            REFERENCES serial.distribution (id)
	                            ON DELETE CASCADE
	                            DEFERRABLE INITIALLY DEFERRED,
	generated_coverage  TEXT    NOT NULL,
	textual_holdings    TEXT,
	show_generated      BOOL    NOT NULL DEFAULT TRUE
);
CREATE INDEX serial_basic_summary_dist_idx ON serial.basic_summary (distribution);

CREATE TABLE serial.supplement_summary (
	id                  SERIAL  PRIMARY KEY,
	distribution        INT     NOT NULL
	                            REFERENCES serial.distribution (id)
	                            ON DELETE CASCADE
	                            DEFERRABLE INITIALLY DEFERRED,
	generated_coverage  TEXT    NOT NULL,
	textual_holdings    TEXT,
	show_generated      BOOL    NOT NULL DEFAULT TRUE
);
CREATE INDEX serial_supplement_summary_dist_idx ON serial.supplement_summary (distribution);

CREATE TABLE serial.index_summary (
	id                  SERIAL  PRIMARY KEY,
	distribution        INT     NOT NULL
	                            REFERENCES serial.distribution (id)
	                            ON DELETE CASCADE
	                            DEFERRABLE INITIALLY DEFERRED,
	generated_coverage  TEXT    NOT NULL,
	textual_holdings    TEXT,
	show_generated      BOOL    NOT NULL DEFAULT TRUE
);
CREATE INDEX serial_index_summary_dist_idx ON serial.index_summary (distribution);

-- DELETE FROM action_trigger.environment WHERE event_def IN (29,30); DELETE FROM action_trigger.event where event_def IN (29,30); DELETE FROM action_trigger.event_definition WHERE id IN (29,30); DELETE FROM action_trigger.hook WHERE key IN ('money.format.payment_receipt.email','money.format.payment_receipt.print'); DELETE FROM config.upgrade_log WHERE version = '0289'; -- from testing, this sql will remove these events, etc.

DROP INDEX IF EXISTS authority.authority_record_unique_tcn;
CREATE UNIQUE INDEX authority_record_unique_tcn ON authority.record_entry (arn_source,arn_value) WHERE deleted = FALSE OR deleted IS FALSE;

DROP INDEX IF EXISTS asset.asset_call_number_label_once_per_lib;
CREATE UNIQUE INDEX asset_call_number_label_once_per_lib ON asset.call_number (record, owning_lib, label) WHERE deleted = FALSE OR deleted IS FALSE;

DROP INDEX IF EXISTS biblio.biblio_record_unique_tcn;
CREATE UNIQUE INDEX biblio_record_unique_tcn ON biblio.record_entry (tcn_value) WHERE deleted = FALSE OR deleted IS FALSE;

CREATE OR REPLACE FUNCTION config.interval_to_seconds( interval_val INTERVAL )
RETURNS INTEGER AS $$
BEGIN
	RETURN EXTRACT( EPOCH FROM interval_val );
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION config.interval_to_seconds( interval_string TEXT )
RETURNS INTEGER AS $$
BEGIN
	RETURN config.interval_to_seconds( interval_string::INTERVAL );
END;
$$ LANGUAGE plpgsql;

INSERT INTO container.biblio_record_entry_bucket_type( code, label ) VALUES (
    'temp',
    oils_i18n_gettext(
        'temp',
        'Temporary bucket which gets deleted after use.',
        'cbrebt',
        'label'
    )
);

-- DELETE FROM action_trigger.environment WHERE event_def IN (31,32); DELETE FROM action_trigger.event where event_def IN (31,32); DELETE FROM action_trigger.event_definition WHERE id IN (31,32); DELETE FROM action_trigger.hook WHERE key IN ('biblio.format.record_entry.email','biblio.format.record_entry.print'); DELETE FROM action_trigger.cleanup WHERE module = 'DeleteTempBiblioBucket'; DELETE FROM container.biblio_record_entry_bucket_item WHERE bucket IN (SELECT id FROM container.biblio_record_entry_bucket WHERE btype = 'temp'); DELETE FROM container.biblio_record_entry_bucket WHERE btype = 'temp'; DELETE FROM container.biblio_record_entry_bucket_type WHERE code = 'temp'; DELETE FROM config.upgrade_log WHERE version = '0294'; -- from testing, this sql will remove these events, etc.

CREATE OR REPLACE FUNCTION biblio.check_marcxml_well_formed () RETURNS TRIGGER AS $func$
BEGIN

    IF xml_is_well_formed(NEW.marc) THEN
        RETURN NEW;
    ELSE
        RAISE EXCEPTION 'Attempted to % MARCXML that is not well formed', TG_OP;
    END IF;
    
END;
$func$ LANGUAGE PLPGSQL;

CREATE TRIGGER a_marcxml_is_well_formed BEFORE INSERT OR UPDATE ON biblio.record_entry FOR EACH ROW EXECUTE PROCEDURE biblio.check_marcxml_well_formed();

CREATE TRIGGER a_marcxml_is_well_formed BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE biblio.check_marcxml_well_formed();

ALTER TABLE serial.record_entry
	ALTER COLUMN marc DROP NOT NULL;

insert INTO CONFIG.xml_transform(name, namespace_uri, prefix, xslt)
VALUES ('marc21expand880', 'http://www.loc.gov/MARC21/slim', 'marc', $$<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
    xmlns:marc="http://www.loc.gov/MARC21/slim"
    version="1.0">
<!--
Copyright (C) 2010  Equinox Software, Inc.
Galen Charlton <gmc@esilibrary.cOM.

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.

marc21_expand_880.xsl - stylesheet used during indexing to
                        map alternative graphical representations
                        of MARC fields stored in 880 fields
                        to the corresponding tag name and value.

For example, if a MARC record for a Chinese book has

245.00 $6 880-01 $a Ba shi san nian duan pian xiao shuo xuan
880.00 $6 245-01/$1 $a八十三年短篇小說選

this stylesheet will transform it to the equivalent of

245.00 $6 880-01 $a Ba shi san nian duan pian xiao shuo xuan
245.00 $6 245-01/$1 $a八十三年短篇小說選

-->
    <xsl:output encoding="UTF-8" indent="yes" method="xml"/>

    <xsl:template match="@*|node()">
        <xsl:copy>
            <xsl:apply-templates select="@*|node()"/>
        </xsl:copy>
    </xsl:template>

    <xsl:template match="//marc:datafield[@tag='880']">
        <xsl:if test="./marc:subfield[@code='6'] and string-length(./marc:subfield[@code='6']) &gt;= 6">
            <marc:datafield>
                <xsl:attribute name="tag">
                    <xsl:value-of select="substring(./marc:subfield[@code='6'], 1, 3)" />
                </xsl:attribute>
                <xsl:attribute name="ind1">
                    <xsl:value-of select="@ind1" />
                </xsl:attribute>
                <xsl:attribute name="ind2">
                    <xsl:value-of select="@ind2" />
                </xsl:attribute>
                <xsl:apply-templates />
            </marc:datafield>
        </xsl:if>
    </xsl:template>
    
</xsl:stylesheet>$$);

-- Splitting the ingest trigger up into little bits

CREATE TEMPORARY TABLE eg_0301_check_if_has_contents (
    flag INTEGER PRIMARY KEY
) ON COMMIT DROP;
INSERT INTO eg_0301_check_if_has_contents VALUES (1);

-- cause failure if either of the tables we want to drop have rows
INSERT INTO eg_0301_check_if_has_contents SELECT 1 FROM asset.copy_transparency LIMIT 1;
INSERT INTO eg_0301_check_if_has_contents SELECT 1 FROM asset.copy_transparency_map LIMIT 1;


UPDATE config.metabib_field SET facet_xpath = '//' || facet_xpath WHERE facet_xpath IS NOT NULL;

-- We won't necessarily use all of these, but they are here for completeness.
-- Source is the EDI spec 1229 codelist, eg: http://www.stylusstudio.com/edifact/D04B/1229.htm
-- Values are the EDI code value + 1000

INSERT INTO acq.cancel_reason (keep_debits, id, org_unit, label, description) VALUES 
('t',(  1+1000), 1, 'Added',     'The information is to be or has been added.'),
('f',(  2+1000), 1, 'Deleted',   'The information is to be or has been deleted.'),
('t',(  3+1000), 1, 'Changed',   'The information is to be or has been changed.'),
('t',(  4+1000), 1, 'No action',                  'This line item is not affected by the actual message.'),
('t',(  5+1000), 1, 'Accepted without amendment', 'This line item is entirely accepted by the seller.'),
('t',(  6+1000), 1, 'Accepted with amendment',    'This line item is accepted but amended by the seller.'),
('f',(  7+1000), 1, 'Not accepted',               'This line item is not accepted by the seller.'),
('t',(  8+1000), 1, 'Schedule only', 'Code specifying that the message is a schedule only.'),
('t',(  9+1000), 1, 'Amendments',    'Code specifying that amendments are requested/notified.'),
('f',( 10+1000), 1, 'Not found',   'This line item is not found in the referenced message.'),
('t',( 11+1000), 1, 'Not amended', 'This line is not amended by the buyer.'),
('t',( 12+1000), 1, 'Line item numbers changed', 'Code specifying that the line item numbers have changed.'),
('t',( 13+1000), 1, 'Buyer has deducted amount', 'Buyer has deducted amount from payment.'),
('t',( 14+1000), 1, 'Buyer claims against invoice', 'Buyer has a claim against an outstanding invoice.'),
('t',( 15+1000), 1, 'Charge back by seller', 'Factor has been requested to charge back the outstanding item.'),
('t',( 16+1000), 1, 'Seller will issue credit note', 'Seller agrees to issue a credit note.'),
('t',( 17+1000), 1, 'Terms changed for new terms', 'New settlement terms have been agreed.'),
('t',( 18+1000), 1, 'Abide outcome of negotiations', 'Factor agrees to abide by the outcome of negotiations between seller and buyer.'),
('t',( 19+1000), 1, 'Seller rejects dispute', 'Seller does not accept validity of dispute.'),
('t',( 20+1000), 1, 'Settlement', 'The reported situation is settled.'),
('t',( 21+1000), 1, 'No delivery', 'Code indicating that no delivery will be required.'),
('t',( 22+1000), 1, 'Call-off delivery', 'A request for delivery of a particular quantity of goods to be delivered on a particular date (or within a particular period).'),
('t',( 23+1000), 1, 'Proposed amendment', 'A code used to indicate an amendment suggested by the sender.'),
('t',( 24+1000), 1, 'Accepted with amendment, no confirmation required', 'Accepted with changes which require no confirmation.'),
('t',( 25+1000), 1, 'Equipment provisionally repaired', 'The equipment or component has been provisionally repaired.'),
('t',( 26+1000), 1, 'Included', 'Code indicating that the entity is included.'),
('t',( 27+1000), 1, 'Verified documents for coverage', 'Upon receipt and verification of documents we shall cover you when due as per your instructions.'),
('t',( 28+1000), 1, 'Verified documents for debit',    'Upon receipt and verification of documents we shall authorize you to debit our account with you when due.'),
('t',( 29+1000), 1, 'Authenticated advice for coverage',      'On receipt of your authenticated advice we shall cover you when due as per your instructions.'),
('t',( 30+1000), 1, 'Authenticated advice for authorization', 'On receipt of your authenticated advice we shall authorize you to debit our account with you when due.'),
('t',( 31+1000), 1, 'Authenticated advice for credit',        'On receipt of your authenticated advice we shall credit your account with us when due.'),
('t',( 32+1000), 1, 'Credit advice requested for direct debit',           'A credit advice is requested for the direct debit.'),
('t',( 33+1000), 1, 'Credit advice and acknowledgement for direct debit', 'A credit advice and acknowledgement are requested for the direct debit.'),
('t',( 34+1000), 1, 'Inquiry',     'Request for information.'),
('t',( 35+1000), 1, 'Checked',     'Checked.'),
('t',( 36+1000), 1, 'Not checked', 'Not checked.'),
('f',( 37+1000), 1, 'Cancelled',   'Discontinued.'),
('t',( 38+1000), 1, 'Replaced',    'Provide a replacement.'),
('t',( 39+1000), 1, 'New',         'Not existing before.'),
('t',( 40+1000), 1, 'Agreed',      'Consent.'),
('t',( 41+1000), 1, 'Proposed',    'Put forward for consideration.'),
('t',( 42+1000), 1, 'Already delivered', 'Delivery has taken place.'),
('t',( 43+1000), 1, 'Additional subordinate structures will follow', 'Additional subordinate structures will follow the current hierarchy level.'),
('t',( 44+1000), 1, 'Additional subordinate structures will not follow', 'No additional subordinate structures will follow the current hierarchy level.'),
('t',( 45+1000), 1, 'Result opposed',         'A notification that the result is opposed.'),
('t',( 46+1000), 1, 'Auction held',           'A notification that an auction was held.'),
('t',( 47+1000), 1, 'Legal action pursued',   'A notification that legal action has been pursued.'),
('t',( 48+1000), 1, 'Meeting held',           'A notification that a meeting was held.'),
('t',( 49+1000), 1, 'Result set aside',       'A notification that the result has been set aside.'),
('t',( 50+1000), 1, 'Result disputed',        'A notification that the result has been disputed.'),
('t',( 51+1000), 1, 'Countersued',            'A notification that a countersuit has been filed.'),
('t',( 52+1000), 1, 'Pending',                'A notification that an action is awaiting settlement.'),
('f',( 53+1000), 1, 'Court action dismissed', 'A notification that a court action will no longer be heard.'),
('t',( 54+1000), 1, 'Referred item, accepted', 'The item being referred to has been accepted.'),
('f',( 55+1000), 1, 'Referred item, rejected', 'The item being referred to has been rejected.'),
('t',( 56+1000), 1, 'Debit advice statement line',  'Notification that the statement line is a debit advice.'),
('t',( 57+1000), 1, 'Credit advice statement line', 'Notification that the statement line is a credit advice.'),
('t',( 58+1000), 1, 'Grouped credit advices',       'Notification that the credit advices are grouped.'),
('t',( 59+1000), 1, 'Grouped debit advices',        'Notification that the debit advices are grouped.'),
('t',( 60+1000), 1, 'Registered', 'The name is registered.'),
('f',( 61+1000), 1, 'Payment denied', 'The payment has been denied.'),
('t',( 62+1000), 1, 'Approved as amended', 'Approved with modifications.'),
('t',( 63+1000), 1, 'Approved as submitted', 'The request has been approved as submitted.'),
('f',( 64+1000), 1, 'Cancelled, no activity', 'Cancelled due to the lack of activity.'),
('t',( 65+1000), 1, 'Under investigation', 'Investigation is being done.'),
('t',( 66+1000), 1, 'Initial claim received', 'Notification that the initial claim was received.'),
('f',( 67+1000), 1, 'Not in process', 'Not in process.'),
('f',( 68+1000), 1, 'Rejected, duplicate', 'Rejected because it is a duplicate.'),
('f',( 69+1000), 1, 'Rejected, resubmit with corrections', 'Rejected but may be resubmitted when corrected.'),
('t',( 70+1000), 1, 'Pending, incomplete', 'Pending because of incomplete information.'),
('t',( 71+1000), 1, 'Under field office investigation', 'Investigation by the field is being done.'),
('t',( 72+1000), 1, 'Pending, awaiting additional material', 'Pending awaiting receipt of additional material.'),
('t',( 73+1000), 1, 'Pending, awaiting review', 'Pending while awaiting review.'),
('t',( 74+1000), 1, 'Reopened', 'Opened again.'),
('t',( 75+1000), 1, 'Processed by primary, forwarded to additional payer(s)',   'This request has been processed by the primary payer and sent to additional payer(s).'),
('t',( 76+1000), 1, 'Processed by secondary, forwarded to additional payer(s)', 'This request has been processed by the secondary payer and sent to additional payer(s).'),
('t',( 77+1000), 1, 'Processed by tertiary, forwarded to additional payer(s)',  'This request has been processed by the tertiary payer and sent to additional payer(s).'),
('t',( 78+1000), 1, 'Previous payment decision reversed', 'A previous payment decision has been reversed.'),
('t',( 79+1000), 1, 'Not our claim, forwarded to another payer(s)', 'A request does not belong to this payer but has been forwarded to another payer(s).'),
('t',( 80+1000), 1, 'Transferred to correct insurance carrier', 'The request has been transferred to the correct insurance carrier for processing.'),
('t',( 81+1000), 1, 'Not paid, predetermination pricing only', 'Payment has not been made and the enclosed response is predetermination pricing only.'),
('t',( 82+1000), 1, 'Documentation claim', 'The claim is for documentation purposes only, no payment required.'),
('t',( 83+1000), 1, 'Reviewed', 'Assessed.'),
('f',( 84+1000), 1, 'Repriced', 'This price was changed.'),
('t',( 85+1000), 1, 'Audited', 'An official examination has occurred.'),
('t',( 86+1000), 1, 'Conditionally paid', 'Payment has been conditionally made.'),
('t',( 87+1000), 1, 'On appeal', 'Reconsideration of the decision has been applied for.'),
('t',( 88+1000), 1, 'Closed', 'Shut.'),
('t',( 89+1000), 1, 'Reaudited', 'A subsequent official examination has occurred.'),
('t',( 90+1000), 1, 'Reissued', 'Issued again.'),
('t',( 91+1000), 1, 'Closed after reopening', 'Reopened and then closed.'),
('t',( 92+1000), 1, 'Redetermined', 'Determined again or differently.'),
('t',( 93+1000), 1, 'Processed as primary',   'Processed as the first.'),
('t',( 94+1000), 1, 'Processed as secondary', 'Processed as the second.'),
('t',( 95+1000), 1, 'Processed as tertiary',  'Processed as the third.'),
('t',( 96+1000), 1, 'Correction of error', 'A correction to information previously communicated which contained an error.'),
('t',( 97+1000), 1, 'Single credit item of a group', 'Notification that the credit item is a single credit item of a group of credit items.'),
('t',( 98+1000), 1, 'Single debit item of a group',  'Notification that the debit item is a single debit item of a group of debit items.'),
('t',( 99+1000), 1, 'Interim response', 'The response is an interim one.'),
('t',(100+1000), 1, 'Final response',   'The response is an final one.'),
('t',(101+1000), 1, 'Debit advice requested', 'A debit advice is requested for the transaction.'),
('t',(102+1000), 1, 'Transaction not impacted', 'Advice that the transaction is not impacted.'),
('t',(103+1000), 1, 'Patient to be notified',                    'The action to take is to notify the patient.'),
('t',(104+1000), 1, 'Healthcare provider to be notified',        'The action to take is to notify the healthcare provider.'),
('t',(105+1000), 1, 'Usual general practitioner to be notified', 'The action to take is to notify the usual general practitioner.'),
('t',(106+1000), 1, 'Advice without details', 'An advice without details is requested or notified.'),
('t',(107+1000), 1, 'Advice with details', 'An advice with details is requested or notified.'),
('t',(108+1000), 1, 'Amendment requested', 'An amendment is requested.'),
('t',(109+1000), 1, 'For information', 'Included for information only.'),
('f',(110+1000), 1, 'Withdraw', 'A code indicating discontinuance or retraction.'),
('t',(111+1000), 1, 'Delivery date change', 'The action / notiification is a change of the delivery date.'),
('f',(112+1000), 1, 'Quantity change',      'The action / notification is a change of quantity.'),
('t',(113+1000), 1, 'Resale and claim', 'The identified items have been sold by the distributor to the end customer, and compensation for the loss of inventory value is claimed.'),
('t',(114+1000), 1, 'Resale',           'The identified items have been sold by the distributor to the end customer.'),
('t',(115+1000), 1, 'Prior addition', 'This existing line item becomes available at an earlier date.');

INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_field, search_field ) VALUES
    (26, 'identifier', 'arcn', oils_i18n_gettext(26, 'Authority record control number', 'cmf', 'label'), 'marcxml', $$//marc:subfield[@code='0']$$, TRUE, FALSE );
 
SELECT SETVAL('config.metabib_field_id_seq'::TEXT, (SELECT MAX(id) FROM config.metabib_field), TRUE);
 
INSERT INTO config.index_normalizer (name, description, func, param_count) VALUES (
	'Remove Parenthesized Substring',
	'Remove any parenthesized substrings from the extracted text, such as the agency code preceding authority record control numbers in subfield 0.',
	'remove_paren_substring',
	0
);

INSERT INTO config.index_normalizer (name, description, func, param_count) VALUES (
	'Trim Surrounding Space',
	'Trim leading and trailing spaces from extracted text.',
	'btrim',
	0
);

INSERT INTO config.metabib_field_index_norm_map (field,norm,pos)
    SELECT  m.id,
            i.id,
            -2
      FROM  config.metabib_field m,
            config.index_normalizer i
      WHERE i.func IN ('remove_paren_substring')
            AND m.id IN (26);

INSERT INTO config.metabib_field_index_norm_map (field,norm,pos)
    SELECT  m.id,
            i.id,
            -1
      FROM  config.metabib_field m,
            config.index_normalizer i
      WHERE i.func IN ('btrim')
            AND m.id IN (26);

-- Function that takes, and returns, marcxml and compiles an embedded ruleset for you, and they applys it
CREATE OR REPLACE FUNCTION vandelay.merge_record_xml ( target_marc TEXT, template_marc TEXT ) RETURNS TEXT AS $$
DECLARE
    dyn_profile     vandelay.compile_profile%ROWTYPE;
    replace_rule    TEXT;
    tmp_marc        TEXT;
    trgt_marc        TEXT;
    tmpl_marc        TEXT;
    match_count     INT;
BEGIN

    IF target_marc IS NULL OR template_marc IS NULL THEN
        -- RAISE NOTICE 'no marc for target or template record';
        RETURN NULL;
    END IF;

    dyn_profile := vandelay.compile_profile( template_marc );

    IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN
        -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule;
        RETURN NULL;
    END IF;

    IF dyn_profile.replace_rule <> '' THEN
        trgt_marc = target_marc;
        tmpl_marc = template_marc;
        replace_rule = dyn_profile.replace_rule;
    ELSE
        tmp_marc = target_marc;
        trgt_marc = template_marc;
        tmpl_marc = tmp_marc;
        replace_rule = dyn_profile.preserve_rule;
    END IF;

    RETURN vandelay.merge_record_xml( trgt_marc, tmpl_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule );

END;
$$ LANGUAGE PLPGSQL;

-- Function to generate an ephemeral overlay template from an authority record
CREATE OR REPLACE FUNCTION authority.generate_overlay_template ( TEXT, BIGINT ) RETURNS TEXT AS $func$

    use MARC::Record;
    use MARC::File::XML (BinaryEncoding => 'UTF-8');

    my $xml = shift;
    my $r = MARC::Record->new_from_xml( $xml );

    return undef unless ($r);

    my $id = shift() || $r->subfield( '901' => 'c' );
    $id =~ s/^\s*(?:\([^)]+\))?\s*(.+)\s*?$/$1/;
    return undef unless ($id); # We need an ID!

    my $tmpl = MARC::Record->new();

    my @rule_fields;
    for my $field ( $r->field( '1..' ) ) { # Get main entry fields from the authority record

        my $tag = $field->tag;
        my $i1 = $field->indicator(1);
        my $i2 = $field->indicator(2);
        my $sf = join '', map { $_->[0] } $field->subfields;
        my @data = map { @$_ } $field->subfields;

        my @replace_them;

        # Map the authority field to bib fields it can control.
        if ($tag >= 100 and $tag <= 111) {       # names
            @replace_them = map { $tag + $_ } (0, 300, 500, 600, 700);
        } elsif ($tag eq '130') {                # uniform title
            @replace_them = qw/130 240 440 730 830/;
        } elsif ($tag >= 150 and $tag <= 155) {  # subjects
            @replace_them = ($tag + 500);
        } elsif ($tag >= 180 and $tag <= 185) {  # floating subdivisions
            @replace_them = qw/100 400 600 700 800 110 410 610 710 810 111 411 611 711 811 130 240 440 730 830 650 651 655/;
        } else {
            next;
        }

        # Dummy up the bib-side data
        $tmpl->append_fields(
            map {
                MARC::Field->new( $_, $i1, $i2, @data )
            } @replace_them
        );

        # Construct some 'replace' rules
        push @rule_fields, map { $_ . $sf . '[0~\)' .$id . '$]' } @replace_them;
    }

    # Insert the replace rules into the template
    $tmpl->append_fields(
        MARC::Field->new( '905' => ' ' => ' ' => 'r' => join(',', @rule_fields ) )
    );

    $xml = $tmpl->as_xml_record;
    $xml =~ s/^<\?.+?\?>$//mo;
    $xml =~ s/\n//sgo;
    $xml =~ s/>\s+</></sgo;

    return $xml;

$func$ LANGUAGE PLPERLU;

CREATE OR REPLACE FUNCTION authority.generate_overlay_template ( BIGINT ) RETURNS TEXT AS $func$
    SELECT authority.generate_overlay_template( marc, id ) FROM authority.record_entry WHERE id = $1;
$func$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION authority.generate_overlay_template ( TEXT ) RETURNS TEXT AS $func$
    SELECT authority.generate_overlay_template( $1, NULL );
$func$ LANGUAGE SQL;

DELETE FROM config.metabib_field_index_norm_map WHERE field = 26;
DELETE FROM config.metabib_field WHERE id = 26;

-- Making this a global_flag (UI accessible) instead of an internal_flag
INSERT INTO config.global_flag (name, label) -- defaults to enabled=FALSE
    VALUES (
        'ingest.disable_authority_linking',
        oils_i18n_gettext(
            'ingest.disable_authority_linking',
            'Authority Automation: Disable bib-authority link tracking',
            'cgf', 
            'label'
        )
    );
UPDATE config.global_flag SET enabled = (SELECT enabled FROM ONLY config.internal_flag WHERE name = 'ingest.disable_authority_linking');
DELETE FROM config.internal_flag WHERE name = 'ingest.disable_authority_linking';

INSERT INTO config.global_flag (name, label) -- defaults to enabled=FALSE
    VALUES (
        'ingest.disable_authority_auto_update',
        oils_i18n_gettext(
            'ingest.disable_authority_auto_update',
            'Authority Automation: Disable automatic authority updating (requires link tracking)',
            'cgf', 
            'label'
        )
    );

-- Enable automated ingest of authority records; just insert the row into
-- authority.record_entry and authority.full_rec will automatically be populated

CREATE OR REPLACE FUNCTION authority.propagate_changes (aid BIGINT, bid BIGINT) RETURNS BIGINT AS $func$
    UPDATE  biblio.record_entry
      SET   marc = vandelay.merge_record_xml( marc, authority.generate_overlay_template( $1 ) )
      WHERE id = $2;
    SELECT $1;
$func$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION authority.propagate_changes (aid BIGINT) RETURNS SETOF BIGINT AS $func$
    SELECT authority.propagate_changes( authority, bib ) FROM authority.bib_linking WHERE authority = $1;
$func$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION authority.flatten_marc ( TEXT ) RETURNS SETOF authority.full_rec AS $func$

use MARC::Record;
use MARC::File::XML (BinaryEncoding => 'UTF-8');

my $xml = shift;
my $r = MARC::Record->new_from_xml( $xml );

return_next( { tag => 'LDR', value => $r->leader } );

for my $f ( $r->fields ) {
    if ($f->is_control_field) {
        return_next({ tag => $f->tag, value => $f->data });
    } else {
        for my $s ($f->subfields) {
            return_next({
                tag      => $f->tag,
                ind1     => $f->indicator(1),
                ind2     => $f->indicator(2),
                subfield => $s->[0],
                value    => $s->[1]
            });

        }
    }
}

return undef;

$func$ LANGUAGE PLPERLU;

CREATE OR REPLACE FUNCTION authority.flatten_marc ( rid BIGINT ) RETURNS SETOF authority.full_rec AS $func$
DECLARE
    auth    authority.record_entry%ROWTYPE;
    output    authority.full_rec%ROWTYPE;
    field    RECORD;
BEGIN
    SELECT INTO auth * FROM authority.record_entry WHERE id = rid;

    FOR field IN SELECT * FROM authority.flatten_marc( auth.marc ) LOOP
        output.record := rid;
        output.ind1 := field.ind1;
        output.ind2 := field.ind2;
        output.tag := field.tag;
        output.subfield := field.subfield;
        IF field.subfield IS NOT NULL THEN
            output.value := naco_normalize(field.value, field.subfield);
        ELSE
            output.value := field.value;
        END IF;

        CONTINUE WHEN output.value IS NULL;

        RETURN NEXT output;
    END LOOP;
END;
$func$ LANGUAGE PLPGSQL;

-- authority.rec_descriptor appears to be unused currently
CREATE OR REPLACE FUNCTION authority.reingest_authority_rec_descriptor( auth_id BIGINT ) RETURNS VOID AS $func$
BEGIN
    DELETE FROM authority.rec_descriptor WHERE record = auth_id;
--    INSERT INTO authority.rec_descriptor (record, record_status, char_encoding)
--        SELECT  auth_id, ;

    RETURN;
END;
$func$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION authority.reingest_authority_full_rec( auth_id BIGINT ) RETURNS VOID AS $func$
BEGIN
    DELETE FROM authority.full_rec WHERE record = auth_id;
    INSERT INTO authority.full_rec (record, tag, ind1, ind2, subfield, value)
        SELECT record, tag, ind1, ind2, subfield, value FROM authority.flatten_marc( auth_id );

    RETURN;
END;
$func$ LANGUAGE PLPGSQL;

-- AFTER UPDATE OR INSERT trigger for authority.record_entry
CREATE OR REPLACE FUNCTION authority.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$
BEGIN

    IF NEW.deleted IS TRUE THEN -- If this authority is deleted
        DELETE FROM authority.bib_linking WHERE authority = NEW.id; -- Avoid updating fields in bibs that are no longer visible
        DELETE FROM authority.full_rec WHERE record = NEW.id; -- Avoid validating fields against deleted authority records
          -- Should remove matching $0 from controlled fields at the same time?
        RETURN NEW; -- and we're done
    END IF;

    IF TG_OP = 'UPDATE' THEN -- re-ingest?
        PERFORM * FROM config.internal_flag WHERE name = 'ingest.reingest.force_on_same_marc' AND enabled;

        IF NOT FOUND AND OLD.marc = NEW.marc THEN -- don't do anything if the MARC didn't change
            RETURN NEW;
        END IF;
    END IF;

    -- Flatten and insert the afr data
    PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_full_rec' AND enabled;
    IF NOT FOUND THEN
        PERFORM authority.reingest_authority_full_rec(NEW.id);
-- authority.rec_descriptor is not currently used
--        PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_rec_descriptor' AND enabled;
--        IF NOT FOUND THEN
--            PERFORM authority.reingest_authority_rec_descriptor(NEW.id);
--        END IF;
    END IF;

    RETURN NEW;
END;
$func$ LANGUAGE PLPGSQL;

CREATE TRIGGER aaa_auth_ingest_or_delete AFTER INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE authority.indexing_ingest_or_delete ();

-- Some records manage to get XML namespace declarations into each element,
-- like <datafield xmlns:marc="http://www.loc.gov/MARC21/slim"
-- This broke the old maintain_901(), so we'll make the regex more robust

CREATE OR REPLACE FUNCTION maintain_901 () RETURNS TRIGGER AS $func$
BEGIN
    -- Remove any existing 901 fields before we insert the authoritative one
    NEW.marc := REGEXP_REPLACE(NEW.marc, E'<datafield\s*[^<>]*?\s*tag="901".+?</datafield>', '', 'g');
    IF TG_TABLE_SCHEMA = 'biblio' THEN
        NEW.marc := REGEXP_REPLACE(
            NEW.marc,
            E'(</(?:[^:]*?:)?record>)',
            E'<datafield tag="901" ind1=" " ind2=" ">' ||
                '<subfield code="a">' || NEW.tcn_value || E'</subfield>' ||
                '<subfield code="b">' || NEW.tcn_source || E'</subfield>' ||
                '<subfield code="c">' || NEW.id || E'</subfield>' ||
                '<subfield code="t">' || TG_TABLE_SCHEMA || E'</subfield>' ||
                CASE WHEN NEW.owner IS NOT NULL THEN '<subfield code="o">' || NEW.owner || E'</subfield>' ELSE '' END ||
                CASE WHEN NEW.share_depth IS NOT NULL THEN '<subfield code="d">' || NEW.share_depth || E'</subfield>' ELSE '' END ||
             E'</datafield>\\1'
        );
    ELSIF TG_TABLE_SCHEMA = 'authority' THEN
        NEW.marc := REGEXP_REPLACE(
            NEW.marc,
            E'(</(?:[^:]*?:)?record>)',
            E'<datafield tag="901" ind1=" " ind2=" ">' ||
                '<subfield code="c">' || NEW.id || E'</subfield>' ||
                '<subfield code="t">' || TG_TABLE_SCHEMA || E'</subfield>' ||
             E'</datafield>\\1'
        );
    ELSIF TG_TABLE_SCHEMA = 'serial' THEN
        NEW.marc := REGEXP_REPLACE(
            NEW.marc,
            E'(</(?:[^:]*?:)?record>)',
            E'<datafield tag="901" ind1=" " ind2=" ">' ||
                '<subfield code="c">' || NEW.id || E'</subfield>' ||
                '<subfield code="t">' || TG_TABLE_SCHEMA || E'</subfield>' ||
                '<subfield code="o">' || NEW.owning_lib || E'</subfield>' ||
                CASE WHEN NEW.record IS NOT NULL THEN '<subfield code="r">' || NEW.record || E'</subfield>' ELSE '' END ||
             E'</datafield>\\1'
        );
    ELSE
        NEW.marc := REGEXP_REPLACE(
            NEW.marc,
            E'(</(?:[^:]*?:)?record>)',
            E'<datafield tag="901" ind1=" " ind2=" ">' ||
                '<subfield code="c">' || NEW.id || E'</subfield>' ||
                '<subfield code="t">' || TG_TABLE_SCHEMA || E'</subfield>' ||
             E'</datafield>\\1'
        );
    END IF;

    RETURN NEW;
END;
$func$ LANGUAGE PLPGSQL;

CREATE TRIGGER b_maintain_901 BEFORE INSERT OR UPDATE ON biblio.record_entry FOR EACH ROW EXECUTE PROCEDURE maintain_901();
CREATE TRIGGER b_maintain_901 BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE maintain_901();
CREATE TRIGGER b_maintain_901 BEFORE INSERT OR UPDATE ON serial.record_entry FOR EACH ROW EXECUTE PROCEDURE maintain_901();
 
-- In booking, elbow room defines:
--  a) how far in the future you must make a reservation on a given item if
--      that item will have to transit somewhere to fulfill the reservation.
--  b) how soon a reservation must be starting for the reserved item to
--      be op-captured by the checkin interface.

-- We don't want to clobber any default_elbow room at any level:

CREATE OR REPLACE FUNCTION pg_temp.default_elbow() RETURNS INTEGER AS $$
DECLARE
    existing    actor.org_unit_setting%ROWTYPE;
BEGIN
    SELECT INTO existing id FROM actor.org_unit_setting WHERE name = 'circ.booking_reservation.default_elbow_room';
    IF NOT FOUND THEN
        INSERT INTO actor.org_unit_setting (org_unit, name, value) VALUES (
            (SELECT id FROM actor.org_unit WHERE parent_ou IS NULL),
            'circ.booking_reservation.default_elbow_room',
            '"1 day"'
        );
        RETURN 1;
    END IF;
    RETURN 0;
END;
$$ LANGUAGE plpgsql;

SELECT pg_temp.default_elbow();

DROP FUNCTION IF EXISTS action.usr_visible_circ_copies( INTEGER );

-- returns the distinct set of target copy IDs from a user's visible circulation history
CREATE OR REPLACE FUNCTION action.usr_visible_circ_copies( INTEGER ) RETURNS SETOF BIGINT AS $$
    SELECT DISTINCT(target_copy) FROM action.usr_visible_circs($1)
$$ LANGUAGE SQL;

ALTER TABLE action.in_house_use DROP CONSTRAINT in_house_use_item_fkey;
ALTER TABLE action.transit_copy DROP CONSTRAINT transit_copy_target_copy_fkey;
ALTER TABLE action.hold_transit_copy DROP CONSTRAINT ahtc_tc_fkey;
ALTER TABLE action.hold_copy_map DROP CONSTRAINT hold_copy_map_target_copy_fkey;

ALTER TABLE asset.stat_cat_entry_copy_map DROP CONSTRAINT a_sc_oc_fkey;

ALTER TABLE authority.record_entry ADD COLUMN owner INT;
ALTER TABLE serial.record_entry ADD COLUMN owner INT;

INSERT INTO config.global_flag (name, label, enabled)
    VALUES (
        'cat.maintain_control_numbers',
        oils_i18n_gettext(
            'cat.maintain_control_numbers',
            'Cat: Maintain 001/003/035 according to the MARC21 specification',
            'cgf', 
            'label'
        ),
        TRUE
    );

INSERT INTO config.global_flag (name, label, enabled)
    VALUES (
        'circ.holds.empty_issuance_ok',
        oils_i18n_gettext(
            'circ.holds.empty_issuance_ok',
            'Holds: Allow holds on empty issuances',
            'cgf',
            'label'
        ),
        TRUE
    );

INSERT INTO config.global_flag (name, label, enabled)
    VALUES (
        'circ.holds.usr_not_requestor',
        oils_i18n_gettext(
            'circ.holds.usr_not_requestor',
            'Holds: When testing hold matrix matchpoints, use the profile group of the receiving user instead of that of the requestor (affects staff-placed holds)',
            'cgf',
            'label'
        ),
        TRUE
    );

CREATE OR REPLACE FUNCTION maintain_control_numbers() RETURNS TRIGGER AS $func$
use strict;
use MARC::Record;
use MARC::File::XML (BinaryEncoding => 'UTF-8');
use Encode;
use Unicode::Normalize;

my $record = MARC::Record->new_from_xml($_TD->{new}{marc});
my $schema = $_TD->{table_schema};
my $rec_id = $_TD->{new}{id};

# Short-circuit if maintaining control numbers per MARC21 spec is not enabled
my $enable = spi_exec_query("SELECT enabled FROM config.global_flag WHERE name = 'cat.maintain_control_numbers'");
if (!($enable->{processed}) or $enable->{rows}[0]->{enabled} eq 'f') {
    return;
}

# Get the control number identifier from an OU setting based on $_TD->{new}{owner}
my $ou_cni = 'EVRGRN';

my $owner;
if ($schema eq 'serial') {
    $owner = $_TD->{new}{owning_lib};
} else {
    # are.owner and bre.owner can be null, so fall back to the consortial setting
    $owner = $_TD->{new}{owner} || 1;
}

my $ous_rv = spi_exec_query("SELECT value FROM actor.org_unit_ancestor_setting('cat.marc_control_number_identifier', $owner)");
if ($ous_rv->{processed}) {
    $ou_cni = $ous_rv->{rows}[0]->{value};
    $ou_cni =~ s/"//g; # Stupid VIM syntax highlighting"
} else {
    # Fall back to the shortname of the OU if there was no OU setting
    $ous_rv = spi_exec_query("SELECT shortname FROM actor.org_unit WHERE id = $owner");
    if ($ous_rv->{processed}) {
        $ou_cni = $ous_rv->{rows}[0]->{shortname};
    }
}

my ($create, $munge) = (0, 0);
my ($orig_001, $orig_003) = ('', '');

# Incoming MARC records may have multiple 001s or 003s, despite the spec
my @control_ids = $record->field('003');
my @scns = $record->field('035');

foreach my $id_field ('001', '003') {
    my $spec_value;
    my @controls = $record->field($id_field);

    if ($id_field eq '001') {
        $spec_value = $rec_id;
    } else {
        $spec_value = $ou_cni;
    }

    # Create the 001/003 if none exist
    if (scalar(@controls) == 0) {
        $record->insert_fields_ordered(MARC::Field->new($id_field, $spec_value));
        $create = 1;
    } elsif (scalar(@controls) > 1) {
        # Do we already have the right 001/003 value in the existing set?
        unless (grep $_->data() eq $spec_value, @controls) {
            $munge = 1;
        }

        # Delete the other fields, as with more than 1 001/003 we do not know which 003/001 to match
        foreach my $control (@controls) {
            unless ($control->data() eq $spec_value) {
                $record->delete_field($control);
            }
        }
    } else {
        # Only one field; check to see if we need to munge it
        unless (grep $_->data() eq $spec_value, @controls) {
            $munge = 1;
        }
    }
}

# Now, if we need to munge the 001, we will first push the existing 001/003 into the 035
if ($munge) {
    my $scn = "(" . $record->field('003')->data() . ")" . $record->field('001')->data();

    # Do not create duplicate 035 fields
    unless (grep $_->subfield('a') eq $scn, @scns) {
        $record->insert_fields_ordered(MARC::Field->new('035', '', '', 'a' => $scn));
    }
}

# Set the 001/003 and update the MARC
if ($create or $munge) {
    $record->field('001')->data($rec_id);
    $record->field('003')->data($ou_cni);

    my $xml = $record->as_xml_record();
    $xml =~ s/\n//sgo;
    $xml =~ s/^<\?xml.+\?\s*>//go;
    $xml =~ s/>\s+</></go;
    $xml =~ s/\p{Cc}//go;

    # Embed a version of OpenILS::Application::AppUtils->entityize()
    # to avoid having to set PERL5LIB for PostgreSQL as well

    # If we are going to convert non-ASCII characters to XML entities,
    # we had better be dealing with a UTF8 string to begin with
    $xml = decode_utf8($xml);

    $xml = NFC($xml);

    # Convert raw ampersands to entities
    $xml =~ s/&(?!\S+;)/&amp;/gso;

    # Convert Unicode characters to entities
    $xml =~ s/([\x{0080}-\x{fffd}])/sprintf('&#x%X;',ord($1))/sgoe;

    $xml =~ s/[\x00-\x1f]//go;
    $_TD->{new}{marc} = $xml;

    return "MODIFY";
}

return;
$func$ LANGUAGE PLPERLU;

CREATE TRIGGER c_maintain_control_numbers BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE maintain_control_numbers();
CREATE TRIGGER c_maintain_control_numbers BEFORE INSERT OR UPDATE ON biblio.record_entry FOR EACH ROW EXECUTE PROCEDURE maintain_control_numbers();
CREATE TRIGGER c_maintain_control_numbers BEFORE INSERT OR UPDATE ON serial.record_entry FOR EACH ROW EXECUTE PROCEDURE maintain_control_numbers();

INSERT INTO metabib.facet_entry (source, field, value)
    SELECT source, field, value FROM (
        SELECT * FROM metabib.author_field_entry
            UNION ALL
        SELECT * FROM metabib.keyword_field_entry
            UNION ALL
        SELECT * FROM metabib.identifier_field_entry
            UNION ALL
        SELECT * FROM metabib.title_field_entry
            UNION ALL
        SELECT * FROM metabib.subject_field_entry
            UNION ALL
        SELECT * FROM metabib.series_field_entry
        )x
    WHERE x.index_vector = '';
        
DELETE FROM metabib.author_field_entry WHERE index_vector = '';
DELETE FROM metabib.keyword_field_entry WHERE index_vector = '';
DELETE FROM metabib.identifier_field_entry WHERE index_vector = '';
DELETE FROM metabib.title_field_entry WHERE index_vector = '';
DELETE FROM metabib.subject_field_entry WHERE index_vector = '';
DELETE FROM metabib.series_field_entry WHERE index_vector = '';

CREATE INDEX metabib_facet_entry_field_idx ON metabib.facet_entry (field);
CREATE INDEX metabib_facet_entry_value_idx ON metabib.facet_entry (SUBSTRING(value,1,1024));
CREATE INDEX metabib_facet_entry_source_idx ON metabib.facet_entry (source);

-- copy OPAC visibility materialized view
CREATE OR REPLACE FUNCTION asset.refresh_opac_visible_copies_mat_view () RETURNS VOID AS $$

    TRUNCATE TABLE asset.opac_visible_copies;

    INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
    SELECT  cp.id, cp.circ_lib, cn.record
    FROM  asset.copy cp
        JOIN asset.call_number cn ON (cn.id = cp.call_number)
        JOIN actor.org_unit a ON (cp.circ_lib = a.id)
        JOIN asset.copy_location cl ON (cp.location = cl.id)
        JOIN config.copy_status cs ON (cp.status = cs.id)
        JOIN biblio.record_entry b ON (cn.record = b.id)
    WHERE NOT cp.deleted
        AND NOT cn.deleted
        AND NOT b.deleted
        AND cs.opac_visible
        AND cl.opac_visible
        AND cp.opac_visible
        AND a.opac_visible;

$$ LANGUAGE SQL;
COMMENT ON FUNCTION asset.refresh_opac_visible_copies_mat_view() IS $$
Rebuild the copy OPAC visibility cache.  Useful during migrations.
$$;

-- and actually populate the table
SELECT asset.refresh_opac_visible_copies_mat_view();

CREATE OR REPLACE FUNCTION asset.cache_copy_visibility () RETURNS TRIGGER as $func$
DECLARE
    add_query       TEXT;
    remove_query    TEXT;
    do_add          BOOLEAN := false;
    do_remove       BOOLEAN := false;
BEGIN
    add_query := $$
            INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
                SELECT  cp.id, cp.circ_lib, cn.record
                  FROM  asset.copy cp
                        JOIN asset.call_number cn ON (cn.id = cp.call_number)
                        JOIN actor.org_unit a ON (cp.circ_lib = a.id)
                        JOIN asset.copy_location cl ON (cp.location = cl.id)
                        JOIN config.copy_status cs ON (cp.status = cs.id)
                        JOIN biblio.record_entry b ON (cn.record = b.id)
                  WHERE NOT cp.deleted
                        AND NOT cn.deleted
                        AND NOT b.deleted
                        AND cs.opac_visible
                        AND cl.opac_visible
                        AND cp.opac_visible
                        AND a.opac_visible
    $$;
 
    remove_query := $$ DELETE FROM asset.opac_visible_copies WHERE id IN ( SELECT id FROM asset.copy WHERE $$;

    IF TG_OP = 'INSERT' THEN

        IF TG_TABLE_NAME IN ('copy', 'unit') THEN
            add_query := add_query || 'AND cp.id = ' || NEW.id || ';';
            EXECUTE add_query;
        END IF;

        RETURN NEW;

    END IF;

    -- handle items first, since with circulation activity
    -- their statuses change frequently
    IF TG_TABLE_NAME IN ('copy', 'unit') THEN

        IF OLD.location    <> NEW.location OR
           OLD.call_number <> NEW.call_number OR
           OLD.status      <> NEW.status OR
           OLD.circ_lib    <> NEW.circ_lib THEN
            -- any of these could change visibility, but
            -- we'll save some queries and not try to calculate
            -- the change directly
            do_remove := true;
            do_add := true;
        ELSE

            IF OLD.deleted <> NEW.deleted THEN
                IF NEW.deleted THEN
                    do_remove := true;
                ELSE
                    do_add := true;
                END IF;
            END IF;

            IF OLD.opac_visible <> NEW.opac_visible THEN
                IF OLD.opac_visible THEN
                    do_remove := true;
                ELSIF NOT do_remove THEN -- handle edge case where deleted item
                                        -- is also marked opac_visible
                    do_add := true;
                END IF;
            END IF;

        END IF;

        IF do_remove THEN
            DELETE FROM asset.opac_visible_copies WHERE id = NEW.id;
        END IF;
        IF do_add THEN
            add_query := add_query || 'AND cp.id = ' || NEW.id || ';';
            EXECUTE add_query;
        END IF;

        RETURN NEW;

    END IF;

    IF TG_TABLE_NAME IN ('call_number', 'record_entry') THEN -- these have a 'deleted' column
 
        IF OLD.deleted AND NEW.deleted THEN -- do nothing

            RETURN NEW;
 
        ELSIF NEW.deleted THEN -- remove rows
 
            IF TG_TABLE_NAME = 'call_number' THEN
                DELETE FROM asset.opac_visible_copies WHERE id IN (SELECT id FROM asset.copy WHERE call_number = NEW.id);
            ELSIF TG_TABLE_NAME = 'record_entry' THEN
                DELETE FROM asset.opac_visible_copies WHERE record = NEW.id;
            END IF;
 
            RETURN NEW;
 
        ELSIF OLD.deleted THEN -- add rows
 
            IF TG_TABLE_NAME IN ('copy','unit') THEN
                add_query := add_query || 'AND cp.id = ' || NEW.id || ';';
            ELSIF TG_TABLE_NAME = 'call_number' THEN
                add_query := add_query || 'AND cp.call_number = ' || NEW.id || ';';
            ELSIF TG_TABLE_NAME = 'record_entry' THEN
                add_query := add_query || 'AND cn.record = ' || NEW.id || ';';
            END IF;
 
            EXECUTE add_query;
            RETURN NEW;
 
        END IF;
 
    END IF;

    IF TG_TABLE_NAME = 'call_number' THEN

        IF OLD.record <> NEW.record THEN
            -- call number is linked to different bib
            remove_query := remove_query || 'call_number = ' || NEW.id || ');';
            EXECUTE remove_query;
            add_query := add_query || 'AND cp.call_number = ' || NEW.id || ';';
            EXECUTE add_query;
        END IF;

        RETURN NEW;

    END IF;

    IF TG_TABLE_NAME IN ('record_entry') THEN
        RETURN NEW; -- don't have 'opac_visible'
    END IF;

    -- actor.org_unit, asset.copy_location, asset.copy_status
    IF NEW.opac_visible = OLD.opac_visible THEN -- do nothing

        RETURN NEW;

    ELSIF NEW.opac_visible THEN -- add rows

        IF TG_TABLE_NAME = 'org_unit' THEN
            add_query := add_query || 'AND cp.circ_lib = ' || NEW.id || ';';
        ELSIF TG_TABLE_NAME = 'copy_location' THEN
            add_query := add_query || 'AND cp.location = ' || NEW.id || ';';
        ELSIF TG_TABLE_NAME = 'copy_status' THEN
            add_query := add_query || 'AND cp.status = ' || NEW.id || ';';
        END IF;
 
        EXECUTE add_query;
 
    ELSE -- delete rows

        IF TG_TABLE_NAME = 'org_unit' THEN
            remove_query := 'DELETE FROM asset.opac_visible_copies WHERE circ_lib = ' || NEW.id || ';';
        ELSIF TG_TABLE_NAME = 'copy_location' THEN
            remove_query := remove_query || 'location = ' || NEW.id || ');';
        ELSIF TG_TABLE_NAME = 'copy_status' THEN
            remove_query := remove_query || 'status = ' || NEW.id || ');';
        END IF;
 
        EXECUTE remove_query;
 
    END IF;
 
    RETURN NEW;
END;
$func$ LANGUAGE PLPGSQL;
COMMENT ON FUNCTION asset.cache_copy_visibility() IS $$
Trigger function to update the copy OPAC visiblity cache.
$$;
CREATE TRIGGER a_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON biblio.record_entry FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
CREATE TRIGGER a_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON asset.copy FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
CREATE TRIGGER a_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON asset.call_number FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
CREATE TRIGGER a_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON asset.copy_location FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
CREATE TRIGGER a_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON serial.unit FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
CREATE TRIGGER a_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON config.copy_status FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
CREATE TRIGGER a_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON actor.org_unit FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();

-- must create this rule explicitly; it is not inherited from asset.copy
CREATE RULE protect_serial_unit_delete AS ON DELETE TO serial.unit DO INSTEAD UPDATE serial.unit SET deleted = TRUE WHERE OLD.id = serial.unit.id;

CREATE RULE protect_authority_rec_delete AS ON DELETE TO authority.record_entry DO INSTEAD (UPDATE authority.record_entry SET deleted = TRUE WHERE OLD.id = authority.record_entry.id);

CREATE OR REPLACE FUNCTION authority.merge_records ( target_record BIGINT, source_record BIGINT ) RETURNS INT AS $func$
DECLARE
    moved_objects INT := 0;
    bib_id        INT := 0;
    bib_rec       biblio.record_entry%ROWTYPE;
    auth_link     authority.bib_linking%ROWTYPE;
    ingest_same   boolean;
BEGIN

    -- 1. Update all bib records with the ID from target_record in their $0
    FOR bib_rec IN SELECT bre.* FROM biblio.record_entry bre
      INNER JOIN authority.bib_linking abl ON abl.bib = bre.id
      WHERE abl.authority = source_record LOOP

        UPDATE biblio.record_entry
          SET marc = REGEXP_REPLACE(marc,
            E'(<subfield\\s+code="0"\\s*>[^<]*?\\))' || source_record || '<',
            E'\\1' || target_record || '<', 'g')
          WHERE id = bib_rec.id;

          moved_objects := moved_objects + 1;
    END LOOP;

    -- 2. Grab the current value of reingest on same MARC flag
    SELECT enabled INTO ingest_same
      FROM config.internal_flag
      WHERE name = 'ingest.reingest.force_on_same_marc'
    ;

    -- 3. Temporarily set reingest on same to TRUE
    UPDATE config.internal_flag
      SET enabled = TRUE
      WHERE name = 'ingest.reingest.force_on_same_marc'
    ;

    -- 4. Make a harmless update to target_record to trigger auto-update
    --    in linked bibliographic records
    UPDATE authority.record_entry
      SET DELETED = FALSE
      WHERE id = source_record;

    -- 5. "Delete" source_record
    DELETE FROM authority.record_entry
      WHERE id = source_record;

    -- 6. Set "reingest on same MARC" flag back to initial value
    UPDATE config.internal_flag
      SET enabled = ingest_same
      WHERE name = 'ingest.reingest.force_on_same_marc'
    ;

    RETURN moved_objects;
END;
$func$ LANGUAGE plpgsql;

-- serial.record_entry already had an owner column spelled "owning_lib"
-- Adjust the table and affected functions accordingly

ALTER TABLE serial.record_entry DROP COLUMN owner;

CREATE TABLE actor.usr_saved_search (
    id              SERIAL          PRIMARY KEY,
	owner           INT             NOT NULL REFERENCES actor.usr (id)
	                                ON DELETE CASCADE
	                                DEFERRABLE INITIALLY DEFERRED,
	name            TEXT            NOT NULL,
	create_date     TIMESTAMPTZ     NOT NULL DEFAULT now(),
	query_text      TEXT            NOT NULL,
	query_type      TEXT            NOT NULL
	                                CONSTRAINT valid_query_text CHECK (
	                                query_type IN ( 'URL' )) DEFAULT 'URL',
	                                -- we may add other types someday
	target          TEXT            NOT NULL
	                                CONSTRAINT valid_target CHECK (
	                                target IN ( 'record', 'metarecord', 'callnumber' )),
	CONSTRAINT name_once_per_user UNIQUE (owner, name)
);

-- Apply Dan Wells' changes to the serial schema, from the
-- seials-integration branch

CREATE TABLE serial.subscription_note (
	id           SERIAL PRIMARY KEY,
	subscription INT    NOT NULL
	                    REFERENCES serial.subscription (id)
	                    ON DELETE CASCADE
	                    DEFERRABLE INITIALLY DEFERRED,
	creator      INT    NOT NULL
	                    REFERENCES actor.usr (id)
	                    DEFERRABLE INITIALLY DEFERRED,
	create_date  TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
	pub          BOOL   NOT NULL DEFAULT FALSE,
	title        TEXT   NOT NULL,
	value        TEXT   NOT NULL
);
CREATE INDEX serial_subscription_note_sub_idx ON serial.subscription_note (subscription);

CREATE TABLE serial.distribution_note (
	id           SERIAL PRIMARY KEY,
	distribution INT    NOT NULL
	                    REFERENCES serial.distribution (id)
	                    ON DELETE CASCADE
	                    DEFERRABLE INITIALLY DEFERRED,
	creator      INT    NOT NULL
	                    REFERENCES actor.usr (id)
	                    DEFERRABLE INITIALLY DEFERRED,
	create_date  TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
	pub          BOOL   NOT NULL DEFAULT FALSE,
	title        TEXT   NOT NULL,
	value        TEXT   NOT NULL
);
CREATE INDEX serial_distribution_note_dist_idx ON serial.distribution_note (distribution);

------- Begin surgery on serial.unit

ALTER TABLE serial.unit
	DROP COLUMN label;

ALTER TABLE serial.unit
	RENAME COLUMN label_sort_key TO sort_key;

ALTER TABLE serial.unit
	RENAME COLUMN contents TO detailed_contents;

ALTER TABLE serial.unit
	ADD COLUMN summary_contents TEXT;

UPDATE serial.unit
SET summary_contents = detailed_contents;

ALTER TABLE serial.unit
	ALTER column summary_contents SET NOT NULL;

------- End surgery on serial.unit

-- DELETE FROM config.upgrade_log WHERE version = 'temp'; DELETE FROM action_trigger.event WHERE event_def IN (33,34); DELETE FROM action_trigger.environment WHERE event_def IN (33,34); DELETE FROM action_trigger.event_definition WHERE id IN (33,34); DELETE FROM action_trigger.hook WHERE key IN ( 'circ.format.missing_pieces.slip.print', 'circ.format.missing_pieces.letter.print' );

-- Now rebuild the constraints dropped via cascade.
-- ALTER TABLE acq.provider    ADD CONSTRAINT provider_edi_default_fkey FOREIGN KEY (edi_default) REFERENCES acq.edi_account (id) DEFERRABLE INITIALLY DEFERRED;
DROP INDEX IF EXISTS money.money_mat_summary_id_idx;
ALTER TABLE money.materialized_billable_xact_summary ADD PRIMARY KEY (id);

-- ALTER TABLE staging.billing_address_stage ADD PRIMARY KEY (row_id);

DELETE FROM config.metabib_field_index_norm_map
    WHERE norm IN (
        SELECT id 
            FROM config.index_normalizer
            WHERE func IN ('first_word', 'naco_normalize', 'split_date_range')
    )
    AND field = 18
;

-- We won't necessarily use all of these, but they are here for completeness.
-- Source is the EDI spec 6063 codelist, eg: http://www.stylusstudio.com/edifact/D04B/6063.htm
-- Values are the EDI code value + 1200

INSERT INTO acq.cancel_reason (org_unit, keep_debits, id, label, description) VALUES 
(1, 't', 1201, 'Discrete quantity', 'Individually separated and distinct quantity.'),
(1, 't', 1202, 'Charge', 'Quantity relevant for charge.'),
(1, 't', 1203, 'Cumulative quantity', 'Quantity accumulated.'),
(1, 't', 1204, 'Interest for overdrawn account', 'Interest for overdrawing the account.'),
(1, 't', 1205, 'Active ingredient dose per unit', 'The dosage of active ingredient per unit.'),
(1, 't', 1206, 'Auditor', 'The number of entities that audit accounts.'),
(1, 't', 1207, 'Branch locations, leased', 'The number of branch locations being leased by an entity.'),
(1, 't', 1208, 'Inventory quantity at supplier''s subject to inspection by', 'customer Quantity of goods which the customer requires the supplier to have in inventory and which may be inspected by the customer if desired.'),
(1, 't', 1209, 'Branch locations, owned', 'The number of branch locations owned by an entity.'),
(1, 't', 1210, 'Judgements registered', 'The number of judgements registered against an entity.'),
(1, 't', 1211, 'Split quantity', 'Part of the whole quantity.'),
(1, 't', 1212, 'Despatch quantity', 'Quantity despatched by the seller.'),
(1, 't', 1213, 'Liens registered', 'The number of liens registered against an entity.'),
(1, 't', 1214, 'Livestock', 'The number of animals kept for use or profit.'),
(1, 't', 1215, 'Insufficient funds returned cheques', 'The number of cheques returned due to insufficient funds.'),
(1, 't', 1216, 'Stolen cheques', 'The number of stolen cheques.'),
(1, 't', 1217, 'Quantity on hand', 'The total quantity of a product on hand at a location. This includes as well units awaiting return to manufacturer, units unavailable due to inspection procedures and undamaged stock available for despatch, resale or use.'),
(1, 't', 1218, 'Previous quantity', 'Quantity previously referenced.'),
(1, 't', 1219, 'Paid-in security shares', 'The number of security shares issued and for which full payment has been made.'),
(1, 't', 1220, 'Unusable quantity', 'Quantity not usable.'),
(1, 't', 1221, 'Ordered quantity', '[6024] The quantity which has been ordered.'),
(1, 't', 1222, 'Quantity at 100%', 'Equivalent quantity at 100% purity.'),
(1, 't', 1223, 'Active ingredient', 'Quantity at 100% active agent content.'),
(1, 't', 1224, 'Inventory quantity at supplier''s not subject to inspection', 'by customer Quantity of goods which the customer requires the supplier to have in inventory but which will not be checked by the customer.'),
(1, 't', 1225, 'Retail sales', 'Quantity of retail point of sale activity.'),
(1, 't', 1226, 'Promotion quantity', 'A quantity associated with a promotional event.'),
(1, 't', 1227, 'On hold for shipment', 'Article received which cannot be shipped in its present form.'),
(1, 't', 1228, 'Military sales quantity', 'Quantity of goods or services sold to a military organization.'),
(1, 't', 1229, 'On premises sales',  'Sale of product in restaurants or bars.'),
(1, 't', 1230, 'Off premises sales', 'Sale of product directly to a store.'),
(1, 't', 1231, 'Estimated annual volume', 'Volume estimated for a year.'),
(1, 't', 1232, 'Minimum delivery batch', 'Minimum quantity of goods delivered at one time.'),
(1, 't', 1233, 'Maximum delivery batch', 'Maximum quantity of goods delivered at one time.'),
(1, 't', 1234, 'Pipes', 'The number of tubes used to convey a substance.'),
(1, 't', 1235, 'Price break from', 'The minimum quantity of a quantity range for a specified (unit) price.'),
(1, 't', 1236, 'Price break to', 'Maximum quantity to which the price break applies.'),
(1, 't', 1237, 'Poultry', 'The number of domestic fowl.'),
(1, 't', 1238, 'Secured charges registered', 'The number of secured charges registered against an entity.'),
(1, 't', 1239, 'Total properties owned', 'The total number of properties owned by an entity.'),
(1, 't', 1240, 'Normal delivery', 'Quantity normally delivered by the seller.'),
(1, 't', 1241, 'Sales quantity not included in the replenishment', 'calculation Sales which will not be included in the calculation of replenishment requirements.'),
(1, 't', 1242, 'Maximum supply quantity, supplier endorsed', 'Maximum supply quantity endorsed by a supplier.'),
(1, 't', 1243, 'Buyer', 'The number of buyers.'),
(1, 't', 1244, 'Debenture bond', 'The number of fixed-interest bonds of an entity backed by general credit rather than specified assets.'),
(1, 't', 1245, 'Debentures filed against directors', 'The number of notices of indebtedness filed against an entity''s directors.'),
(1, 't', 1246, 'Pieces delivered', 'Number of pieces actually received at the final destination.'),
(1, 't', 1247, 'Invoiced quantity', 'The quantity as per invoice.'),
(1, 't', 1248, 'Received quantity', 'The quantity which has been received.'),
(1, 't', 1249, 'Chargeable distance', '[6110] The distance between two points for which a specific tariff applies.'),
(1, 't', 1250, 'Disposition undetermined quantity', 'Product quantity that has not yet had its disposition determined.'),
(1, 't', 1251, 'Inventory category transfer', 'Inventory that has been moved from one inventory category to another.'),
(1, 't', 1252, 'Quantity per pack', 'Quantity for each pack.'),
(1, 't', 1253, 'Minimum order quantity', 'Minimum quantity of goods for an order.'),
(1, 't', 1254, 'Maximum order quantity', 'Maximum quantity of goods for an order.'),
(1, 't', 1255, 'Total sales', 'The summation of total quantity sales.'),
(1, 't', 1256, 'Wholesaler to wholesaler sales', 'Sale of product to other wholesalers by a wholesaler.'),
(1, 't', 1257, 'In transit quantity', 'A quantity that is en route.'),
(1, 't', 1258, 'Quantity withdrawn', 'Quantity withdrawn from a location.'),
(1, 't', 1259, 'Numbers of consumer units in the traded unit', 'Number of units for consumer sales in a unit for trading.'),
(1, 't', 1260, 'Current inventory quantity available for shipment', 'Current inventory quantity available for shipment.'),
(1, 't', 1261, 'Return quantity', 'Quantity of goods returned.'),
(1, 't', 1262, 'Sorted quantity', 'The quantity that is sorted.'),
(1, 'f', 1263, 'Sorted quantity rejected', 'The sorted quantity that is rejected.'),
(1, 't', 1264, 'Scrap quantity', 'Remainder of the total quantity after split deliveries.'),
(1, 'f', 1265, 'Destroyed quantity', 'Quantity of goods destroyed.'),
(1, 't', 1266, 'Committed quantity', 'Quantity a party is committed to.'),
(1, 't', 1267, 'Estimated reading quantity', 'The value that is estimated to be the reading of a measuring device (e.g. meter).'),
(1, 't', 1268, 'End quantity', 'The quantity recorded at the end of an agreement or period.'),
(1, 't', 1269, 'Start quantity', 'The quantity recorded at the start of an agreement or period.'),
(1, 't', 1270, 'Cumulative quantity received', 'Cumulative quantity of all deliveries of this article received by the buyer.'),
(1, 't', 1271, 'Cumulative quantity ordered', 'Cumulative quantity of all deliveries, outstanding and scheduled orders.'),
(1, 't', 1272, 'Cumulative quantity received end of prior year', 'Cumulative quantity of all deliveries of the product received by the buyer till end of prior year.'),
(1, 't', 1273, 'Outstanding quantity', 'Difference between quantity ordered and quantity received.'),
(1, 't', 1274, 'Latest cumulative quantity', 'Cumulative quantity after complete delivery of all scheduled quantities of the product.'),
(1, 't', 1275, 'Previous highest cumulative quantity', 'Cumulative quantity after complete delivery of all scheduled quantities of the product from a prior schedule period.'),
(1, 't', 1276, 'Adjusted corrector reading', 'A corrector reading after it has been adjusted.'),
(1, 't', 1277, 'Work days', 'Number of work days, e.g. per respective period.'),
(1, 't', 1278, 'Cumulative quantity scheduled', 'Adding the quantity actually scheduled to previous cumulative quantity.'),
(1, 't', 1279, 'Previous cumulative quantity', 'Cumulative quantity prior the actual order.'),
(1, 't', 1280, 'Unadjusted corrector reading', 'A corrector reading before it has been adjusted.'),
(1, 't', 1281, 'Extra unplanned delivery', 'Non scheduled additional quantity.'),
(1, 't', 1282, 'Quantity requirement for sample inspection', 'Required quantity for sample inspection.'),
(1, 't', 1283, 'Backorder quantity', 'The quantity of goods that is on back-order.'),
(1, 't', 1284, 'Urgent delivery quantity', 'Quantity for urgent delivery.'),
(1, 'f', 1285, 'Previous order quantity to be cancelled', 'Quantity ordered previously to be cancelled.'),
(1, 't', 1286, 'Normal reading quantity', 'The value recorded or read from a measuring device (e.g. meter) in the normal conditions.'),
(1, 't', 1287, 'Customer reading quantity', 'The value recorded or read from a measuring device (e.g. meter) by the customer.'),
(1, 't', 1288, 'Information reading quantity', 'The value recorded or read from a measuring device (e.g. meter) for information purposes.'),
(1, 't', 1289, 'Quality control held', 'Quantity of goods held pending completion of a quality control assessment.'),
(1, 't', 1290, 'As is quantity', 'Quantity as it is in the existing circumstances.'),
(1, 't', 1291, 'Open quantity', 'Quantity remaining after partial delivery.'),
(1, 't', 1292, 'Final delivery quantity', 'Quantity of final delivery to a respective order.'),
(1, 't', 1293, 'Subsequent delivery quantity', 'Quantity delivered to a respective order after it''s final delivery.'),
(1, 't', 1294, 'Substitutional quantity', 'Quantity delivered replacing previous deliveries.'),
(1, 't', 1295, 'Redelivery after post processing', 'Quantity redelivered after post processing.'),
(1, 'f', 1296, 'Quality control failed', 'Quantity of goods which have failed quality control.'),
(1, 't', 1297, 'Minimum inventory', 'Minimum stock quantity on which replenishment is based.'),
(1, 't', 1298, 'Maximum inventory', 'Maximum stock quantity on which replenishment is based.'),
(1, 't', 1299, 'Estimated quantity', 'Quantity estimated.'),
(1, 't', 1300, 'Chargeable weight', 'The weight on which charges are based.'),
(1, 't', 1301, 'Chargeable gross weight', 'The gross weight on which charges are based.'),
(1, 't', 1302, 'Chargeable tare weight', 'The tare weight on which charges are based.'),
(1, 't', 1303, 'Chargeable number of axles', 'The number of axles on which charges are based.'),
(1, 't', 1304, 'Chargeable number of containers', 'The number of containers on which charges are based.'),
(1, 't', 1305, 'Chargeable number of rail wagons', 'The number of rail wagons on which charges are based.'),
(1, 't', 1306, 'Chargeable number of packages', 'The number of packages on which charges are based.'),
(1, 't', 1307, 'Chargeable number of units', 'The number of units on which charges are based.'),
(1, 't', 1308, 'Chargeable period', 'The period of time on which charges are based.'),
(1, 't', 1309, 'Chargeable volume', 'The volume on which charges are based.'),
(1, 't', 1310, 'Chargeable cubic measurements', 'The cubic measurements on which charges are based.'),
(1, 't', 1311, 'Chargeable surface', 'The surface area on which charges are based.'),
(1, 't', 1312, 'Chargeable length', 'The length on which charges are based.'),
(1, 't', 1313, 'Quantity to be delivered', 'The quantity to be delivered.'),
(1, 't', 1314, 'Number of passengers', 'Total number of passengers on the conveyance.'),
(1, 't', 1315, 'Number of crew', 'Total number of crew members on the conveyance.'),
(1, 't', 1316, 'Number of transport documents', 'Total number of air waybills, bills of lading, etc. being reported for a specific conveyance.'),
(1, 't', 1317, 'Quantity landed', 'Quantity of goods actually arrived.'),
(1, 't', 1318, 'Quantity manifested', 'Quantity of goods contracted for delivery by the carrier.'),
(1, 't', 1319, 'Short shipped', 'Indication that part of the consignment was not shipped.'),
(1, 't', 1320, 'Split shipment', 'Indication that the consignment has been split into two or more shipments.'),
(1, 't', 1321, 'Over shipped', 'The quantity of goods shipped that exceeds the quantity contracted.'),
(1, 't', 1322, 'Short-landed goods', 'If quantity of goods actually landed is less than the quantity which appears in the documentation. This quantity is the difference between these quantities.'),
(1, 't', 1323, 'Surplus goods', 'If quantity of goods actually landed is more than the quantity which appears in the documentation. This quantity is the difference between these quantities.'),
(1, 'f', 1324, 'Damaged goods', 'Quantity of goods which have deteriorated in transport such that they cannot be used for the purpose for which they were originally intended.'),
(1, 'f', 1325, 'Pilferage goods', 'Quantity of goods stolen during transport.'),
(1, 'f', 1326, 'Lost goods', 'Quantity of goods that disappeared in transport.'),
(1, 't', 1327, 'Report difference', 'The quantity concerning the same transaction differs between two documents/messages and the source of this difference is a typing error.'),
(1, 't', 1328, 'Quantity loaded', 'Quantity of goods loaded onto a means of transport.'),
(1, 't', 1329, 'Units per unit price', 'Number of units per unit price.'),
(1, 't', 1330, 'Allowance', 'Quantity relevant for allowance.'),
(1, 't', 1331, 'Delivery quantity', 'Quantity required by buyer to be delivered.'),
(1, 't', 1332, 'Cumulative quantity, preceding period, planned', 'Cumulative quantity originally planned for the preceding period.'),
(1, 't', 1333, 'Cumulative quantity, preceding period, reached', 'Cumulative quantity reached in the preceding period.'),
(1, 't', 1334, 'Cumulative quantity, actual planned',            'Cumulative quantity planned for now.'),
(1, 't', 1335, 'Period quantity, planned', 'Quantity planned for this period.'),
(1, 't', 1336, 'Period quantity, reached', 'Quantity reached during this period.'),
(1, 't', 1337, 'Cumulative quantity, preceding period, estimated', 'Estimated cumulative quantity reached in the preceding period.'),
(1, 't', 1338, 'Cumulative quantity, actual estimated',            'Estimated cumulative quantity reached now.'),
(1, 't', 1339, 'Cumulative quantity, preceding period, measured', 'Surveyed cumulative quantity reached in the preceding period.'),
(1, 't', 1340, 'Cumulative quantity, actual measured', 'Surveyed cumulative quantity reached now.'),
(1, 't', 1341, 'Period quantity, measured',            'Surveyed quantity reached during this period.'),
(1, 't', 1342, 'Total quantity, planned', 'Total quantity planned.'),
(1, 't', 1343, 'Quantity, remaining', 'Quantity remaining.'),
(1, 't', 1344, 'Tolerance', 'Plus or minus tolerance expressed as a monetary amount.'),
(1, 't', 1345, 'Actual stock',          'The stock on hand, undamaged, and available for despatch, sale or use.'),
(1, 't', 1346, 'Model or target stock', 'The stock quantity required or planned to have on hand, undamaged and available for use.'),
(1, 't', 1347, 'Direct shipment quantity', 'Quantity to be shipped directly to a customer from a manufacturing site.'),
(1, 't', 1348, 'Amortization total quantity',     'Indication of final quantity for amortization.'),
(1, 't', 1349, 'Amortization order quantity',     'Indication of actual share of the order quantity for amortization.'),
(1, 't', 1350, 'Amortization cumulated quantity', 'Indication of actual cumulated quantity of previous and actual amortization order quantity.'),
(1, 't', 1351, 'Quantity advised',  'Quantity advised by supplier or shipper, in contrast to quantity actually received.'),
(1, 't', 1352, 'Consignment stock', 'Quantity of goods with an external customer which is still the property of the supplier. Payment for these goods is only made to the supplier when the ownership has been transferred between the trading partners.'),
(1, 't', 1353, 'Statistical sales quantity', 'Quantity of goods sold in a specified period.'),
(1, 't', 1354, 'Sales quantity planned',     'Quantity of goods required to meet future demands. - Market intelligence quantity.'),
(1, 't', 1355, 'Replenishment quantity',     'Quantity required to maintain the requisite on-hand stock of goods.'),
(1, 't', 1356, 'Inventory movement quantity', 'To specify the quantity of an inventory movement.'),
(1, 't', 1357, 'Opening stock balance quantity', 'To specify the quantity of an opening stock balance.'),
(1, 't', 1358, 'Closing stock balance quantity', 'To specify the quantity of a closing stock balance.'),
(1, 't', 1359, 'Number of stops', 'Number of times a means of transport stops before arriving at destination.'),
(1, 't', 1360, 'Minimum production batch', 'The quantity specified is the minimum output from a single production run.'),
(1, 't', 1361, 'Dimensional sample quantity', 'The quantity defined is a sample for the purpose of validating dimensions.'),
(1, 't', 1362, 'Functional sample quantity', 'The quantity defined is a sample for the purpose of validating function and performance.'),
(1, 't', 1363, 'Pre-production quantity', 'Quantity of the referenced item required prior to full production.'),
(1, 't', 1364, 'Delivery batch', 'Quantity of the referenced item which constitutes a standard batch for deliver purposes.'),
(1, 't', 1365, 'Delivery batch multiple', 'The multiples in which delivery batches can be supplied.'),
(1, 't', 1366, 'All time buy',             'The total quantity of the referenced covering all future needs. Further orders of the referenced item are not expected.'),
(1, 't', 1367, 'Total delivery quantity',  'The total quantity required by the buyer to be delivered.'),
(1, 't', 1368, 'Single delivery quantity', 'The quantity required by the buyer to be delivered in a single shipment.'),
(1, 't', 1369, 'Supplied quantity',  'Quantity of the referenced item actually shipped.'),
(1, 't', 1370, 'Allocated quantity', 'Quantity of the referenced item allocated from available stock for delivery.'),
(1, 't', 1371, 'Maximum stackability', 'The number of pallets/handling units which can be safely stacked one on top of another.'),
(1, 't', 1372, 'Amortisation quantity', 'The quantity of the referenced item which has a cost for tooling amortisation included in the item price.'),
(1, 't', 1373, 'Previously amortised quantity', 'The cumulative quantity of the referenced item which had a cost for tooling amortisation included in the item price.'),
(1, 't', 1374, 'Total amortisation quantity', 'The total quantity of the referenced item which has a cost for tooling amortisation included in the item price.'),
(1, 't', 1375, 'Number of moulds', 'The number of pressing moulds contained within a single piece of the referenced tooling.'),
(1, 't', 1376, 'Concurrent item output of tooling', 'The number of related items which can be produced simultaneously with a single piece of the referenced tooling.'),
(1, 't', 1377, 'Periodic capacity of tooling', 'Maximum production output of the referenced tool over a period of time.'),
(1, 't', 1378, 'Lifetime capacity of tooling', 'Maximum production output of the referenced tool over its productive lifetime.'),
(1, 't', 1379, 'Number of deliveries per despatch period', 'The number of deliveries normally expected to be despatched within each despatch period.'),
(1, 't', 1380, 'Provided quantity', 'The quantity of a referenced component supplied by the buyer for manufacturing of an ordered item.'),
(1, 't', 1381, 'Maximum production batch', 'The quantity specified is the maximum output from a single production run.'),
(1, 'f', 1382, 'Cancelled quantity', 'Quantity of the referenced item which has previously been ordered and is now cancelled.'),
(1, 't', 1383, 'No delivery requirement in this instruction', 'This delivery instruction does not contain any delivery requirements.'),
(1, 't', 1384, 'Quantity of material in ordered time', 'Quantity of the referenced material within the ordered time.'),
(1, 'f', 1385, 'Rejected quantity', 'The quantity of received goods rejected for quantity reasons.'),
(1, 't', 1386, 'Cumulative quantity scheduled up to accumulation start date', 'The cumulative quantity scheduled up to the accumulation start date.'),
(1, 't', 1387, 'Quantity scheduled', 'The quantity scheduled for delivery.'),
(1, 't', 1388, 'Number of identical handling units', 'Number of identical handling units in terms of type and contents.'),
(1, 't', 1389, 'Number of packages in handling unit', 'The number of packages contained in one handling unit.'),
(1, 't', 1390, 'Despatch note quantity', 'The item quantity specified on the despatch note.'),
(1, 't', 1391, 'Adjustment to inventory quantity', 'An adjustment to inventory quantity.'),
(1, 't', 1392, 'Free goods quantity',    'Quantity of goods which are free of charge.'),
(1, 't', 1393, 'Free quantity included', 'Quantity included to which no charge is applicable.'),
(1, 't', 1394, 'Received and accepted',  'Quantity which has been received and accepted at a given location.'),
(1, 'f', 1395, 'Received, not accepted, to be returned',  'Quantity which has been received but not accepted at a given location and which will consequently be returned to the relevant party.'),
(1, 'f', 1396, 'Received, not accepted, to be destroyed', 'Quantity which has been received but not accepted at a given location and which will consequently be destroyed.'),
(1, 't', 1397, 'Reordering level', 'Quantity at which an order may be triggered to replenish.'),
(1, 't', 1399, 'Inventory withdrawal quantity', 'Quantity which has been withdrawn from inventory since the last inventory report.'),
(1, 't', 1400, 'Free quantity not included', 'Free quantity not included in ordered quantity.'),
(1, 't', 1401, 'Recommended overhaul and repair quantity', 'To indicate the recommended quantity of an article required to support overhaul and repair activities.'),
(1, 't', 1402, 'Quantity per next higher assembly', 'To indicate the quantity required for the next higher assembly.'),
(1, 't', 1403, 'Quantity per unit of issue', 'Provides the standard quantity of an article in which one unit can be issued.'),
(1, 't', 1404, 'Cumulative scrap quantity',  'Provides the cumulative quantity of an item which has been identified as scrapped.'),
(1, 't', 1405, 'Publication turn size', 'The quantity of magazines or newspapers grouped together with the spine facing alternate directions in a bundle.'),
(1, 't', 1406, 'Recommended maintenance quantity', 'Recommended quantity of an article which is required to meet an agreed level of maintenance.'),
(1, 't', 1407, 'Labour hours', 'Number of labour hours.'),
(1, 't', 1408, 'Quantity requirement for maintenance and repair of', 'equipment Quantity of the material needed to maintain and repair equipment.'),
(1, 't', 1409, 'Additional replenishment demand quantity', 'Incremental needs over and above normal replenishment calculations, but not intended to permanently change the model parameters.'),
(1, 't', 1410, 'Returned by consumer quantity', 'Quantity returned by a consumer.'),
(1, 't', 1411, 'Replenishment override quantity', 'Quantity to override the normal replenishment model calculations, but not intended to permanently change the model parameters.'),
(1, 't', 1412, 'Quantity sold, net', 'Net quantity sold which includes returns of saleable inventory and other adjustments.'),
(1, 't', 1413, 'Transferred out quantity',   'Quantity which was transferred out of this location.'),
(1, 't', 1414, 'Transferred in quantity',    'Quantity which was transferred into this location.'),
(1, 't', 1415, 'Unsaleable quantity',        'Quantity of inventory received which cannot be sold in its present condition.'),
(1, 't', 1416, 'Consumer reserved quantity', 'Quantity reserved for consumer delivery or pickup and not yet withdrawn from inventory.'),
(1, 't', 1417, 'Out of inventory quantity',  'Quantity of inventory which was requested but was not available.'),
(1, 't', 1418, 'Quantity returned, defective or damaged', 'Quantity returned in a damaged or defective condition.'),
(1, 't', 1419, 'Taxable quantity',           'Quantity subject to taxation.'),
(1, 't', 1420, 'Meter reading', 'The numeric value of measure units counted by a meter.'),
(1, 't', 1421, 'Maximum requestable quantity', 'The maximum quantity which may be requested.'),
(1, 't', 1422, 'Minimum requestable quantity', 'The minimum quantity which may be requested.'),
(1, 't', 1423, 'Daily average quantity', 'The quantity for a defined period divided by the number of days of the period.'),
(1, 't', 1424, 'Budgeted hours',     'The number of budgeted hours.'),
(1, 't', 1425, 'Actual hours',       'The number of actual hours.'),
(1, 't', 1426, 'Earned value hours', 'The number of earned value hours.'),
(1, 't', 1427, 'Estimated hours',    'The number of estimated hours.'),
(1, 't', 1428, 'Level resource task quantity', 'Quantity of a resource that is level for the duration of the task.'),
(1, 't', 1429, 'Available resource task quantity', 'Quantity of a resource available to complete a task.'),
(1, 't', 1430, 'Work time units',   'Quantity of work units of time.'),
(1, 't', 1431, 'Daily work shifts', 'Quantity of work shifts per day.'),
(1, 't', 1432, 'Work time units per shift', 'Work units of time per work shift.'),
(1, 't', 1433, 'Work calendar units',       'Work calendar units of time.'),
(1, 't', 1434, 'Elapsed duration',   'Quantity representing the elapsed duration.'),
(1, 't', 1435, 'Remaining duration', 'Quantity representing the remaining duration.'),
(1, 't', 1436, 'Original duration',  'Quantity representing the original duration.'),
(1, 't', 1437, 'Current duration',   'Quantity representing the current duration.'),
(1, 't', 1438, 'Total float time',   'Quantity representing the total float time.'),
(1, 't', 1439, 'Free float time',    'Quantity representing the free float time.'),
(1, 't', 1440, 'Lag time',           'Quantity representing lag time.'),
(1, 't', 1441, 'Lead time',          'Quantity representing lead time.'),
(1, 't', 1442, 'Number of months', 'The number of months.'),
(1, 't', 1443, 'Reserved quantity customer direct delivery sales', 'Quantity of products reserved for sales delivered direct to the customer.'),
(1, 't', 1444, 'Reserved quantity retail sales', 'Quantity of products reserved for retail sales.'),
(1, 't', 1445, 'Consolidated discount inventory', 'A quantity of inventory supplied at consolidated discount terms.'),
(1, 't', 1446, 'Returns replacement quantity',    'A quantity of goods issued as a replacement for a returned quantity.'),
(1, 't', 1447, 'Additional promotion sales forecast quantity', 'A forecast of additional quantity which will be sold during a period of promotional activity.'),
(1, 't', 1448, 'Reserved quantity', 'Quantity reserved for specific purposes.'),
(1, 't', 1449, 'Quantity displayed not available for sale', 'Quantity displayed within a retail outlet but not available for sale.'),
(1, 't', 1450, 'Inventory discrepancy', 'The difference recorded between theoretical and physical inventory.'),
(1, 't', 1451, 'Incremental order quantity', 'The incremental quantity by which ordering is carried out.'),
(1, 't', 1452, 'Quantity requiring manipulation before despatch', 'A quantity of goods which needs manipulation before despatch.'),
(1, 't', 1453, 'Quantity in quarantine',              'A quantity of goods which are held in a restricted area for quarantine purposes.'),
(1, 't', 1454, 'Quantity withheld by owner of goods', 'A quantity of goods which has been withheld by the owner of the goods.'),
(1, 't', 1455, 'Quantity not available for despatch', 'A quantity of goods not available for despatch.'),
(1, 't', 1456, 'Quantity awaiting delivery', 'Quantity of goods which are awaiting delivery.'),
(1, 't', 1457, 'Quantity in physical inventory',      'A quantity of goods held in physical inventory.'),
(1, 't', 1458, 'Quantity held by logistic service provider', 'Quantity of goods under the control of a logistic service provider.'),
(1, 't', 1459, 'Optimal quantity', 'The optimal quantity for a given purpose.'),
(1, 't', 1460, 'Delivery quantity balance', 'The difference between the scheduled quantity and the quantity delivered to the consignee at a given date.'),
(1, 't', 1461, 'Cumulative quantity shipped', 'Cumulative quantity of all shipments.'),
(1, 't', 1462, 'Quantity suspended', 'The quantity of something which is suspended.'),
(1, 't', 1463, 'Control quantity', 'The quantity designated for control purposes.'),
(1, 't', 1464, 'Equipment quantity', 'A count of a quantity of equipment.'),
(1, 't', 1465, 'Factor', 'Number by which the measured unit has to be multiplied to calculate the units used.'),
(1, 't', 1466, 'Unsold quantity held by wholesaler', 'Unsold quantity held by the wholesaler.'),
(1, 't', 1467, 'Quantity held by delivery vehicle', 'Quantity of goods held by the delivery vehicle.'),
(1, 't', 1468, 'Quantity held by retail outlet', 'Quantity held by the retail outlet.'),
(1, 'f', 1469, 'Rejected return quantity', 'A quantity for return which has been rejected.'),
(1, 't', 1470, 'Accounts', 'The number of accounts.'),
(1, 't', 1471, 'Accounts placed for collection', 'The number of accounts placed for collection.'),
(1, 't', 1472, 'Activity codes', 'The number of activity codes.'),
(1, 't', 1473, 'Agents', 'The number of agents.'),
(1, 't', 1474, 'Airline attendants', 'The number of airline attendants.'),
(1, 't', 1475, 'Authorised shares',  'The number of shares authorised for issue.'),
(1, 't', 1476, 'Employee average',   'The average number of employees.'),
(1, 't', 1477, 'Branch locations',   'The number of branch locations.'),
(1, 't', 1478, 'Capital changes',    'The number of capital changes made.'),
(1, 't', 1479, 'Clerks', 'The number of clerks.'),
(1, 't', 1480, 'Companies in same activity', 'The number of companies doing business in the same activity category.'),
(1, 't', 1481, 'Companies included in consolidated financial statement', 'The number of companies included in a consolidated financial statement.'),
(1, 't', 1482, 'Cooperative shares', 'The number of cooperative shares.'),
(1, 't', 1483, 'Creditors',   'The number of creditors.'),
(1, 't', 1484, 'Departments', 'The number of departments.'),
(1, 't', 1485, 'Design employees', 'The number of employees involved in the design process.'),
(1, 't', 1486, 'Physicians', 'The number of medical doctors.'),
(1, 't', 1487, 'Domestic affiliated companies', 'The number of affiliated companies located within the country.'),
(1, 't', 1488, 'Drivers', 'The number of drivers.'),
(1, 't', 1489, 'Employed at location',     'The number of employees at the specified location.'),
(1, 't', 1490, 'Employed by this company', 'The number of employees at the specified company.'),
(1, 't', 1491, 'Total employees',    'The total number of employees.'),
(1, 't', 1492, 'Employees shared',   'The number of employees shared among entities.'),
(1, 't', 1493, 'Engineers',          'The number of engineers.'),
(1, 't', 1494, 'Estimated accounts', 'The estimated number of accounts.'),
(1, 't', 1495, 'Estimated employees at location', 'The estimated number of employees at the specified location.'),
(1, 't', 1496, 'Estimated total employees',       'The total estimated number of employees.'),
(1, 't', 1497, 'Executives', 'The number of executives.'),
(1, 't', 1498, 'Agricultural workers',   'The number of agricultural workers.'),
(1, 't', 1499, 'Financial institutions', 'The number of financial institutions.'),
(1, 't', 1500, 'Floors occupied', 'The number of floors occupied.'),
(1, 't', 1501, 'Foreign related entities', 'The number of related entities located outside the country.'),
(1, 't', 1502, 'Group employees',    'The number of employees within the group.'),
(1, 't', 1503, 'Indirect employees', 'The number of employees not associated with direct production.'),
(1, 't', 1504, 'Installers',    'The number of employees involved with the installation process.'),
(1, 't', 1505, 'Invoices',      'The number of invoices.'),
(1, 't', 1506, 'Issued shares', 'The number of shares actually issued.'),
(1, 't', 1507, 'Labourers',     'The number of labourers.'),
(1, 't', 1508, 'Manufactured units', 'The number of units manufactured.'),
(1, 't', 1509, 'Maximum number of employees', 'The maximum number of people employed.'),
(1, 't', 1510, 'Maximum number of employees at location', 'The maximum number of people employed at a location.'),
(1, 't', 1511, 'Members in group', 'The number of members within a group.'),
(1, 't', 1512, 'Minimum number of employees at location', 'The minimum number of people employed at a location.'),
(1, 't', 1513, 'Minimum number of employees', 'The minimum number of people employed.'),
(1, 't', 1514, 'Non-union employees', 'The number of employees not belonging to a labour union.'),
(1, 't', 1515, 'Floors', 'The number of floors in a building.'),
(1, 't', 1516, 'Nurses', 'The number of nurses.'),
(1, 't', 1517, 'Office workers', 'The number of workers in an office.'),
(1, 't', 1518, 'Other employees', 'The number of employees otherwise categorised.'),
(1, 't', 1519, 'Part time employees', 'The number of employees working on a part time basis.'),
(1, 't', 1520, 'Accounts payable average overdue days', 'The average number of days accounts payable are overdue.'),
(1, 't', 1521, 'Pilots', 'The number of pilots.'),
(1, 't', 1522, 'Plant workers', 'The number of workers within a plant.'),
(1, 't', 1523, 'Previous number of accounts', 'The number of accounts which preceded the current count.'),
(1, 't', 1524, 'Previous number of branch locations', 'The number of branch locations which preceded the current count.'),
(1, 't', 1525, 'Principals included as employees', 'The number of principals which are included in the count of employees.'),
(1, 't', 1526, 'Protested bills', 'The number of bills which are protested.'),
(1, 't', 1527, 'Registered brands distributed', 'The number of registered brands which are being distributed.'),
(1, 't', 1528, 'Registered brands manufactured', 'The number of registered brands which are being manufactured.'),
(1, 't', 1529, 'Related business entities', 'The number of related business entities.'),
(1, 't', 1530, 'Relatives employed', 'The number of relatives which are counted as employees.'),
(1, 't', 1531, 'Rooms',        'The number of rooms.'),
(1, 't', 1532, 'Salespersons', 'The number of salespersons.'),
(1, 't', 1533, 'Seats',        'The number of seats.'),
(1, 't', 1534, 'Shareholders', 'The number of shareholders.'),
(1, 't', 1535, 'Shares of common stock', 'The number of shares of common stock.'),
(1, 't', 1536, 'Shares of preferred stock', 'The number of shares of preferred stock.'),
(1, 't', 1537, 'Silent partners', 'The number of silent partners.'),
(1, 't', 1538, 'Subcontractors',  'The number of subcontractors.'),
(1, 't', 1539, 'Subsidiaries',    'The number of subsidiaries.'),
(1, 't', 1540, 'Law suits',       'The number of law suits.'),
(1, 't', 1541, 'Suppliers',       'The number of suppliers.'),
(1, 't', 1542, 'Teachers',        'The number of teachers.'),
(1, 't', 1543, 'Technicians',     'The number of technicians.'),
(1, 't', 1544, 'Trainees',        'The number of trainees.'),
(1, 't', 1545, 'Union employees', 'The number of employees who are members of a labour union.'),
(1, 't', 1546, 'Number of units', 'The quantity of units.'),
(1, 't', 1547, 'Warehouse employees', 'The number of employees who work in a warehouse setting.'),
(1, 't', 1548, 'Shareholders holding remainder of shares', 'Number of shareholders owning the remainder of shares.'),
(1, 't', 1549, 'Payment orders filed', 'Number of payment orders filed.'),
(1, 't', 1550, 'Uncovered cheques', 'Number of uncovered cheques.'),
(1, 't', 1551, 'Auctions', 'Number of auctions.'),
(1, 't', 1552, 'Units produced', 'The number of units produced.'),
(1, 't', 1553, 'Added employees', 'Number of employees that were added to the workforce.'),
(1, 't', 1554, 'Number of added locations', 'Number of locations that were added.'),
(1, 't', 1555, 'Total number of foreign subsidiaries not included in', 'financial statement The total number of foreign subsidiaries not included in the financial statement.'),
(1, 't', 1556, 'Number of closed locations', 'Number of locations that were closed.'),
(1, 't', 1557, 'Counter clerks', 'The number of clerks that work behind a flat-topped fitment.'),
(1, 't', 1558, 'Payment experiences in the last 3 months', 'The number of payment experiences received for an entity over the last 3 months.'),
(1, 't', 1559, 'Payment experiences in the last 12 months', 'The number of payment experiences received for an entity over the last 12 months.'),
(1, 't', 1560, 'Total number of subsidiaries not included in the financial', 'statement The total number of subsidiaries not included in the financial statement.'),
(1, 't', 1561, 'Paid-in common shares', 'The number of paid-in common shares.'),
(1, 't', 1562, 'Total number of domestic subsidiaries not included in', 'financial statement The total number of domestic subsidiaries not included in the financial statement.'),
(1, 't', 1563, 'Total number of foreign subsidiaries included in financial statement', 'The total number of foreign subsidiaries included in the financial statement.'),
(1, 't', 1564, 'Total number of domestic subsidiaries included in financial statement', 'The total number of domestic subsidiaries included in the financial statement.'),
(1, 't', 1565, 'Total transactions', 'The total number of transactions.'),
(1, 't', 1566, 'Paid-in preferred shares', 'The number of paid-in preferred shares.'),
(1, 't', 1567, 'Employees', 'Code specifying the quantity of persons working for a company, whose services are used for pay.'),
(1, 't', 1568, 'Active ingredient dose per unit, dispensed', 'The dosage of active ingredient per dispensed unit.'),
(1, 't', 1569, 'Budget', 'Budget quantity.'),
(1, 't', 1570, 'Budget, cumulative to date', 'Budget quantity, cumulative to date.'),
(1, 't', 1571, 'Actual units', 'The number of actual units.'),
(1, 't', 1572, 'Actual units, cumulative to date', 'The number of cumulative to date actual units.'),
(1, 't', 1573, 'Earned value', 'Earned value quantity.'),
(1, 't', 1574, 'Earned value, cumulative to date', 'Earned value quantity accumulated to date.'),
(1, 't', 1575, 'At completion quantity, estimated', 'The estimated quantity when a project is complete.'),
(1, 't', 1576, 'To complete quantity, estimated', 'The estimated quantity required to complete a project.'),
(1, 't', 1577, 'Adjusted units', 'The number of adjusted units.'),
(1, 't', 1578, 'Number of limited partnership shares', 'Number of shares held in a limited partnership.'),
(1, 't', 1579, 'National business failure incidences', 'Number of firms in a country that discontinued with a loss to creditors.'),
(1, 't', 1580, 'Industry business failure incidences', 'Number of firms in a specific industry that discontinued with a loss to creditors.'),
(1, 't', 1581, 'Business class failure incidences', 'Number of firms in a specific class that discontinued with a loss to creditors.'),
(1, 't', 1582, 'Mechanics', 'Number of mechanics.'),
(1, 't', 1583, 'Messengers', 'Number of messengers.'),
(1, 't', 1584, 'Primary managers', 'Number of primary managers.'),
(1, 't', 1585, 'Secretaries', 'Number of secretaries.'),
(1, 't', 1586, 'Detrimental legal filings', 'Number of detrimental legal filings.'),
(1, 't', 1587, 'Branch office locations, estimated', 'Estimated number of branch office locations.'),
(1, 't', 1588, 'Previous number of employees', 'The number of employees for a previous period.'),
(1, 't', 1589, 'Asset seizers', 'Number of entities that seize assets of another entity.'),
(1, 't', 1590, 'Out-turned quantity', 'The quantity discharged.'),
(1, 't', 1591, 'Material on-board quantity, prior to loading', 'The material in vessel tanks, void spaces, and pipelines prior to loading.'),
(1, 't', 1592, 'Supplier estimated previous meter reading', 'Previous meter reading estimated by the supplier.'),
(1, 't', 1593, 'Supplier estimated latest meter reading',   'Latest meter reading estimated by the supplier.'),
(1, 't', 1594, 'Customer estimated previous meter reading', 'Previous meter reading estimated by the customer.'),
(1, 't', 1595, 'Customer estimated latest meter reading',   'Latest meter reading estimated by the customer.'),
(1, 't', 1596, 'Supplier previous meter reading',           'Previous meter reading done by the supplier.'),
(1, 't', 1597, 'Supplier latest meter reading',             'Latest meter reading recorded by the supplier.'),
(1, 't', 1598, 'Maximum number of purchase orders allowed', 'Maximum number of purchase orders that are allowed.'),
(1, 't', 1599, 'File size before compression', 'The size of a file before compression.'),
(1, 't', 1600, 'File size after compression', 'The size of a file after compression.'),
(1, 't', 1601, 'Securities shares', 'Number of shares of securities.'),
(1, 't', 1602, 'Patients',         'Number of patients.'),
(1, 't', 1603, 'Completed projects', 'Number of completed projects.'),
(1, 't', 1604, 'Promoters',        'Number of entities who finance or organize an event or a production.'),
(1, 't', 1605, 'Administrators',   'Number of administrators.'),
(1, 't', 1606, 'Supervisors',      'Number of supervisors.'),
(1, 't', 1607, 'Professionals',    'Number of professionals.'),
(1, 't', 1608, 'Debt collectors',  'Number of debt collectors.'),
(1, 't', 1609, 'Inspectors',       'Number of individuals who perform inspections.'),
(1, 't', 1610, 'Operators',        'Number of operators.'),
(1, 't', 1611, 'Trainers',         'Number of trainers.'),
(1, 't', 1612, 'Active accounts',  'Number of accounts in a current or active status.'),
(1, 't', 1613, 'Trademarks used',  'Number of trademarks used.'),
(1, 't', 1614, 'Machines',         'Number of machines.'),
(1, 't', 1615, 'Fuel pumps',       'Number of fuel pumps.'),
(1, 't', 1616, 'Tables available', 'Number of tables available for use.'),
(1, 't', 1617, 'Directors',        'Number of directors.'),
(1, 't', 1618, 'Freelance debt collectors', 'Number of debt collectors who work on a freelance basis.'),
(1, 't', 1619, 'Freelance salespersons',    'Number of salespersons who work on a freelance basis.'),
(1, 't', 1620, 'Travelling employees',      'Number of travelling employees.'),
(1, 't', 1621, 'Foremen', 'Number of workers with limited supervisory responsibilities.'),
(1, 't', 1622, 'Production workers', 'Number of employees engaged in production.'),
(1, 't', 1623, 'Employees not including owners', 'Number of employees excluding business owners.'),
(1, 't', 1624, 'Beds', 'Number of beds.'),
(1, 't', 1625, 'Resting quantity', 'A quantity of product that is at rest before it can be used.'),
(1, 't', 1626, 'Production requirements', 'Quantity needed to meet production requirements.'),
(1, 't', 1627, 'Corrected quantity', 'The quantity has been corrected.'),
(1, 't', 1628, 'Operating divisions', 'Number of divisions operating.'),
(1, 't', 1629, 'Quantitative incentive scheme base', 'Quantity constituting the base for the quantitative incentive scheme.'),
(1, 't', 1630, 'Petitions filed', 'Number of petitions that have been filed.'),
(1, 't', 1631, 'Bankruptcy petitions filed', 'Number of bankruptcy petitions that have been filed.'),
(1, 't', 1632, 'Projects in process', 'Number of projects in process.'),
(1, 't', 1633, 'Changes in capital structure', 'Number of modifications made to the capital structure of an entity.'),
(1, 't', 1634, 'Detrimental legal filings against directors', 'The number of legal filings that are of a detrimental nature that have been filed against the directors.'),
(1, 't', 1635, 'Number of failed businesses of directors', 'The number of failed businesses with which the directors have been associated.'),
(1, 't', 1636, 'Professor', 'The number of professors.'),
(1, 't', 1637, 'Seller',    'The number of sellers.'),
(1, 't', 1638, 'Skilled worker', 'The number of skilled workers.'),
(1, 't', 1639, 'Trademark represented', 'The number of trademarks represented.'),
(1, 't', 1640, 'Number of quantitative incentive scheme units', 'Number of units allocated to a quantitative incentive scheme.'),
(1, 't', 1641, 'Quantity in manufacturing process', 'Quantity currently in the manufacturing process.'),
(1, 't', 1642, 'Number of units in the width of a layer', 'Number of units which make up the width of a layer.'),
(1, 't', 1643, 'Number of units in the depth of a layer', 'Number of units which make up the depth of a layer.'),
(1, 't', 1644, 'Return to warehouse', 'A quantity of products sent back to the warehouse.'),
(1, 't', 1645, 'Return to the manufacturer', 'A quantity of products sent back from the manufacturer.'),
(1, 't', 1646, 'Delta quantity', 'An increment or decrement to a quantity.'),
(1, 't', 1647, 'Quantity moved between outlets', 'A quantity of products moved between outlets.'),
(1, 't', 1648, 'Pre-paid invoice annual consumption, estimated', 'The estimated annual consumption used for a prepayment invoice.'),
(1, 't', 1649, 'Total quoted quantity', 'The sum of quoted quantities.'),
(1, 't', 1650, 'Requests pertaining to entity in last 12 months', 'Number of requests received in last 12 months pertaining to the entity.'),
(1, 't', 1651, 'Total inquiry matches', 'Number of instances which correspond with the inquiry.'),
(1, 't', 1652, 'En route to warehouse quantity',   'A quantity of products that is en route to a warehouse.'),
(1, 't', 1653, 'En route from warehouse quantity', 'A quantity of products that is en route from a warehouse.'),
(1, 't', 1654, 'Quantity ordered but not yet allocated from stock', 'A quantity of products which has been ordered but which has not yet been allocated from stock.'),
(1, 't', 1655, 'Not yet ordered quantity', 'The quantity which has not yet been ordered.'),
(1, 't', 1656, 'Net reserve power', 'The reserve power available for the net.'),
(1, 't', 1657, 'Maximum number of units per shelf', 'Maximum number of units of a product that can be placed on a shelf.'),
(1, 't', 1658, 'Stowaway', 'Number of stowaway(s) on a conveyance.'),
(1, 't', 1659, 'Tug', 'The number of tugboat(s).'),
(1, 't', 1660, 'Maximum quantity capability of the package', 'Maximum quantity of a product that can be contained in a package.'),
(1, 't', 1661, 'Calculated', 'The calculated quantity.'),
(1, 't', 1662, 'Monthly volume, estimated', 'Volume estimated for a month.'),
(1, 't', 1663, 'Total number of persons', 'Quantity representing the total number of persons.'),
(1, 't', 1664, 'Tariff Quantity', 'Quantity of the goods in the unit as required by Customs for duty/tax/fee assessment. These quantities may also be used for other fiscal or statistical purposes.'),
(1, 't', 1665, 'Deducted tariff quantity',   'Quantity deducted from tariff quantity to reckon duty/tax/fee assessment bases.'),
(1, 't', 1666, 'Advised but not arrived',    'Goods are advised by the consignor or supplier, but have not yet arrived at the destination.'),
(1, 't', 1667, 'Received but not available', 'Goods have been received in the arrival area but are not yet available.'),
(1, 't', 1668, 'Goods blocked for transshipment process', 'Goods are physically present, but can not be ordered because they are scheduled for a transshipment process.'),
(1, 't', 1669, 'Goods blocked for cross docking process', 'Goods are physically present, but can not be ordered because they are scheduled for a cross docking process.'),
(1, 't', 1670, 'Chargeable number of trailers', 'The number of trailers on which charges are based.'),
(1, 't', 1671, 'Number of packages for a set', 'Number of packages used to pack the individual items in a grouping of merchandise that is sold together as a single trade item.'),
(1, 't', 1672, 'Number of items in a set', 'The number of individual items in a grouping of merchandise that is sold together as a single trade item.'),
(1, 't', 1673, 'Order sizing factor', 'A trade item specification other than gross, net weight, or volume for a trade item or a transaction, used for order sizing and pricing purposes.'),
(1, 't', 1674, 'Number of different next lower level trade items', 'Value indicates the number of differrent next lower level trade items contained in a complex trade item.'),
(1, 't', 1675, 'Agreed maximum buying quantity', 'The agreed maximum quantity of the trade item that may be purchased.'),
(1, 't', 1676, 'Agreed minimum buying quantity', 'The agreed minimum quantity of the trade item that may be purchased.'),
(1, 't', 1677, 'Free quantity of next lower level trade item', 'The numeric quantity of free items in a combination pack. The unit of measure used for the free quantity of the next lower level must be the same as the unit of measure of the Net Content of the Child Trade Item.'),
(1, 't', 1678, 'Marine Diesel Oil bunkers on board, on arrival',     'Number of Marine Diesel Oil (MDO) bunkers on board when the vessel arrives in the port.'),
(1, 't', 1679, 'Marine Diesel Oil bunkers, loaded',                  'Number of Marine Diesel Oil (MDO) bunkers taken on in the port.'),
(1, 't', 1680, 'Intermediate Fuel Oil bunkers on board, on arrival', 'Number of Intermediate Fuel Oil (IFO) bunkers on board when the vessel arrives in the port.'),
(1, 't', 1681, 'Intermediate Fuel Oil bunkers, loaded',              'Number of Intermediate Fuel Oil (IFO) bunkers taken on in the port.'),
(1, 't', 1682, 'Bunker C bunkers on board, on arrival',              'Number of Bunker C, or Number 6 fuel oil bunkers on board when the vessel arrives in the port.'),
(1, 't', 1683, 'Bunker C bunkers, loaded', 'Number of Bunker C, or Number 6 fuel oil bunkers, taken on in the port.'),
(1, 't', 1684, 'Number of individual units within the smallest packaging', 'unit Total number of individual units contained within the smallest unit of packaging.'),
(1, 't', 1685, 'Percentage of constituent element', 'The part of a product or material that is composed of the constituent element, as a percentage.'),
(1, 't', 1686, 'Quantity to be decremented (LPCO)', 'Quantity to be decremented from the allowable quantity on a License, Permit, Certificate, or Other document (LPCO).'),
(1, 't', 1687, 'Regulated commodity count', 'The number of regulated items.'),
(1, 't', 1688, 'Number of passengers, embarking', 'The number of passengers going aboard a conveyance.'),
(1, 't', 1689, 'Number of passengers, disembarking', 'The number of passengers disembarking the conveyance.'),
(1, 't', 1690, 'Constituent element or component quantity', 'The specific quantity of the identified constituent element.')
;
-- ZZZ, 'Mutually defined', 'As agreed by the trading partners.'),

CREATE TABLE acq.serial_claim (
    id     SERIAL           PRIMARY KEY,
    type   INT              NOT NULL REFERENCES acq.claim_type
                                     DEFERRABLE INITIALLY DEFERRED,
    item    BIGINT          NOT NULL REFERENCES serial.item
                                     DEFERRABLE INITIALLY DEFERRED
);

CREATE INDEX serial_claim_lid_idx ON acq.serial_claim( item );

CREATE TABLE acq.serial_claim_event (
    id             BIGSERIAL        PRIMARY KEY,
    type           INT              NOT NULL REFERENCES acq.claim_event_type
                                             DEFERRABLE INITIALLY DEFERRED,
    claim          SERIAL           NOT NULL REFERENCES acq.serial_claim
                                             DEFERRABLE INITIALLY DEFERRED,
    event_date     TIMESTAMPTZ      NOT NULL DEFAULT now(),
    creator        INT              NOT NULL REFERENCES actor.usr
                                             DEFERRABLE INITIALLY DEFERRED,
    note           TEXT
);

CREATE INDEX serial_claim_event_claim_date_idx ON acq.serial_claim_event( claim, event_date );

ALTER TABLE asset.stat_cat ADD COLUMN required BOOL NOT NULL DEFAULT FALSE;

-- now what about the auditor.*_lifecycle views??

INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath ) VALUES
    (26, 'identifier', 'tcn', oils_i18n_gettext(26, 'Title Control Number', 'cmf', 'label'), 'marcxml', $$//marc:datafield[@tag='901']/marc:subfield[@code='a']$$ );
INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath ) VALUES
    (27, 'identifier', 'bibid', oils_i18n_gettext(27, 'Internal ID', 'cmf', 'label'), 'marcxml', $$//marc:datafield[@tag='901']/marc:subfield[@code='c']$$ );
INSERT INTO config.metabib_search_alias (alias,field_class,field) VALUES ('eg.tcn','identifier', 26);
INSERT INTO config.metabib_search_alias (alias,field_class,field) VALUES ('eg.bibid','identifier', 27);

CREATE TABLE asset.call_number_class (
    id             bigserial     PRIMARY KEY,
    name           TEXT          NOT NULL,
    normalizer     TEXT          NOT NULL DEFAULT 'asset.normalize_generic',
    field          TEXT          NOT NULL DEFAULT '050ab,055ab,060ab,070ab,080ab,082ab,086ab,088ab,090,092,096,098,099'
);

COMMENT ON TABLE asset.call_number_class IS $$
Defines the call number normalization database functions in the "normalizer"
column and the tag/subfield combinations to use to lookup the call number in
the "field" column for a given classification scheme. Tag/subfield combinations
are delimited by commas.
$$;

INSERT INTO asset.call_number_class (name, normalizer) VALUES 
    ('Generic', 'asset.label_normalizer_generic'),
    ('Dewey (DDC)', 'asset.label_normalizer_dewey'),
    ('Library of Congress (LC)', 'asset.label_normalizer_lc')
;

-- Generic fields
UPDATE asset.call_number_class
    SET field = '050ab,055ab,060ab,070ab,080ab,082ab,086ab,088ab,090,092,096,098,099'
    WHERE id = 1
;

-- Dewey fields
UPDATE asset.call_number_class
    SET field = '080ab,082ab'
    WHERE id = 2
;

-- LC fields
UPDATE asset.call_number_class
    SET field = '050ab,055ab'
    WHERE id = 3
;
 
ALTER TABLE asset.call_number
	ADD COLUMN label_class BIGINT DEFAULT 1 NOT NULL
		REFERENCES asset.call_number_class(id)
		DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE asset.call_number
	ADD COLUMN label_sortkey TEXT;

CREATE INDEX asset_call_number_label_sortkey
	ON asset.call_number(oils_text_as_bytea(label_sortkey));

ALTER TABLE auditor.asset_call_number_history
	ADD COLUMN label_class BIGINT;

ALTER TABLE auditor.asset_call_number_history
	ADD COLUMN label_sortkey TEXT;

-- Pick up the new columns in dependent views

DROP VIEW auditor.asset_call_number_lifecycle;

SELECT auditor.create_auditor_lifecycle( 'asset', 'call_number' );

DROP VIEW auditor.asset_call_number_lifecycle;

SELECT auditor.create_auditor_lifecycle( 'asset', 'call_number' );

DROP VIEW IF EXISTS stats.fleshed_call_number;

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

CREATE OR REPLACE FUNCTION asset.label_normalizer() RETURNS TRIGGER AS $func$
DECLARE
    sortkey        TEXT := '';
BEGIN
    sortkey := NEW.label_sortkey;

    EXECUTE 'SELECT ' || acnc.normalizer || '(' || 
       quote_literal( NEW.label ) || ')'
       FROM asset.call_number_class acnc
       WHERE acnc.id = NEW.label_class
       INTO sortkey;

    NEW.label_sortkey = sortkey;

    RETURN NEW;
END;
$func$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION asset.label_normalizer_generic(TEXT) RETURNS TEXT AS $func$
    # Created after looking at the Koha C4::ClassSortRoutine::Generic module,
    # thus could probably be considered a derived work, although nothing was
    # directly copied - but to err on the safe side of providing attribution:
    # Copyright (C) 2007 LibLime
    # Licensed under the GPL v2 or later

    use strict;
    use warnings;

    # Converts the callnumber to uppercase
    # Strips spaces from start and end of the call number
    # Converts anything other than letters, digits, and periods into underscores
    # Collapses multiple underscores into a single underscore
    my $callnum = uc(shift);
    $callnum =~ s/^\s//g;
    $callnum =~ s/\s$//g;
    $callnum =~ s/[^A-Z0-9_.]/_/g;
    $callnum =~ s/_{2,}/_/g;

    return $callnum;
$func$ LANGUAGE PLPERLU;

CREATE OR REPLACE FUNCTION asset.label_normalizer_dewey(TEXT) RETURNS TEXT AS $func$
    # Derived from the Koha C4::ClassSortRoutine::Dewey module
    # Copyright (C) 2007 LibLime
    # Licensed under the GPL v2 or later

    use strict;
    use warnings;

    my $init = uc(shift);
    $init =~ s/^\s+//;
    $init =~ s/\s+$//;
    $init =~ s!/!!g;
    $init =~ s/^([\p{IsAlpha}]+)/$1 /;
    my @tokens = split /\.|\s+/, $init;
    my $digit_group_count = 0;
    for (my $i = 0; $i <= $#tokens; $i++) {
        if ($tokens[$i] =~ /^\d+$/) {
            $digit_group_count++;
            if (2 == $digit_group_count) {
                $tokens[$i] = sprintf("%-15.15s", $tokens[$i]);
                $tokens[$i] =~ tr/ /0/;
            }
        }
    }
    my $key = join("_", @tokens);
    $key =~ s/[^\p{IsAlnum}_]//g;

    return $key;

$func$ LANGUAGE PLPERLU;

CREATE OR REPLACE FUNCTION asset.label_normalizer_lc(TEXT) RETURNS TEXT AS $func$
    use strict;
    use warnings;

    # Library::CallNumber::LC is currently hosted at http://code.google.com/p/library-callnumber-lc/
    # The author hopes to upload it to CPAN some day, which would make our lives easier
    use Library::CallNumber::LC;

    my $callnum = Library::CallNumber::LC->new(shift);
    return $callnum->normalize();

$func$ LANGUAGE PLPERLU;

CREATE OR REPLACE FUNCTION asset.opac_ou_record_copy_count (org INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
DECLARE
    ans RECORD;
    trans INT;
BEGIN
    SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = record;

    FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP
        RETURN QUERY
        SELECT  ans.depth,
                ans.id,
                COUNT( av.id ),
                SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
                COUNT( av.id ),
                trans
          FROM
                actor.org_unit_descendants(ans.id) d
                JOIN asset.opac_visible_copies av ON (av.record = record AND av.circ_lib = d.id)
                JOIN asset.copy cp ON (cp.id = av.id)
          GROUP BY 1,2,6;

        IF NOT FOUND THEN
            RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
        END IF;

    END LOOP;

    RETURN;
END;
$f$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION asset.opac_lasso_record_copy_count (i_lasso INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
DECLARE
    ans RECORD;
    trans INT;
BEGIN
    SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = record;

    FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
        RETURN QUERY
        SELECT  -1,
                ans.id,
                COUNT( av.id ),
                SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
                COUNT( av.id ),
                trans
          FROM
                actor.org_unit_descendants(ans.id) d
                JOIN asset.opac_visible_copies av ON (av.record = record AND av.circ_lib = d.id)
                JOIN asset.copy cp ON (cp.id = av.id)
          GROUP BY 1,2,6;

        IF NOT FOUND THEN
            RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
        END IF;

    END LOOP;

    RETURN;
END;
$f$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION asset.staff_ou_record_copy_count (org INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
DECLARE
    ans RECORD;
    trans INT;
BEGIN
    SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = record;

    FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP
        RETURN QUERY
        SELECT  ans.depth,
                ans.id,
                COUNT( cp.id ),
                SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
                COUNT( cp.id ),
                trans
          FROM
                actor.org_unit_descendants(ans.id) d
                JOIN asset.copy cp ON (cp.circ_lib = d.id)
                JOIN asset.call_number cn ON (cn.record = record AND cn.id = cp.call_number)
          GROUP BY 1,2,6;

        IF NOT FOUND THEN
            RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
        END IF;

    END LOOP;

    RETURN;
END;
$f$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION asset.staff_lasso_record_copy_count (i_lasso INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
DECLARE
    ans RECORD;
    trans INT;
BEGIN
    SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = record;

    FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
        RETURN QUERY
        SELECT  -1,
                ans.id,
                COUNT( cp.id ),
                SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
                COUNT( cp.id ),
                trans
          FROM
                actor.org_unit_descendants(ans.id) d
                JOIN asset.copy cp ON (cp.circ_lib = d.id)
                JOIN asset.call_number cn ON (cn.record = record AND cn.id = cp.call_number)
          GROUP BY 1,2,6;

        IF NOT FOUND THEN
            RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
        END IF;

    END LOOP;

    RETURN;
END;
$f$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION asset.record_copy_count ( place INT, record BIGINT, staff BOOL) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
BEGIN
    IF staff IS TRUE THEN
        IF place > 0 THEN
            RETURN QUERY SELECT * FROM asset.staff_ou_record_copy_count( place, record );
        ELSE
            RETURN QUERY SELECT * FROM asset.staff_lasso_record_copy_count( -place, record );
        END IF;
    ELSE
        IF place > 0 THEN
            RETURN QUERY SELECT * FROM asset.opac_ou_record_copy_count( place, record );
        ELSE
            RETURN QUERY SELECT * FROM asset.opac_lasso_record_copy_count( -place, record );
        END IF;
    END IF;

    RETURN;
END;
$f$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION asset.opac_ou_metarecord_copy_count (org INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
DECLARE
    ans RECORD;
    trans INT;
BEGIN
    SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = record;

    FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP
        RETURN QUERY
        SELECT  ans.depth,
                ans.id,
                COUNT( av.id ),
                SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
                COUNT( av.id ),
                trans
          FROM
                actor.org_unit_descendants(ans.id) d
                JOIN asset.opac_visible_copies av ON (av.record = record AND av.circ_lib = d.id)
                JOIN asset.copy cp ON (cp.id = av.id)
                JOIN metabib.metarecord_source_map m ON (m.source = av.record)
          GROUP BY 1,2,6;

        IF NOT FOUND THEN
            RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
        END IF;

    END LOOP;

    RETURN;
END;
$f$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION asset.opac_lasso_metarecord_copy_count (i_lasso INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
DECLARE
    ans RECORD;
    trans INT;
BEGIN
    SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = record;

    FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
        RETURN QUERY
        SELECT  -1,
                ans.id,
                COUNT( av.id ),
                SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
                COUNT( av.id ),
                trans
          FROM
                actor.org_unit_descendants(ans.id) d
                JOIN asset.opac_visible_copies av ON (av.record = record AND av.circ_lib = d.id)
                JOIN asset.copy cp ON (cp.id = av.id)
                JOIN metabib.metarecord_source_map m ON (m.source = av.record)
          GROUP BY 1,2,6;

        IF NOT FOUND THEN
            RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
        END IF;

    END LOOP;

    RETURN;
END;
$f$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION asset.staff_ou_metarecord_copy_count (org INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
DECLARE
    ans RECORD;
    trans INT;
BEGIN
    SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = record;

    FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP
        RETURN QUERY
        SELECT  ans.depth,
                ans.id,
                COUNT( cp.id ),
                SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
                COUNT( cp.id ),
                trans
          FROM
                actor.org_unit_descendants(ans.id) d
                JOIN asset.copy cp ON (cp.circ_lib = d.id)
                JOIN asset.call_number cn ON (cn.record = record AND cn.id = cp.call_number)
                JOIN metabib.metarecord_source_map m ON (m.source = cn.record)
          GROUP BY 1,2,6;

        IF NOT FOUND THEN
            RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
        END IF;

    END LOOP;

    RETURN;
END;
$f$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION asset.staff_lasso_metarecord_copy_count (i_lasso INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
DECLARE
    ans RECORD;
    trans INT;
BEGIN
    SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = record;

    FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
        RETURN QUERY
        SELECT  -1,
                ans.id,
                COUNT( cp.id ),
                SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
                COUNT( cp.id ),
                trans
          FROM
                actor.org_unit_descendants(ans.id) d
                JOIN asset.copy cp ON (cp.circ_lib = d.id)
                JOIN asset.call_number cn ON (cn.record = record AND cn.id = cp.call_number)
                JOIN metabib.metarecord_source_map m ON (m.source = cn.record)
          GROUP BY 1,2,6;

        IF NOT FOUND THEN
            RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
        END IF;

    END LOOP;

    RETURN;
END;
$f$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION asset.metarecord_copy_count ( place INT, record BIGINT, staff BOOL) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
BEGIN
    IF staff IS TRUE THEN
        IF place > 0 THEN
            RETURN QUERY SELECT * FROM asset.staff_ou_metarecord_copy_count( place, record );
        ELSE
            RETURN QUERY SELECT * FROM asset.staff_lasso_metarecord_copy_count( -place, record );
        END IF;
    ELSE
        IF place > 0 THEN
            RETURN QUERY SELECT * FROM asset.opac_ou_metarecord_copy_count( place, record );
        ELSE
            RETURN QUERY SELECT * FROM asset.opac_lasso_metarecord_copy_count( -place, record );
        END IF;
    END IF;

    RETURN;
END;
$f$ LANGUAGE PLPGSQL;

-- No transaction is required

-- Triggers on the vandelay.queued_*_record tables delete entries from
-- the associated vandelay.queued_*_record_attr tables based on the record's
-- ID; create an index on that column to avoid sequential scans for each
-- queued record that is deleted
CREATE INDEX queued_bib_record_attr_record_idx ON vandelay.queued_bib_record_attr (record);
CREATE INDEX queued_authority_record_attr_record_idx ON vandelay.queued_authority_record_attr (record);

-- Avoid sequential scans for queue retrieval operations by providing an
-- index on the queue column
CREATE INDEX queued_bib_record_queue_idx ON vandelay.queued_bib_record (queue);
CREATE INDEX queued_authority_record_queue_idx ON vandelay.queued_authority_record (queue);

-- Start picking up call number label prefixes and suffixes
-- from asset.copy_location
ALTER TABLE asset.copy_location ADD COLUMN label_prefix TEXT;
ALTER TABLE asset.copy_location ADD COLUMN label_suffix TEXT;

DROP VIEW auditor.asset_copy_lifecycle;

SELECT auditor.create_auditor_lifecycle( 'asset', 'copy' );

ALTER TABLE reporter.report RENAME COLUMN recurance TO recurrence;

-- Let's not break existing reports
UPDATE reporter.template SET data = REGEXP_REPLACE(data, E'^(.*)recuring(.*)$', E'\\1recurring\\2') WHERE data LIKE '%recuring%';
UPDATE reporter.template SET data = REGEXP_REPLACE(data, E'^(.*)recurance(.*)$', E'\\1recurrence\\2') WHERE data LIKE '%recurance%';

-- Need to recreate this view with DISTINCT calls to ARRAY_ACCUM, thus avoiding duplicated ISBN and ISSN values
CREATE OR REPLACE VIEW reporter.old_super_simple_record AS
SELECT  r.id,
    r.fingerprint,
    r.quality,
    r.tcn_source,
    r.tcn_value,
    FIRST(title.value) AS title,
    FIRST(author.value) AS author,
    ARRAY_TO_STRING(ARRAY_ACCUM( DISTINCT publisher.value), ', ') AS publisher,
    ARRAY_TO_STRING(ARRAY_ACCUM( DISTINCT SUBSTRING(pubdate.value FROM $$\d+$$) ), ', ') AS pubdate,
    ARRAY_ACCUM( DISTINCT SUBSTRING(isbn.value FROM $$^\S+$$) ) AS isbn,
    ARRAY_ACCUM( DISTINCT SUBSTRING(issn.value FROM $$^\S+$$) ) AS issn
  FROM  biblio.record_entry r
    LEFT JOIN metabib.full_rec title ON (r.id = title.record AND title.tag = '245' AND title.subfield = 'a')
    LEFT JOIN metabib.full_rec author ON (r.id = author.record AND author.tag IN ('100','110','111') AND author.subfield = 'a')
    LEFT JOIN metabib.full_rec publisher ON (r.id = publisher.record AND publisher.tag = '260' AND publisher.subfield = 'b')
    LEFT JOIN metabib.full_rec pubdate ON (r.id = pubdate.record AND pubdate.tag = '260' AND pubdate.subfield = 'c')
    LEFT JOIN metabib.full_rec isbn ON (r.id = isbn.record AND isbn.tag IN ('024', '020') AND isbn.subfield IN ('a','z'))
    LEFT JOIN metabib.full_rec issn ON (r.id = issn.record AND issn.tag = '022' AND issn.subfield = 'a')
  GROUP BY 1,2,3,4,5;

-- Correct the ISSN array definition for reporter.simple_record

CREATE OR REPLACE VIEW reporter.simple_record AS
SELECT	r.id,
	s.metarecord,
	r.fingerprint,
	r.quality,
	r.tcn_source,
	r.tcn_value,
	title.value AS title,
	uniform_title.value AS uniform_title,
	author.value AS author,
	publisher.value AS publisher,
	SUBSTRING(pubdate.value FROM $$\d+$$) AS pubdate,
	series_title.value AS series_title,
	series_statement.value AS series_statement,
	summary.value AS summary,
	ARRAY_ACCUM( SUBSTRING(isbn.value FROM $$^\S+$$) ) AS isbn,
	ARRAY_ACCUM( REGEXP_REPLACE(issn.value, E'^\\S*(\\d{4})[-\\s](\\d{3,4}x?)', E'\\1 \\2') ) AS issn,
	ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '650' AND subfield = 'a' AND record = r.id)) AS topic_subject,
	ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '651' AND subfield = 'a' AND record = r.id)) AS geographic_subject,
	ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '655' AND subfield = 'a' AND record = r.id)) AS genre,
	ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '600' AND subfield = 'a' AND record = r.id)) AS name_subject,
	ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '610' AND subfield = 'a' AND record = r.id)) AS corporate_subject,
	ARRAY((SELECT value FROM metabib.full_rec WHERE tag = '856' AND subfield IN ('3','y','u') AND record = r.id ORDER BY CASE WHEN subfield IN ('3','y') THEN 0 ELSE 1 END)) AS external_uri
  FROM	biblio.record_entry r
	JOIN metabib.metarecord_source_map s ON (s.source = r.id)
	LEFT JOIN metabib.full_rec uniform_title ON (r.id = uniform_title.record AND uniform_title.tag = '240' AND uniform_title.subfield = 'a')
	LEFT JOIN metabib.full_rec title ON (r.id = title.record AND title.tag = '245' AND title.subfield = 'a')
	LEFT JOIN metabib.full_rec author ON (r.id = author.record AND author.tag = '100' AND author.subfield = 'a')
	LEFT JOIN metabib.full_rec publisher ON (r.id = publisher.record AND publisher.tag = '260' AND publisher.subfield = 'b')
	LEFT JOIN metabib.full_rec pubdate ON (r.id = pubdate.record AND pubdate.tag = '260' AND pubdate.subfield = 'c')
	LEFT JOIN metabib.full_rec isbn ON (r.id = isbn.record AND isbn.tag IN ('024', '020') AND isbn.subfield IN ('a','z'))
	LEFT JOIN metabib.full_rec issn ON (r.id = issn.record AND issn.tag = '022' AND issn.subfield = 'a')
	LEFT JOIN metabib.full_rec series_title ON (r.id = series_title.record AND series_title.tag IN ('830','440') AND series_title.subfield = 'a')
	LEFT JOIN metabib.full_rec series_statement ON (r.id = series_statement.record AND series_statement.tag = '490' AND series_statement.subfield = 'a')
	LEFT JOIN metabib.full_rec summary ON (r.id = summary.record AND summary.tag = '520' AND summary.subfield = 'a')
  GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14;

CREATE OR REPLACE FUNCTION reporter.disable_materialized_simple_record_trigger () RETURNS VOID AS $$
    DROP TRIGGER IF EXISTS zzz_update_materialized_simple_record_tgr ON metabib.real_full_rec;
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION reporter.simple_rec_trigger () RETURNS TRIGGER AS $func$
BEGIN
    IF TG_OP = 'DELETE' THEN
        PERFORM reporter.simple_rec_delete(NEW.id);
    ELSE
        PERFORM reporter.simple_rec_update(NEW.id);
    END IF;

    RETURN NEW;
END;
$func$ LANGUAGE PLPGSQL;

CREATE TRIGGER bbb_simple_rec_trigger AFTER INSERT OR UPDATE ON biblio.record_entry FOR EACH ROW EXECUTE PROCEDURE reporter.simple_rec_trigger ();

ALTER TABLE extend_reporter.legacy_circ_count DROP CONSTRAINT legacy_circ_count_id_fkey;

CREATE INDEX asset_copy_note_owning_copy_idx ON asset.copy_note ( owning_copy );

UPDATE config.org_unit_setting_type
    SET view_perm = (SELECT id FROM permission.perm_list
        WHERE code = 'VIEW_CREDIT_CARD_PROCESSING' LIMIT 1)
    WHERE name LIKE 'credit.processor%' AND view_perm IS NULL;

UPDATE config.org_unit_setting_type
    SET update_perm = (SELECT id FROM permission.perm_list
        WHERE code = 'ADMIN_CREDIT_CARD_PROCESSING' LIMIT 1)
    WHERE name LIKE 'credit.processor%' AND update_perm IS NULL;

INSERT INTO config.org_unit_setting_type (name, label, description, datatype)
    VALUES (
        'opac.fully_compressed_serial_holdings',
        'OPAC: Use fully compressed serial holdings',
        'Show fully compressed serial holdings for all libraries at and below
        the current context unit',
        'bool'
    );

CREATE OR REPLACE FUNCTION authority.normalize_heading( TEXT ) RETURNS TEXT AS $func$
    use strict;
    use warnings;

    use utf8;
    use MARC::Record;
    use MARC::File::XML (BinaryEncoding => 'UTF8');
    use UUID::Tiny ':std';

    my $xml = shift() or return undef;

    my $r;

    # Prevent errors in XML parsing from blowing out ungracefully
    eval {
        $r = MARC::Record->new_from_xml( $xml );
        1;
    } or do {
       return 'BAD_MARCXML_' . create_uuid_as_string(UUID_MD5, $xml);
    };

    if (!$r) {
       return 'BAD_MARCXML_' . create_uuid_as_string(UUID_MD5, $xml);
    }

    # From http://www.loc.gov/standards/sourcelist/subject.html
    my $thes_code_map = {
        a => 'lcsh',
        b => 'lcshac',
        c => 'mesh',
        d => 'nal',
        k => 'cash',
        n => 'notapplicable',
        r => 'aat',
        s => 'sears',
        v => 'rvm',
    };

    # Default to "No attempt to code" if the leader is horribly broken
    my $fixed_field = $r->field('008');
    my $thes_char = '|';
    if ($fixed_field) {
        $thes_char = substr($fixed_field->data(), 11, 1) || '|';
    }

    my $thes_code = 'UNDEFINED';

    if ($thes_char eq 'z') {
        # Grab the 040 $f per http://www.loc.gov/marc/authority/ad040.html
        $thes_code = $r->subfield('040', 'f') || 'UNDEFINED';
    } elsif ($thes_code_map->{$thes_char}) {
        $thes_code = $thes_code_map->{$thes_char};
    }

    my $auth_txt = '';
    my $head = $r->field('1..');
    if ($head) {
        # Concatenate all of these subfields together, prefixed by their code
        # to prevent collisions along the lines of "Fiction, North Carolina"
        foreach my $sf ($head->subfields()) {
            $auth_txt .= '‡' . $sf->[0] . ' ' . $sf->[1];
        }
    }

    # Perhaps better to parameterize the spi and pass as a parameter
    $auth_txt =~ s/'//go;

    if ($auth_txt) {
        my $result = spi_exec_query("SELECT public.naco_normalize('$auth_txt') AS norm_text");
        my $norm_txt = $result->{rows}[0]->{norm_text};
        return $head->tag() . "_" . $thes_code . " " . $norm_txt;
    }

    return 'NOHEADING_' . $thes_code . ' ' . create_uuid_as_string(UUID_MD5, $xml);
$func$ LANGUAGE 'plperlu' IMMUTABLE;

COMMENT ON FUNCTION authority.normalize_heading( TEXT ) IS $$
/**
* Extract the authority heading, thesaurus, and NACO-normalized values
* from an authority record. The primary purpose is to build a unique
* index to defend against duplicated authority records from the same
* thesaurus.
*/
$$;

DROP INDEX authority.authority_record_unique_tcn;
ALTER TABLE authority.record_entry DROP COLUMN arn_value;
ALTER TABLE authority.record_entry DROP COLUMN arn_source;

ALTER TABLE acq.provider_contact
	ALTER COLUMN name SET NOT NULL;

ALTER TABLE actor.stat_cat
	ADD COLUMN usr_summary BOOL NOT NULL DEFAULT FALSE;

-- Recreate some foreign keys that were somehow dropped, probably
-- by some kind of cascade from an inherited table:

ALTER TABLE action.reservation_transit_copy
	ADD CONSTRAINT artc_tc_fkey FOREIGN KEY (target_copy)
		REFERENCES booking.resource(id)
		ON DELETE CASCADE
		DEFERRABLE INITIALLY DEFERRED,
	ADD CONSTRAINT reservation_transit_copy_reservation_fkey FOREIGN KEY (reservation)
		REFERENCES booking.reservation(id)
		ON DELETE SET NULL
		DEFERRABLE INITIALLY DEFERRED;

CREATE INDEX user_bucket_item_target_user_idx
	ON container.user_bucket_item ( target_user );

CREATE INDEX m_c_t_collector_idx
	ON money.collections_tracker ( collector );

CREATE INDEX aud_actor_usr_address_hist_id_idx
	ON auditor.actor_usr_address_history ( id );

CREATE INDEX aud_actor_usr_hist_id_idx
	ON auditor.actor_usr_history ( id );

CREATE INDEX aud_asset_cn_hist_creator_idx
	ON auditor.asset_call_number_history ( creator );

CREATE INDEX aud_asset_cn_hist_editor_idx
	ON auditor.asset_call_number_history ( editor );

CREATE INDEX aud_asset_cp_hist_creator_idx
	ON auditor.asset_copy_history ( creator );

CREATE INDEX aud_asset_cp_hist_editor_idx
	ON auditor.asset_copy_history ( editor );

CREATE INDEX aud_bib_rec_entry_hist_creator_idx
	ON auditor.biblio_record_entry_history ( creator );

CREATE INDEX aud_bib_rec_entry_hist_editor_idx
	ON auditor.biblio_record_entry_history ( editor );

CREATE TABLE action.hold_request_note (

    id     BIGSERIAL PRIMARY KEY,
    hold   BIGINT    NOT NULL REFERENCES action.hold_request (id)
                              ON DELETE CASCADE
                              DEFERRABLE INITIALLY DEFERRED,
    title  TEXT      NOT NULL,
    body   TEXT      NOT NULL,
    slip   BOOL      NOT NULL DEFAULT FALSE,
    pub    BOOL      NOT NULL DEFAULT FALSE,
    staff  BOOL      NOT NULL DEFAULT FALSE  -- created by staff

);
CREATE INDEX ahrn_hold_idx ON action.hold_request_note (hold);

-- Tweak a constraint to add a CASCADE

ALTER TABLE action.hold_notification DROP CONSTRAINT hold_notification_hold_fkey;

ALTER TABLE action.hold_notification
	ADD CONSTRAINT hold_notification_hold_fkey
		FOREIGN KEY (hold) REFERENCES action.hold_request (id)
		ON DELETE CASCADE
		DEFERRABLE INITIALLY DEFERRED;

CREATE TRIGGER asset_label_sortkey_trigger
    BEFORE UPDATE OR INSERT ON asset.call_number
    FOR EACH ROW EXECUTE PROCEDURE asset.label_normalizer();

CREATE OR REPLACE FUNCTION container.clear_all_expired_circ_history_items( )
RETURNS VOID AS $$
--
-- Delete expired circulation bucket items for all users that have
-- a setting for patron.max_reading_list_interval.
--
DECLARE
    today        TIMESTAMP WITH TIME ZONE;
    threshold    TIMESTAMP WITH TIME ZONE;
	usr_setting  RECORD;
BEGIN
	SELECT date_trunc( 'day', now() ) INTO today;
	--
	FOR usr_setting in
		SELECT
			usr,
			value
		FROM
			actor.usr_setting
		WHERE
			name = 'patron.max_reading_list_interval'
	LOOP
		--
		-- Make sure the setting is a valid interval
		--
		BEGIN
			threshold := today - CAST( translate( usr_setting.value, '"', '' ) AS INTERVAL );
		EXCEPTION
			WHEN OTHERS THEN
				RAISE NOTICE 'Invalid setting patron.max_reading_list_interval for user %: ''%''',
					usr_setting.usr, usr_setting.value;
				CONTINUE;
		END;
		--
		--RAISE NOTICE 'User % threshold %', usr_setting.usr, threshold;
		--
    	DELETE FROM container.copy_bucket_item
    	WHERE
        	bucket IN
        	(
        	    SELECT
        	        id
        	    FROM
        	        container.copy_bucket
        	    WHERE
        	        owner = usr_setting.usr
        	        AND btype = 'circ_history'
        	)
        	AND create_time < threshold;
	END LOOP;
	--
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION container.clear_all_expired_circ_history_items( ) IS $$
/*
 * Delete expired circulation bucket items for all users that have
 * a setting for patron.max_reading_list_interval.
*/
$$;

CREATE OR REPLACE FUNCTION container.clear_expired_circ_history_items( 
	 ac_usr IN INTEGER
) RETURNS VOID AS $$
--
-- Delete old circulation bucket items for a specified user.
-- "Old" means older than the interval specified by a
-- user-level setting, if it is so specified.
--
DECLARE
    threshold TIMESTAMP WITH TIME ZONE;
BEGIN
	-- Sanity check
	IF ac_usr IS NULL THEN
		RETURN;
	END IF;
	-- Determine the threshold date that defines "old".  Subtract the
	-- interval from the system date, then truncate to midnight.
	SELECT
		date_trunc( 
			'day',
			now() - CAST( translate( value, '"', '' ) AS INTERVAL )
		)
	INTO
		threshold
	FROM
		actor.usr_setting
	WHERE
		usr = ac_usr
		AND name = 'patron.max_reading_list_interval';
	--
	IF threshold is null THEN
		-- No interval defined; don't delete anything
		-- RAISE NOTICE 'No interval defined for user %', ac_usr;
		return;
	END IF;
	--
	-- RAISE NOTICE 'Date threshold: %', threshold;
	--
	-- Threshold found; do the delete
	delete from container.copy_bucket_item
	where
		bucket in
		(
			select
				id
			from
				container.copy_bucket
			where
				owner = ac_usr
				and btype = 'circ_history'
		)
		and create_time < threshold;
	--
	RETURN;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION container.clear_expired_circ_history_items( INTEGER ) IS $$
/*
 * Delete old circulation bucket items for a specified user.
 * "Old" means older than the interval specified by a
 * user-level setting, if it is so specified.
*/
$$;

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

UPDATE  metabib.rec_descriptor
  SET   date1=LPAD(NULLIF(REGEXP_REPLACE(NULLIF(date1, ''), E'\\D', '0', 'g')::INT,0)::TEXT,4,'0'),
        date2=LPAD(NULLIF(REGEXP_REPLACE(NULLIF(date2, ''), E'\\D', '9', 'g')::INT,9999)::TEXT,4,'0');

-- Change some ints to bigints:

ALTER TABLE container.biblio_record_entry_bucket_item
	ALTER COLUMN target_biblio_record_entry SET DATA TYPE bigint;

ALTER TABLE vandelay.queued_bib_record
	ALTER COLUMN imported_as SET DATA TYPE bigint;

ALTER TABLE action.hold_copy_map
	ALTER COLUMN id SET DATA TYPE bigint;

-- Make due times get pushed to 23:59:59 on insert OR update
DROP TRIGGER IF EXISTS push_due_date_tgr ON action.circulation;
CREATE TRIGGER push_due_date_tgr BEFORE INSERT OR UPDATE ON action.circulation FOR EACH ROW EXECUTE PROCEDURE action.push_circ_due_time();

INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath, remove )
SELECT 'upc', 'UPC', '//*[@tag="024" and @ind1="1"]/*[@code="a"]', $r$(?:-|\s.+$)$r$
WHERE NOT EXISTS (
    SELECT 1 FROM acq.lineitem_marc_attr_definition WHERE code = 'upc'
);  

COMMIT;

-- Some operations go outside of the transaction, because they may
-- legitimately fail.

\qecho ALTERs of auditor.action_hold_request_history will fail if the table
\qecho doesn't exist; ignore those errors if they occur.

ALTER TABLE auditor.action_hold_request_history ADD COLUMN cut_in_line BOOL;

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

ALTER TABLE auditor.action_hold_request_history
ADD COLUMN shelf_expire_time TIMESTAMPTZ;

\qecho Outside of the transaction: adding indexes that may or may not exist.
\qecho If any of these CREATE INDEX statements fails because the index already
\qecho exists, ignore the failure.

CREATE INDEX acq_picklist_owner_idx   ON acq.picklist ( owner );
CREATE INDEX acq_picklist_creator_idx ON acq.picklist ( creator );
CREATE INDEX acq_picklist_editor_idx  ON acq.picklist ( editor );
CREATE INDEX acq_po_note_creator_idx  ON acq.po_note ( creator );
CREATE INDEX acq_po_note_editor_idx   ON acq.po_note ( editor );
CREATE INDEX fund_alloc_allocator_idx ON acq.fund_allocation ( allocator );
CREATE INDEX li_creator_idx   ON acq.lineitem ( creator );
CREATE INDEX li_editor_idx    ON acq.lineitem ( editor );
CREATE INDEX li_selector_idx  ON acq.lineitem ( selector );
CREATE INDEX li_note_creator_idx  ON acq.lineitem_note ( creator );
CREATE INDEX li_note_editor_idx   ON acq.lineitem_note ( editor );
CREATE INDEX li_usr_attr_def_usr_idx  ON acq.lineitem_usr_attr_definition ( usr );
CREATE INDEX po_editor_idx   ON acq.purchase_order ( editor );
CREATE INDEX po_creator_idx  ON acq.purchase_order ( creator );
CREATE INDEX acq_po_org_name_order_date_idx ON acq.purchase_order( ordering_agency, name, order_date );
CREATE INDEX action_in_house_use_staff_idx  ON action.in_house_use ( staff );
CREATE INDEX action_non_cat_circ_patron_idx ON action.non_cataloged_circulation ( patron );
CREATE INDEX action_non_cat_circ_staff_idx  ON action.non_cataloged_circulation ( staff );
CREATE INDEX action_survey_response_usr_idx ON action.survey_response ( usr );
CREATE INDEX ahn_notify_staff_idx           ON action.hold_notification ( notify_staff );
CREATE INDEX circ_all_usr_idx               ON action.circulation ( usr );
CREATE INDEX circ_circ_staff_idx            ON action.circulation ( circ_staff );
CREATE INDEX circ_checkin_staff_idx         ON action.circulation ( checkin_staff );
CREATE INDEX hold_request_fulfillment_staff_idx ON action.hold_request ( fulfillment_staff );
CREATE INDEX hold_request_requestor_idx     ON action.hold_request ( requestor );
CREATE INDEX non_cat_in_house_use_staff_idx ON action.non_cat_in_house_use ( staff );
CREATE INDEX actor_usr_note_creator_idx     ON actor.usr_note ( creator );
CREATE INDEX actor_usr_standing_penalty_staff_idx ON actor.usr_standing_penalty ( staff );
CREATE INDEX usr_org_unit_opt_in_staff_idx  ON actor.usr_org_unit_opt_in ( staff );
CREATE INDEX asset_call_number_note_creator_idx ON asset.call_number_note ( creator );
CREATE INDEX asset_copy_note_creator_idx    ON asset.copy_note ( creator );
CREATE INDEX cp_creator_idx                 ON asset.copy ( creator );
CREATE INDEX cp_editor_idx                  ON asset.copy ( editor );

CREATE INDEX actor_card_barcode_lower_idx ON actor.card (lower(barcode));

DROP INDEX IF EXISTS authority.unique_by_heading_and_thesaurus;

\qecho If the following CREATE INDEX fails, It will be necessary to do some
\qecho data cleanup as described in the comments.

CREATE UNIQUE INDEX unique_by_heading_and_thesaurus
    ON authority.record_entry (authority.normalize_heading(marc))
	WHERE deleted IS FALSE or deleted = FALSE;

-- If the unique index fails, uncomment the following to create
-- a regular index that will help find the duplicates in a hurry:
--CREATE INDEX by_heading_and_thesaurus
--    ON authority.record_entry (authority.normalize_heading(marc))
--    WHERE deleted IS FALSE or deleted = FALSE
--;

-- Then find the duplicates like so to get an idea of how much
-- pain you're looking at to clean things up:
--SELECT id, authority.normalize_heading(marc)
--    FROM authority.record_entry
--    WHERE authority.normalize_heading(marc) IN (
--        SELECT authority.normalize_heading(marc)
--        FROM authority.record_entry
--        GROUP BY authority.normalize_heading(marc)
--        HAVING COUNT(*) > 1
--    )
--;

-- Once you have removed the duplicates and the CREATE UNIQUE INDEX
-- statement succeeds, drop the temporary index to avoid unnecessary
-- duplication:
-- DROP INDEX authority.by_heading_and_thesaurus;

-- 0448.data.trigger.circ.staff_age_to_lost.sql

INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES 
    (   'circ.staff_age_to_lost',
        'circ', 
        oils_i18n_gettext(
            'circ.staff_age_to_lost',
            'An overdue circulation should be aged to a Lost status.',
            'ath',
            'description'
        ), 
        TRUE
    )
;

INSERT INTO action_trigger.event_definition (
        id,
        active,
        owner,
        name,
        hook,
        validator,
        reactor,
        delay_field
    ) VALUES (
        36,
        FALSE,
        1,
        'circ.staff_age_to_lost',
        'circ.staff_age_to_lost',
        'CircIsOverdue',
        'MarkItemLost',
        'due_date'
    )
;


\qecho Upgrade script completed.

