SELECT *
FROM agreement_totals( tstzrange( '2020-07-01', '2020-08-01' ) )
-- PAY ATTENTION TO THIS 
WHERE agreement_id = 161::int  AND  (o).period_id = 10::int



WITH gconf AS
  -- https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-SELECT
  -- NOT MATERIALIZED -- force it to be merged into the parent query
  -- it gives a net savings because each usage of the WITH query needs only a small part of the WITH query''s full output.
( SELECT
  ocd.*,

  (ic).consumed_period  AS consumed_period,
  dense_rank()  OVER ( PARTITION BY agreement_id, order_id ORDER BY (ic).consumed_period        )  AS nconf,
  row_number()  OVER ( wconf                               ORDER BY (c).sort_order NULLS LAST   )  AS nitem,

  -- Summarize each item of configuration and get configuration cost
  (sum( ocd.item_cost     )  OVER wconf)::numeric( 10, 2 )  AS conf_cost,
  -- For resource `consumed` is not calculated, because there is not period_id for it
  -- For ServiceType there is Price which has period_id, but it depends on Order.period_id
  -- TODO: Calculate consumption depending on Order.period_id
  (max( (ocd.ic).consumed )  OVER wconf)                    AS consumed,
  -- NOTICE: Here we MUST select cost because invoice does not know how much
  -- service will be consumed. If we are regenerate past Invoice it MUST NOT
  -- take into account changes, like it is just builded

  -- Summarize each item of configuration to get configuration suma
  CASE WHEN false
    THEN (sum( ocd.item_cost )  OVER wconf)::numeric( 10, 2 )
    ELSE (sum( ocd.item_suma )  OVER wconf)::numeric( 10, 2 )
  END AS conf_suma
FROM order_cost_details( tstzrange( '2020-07-01', '2020-08-01' ) ) ocd

-- Each agreement could have few different orders
-- Each order could have changes within consumed_period
-- So we partition by these three factors:
WINDOW wconf AS ( PARTITION BY agreement_id, order_id, (ic).consumed_period )
),
gorder AS (
  SELECT *,
    (conf_suma/6)::numeric( 10, 2 )                        AS conf_nds,
    -- Summarize configuration suma for each consumed_period to get order suma
    sum( conf_suma ) FILTER (WHERE nitem = 1) OVER worder  AS order_suma
  FROM gconf
  WINDOW worder AS ( PARTITION BY agreement_id, order_id )
)
SELECT
  agreement_id, (o).period_id, order_id, consumed_period, nconf, nitem,
  (c).id                                                AS item_id,
  COALESCE( (c).sort_order,   pd.sort_order          )  AS item_order,
  COALESCE( st.display, st.name, rt.display, rt.name )  AS item_name,
  COALESCE( item_qty,         (c).amount/rt.unit     )  AS item_qty,
  COALESCE( (p).label,        rt.label               )  AS measure,
  item_price, item_cost, item_suma,
  conf_cost, consumed, conf_suma, conf_nds, order_suma,
  (order_suma/6)::numeric( 10, 2 )                      AS order_nds,
  -- Summarize configuration suma for each consumed_period and order to get agreement suma
  sum( conf_suma                        ) FILTER (WHERE nitem = 1                ) OVER wagreement  AS total_suma,
  sum( (order_suma/6)::numeric( 10, 2 ) ) FILTER (WHERE nitem = 1  AND  nconf = 1) OVER wagreement  AS total_nds,

  pkg.id                            AS package_id,
  pkg.link_1c_id                    AS package_1c_id,
  COALESCE( pkg.display, pkg.name ) AS package,

  o, c, p, ic
FROM gorder u
LEFT JOIN resource_type rt ON rt.id  = (c).resource_type_id
LEFT JOIN service_type  st ON st.id  = (c).service_type_id
LEFT JOIN package      pkg ON pkg.id = (o).package_id
LEFT JOIN package_detail     pd  ON pd.package_id = (o).package_id
  AND pd.resource_type_id IS NOT DISTINCT FROM (c).resource_type_id
  AND pd.service_type_id  IS NOT DISTINCT FROM (c).service_type_id

WHERE agreement_id = 161 AND (o).period_id = 10  -- PAY ATTENTION TO THIS

WINDOW wagreement AS ( PARTITION BY agreement_id )