-- Migration: Création de la table users
-- Date: 2025-09-09
-- Description: Table des utilisateurs avec rôles et sécurité renforcée

CREATE TABLE `users` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `email` varchar(255) NOT NULL,
    `password` varchar(255) NOT NULL,
    `first_name` varchar(100) NOT NULL,
    `last_name` varchar(100) NOT NULL,
    `role` enum('admin', 'editor', 'client') NOT NULL DEFAULT 'client',
    `status` enum('active', 'inactive', 'suspended') NOT NULL DEFAULT 'active',
    `email_verified_at` timestamp NULL DEFAULT NULL,
    `last_login_at` timestamp NULL DEFAULT NULL,
    `login_attempts` int(11) NOT NULL DEFAULT 0,
    `locked_until` timestamp NULL DEFAULT NULL,
    `two_factor_secret` varchar(255) NULL DEFAULT NULL,
    `two_factor_enabled` tinyint(1) NOT NULL DEFAULT 0,
    `api_token` varchar(255) NULL DEFAULT NULL,
    `remember_token` varchar(100) NULL DEFAULT NULL,
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `users_email_unique` (`email`),
    UNIQUE KEY `users_api_token_unique` (`api_token`),
    KEY `users_role_index` (`role`),
    KEY `users_status_index` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table pour les sessions
CREATE TABLE `sessions` (
    `id` varchar(255) NOT NULL,
    `user_id` bigint(20) UNSIGNED NULL DEFAULT NULL,
    `ip_address` varchar(45) NULL DEFAULT NULL,
    `user_agent` text NULL DEFAULT NULL,
    `payload` longtext NOT NULL,
    `last_activity` int(11) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `sessions_user_id_index` (`user_id`),
    KEY `sessions_last_activity_index` (`last_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table pour les réinitialisations de mot de passe
CREATE TABLE `password_resets` (
    `email` varchar(255) NOT NULL,
    `token` varchar(255) NOT NULL,
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY `password_resets_email_index` (`email`),
    KEY `password_resets_token_index` (`token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;