/*
  Minimal HERP <-> Electra 2.0 login/session compatibility patch.

  Purpose:
  - Do not add Electra reference columns to every HERP master table.
  - Use existing HERP www_users.hmis_userid to store the Electra users.id UUID.
  - Keep HERP SSO/session flow compatible with sso_login.php, which loads by
    www_users.userid.
  - Add only lookup indexes needed for exact user verification and SSO.

  Exact match used by MasterDataExchangeService:
  - Electra users.id = HERP www_users.hmis_userid
  - SSO token herp_userid = HERP www_users.userid

  Note:
  - www_users.userid is intentionally NOT made unique because existing HERP data
    can contain duplicate userid values.
*/

CREATE TABLE IF NOT EXISTS `sso_tokens` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `token` VARCHAR(64) NOT NULL,
  `herp_userid` VARCHAR(50) NOT NULL,
  `expires_at` DATETIME NOT NULL,
  `used_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ux_sso_tokens_token` (`token`),
  KEY `idx_sso_tokens_herp_userid` (`herp_userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP PROCEDURE IF EXISTS herp_modify_column_if_exists;
DROP PROCEDURE IF EXISTS herp_add_index_if_missing;

DELIMITER $$

CREATE PROCEDURE herp_modify_column_if_exists(
  IN p_table_name VARCHAR(64),
  IN p_column_name VARCHAR(64),
  IN p_column_definition TEXT
)
BEGIN
  IF EXISTS (
    SELECT 1
    FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name = p_table_name
      AND column_name = p_column_name
  ) THEN
    SET @ddl = CONCAT('ALTER TABLE `', p_table_name, '` MODIFY COLUMN `', p_column_name, '` ', p_column_definition);
    PREPARE stmt FROM @ddl;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;
END$$

CREATE PROCEDURE herp_add_index_if_missing(
  IN p_table_name VARCHAR(64),
  IN p_index_name VARCHAR(64),
  IN p_index_definition TEXT
)
BEGIN
  IF EXISTS (
    SELECT 1
    FROM information_schema.tables
    WHERE table_schema = DATABASE()
      AND table_name = p_table_name
  )
  AND NOT EXISTS (
    SELECT 1
    FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = p_table_name
      AND index_name = p_index_name
  ) THEN
    SET @ddl = CONCAT('ALTER TABLE `', p_table_name, '` ADD ', p_index_definition);
    PREPARE stmt FROM @ddl;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;
END$$

DELIMITER ;

CALL herp_modify_column_if_exists('sso_tokens', 'herp_userid', 'VARCHAR(50) NOT NULL');
CALL herp_modify_column_if_exists('www_users', 'hmis_userid', 'VARCHAR(36) NULL');

CALL herp_add_index_if_missing('sso_tokens', 'idx_sso_tokens_herp_userid', 'KEY `idx_sso_tokens_herp_userid` (`herp_userid`)');
CALL herp_add_index_if_missing('www_users', 'idx_www_users_userid', 'KEY `idx_www_users_userid` (`userid`)');
CALL herp_add_index_if_missing('www_users', 'idx_www_users_hmis_userid', 'KEY `idx_www_users_hmis_userid` (`hmis_userid`)');

DROP PROCEDURE IF EXISTS herp_modify_column_if_exists;
DROP PROCEDURE IF EXISTS herp_add_index_if_missing;
