CREATE DATABASE IF NOT EXISTS rsecurity_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE rsecurity_db;

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    username VARCHAR(100) NOT NULL,
    machine_id VARCHAR(255) NOT NULL UNIQUE,
    computer_name VARCHAR(255),
    windows_user VARCHAR(100),
    os VARCHAR(50),
    os_version VARCHAR(100),
    app_version VARCHAR(20),
    registered_at DATETIME NOT NULL,
    last_active DATETIME,
    status ENUM('active', 'blocked', 'pending') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS analytics (
    id INT AUTO_INCREMENT PRIMARY KEY,
    machine_id VARCHAR(255) NOT NULL,
    event_type VARCHAR(50),
    event_data JSON,
    timestamp DATETIME NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
