--FUNÇÃO QUE FAZ A UNIÃO DE PESSOAS
CREATE OR REPLACE FUNCTION gtcPersonUnion(stayPerson bigint, outPerson bigint) 
RETURNS bool as $BODY$
DECLARE
    BEGIN

        PERFORM * FROM basPerson WHERE personId = stayPerson LIMIT 1;
        IF NOT FOUND
        THEN
            RAISE EXCEPTION 'Pessoa % não existe.' , stayPerson;
        END IF;
        PERFORM * FROM basPerson WHERE personId = outPerson;
        IF NOT FOUND
        THEN
            RAISE EXCEPTION 'Pessoa % não existe.' , outPerson;
        END IF;
        IF stayPerson = outPerson 
        THEN
            RAISE EXCEPTION 'É necessário escolher duas pessoas diferentes.';
        END IF;

        --UNIÃO.
        -- empréstimos
        UPDATE gtcLoan SET personId = stayPerson WHERE personId = outPerson;
        -- emprésimos entre biblioteca
        UPDATE gtcLoanBetweenLibrary SET personId = stayPerson WHERE personId = outPerson; 
        -- avaliação
        UPDATE gtcMaterialEvaluation SET personId = stayPerson WHERE personId = outPerson;
        -- mINha biblioteca
        UPDATE gtcMylibrary SET personId = stayPerson WHERE personId = outPerson;
        -- penalidades
        UPDATE gtcPenalty SET personId = stayPerson WHERE personId = outPerson;
        -- solicitação de compras
        UPDATE gtcPurchaseRequest SET personId = stayPerson WHERE personId = outPerson;
        -- solicitação de alteração de estado do exempla (congelamento)
        UPDATE gtcRequestChangeExemplaryStatus SET personId = stayPerson WHERE personId = outPerson;
        -- reservas
        UPDATE gtcReserve SET personId = stayPerson WHERE personId = outPerson;
        --CONTROLE DE NOTIFICAÇÃO DE E-MAIL DE REQUISIÇÃO.
        UPDATE gtcEmailControlNotifyAquisition SET personId = stayPerson WHERE personId = outPerson;
        --MANTER A DA PESSOA SELECIONADA, REMOVER AS OUTRAS.
        DELETE FROM gtcPersonConfig WHERE personId = outPerson;

        --TELEFONES
        INSERT INTO basPhone 
                    (personId, 
                    type ,phone)    
            (SELECT stayPerson,     
                    type, 
                    phone 
               FROM basPhone 
              WHERE personId = outPerson 
                AND type 
                 IN ( SELECT type 
               FROM basPhone 
              WHERE personId = outPerson EXCEPT
             SELECT type 
               FROM basPhone 
              WHERE personId = stayPerson)
            );

         DELETE FROM basPhone where personId = outPerson;

        --AREAS DE INTERESSE.
        INSERT INTO gtcInterestsArea
                    (personId,
                    classificationAreaId )
            (SELECT stayPerson,
                    classificationAreaId
               FROM gtcINterestsarea
              WHERE personId = outPerson
                AND classificationAreaId
             NOT IN ( SELECT classificationAreaId
               FROM gtcInterestsArea
              WHERE personId = stayPerson)
             );

        DELETE FROM gtcInterestsarea WHERE personId = outPerson;

        --LIGAÇÃO ENTRE PESSOAS
        INSERT INTO baspersonlink
                    (personId, 
                    linkId, 
                    dateValidate ) 
            (SELECT stayPerson, 
                    linkId, 
                    dateValidate 
               FROM baspersonLink               
              WHERE personId = outPerson 
                AND linkId NOT IN 
           ( SELECT linkId 
               FROM basPersonLink 
              WHERE personId = stayPerson)
           );

        DELETE FROM basPersonLink WHERE personId = outPerson;

        --ANALÍTICA

        UPDATE gtcAnalytics set personId = stayPerson where personId = outPerson;

        DELETE FROM gtcAnalytics WHERE personId = outPerson;

        --FAVORITOS
       INSERT INTO gtcFavorite 
                   (personId, 
                   controlNumber) 
           (select stayPerson, 
                   controlNumber 
              from gtcFavorite 
             where personId = outPerson 
               and controlNumber not in 
           (select controlNumber 
              from gtcFavorite 
             where personId = stayPerson)
           );

       DELETE FROM gtcFavorite where personId = outPerson;

       --PESSOAS NAS UNIDADES DE BIBLOTECA
       INSERT INTO gtcPersonLibraryUnit
                   (personId, 
                   libraryUnitId)    
                (SELECT stayPerson,     
                        libraryUnitId
                   FROM gtcPersonLibraryUnit
                  WHERE personId = outPerson 
                    AND libraryUnitId
                 NOT IN ( SELECT libraryUnitId
                   FROM gtcPersonLibraryUnit
                  WHERE personId = stayPerson)
                );

       DELETE FROM gtcPersonLibraryUnit where personId = outPerson;

        --DOCUMENTOS
        INSERT INTO basDocument 
                    (personId, 
                    documentTypeId) 
            (SELECT stayPerson, documentTypeId 
               FROM basDocument 
              WHERE personId = outPerson 
                AND documentTypeId  
             NOT IN (SELECT documentTypeId 
               FROM basDocument  
              WHERE personId = stayPerson)
            );

        DELETE FROM basDocument where personId = outPerson;

        IF (existTable( 'basphysicalperson' ))
        THEN    
            IF (existContract( outperson ))
            THEN
                IF NOT ( existContract( stayperson ) )
                THEN
                    RAISE EXCEPTION 'Pessoa % possui contrato e não pode ser unida ou removida. Tenta inverter as pessoas.', outPerson;
                END IF;

                RAISE EXCEPTION 'Pessoa % possui contrato e não pode ser unida ou removida.', outPerson;
            END IF;
        INSERT INTO basphysicalpersonkinship
                    (personid,
                     relativepersonid,
                     kinshipid,
                     datetime
                    ) 
            (SELECT stayPerson , 
                    relativepersonid, 
                    kinshipid,
                    datetime
               FROM basphysicalpersonkinship            
              WHERE personId = outPerson 
                AND kinshipid NOT IN 
           ( SELECT kinshipid
               FROM basphysicalpersonkinship
              WHERE personId = stayPerson )
           );
        DELETE FROM basphysicalpersonkinship WHERE personid = outPerson;

        INSERT INTO basbadgeloan
                    (personid,
                     datetime,
                     loanid, 
                     badgeid, 
                     loandate,  
                     expectedreturndate) 
            (SELECT stayPerson,
                    datetime, 
                    loanid,
                    badgeid,
                    loandate,
                    expectedreturndate
               FROM basbadgeloan            
              WHERE personId = outPerson 
                AND badgeid NOT IN 
           ( SELECT badgeid
               FROM basbadgeloan
              WHERE personId = stayPerson)
           );
        DELETE FROM basbadgeloan WHERE personid = outPerson;

        INSERT INTO bashistoricofuncional
                    (personid,
                     datetime,
                     historicofuncionalid,
                     data, 
                     assunto, 
                     inicio,  
                     observacao) 
            (SELECT stayPerson,
                    datetime, 
                    historicofuncionalid,
                    data,
                    assunto,
                    inicio,
                    observacao
               FROM bashistoricofuncional           
              WHERE personId = outPerson 
                AND historicofuncionalid NOT IN 
           ( SELECT historicofuncionalid
               FROM bashistoricofuncional
              WHERE personId = stayPerson)
           );
        DELETE FROM bashistoricofuncional WHERE personid = outPerson;

        INSERT INTO basprofessionalactivitypeople
                    (personid,
                     datetime,
                     professionalactivitypeopleid,
                     professionalactivityid, 
                     legalpersonid, 
                     begindate,  
                     professionalactivitylinktypeid) 
            (SELECT stayPerson,
                    datetime, 
                    professionalactivitypeopleid,
                    professionalactivityid,
                    legalpersonid,
                    begindate,
                    professionalactivitylinktypeid
               FROM basprofessionalactivitypeople           
              WHERE personId = outPerson 
                AND professionalactivityid NOT IN 
           ( SELECT professionalactivityid
               FROM basprofessionalactivitypeople
              WHERE personId = stayPerson)
           );
        DELETE FROM basprofessionalactivitypeople WHERE personid = outPerson;

        INSERT INTO bassectorboss
                    (bossid,
                     sectorid,
                     issendemail, 
                     email 
                     ) 
            (SELECT stayPerson,
                    sectorid,
                    issendemail, 
                    email
               FROM bassectorboss   
              WHERE bossid = outPerson 
                AND sectorid NOT IN 
           ( SELECT sectorid 
               FROM bassectorboss
              WHERE bossid = stayPerson)
           );
        DELETE FROM bassectorboss WHERE bossid = outPerson;

        INSERT INTO basstamp
                    (personid,
                     datetime,
                     stampid,
                     functiondescription 
                     ) 
            (SELECT stayPerson,
                    datetime, 
                    stampid,
                    functiondescription
               FROM basstamp            
              WHERE personId = outPerson 
                AND stampid NOT IN 
           ( SELECT stampid
               FROM basstamp
              WHERE personId = stayPerson)
           );
        DELETE FROM basstamp WHERE personid = outPerson;

        INSERT INTO basphysicalpersonprofessor 
                    ( personid , 
                    persondv , 
                    personmask , 
                    name , 
                    shortname , 
                    cityid , 
                    zipcode , 
                    location , 
                    number , 
                    complement , 
                    neighborhood , 
                    email , 
                    emailalternative , 
                    url , 
                    datein , 
                    password , 
                    isallowpersonaldata , 
                    miolousername , 
                    locationtypeid , 
                    sentemail , 
                    photoid , 
                    login , 
                    obs , 
                    sex , 
                    maritalstatusid , 
                    residentialphone , 
                    workphone , 
                    cellphone , 
                    messagephone , 
                    messagecontact , 
                    datebirth , 
                    cityidbirth , 
                    countryidbirth , 
                    responsablelegalid , 
                    carplate , 
                    specialnecessityid , 
                    specialnecessitydescription , 
                    cityidwork , 
                    zipcodework , 
                    locationwork , 
                    complementwork , 
                    neighborhoodwork , 
                    ethnicoriginid , 
                    datedeath , 
                    persontitleid , 
                    locationtypeidwork , 
                    mothername ,    
                    fathername , 
                    workemployername , 
                    workfunction , 
                    workstartdate ,
                    workenddate , 
                    workathome , 
                    ethnicorigin , 
                    identifier ) 
          ( SELECT  stayperson, 
                    persondv , 
                    personmask , 
                    name , 
                    shortname , 
                    cityid , 
                    zipcode , 
                    location , 
                    number , 
                    complement , 
                    neighborhood , 
                    email , 
                    emailalternative , 
                    url , 
                    datein , 
                    password , 
                    isallowpersonaldata , 
                    miolousername ,         
                    locationtypeid , 
                    sentemail , 
                    photoid , 
                    login , 
                    obs , 
                    sex , 
                    maritalstatusid , 
                    residentialphone , 
                    workphone ,         
                    cellphone  , 
                    messagephone , 
                    messagecontact ,    
                    datebirth , 
                    cityidbirth , 
                    countryidbirth , 
                    responsablelegalid , 
                    carplate , 
                    specialnecessityid , 
                    specialnecessitydescription , 
                    cityidwork , 
                    zipcodework , 
                    locationwork , 
                    complementwork , 
                    neighborhoodwork , 
                    ethnicoriginid , 
                    datedeath , 
                    persontitleid , 
                    locationtypeidwork , 
                    mothername , 
                    fathername , 
                    workemployername , 
                    workfunction , 
                    workstartdate , 
                    workenddate , 
                    workathome , 
                    ethnicorigin , 
                    identifier 
               FROM basphysicalpersonprofessor 
              WHERE personid = outperson);

        INSERT INTO basprofessorcommitment
                    (personid,
                     datetime,
                     begindate,
                     workload 
                     ) 
            (SELECT stayPerson,
                    datetime, 
                    begindate,
                    workload
               FROM basprofessorcommitment          
              WHERE personId = outPerson 
                AND begindate NOT IN 
           ( SELECT begindate
               FROM basprofessorcommitment
              WHERE personId = stayPerson)
           );
        DELETE FROM basprofessorcommitment WHERE personid = outPerson; 

        UPDATE fininvoice SET personId = stayPerson WHERE personId = outPerson;

        INSERT INTO acdprofessorformation
                    (professorid,
                     formationlevelid,
                     externalcourseid,
                     begindate,
                     dateconclusion,
                     institutionid 
                     ) 
            (SELECT stayPerson,
                    formationlevelid,
                    externalcourseid,
                    begindate,
                    dateconclusion,
                    institutionid 
               FROM acdprofessorformation           
              WHERE professorid = outPerson 
                AND formationlevelid NOT IN 
           ( SELECT formationlevelid
               FROM acdprofessorformation
              WHERE professorid = stayPerson)
                AND externalcourseid NOT IN
           ( SELECT externalcourseid
               FROM acdprofessorformation
              WHERE professorid = stayPerson));
         DELETE FROM acdprofessorformation WHERE professorid = outPerson;

   INSERT INTO acdprofessorcenter
                    (professorid,
                     centerid,
                     begindate 
                     ) 
            (SELECT stayPerson,
                    centerid,
                    begindate
               FROM acdprofessorcenter      
              WHERE professorid = outPerson 
                AND centerid NOT IN 
           ( SELECT centerid
               FROM acdprofessorcenter
              WHERE professorid = stayPerson));
    DELETE FROM acdprofessorcenter WHERE professorid = outPerson;

    INSERT INTO basphysicalpersonemployee 
                (personid , 
                persondv , 
                personmask , 
                name , 
                shortname , 
                cityid , 
                zipcode , 
                location , 
                number , 
                complement , 
                neighborhood , 
                email , 
                emailalternative , 
                url , 
                datein , 
                password , 
                isallowpersonaldata , 
                miolousername ,     
                locationtypeid , 
                sentemail , 
                photoid , 
                login , 
                obs , 
                sex , 
                maritalstatusid , 
                residentialphone , 
                workphone , 
                cellphone , 
                messagephone , 
                messagecontact , 
                datebirth , 
                cityidbirth , 
                countryidbirth , 
                responsablelegalid , 
                carplate , 
                specialnecessityid , 
                specialnecessitydescription , 
                cityidwork , 
                zipcodework , 
                locationwork , 
                complementwork , 
                neighborhoodwork , 
                ethnicoriginid , 
                datedeath , 
                persontitleid , 
                locationtypeidwork ,    
                mothername , 
                fathername ,
                workemployername , 
                workfunction , 
                workstartdate , 
                workenddate , 
                workathome , 
                ethnicorigin ,  
                identifier) 
       ( SELECT stayperson ,   
                persondv , 
                personmask , 
                name , 
                shortname , 
                cityid , 
                zipcode , 
                location , 
                number , 
                complement , 
                neighborhood , 
                email , 
                emailalternative , 
                url , 
                datein , 
                password , 
                isallowpersonaldata , 
                miolousername , 
                locationtypeid , 
                sentemail , 
                photoid , 
                login , 
                obs , 
                sex , 
                maritalstatusid , 
                residentialphone , 
                workphone , 
                cellphone , 
                messagephone , 
                messagecontact , 
                datebirth , 
                cityidbirth , 
                countryidbirth , 
                responsablelegalid , 
                carplate , 
                specialnecessityid , 
                specialnecessitydescription , 
                cityidwork , 
                zipcodework , 
                locationwork , 
                complementwork , 
                neighborhoodwork , 
                ethnicoriginid , 
                datedeath , 
                persontitleid , 
                locationtypeidwork , 
                mothername , 
                fathername , 
                workemployername , 
                workfunction , 
                workstartdate , 
                workenddate , 
                workathome , 
                ethnicorigin , 
                identifier 
           FROM basphysicalpersonemployee 
          WHERE personid = outperson);

    INSERT INTO basemployee
                    (personid,
                     employeetypeid 
                     ) 
            (SELECT stayPerson,
                    employeetypeid
               FROM basemployee     
              WHERE personid = outPerson 
                AND employeetypeid NOT IN 
           ( SELECT employeetypeid
               FROM basemployee
              WHERE personid = stayPerson));
    DELETE FROM basemployee WHERE personid = outPerson;

    INSERT INTO  Acdmoodlesubscription
                    (personId, 
                    groupid)    
            (SELECT stayPerson,
                    groupid     

               FROM Acdmoodlesubscription
              WHERE personId = outPerson 
                AND groupid
                 IN ( SELECT groupid
               FROM Acdmoodlesubscription 
              WHERE personId = outPerson EXCEPT
             SELECT groupid 
               FROM Acdmoodlesubscription 
              WHERE personId = stayPerson)
            );

         DELETE FROM Acdmoodlesubscription  where personId = outPerson;

    DELETE FROM basphysicalperson WHERE personId = outperson; 
    DELETE FROM basphysicalpersonprofessor WHERE personId = outperson;
    DELETE FROM basphysicalpersonemployee WHERE personId = outperson; 
    DELETE FROM basphysicalpersonstudent WHERE personId = outperson; 
    UPDATE fininvoice SET personId = stayPerson WHERE personId = outPerson; 
    END IF;

    DELETE FROM basDocument where personId = outPerson;
    DELETE FROM gtcLibPerson WHERE personId = outPerson;
    DELETE FROM basPerson WHERE personId = outperson;

        RETURN true;

        END;
$BODY$ language plpgsql;
