\set ON_ERROR_STOP 1

BEGIN;

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;
BEGIN
	-- 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;

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


CREATE INDEX biblio_record_entry_create_date_idx ON biblio.record_entry ( create_date );
CREATE INDEX biblio_record_entry_edit_date_idx ON biblio.record_entry ( edit_date );
 
CREATE OR REPLACE FUNCTION vandelay.match_bib_record ( ) RETURNS TRIGGER AS $func$
DECLARE
    attr    RECORD;
    eg_rec  RECORD;
BEGIN
    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;

    RETURN NULL;
END;
$func$ LANGUAGE PLPGSQL;


CREATE TABLE config.upgrade_log (
    version         TEXT    PRIMARY KEY,
    install_date    TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);
INSERT INTO config.upgrade_log (version) VALUES ('1.4.0.0rc2');

ALTER TABLE money.credit_card_payment ALTER cc_type DROP NOT NULL;
ALTER TABLE money.credit_card_payment ALTER cc_number DROP NOT NULL;
ALTER TABLE money.credit_card_payment ALTER expire_month DROP NOT NULL;
ALTER TABLE money.credit_card_payment ALTER expire_year DROP NOT NULL;
ALTER TABLE money.credit_card_payment ALTER approval_code DROP NOT NULL;


ALTER TABLE asset.copy ALTER price DROP NOT NULL;
ALTER TABLE asset.copy ALTER price DROP DEFAULT;


INSERT INTO permission.perm_list (code) VALUES ('CREATE_AUTHORITY_IMPORT_IMPORT_FIELD_DEF');
INSERT INTO permission.perm_list (code) VALUES ('CREATE_AUTHORITY_IMPORT_QUEUE');
INSERT INTO permission.perm_list (code) VALUES ('CREATE_AUTHORITY_RECORD_NOTE');
INSERT INTO permission.perm_list (code) VALUES ('CREATE_BIB_IMPORT_FIELD_DEF');
INSERT INTO permission.perm_list (code) VALUES ('CREATE_BIB_IMPORT_QUEUE');
INSERT INTO permission.perm_list (code) VALUES ('CREATE_LOCALE');
INSERT INTO permission.perm_list (code) VALUES ('CREATE_MARC_CODE');
INSERT INTO permission.perm_list (code) VALUES ('CREATE_TRANSLATION');
INSERT INTO permission.perm_list (code) VALUES ('DELETE_AUTHORITY_IMPORT_IMPORT_FIELD_DEF');
INSERT INTO permission.perm_list (code) VALUES ('DELETE_AUTHORITY_IMPORT_QUEUE');
INSERT INTO permission.perm_list (code) VALUES ('DELETE_AUTHORITY_RECORD_NOTE');
INSERT INTO permission.perm_list (code) VALUES ('DELETE_BIB_IMPORT_IMPORT_FIELD_DEF');
INSERT INTO permission.perm_list (code) VALUES ('DELETE_BIB_IMPORT_QUEUE');
INSERT INTO permission.perm_list (code) VALUES ('DELETE_LOCALE');
INSERT INTO permission.perm_list (code) VALUES ('DELETE_MARC_CODE');
INSERT INTO permission.perm_list (code) VALUES ('DELETE_TRANSLATION');
INSERT INTO permission.perm_list (code) VALUES ('UPDATE_AUTHORITY_IMPORT_IMPORT_FIELD_DEF');
INSERT INTO permission.perm_list (code) VALUES ('UPDATE_AUTHORITY_IMPORT_QUEUE');
INSERT INTO permission.perm_list (code) VALUES ('UPDATE_AUTHORITY_RECORD_NOTE');
INSERT INTO permission.perm_list (code) VALUES ('UPDATE_BIB_IMPORT_IMPORT_FIELD_DEF');
INSERT INTO permission.perm_list (code) VALUES ('UPDATE_BIB_IMPORT_QUEUE');
INSERT INTO permission.perm_list (code) VALUES ('UPDATE_LOCALE');
INSERT INTO permission.perm_list (code) VALUES ('UPDATE_MARC_CODE');
INSERT INTO permission.perm_list (code) VALUES ('UPDATE_TRANSLATION');
INSERT INTO permission.perm_list (code) VALUES ('VIEW_AUTHORITY_RECORD_NOTES');
INSERT INTO permission.perm_list (code) VALUES ('CREATE_IMPORT_ITEM');
INSERT INTO permission.perm_list (code) VALUES ('CREATE_IMPORT_ITEM_ATTR_DEF');
INSERT INTO permission.perm_list (code) VALUES ('CREATE_IMPORT_TRASH_FIELD');
INSERT INTO permission.perm_list (code) VALUES ('DELETE_IMPORT_ITEM');
INSERT INTO permission.perm_list (code) VALUES ('DELETE_IMPORT_ITEM_ATTR_DEF');
INSERT INTO permission.perm_list (code) VALUES ('DELETE_IMPORT_TRASH_FIELD');
INSERT INTO permission.perm_list (code) VALUES ('UPDATE_IMPORT_ITEM');
INSERT INTO permission.perm_list (code) VALUES ('UPDATE_IMPORT_ITEM_ATTR_DEF');
INSERT INTO permission.perm_list (code) VALUES ('UPDATE_IMPORT_TRASH_FIELD');
 
INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath ) VALUES (15, 'item_barcode',oils_i18n_gettext(15, 'Item Barcode', 'vqbrad', 'description'),'//*[@tag="852p"]/*[@code="a"][1]');
 
CREATE OR REPLACE FUNCTION permission.grp_descendants ( INT ) RETURNS SETOF permission.grp_tree AS $$
    SELECT  a.*
      FROM  connectby('permission.grp_tree'::text,'id'::text,'parent'::text,'name'::text,$1::text,100,'.'::text)
            AS t(keyid text, parent_keyid text, level int, branch text,pos int)
        JOIN permission.grp_tree a ON a.id::text = t.keyid::text
      ORDER BY  CASE WHEN a.parent IS NULL THEN 0 ELSE 1 END, a.name;
$$ LANGUAGE SQL STABLE;

CREATE OR REPLACE FUNCTION permission.grp_full_path ( INT ) RETURNS SETOF permission.grp_tree AS $$
    SELECT  *
      FROM  permission.grp_ancestors($1)
            UNION
    SELECT  *
      FROM  permission.grp_descendants($1);
$$ LANGUAGE SQL STABLE;

CREATE OR REPLACE FUNCTION permission.grp_combined_ancestors ( INT, INT ) RETURNS SETOF permission.grp_tree AS $$
    SELECT  *
      FROM  permission.grp_ancestors($1)
            UNION
    SELECT  *
      FROM  permission.grp_ancestors($2);
$$ LANGUAGE SQL STABLE;

CREATE OR REPLACE FUNCTION permission.grp_common_ancestors ( INT, INT ) RETURNS SETOF permission.grp_tree AS $$
    SELECT  *
      FROM  permission.grp_ancestors($1)
            INTERSECT
    SELECT  *
      FROM  permission.grp_ancestors($2);
$$ LANGUAGE SQL STABLE;

CREATE OR REPLACE FUNCTION permission.grp_proximity ( INT, INT ) RETURNS INT AS $$
    SELECT COUNT(id)::INT FROM (
        SELECT id FROM permission.grp_combined_ancestors($1, $2)
            EXCEPT
        SELECT id FROM permission.grp_common_ancestors($1, $2)
    ) z;
$$ LANGUAGE SQL STABLE;

COMMIT;
