-- GOO-118 — DB query optimization migration
-- Source: GOO-57 audit (plan document: /GOO/issues/GOO-57#document-plan)
--
-- Changes:
--   1. GIN expression index for Property full-text search (addresses M-3)
--      Matches the exact expression used by
--      apps/api/src/modules/search/infrastructure/services/search-query-builder.ts (FTS_COLUMNS).
--
--   2. Partial index on SavedSearch (createdAt) WHERE alertEnabled = true (supports H-1 / H-2)
--      Listener / cron loads rows filtered by alertEnabled = true; partial index
--      lets the query skip the seq scan and stays small (only ~alertEnabled rows).

-- 1) GIN FTS index on Property
CREATE INDEX IF NOT EXISTS "idx_property_fts"
  ON "Property"
  USING GIN (
    to_tsvector(
      'simple',
      coalesce("title", '') || ' ' ||
      coalesce("description", '') || ' ' ||
      coalesce("address", '') || ' ' ||
      coalesce("district", '') || ' ' ||
      coalesce("city", '')
    )
  );

-- 2) Partial index: only alert-enabled saved searches
CREATE INDEX IF NOT EXISTS "idx_savedsearch_alert_enabled"
  ON "SavedSearch" ("createdAt")
  WHERE "alertEnabled" = true;