Select * from vit.individual_consumption_by_resource



CREATE OR REPLACE VIEW vit.individual_consumption_by_resource AS
WITH meter_readings AS (
    SELECT 
        mr.id,
        mr.meter_id,
        mr.datetimeoffset AS reading_date,
        mr.firsttariffvalue AS t1_value,
        mr.secondtariffvalue AS t2_value,
        mr.thirdtariffvalue AS t3_value,
        m.resourse_id,
        rt.type AS resource_type,
        h.street,
        h.number AS house,
        CASE rt.type
            WHEN 'Холодная вода' THEN 0
            WHEN 'Горячая вода' THEN 1
            WHEN 'Электроэнергия' THEN 2
            WHEN 'Природный газ' THEN 3
        END AS resource
    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
),
daily_consumption AS (
    SELECT
        street,
        house,
        resource,
        reading_date AS date,
        NULLIF(ROUND(t1_value - LAG(t1_value) OVER (
            PARTITION BY meter_id 
            ORDER BY reading_date
        ), 3), 0) AS t1,
        CASE 
            WHEN m.issecondtariffinuse THEN NULLIF(ROUND(t2_value - LAG(t2_value) OVER (
                PARTITION BY meter_id 
                ORDER BY reading_date
            ), 3), 0) 
        END AS t2,
        CASE 
            WHEN m.isthirdtariffinuse THEN NULLIF(ROUND(t3_value - LAG(t3_value) OVER (
                PARTITION BY meter_id 
                ORDER BY reading_date
            ), 3), 0) 
        END AS t3
    FROM meter_readings mr
    JOIN vit.meter m ON m.id = mr.meter_id
)

SELECT
    street,
    house,
    resource,
    date,
    SUM(t1) AS t1,
    SUM(t2) AS t2,  
    SUM(t3) AS t3   
FROM daily_consumption
WHERE date IS NOT NULL
  AND (t1 IS NOT NULL OR t2 IS NOT NULL OR t3 IS NOT NULL)
GROUP BY street, house, resource, date
HAVING COALESCE(SUM(t1), 0) >= 0 
   AND COALESCE(SUM(t2), 0) >= 0 
   AND COALESCE(SUM(t3), 0) >= 0
ORDER BY 
    street, 
    house, 
    resource, 
    date;