-- Buildings registry sourced from Porsenna (e-manažer)
-- Populated from the embedded `building` object in /gauges/{id} responses.
-- The (source, id) PK reflects that Porsenna building ids are integers local to each tenant instance.
CREATE TABLE enapo_porsenna_buildings (
	source varchar(50) NOT NULL,
	id int4 NOT NULL,
	name varchar NULL,
	street varchar NULL,
	house_number varchar NULL,
	street_number varchar NULL,
	city varchar NULL,
	postcode varchar NULL,
	gid varchar NULL,
	created_at timestamptz DEFAULT now() NULL,
	updated_at timestamptz NULL,
	CONSTRAINT enapo_porsenna_buildings_pk PRIMARY KEY (source, id)
);

COMMENT ON COLUMN enapo_porsenna_buildings.source IS 'Tenant identifier, e.g. porsenna_p7. Matches ProviderType enum.';
COMMENT ON COLUMN enapo_porsenna_buildings.gid IS 'gid string as supplied by Porsenna. Suspected RUIAN building identifier based on format (8-digit numeric in sample payload) but not confirmed with the source system. If confirmed, becomes a soft link to eno_budova.gid (no FK).';

CREATE INDEX enapo_porsenna_buildings_gid_idx ON enapo_porsenna_buildings (gid);


-- Devices sourced from Porsenna Gauges. Identification fields flattened onto the device.
-- DisableReason endpoint is not accessible to us; we keep only the numeric id parsed from the IRI.
CREATE TABLE enapo_porsenna_devices (
	source varchar(50) NOT NULL,
	id uuid NOT NULL,
	building_id int4 NULL,
	parent_device_id uuid NULL,
	"level" varchar NULL,
	"type" varchar NULL,
	name varchar NULL,
	measurement_target varchar NULL,
	disabled_at timestamptz NULL,
	disable_reason_id int4 NULL,
	show_in_output bool NULL,
	-- flattened Identification
	identifier varchar NULL,
	supply_point_number varchar NULL,
	meter_number varchar NULL,
	identification_gid varchar NULL,
	created_at timestamptz DEFAULT now() NULL,
	updated_at timestamptz NULL,
	CONSTRAINT enapo_porsenna_devices_pk PRIMARY KEY (source, id)
);

COMMENT ON COLUMN enapo_porsenna_devices.source IS 'Tenant identifier, e.g. porsenna_p7. Matches ProviderType enum.';
COMMENT ON COLUMN enapo_porsenna_devices.building_id IS 'Soft ref to enapo_porsenna_buildings (source, id); no FK.';
COMMENT ON COLUMN enapo_porsenna_devices.parent_device_id IS 'Soft self-ref to enapo_porsenna_devices (source, id); no FK. Used for sub-meter hierarchy.';
COMMENT ON COLUMN enapo_porsenna_devices."level" IS 'main / sub';
COMMENT ON COLUMN enapo_porsenna_devices."type" IS 'gasometer / electrometer / heatmeter / watermeter / ...';
COMMENT ON COLUMN enapo_porsenna_devices.disable_reason_id IS 'Numeric id parsed from the /disable_reasons/{n} IRI. The lookup endpoint is not accessible to us, so the human-readable name is not retrievable.';
COMMENT ON COLUMN enapo_porsenna_devices.identifier IS 'Primary identifier from Porsenna Identification. Can be an EAN (prefix 859182400, typically electricity_meter), an EIC code (prefix 27ZG100Z, typically gasometer), or a numerical OM identifier (heat_meter, water_meter).';

CREATE INDEX enapo_porsenna_devices_identifier_idx ON enapo_porsenna_devices (identifier);
CREATE INDEX enapo_porsenna_devices_building_idx ON enapo_porsenna_devices (source, building_id);


-- Yearly consumption aggregates per device per period.
-- 'var' is the period kind (yearly today; monthly/daily reserved for future use).
-- @type from the Hydra payload is always "Consumption" and is implicit in this table.
CREATE TABLE enapo_porsenna_consumption (
	source varchar(50) NOT NULL,
	device_id uuid NOT NULL,
	"var" varchar(20) NOT NULL,
	"period" varchar(20) NOT NULL,
	value numeric(30, 15) NOT NULL,
	unit varchar(20) NOT NULL,
	commodity varchar(20) NULL,
	"count" int4 NULL,
	created_at timestamptz DEFAULT now() NULL,
	updated_at timestamptz NULL,
	CONSTRAINT enapo_porsenna_consumption_pk PRIMARY KEY (source, device_id, "var", "period")
);

COMMENT ON COLUMN enapo_porsenna_consumption.source IS 'Tenant identifier, e.g. porsenna_p7. Matches ProviderType enum.';
COMMENT ON COLUMN enapo_porsenna_consumption.device_id IS 'Soft ref to enapo_porsenna_devices (source, id); no FK.';
COMMENT ON COLUMN enapo_porsenna_consumption."var" IS 'Period kind: yearly | monthly | daily. Currently only yearly is ingested.';
COMMENT ON COLUMN enapo_porsenna_consumption."period" IS 'Period identifier as supplied by the API (e.g. "2023" for yearly aggregates).';
COMMENT ON COLUMN enapo_porsenna_consumption.commodity IS 'Denormalised from enapo_porsenna_devices.type for fast filtered reads.';
COMMENT ON COLUMN enapo_porsenna_consumption."count" IS 'Days covered by the period; useful for partial periods (e.g. current year).';

CREATE INDEX enapo_porsenna_consumption_period_idx ON enapo_porsenna_consumption ("period");
