BEGIN;

DO $$ 
BEGIN
	BEGIN
		ALTER TABLE user_profile ADD COLUMN user_catalog_id character varying(255) default NULL;
		CREATE INDEX user_catalog_id_idx ON user_profile USING btree (user_catalog_id);
	EXCEPTION
		WHEN duplicate_column THEN RAISE NOTICE 'column user_catalog_id already exists in user_profile.';
	END;
	PERFORM setval('user_profile_custom_property_seq', (select max(id) from user_profile_custom_property) + 1); 
	PERFORM setval('user_profile_custom_property_val_seq', (select max(id) from user_profile_custom_property_val) + 1); 
END;
$$;


CREATE OR REPLACE FUNCTION updateUserProfile(profileBusinessId TEXT, userCatalogId TEXT)
RETURNS VOID AS $$
DECLARE
        affectedRows INT;
        profileId BIGINT;
BEGIN
	-- get db profile id based on user id (businessId)
	EXECUTE format('
		SELECT id FROM user_profile WHERE business_id = ''%1$s''
		', profileBusinessId)
	INTO profileId;

	IF profileId IS NULL THEN
		RETURN;
	END IF;

	-- update profile user catalog id field
	EXECUTE format('
		UPDATE user_profile SET user_catalog_id = ''%1$s'' WHERE business_id = ''%2$s''
                ', userCatalogId, profileBusinessId);

	-- try to update custom property if it exists
	EXECUTE format('
		UPDATE user_profile_custom_property_val SET value=''%1$s'' WHERE name = ''value'' AND property_id = 
		(
			SELECT property_id 
			FROM user_profile_custom_property_val prop_val, user_profile_custom_property prop, user_profile prof
			WHERE prop_val.property_id = prop.id AND prop.profile_id = prof.id AND prof.business_id = ''%2$s'' AND prop.key = ''userCatalogId''
		)
                ', userCatalogId, profileBusinessId);

	GET DIAGNOSTICS affectedRows = ROW_COUNT;

	-- insert custom property if doesn't exist
	IF affectedRows = 0 THEN
                EXECUTE format('
			INSERT INTO user_profile_custom_property(id, key, supplementary_key, profile_id, version) VALUES (nextval(''user_profile_custom_property_seq''), ''userCatalogId'', ''SUPPLEMENTARY_KEY'', %1$s, 0);
			', profileId);

		EXECUTE format('
			INSERT INTO user_profile_custom_property_val(id, name, value, property_id, version) VALUES (nextval(''user_profile_custom_property_val_seq''), ''value'', ''%1$s'', currval(''user_profile_custom_property_seq''),  0)
			', userCatalogId);
        END IF;

        RAISE NOTICE 'Zaktualizowano profil [%] o user cataolg id [%].', profileBusinessId, userCatalogId;
END;
$$ LANGUAGE plpgsql;


COMMIT;