merge users from staging

BEGIN;

CREATE OR REPLACE FUNCTION staged_patron_update () RETURNS INT AS $func$
DECLARE

    live_user   actor.usr%ROWTYPE;
    live_card   actor.card%ROWTYPE;
    live_maddr  actor.usr_address%ROWTYPE;
    live_baddr  actor.usr_address%ROWTYPE;
    live_statcat    actor.stat_cat%ROWTYPE;
    live_statcat_entry  actor.stat_cat_entry_usr_map%ROWTYPE;

    staged_user staging.user_stage%ROWTYPE;
    staged_card staging.card_stage%ROWTYPE;
    staged_maddr    staging.mailing_address_stage%ROWTYPE;
    staged_baddr    staging.billing_address_stage%ROWTYPE;
    staged_statcat  staging.statcat_stage%ROWTYPE;

    tmp_dob     TEXT;

    update_count    int := 0;
BEGIN

    FOR staged_user IN SELECT * FROM staging.user_stage WHERE complete IS FALSE ORDER BY row_id LOOP

        tmp_dob := staged_user.dob;
        IF tmp_dob ~ E'^\\s+$' THEN
            RAISE NOTICE 'User % has no DOB', staged_user.usrname;
            tmp_dob = NULL;
        END IF;

        PERFORM 1 FROM actor.usr WHERE usrname = staged_user.usrname;

        IF NOT FOUND THEN
            RAISE NOTICE 'Adding user %', staged_user.usrname;
            INSERT INTO actor.usr (usrname,profile,email,passwd,ident_type,first_given_name,family_name,day_phone,evening_phone,home_ou,dob)
                SELECT  usrname,profile,email,passwd,ident_type,first_given_name,family_name,day_phone,evening_phone,home_ou,tmp_dob::DATE
                  FROM  staging.user_stage
                  WHERE row_id = staged_user.row_id;
        ELSE
            RAISE NOTICE 'Updating user %', staged_user.usrname;
            UPDATE  actor.usr
              SET   profile = staged_user.profile,
                email = staged_user.email,
                passwd = staged_user.passwd,
                ident_type = staged_user.ident_type,
                first_given_name = staged_user.first_given_name,
                family_name = staged_user.family_name,
                day_phone = staged_user.day_phone,
                evening_phone = staged_user.evening_phone,
                home_ou = staged_user.home_ou,
                dob = tmp_dob::DATE
              WHERE usrname = staged_user.usrname;
        END IF;

        update_count = update_count + 1;

        UPDATE staging.user_stage SET complete = TRUE WHERE row_id = staged_user.row_id;

    END LOOP;

    FOR staged_card IN SELECT * FROM staging.card_stage WHERE complete IS FALSE ORDER BY row_id LOOP

        SELECT * INTO live_user FROM actor.usr WHERE usrname = staged_card.usrname;

        IF live_user.id IS NOT NULL THEN

            SELECT * INTO live_card FROM actor.card WHERE barcode = staged_card.barcode;

            IF live_card.id IS NULL THEN

                IF live_user.id IS NOT NULL THEN
                    RAISE NOTICE 'Inserting card % for user %', staged_card.barcode, staged_card.usrname;
                    INSERT INTO actor.card (usr,barcode) VALUES (live_user.id, staged_card.barcode);

                    UPDATE actor.card SET active = FALSE WHERE usr = live_user.id AND barcode <> staged_card.barcode;

                    UPDATE  actor.usr
                      SET   card = actor.card.id
                      FROM  actor.card
                      WHERE actor.usr.usrname = staged_user.usrname
                        AND actor.card.usr = actor.usr.id
                        AND actor.card.active IS TRUE;

                    update_count = update_count + 1;
                END IF;

            ELSE
                RAISE NOTICE 'Updating card % for user %', staged_card.barcode, staged_card.usrname;
                UPDATE actor.card SET usr = live_user.id WHERE id = live_card.id;
                UPDATE actor.usr SET card = live_card.id WHERE id = live_user.id;

                UPDATE actor.card SET active = FALSE WHERE usr = live_user.id AND id <> live_card.id;

                update_count = update_count + 1;
            END IF;

            live_card.id = NULL;

        END IF;

        live_user.id = NULL;
        UPDATE staging.card_stage SET complete = TRUE WHERE row_id = staged_card.row_id;

    END LOOP;

    FOR staged_maddr IN SELECT * FROM staging.mailing_address_stage WHERE complete IS FALSE ORDER BY row_id LOOP

        SELECT * INTO live_user FROM actor.usr WHERE usrname = staged_maddr.usrname;

        IF live_user.id IS NOT NULL THEN

            IF live_user.mailing_address IS NOT NULL THEN
                SELECT * INTO live_maddr FROM actor.usr_address WHERE id = live_user.mailing_address;
            END IF;

            IF live_maddr.id IS NULL THEN
                RAISE NOTICE 'Inserting mailing address for user %', staged_maddr.usrname;
                INSERT INTO actor.usr_address (usr,street1,street2,city,state,country,post_code)
                    VALUES (live_user.id,staged_maddr.street1,staged_maddr.street2,staged_maddr.city,staged_maddr.state,staged_maddr.country,staged_maddr.post_code);
                UPDATE actor.usr SET mailing_address = currval('actor.usr_address_id_seq') WHERE usrname = staged_maddr.usrname;
            ELSE
                RAISE NOTICE 'Updating mailing address for user %', staged_maddr.usrname;
                UPDATE  actor.usr_address
                  SET   street1 = staged_maddr.street1,
                    street2 = staged_maddr.street2,
                    city = staged_maddr.city,
                    state = staged_maddr.state,
                    country = staged_maddr.country,
                    post_code = staged_maddr.post_code
                  WHERE id = live_maddr.id;
            END IF;

            live_maddr.id := NULL;

            update_count = update_count + 1;

        END IF;

        live_user.id = NULL;
        UPDATE staging.mailing_address_stage SET complete = TRUE WHERE row_id = staged_maddr.row_id;

    END LOOP;

    FOR staged_baddr IN SELECT * FROM staging.billing_address_stage WHERE complete IS FALSE ORDER BY row_id LOOP

        SELECT * INTO live_user FROM actor.usr WHERE usrname = staged_baddr.usrname;

        IF live_user.id IS NOT NULL THEN

            IF live_user.billing_address IS NOT NULL THEN
                SELECT * INTO live_baddr FROM actor.usr_address WHERE id = live_user.billing_address;
            END IF;

            IF live_baddr.id IS NULL THEN
                RAISE NOTICE 'Inserting billing address for user %', staged_baddr.usrname;
                INSERT INTO actor.usr_address (usr,street1,street2,city,state,country,post_code)
                    VALUES (live_user.id,staged_baddr.street1,staged_baddr.street2,staged_baddr.city,staged_baddr.state,staged_baddr.country,staged_baddr.post_code);
                UPDATE actor.usr SET billing_address = currval('actor.usr_address_id_seq') WHERE usrname = staged_baddr.usrname;
            ELSE
                RAISE NOTICE 'Updating billing address for user %', staged_baddr.usrname;
                UPDATE  actor.usr_address
                  SET   street1 = staged_baddr.street1,
                    street2 = staged_baddr.street2,
                    city = staged_baddr.city,
                    state = staged_baddr.state,
                    country = staged_baddr.country,
                    post_code = staged_baddr.post_code
                  WHERE id = live_baddr.id;
            END IF;

            live_baddr.id := NULL;

            update_count = update_count + 1;

        END IF;

        live_user.id = NULL;
        UPDATE staging.billing_address_stage SET complete = TRUE WHERE row_id = staged_baddr.row_id;

    END LOOP;

    FOR staged_statcat IN SELECT * FROM staging.statcat_stage WHERE complete IS FALSE ORDER BY row_id LOOP

        SELECT * INTO live_user FROM actor.usr WHERE usrname = staged_statcat.usrname;

        IF live_user.id IS NOT NULL THEN

            SELECT * INTO live_statcat FROM actor.stat_cat WHERE name = staged_statcat.statcat and owner = live_user.home_ou;

            IF live_statcat_entry.id IS NOT NULL THEN

                SELECT * INTO live_statcat_entry FROM actor.stat_cat_entry_usr_map WHERE stat_cat = live_statcat.id and target_usr = live_user.id;

                IF live_statcat_entry.id IS NULL THEN
                    RAISE NOTICE 'Inserting statcat %=>% for user %', staged_statcat.statcat, staged_statcat.value, staged_statcat.usrname;
                    INSERT INTO actor.stat_cat_entry_usr_map (target_usr, stat_cat, stat_cat_entry)
                        VALUES (live_user.id, live_statcat.id, staged_statcat.value);
                ELSE
                    RAISE NOTICE 'Updating statcat %=>% for user %', staged_statcat.statcat, staged_statcat.value, staged_statcat.usrname;
                    UPDATE  actor.stat_cat_entry_usr_map
                      SET   stat_cat_entry = staged_statcat.value
                      WHERE id = live_statcat_entry.id;
                END IF;

            END IF;

            live_statcat_entry.id := NULL;

            update_count = update_count + 1;

        END IF;

        live_user.id = NULL;
        UPDATE staging.statcat_stage SET complete = TRUE WHERE row_id = staged_statcat.row_id;

    END LOOP;

    RETURN update_count;
END;
$func$ LANGUAGE PLPGSQL;