-- DROP FUNCTION par.omisso_declarado_candidato_par();

CREATE OR REPLACE FUNCTION par.omisso_declarado_candidato_par()
 RETURNS TABLE(_processo character varying, _prpid integer, _dopid integer, _numero_termo bigint, _fase_pc_esddsc character varying, _fase_pc_esdid integer, _situacao_da_pc_esddsc character varying, _situacao_da_pc_esdid integer, _situacao_da_opc_esddsc character varying, _situacao_da_opc_esdid integer, _muncod character varying, _datainiciovigencia date, _datafimvigencia date, _total_cpf_ciencia integer, _contador_responsavel integer, _contador_ciencia integer, _aux_cpf_ciencia text, _aux_cpf_responsavel text)
 LANGUAGE plpgsql
AS $function$
	DECLARE
		erro_carga boolean default false;
		erro_msg text default '';
		dt_limite_envio_pc date; 
	    contador_cpf_ciencia integer default 0;
	    contador_ciencia integer default 0;
	    contador_responsavel integer default 0;
	    last_insert_odeid integer default 0;
	   	array_ciencia_cpf text[];
	    array_responsavel_cpf text[];
		array_ciencia_ids integer[];
	   	sem_dtinicio_mandato integer default 0;
	    programa record;
	    cpfs_ciencia record;
	    cpfs_responsavel record;
		verifica_equivalencia boolean default false;
	BEGIN
			FOR programa IN(
			    WITH wf_documento_estado AS
			    (
			        SELECT
			            esd.esddsc, docid, doc.esdid
			        FROM workflow.documento doc
			            INNER join workflow.estadodocumento esd ON doc.esdid = esd.esdid
			        where doc.tpdid in (303, 336, 335, 328, 344, 345, 443, 444, 442 )
              	)
		         SELECT
		            distinct processo, prpid, dopid, numero_termo, situacao_da_pc as situacao_da_pc_docid, fase_pc_esddsc, fase_pc_esdid, situacao_da_pc_esddsc, situacao_da_pc_esdid,
		            situacao_da_opc_esddsc, situacao_da_opc_esdid, muncod, datainiciovigencia, datafimvigencia
			      FROM(
	                select
		                DISTINCT
		                prp.prpnumeroprocesso as processo,
		                prp.prpid as prpid,
		                dop.dopid as dopid,
		                dop.dopnumerodocumento as numero_termo,
		                dpp.docid as fase_pc,
		                spc.docid  AS situacao_da_pc,
		                opc.docid   AS situacao_da_opc,
		                inu.muncod as muncod,
		                ((date_trunc('MONTH', to_date(dop.dopdatainiciovigencia, 'MM/YYYY'))) :: DATE) as datainiciovigencia, 
		              
						((date_trunc('MONTH', to_date((
                    SELECT
                        to_char(vigencia, 'MM/YYYY') AS vigencia
                    FROM (
                        SELECT
                            to_date(dopdatafimvigencia, 'MM/YYYY') as vigencia,
                            CASE
                                WHEN (dpv.dpvid IS NOT NULL AND d.dopstatus = 'A') THEN 0
                                WHEN (dpv.dpvid IS NULL AND d.dopstatus = 'A' AND d.mdoid IN (69,82,81,41,80,68,42,67,65,76,79,74,44,78,56,62,52,71,66,73,75,77)) THEN 1
                                WHEN (dpv.dpvid IS NULL AND d.dopstatus = 'I' AND d.mdoid IN (69,82,81,41,80,68,42,67,65,76,79,74,44,78,56,62,52,71,66,73,75,77)) THEN 2
                                WHEN (dpv.dpvid IS NOT NULL AND d.dopstatus = 'I') THEN 3
                                ELSE 4
                            END AS prioridade
                        FROM par.documentopar d
                        LEFT JOIN par.documentoparvalidacao dpv ON d.dopid = dpv.dopid AND dpv.dpvstatus = 'A'
                        WHERE d.prpid = prp.prpid AND d.dopstatus <> 'E'
                        ORDER BY d.dopid DESC, prioridade
                ) AS foo LIMIT 1
                ), 'MM/YYYY')) + INTERVAL '1 MONTH - 1 day') :: DATE) as datafimvigencia,	
		                (SELECT esd2.esdid from workflow.estadodocumento esd2 INNER join workflow.documento doc2 ON doc2.esdid = esd2.esdid where doc2.docid = dpp.docid) AS fase_pc_esdid,
		                (SELECT esd2.esdid from workflow.estadodocumento esd2 INNER join workflow.documento doc2 ON doc2.esdid = esd2.esdid where doc2.docid = spc.docid) AS situacao_da_pc_esdid,
		                (SELECT esd2.esdid from workflow.estadodocumento esd2 INNER join workflow.documento doc2 ON doc2.esdid = esd2.esdid where doc2.docid = opc.docid) AS situacao_da_opc_esdid,
		                (SELECT esd2.esddsc from workflow.estadodocumento esd2 INNER join workflow.documento doc2 ON doc2.esdid = esd2.esdid where doc2.docid = dpp.docid) AS fase_pc_esddsc,
		                (SELECT esd2.esddsc from workflow.estadodocumento esd2 INNER join workflow.documento doc2 ON doc2.esdid = esd2.esdid where doc2.docid = spc.docid) AS situacao_da_pc_esddsc,
		                (SELECT esd2.esddsc from workflow.estadodocumento esd2 INNER join workflow.documento doc2 ON doc2.esdid = esd2.esdid where doc2.docid = opc.docid) AS situacao_da_opc_esddsc
	                FROM par.processopar prp
	                INNER JOIN par.documentopar dop ON dop.prpid = prp.prpid AND dopstatus = 'A' and prp.prpstatus = 'A'
	                INNER JOIN par.modelosdocumentos mdo ON mdo.mdoid = dop.mdoid AND mdostatus = 'A' and tpdcod in (16, 21, 102, 103)
	                INNER JOIN par.instrumentounidade inu ON inu.inuid = prp.inuid
	                left JOIN par.situacaoopc opc ON opc.prpid = prp.prpid
	                left JOIN par.situacaoprestacaocontas spc ON spc.prpid = prp.prpid
	                left JOIN par.documentoparprestacaodecontas dpp ON dpp.prpid = prp.prpid
			        ) as sql
			         left join wf_documento_estado situacao_da_opc on sql.situacao_da_opc = situacao_da_opc.docid
			         left join wf_documento_estado situacao_da_pc on sql.situacao_da_pc = situacao_da_pc.docid
			         left join wf_documento_estado doc_est on sql.fase_pc = doc_est.docid
			        where
			          datainiciovigencia is not null and  fase_pc_esdid='2005' and  situacao_da_pc_esdid='2305' and situacao_da_opc_esdid='2298'				
											
      ) LOOP
	   		sem_dtinicio_mandato :=	(SELECT count(*) FROM par.rollresponsaveisprefeitos WHERE muncod = programa.muncod and rrpdtiniciomandato isnull);
	   	    IF sem_dtinicio_mandato < 1 THEN

		   	    contador_responsavel := 0;
		   	    contador_cpf_ciencia := 0;
				dt_limite_envio_pc := (select data_limite_pc from par.data_limite_pc(programa.processo::text));
			
	   	    	FOR cpfs_responsavel IN( select _cpf as cpf, _rrpid as rrpid from par.get_responsaveis_termo(programa.muncod, programa.datainiciovigencia, programa.datafimvigencia, dt_limite_envio_pc, 'PAR') )
				LOOP
						array_responsavel_cpf := array_append(array_responsavel_cpf, cpfs_responsavel.cpf::text);
						contador_responsavel := contador_responsavel + 1;
						contador_ciencia := 0;
						FOR cpfs_ciencia IN ( select  _cpf as cpf, _id as id  from par.get_ciencias_termo(programa.prpid, programa.dopid) )
						LOOP
							
							contador_ciencia := contador_ciencia + 1;
							IF cpfs_responsavel.cpf = cpfs_ciencia.cpf THEN
								array_ciencia_cpf := array_append(array_ciencia_cpf, cpfs_ciencia.cpf::text);
								array_ciencia_ids := array_append(array_ciencia_ids, cpfs_ciencia.id );
							END IF;
							
						END LOOP;
				
				END LOOP;

						array_ciencia_ids := (SELECT array_agg(DISTINCT elem) FROM unnest(array_ciencia_ids) AS elem);
						contador_cpf_ciencia := array_length(array_ciencia_ids, 1);
						verifica_equivalencia := (SELECT bool_and(elem = ANY(array_ciencia_cpf)) FROM unnest(array_responsavel_cpf) AS elem);
	
						IF (contador_responsavel > 0) and ( contador_ciencia > 0 )  and (contador_cpf_ciencia >= contador_responsavel) and (verifica_equivalencia) THEN
					     	IF( select not exists( select * from par.omissodeclarado where prpid in(programa.prpid) and odestatus = 'A' ) ) THEN
							
							BEGIN							
							    BEGIN
							        INSERT INTO par.omissodeclarado (prpid, dopid, programa)
							        VALUES (programa.prpid, programa.dopid, 'PAR')
							        RETURNING par.omissodeclarado.odeid INTO last_insert_odeid;
							    EXCEPTION WHEN others THEN
									erro_carga := true;
							        erro_msg := format(E' \n INSERT INTO par.omissodeclarado (prpid, dopid, programa VALUES (%, %, "PAR")', programa.prpid, programa.dopid);
									RAISE WARNING 'Erro ao inserir em omissodeclarado: % PROCESSO: %', SQLERRM, programa.processo;
							        RETURN;
							    END;
							
						
							    FOR cpfs_responsavel IN ( 
									select  _rrpid as rrpid, _cpf as cpf , _nomeprefeito as nomeprefeito, _dtiniciomandato as dtiniciomandato, _dtfimmandato as dtfimmandato, _naturezainteresse as naturezainteresse 
									from par.get_responsaveis(programa.muncod, programa.datainiciovigencia, programa.datafimvigencia, dt_limite_envio_pc, 'PAR')
							   	)
							    LOOP
							        BEGIN
										raise notice '%, %, %, %, %, %, "PAR" ',programa.muncod, programa.processo, programa.prpid, programa.dopid, programa.datainiciovigencia, programa.datafimvigencia;
										INSERT INTO par.responsaveisomisso(odeid, usucpf, dtiniciomandato, dtfimmandato, naturezainteresse, rrpid)
							            VALUES (last_insert_odeid, cpfs_responsavel.cpf, cpfs_responsavel.dtiniciomandato, cpfs_responsavel.dtfimmandato, cpfs_responsavel.naturezainteresse, cpfs_responsavel.rrpid);
							        EXCEPTION WHEN others THEN
										erro_carga := true;
										erro_msg := erro_msg || format(E' \n INSERT INTO par.responsaveisomisso (odeid, usucpf, dtiniciomandato, dtfimmandato, naturezainteresse, rrpid) VALUES (%s, %s, %s, %s, %s, %s)',last_insert_odeid, cpfs_responsavel.cpf, cpfs_responsavel.dtiniciomandato, cpfs_responsavel.dtfimmandato, cpfs_responsavel.naturezainteresse, cpfs_responsavel.rrpid);
										RAISE WARNING 'Erro ao inserir em responsaveisomisso: % PROCESSO: %', SQLERRM, programa.processo;
									END;
							    END LOOP;
							
							
							    FOR cpfs_ciencia IN (
							        SELECT _id AS id, _cpf AS cpf, _tipo AS tipo 
							        FROM par.get_ciencias_termo(programa.prpid, programa.dopid)
							    )
							    LOOP
							        IF NOT EXISTS(
							            SELECT * 
							            FROM par.cienciasomisso 
							            WHERE opcid = cpfs_ciencia.id OR npid = cpfs_ciencia.id OR negid = cpfs_ciencia.id
							        ) THEN
							            BEGIN
							                IF cpfs_ciencia.tipo = 'omissaopcente' THEN
												INSERT INTO par.cienciasomisso(odeid, usucpf, opcid) 
							                    VALUES (last_insert_odeid, cpfs_ciencia.cpf, cpfs_ciencia.id);
							                ELSIF cpfs_ciencia.tipo = 'notificacaoexgestor' THEN
												
							                    INSERT INTO par.cienciasomisso(odeid, usucpf, negid) 
							                    VALUES (last_insert_odeid, cpfs_ciencia.cpf, cpfs_ciencia.id);
							                ELSE
							                    INSERT INTO par.cienciasomisso(odeid, usucpf, npid) 
							                    VALUES (last_insert_odeid, cpfs_ciencia.cpf, cpfs_ciencia.id);
							                END IF;
							            EXCEPTION WHEN others THEN
											erro_carga := true;
											erro_msg := erro_msg || format(E' \nINSERT INTO par.cienciasomisso(odeid, usucpf, npid) VALUES (%, %, %)', last_insert_odeid, cpfs_ciencia.cpf, cpfs_ciencia.id );
											RAISE WARNING 'Erro ao inserir em cienciasomisso: % PROCESSO: %', SQLERRM, programa.processo;
							            END;
							        END IF;
							    END LOOP;
								
							END;
					
							IF erro_carga THEN
								IF last_insert_odeid > 0 THEN -- Em caso de alguma falha na carga realiza especie de Rollback
									DELETE FROM par.cienciasomisso WHERE odeid = last_insert_odeid; 
									DELETE FROM par.responsaveisomisso where odeid = last_insert_odeid;
									DELETE FROM par.omissodeclarado where odeid = last_insert_odeid; 
								END IF; 
								-- Registra a falha  
								INSERT INTO par.logerros_omissodeclarado_carga (mensagem, numeroprocesso, programa, origem)
								VALUES (erro_msg, programa.processo, 'PAR','par.omisso_declarado_candidato_par');  
							ELSE
								update par.omissodeclarado set andamento='fase-1' where prpid in(programa.prpid);
							END IF;							
						
							erro_carga := false;
							last_insert_odeid := 0;
								
							_processo := programa.processo;
							_prpid := programa.prpid;
							_dopid := programa.dopid;
							_numero_termo := programa.numero_termo;
							_fase_pc_esddsc := programa.fase_pc_esddsc;
							_fase_pc_esdid := programa.fase_pc_esdid;
							_situacao_da_pc_esddsc := programa.situacao_da_pc_esddsc;
							_situacao_da_pc_esdid := programa.situacao_da_pc_esdid;
							_situacao_da_opc_esddsc := programa.situacao_da_opc_esddsc;
							_situacao_da_opc_esdid := programa.situacao_da_opc_esdid;
							_muncod := programa.muncod;
							_datainiciovigencia := programa.datainiciovigencia;
							_datafimvigencia := programa.datafimvigencia;
         					_total_cpf_ciencia := contador_cpf_ciencia;
         					_contador_ciencia := contador_ciencia;
         					_contador_responsavel := contador_responsavel;
          					_aux_cpf_ciencia := array_to_string(array_ciencia_cpf, ', ');  
          					_aux_cpf_responsavel := array_to_string(array_responsavel_cpf, ', '); 
							RETURN NEXT;
						   END IF;           
						END IF;
					array_ciencia_ids := NULL;
					array_responsavel_cpf := NULL;
					array_ciencia_cpf := NULL;
			END IF;

		END LOOP;

END; $function$;