-- Align user_equipment with the nozzle-aware equipment routes.

ALTER TABLE user_equipment
ADD COLUMN IF NOT EXISTS orifice_size VARCHAR(20),
ADD COLUMN IF NOT EXISTS spray_angle INTEGER,
ADD COLUMN IF NOT EXISTS flow_rate_gpm DECIMAL(6, 3),
ADD COLUMN IF NOT EXISTS droplet_size VARCHAR(50),
ADD COLUMN IF NOT EXISTS spray_pattern VARCHAR(50),
ADD COLUMN IF NOT EXISTS pressure_range_psi VARCHAR(50),
ADD COLUMN IF NOT EXISTS thread_size VARCHAR(20),
ADD COLUMN IF NOT EXISTS material VARCHAR(50),
ADD COLUMN IF NOT EXISTS color_code VARCHAR(50),
ADD COLUMN IF NOT EXISTS quantity_owned INTEGER DEFAULT 1;

CREATE INDEX IF NOT EXISTS idx_user_equipment_nozzle_specs
  ON user_equipment(orifice_size, droplet_size, spray_angle)
  WHERE orifice_size IS NOT NULL;