-- Ajustes de senha/segurança dos usuários.
-- Rode no banco da aplicação antes de usar troca de senha pelo painel.

DELIMITER $$

DROP PROCEDURE IF EXISTS `add_column_if_missing`$$

CREATE PROCEDURE `add_column_if_missing`(
    IN table_name VARCHAR(64),
    IN column_name VARCHAR(64),
    IN column_definition TEXT
)
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = table_name
          AND COLUMN_NAME = column_name
    ) THEN
        SET @sql = CONCAT('ALTER TABLE `', table_name, '` ADD COLUMN `', column_name, '` ', column_definition);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;
END$$

DELIMITER ;

CALL `add_column_if_missing`('users', 'must_change_password', 'TINYINT(1) NOT NULL DEFAULT 0 AFTER `active`');
CALL `add_column_if_missing`('users', 'disabled_at', 'DATETIME NULL AFTER `must_change_password`');
CALL `add_column_if_missing`('users', 'disabled_reason', 'VARCHAR(255) NULL AFTER `disabled_at`');
CALL `add_column_if_missing`('users', 'session_version', 'INT(11) NOT NULL DEFAULT 1 AFTER `disabled_reason`');

DROP PROCEDURE IF EXISTS `add_column_if_missing`;

UPDATE `users`
SET
    `must_change_password` = COALESCE(`must_change_password`, 0),
    `session_version` = COALESCE(`session_version`, 1);

CREATE TABLE IF NOT EXISTS `password_history` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INT(11) UNSIGNED NOT NULL,
    `password_hash` VARCHAR(255) NOT NULL,
    `created_at` DATETIME NULL,
    PRIMARY KEY (`id`),
    KEY `password_history_user_id_foreign` (`user_id`),
    CONSTRAINT `password_history_user_id_foreign`
        FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;