-- =============================================================================
-- Search performance indexes — adultwork2
-- =============================================================================
-- Run these against the production Supabase Postgres ONE STATEMENT AT A TIME.
-- CREATE INDEX CONCURRENTLY:
--   * cannot run inside a transaction (Supabase SQL editor's "Run" button wraps
--     multi-statement scripts in a transaction by default — paste & run each
--     statement individually, OR use psql with `\set AUTOCOMMIT on`).
--   * does not lock the table for writes (safe on a live DB).
--   * is slower and uses more disk than a regular CREATE INDEX, but is the
--     only safe option for production tables that are actively being queried.
--
-- After this is run, the matching @@index lines in prisma/schema.prisma keep
-- the schema in sync, so a future `prisma db pull` won't drop them.
-- =============================================================================

-- Characteristic table — every search filter that selects on these columns
-- currently does a full table scan. With ~N escorts, even one filter is O(N).
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_characteristics_gender_id
  ON characteristics (gender_id);

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_characteristics_age_id
  ON characteristics (age_id);

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_characteristics_orientation_id
  ON characteristics (orientation_id);

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_characteristics_ethnicity_id
  ON characteristics (ethnicity_id);

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_characteristics_nationality_id
  ON characteristics (nationality_id);

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_characteristics_height_id
  ON characteristics (height_id);

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_characteristics_weight_id
  ON characteristics (weight_id);

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_characteristics_hair_color_id
  ON characteristics (hair_color_id);

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_characteristics_eye_color_id
  ON characteristics (eye_color_id);

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_characteristics_breast_size_id
  ON characteristics (breast_size_id);

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_characteristics_breast_state_id
  ON characteristics (breast_state_id);

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_characteristics_cup_size_id
  ON characteristics (cup_size_id);

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_characteristics_smoking_id
  ON characteristics (smoking_id);

-- Composite for the most common combination (gender + age) to support
-- index-only scans when both filters are present.
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_characteristics_gender_age
  ON characteristics (gender_id, age_id);

-- enjoy_user: search filters by enjoy_id directly. The existing UNIQUE
-- constraint indexes (user_id, enjoy_id) — usable for user_id-prefixed
-- queries only, NOT for enjoy_id-only lookups.
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_enjoy_user_enjoy_id
  ON enjoy_user (enjoy_id);