-- ============================================================================
-- 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;