MVLC Part Fun

-- Fix "two parts on one copy" issues
DELETE FROM asset.copy_part_map WHERE id IN (SELECT acpm.id FROM asset.copy_part_map acpm JOIN asset.copy acp ON acpm.target_copy = acp.id JOIN asset.call_number acn ON acp.call_number = acn.id JOIN biblio.monograph_part bmp ON bmp.id = acpm.part
WHERE bmp.record != acn.record);

-- Remove unused parts
DELETE FROM biblio.monograph_part WHERE id NOT IN (select part from asset.copy_part_map UNION select target from action.hold_request where hold_type = 'P' UNION select target from action.aged_hold_request WHERE hold_type = 'P');

-- Rename parts!
CREATE OR REPLACE FUNCTION evergreen.mvlc_rename_part(part_id integer, new_label text) RETURNS void AS $func$
DECLARE
    record_id bigint;
    existing_part integer;
BEGIN
    SELECT INTO record_id record FROM biblio.monograph_part WHERE id = part_id;
    IF NOT FOUND THEN
        RETURN;
    END IF;
    PERFORM * FROM biblio.monograph_part WHERE id = part_id AND label = new_label;
    IF FOUND THEN
        RETURN;
    END IF;
    SELECT INTO existing_part id FROM biblio.monograph_part WHERE label = new_label AND record = record_id;
    IF FOUND THEN
        UPDATE asset.copy_part_map SET part = existing_part WHERE part = part_id;
        UPDATE action.hold_request SET target = existing_part WHERE target = part_id AND hold_type = 'P';
        UPDATE action.aged_hold_request SET target = existing_part WHERE target = part_id AND hold_type = 'P';
        DELETE FROM biblio.monograph_part WHERE id = part_id;
    ELSE
        UPDATE biblio.monograph_part SET label = new_label WHERE id = part_id;
    END IF;
END;
$func$ LANGUAGE plpgsql;

-- Standardize parts
CREATE OR REPLACE FUNCTION evergreen.check_parts() RETURNS void AS $func$
DECLARE
    current_part integer;
    current_label text;
    current_record bigint;
    new_label text;
BEGIN
    <<partloop>>
    FOR current_part, current_label, current_record IN SELECT id, label, record FROM biblio.monograph_part
    -- TEST
    --WHERE label ~* 'disc'
    ORDER BY record, label
    --LIMIT 1000
    -- END TEST
    LOOP
	-- Entire-record exceptions
	CONTINUE WHEN current_record IN (1245044);
	-- Specific part exceptions
	CONTINUE WHEN current_part IN (2925,2905,3124,5616,4643,5814,5619,5620,5621,5622,5623,5624,5625,5626,5627,5628,6614,6299,6305,5360);
	-- We don't know what to do with bonus/guide/whatever
	CONTINUE WHEN current_label ~* ANY(ARRAY['bonus', 'guide']);
        new_label := current_label;
        IF new_label ~ ANY (ARRAY[
            '^((Season|Series|Part|DVD|Blu-ray) [0-9]+, )?Disc [0-9]+(-[0-9]+)?$',
            '^DVD$',
            '^Blu-ray$',
            '^Blu-ray \+ DVD$',
            '^v\. [0-9]+(-[0-9]+)?(, pt\. [0-9]+(-[0-9]+)?)?$',
            '^(January|February|March|April|May|June|July|August|September|October|November|December|Fall|Spring|Summer|Winter)(/(January|February|March|April|May|June|July|August|September|October|November|December))?$',
            '^c\. [0-9]+(-[0-9]+)?$'
        ]) THEN
            CONTINUE partloop; -- Part is good! Move on
        END IF;

        new_label = regexp_replace(new_label, '^\s+', ''); -- Replace leading spaces
        new_label = regexp_replace(new_label, '\s+$', ''); -- Replace trailing spaces

        -- Generic #-# cleanups?
        new_label := regexp_replace(new_label, '0*([0-9]+) *[-&+] *0*([0-9]+)', '\1-\2');

        -- Digital copies? HAH!
        new_label := regexp_replace(new_label, ' *[+&] *(Digital|Ultraviolet) Copy', '', 'i');

        IF new_label ~* 'blu' THEN -- Some form of Blu-ray?
            new_label := regexp_replace(new_label, 'blue?([- ]?rays?)?', 'Blu-ray', 'i');
            new_label := regexp_replace(new_label, '^dvd *[+&/] *blu-ray$', 'Blu-ray + DVD', 'i');
            new_label := regexp_replace(new_label, '^(1 )?blu-ray( disc)? *([+&/]|and) *(1 )?dvd( disc)?$', 'Blu-ray + DVD', 'i');
            new_label := regexp_replace(new_label, '^Blu-ray disc *$','Blu-ray','i');
        END IF;

        -- Disc #(-#)?
        IF new_label ~* '^((Season|Series|Part|DVD|Blu-ray) [0-9], )?Disc.*' THEN -- Starts with Disc in some way
            new_label := regexp_replace(new_label, '^((Season|Series|Part|DVD|Blu-ray) [0-9], )?[Dd][Ii][Ss][Cc][Ss.]* *0*', '\1Disc '); -- Replace leading Disc variant(s) (and leading zeros)
            new_label := regexp_replace(new_label, '^((Season|Series|Part|DVD|Blu-ray) [0-9], )?Disc 0*([0-9]+)(,* *[0-9]+,*)? *[-&+] *0*([0-9]+).*$', '\1Disc \3-\5'); -- Range correction
            new_label := regexp_replace(new_label, '^(((Season|Series|Part|DVD|Blu-ray) [0-9], )?Disc [0-9]+(-[0-9]+)?).*$', '\1'); -- Strip trailing
        END IF;

        new_label := regexp_replace(new_label, '^Volumes? ', 'v. ','i'); -- Change Volume into v.
        -- v. #(-#)?(, pt. #)?
        IF new_label ~* '^v(ol)?\..*' THEN -- Starts with v. or vol. in some way
            -- EXCEPTION FUN!
            IF current_part = 5303 THEN
                new_label := 'v. 1-2 + Guidebook';
            ELSIF current_part = 3072 THEN
                new_label := 'v. 1-3';
            ELSE
                new_label := regexp_replace(new_label, '^v(ol)?. *0*', 'v. ','i'); -- Start with "v. " and remove leading zeros
                new_label := regexp_replace(new_label, '-v\. *', '-', 'ig'); -- Remove extra v. later (part 4340, 6910 test cases)
                new_label := regexp_replace(new_label, '^v\. 0*([0-9]+) *[-&+] *0*([0-9]+) *$', 'v. \1-\2'); -- Range correction
                new_label := regexp_replace(new_label, '([0-9]),* *pt\. *([0-9]+(-[0-9]+)?).*', '\1, pt. \2','i'); -- Part designator?
                new_label := regexp_replace(new_label, '([0-9]),* *Disc *([0-9]+).*', '\1, Disc \2','i'); -- Disc designator?
                new_label := regexp_replace(new_label, '^(v\. [0-9]+(-[0-9]+)?(, (pt\.|Disc) [0-9]+(-[0-9]+)?)?).*', '\1'); -- Remove trailing elements
            END IF;
        END IF;

        -- Month fun!
        --new_label := regexp_replace(new_label, '(January|February|March|April|May|June|July|August|September|October|November|December) *[-/] *(January|February|March|April|May|June|July|August|September|October|November|December)', '\1/\2', 'i');

        IF new_label != current_label THEN
            RAISE NOTICE 'Change %	%	%	%', current_part, current_record, current_label, new_label;
            PERFORM evergreen.mvlc_rename_part(current_part, new_label);
            CONTINUE partloop;
        END IF;
        RAISE NOTICE 'Unknown %	%	%', current_part, current_record, current_label;
    END LOOP;
END;
$func$ LANGUAGE plpgsql;

SELECT evergreen.check_parts();