-- Gift cards: expiry date and a reason when voided
ALTER TABLE gift_cards ADD COLUMN expires_at DATE NULL AFTER deliver_at, ADD COLUMN void_reason VARCHAR(200) NULL AFTER expires_at;

-- Discount codes: cap, first-order-only, category limits, an internal note
ALTER TABLE discounts ADD COLUMN max_discount DECIMAL(10,2) NULL AFTER value,
  ADD COLUMN first_order_only TINYINT(1) NOT NULL DEFAULT 0 AFTER per_customer,
  ADD COLUMN category_ids VARCHAR(255) NULL AFTER first_order_only,
  ADD COLUMN note VARCHAR(200) NULL AFTER origin;

-- Orders: where it came from, how it was paid, a manual extra fee. Unpaid manual orders must not expire like abandoned checkouts.
ALTER TABLE orders ADD COLUMN source VARCHAR(20) NOT NULL DEFAULT 'website' AFTER status,
  ADD COLUMN payment_status VARCHAR(20) NOT NULL DEFAULT 'paid' AFTER source,
  ADD COLUMN payment_method VARCHAR(30) NULL AFTER payment_status,
  ADD COLUMN extra_fee DECIMAL(10,2) NOT NULL DEFAULT 0 AFTER discount,
  ADD COLUMN created_by INT UNSIGNED NULL AFTER placed_ip;

-- Returns: what staff told the customer, how it ended
ALTER TABLE returns ADD COLUMN admin_message TEXT NULL AFTER customer_message,
  ADD COLUMN credit_code VARCHAR(24) NULL AFTER admin_message,
  ADD COLUMN replacement_order_id INT UNSIGNED NULL AFTER credit_code,
  ADD COLUMN received_at DATETIME NULL AFTER replacement_order_id;

INSERT INTO migrations (name) VALUES ('002_money_features');
