-- redo the status relationships
ALTER TABLE opportunities ADD status_id INT;

UPDATE opportunities SET status_id = 
CASE opportunities.status
WHEN 'Awaiting Quote' or 'awaiting_quote' THEN 1
WHEN 'Quoted' or 'quoted' THEN 2
WHEN 'In Design' or 'in_design' THEN 3
WHEN 'Out For Approval' or 'approval' THEN 4
WHEN 'Approved (toolpath)' or 'approved' THEN 5
WHEN 'In Production' or 'in_production' THEN 6
WHEN 'Partially Shipped' or 'partially_shipped' THEN 7
WHEN 'Fully Shipped' or 'fully_shipped' THEN 8
WHEN 'On Hold (customer)' or 'on_hold_customer' THEN 9
WHEN 'On Hold (datum)' or 'on_hold_datum' THEN 10
WHEN 'Dead' or 'dead' THEN 11
END;