
-- We add new columns to the reg_users tables to support improved password security
ALTER TABLE `reg_users` 
ADD COLUMN `user_pwd_hash` VARCHAR(255) NULL AFTER `user_pwd`,
ADD COLUMN `user_pwd_salt` VARCHAR(255) NULL AFTER `user_pwd_hash`;

-- Ads a recovery token field for the reg_users table
ALTER TABLE `reg_users`  ADD COLUMN `recovery_token` VARCHAR(255) NULL AFTER `user_pwd_salt`;

-- Remove not null requirement on reg_users.user_pwd field.
ALTER TABLE `reg_users`  CHANGE COLUMN `user_pwd` `user_pwd` VARCHAR(64) NULL ;

-- Add validation token field to  reg_users_emails
ALTER TABLE `reg_users_emails`  ADD COLUMN `validation_token` VARCHAR(255) NULL AFTER `date_verified`;

-- Added accept_member_token to reg_users_providers
-- ALTER TABLE `reg_users_providers` ADD COLUMN `accept_member_token` VARCHAR(255) NULL AFTER `invite_id`;

-- Added created_at field to terms_and_conditions
ALTER TABLE `terms_and_conditions` ADD COLUMN `created_at` DATETIME NULL AFTER `active`;

-- Added terms_and_conditions_id field to reg_users_tos_agreements
ALTER TABLE `reg_users_tos_agreements` ADD COLUMN `terms_and_conditions_id` INT NULL AFTER `ip_address`;
-- Update the old way of handling TOS for the new and improved one.
UPDATE reg_users_tos_agreements SET terms_and_conditions_id = 2 WHERE agreement_html like '%PROVIDER Generic%';
UPDATE reg_users_tos_agreements SET terms_and_conditions_id = 1 WHERE agreement_html like '%HOLDER Generic%' ;
-- We allow agreement_html to be null
ALTER TABLE `reg_users_tos_agreements` CHANGE COLUMN `agreement_html` `agreement_html` TEXT  NULL ;
-- We remove unnecessary column
ALTER TABLE `reg_users_tos_agreements` DROP COLUMN `agreement_html`;

-- We create a new table for provisional providers
CREATE TABLE `reg_users_providers_provisional` (
  `entity_id` int(11) NOT NULL,
  `provider_id` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `accept_member_token` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`entity_id`,`provider_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- We create a new table for rejected providers
CREATE TABLE `reg_users_providers_rejected` (
  `entity_id` int(11) NOT NULL,
  `provider_id` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `created` DATETIME NOT NULL,
  PRIMARY KEY (`entity_id`,`provider_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- We create a new table for new user providers for those users that have not registered.
CREATE TABLE `new_users_providers` (
  `email_address` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `provider_id` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `role` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `invite_id` varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created` DATETIME  DEFAULT NULL,
  PRIMARY KEY (`email_address`,`provider_id`),
  KEY `invite_id` (`invite_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

