USE `minasvale_producao`;

DELIMITER $$

DROP PROCEDURE IF EXISTS `add_column_if_missing`$$

CREATE PROCEDURE `add_column_if_missing`(
    IN table_name_param VARCHAR(64),
    IN column_name_param VARCHAR(64),
    IN column_definition_param TEXT
)
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = table_name_param
          AND COLUMN_NAME = column_name_param
    ) THEN
        SET @ddl = CONCAT(
            'ALTER TABLE `',
            REPLACE(table_name_param, '`', '``'),
            '` ADD COLUMN `',
            REPLACE(column_name_param, '`', '``'),
            '` ',
            column_definition_param
        );
        PREPARE stmt FROM @ddl;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;
END$$

DELIMITER ;

CALL `add_column_if_missing`('inspections', 'inspection_type', 'ENUM(''DER'', ''Escolar'') NULL AFTER `plate`');
CALL `add_column_if_missing`('inspection_evidences', 'rotation_degrees', 'SMALLINT(4) NOT NULL DEFAULT 0 AFTER `file_size`');

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`;

CREATE TABLE IF NOT EXISTS `roles` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `slug` VARCHAR(80) NOT NULL,
    `name` VARCHAR(120) NOT NULL,
    `active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NULL,
    `updated_at` DATETIME NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `roles_slug_unique` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `permissions` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `slug` VARCHAR(120) NOT NULL,
    `description` VARCHAR(255) NOT NULL,
    `created_at` DATETIME NULL,
    `updated_at` DATETIME NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `permissions_slug_unique` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `role_permissions` (
    `role_id` INT(11) UNSIGNED NOT NULL,
    `permission_id` INT(11) UNSIGNED NOT NULL,
    `created_at` DATETIME NULL,
    PRIMARY KEY (`role_id`, `permission_id`),
    CONSTRAINT `role_permissions_role_id_foreign` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `role_permissions_permission_id_foreign` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `user_permissions` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INT(11) UNSIGNED NOT NULL,
    `permission_id` INT(11) UNSIGNED NOT NULL,
    `granted` TINYINT(1) NOT NULL DEFAULT 1,
    `changed_by` INT(11) UNSIGNED NULL,
    `created_at` DATETIME NULL,
    `updated_at` DATETIME NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `user_permissions_user_permission_unique` (`user_id`, `permission_id`),
    KEY `user_permissions_permission_id_foreign` (`permission_id`),
    CONSTRAINT `user_permissions_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `user_permissions_permission_id_foreign` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `user_mfa` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INT(11) UNSIGNED NOT NULL,
    `totp_secret_ciphertext` TEXT NOT NULL,
    `totp_secret_nonce` VARCHAR(80) NOT NULL,
    `key_id` VARCHAR(80) NOT NULL,
    `key_version` INT(11) NOT NULL,
    `active` TINYINT(1) NOT NULL DEFAULT 0,
    `activated_at` DATETIME NULL,
    `last_used_at` DATETIME NULL,
    `created_at` DATETIME NULL,
    `updated_at` DATETIME NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `user_mfa_user_id_unique` (`user_id`),
    CONSTRAINT `user_mfa_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;

CREATE TABLE IF NOT EXISTS `user_mfa_recovery_codes` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INT(11) UNSIGNED NOT NULL,
    `code_hash` VARCHAR(255) NOT NULL,
    `used_at` DATETIME NULL,
    `created_at` DATETIME NULL,
    PRIMARY KEY (`id`),
    KEY `user_mfa_recovery_codes_user_id_foreign` (`user_id`),
    CONSTRAINT `user_mfa_recovery_codes_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;

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;

CREATE TABLE IF NOT EXISTS `login_attempts` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `email` VARCHAR(191) NULL,
    `ip_address` VARCHAR(64) NOT NULL,
    `purpose` VARCHAR(30) NOT NULL,
    `success` TINYINT(1) NOT NULL DEFAULT 0,
    `created_at` DATETIME NULL,
    PRIMARY KEY (`id`),
    KEY `login_attempts_lookup` (`ip_address`, `purpose`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `audit_logs` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `uuid` VARCHAR(36) NOT NULL,
    `user_id` INT(11) UNSIGNED NULL,
    `action` VARCHAR(120) NOT NULL,
    `resource` VARCHAR(190) NULL,
    `result` VARCHAR(30) NOT NULL,
    `metadata_json` TEXT NULL,
    `ip_address` VARCHAR(64) NULL,
    `user_agent` VARCHAR(255) NULL,
    `request_id` VARCHAR(80) NULL,
    `previous_hash` CHAR(64) NOT NULL,
    `current_hash` CHAR(64) NOT NULL,
    `created_at` DATETIME NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `audit_logs_uuid_unique` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `backup_restore_tests` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `backup_type` VARCHAR(80) NOT NULL,
    `artifact_hash` VARCHAR(128) NOT NULL,
    `responsible_user_id` INT(11) UNSIGNED NULL,
    `isolated_environment` VARCHAR(150) NOT NULL,
    `result` VARCHAR(30) NOT NULL,
    `restoration_seconds` INT(11) NULL,
    `evidence` TEXT NULL,
    `tested_at` DATETIME NOT NULL,
    `created_at` DATETIME NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `security_validation_results` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `requirement` VARCHAR(80) NOT NULL,
    `status` ENUM('ok', 'fail') NOT NULL,
    `message` TEXT NULL,
    `checked_at` DATETIME NOT NULL,
    PRIMARY KEY (`id`),
    KEY `security_validation_results_requirement` (`requirement`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `roles` (`slug`, `name`, `active`, `created_at`, `updated_at`)
VALUES ('admin', 'Administrador', 1, NOW(), NOW())
ON DUPLICATE KEY UPDATE
    `name` = VALUES(`name`),
    `active` = VALUES(`active`),
    `updated_at` = NOW();

INSERT INTO `permissions` (`slug`, `description`, `created_at`, `updated_at`)
VALUES
    ('senatran.query.vehicle', 'Consultar veículo na SENATRAN', NOW(), NOW()),
    ('senatran.view.full_data', 'Visualizar dados completos da SENATRAN', NOW(), NOW()),
    ('senatran.export', 'Exportar dados da SENATRAN', NOW(), NOW()),
    ('senatran.audit.view', 'Visualizar auditoria da SENATRAN', NOW(), NOW()),
    ('certificate.manage', 'Gerenciar certificado digital', NOW(), NOW()),
    ('integration.manage', 'Configurar integração', NOW(), NOW()),
    ('user.manage', 'Gerenciar usuários', NOW(), NOW()),
    ('role.manage', 'Gerenciar perfis e permissões', NOW(), NOW())
ON DUPLICATE KEY UPDATE
    `description` = VALUES(`description`),
    `updated_at` = NOW();

INSERT IGNORE INTO `role_permissions` (`role_id`, `permission_id`, `created_at`)
SELECT r.`id`, p.`id`, NOW()
FROM `roles` r
CROSS JOIN `permissions` p
WHERE r.`slug` = 'admin';

INSERT IGNORE INTO `user_permissions` (`user_id`, `permission_id`, `granted`, `created_at`, `updated_at`)
SELECT u.`id`, p.`id`, 1, NOW(), NOW()
FROM `users` u
CROSS JOIN `permissions` p
WHERE u.`role` = 'admin'
  AND u.`active` = 1;