-- Production pre-build for migration 0257 (reporting / EPC-graph FK indexes).
--
-- The drizzle migrator (src/app/db/migrate.ts) runs migrations inside a
-- transaction, where CREATE INDEX CONCURRENTLY is not allowed. On a live,
-- loaded database, run this file first via psql — it builds the same four
-- indexes without blocking reads or writes — and 0257's
-- CREATE INDEX IF NOT EXISTS then no-ops. On dev/CI databases the tables are
-- small, so skipping this and letting 0257 build them directly is fine.
--
--   psql "$DATABASE_URL" -f src/app/db/sql/prebuild-reporting-indexes.sql
--
-- CONCURRENTLY waits out in-flight transactions between build phases, so
-- long-running reporting queries can stall it — prefer a quiet window. If a
-- build is interrupted it leaves an INVALID index (visible in \d+ <table>);
-- DROP INDEX it and re-run. Note IF NOT EXISTS would also skip an INVALID
-- leftover, so check before re-running.

CREATE INDEX CONCURRENTLY IF NOT EXISTS "ix_epc_building_part_epc_property" ON "epc_building_part" USING btree ("epc_property_id");
CREATE INDEX CONCURRENTLY IF NOT EXISTS "ix_epc_energy_element_epc_property" ON "epc_energy_element" USING btree ("epc_property_id");
CREATE INDEX CONCURRENTLY IF NOT EXISTS "ix_epc_floor_dimension_building_part" ON "epc_floor_dimension" USING btree ("epc_building_part_id");
CREATE INDEX CONCURRENTLY IF NOT EXISTS "ix_property_portfolio" ON "property" USING btree ("portfolio_id");
