-- drop function if exists par.omisso_declarado_candidato_par()

create or replace function par.omisso_declarado_candidato_par()
	returns table (
		_processo varchar,
		_prpid int,
		_dopid int,
		_numero_termo bigint,
		_fase_pc_esddsc varchar,
		_fase_pc_esdid int,
		_situacao_da_pc_esddsc varchar,
		_situacao_da_pc_esdid int,
		_situacao_da_opc_esddsc varchar,
		_situacao_da_opc_esdid int,
		_muncod varchar,
		_datainiciovigencia date,
		_datafimvigencia date,
		_total_cpf_ciencia int,
		_contador_responsavel int,
		_contador_ciencia int,
		_aux_cpf_ciencia text,
		_aux_cpf_responsavel text
	)
	LANGUAGE plpgsql
	AS $$
	DECLARE
		dt_limite_envio_pc date; 
	    total_cpf_ciencia integer default 0;
	    contador_ciencia integer default 0;
	    contador_responsavel integer default 0;
	    last_insert_odeid integer default 0;
	   	aux_cpf_ciencia text;
	    aux_cpf_responsavel text;
	   	sem_dtinicio_mandato integer default 0;
	    programa record;
	    cpfs_ciencia record;
	    cpfs_responsavel record;
	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,
		                to_date('01/'||dop.dopdatainiciovigencia,'DD/MM/YYYY') as datainiciovigencia,
		                to_date('31/'||dop.dopdatafimvigencia,'DD/MM/YYYY') 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

		   		aux_cpf_ciencia := '';
		   	    aux_cpf_responsavel := '';
		   	    contador_responsavel := 0;
		   	    total_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 from par.get_responsaveis_termo_par(programa.muncod, programa.processo, programa.prpid, programa.dopid, programa.datainiciovigencia, programa.datafimvigencia, 'PAR') )
				LOOP
						IF contador_responsavel = 0 THEN
							aux_cpf_responsavel := cpfs_responsavel.cpf;
						ELSE
							aux_cpf_responsavel := concat(aux_cpf_responsavel,',',cpfs_responsavel.cpf);
						END IF;
						contador_responsavel := contador_responsavel + 1;
						contador_ciencia := 0;
						FOR cpfs_ciencia IN ( select distinct _cpf as cpf from par.get_ciencias_termo(programa.prpid, programa.dopid) )
						LOOP
							  IF (contador_ciencia = 0 ) THEN
									aux_cpf_ciencia := cpfs_ciencia.cpf;
							   ELSE
									aux_cpf_ciencia := concat(aux_cpf_ciencia,',',cpfs_ciencia.cpf) ;
								END IF;
								IF cpfs_responsavel.cpf = cpfs_ciencia.cpf THEN
									total_cpf_ciencia := total_cpf_ciencia + 1;
								END IF;
								  contador_ciencia := contador_ciencia + 1;
						END LOOP;
				
				END LOOP;

   	 					IF (contador_responsavel > 0) and ( contador_ciencia > 0 )  and (total_cpf_ciencia  >= contador_responsavel) THEN
					     	IF( select not exists( select * from par.omissodeclarado where prpid in(programa.prpid) ) ) THEN
								insert into par.omissodeclarado(prpid, dopid, programa) values(programa.prpid, programa.dopid, 'PAR') returning par.omissodeclarado.odeid into last_insert_odeid;
								FOR cpfs_responsavel in( select distinct _rrpid as rrpid, _cpf as cpf , _nomeprefeito as nomeprefeito, _dtiniciomandato as dtiniciomandato, _dtfimmandato as dtfimmandato, _naturezainteresse as naturezainteresse 
														 from par.get_responsaveis_termo_par(programa.muncod, programa.processo, programa.prpid, programa.dopid, programa.datainiciovigencia, programa.datafimvigencia, 'PAR') 
														)
								LOOP
									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);
									
								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
											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;
									  END IF;
						  	    END LOOP;

							update par.omissodeclarado set andamento='fase-1' where prpid in(programa.prpid);
							_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 := total_cpf_ciencia;
         					_contador_ciencia := contador_ciencia;
         					_contador_responsavel := contador_responsavel;
          					_aux_cpf_ciencia := aux_cpf_ciencia;
          					_aux_cpf_responsavel := aux_cpf_responsavel;
							RETURN NEXT;
						   END IF;
						END IF;
			END IF;
		END LOOP;
END; $$