-- Trigram matching for the fuzzy part of the search. IF NOT EXISTS keeps the migration re-runnable
-- on environments that already have it; note it does NOT relocate an installation sitting in another
-- schema - the index definitions below reference public.gist_trgm_ops, so if the extension lives
-- elsewhere, move it first with ALTER EXTENSION pg_trgm SET SCHEMA public.
CREATE EXTENSION IF NOT EXISTS unaccent WITH SCHEMA public;
CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA public;

-- Text is normalised with lower(public.unaccent(...)) so that searching without Czech diacritics
-- works. The same expression is applied to the incoming query in the search repository - keep the
-- two in sync, otherwise the normalised columns stop matching what is searched for.
-- unaccent is STABLE rather than IMMUTABLE, which is fine here: the normalised text is stored as
-- real columns of the materialized view and the trigram indexes are built on those columns, not on
-- an expression.

-- ENO geometry is stored as WKT text with a per-row EPSG code (S-JTSK 5514 by default).
-- Malformed WKT would abort the whole REFRESH, so the conversion returns NULL instead of raising.
CREATE OR REPLACE FUNCTION f_ded_safe_geom(wkt text, epsg varchar)
RETURNS public.geometry
LANGUAGE plpgsql
IMMUTABLE
PARALLEL SAFE
AS $$
BEGIN
	RETURN public.ST_Transform(
		public.ST_SetSRID(public.ST_GeomFromText(wkt), COALESCE(NULLIF(epsg, '')::int, 5514)),
		4326
	);
EXCEPTION WHEN OTHERS THEN
	RETURN NULL;
END;
$$;

COMMENT ON FUNCTION f_ded_safe_geom(text, varchar) IS
	'Converts an eno_geometrie WKT polygon to EPSG:4326, returning NULL for unparsable geometry instead of raising (a single bad row must not abort the search index refresh).';

-- Building detail view, expanded with the ENO lookups (eno_ciselnik_*) resolved through eno_majetek:
-- the evidenční jednotka and both správci (mandate and accounting-unit). Kept as one row per building
-- GID via DISTINCT ON; the mv_ded_building_search below depends on this view, so it must be defined first.
--
-- The LATERALs pick the latest row by platnost_od and deliberately do NOT filter on aktivni: every
-- existing ENO repository in this module reads these lookups the same way, and the endpoint has to keep
-- reporting the správce/evidenční jednotka a building was last assigned to even after that lookup row is
-- deactivated. If the ENO data contract ever says a deactivated row must disappear from the detail, the
-- filter belongs in all ENO consumers at once, not only here.
CREATE OR REPLACE VIEW v_enapo_building_detail AS
SELECT DISTINCT ON (b.gid)
	b.gid,
	b.zdroj,
	b.nazev,
	b.oznaceni,
	b.druh_vytapeni,
	b.pripojka_elektro,
	b.pripojka_voda,
	b.pripojka_kanalizace,
	b.budova_rozdelena_byt_nebyt,
	b.celkova_plocha_budova,
	b.celkova_plocha_byt_budova,
	b.celkova_plocha_nebyt_budova,
	b.zastavena_plocha,
	b.obestaveny_prostor,
	b.pocet_byt,
	b.pocet_nebyt_budova,
	b.pocet_nadzem_podlazi,
	b.pocet_podzem_podlazi,
	b.pocet_podkrovi,
	b.vytah,
	b.czcc,
	b.id_cuzk,
	b.id_objekt,
	b.kod_vyuziti,
	b.nazev_vyuziti,
	b.kod_ochrana,
	b.nazev_ochrana,
	b.platnost_od,
	ej.ej_id,
	ej.ej_kod,
	ej.ej_nazev,
	ej.ej_typ,
	spm.sp_id AS spm_id,
	spm.sp_kod AS spm_kod,
	spm.sp_nazev AS spm_nazev,
	spm.sp_ic AS spm_ic,
	spm.sp_dic AS spm_dic,
	spu.sp_id AS spu_id,
	spu.sp_kod AS spu_kod,
	spu.sp_nazev AS spu_nazev,
	spu.sp_ic AS spu_ic,
	spu.sp_dic AS spu_dic
FROM eno_budova b
LEFT JOIN eno_majetek m
	ON m.gid = b.gid
	AND m.gid_typ = 'budova'
	AND m.zdroj = b.zdroj
LEFT JOIN LATERAL (
	SELECT
		e.id AS ej_id,
		e.kod AS ej_kod,
		e.nazev AS ej_nazev,
		e.typ AS ej_typ
	FROM eno_ciselnik_evidencni_jednotka e
	WHERE e.id = m.id_maj_evidencni_jednotka
	ORDER BY e.platnost_od DESC NULLS LAST
	LIMIT 1
) ej ON true
LEFT JOIN LATERAL (
	SELECT
		s.id_maj_spravce AS sp_id,
		s.kod AS sp_kod,
		s.nazev AS sp_nazev,
		s.ic AS sp_ic,
		s.dic AS sp_dic
	FROM eno_ciselnik_spravce s
	WHERE s.id_maj_spravce = m.id_maj_spravce_mandatni
	ORDER BY s.platnost_od DESC NULLS LAST
	LIMIT 1
) spm ON true
LEFT JOIN LATERAL (
	SELECT
		s.id_maj_spravce AS sp_id,
		s.kod AS sp_kod,
		s.nazev AS sp_nazev,
		s.ic AS sp_ic,
		s.dic AS sp_dic
	FROM eno_ciselnik_spravce s
	WHERE s.id_maj_spravce = m.id_maj_spravce_ucet_jednotka
	ORDER BY s.platnost_od DESC NULLS LAST
	LIMIT 1
) spu ON true
ORDER BY b.gid, b.zdroj;

-- One row per building GID, precomputed for the DeD search endpoint.
-- Materialized because v_enapo_building_consumption_points carries three LEFT JOIN LATERALs plus a
-- UNION ALL branch per point and the geometry needs a PostGIS transform - too much to recompute on
-- every keystroke of an autocomplete.
-- Scope: buildings only (eno_budova / gid_typ = 'budova'), eno_stavba is deliberately not covered.
CREATE MATERIALIZED VIEW mv_ded_building_search AS
WITH searchable_gid AS (
	SELECT gid FROM v_enapo_building_detail
	UNION
	SELECT gid FROM enapo_gid_mapping
	UNION
	SELECT gid FROM v_enapo_building_porsenna_devices
),
active_gid AS (
	SELECT g.gid
	FROM searchable_gid g
	WHERE NOT EXISTS (
		SELECT 1
		FROM eno_vyrazeno v
		WHERE v.gid = g.gid AND v.gid_typ = 'budova'
	)
),
building_address AS (
	SELECT
		a.gid,
		array_agg(a.popis::text ORDER BY a.hlavni DESC NULLS LAST, a.poradi ASC) AS addresses
	FROM (
		SELECT DISTINCT ON (gid, popis) gid, popis, hlavni, poradi
		FROM eno_adresa
		WHERE gid_typ = 'budova' AND popis IS NOT NULL
		ORDER BY gid, popis, hlavni DESC NULLS LAST, poradi ASC
	) a
	GROUP BY a.gid
),
building_point AS (
	SELECT
		gid,
		COALESCE(array_agg(DISTINCT place_id::text ORDER BY place_id::text) FILTER (WHERE id_type = 'ean'), '{}'::text[]) AS ean,
		COALESCE(array_agg(DISTINCT place_id::text ORDER BY place_id::text) FILTER (WHERE id_type = 'eic'), '{}'::text[]) AS eic,
		COALESCE(array_agg(DISTINCT place_id::text ORDER BY place_id::text) FILTER (WHERE id_type = 'om'), '{}'::text[]) AS om,
		count(*) FILTER (WHERE is_active) AS active_points_count
	FROM v_enapo_building_consumption_points
	GROUP BY gid
),
building_geometry AS (
	SELECT
		gid,
		public.ST_Multi(public.ST_Collect(f_ded_safe_geom(zakres, '5514'))) AS geom
	FROM eno_geometrie
	WHERE zakres IS NOT NULL
	GROUP BY gid
)
SELECT
	g.gid::text AS gid,
	b.nazev::text AS nazev,
	b.oznaceni::text AS oznaceni,
	-- The whole evidenční jednotka is carried so the search response can use the same shape as the
	-- building detail endpoint (id / kod / nazev / typ) instead of a narrower look-alike.
	b.ej_id AS ej_id,
	b.ej_kod::text AS ej_kod,
	b.ej_nazev::text AS ej_nazev,
	b.ej_typ::text AS ej_typ,
	a.addresses[1] AS address_main,
	COALESCE(a.addresses, '{}'::text[]) AS addresses,
	COALESCE(p.ean, '{}'::text[]) AS ean,
	COALESCE(p.eic, '{}'::text[]) AS eic,
	COALESCE(p.om, '{}'::text[]) AS om,
	COALESCE(p.active_points_count, 0) AS active_points_count,
	(b.gid IS NOT NULL) AS has_building_record,
	lower(public.unaccent(concat_ws(' ', b.nazev, b.oznaceni))) AS name_norm,
	lower(public.unaccent(COALESCE(array_to_string(a.addresses, ' '), ''))) AS address_norm,
	(
		setweight(to_tsvector('simple', lower(public.unaccent(concat_ws(' ', b.nazev, b.oznaceni, b.ej_nazev)))), 'A') ||
		setweight(to_tsvector('simple', lower(public.unaccent(COALESCE(a.addresses[1], '')))), 'B') ||
		setweight(to_tsvector('simple', lower(public.unaccent(COALESCE(array_to_string(a.addresses[2:], ' '), '')))), 'C') ||
		setweight(
			to_tsvector(
				'simple',
				lower(concat_ws(' ', array_to_string(COALESCE(p.ean, '{}') || COALESCE(p.eic, '{}') || COALESCE(p.om, '{}'), ' '), g.gid, b.ej_kod))
			),
			'D'
		)
	) AS search_document,
	geo.geom
FROM active_gid g
LEFT JOIN v_enapo_building_detail b ON b.gid = g.gid
LEFT JOIN building_address a ON a.gid = g.gid
LEFT JOIN building_point p ON p.gid = g.gid
LEFT JOIN building_geometry geo ON geo.gid = g.gid;

COMMENT ON MATERIALIZED VIEW mv_ded_building_search IS
	'Search index for the DeD building search endpoint - one row per building GID with the weighted tsvector, normalised name/address for trigram matching, EAN/EIC/OM identifiers for exact matching and the building geometry in EPSG:4326. Refreshed by the DedSearchIndexRefreshTask (integration engine).';

-- The internal_ prefix the DB conventions mandate for generated columns is a rule about tables: every
-- column of this object is derived, so prefixing all of them would carry no information and would break
-- the shared naming with v_enapo_building_detail. The intent behind the rule - being able to tell how a
-- value was produced - is met with a per-column comment on each computed column instead.
COMMENT ON COLUMN mv_ded_building_search.addresses IS
	'Distinct eno_adresa.popis of the building (gid_typ = ''budova''), ordered main address first, then by poradi.';
COMMENT ON COLUMN mv_ded_building_search.address_main IS 'First element of addresses, i.e. the main address.';
COMMENT ON COLUMN mv_ded_building_search.ean IS
	'Distinct EAN identifiers of the building consumption points (v_enapo_building_consumption_points, id_type = ''ean'').';
COMMENT ON COLUMN mv_ded_building_search.eic IS
	'Distinct EIC identifiers of the building consumption points (v_enapo_building_consumption_points, id_type = ''eic'').';
COMMENT ON COLUMN mv_ded_building_search.om IS
	'Distinct OM identifiers of the building consumption points (v_enapo_building_consumption_points, id_type = ''om'').';
COMMENT ON COLUMN mv_ded_building_search.active_points_count IS
	'Number of consumption points of the building flagged is_active in v_enapo_building_consumption_points.';
COMMENT ON COLUMN mv_ded_building_search.has_building_record IS
	'False when the GID reached the index only through enapo_gid_mapping or Porsenna and has no eno_budova record.';
COMMENT ON COLUMN mv_ded_building_search.name_norm IS
	'lower(public.unaccent(nazev || '' '' || oznaceni)); the trigram column matched against the equally normalised query.';
COMMENT ON COLUMN mv_ded_building_search.address_norm IS
	'lower(public.unaccent(...)) over all addresses joined by a space; the trigram column for address matching.';
COMMENT ON COLUMN mv_ded_building_search.search_document IS
	'Weighted tsvector: A = nazev + oznaceni + evidenční jednotka name, B = main address, C = secondary addresses, D = EAN/EIC/OM + gid + evidenční jednotka code.';
COMMENT ON COLUMN mv_ded_building_search.geom IS
	'eno_geometrie.zakres polygons of the GID collected into one MultiPolygon and transformed to EPSG:4326 by f_ded_safe_geom; NULL when no polygon is parsable.';

-- Required by REFRESH MATERIALIZED VIEW CONCURRENTLY.
CREATE UNIQUE INDEX mv_ded_building_search_gid_uidx ON mv_ded_building_search (gid);

CREATE INDEX mv_ded_building_search_document_gin ON mv_ded_building_search USING gin (search_document);

-- GiST rather than GIN on the trigram columns: only GiST supports the <-> distance operator used by
-- the nearest-neighbour candidate generator.
CREATE INDEX mv_ded_building_search_name_trgm ON mv_ded_building_search USING gist (name_norm public.gist_trgm_ops);
CREATE INDEX mv_ded_building_search_address_trgm ON mv_ded_building_search USING gist (address_norm public.gist_trgm_ops);

CREATE INDEX mv_ded_building_search_ean_gin ON mv_ded_building_search USING gin (ean);
CREATE INDEX mv_ded_building_search_eic_gin ON mv_ded_building_search USING gin (eic);
CREATE INDEX mv_ded_building_search_om_gin ON mv_ded_building_search USING gin (om);
