USE `registration`;
CREATE TABLE `order` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `reference` varchar(50) NOT NULL,
  `store` varchar(10) NOT NULL,
  `referrer` varchar(500) DEFAULT NULL,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `company` varchar(100) DEFAULT NULL,
  `address_line_1` varchar(100) DEFAULT NULL,
  `address_line_2` varchar(100) DEFAULT NULL,
  `city` varchar(60) DEFAULT NULL,
  `region` varchar(60) DEFAULT NULL,
  `country` varchar(100) DEFAULT NULL,
  `postcode` varchar(10) DEFAULT NULL,
  `email` varchar(100) NOT NULL,
  `phone` varchar(15) DEFAULT NULL,
  `language` varchar(2) NOT NULL DEFAULT 'en',
  `currency` varchar(3) DEFAULT NULL,
  `subtotal` decimal(10,2) DEFAULT NULL,
  `total` decimal(10,2) DEFAULT NULL,
  `total_usd` decimal(10,2) NOT NULL,
  `discount` decimal(10,2) DEFAULT NULL,
  `refund` decimal(10,2) DEFAULT NULL,
  `refund_usd` decimal(10,2) DEFAULT NULL,
  `coupons` varchar(200) DEFAULT NULL COMMENT 'Coupon Code entered by user',
  `products` varchar(1000) DEFAULT NULL,
  `is_test` tinyint(1) DEFAULT '0',
  `date` timestamp NULL DEFAULT NULL,
  `return_code` varchar(20) DEFAULT NULL COMMENT 'Code returned by FastSpring',
  `return_note` varchar(200) DEFAULT NULL COMMENT 'Text entered by admin (us) during refund.',
  PRIMARY KEY (`id`),
  UNIQUE KEY `reference` (`reference`),
  KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8;


-- Changes for SmallCubed Subscription (08 Oct 2020)

ALTER TABLE `order`
	ADD COLUMN `domain` VARCHAR(100) DEFAULT '' AFTER `email`,
	ADD COLUMN `email_hash` VARCHAR(100) DEFAULT '' AFTER `domain`,
	ADD COLUMN `email_enc` VARCHAR(250) DEFAULT '' AFTER `email_hash`,
	MODIFY COLUMN `first_name` VARCHAR(100) DEFAULT '',
	MODIFY COLUMN `last_name` VARCHAR(100) DEFAULT '',
	MODIFY COLUMN `email` VARCHAR(100) DEFAULT '',
	ADD INDEX `email_hash_idx` (`email_hash`);


-- Changes for SmallCubed Subscription (23 Oct 2020)

ALTER TABLE `order`
  MODIFY COLUMN `return_code` VARCHAR(100) DEFAULT NULL,
  MODIFY COLUMN `date` timestamp NULL DEFAULT CURRENT_TIMESTAMP;

--  Changes for purging clear text email addresses (17 February 2022)

ALTER TABLE `registration`.`order`
  DROP COLUMN `address_line_1`,
  DROP COLUMN `address_line_2`,
  DROP COLUMN `city`,
  DROP COLUMN `region`,
  DROP COLUMN `country`,
  DROP COLUMN `postcode`,
  DROP COLUMN `email`,
  DROP COLUMN `phone`;