USE `registration`;
CREATE TABLE `subscription` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `subscription_type` varchar(10) NOT NULL,
  `sub_reference` varchar(50) NOT NULL,
  `referrer` varchar(500) DEFAULT NULL,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `company` varchar(100) DEFAULT NULL,
  `email` varchar(100) NOT NULL,
  `email_hash` varchar(50) NOT NULL DEFAULT '',
  `customer_url` varchar(100) NOT NULL,
  `product_name` varchar(10) DEFAULT NULL,
  `quantity` int(11) NOT NULL,
  `license_key` varchar(30) NOT NULL DEFAULT '',
  `status` varchar(10) NOT NULL,
  `status_reason` varchar(20) DEFAULT '',
  `end_date` timestamp NULL DEFAULT NULL,
  `is_test` tinyint(1) DEFAULT 0,
  `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `date_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email_sub_ref` (`email`,`sub_reference`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8;


-- Changes for SmallCubed Subscription (09 Sept 2020)

ALTER TABLE `subscription`
	ADD COLUMN `domain` VARCHAR(100) DEFAULT '' AFTER `email`,
	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 '',
	MODIFY COLUMN `email_hash` VARCHAR(100) DEFAULT '',
	MODIFY COLUMN `product_name` VARCHAR(100) DEFAULT NULL,
  MODIFY COLUMN `license_key` varchar(50) NOT NULL DEFAULT '',
	MODIFY COLUMN `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	ADD INDEX `email_hash_idx` (`email_hash`),
	ADD INDEX `license_key_idx` (`license_key`);

--  Changes for Subscription (20 October 2020)

ALTER TABLE `subscription`
  MODIFY COLUMN `status_reason` VARCHAR(100) DEFAULT '',
  DROP KEY `email_sub_ref`,
  ADD INDEX `sub_ref_idx` (`sub_reference`);
  
--  Changes for FS Subscription Updating (13 November 2020)

ALTER TABLE `subscription`
  ADD COLUMN `update_fs_next_date` tinyint(1) DEFAULT 0;
  
--  Changes for FS Subscription AutoRenew checking (08 November 2021)

ALTER TABLE `subscription`
  ADD COLUMN `auto_renews` VARCHAR(10) DEFAULT NULL; 
  
--  Changes for FS Account Customer URL (22 November 2021)

ALTER TABLE `subscription`
  ADD COLUMN `fs_account_id` VARCHAR(30) DEFAULT NULL; 

--  Changes for Handling Coupon/Discount for Subs (31 January 2022)

ALTER TABLE `registration`.`subscription`
  ADD COLUMN `coupon_type` VARCHAR(50) DEFAULT NULL;

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

ALTER TABLE `registration`.`subscription`
  DROP COLUMN `email`;