with v as (
	select distinct on (v.pkvenda) v.pkvenda
	, v.fkindiceeconomico
	, date_trunc('month', v.data) as datavenda
	from venda v
	where v.pkvenda  = 20311
)
, pag as (

		with t as (
		select DISTINCT date_trunc('month', p.datapagamento) as datapagamento
		, sum(case when p.datapagamento is not null then p.valor + p.valorcomissao + p.valorservicos else 0 end) OVER (PARTITION BY v.pkvenda ORDER BY date_trunc('month', p.datapagamento)) as v
		, sum(case when p.datapagamento is not null then p.valor + p.valorcomissao + p.valorservicos else 0 end) OVER (PARTITION BY v.pkvenda, date_trunc('month', p.datapagamento)) as vm
		from parcelavenda p
		inner join v v on (v.pkvenda = p.fkvenda)
		where p.datapagamento is not null
		)


	, pdata as (
			select DISTINCT date_trunc('month', p.datavencimento) as datavencimento, p.fkvenda
			from parcelavenda p
			inner join v v on (v.pkvenda = p.fkvenda)
			left outer join t t on (t.datapagamento = date_trunc('month', p.datavencimento))
			where t is null
		)

		select distinct date_trunc('month', p.datavencimento) as datapagamento
		, sum(pa.vm) as v
		, 0 as vm
		from pdata p
		inner join v v on (v.pkvenda = p.fkvenda)
		left outer join t pa on (pa.datapagamento <= date_trunc('month', p.datavencimento))
		where true
		GROUP BY date_trunc('month', p.datavencimento)
		union
		select * from t t
)
, p as (
	select
	rank() over (PARTITION by p.fkvenda ORDER BY date_trunc('month', p.datavencimento), p.pkparcelavenda) as c
	, rank() over (PARTITION by p.fkvenda, greatest(0, trunc(cast(((DATE_PART('year', date_trunc('month', p.datavencimento) ) - DATE_PART('year', v.datavenda)) * 12 +
		((DATE_PART('month', date_trunc('month', p.datavencimento) ) - DATE_PART('month', v.datavenda)) )) / 12  as numeric),0) ) ORDER BY date_trunc('month', p.datavencimento), p.pkparcelavenda) as ca
	, rank() over (PARTITION by p.fkvenda, date_trunc('month', p.datavencimento) ORDER BY p.pkparcelavenda) as cm
	, sum(p.valor + p.valorservicos + p.valorcomissao) over (partition by p.fkvenda) as valorcontrato
	, pag.v as totalpago
	, pag.vm as totalpagomes
	, date_trunc('month', p.datavencimento) as datavencimento
	, p.pkparcelavenda
	, p.valor
	, p.status
	, date_trunc('month', p.datapagamento) as datapagamento
	, p.fkvenda
	, (p.valor + p.valorservicos + p.valorcomissao) as v
	, v.fkindiceeconomico
	, v.datavenda
	, greatest(0, trunc(cast(((DATE_PART('year', date_trunc('month', p.datavencimento) ) - DATE_PART('year', v.datavenda)) * 12 +
		((DATE_PART('month', date_trunc('month', p.datavencimento) ) - DATE_PART('month', v.datavenda)) )) / 12  as numeric),0) )  as anos
  , cast(COALESCE(ie.valor, 0) as numeric) as ieporcentagem

	from parcelavenda p
	inner join v v on (v.pkvenda = p.fkvenda)
  left outer join lancamentoindiceeconomico ie on (ie.fkindiceeconomico = v.fkindiceeconomico and ie.status = 0 and date_trunc('month', ie.data) = date_trunc('month', p.datavencimento - interval '1' month ))
  left outer join pag pag on (pag.datapagamento = date_trunc('month', p.datavencimento))


)


, f as(

	WITH RECURSIVE l AS (

			select
			p.c as ordem
			, p.datavencimento as datavencimento
			, p.valorcontrato as saldoinicial
			, cast(0 as numeric) as fator
			, p.valorcontrato + (p.valorcontrato * 0) as saldocorrigido
			, p.totalpagomes as abatimento
			, (p.valorcontrato + (p.valorcontrato * 0)) - p.totalpagomes as saldofinal
			, '|                                         |' as "-"
			, p.v / p.valorcontrato as parcelaporcentagem
			, cast(1 as DOUBLE PRECISION) as saldoDevedorAno
			, cast(1 as DOUBLE PRECISION)  as valorParcelaFaltandoAno
			, p.v as valorParcelaBase
			, cast(0 as DOUBLE PRECISION) as correcao
			, p.v as valorParcela
			, '|                                         |' as "-2"
			, p.*
			from p p
			where p.c = 1

		 UNION ALL
				select
			p.c as ordem
			, p.datavencimento as datavencimento
			, pa.saldofinal as saldoinicial
			, p.ieporcentagem as fator
			, pa.saldofinal + + case when p.cm = 1 then (pa.saldofinal * p.ieporcentagem / 100) else 0 end as saldocorrigido
			, p.totalpagomes as abatimento
			, pa.saldofinal + case when p.cm = 1 then (pa.saldofinal * p.ieporcentagem / 100) - p.totalpagomes else 0 end as saldofinal
			, '|                                         |' as "-"
			, p.v / p.valorcontrato as parcelaporcentagem
			, (CASE WHEN p.ca = 1 THEN pa.saldofinal else pa.saldoDevedorAno end) as saldoDevedorAno
			, (CASE WHEN p.ca = 1 THEN p.valorcontrato - pa.totalpago else pa.valorParcelaFaltandoAno end) as valorParcelaFaltandoAno
			, p.v as valorParcelaBase
			, (CASE WHEN p.ca = 1 THEN pa.saldofinal * (p.v / (p.valorcontrato - pa.totalpago)) - p.v else pa.saldoDevedorAno * (p.v / (pa.valorParcelaFaltandoAno)) - p.v end) as correcao
			, (CASE WHEN p.ca = 1 THEN pa.saldofinal * (p.v / (p.valorcontrato - pa.totalpago)) else pa.saldoDevedorAno * (p.v / (pa.valorParcelaFaltandoAno)) end) as valorParcela
			, '|                                         |' as "-2"
			, p.*
			from p p
			INNER JOIN l pa ON (p.c = pa.c + 1)

			)
			SELECT
		 *
	FROM l

	 )

	 select *
	 from f