CREATE OR REPLACE FUNCTION sitka.overdrive_bc_delete_all_uri_fields( TEXT ) 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 );

    # check all 856s
    URIFIELD: foreach my $uri ($r->field('856')){

        # Overdrive uses a $3 for Excerpts, we want to keep this intact so carry on then
        next URIFIELD if ($uri->subfield('3')); 

        # otherwise, delete away
        $r->delete_field($uri);
    }

    $xml = $r->as_xml_record();
    $xml =~ s/^<\?.+?\?>$//mo;
    $xml =~ s/\n//sgo;
    $xml =~ s/>\s+</></sgo;
    return $xml;
$func$ LANGUAGE PLPERLU;

CREATE OR REPLACE FUNCTION sitka.overdrive_bc_add_uri_field( TEXT, TEXT, TEXT, TEXT, TEXT ) RETURNS TEXT AS $func$
    use MARC::Record;
    use MARC::File::XML (BinaryEncoding => 'UTF-8');

    my $xml = shift;
	my $tcn_prefix = shift;
	my $url_prefix = shift;
	my $uri_caption = shift;
    my $shortname = shift;

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

    my $tcn = $r->subfield('901','a');
    $tcn =~ s/^$tcn_prefix//g;

    foreach my $uri ($r->field('856')){
        if($uri->subfield('9')){
            if($uri->subfield('9') eq $shortname){
                $r->delete_field($uri);
            }
        }
    }
    my $field856 = MARC::Field->new(
            '856' => ('4', '0'),
            u => $url_prefix . $tcn,
            y => $uri_caption,
            9 => $shortname
    );

    $r->insert_fields_ordered($field856);

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

$func$ LANGUAGE PLPERLU;

-- #u => "http://downloads.bclibrary.ca/ContentDetails.htm?ID=" . $tcn,
-- "Click to access online (library card required)",


CREATE OR REPLACE FUNCTION sitka.overdrive_bc_refresh_uris ( rid BIGINT ) RETURNS BOOLEAN AS $$
DECLARE
	marcxml TEXT;
	tcnprefix TEXT := 'Overdrive_';
	urlprefix TEXT := 'http://downloads.bclibrary.ca/ContentDetails.htm?ID=';
	urlcaption TEXT := 'Click to access online (library card required)';
BEGIN
    SELECT sitka.overdrive_bc_delete_all_uri_fields(marc) FROM biblio.record_entry where id = rid INTO marcxml;

	-- clean out asset.call_number and asset.uri and asset.uri_call_number_map

	-- could be modified to pull shortnames based on an ou setting
	-- select sitka.overdrive_MARCXML_add_uri_field(rid,tcnprefix,urlprefix,urlcaption,a.shortname) 
	-- from actor.org_unit_setting b join actor.org_unit a on (a.id = b.org_unit) where b.name = 'sitka.overdrive_setting' and b.value = true
	-- something like that

	SELECT sitka.overdrive_bc_add_uri_field(marcxml::TEXT, tcnprefix::TEXT, urlprefix::TEXT, urlcaption::TEXT, shortname::TEXT) FROM sitka.bc_overdrive_subscribers INTO marcxml;

    UPDATE biblio.record_entry set marc = marcxml WHERE id = rid;
	return true;
END;
$$ LANGUAGE PLPGSQL;

CREATE TABLE sitka.bc_overdrive_subscribers (shortname TEXT);
INSERT INTO sitka.bc_overdrive_subscribers VALUES
('BB'),
('BNV'),
('BMV'),
('BVA'),
('BPE'),
('BCOQ'),
('BNW'),
('BSUR'),
('BNVD'),
('BBI'),
('BSQ'),
('BW'),
('BRI'),
('BABF'),
('BLP'),
('BGIBSE'),
('BPMP'),
('BMB'),
('BVDH'),
('BFSJA'),
('BPG'),
('BWLCR'),
('BMK'),
('BGSI'),
('BCR'),
('BVI'),
('BS'),
('BFN'),
('BPRDP'),
('BTA'),
('BNA'),
('BF'),
('BH'),
('BSPA'),
('BHA'),
('BTR'),
('BHH'),
('BPR'),
('BCHE'),
('BKASL'),
('BFSJ'),
('BR'),
('BFBV'),
('BKI'),
('BCD'),
('BIN'),
('BGF'),
('BSA'),
('BNE'),
('BT'),
('BTE'),
('BCRE'),
('BKO'),
('BP'),
('BKCT'),
('BNVI');