-- 01_indexes.sql
-- Performance indexes for the catalogue search path.
-- Idempotent: safe to re-run. Apply with:
--   docker compose exec -T db psql -U user -d mydatabase < app/backend/src/db/sql/migrations/01_indexes.sql

-- vehicle dimension lookups
CREATE INDEX IF NOT EXISTS idx_vehicle_brands_brand_l     ON vehicle_brands (LOWER(brand));
CREATE INDEX IF NOT EXISTS idx_vehicle_brands_brand        ON vehicle_brands (brand);
CREATE INDEX IF NOT EXISTS idx_vehicle_model_year_brand   ON vehicle_model_year ("fk_idVehicleBrand");
CREATE INDEX IF NOT EXISTS idx_vehicle_model_year_type    ON vehicle_model_year ("fk_idVehicleType");
CREATE INDEX IF NOT EXISTS idx_vehicle_model_year_model_l ON vehicle_model_year (LOWER(model));

-- spec -> vehicle_model_year joins (N+1 killer)
CREATE INDEX IF NOT EXISTS idx_shock_specs_vmy ON shock_brand_specs ("fk_idVehicleModelYear");
CREATE INDEX IF NOT EXISTS idx_brake_specs_vmy ON brake_brand_specs ("fk_idVehicleModelYear");
CREATE INDEX IF NOT EXISTS idx_disk_specs_vmy  ON disk_brand_specs  ("fk_idVehicleModelYear");
CREATE INDEX IF NOT EXISTS idx_strut_specs_vmy ON strut_brand_specs ("fk_idVehicleModelYear");
CREATE INDEX IF NOT EXISTS idx_leg_specs_vmy   ON leg_brand_specs   ("fk_idVehicleModelYear");

-- part-number lookups (product detail page)
CREATE INDEX IF NOT EXISTS idx_shock_specs_pn ON shock_brand_specs ("partNumber");
CREATE INDEX IF NOT EXISTS idx_brake_specs_pn ON brake_brand_specs ("partNumber");
CREATE INDEX IF NOT EXISTS idx_disk_specs_pn  ON disk_brand_specs  ("partNumber");
CREATE INDEX IF NOT EXISTS idx_strut_specs_pn ON strut_brand_specs ("partNumber");
CREATE INDEX IF NOT EXISTS idx_leg_specs_pn   ON leg_brand_specs   ("partNumber");