-- 02_performance_indexes.sql
-- Additional indexes for the catalogue search path after the data reload.
-- Idempotent: safe to re-run. Apply with:
--   docker compose exec -T db psql -U user -d mydatabase \
--     < app/backend/src/db/sql/migrations/02_performance_indexes.sql

-- Composite (brand + type) lookups on vehicle_model_year.
-- Used by the matching_vmy CTE once it filters by vehicle_type, and by the
-- importer's findOrCreateVehicleModelYear(brand, model, type) path.
CREATE INDEX IF NOT EXISTS idx_vehicle_model_year_brand_type
  ON vehicle_model_year ("fk_idVehicleBrand", "fk_idVehicleType");

-- Prefix-LIKE search on model scoped to a brand:
--   LOWER(v.model) = LOWER($5) OR LOWER(v.model) LIKE LOWER($5) || ' %'
-- text_pattern_ops is required so the trailing wildcard ' %' can use the index.
CREATE INDEX IF NOT EXISTS idx_vehicle_model_year_brand_model_ltp
  ON vehicle_model_year ("fk_idVehicleBrand", LOWER(model) text_pattern_ops);

-- Brand + model + type equality lookup (importer dedupe + exact match path).
CREATE INDEX IF NOT EXISTS idx_vehicle_model_year_brand_model_type
  ON vehicle_model_year ("fk_idVehicleBrand", model, "fk_idVehicleType");