-- Covering partial index for the reporting "where the money goes" aggregate.
-- The query joins the latest plan per property to its recommendations ON
-- plan_id, filters to default = true AND already_installed = false, and sums
-- estimated_cost / counts property_id per measure_type. The plain
-- idx_recommendation_plan_id only locates the rows; the aggregate then
-- heap-fetches every recommendation to apply the filter and read these
-- columns, which times out on large portfolios (e.g. 434). Carrying
-- measure_type, property_id and estimated_cost as key columns makes the whole
-- aggregate index-only.
--
-- OPS: `recommendation` is large, so build this CONCURRENTLY out-of-band on
-- prod BEFORE this migration runs, so the migration no-ops there (matches the
-- 0258 idx_plan_recommendations_recommendation_id pattern). A plain
-- CREATE INDEX here would lock writes on the table. Fresh environments build
-- it inline via IF NOT EXISTS.
--
--   CREATE INDEX CONCURRENTLY IF NOT EXISTS "idx_recommendation_plan_default"
--     ON "recommendation" USING btree ("plan_id","measure_type","property_id","estimated_cost")
--     WHERE "default" = true AND "already_installed" = false;

CREATE INDEX IF NOT EXISTS "idx_recommendation_plan_default" ON "recommendation" USING btree ("plan_id","measure_type","property_id","estimated_cost") WHERE "recommendation"."default" = true AND "recommendation"."already_installed" = false;