/* Update the page number of the index from a given scheme, regardless of the index being a section or a table */
CREATE OR REPLACE FUNCTION update_page_number_based_scheme_and_content(schemeid text, contentstring text, pagenumber text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
BEGIN
update am_oasis_tag set content=$3 where id in(
    select am_oasis_tag.id
    from
    am_oasis_tag,
    am_oasis_test_assertion_tag,
    am_oasis_test_assertion
    where name='Page'
    and am_oasis_test_assertion_tag.tag_id = am_oasis_tag.id
    and am_oasis_test_assertion_tag.test_assertion_id= am_oasis_test_assertion.id
    and am_oasis_test_assertion.id in(
        select am_oasis_test_assertion.id
        FROM am_oasis_tag,
        am_oasis_test_assertion_tag,
        am_oasis_test_assertion,
        am_oasis_target
        WHERE am_oasis_tag.content = $2
        AND am_oasis_test_assertion_tag.tag_id=am_oasis_tag.id
        AND am_oasis_test_assertion.id = am_oasis_test_assertion_tag.test_assertion_id
        And am_oasis_target.idscheme=$1
        AND am_oasis_target.id = am_oasis_test_assertion.target_id
        )
    );
return 1 ;
END;
$_$;

/* Update page number for all scheme of a document */
CREATE OR REPLACE FUNCTION update_page_number_based_on_document_id_ignore_section_or_table(document_id integer, content text, page text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
DECLARE
    mviews RECORD;
BEGIN
        FOR mviews IN   select distinct idscheme 
                        from am_oasis_target as t 
                        join am_oasis_test_assertion as ta on t.id=ta.target_id 
                        join am_oasis_normative_source as ns on ns.id=ta.normative_source_id 
                        join am_oasis_normative_source_ref_source_item  as sr on sr.normative_source_id=ns.id 
                        join am_oasis_ref_source_item as r on r.id=sr.ref_source_item_id
                        where r.id=$1 
    LOOP	
        PERFORM "update_page_number_based_scheme_and_content"(mviews.idscheme, content, page);
    END LOOP;
RETURN 1;
END;
$_$;
