SELECT * FROM drop_function_if_exists('getsuggestionmaterial','');
DROP TYPE IF EXISTS type_suggestion_material;
CREATE TYPE type_suggestion_material AS ( idPerson bigint, number int );

--obtem materias para sugestão na minha biblioteca
CREATE OR REPLACE FUNCTION getsuggestionmaterial()
RETURNS SETOF type_suggestion_material as $BODY$
DECLARE
    vclassification RECORD;
    vcontrolNumber RECORD;
BEGIN
    CREATE TEMP TABLE gtcPersonMaterial (personid bigint, controlnumber int); --tabela temporária para relacionar pessoa a número de controle
    
    FOR vclassification IN SELECT A.personId, 
                                A.classificationareaid, 
                                regexp_split_to_table(B.classification, ', ') as classification, 
                                regexp_split_to_table( regexp_split_to_table(coalesce(B.ignoreclassification,''), ', '), ' ,')  as ignoreclassification
                            FROM gtcinterestsarea A
                    INNER JOIN gtcclassificationarea B
                            USING (classificationareaid)
    LOOP
        FOR vcontrolNumber IN SELECT distinct(A.controlNumber) as controlNumber, 
                                            count(B.*) as max 
                                        FROM gtcexemplarycontrol A 
                                INNER JOIN gtcloan B 
                                    USING (itemnumber) 
                                LEFT JOIN gtcMyLibrary C
                                        ON (C.tableid = 'gtcMaterial')  
                                INNER JOIN gtcMaterial D
                                        USING (controlNumber)
                                    WHERE A.controlNumber NOT IN (SELECT controlnumber 
                                                                    FROM gtcloan 
                                                                INNER JOIN gtcExemplaryControl 
                                                                    USING (itemnumber) 
                                                                    WHERE personid = vclassification.personId)
                                        AND (D.fieldid = '090' AND D.subfieldid = 'a')
                                        AND D.content LIKE (vclassification.classification) 
                                        AND D.content NOT LIKE (vclassification.ignoreclassification)
                                        AND controlNumber NOT IN (SELECT tableid::int FROM gtcMyLibrary WHERE tablename = 'gtcMaterial' AND personId = vclassification.personId)                      
                                    GROUP BY 1 ORDER BY 2 DESC LIMIT 1
        LOOP                    
            INSERT INTO gtcPersonMaterial VALUES ( vclassification.personId, vcontrolNumber.controlnumber );

        END LOOP;            
    END LOOP;

    RETURN QUERY SELECT DISTINCT personId, controlNumber FROM gtcPersonMaterial;

    DROP TABLE gtcPersonMaterial;
    
END;
$BODY$ language plpgsql;
