website/db/reorg/06_hardware_energy_cs.sql

-- 06_hardware_energy_cs.sql: bring hardware + energy + CS prices over from dev.
-- Expects schemas import_hardware / import_energy / import_cs, staged by run.sh from dumps/dev-import-staged.dump.
SET ROLE personal;

-- ---------- energy (DDL identical to ned-collector/collect.py so its IF NOT EXISTS is a no-op) ----------
CREATE TABLE energy.gas_storage_daily (
  gas_day    date NOT NULL,
  site       text NOT NULL,
  point_name text,
  volume_twh numeric(12,4),
  fill_pct   numeric(7,3),
  source     text NOT NULL DEFAULT 'ned-json',
  first_seen timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (gas_day, site)
);
CREATE TABLE energy.ned_utilization (
  valid_from timestamptz NOT NULL, valid_to timestamptz NOT NULL,
  point text NOT NULL, type text NOT NULL, activity text NOT NULL, classification text NOT NULL, granularity text NOT NULL,
  capacity_kw numeric, volume_kwh numeric, percentage numeric, emission_kg numeric, emission_factor numeric,
  source_file text,
  first_seen timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (valid_from, point, type, activity, classification, granularity)
);
CREATE INDEX ned_utilization_type_time ON energy.ned_utilization (type, granularity, valid_from DESC);
CREATE TABLE energy.collector_runs (
  id bigserial PRIMARY KEY,
  started_at timestamptz NOT NULL DEFAULT now(), finished_at timestamptz,
  mode text NOT NULL, ok boolean, rows_upserted integer, errors text
);
CREATE VIEW energy.gas_storage_latest AS
  SELECT DISTINCT ON (site) site, gas_day, volume_twh, fill_pct, updated_at
    FROM energy.gas_storage_daily ORDER BY site, gas_day DESC;
CREATE VIEW energy.electricity_hourly AS
  SELECT valid_from AT TIME ZONE 'Europe/Amsterdam' AS hour_local, type, classification,
         round(volume_kwh / 1000.0, 1) AS mwh, percentage, emission_factor AS kg_co2_per_kwh
    FROM energy.ned_utilization WHERE granularity = 'Hour';

INSERT INTO energy.gas_storage_daily SELECT * FROM import_energy.gas_storage_daily;
INSERT INTO energy.ned_utilization  SELECT * FROM import_energy.ned_utilization;
INSERT INTO energy.collector_runs   SELECT * FROM import_energy.collector_runs;
SELECT setval('energy.collector_runs_id_seq', coalesce((SELECT max(id) FROM energy.collector_runs), 1));

-- ---------- hardware (prices -> integer cents) ----------
CREATE TABLE hardware.devices (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       text NOT NULL UNIQUE,
  kind       text NOT NULL CHECK (kind IN ('desktop','laptop','phone','vm','monitor')),
  notes      text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE hardware.components (
  id                bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  device_id         bigint NOT NULL REFERENCES hardware.devices(id) ON DELETE CASCADE,
  category          text NOT NULL CHECK (category IN ('case','cpu','motherboard','ram','cpu_cooler','gpu','psu','storage','fan','monitor','lighting','accessory')),
  brand             text,
  model             text NOT NULL,
  quantity          integer NOT NULL DEFAULT 1 CHECK (quantity > 0),
  specs             jsonb NOT NULL DEFAULT '{}'::jsonb,
  unit_price_cents  integer CHECK (unit_price_cents >= 0),
  total_price_cents integer CHECK (total_price_cents >= 0),
  shop              text,
  order_number      text,
  order_date        date,
  warranty_until    date,
  invoice_number    text,
  status            text NOT NULL DEFAULT 'in_use' CHECK (status IN ('in_use','retired','sold')),
  verified          boolean NOT NULL DEFAULT true,
  notes             text,
  source            text,
  created_at        timestamptz NOT NULL DEFAULT now(),
  updated_at        timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX components_category_idx ON hardware.components (category);
CREATE INDEX components_device_idx ON hardware.components (device_id);
CREATE UNIQUE INDEX components_dedupe_uq ON hardware.components (device_id, model, order_number) NULLS NOT DISTINCT;
CREATE INDEX components_specs_gin ON hardware.components USING gin (specs);
COMMENT ON SCHEMA hardware IS 'Stan''s hardware inventory: devices (PCs, laptops, phones, VMs, monitors) and their components/peripherals with purchase + warranty details.';
COMMENT ON TABLE hardware.devices IS 'A physical or virtual machine Stan owns/uses (e.g. stan-pc). Components hang off a device.';
COMMENT ON TABLE hardware.components IS 'One line item (part or peripheral) belonging to a device, with purchase, price and warranty info.';
COMMENT ON COLUMN hardware.components.unit_price_cents IS 'Price per unit in EUR cents incl. VAT.';
COMMENT ON COLUMN hardware.components.total_price_cents IS 'unit price * quantity in EUR cents incl. VAT (excl. shipping).';
COMMENT ON COLUMN hardware.components.verified IS 'false = purchase is real but unconfirmed whether it is (still) part of this setup, or some details are guessed. See notes.';
COMMENT ON COLUMN hardware.components.source IS 'Where the data came from: user_list, gmail, manual.';

INSERT INTO hardware.devices (id, name, kind, notes, created_at, updated_at) OVERRIDING SYSTEM VALUE
  SELECT id, name, kind, notes, created_at, created_at FROM import_hardware.devices;
INSERT INTO hardware.components (id, device_id, category, brand, model, quantity, specs, unit_price_cents, total_price_cents,
  shop, order_number, order_date, warranty_until, invoice_number, status, verified, notes, source, created_at, updated_at)
  OVERRIDING SYSTEM VALUE
  SELECT id, device_id, category, brand, model, quantity, specs, round(unit_price_eur * 100)::integer, round(total_price_eur * 100)::integer,
         shop, order_number, order_date, warranty_until, invoice_number, status, verified, notes, source, created_at, updated_at
    FROM import_hardware.components;
SELECT setval(pg_get_serial_sequence('hardware.devices', 'id'), (SELECT max(id) FROM hardware.devices));
SELECT setval(pg_get_serial_sequence('hardware.components', 'id'), (SELECT max(id) FROM hardware.components));
DO $$ BEGIN
  IF (SELECT sum(total_price_cents) FROM hardware.components) <> (SELECT round(sum(total_price_eur) * 100) FROM import_hardware.components) THEN
    RAISE EXCEPTION 'hardware cents conversion mismatch'; END IF;
END $$;

CREATE VIEW hardware.setup_overview AS
 SELECT d.name AS device, d.kind AS device_kind, c.category, c.brand, c.model, c.quantity,
        c.total_price_cents, round(c.total_price_cents / 100.0, 2) AS total_price_eur,
        c.shop, c.order_date, c.warranty_until, (c.warranty_until >= CURRENT_DATE) AS under_warranty,
        c.status, c.verified, c.specs, c.notes, c.order_number, c.invoice_number, c.source, c.id AS component_id
   FROM hardware.components c JOIN hardware.devices d ON d.id = c.device_id
  ORDER BY d.name, array_position(ARRAY['case','cpu','motherboard','ram','cpu_cooler','gpu','psu','storage','fan','lighting','monitor','accessory'], c.category),
           c.order_date, c.model;
COMMENT ON VIEW hardware.setup_overview IS 'Flat view of every component per device, build order, live under_warranty flag. Filter status = ''in_use'' for the current setup.';

-- ---------- CS prices (prod tables are empty; copy dev) ----------
DO $$ BEGIN
  IF EXISTS (SELECT 1 FROM cs.items) OR EXISTS (SELECT 1 FROM cs.price_observations)
     OR EXISTS (SELECT 1 FROM cs.item_dimensions) OR EXISTS (SELECT 1 FROM cs.price_streams) THEN
    RAISE EXCEPTION 'cs.* not empty in target; refusing to merge dev data'; END IF;
END $$;
INSERT INTO cs.items (id, market_hash_name, name, icon_url, item_type, first_seen_at, last_seen_at)
  SELECT id, market_hash_name, name, icon_url, item_type, first_seen_at, last_seen_at FROM import_cs.cs_market_items;
INSERT INTO cs.item_dimensions (item_id, key, value) SELECT item_id, key, value FROM import_cs.cs_item_dimensions;
INSERT INTO cs.price_streams (source, currency, kind) SELECT source, currency, kind FROM import_cs.cs_price_streams;
INSERT INTO cs.price_observations (id, item_id, source, currency, observed_at, price_cents, volume, kind, imported_at) OVERRIDING SYSTEM VALUE
  SELECT id, item_id, source, currency, observed_at, price_cents, volume, kind, imported_at FROM import_cs.cs_price_observations;
SELECT setval(pg_get_serial_sequence('cs.items', 'id'), coalesce((SELECT max(id) FROM cs.items), 1));
SELECT setval(pg_get_serial_sequence('cs.price_observations', 'id'), coalesce((SELECT max(id) FROM cs.price_observations), 1));

CREATE TABLE legacy.reorg_imports (dst text PRIMARY KEY, src text NOT NULL, n bigint NOT NULL);
-- ---------- verify imports against the staged dev copies, then drop staging ----------
RESET ROLE;
DO $$
DECLARE r record; a bigint; b bigint;
BEGIN
  FOR r IN SELECT * FROM (VALUES
     ('energy.gas_storage_daily','import_energy.gas_storage_daily'), ('energy.ned_utilization','import_energy.ned_utilization'),
     ('energy.collector_runs','import_energy.collector_runs'), ('hardware.devices','import_hardware.devices'),
     ('hardware.components','import_hardware.components'), ('cs.items','import_cs.cs_market_items'),
     ('cs.item_dimensions','import_cs.cs_item_dimensions'), ('cs.price_streams','import_cs.cs_price_streams'),
     ('cs.price_observations','import_cs.cs_price_observations')) v(dst, srct) LOOP
    EXECUTE 'SELECT count(*) FROM ' || r.dst INTO a;
    EXECUTE 'SELECT count(*) FROM ' || r.srct INTO b;
    IF a <> b THEN RAISE EXCEPTION '% has % rows, dev % has %', r.dst, a, r.srct, b; END IF;
    INSERT INTO legacy.reorg_imports VALUES (r.dst, r.srct, a);
  END LOOP;
END $$;
DROP SCHEMA import_energy CASCADE;
DROP SCHEMA import_hardware CASCADE;
DROP SCHEMA import_cs CASCADE;