-- TRAIL DPS 4.2.3.3 - Correctif Direction Certifie ++
-- Compatibilite MySQL/MariaDB OVH sans ADD COLUMN IF NOT EXISTS.
-- Objectifs :
-- 1) completer course_feed_direction_auth avec status et used_at,
-- 2) permettre la demande SMS Direction,
-- 3) permettre a PLACE de lister les acces Direction certifies stockes sur OVH.

DELIMITER $$

DROP PROCEDURE IF EXISTS traildps_add_column_if_missing $$
CREATE PROCEDURE traildps_add_column_if_missing(
  IN p_table VARCHAR(64),
  IN p_column VARCHAR(64),
  IN p_definition TEXT
)
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = p_table
      AND COLUMN_NAME = p_column
  ) THEN
    SET @sql = CONCAT('ALTER TABLE `', p_table, '` ADD COLUMN `', p_column, '` ', p_definition);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;
END $$

DROP PROCEDURE IF EXISTS traildps_create_index_if_missing $$
CREATE PROCEDURE traildps_create_index_if_missing(
  IN p_table VARCHAR(64),
  IN p_index VARCHAR(64),
  IN p_definition TEXT
)
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = p_table
      AND INDEX_NAME = p_index
  ) THEN
    SET @sql = CONCAT('ALTER TABLE `', p_table, '` ADD INDEX `', p_index, '` ', p_definition);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;
END $$

DELIMITER ;

CREATE TABLE IF NOT EXISTS course_feed_direction_auth (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  channel_id BIGINT UNSIGNED NOT NULL,
  phone_hash VARCHAR(128) NOT NULL,
  code_hash VARCHAR(255) NOT NULL,
  status VARCHAR(16) NOT NULL DEFAULT 'PENDING',
  attempts INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at DATETIME NOT NULL,
  used_at DATETIME NULL,
  request_ip VARCHAR(64) NULL,
  user_agent VARCHAR(255) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CALL traildps_add_column_if_missing('course_feed_direction_auth', 'status', "VARCHAR(16) NOT NULL DEFAULT 'PENDING'");
CALL traildps_add_column_if_missing('course_feed_direction_auth', 'used_at', 'DATETIME NULL');
CALL traildps_add_column_if_missing('course_feed_direction_auth', 'attempts', 'INT NOT NULL DEFAULT 0');
CALL traildps_add_column_if_missing('course_feed_direction_auth', 'request_ip', 'VARCHAR(64) NULL');
CALL traildps_add_column_if_missing('course_feed_direction_auth', 'user_agent', 'VARCHAR(255) NULL');

CALL traildps_create_index_if_missing('course_feed_direction_auth', 'idx_direction_auth_lookup', '(channel_id, phone_hash, status, expires_at)');
CALL traildps_create_index_if_missing('course_feed_direction_auth', 'idx_direction_auth_expiry', '(expires_at)');

DROP PROCEDURE IF EXISTS traildps_add_column_if_missing;
DROP PROCEDURE IF EXISTS traildps_create_index_if_missing;
