/* update the section content of an assertion according to its scheme and assertion id (eg. : 'ATNA', 'ATNA-1') */

CREATE OR REPLACE FUNCTION update_section_number_based_scheme_and_assertion_id(schemeid text, assertion text, sectionnumber text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
BEGIN
update am_oasis_tag
set content=$3 where id in (
	select t.id
	from
	am_oasis_tag t,
	am_oasis_test_assertion_tag tat,
	am_oasis_test_assertion ta, 
	am_oasis_target ot 
	where 	t.name in ('Section', 'Table', 'Figure')
	and 	t.id = tat.tag_id
	and 	tat.test_assertion_id = ta.id
	and 	ot.id = ta.target_id
	and 	ta.id in(
			select ta.id
			FROM am_oasis_tag t,
			am_oasis_test_assertion_tag tat,
			am_oasis_test_assertion ta,
			am_oasis_target ot
			WHERE ta.assertion_id = $2
			AND tat.tag_id = t.id
			AND ta.id = tat.test_assertion_id
			And ot.idscheme=$1
			AND ot.id = ta.target_id
        	)

	);
return 1 ;
END;
$_$;

/* update the section content AND NAME (Section, Table or Figure) of an assertion according to its scheme and assertion id (eg. : 'ATNA', 'ATNA-1') */

CREATE OR REPLACE FUNCTION update_section_number_based_scheme_and_assertion_id(schemeid text, assertion text, sectionnumber text, sectionname text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
BEGIN
update am_oasis_tag
set content=$3, name=$4 where id in (
	select t.id
	from
	am_oasis_tag t,
	am_oasis_test_assertion_tag tat,
	am_oasis_test_assertion ta,
	am_oasis_target ot
	where 	t.name in ('Section', 'Table', 'Figure')
	and 	t.id = tat.tag_id
	and 	tat.test_assertion_id = ta.id
	and 	ot.id = ta.target_id
	and 	ta.id in(
			select ta.id
			FROM am_oasis_tag t,
			am_oasis_test_assertion_tag tat,
			am_oasis_test_assertion ta,
			am_oasis_target ot
			WHERE ta.assertion_id = $2
			AND tat.tag_id = t.id
			AND ta.id = tat.test_assertion_id
			And ot.idscheme=$1
			AND ot.id = ta.target_id
        	)

	);
return 1 ;
END;
$_$;
