BEGIN;

ALTER TABLE customer_requests ADD COLUMN IF NOT EXISTS exterior_color_flexible boolean;
ALTER TABLE opportunities ADD COLUMN IF NOT EXISTS exterior_color_flexible boolean;

-- Preserve specified colours on existing requests while making every blank colour flexible.
UPDATE customer_requests
SET exterior_color_flexible = nullif(trim(exterior_color), '') IS NULL
WHERE exterior_color_flexible IS NULL;

UPDATE opportunities
SET exterior_color_flexible = nullif(trim(exterior_color), '') IS NULL
WHERE exterior_color_flexible IS NULL;

ALTER TABLE customer_requests ALTER COLUMN exterior_color_flexible SET DEFAULT true;
ALTER TABLE customer_requests ALTER COLUMN exterior_color_flexible SET NOT NULL;
ALTER TABLE opportunities ALTER COLUMN exterior_color_flexible SET DEFAULT true;
ALTER TABLE opportunities ALTER COLUMN exterior_color_flexible SET NOT NULL;

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.exterior_color_flexible,
  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;