BEGIN;

ALTER TABLE customer_requests ADD COLUMN IF NOT EXISTS min_model_year smallint;
ALTER TABLE customer_requests ADD COLUMN IF NOT EXISTS max_model_year smallint;
ALTER TABLE customer_requests ADD COLUMN IF NOT EXISTS minimum_mileage integer;
ALTER TABLE customer_requests ADD COLUMN IF NOT EXISTS minimum_budget_cents integer;

ALTER TABLE opportunities ADD COLUMN IF NOT EXISTS min_model_year smallint;
ALTER TABLE opportunities ADD COLUMN IF NOT EXISTS max_model_year smallint;
ALTER TABLE opportunities ADD COLUMN IF NOT EXISTS minimum_mileage integer;
ALTER TABLE opportunities ADD COLUMN IF NOT EXISTS minimum_budget_cents integer;
ALTER TABLE opportunities ADD COLUMN IF NOT EXISTS maximum_budget_cents integer;

DROP VIEW IF EXISTS dealer_opportunity_view;
CREATE VIEW dealer_opportunity_view AS
SELECT
  oa.id AS assignment_id,
  oa.dealership_id,
  oa.status AS assignment_status,
  oa.assigned_at,
  o.id AS opportunity_id,
  o.condition,
  o.model_year,
  o.make,
  o.model,
  o.trim,
  o.exterior_color,
  o.interior_color,
  o.must_have_options,
  o.maximum_mileage,
  o.buyer_market,
  o.response_deadline,
  o.status AS opportunity_status,
  o.min_model_year,
  o.max_model_year,
  o.minimum_mileage,
  o.minimum_budget_cents,
  o.maximum_budget_cents
FROM opportunity_assignments oa
JOIN opportunities o ON o.id = oa.opportunity_id;

COMMIT;