select
user_id,
COALESCE(pt_inn,'') pt_inn,
COALESCE(pt_kpp,'') pt_kpp,
case when (sum (COALESCE(amount,0)) - sum(COALESCE(sum_out,0))) <0 then 0 else (sum (COALESCE(amount,0)) - sum(COALESCE(sum_out,0)))  end balance
		from	(with start_balance
			as (
			    select
			            user_id,
			            payment_timestamp start_date,
			            COALESCE(lead(payment_timestamp,1) over (partition by user_id,(json_array_elements(processed_transactions) -> 'gatewayInfo' -> 'gatewayProperties' ->> 'inn') order by payment_timestamp),date '4000-01-01') end_date,
			            json_array_elements(processed_transactions)->'gatewayInfo'->>'gatewayId'  pt_gatewayId,
			            json_array_elements(processed_transactions) -> 'gatewayInfo' -> 'gatewayProperties' ->> 'inn' pt_inn,
			            json_array_elements(processed_transactions) -> 'gatewayInfo' -> 'gatewayProperties' ->> 'kpp' pt_kpp,
			            sum(amount) over(partition by user_id,(json_array_elements(processed_transactions) -> 'gatewayInfo' -> 'gatewayProperties' ->> 'inn') order by payment_timestamp) sum_run,
			            amount
			    from payments
			        where subsystem_id='PromoCode'
			          --  and payment_status in ('Succeeded','Unknown')
			            and transaction_type='Credit'
			            and tenant_id='Evotor'
							and PAYMENT_ID IN (select code from evo_codes )
			    ),
			sum_payments
			as (
			    select
			        start_balance.start_date,
			        start_balance.end_date,
			        start_balance.user_id,
			        payments.payment_timestamp,
			        json_array_elements(payments.processed_transactions)->'gatewayInfo'->>'gatewayId'  pt_gatewayId,
			        json_array_elements(payments.processed_transactions) -> 'gatewayInfo' -> 'gatewayProperties' ->> 'inn' pt_inn,
			        json_array_elements(payments.processed_transactions) -> 'gatewayInfo' -> 'gatewayProperties' ->> 'kpp' pt_kpp,
			        cast(json_array_elements(payments.processed_transactions)->>'processedPaymentAmount' as decimal(20,2))  pt_amount
			       -- sum(payments.amount) amount
			    from    payments
			   left join start_balance
			        on start_balance.user_id = payments.user_id
			            and start_balance.start_date <= payments.payment_timestamp
			            and start_balance.end_date > payments.payment_timestamp
			    where  start_balance.user_id = payments.user_id
						    AND payments.tenant_id = 'Evotor'
						    AND payments.transaction_type = 'Charge'
						    AND payments.payment_status NOT IN  ('Failed','Cancelled')
						    AND payments.subsystem_id NOT IN  ('promo1million')
			    group by start_balance.start_date,
			            start_balance.end_date,
			            start_balance.user_id,
							json_array_elements(payments.processed_transactions)->'gatewayInfo'->>'gatewayId',
							json_array_elements(payments.processed_transactions) -> 'gatewayInfo' -> 'gatewayProperties' ->> 'inn',
							json_array_elements(payments.processed_transactions) -> 'gatewayInfo' -> 'gatewayProperties' ->> 'kpp',
							cast(json_array_elements(payments.processed_transactions)->>'processedPaymentAmount' as decimal(20,2)),
							payments.payment_timestamp
			    )
			select
				start_balance.user_id,
				start_balance.start_date,
				start_balance.end_date,
				start_balance.pt_inn,
				start_balance.pt_kpp,
				--start_balance.sum_run,
				start_balance.amount,
				sum(sum_payments.pt_amount) sum_out
					from start_balance
				left join sum_payments on  start_balance.user_id = sum_payments.user_id
												and start_balance.start_date =  sum_payments.start_date
												and start_balance.end_date = sum_payments.end_date
												and sum_payments.pt_gatewayId = 'BONUS_ACCOUNT'
												and COALESCE(start_balance.pt_inn,'') = COALESCE(sum_payments.pt_inn,'')
												and COALESCE(start_balance.pt_kpp,'') = COALESCE(sum_payments.pt_kpp,'')
				group by
							start_balance.user_id,
							start_balance.start_date,
							start_balance.end_date,
							start_balance.pt_inn,
							start_balance.pt_kpp,
							start_balance.sum_run,
							start_balance.amount) as temp
group by
			user_id,
			COALESCE(pt_inn,''),
			COALESCE(pt_kpp,'')