Lars Nolden
413 lines
28 KiB
PL/PgSQL
413 lines
28 KiB
PL/PgSQL
-- ============================================================================
|
||
-- GebOS – Dev/test fixture data (supabase/seed.sql)
|
||
--
|
||
-- DEV ONLY. Never apply to prod: it contains fake tenants and fake devices,
|
||
-- and the regulatory reference numbers (efficiency class thresholds, CO2
|
||
-- factors, prices, HDD) are plausible APPROXIMATIONS for development, not
|
||
-- verified values from the legal sources. Real reference data ships as its
|
||
-- own migration when the numbers have been verified.
|
||
--
|
||
-- Apply with psql against the local process-compose stack (see
|
||
-- supabase/README.md), AFTER the schema migration:
|
||
--
|
||
-- psql -h 127.0.0.1 -p 5432 -d gebos -f supabase/seed.sql
|
||
--
|
||
-- Idempotent by construction: fixture rows carry fixed UUIDs (so tests can
|
||
-- reference them) and every INSERT ends in ON CONFLICT DO NOTHING. The
|
||
-- measurement series is anchored at a fixed start date (2025-06-01) and
|
||
-- generated deterministically (md5-based jitter, no random()), so re-running
|
||
-- on a later day recomputes identical values for existing days (skipped via
|
||
-- the composite PK) and appends only the new days — cumulative meter totals
|
||
-- stay monotonic across re-runs.
|
||
--
|
||
-- UUID namespaces (first group encodes the table, last digits the row):
|
||
-- dd... weather_station b0... building a0... apartment
|
||
-- e0... app_user 0c... occupancy 9e... gateway
|
||
-- d1... device_type f0... value_parser 5e... sensor
|
||
-- 7a... received_payload 5a... saving_tip
|
||
--
|
||
-- Scenario coverage (what each fixture exists to exercise):
|
||
-- * Building 1 (Berlin, gas, 5 apartments) — normal in-house spectrum.
|
||
-- * Building 2 (Potsdam, district heating, 3 apartments) — small-building
|
||
-- anonymity rule (UVI-FUN-09), second fuel type, and NO weather station
|
||
-- (weather footnote must render "not available", UVI-FUN-15).
|
||
-- * Apartment 3 — Mieterwechsel on 2026-03-01: comparisons across that
|
||
-- date must be suppressed (both months must fall in ONE occupancy row).
|
||
-- * Apartment 5 — vacant since 2026-05-01 (occupancy ended, none current).
|
||
-- * Apartment 4 — real heat meter (energy + flow/return temperatures +
|
||
-- error flags), not a heat cost allocator.
|
||
-- * Apartment 1 — hot water metered as ENERGY; all others as VOLUME
|
||
-- (both measurement kinds must work, see measurement_kind comment).
|
||
-- * Sensor 5e..17 — provisioned but unassigned (apartment_id NULL) with
|
||
-- measurements: data stored, tenant-invisible until assigned.
|
||
-- * received_payload — one row per parse_status, incl. an unknown sender.
|
||
-- * Consumption profiles span thrifty (~45 kWh/m²a) to heavy (~190),
|
||
-- so the Bandtacho classes and the in-house comparison spread out.
|
||
--
|
||
-- app_user note: in prod, app_user.id must equal auth.users.id (GoTrue).
|
||
-- The dev stack has no GoTrue yet, so these users have no auth identity —
|
||
-- when it lands, create auth users with these fixed UUIDs to log in as them.
|
||
-- ============================================================================
|
||
|
||
BEGIN;
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- Reference: weather station (assigned to building 1 only — building 2
|
||
-- deliberately has none)
|
||
-- ---------------------------------------------------------------------------
|
||
|
||
INSERT INTO weather_station (id, dwd_id, name, lat, lng) VALUES
|
||
('dd000000-0000-0000-0000-000000000001', '00433', 'Berlin-Tempelhof', 52.467500, 13.402100)
|
||
ON CONFLICT DO NOTHING;
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- Buildings & apartments
|
||
-- ---------------------------------------------------------------------------
|
||
|
||
INSERT INTO building (id, street, house_number, postal_code, city, lat, lng,
|
||
fuel_type, weather_station_id, note) VALUES
|
||
('b0000000-0000-0000-0000-000000000001',
|
||
'Fichtestraße', '12', '10967', 'Berlin', 52.489200, 13.417100,
|
||
'natural_gas', 'dd000000-0000-0000-0000-000000000001',
|
||
'Schlüsselkasten am Hoftor, Code 4711. Heizungskeller: Zugang über Hof.'),
|
||
('b0000000-0000-0000-0000-000000000002',
|
||
'Gartenweg', '3', '14482', 'Potsdam', 52.390800, 13.115600,
|
||
'district_heating', NULL,
|
||
'Kleine Anlage, 3 WE. Übergabestation im Keller rechts.')
|
||
ON CONFLICT DO NOTHING;
|
||
|
||
INSERT INTO apartment (id, building_id, label, living_area_m2) VALUES
|
||
('a0000000-0000-0000-0000-000000000001', 'b0000000-0000-0000-0000-000000000001', 'EG links', 54.00),
|
||
('a0000000-0000-0000-0000-000000000002', 'b0000000-0000-0000-0000-000000000001', 'EG rechts', 72.00),
|
||
('a0000000-0000-0000-0000-000000000003', 'b0000000-0000-0000-0000-000000000001', '1. OG links', 85.00),
|
||
('a0000000-0000-0000-0000-000000000004', 'b0000000-0000-0000-0000-000000000001', '1. OG rechts', 96.00),
|
||
('a0000000-0000-0000-0000-000000000005', 'b0000000-0000-0000-0000-000000000001', '2. OG', 110.00),
|
||
('a0000000-0000-0000-0000-000000000006', 'b0000000-0000-0000-0000-000000000002', 'WE 01', 60.00),
|
||
('a0000000-0000-0000-0000-000000000007', 'b0000000-0000-0000-0000-000000000002', 'WE 02', 75.00),
|
||
('a0000000-0000-0000-0000-000000000008', 'b0000000-0000-0000-0000-000000000002', 'WE 03', 88.00)
|
||
ON CONFLICT DO NOTHING;
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- Users & occupancies
|
||
-- ---------------------------------------------------------------------------
|
||
|
||
INSERT INTO app_user (id, role, display_name) VALUES
|
||
('e0000000-0000-0000-0000-000000000001', 'admin', 'Anna Admin'),
|
||
('e0000000-0000-0000-0000-000000000002', 'tenant', 'Max Mustermann'),
|
||
('e0000000-0000-0000-0000-000000000003', 'tenant', 'Erika Beispiel'),
|
||
('e0000000-0000-0000-0000-000000000004', 'tenant', 'Otto Vormieter'),
|
||
('e0000000-0000-0000-0000-000000000005', 'tenant', 'Nina Neumieterin'),
|
||
('e0000000-0000-0000-0000-000000000006', 'tenant', 'Karl Konstant'),
|
||
('e0000000-0000-0000-0000-000000000007', 'tenant', 'Ferdinand Fortgezogen'),
|
||
('e0000000-0000-0000-0000-000000000008', 'tenant', 'Gerda Gartenweg'),
|
||
('e0000000-0000-0000-0000-000000000009', 'tenant', 'Hans Hofmann'),
|
||
('e0000000-0000-0000-0000-00000000000a', 'tenant', 'Ines Iser')
|
||
ON CONFLICT DO NOTHING;
|
||
|
||
INSERT INTO occupancy (id, apartment_id, user_id, valid_from, valid_to) VALUES
|
||
-- building 1
|
||
('0c000000-0000-0000-0000-000000000001', 'a0000000-0000-0000-0000-000000000001', 'e0000000-0000-0000-0000-000000000002', '2023-09-01', NULL),
|
||
('0c000000-0000-0000-0000-000000000002', 'a0000000-0000-0000-0000-000000000002', 'e0000000-0000-0000-0000-000000000003', '2024-01-01', NULL),
|
||
-- Mieterwechsel in apartment 3: comparisons across 2026-03-01 are forbidden
|
||
('0c000000-0000-0000-0000-000000000003', 'a0000000-0000-0000-0000-000000000003', 'e0000000-0000-0000-0000-000000000004', '2022-05-01', '2026-03-01'),
|
||
('0c000000-0000-0000-0000-000000000004', 'a0000000-0000-0000-0000-000000000003', 'e0000000-0000-0000-0000-000000000005', '2026-03-01', NULL),
|
||
('0c000000-0000-0000-0000-000000000005', 'a0000000-0000-0000-0000-000000000004', 'e0000000-0000-0000-0000-000000000006', '2021-11-01', NULL),
|
||
-- apartment 5 vacant since 2026-05-01
|
||
('0c000000-0000-0000-0000-000000000006', 'a0000000-0000-0000-0000-000000000005', 'e0000000-0000-0000-0000-000000000007', '2023-02-01', '2026-05-01'),
|
||
-- building 2
|
||
('0c000000-0000-0000-0000-000000000007', 'a0000000-0000-0000-0000-000000000006', 'e0000000-0000-0000-0000-000000000008', '2020-01-01', NULL),
|
||
('0c000000-0000-0000-0000-000000000008', 'a0000000-0000-0000-0000-000000000007', 'e0000000-0000-0000-0000-000000000009', '2024-07-01', NULL),
|
||
('0c000000-0000-0000-0000-000000000009', 'a0000000-0000-0000-0000-000000000008', 'e0000000-0000-0000-0000-00000000000a', '2023-03-15', NULL)
|
||
ON CONFLICT DO NOTHING;
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- Gateways (two in building 1: broadcast radio means overlapping reception)
|
||
-- ---------------------------------------------------------------------------
|
||
|
||
INSERT INTO gateway (id, imei, imsi, building_id, label, installation_note) VALUES
|
||
('9e000000-0000-0000-0000-000000000001', '861234050000011', '901405000000011',
|
||
'b0000000-0000-0000-0000-000000000001', 'GW Keller',
|
||
'Im Heizungskeller, über der Tür zum Anschlussraum.'),
|
||
('9e000000-0000-0000-0000-000000000002', '861234050000012', '901405000000012',
|
||
'b0000000-0000-0000-0000-000000000001', 'GW Dach',
|
||
'Trockenboden, am Kaminschacht montiert.'),
|
||
('9e000000-0000-0000-0000-000000000003', '861234050000013', '901405000000013',
|
||
'b0000000-0000-0000-0000-000000000002', 'GW Keller',
|
||
'Neben der Fernwärme-Übergabestation.')
|
||
ON CONFLICT DO NOTHING;
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- Device types & value parsers
|
||
-- ---------------------------------------------------------------------------
|
||
|
||
INSERT INTO device_type (id, manufacturer, model, medium) VALUES
|
||
('d1000000-0000-0000-0000-000000000001', 'QDS', 'Q caloric 5.5', '08'), -- heat cost allocator
|
||
('d1000000-0000-0000-0000-000000000002', 'EFE', 'SensoStar U', '04'), -- heat meter
|
||
('d1000000-0000-0000-0000-000000000003', 'EFE', 'SensoStar E', '06'), -- warm-water heat meter
|
||
('d1000000-0000-0000-0000-000000000004', 'ZRI', 'Minomess', '06') -- warm-water volume meter
|
||
ON CONFLICT DO NOTHING;
|
||
|
||
INSERT INTO value_parser (id, device_type_id, json_path, parser_instructions, kind, unit) VALUES
|
||
('f0000000-0000-0000-0000-000000000001', 'd1000000-0000-0000-0000-000000000001',
|
||
'$.records[0].value', '{"scale": 1}', 'heating_energy', 'kWh'),
|
||
('f0000000-0000-0000-0000-000000000002', 'd1000000-0000-0000-0000-000000000002',
|
||
'$.records[?(@.type=="energy")].value', '{"scale": 1}', 'heating_energy', 'kWh'),
|
||
('f0000000-0000-0000-0000-000000000003', 'd1000000-0000-0000-0000-000000000002',
|
||
'$.records[?(@.type=="flow_temp")].value', '{"scale": 1}', 'flow_temperature', '°C'),
|
||
('f0000000-0000-0000-0000-000000000004', 'd1000000-0000-0000-0000-000000000002',
|
||
'$.records[?(@.type=="return_temp")].value', '{"scale": 1}', 'return_temperature', '°C'),
|
||
('f0000000-0000-0000-0000-000000000005', 'd1000000-0000-0000-0000-000000000002',
|
||
'$.status', NULL, 'error_flags', 'flags'),
|
||
('f0000000-0000-0000-0000-000000000006', 'd1000000-0000-0000-0000-000000000003',
|
||
'$.records[?(@.type=="energy")].value', '{"scale": 1}', 'hot_water_energy', 'kWh'),
|
||
('f0000000-0000-0000-0000-000000000007', 'd1000000-0000-0000-0000-000000000004',
|
||
'$.records[0].value', '{"scale": 0.001}', 'hot_water_volume', 'm3')
|
||
ON CONFLICT DO NOTHING;
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- Sensors
|
||
-- 01–08: heating, apartments 1–8 (apartment 4 has the real heat meter)
|
||
-- 09–16: hot water, apartments 1–8 (apartment 1 energy, the rest volume)
|
||
-- 17: provisioned but unassigned
|
||
-- ---------------------------------------------------------------------------
|
||
|
||
INSERT INTO sensor (id, oms_id, device_type_id, apartment_id, decrypt_key, installed_at, installation_note) VALUES
|
||
('5e000000-0000-0000-0000-000000000001', '71000001', 'd1000000-0000-0000-0000-000000000001', 'a0000000-0000-0000-0000-000000000001', '000102030405060708090a0b0c0d0e01', '2025-04-14', 'HKV Wohnzimmer, einziger Heizkörper.'),
|
||
('5e000000-0000-0000-0000-000000000002', '71000002', 'd1000000-0000-0000-0000-000000000001', 'a0000000-0000-0000-0000-000000000002', '000102030405060708090a0b0c0d0e02', '2025-04-14', 'HKV Wohnzimmer.'),
|
||
('5e000000-0000-0000-0000-000000000003', '71000003', 'd1000000-0000-0000-0000-000000000001', 'a0000000-0000-0000-0000-000000000003', '000102030405060708090a0b0c0d0e03', '2025-04-14', 'HKV Schlafzimmer.'),
|
||
('5e000000-0000-0000-0000-000000000004', '71000004', 'd1000000-0000-0000-0000-000000000002', 'a0000000-0000-0000-0000-000000000004', '000102030405060708090a0b0c0d0e04', '2025-04-15', 'Wärmemengenzähler im Flurschacht.'),
|
||
('5e000000-0000-0000-0000-000000000005', '71000005', 'd1000000-0000-0000-0000-000000000001', 'a0000000-0000-0000-0000-000000000005', '000102030405060708090a0b0c0d0e05', '2025-04-15', 'HKV Küche.'),
|
||
('5e000000-0000-0000-0000-000000000006', '71000006', 'd1000000-0000-0000-0000-000000000001', 'a0000000-0000-0000-0000-000000000006', '000102030405060708090a0b0c0d0e06', '2025-05-06', 'HKV Wohnzimmer.'),
|
||
('5e000000-0000-0000-0000-000000000007', '71000007', 'd1000000-0000-0000-0000-000000000001', 'a0000000-0000-0000-0000-000000000007', '000102030405060708090a0b0c0d0e07', '2025-05-06', 'HKV Wohnzimmer.'),
|
||
('5e000000-0000-0000-0000-000000000008', '71000008', 'd1000000-0000-0000-0000-000000000001', 'a0000000-0000-0000-0000-000000000008', '000102030405060708090a0b0c0d0e08', '2025-05-06', 'HKV Bad.'),
|
||
('5e000000-0000-0000-0000-000000000009', '71000009', 'd1000000-0000-0000-0000-000000000003', 'a0000000-0000-0000-0000-000000000001', '000102030405060708090a0b0c0d0e09', '2025-04-14', 'WW-Wärmezähler unter der Spüle.'),
|
||
('5e000000-0000-0000-0000-000000000010', '71000010', 'd1000000-0000-0000-0000-000000000004', 'a0000000-0000-0000-0000-000000000002', '000102030405060708090a0b0c0d0e10', '2025-04-14', 'WW-Zähler Bad, Vorwandinstallation.'),
|
||
('5e000000-0000-0000-0000-000000000011', '71000011', 'd1000000-0000-0000-0000-000000000004', 'a0000000-0000-0000-0000-000000000003', '000102030405060708090a0b0c0d0e11', '2025-04-14', 'WW-Zähler Bad.'),
|
||
('5e000000-0000-0000-0000-000000000012', '71000012', 'd1000000-0000-0000-0000-000000000004', 'a0000000-0000-0000-0000-000000000004', '000102030405060708090a0b0c0d0e12', '2025-04-15', 'WW-Zähler Küche.'),
|
||
('5e000000-0000-0000-0000-000000000013', '71000013', 'd1000000-0000-0000-0000-000000000004', 'a0000000-0000-0000-0000-000000000005', '000102030405060708090a0b0c0d0e13', '2025-04-15', 'WW-Zähler Bad.'),
|
||
('5e000000-0000-0000-0000-000000000014', '71000014', 'd1000000-0000-0000-0000-000000000004', 'a0000000-0000-0000-0000-000000000006', '000102030405060708090a0b0c0d0e14', '2025-05-06', 'WW-Zähler Bad.'),
|
||
('5e000000-0000-0000-0000-000000000015', '71000015', 'd1000000-0000-0000-0000-000000000004', 'a0000000-0000-0000-0000-000000000007', '000102030405060708090a0b0c0d0e15', '2025-05-06', 'WW-Zähler Bad.'),
|
||
('5e000000-0000-0000-0000-000000000016', '71000016', 'd1000000-0000-0000-0000-000000000004', 'a0000000-0000-0000-0000-000000000008', '000102030405060708090a0b0c0d0e16', '2025-05-06', 'WW-Zähler Bad.'),
|
||
-- provisioned but not yet assigned to an apartment (normal pilot state)
|
||
('5e000000-0000-0000-0000-000000000017', '71000017', 'd1000000-0000-0000-0000-000000000001', NULL, '000102030405060708090a0b0c0d0e17', '2025-04-15', 'Lager: noch nicht montiert / zugeordnet.')
|
||
ON CONFLICT DO NOTHING;
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- Raw frame log: one row per parse_status, including a frame from a meter
|
||
-- that is not ours (unknown_sensor — normal on a broadcast band).
|
||
-- ---------------------------------------------------------------------------
|
||
|
||
INSERT INTO received_payload (id, gateway_id, sensor_id, timestamp_sent, timestamp_received, raw_payload, parse_status) VALUES
|
||
('7a000000-0000-0000-0000-000000000001', '9e000000-0000-0000-0000-000000000001', '5e000000-0000-0000-0000-000000000001',
|
||
now() - interval '2 hours', now() - interval '2 hours',
|
||
'{"telegram": "2e44685071000001080c7a...", "decoded": {"manufacturer": "QDS", "serial": "71000001", "records": [{"type": "energy", "unit": "kWh", "value": 2841.7}]}}',
|
||
'parsed'),
|
||
('7a000000-0000-0000-0000-000000000002', '9e000000-0000-0000-0000-000000000002', '5e000000-0000-0000-0000-000000000002',
|
||
now() - interval '30 minutes', now() - interval '29 minutes',
|
||
'{"telegram": "2e44685071000002080c7a...", "decoded": {"manufacturer": "QDS", "serial": "71000002", "records": [{"type": "energy", "unit": "kWh", "value": 7404.2}]}}',
|
||
'pending'),
|
||
('7a000000-0000-0000-0000-000000000003', '9e000000-0000-0000-0000-000000000001', NULL,
|
||
now() - interval '1 hour', now() - interval '59 minutes',
|
||
'{"telegram": "2e44a51199887766060c7a...", "decoded": {"manufacturer": "LSE", "serial": "99887766", "records": [{"type": "volume", "unit": "m3", "value": 481.2}]}}',
|
||
'unknown_sensor'),
|
||
('7a000000-0000-0000-0000-000000000004', '9e000000-0000-0000-0000-000000000003', '5e000000-0000-0000-0000-000000000014',
|
||
now() - interval '3 hours', now() - interval '1 hour', -- buffered upload: sent ≪ received
|
||
'{"telegram": "1e44e5c871000014060c7a...", "decoded": null, "error": "decryption failed: wrong key length"}',
|
||
'error')
|
||
ON CONFLICT DO NOTHING;
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- Measurements: daily cumulative meter readings from 2025-06-01 to today.
|
||
--
|
||
-- Values are cumulative totals (meters broadcast counters, consumption is
|
||
-- computed by differencing at query time). Daily increment = annual total
|
||
-- × month weight ÷ days-in-month, ± 10% deterministic jitter (md5 of
|
||
-- sensor/kind/day — NOT random(), so re-runs are byte-identical). Heating
|
||
-- follows a seasonal curve; hot water is flat across the year.
|
||
--
|
||
-- Annual heating totals encode the per-m² profiles the Bandtacho needs:
|
||
-- apt1 45 kWh/m²a (thrifty) … apt4 140 … apt5 190 kWh/m²a (heavy).
|
||
-- ---------------------------------------------------------------------------
|
||
|
||
WITH weights (m, wt) AS (
|
||
VALUES (1, 0.170), (2, 0.140), (3, 0.120), (4, 0.080), (5, 0.040), (6, 0.020),
|
||
(7, 0.015), (8, 0.015), (9, 0.040), (10, 0.080), (11, 0.120), (12, 0.160)
|
||
),
|
||
profiles (sensor_id, kind, unit, annual, start_value, seasonal) AS (
|
||
VALUES
|
||
-- heating (kWh/yr = profile kWh/m²a × living area)
|
||
('5e000000-0000-0000-0000-000000000001'::uuid, 'heating_energy'::measurement_kind, 'kWh', 2430.0, 3210.0, true),
|
||
('5e000000-0000-0000-0000-000000000002'::uuid, 'heating_energy'::measurement_kind, 'kWh', 6120.0, 5480.0, true),
|
||
('5e000000-0000-0000-0000-000000000003'::uuid, 'heating_energy'::measurement_kind, 'kWh', 8500.0, 9120.0, true),
|
||
('5e000000-0000-0000-0000-000000000004'::uuid, 'heating_energy'::measurement_kind, 'kWh', 13440.0, 21930.0, true),
|
||
('5e000000-0000-0000-0000-000000000005'::uuid, 'heating_energy'::measurement_kind, 'kWh', 20900.0, 15040.0, true),
|
||
('5e000000-0000-0000-0000-000000000006'::uuid, 'heating_energy'::measurement_kind, 'kWh', 4200.0, 2660.0, true),
|
||
('5e000000-0000-0000-0000-000000000007'::uuid, 'heating_energy'::measurement_kind, 'kWh', 7125.0, 4310.0, true),
|
||
('5e000000-0000-0000-0000-000000000008'::uuid, 'heating_energy'::measurement_kind, 'kWh', 10560.0, 7750.0, true),
|
||
-- hot water: apartment 1 as energy, the rest as volume
|
||
('5e000000-0000-0000-0000-000000000009'::uuid, 'hot_water_energy'::measurement_kind, 'kWh', 1620.0, 1890.0, false),
|
||
('5e000000-0000-0000-0000-000000000010'::uuid, 'hot_water_volume'::measurement_kind, 'm3', 28.8, 214.3, false),
|
||
('5e000000-0000-0000-0000-000000000011'::uuid, 'hot_water_volume'::measurement_kind, 'm3', 34.0, 131.9, false),
|
||
('5e000000-0000-0000-0000-000000000012'::uuid, 'hot_water_volume'::measurement_kind, 'm3', 38.4, 356.0, false),
|
||
('5e000000-0000-0000-0000-000000000013'::uuid, 'hot_water_volume'::measurement_kind, 'm3', 44.0, 102.4, false),
|
||
('5e000000-0000-0000-0000-000000000014'::uuid, 'hot_water_volume'::measurement_kind, 'm3', 24.0, 188.7, false),
|
||
('5e000000-0000-0000-0000-000000000015'::uuid, 'hot_water_volume'::measurement_kind, 'm3', 30.0, 77.5, false),
|
||
('5e000000-0000-0000-0000-000000000016'::uuid, 'hot_water_volume'::measurement_kind, 'm3', 35.2, 240.1, false),
|
||
-- unassigned sensor: measured and stored regardless
|
||
('5e000000-0000-0000-0000-000000000017'::uuid, 'heating_energy'::measurement_kind, 'kWh', 5000.0, 0.0, true)
|
||
),
|
||
days AS (
|
||
SELECT d::date AS d
|
||
FROM generate_series(date '2025-06-01', current_date, interval '1 day') AS d
|
||
),
|
||
daily AS (
|
||
SELECT p.sensor_id, p.kind, p.unit, dy.d, p.start_value,
|
||
p.annual
|
||
* CASE WHEN p.seasonal THEN w.wt ELSE 1.0 / 12 END
|
||
/ extract(day FROM date_trunc('month', dy.d) + interval '1 month - 1 day')
|
||
* (0.9 + 0.2 * ((('x' || substr(md5(p.sensor_id::text || p.kind::text || dy.d::text), 1, 8))::bit(32)::int & 1023) / 1023.0))
|
||
AS increment
|
||
FROM profiles p
|
||
CROSS JOIN days dy
|
||
JOIN weights w ON w.m = extract(month FROM dy.d)::int
|
||
)
|
||
INSERT INTO measurement (sensor_id, measured_at, kind, value, unit)
|
||
SELECT sensor_id,
|
||
-- per-sensor stable time-of-day, so series don't all tick in lockstep
|
||
d::timestamp + interval '6 hours'
|
||
+ make_interval(mins => (('x' || substr(md5(sensor_id::text), 1, 4))::bit(16)::int % 50)),
|
||
kind,
|
||
round((start_value + sum(increment) OVER (PARTITION BY sensor_id, kind ORDER BY d))::numeric, 3),
|
||
unit
|
||
FROM daily
|
||
ON CONFLICT DO NOTHING;
|
||
|
||
-- Diagnostics from the apartment-4 heat meter: flow/return temperatures
|
||
-- (seasonal: ~69/50 °C in January, ~44/32 °C in July) and an error register
|
||
-- that is 0 except for one flagged day.
|
||
|
||
WITH weights (m, wt) AS (
|
||
VALUES (1, 0.170), (2, 0.140), (3, 0.120), (4, 0.080), (5, 0.040), (6, 0.020),
|
||
(7, 0.015), (8, 0.015), (9, 0.040), (10, 0.080), (11, 0.120), (12, 0.160)
|
||
),
|
||
days AS (
|
||
SELECT d::date AS d
|
||
FROM generate_series(date '2025-06-01', current_date, interval '1 day') AS d
|
||
)
|
||
INSERT INTO measurement (sensor_id, measured_at, kind, value, unit)
|
||
SELECT '5e000000-0000-0000-0000-000000000004'::uuid,
|
||
dy.d::timestamp + interval '6 hours 15 minutes',
|
||
k.kind,
|
||
round((k.base + k.span * w.wt
|
||
+ 2 * ((('x' || substr(md5(k.kind::text || dy.d::text), 1, 8))::bit(32)::int & 255) / 255.0))::numeric, 1),
|
||
'°C'
|
||
FROM days dy
|
||
JOIN weights w ON w.m = extract(month FROM dy.d)::int
|
||
CROSS JOIN (VALUES ('flow_temperature'::measurement_kind, 42.0, 160.0),
|
||
('return_temperature'::measurement_kind, 30.0, 120.0)) AS k (kind, base, span)
|
||
ON CONFLICT DO NOTHING;
|
||
|
||
INSERT INTO measurement (sensor_id, measured_at, kind, value, unit) VALUES
|
||
('5e000000-0000-0000-0000-000000000004', date '2026-02-10' + interval '6 hours 15 minutes', 'error_flags', 0, 'flags'),
|
||
('5e000000-0000-0000-0000-000000000004', date '2026-02-11' + interval '6 hours 15 minutes', 'error_flags', 16, 'flags'), -- e.g. air in flow sensor
|
||
('5e000000-0000-0000-0000-000000000004', date '2026-02-12' + interval '6 hours 15 minutes', 'error_flags', 0, 'flags')
|
||
ON CONFLICT DO NOTHING;
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- Reference data — DEV APPROXIMATIONS, not verified legal values.
|
||
-- ---------------------------------------------------------------------------
|
||
|
||
-- Efficiency class thresholds: GEG Anlage 10 annual bounds split into monthly
|
||
-- bounds using the same seasonal weights as the heating fixtures. The real
|
||
-- UBA CC 69/2021 Tabelle 1 values differ — replace via a verified migration.
|
||
WITH classes (class, annual_max) AS (
|
||
VALUES ('A+', 30.0), ('A', 50.0), ('B', 75.0), ('C', 100.0),
|
||
('D', 130.0), ('E', 160.0), ('F', 200.0), ('G', 250.0)
|
||
),
|
||
weights (m, wt) AS (
|
||
VALUES (1, 0.170), (2, 0.140), (3, 0.120), (4, 0.080), (5, 0.040), (6, 0.020),
|
||
(7, 0.015), (8, 0.015), (9, 0.040), (10, 0.080), (11, 0.120), (12, 0.160)
|
||
)
|
||
INSERT INTO efficiency_class_threshold (class, month, max_kwh_per_m2, source)
|
||
SELECT c.class, w.m, round((c.annual_max * w.wt)::numeric, 3),
|
||
'DEV FIXTURE — approximation of UBA CC 69/2021 Tab. 1 / GEG Anl. 10'
|
||
FROM classes c CROSS JOIN weights w
|
||
UNION ALL
|
||
SELECT 'H', w.m, 999.999, -- open-ended class: sentinel, not a real bound
|
||
'DEV FIXTURE — approximation of UBA CC 69/2021 Tab. 1 / GEG Anl. 10'
|
||
FROM weights w
|
||
ON CONFLICT DO NOTHING;
|
||
|
||
-- CO2 factors: one superseded natural-gas row to exercise versioning.
|
||
INSERT INTO co2_emission_factor (fuel_type, g_co2_per_kwh, valid_from, valid_to, source) VALUES
|
||
('natural_gas', 202.00, '2021-01-01', '2023-12-31', 'DEV FIXTURE — approximates GEG Anlage 9'),
|
||
('natural_gas', 240.00, '2024-01-01', NULL, 'DEV FIXTURE — approximates GEG Anlage 9'),
|
||
('heating_oil', 310.00, '2024-01-01', NULL, 'DEV FIXTURE — approximates GEG Anlage 9'),
|
||
('district_heating', 180.00, '2024-01-01', NULL, 'DEV FIXTURE — approximates GEG Anlage 9'),
|
||
('heat_pump_electricity', 560.00, '2024-01-01', NULL, 'DEV FIXTURE — approximates GEG Anlage 9'),
|
||
('wood_pellets', 20.00, '2024-01-01', NULL, 'DEV FIXTURE — approximates GEG Anlage 9'),
|
||
('other', 300.00, '2024-01-01', NULL, 'DEV FIXTURE — placeholder')
|
||
ON CONFLICT DO NOTHING;
|
||
|
||
-- Energy prices: one superseded natural-gas row to exercise versioning.
|
||
INSERT INTO energy_price (fuel_type, ct_per_kwh, valid_from, valid_to, source, created_by) VALUES
|
||
('natural_gas', 11.200, '2024-01-01', '2025-12-31', 'DEV FIXTURE', 'e0000000-0000-0000-0000-000000000001'),
|
||
('natural_gas', 12.400, '2026-01-01', NULL, 'DEV FIXTURE', 'e0000000-0000-0000-0000-000000000001'),
|
||
('heating_oil', 10.900, '2026-01-01', NULL, 'DEV FIXTURE', 'e0000000-0000-0000-0000-000000000001'),
|
||
('district_heating', 15.800, '2026-01-01', NULL, 'DEV FIXTURE', 'e0000000-0000-0000-0000-000000000001'),
|
||
('heat_pump_electricity', 27.500, '2026-01-01', NULL, 'DEV FIXTURE', 'e0000000-0000-0000-0000-000000000001'),
|
||
('wood_pellets', 8.200, '2026-01-01', NULL, 'DEV FIXTURE', 'e0000000-0000-0000-0000-000000000001')
|
||
ON CONFLICT DO NOTHING;
|
||
|
||
-- Heating degree days: the 15 complete months before the current month.
|
||
-- The CURRENT month is deliberately absent — the freshest footnote renders
|
||
-- "not available" until the (future) monthly DWD import fills it, exactly
|
||
-- like prod. Deterministic per (year, month), so re-runs insert nothing new
|
||
-- for existing months and exactly one row when a month completes.
|
||
WITH base (m, hdd0) AS (
|
||
VALUES (1, 550), (2, 480), (3, 400), (4, 280), (5, 140), (6, 40),
|
||
(7, 15), (8, 20), (9, 110), (10, 280), (11, 420), (12, 520)
|
||
),
|
||
months AS (
|
||
SELECT (date_trunc('month', current_date) - make_interval(months => n))::date AS mon
|
||
FROM generate_series(1, 15) AS n
|
||
)
|
||
INSERT INTO heating_degree_days (station_id, year, month, hdd)
|
||
SELECT 'dd000000-0000-0000-0000-000000000001',
|
||
extract(year FROM mon)::smallint,
|
||
extract(month FROM mon)::smallint,
|
||
round((b.hdd0 * (0.90 + 0.2 * ((('x' || substr(md5(mon::text), 1, 8))::bit(32)::int & 255) / 255.0)))::numeric, 1)
|
||
FROM months
|
||
JOIN base b ON b.m = extract(month FROM mon)::int
|
||
ON CONFLICT DO NOTHING;
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- Savings tips (Module 4) — one retired tip to exercise active = false.
|
||
-- ---------------------------------------------------------------------------
|
||
|
||
INSERT INTO saving_tip (id, category, title, body, link_url, active) VALUES
|
||
('5a000000-0000-0000-0000-000000000001', 'heating', 'Stoßlüften statt Kipplüften',
|
||
'Mehrmals täglich 5–10 Minuten mit weit geöffnetem Fenster lüften, statt das Fenster dauerhaft zu kippen. So bleibt die Wärme in Wänden und Möbeln erhalten.',
|
||
'https://www.verbraucherzentrale.de/wissen/energie/heizen-und-warmwasser', true),
|
||
('5a000000-0000-0000-0000-000000000002', 'heating', 'Raumtemperatur um 1 °C senken',
|
||
'Ein Grad weniger spart rund 6 % Heizenergie. 20 °C im Wohnzimmer und 17–18 °C im Schlafzimmer reichen in der Regel aus.',
|
||
'https://www.co2online.de/energie-sparen/heizenergie-sparen/', true),
|
||
('5a000000-0000-0000-0000-000000000003', 'heating', 'Heizkörper entlüften',
|
||
'Gluckert der Heizkörper oder wird er nur teilweise warm, ist Luft im System. Entlüften stellt die volle Heizleistung wieder her.',
|
||
NULL, true),
|
||
('5a000000-0000-0000-0000-000000000004', 'heating', 'Heizkörper nicht zustellen',
|
||
'Möbel und Vorhänge vor dem Heizkörper stauen die Wärme und erhöhen den Verbrauch.',
|
||
NULL, false), -- retired tip: must remain resolvable for past months
|
||
('5a000000-0000-0000-0000-000000000005', 'hot_water', 'Duschen statt Baden',
|
||
'Ein Vollbad benötigt etwa dreimal so viel warmes Wasser wie eine kurze Dusche.',
|
||
'https://www.verbraucherzentrale.de/wissen/energie/heizen-und-warmwasser', true),
|
||
('5a000000-0000-0000-0000-000000000006', 'hot_water', 'Sparduschkopf einbauen',
|
||
'Ein Sparduschkopf halbiert den Warmwasserverbrauch beim Duschen — bei gleichem Komfort.',
|
||
'https://www.co2online.de/energie-sparen/strom-sparen/', true),
|
||
('5a000000-0000-0000-0000-000000000007', 'hot_water', 'Warmwasser nicht laufen lassen',
|
||
'Beim Einseifen, Zähneputzen und Spülen das warme Wasser abstellen.',
|
||
NULL, true)
|
||
ON CONFLICT DO NOTHING;
|
||
|
||
COMMIT;
|