-- Section 3: tier override, referral switch, badge rules and colours, account deletion requests.
ALTER TABLE users
  ADD COLUMN tier_override VARCHAR(40) NULL,
  ADD COLUMN referrals_off TINYINT(1) NOT NULL DEFAULT 0,
  ADD COLUMN delete_requested_at DATETIME NULL;

ALTER TABLE badges
  ADD COLUMN color_from CHAR(7) NOT NULL DEFAULT '#3FC5D6',
  ADD COLUMN color_to   CHAR(7) NOT NULL DEFAULT '#4F7BEA',
  ADD COLUMN rule_type  ENUM('manual','orders','spent','points','reviews','referrals') NOT NULL DEFAULT 'manual',
  ADD COLUMN rule_n     INT NOT NULL DEFAULT 0,
  ADD COLUMN sort_order INT NOT NULL DEFAULT 0;

ALTER TABLE user_badges ADD COLUMN awarded_by INT UNSIGNED NULL;

INSERT IGNORE INTO badges (code,name,description,icon,category,color_from,color_to,rule_type,rule_n,sort_order) VALUES
 ('first','First order','Place your first order.','box','orders','#3FC5D6','#4F7BEA','orders',1,1),
 ('review','Reviewer','Leave a review.','star','community','#FFC15A','#F0705A','reviews',1,2),
 ('quote','Maker','Request a custom quote.','bolt','community','#5BD08B','#3FC5D6','manual',0,3),
 ('repeat','Regular','Place 3 orders.','heart','orders','#B68BFF','#4F7BEA','orders',3,4),
 ('ref','Ambassador','Refer a friend who orders.','crown','community','#F28BB7','#B68BFF','referrals',1,5),
 ('big','Big spender','Spend $250 in total.','gem','orders','#FF9C8F','#E8A93A','spent',250,6),
 ('bday','Birthday print','Add your birthday to your profile.','flame','community','#8CE6C2','#3FA8D6','manual',0,7),
 ('silver','Silver tier','Reach 1,000 points.','leaf','rewards','#9FB8C9','#4A6A86','points',1000,8);

INSERT INTO migrations (name) VALUES ('009_customer_features');
