-- : SKIP
-- Migration v3.3: Materialized view part_vehicle_preview
-- Purpose: Pre-compute the "most recent vehicle" per part to eliminate
-- DISTINCT ON + 4 JOINs over vehicle_parts (254 GB, 2B+ rows) at query time.
--
-- NOTE: This migration targets the vehicle_database, not tenant databases.
-- The runner skips files marked with ': SKIP' on the first line.
-- To apply manually on the vehicle database, run:
--
--   psql <vehicle_db> -f pos/migrations/v3.3_materialized_view.sql
--
-- (Remove the ': SKIP' line above before manual execution.)

SET statement_timeout = 0;

DROP MATERIALIZED VIEW IF EXISTS part_vehicle_preview;

CREATE MATERIALIZED VIEW part_vehicle_preview AS
SELECT DISTINCT ON (vp.part_id)
       vp.part_id,
       b.name_brand,
       m.name_model,
       y.year_car
FROM vehicle_parts vp
JOIN model_year_engine mye ON mye.id_mye = vp.model_year_engine_id
JOIN models m ON m.id_model = mye.model_id
JOIN brands b ON b.id_brand = m.brand_id
JOIN years y ON y.id_year = mye.year_id
ORDER BY vp.part_id, y.year_car DESC;

CREATE UNIQUE INDEX idx_pvp_part ON part_vehicle_preview(part_id);
CREATE INDEX idx_pvp_brand ON part_vehicle_preview(name_brand);