
CREATE TABLE `was_stripe_customer` (
  `stripe_customer_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned NOT NULL,
  `customer_id` varchar(64) NOT NULL,
  `insert_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `update_timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`stripe_customer_id`),
  UNIQUE KEY `idx_user_id` (`user_id`),
  UNIQUE KEY `idx_customer_id` (`customer_id`)
) ENGINE=InnoDB COMMENT='Stores stripe customer ids in relation to a WA user';

CREATE TABLE `was_stripe_subscription` (
  `stripe_subscription_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned NOT NULL,
  `subscription_id` varchar(64) NOT NULL,
  `subscription_type` ENUM('MEMBERSHIP') NOT NULL,
  `subscription_status` ENUM('active', 'past_due', 'unpaid', 'canceled', 'incomplete', 'incomplete_expired', 'trialing') NOT NULL,
  `stripe_price_id` varchar(64) DEFAULT NULL,
  `insert_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `update_timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`stripe_subscription_id`),
  KEY `idx_user_id_subscription_type` (`user_id`, `subscription_type`),
  KEY `idx_subscription_id` (`subscription_id`),
  KEY `idx_stripe_price_id` (`stripe_price_id`)
) ENGINE=InnoDB COMMENT='Stores stripe subscription ids in relation to a WA user';

CREATE TABLE `was_stripe_event` (
  `stripe_event_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `event_id` varchar(100) NOT NULL,
  `event_type` varchar(100) NOT NULL,
  `pay_load` text NOT NULL,
  `insert_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `update_timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`stripe_event_id`),
  KEY `idx_event_id` (`event_id`),
  KEY `idx_event_type` (`event_type`)
) ENGINE=InnoDB COMMENT='Stores stripe Webhook event data';

CREATE TRIGGER `was_stripe_customer_insert_timestamp` BEFORE INSERT ON `was_stripe_customer` FOR EACH ROW SET NEW.insert_timestamp = NOW();
CREATE TRIGGER `was_stripe_subscription_insert_timestamp` BEFORE INSERT ON `was_stripe_subscription` FOR EACH ROW SET NEW.insert_timestamp = NOW();
CREATE TRIGGER `was_stripe_event_insert_timestamp` BEFORE INSERT ON `was_stripe_event` FOR EACH ROW SET NEW.insert_timestamp = NOW();


-- Note: The stripe_price_id data below is from our test account
-- These need to be set to live IDs when updating the production DB

ALTER TABLE wa_price
	ADD COLUMN `stripe_price_id` varchar(64) DEFAULT NULL AFTER co_product_id;

UPDATE
	wa_price p
SET
	p.stripe_price_id = CASE 
	WHEN p.price = 49 THEN
		'price_1LdJ4gL1lUydjplKa4zwJ1at'
	WHEN p.price = 497 THEN
		'price_1MN47WL1lUydjplK0VC7DPsb'
	WHEN p.price = 99 THEN
		'price_1LdgDdL1lUydjplKpQ11VTp5'
	WHEN p.price = 697 THEN
		'price_1MN4DYL1lUydjplKStBBDHRY'
	WHEN p.price = 399 AND p.price_plan_id = 6 THEN
		'price_1MN4EKL1lUydjplKNs0T9kJK'
	WHEN p.price = 3990 THEN
		'price_1MN4EVL1lUydjplKdtyn9hED'
	WHEN p.price = 29 THEN
		'price_1MQrG0L1lUydjplK8i1HfQIf'
	WHEN p.price = 29.99 THEN
		'price_1MNJSSL1lUydjplKTHJENjYA'
	WHEN p.price = 39 THEN
		'price_1MQrJQL1lUydjplKOLVzULAe'
	WHEN p.price = 47 THEN
		'price_1MQrK1L1lUydjplKDCZMewWK'
	WHEN p.price = 299 THEN
		'price_1MQrLgL1lUydjplKGxekemuL'
	WHEN p.price = 359 THEN
		'price_1MQrMaL1lUydjplKbmp1S8PR'
	WHEN p.price = 395 THEN
		'price_1MQrN6L1lUydjplK1AxJRIz4'
	WHEN p.price = 399 AND p.price_plan_id = 2 THEN
		'price_1MQrNZL1lUydjplKpPCmpqdU'
	WHEN p.price = 495 THEN
		'price_1MQrOUL1lUydjplKF2IEDFJN'
	WHEN p.price = 499 AND p.price_plan_id = 3 THEN
		'price_1MQrSHL1lUydjplKy20vHgef'
	END
WHERE
	p.price_id in(1, 2, 8, 12, 14, 16, 17, 20, 24, 25, 29, 31, 33, 34, 37, 39, 40, 41, 42, 43, 44, 45, 46, 47);


ALTER TABLE `was_stripe_event`
  ADD `customer_id` VARCHAR(64) NOT NULL AFTER `event_id`,
  ADD `stripe_event_status` ENUM('PROCESSING', 'COMPLETE', 'ERROR') NOT NULL DEFAULT 'PROCESSING' AFTER `pay_load`,
  ADD `stripe_event_error` TEXT NULL DEFAULT NULL AFTER `stripe_event_status`,
  ADD KEY `idx_customer_id` (`customer_id`),
  ADD KEY `idx_stripe_event_status` (`stripe_event_status`);
