WITH
dates_m AS ( -- Формирует диапазон дат
	SELECT unnest(fill_left_schedule(:from::date, '2019-07-01'::date, '30 days'::interval)) as date
),
dates_y AS ( -- Формирует диапазон дат
	SELECT unnest(fill_left_schedule(:from::date, '2019-07-01'::date, '365 days'::interval)) as date
),
initials AS ( -- инициирующие платежей
	SELECT
		id AS initial_invoice_id,
        payment_at AS initial_invoice_date,
		agent AS initial_invoice_agent,
		user_id AS user_id,
		"sum" AS initial_sum,
        price_id
	FROM orders
	WHERE agent = 'yandex_kassa'
	  AND status = 3
	  AND payment_at::date < :from::date
	  AND payment_at::date >= '2019-07-15'
	  AND id in (select order_id from recurring_operations)

    ORDER BY payment_at
),
all_recurring_operations_nested AS (
	SELECT *, fill_schedule(last_operation::date, :till::date, ("interval" || ' days')::interval) AS schedule FROM recurring_operations WHERE status = 1
	UNION
	SELECT *, array[last_operation]::date[] AS schedule FROM recurring_operations WHERE status != 1
),
all_recurring_operations AS (
	SELECT *, unnest(schedule) AS last_operation_s FROM all_recurring_operations_nested
),
recurring_operations_data AS ( -- список операций
	SELECT
		i.*,
		o.last_operation_s AS last_recurring_original,
		o.interval AS recurring_interval,
		o.sum AS recurring_sum,
		o.status AS recurring_status,
		o.created_at AS recurring_created_at,
		CASE
			WHEN i.initial_invoice_date > o.last_operation
				THEN i.initial_invoice_date
			ELSE o.last_operation
			END AS last_recurring_activation,
		o.operation_id AS recurring_payment_id,
       i.price_id
	FROM initials i, all_recurring_operations o
	WHERE o.user_id = i.user_id
),
time_write_off AS ( --
	SELECT
    distinct on (user_id) user_id as a_id,
    *
	FROM recurring_operations_data
    WHERE (last_recurring_original >= :from
        AND last_recurring_original < :till) or
        (last_recurring_original in (select date from dates_m)
            and recurring_interval in(29,30,31)
        ) or
        (last_recurring_original in (select date from dates_y)
            and recurring_interval in (365, 366)
        )
),
data AS (
	SELECT
		d.*,
		(
			SELECT email
			FROM public.users
			WHERE id = d.user_id
		) AS email,
		recurring_sum AS price,
		null AS unsubscribe_date,
		(
			SELECT payment_at::date as date
			FROM orders
			WHERE agent = 'yandex_kassa'
			  AND payment_at::date > d.initial_invoice_date::date
			  AND status = 3
			  AND user_id = d.user_id
			ORDER BY payment_at desc
			LIMIT 1
		) AS last_recurring_payment,
		(
			SELECT payment_at::date as date
			FROM orders
			WHERE status = 1
			  AND user_id = d.user_id
			  AND (client_data::json->>'recurring')::boolean is not null
			  AND (client_data::json->>'recurring')::boolean = true
			ORDER BY payment_at desc
			LIMIT 1
		) AS last_failed_recurring_payment,
		initial_invoice_date::date AS last_initial_payment,
		false AS can_new_subscribe,
		(last_recurring_activation::date > initial_invoice_date::date) AS has_recurring_payed
	FROM time_write_off d
)
SELECT *, (
	coalesce(last_recurring_payment, last_failed_recurring_payment) IN (SELECT date FROM dates_m)
) = false AS shift_payment
FROM data;