-- JMS AUTH DATABASE

CREATE TABLE roles (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(100) NOT NULL UNIQUE,
 slug VARCHAR(100) NOT NULL UNIQUE,
 description VARCHAR(255) NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE permissions (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(150) NOT NULL UNIQUE,
 slug VARCHAR(150) NOT NULL UNIQUE,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE role_permissions (
 role_id INT UNSIGNED NOT NULL,
 permission_id INT UNSIGNED NOT NULL,
 PRIMARY KEY(role_id, permission_id)
);

CREATE TABLE users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 uuid CHAR(36) NOT NULL UNIQUE,
 role_id INT UNSIGNED NOT NULL,
 name VARCHAR(100) NOT NULL,
 email VARCHAR(255) NOT NULL UNIQUE,
 phone VARCHAR(20) NULL UNIQUE,
 password VARCHAR(255) NOT NULL,
 status ENUM('pending','active','suspended','banned') DEFAULT 'active',
 email_verified_at DATETIME NULL,
 last_login_at DATETIME NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE user_sessions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_uuid CHAR(36) NOT NULL,
 session_id VARCHAR(255) NOT NULL,
 ip_address VARCHAR(45),
 user_agent TEXT,
 last_activity DATETIME,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE user_tokens (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_uuid CHAR(36) NOT NULL,
 access_token VARCHAR(255) NOT NULL,
 refresh_token VARCHAR(255) NOT NULL,
 expires_at DATETIME NOT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE audit_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_uuid CHAR(36) NULL,
 action VARCHAR(255) NOT NULL,
 ip_address VARCHAR(45),
 details TEXT,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE user_devices (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_uuid CHAR(36) NOT NULL,
 device_name VARCHAR(255),
 ip_address VARCHAR(45),
 last_seen DATETIME,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);