-- Rebuild the 3 secondary indexes on `recommendation` after a backfill window,
-- restoring the app to its normal (pre-backfill) query behaviour.
--
-- The definitions below reproduce exactly what the migrations created (partial
-- WHERE clauses included). Run any time — the backfill does NOT need to be
-- complete first: these indexes don't reference plan_id/material_id, so a
-- half-finished backfill is invisible to them and to the app.
--
-- Two of the three are PARTIAL (only rows where default = true and not yet
-- installed), so they cover a small slice of the table and rebuild in seconds;
-- only recommendation_property_id_idx spans all rows and takes a few minutes.
--
-- Plain CREATE INDEX is fastest but briefly blocks writes while each builds. If
-- you need writes to keep flowing, replace CREATE INDEX with
-- CREATE INDEX CONCURRENTLY (slower, cannot run inside a transaction, and each
-- statement must be sent on its own).

-- 1GB comfortably holds the ~500MB sort for a 21M-row single-column btree.
-- Keep it well under instance RAM (this DB is 4GB) to avoid memory pressure.
SET maintenance_work_mem = '1GB';

CREATE INDEX IF NOT EXISTS recommendation_property_id_idx
  ON recommendation USING btree (property_id);

CREATE INDEX IF NOT EXISTS idx_recommendation_active_defaults
  ON recommendation USING btree (id)
  WHERE "default" = true AND already_installed = false;

CREATE INDEX IF NOT EXISTS idx_recommendation_active_id_property
  ON recommendation USING btree (id, property_id)
  WHERE "default" = true AND already_installed = false;
