CREATE OR REPLACE VIEW vit.monthly_consumption AS
WITH ipu_consumption AS (
    SELECT
        h.street,
        h.number AS house,
        EXTRACT(YEAR FROM mr.datetimeoffset) AS year,
        EXTRACT(MONTH FROM mr.datetimeoffset) AS month,
        CASE rt.type
            WHEN 'Холодная вода' THEN 0
            WHEN 'Горячая вода' THEN 1
            WHEN 'Электроэнергия' THEN 2
            WHEN 'Природный газ' THEN 3
        END AS resource,
        SUM(NULLIF(ROUND(mr.firsttariffvalue - LAG(mr.firsttariffvalue) OVER (
            PARTITION BY mr.meter_id, EXTRACT(YEAR FROM mr.datetimeoffset), EXTRACT(MONTH FROM mr.datetimeoffset)
            ORDER BY mr.datetimeoffset
        ), 3), 0)) AS im_t1,
        SUM(NULLIF(ROUND(mr.secondtariffvalue - LAG(mr.secondtariffvalue) OVER (
            PARTITION BY mr.meter_id, EXTRACT(YEAR FROM mr.datetimeoffset), EXTRACT(MONTH FROM mr.datetimeoffset)
            ORDER BY mr.datetimeoffset
        ), 3), 0)) AS im_t2,
        SUM(NULLIF(ROUND(mr.thirdtariffvalue - LAG(mr.thirdtariffvalue) OVER (
            PARTITION BY mr.meter_id, EXTRACT(YEAR FROM mr.datetimeoffset), EXTRACT(MONTH FROM mr.datetimeoffset)
            ORDER BY mr.datetimeoffset
        ), 3), 0)) AS im_t3
    FROM vit.meter_read mr
    JOIN vit.meter m ON m.id = mr.meter_id
    JOIN vit.resourcetype rt ON rt.id = m.resourse_id
    JOIN vit.apartments a ON a.id = m.apartments_id
    JOIN vit.house h ON h.id = a.house_id
    WHERE m.apartments_id IS NOT NULL  
      AND mr.firsttariffvalue IS NOT NULL
      AND mr.datetimeoffset <= CURRENT_DATE
    GROUP BY 
        h.street, h.number, 
        EXTRACT(YEAR FROM mr.datetimeoffset), 
        EXTRACT(MONTH FROM mr.datetimeoffset),
        rt.type
),
odpu_consumption AS (
    
    SELECT
        h.street,
        h.number AS house,
        EXTRACT(YEAR FROM mr.datetimeoffset) AS year,
        EXTRACT(MONTH FROM mr.datetimeoffset) AS month,
       
        CASE rt.type
            WHEN 'Холодная вода' THEN 0
            WHEN 'Горячая вода' THEN 1
            WHEN 'Электроэнергия' THEN 2
            WHEN 'Природный газ' THEN 3
        END AS resource,
        SUM(NULLIF(ROUND(mr.firsttariffvalue - LAG(mr.firsttariffvalue) OVER (
            PARTITION BY mr.meter_id, EXTRACT(YEAR FROM mr.datetimeoffset), EXTRACT(MONTH FROM mr.datetimeoffset)
            ORDER BY mr.datetimeoffset
        ), 3), 0)) AS hm_t1,
        SUM(NULLIF(ROUND(mr.secondtariffvalue - LAG(mr.secondtariffvalue) OVER (
            PARTITION BY mr.meter_id, EXTRACT(YEAR FROM mr.datetimeoffset), EXTRACT(MONTH FROM mr.datetimeoffset)
            ORDER BY mr.datetimeoffset
        ), 3), 0)) AS hm_t2,
        SUM(NULLIF(ROUND(mr.thirdtariffvalue - LAG(mr.thirdtariffvalue) OVER (
            PARTITION BY mr.meter_id, EXTRACT(YEAR FROM mr.datetimeoffset), EXTRACT(MONTH FROM mr.datetimeoffset)
            ORDER BY mr.datetimeoffset
        ), 3), 0)) AS hm_t3
    FROM vit.meter_read mr
    JOIN vit.meter m ON m.id = mr.meter_id
    JOIN vit.resourcetype rt ON rt.id = m.resourse_id
    JOIN vit.house h ON h.id = m.house_id
    WHERE m.apartments_id IS NULL  
      AND m.house_id IS NOT NULL
      AND mr.firsttariffvalue IS NOT NULL
      AND mr.datetimeoffset <= CURRENT_DATE  
    GROUP BY 
        h.street, h.number, 
        EXTRACT(YEAR FROM mr.datetimeoffset), 
        EXTRACT(MONTH FROM mr.datetimeoffset),
        rt.type
)
SELECT
    COALESCE(i.street, o.street) AS street,
    COALESCE(i.house, o.house) AS house,
    COALESCE(i.year, o.year) AS year,
    COALESCE(i.month, o.month) AS month,
    COALESCE(i.resource, o.resource) AS resource,
    i.im_t1,
    i.im_t2,
    i.im_t3,
    o.hm_t1,
    o.hm_t2,
    o.hm_t3
FROM ipu_consumption i
FULL OUTER JOIN odpu_consumption o
    ON i.street = o.street
    AND i.house = o.house
    AND i.year = o.year
    AND i.month = o.month
    AND i.resource = o.resource
WHERE COALESCE(i.im_t1, i.im_t2, i.im_t3, o.hm_t1, o.hm_t2, o.hm_t3) IS NOT NULL
ORDER BY 
    street, 
    house, 
    year, 
    month, 
    resource;