-- قاعدة بيانات نظام الترخيص
-- للاستخدام على api.boxpluse.com

-- جدول المستخدمين والتراخيص
CREATE TABLE IF NOT EXISTS `license_users` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(100) NOT NULL UNIQUE,
  `email` VARCHAR(255) NOT NULL UNIQUE,
  `serial_key` VARCHAR(50) NOT NULL UNIQUE,
  `machine_id` VARCHAR(255) DEFAULT NULL,
  `license_type` ENUM('monthly', 'yearly', 'lifetime') NOT NULL DEFAULT 'monthly',
  `status` ENUM('active', 'expired', 'suspended') NOT NULL DEFAULT 'active',
  `expires_at` DATETIME DEFAULT NULL,
  `activated_at` DATETIME DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_serial` (`serial_key`),
  INDEX `idx_machine` (`machine_id`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول سجل التفعيل
CREATE TABLE IF NOT EXISTS `license_activations` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `machine_id` VARCHAR(255) NOT NULL,
  `ip_address` VARCHAR(45) NOT NULL,
  `activated_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `license_users`(`id`) ON DELETE CASCADE,
  INDEX `idx_user` (`user_id`),
  INDEX `idx_machine` (`machine_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول سجل النشاط (Heartbeat)
CREATE TABLE IF NOT EXISTS `license_activity_log` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `machine_id` VARCHAR(255) NOT NULL,
  `ip_address` VARCHAR(45) NOT NULL,
  `last_seen` DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `license_users`(`id`) ON DELETE CASCADE,
  INDEX `idx_user` (`user_id`),
  INDEX `idx_last_seen` (`last_seen`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول الإحصائيات اليومية
CREATE TABLE IF NOT EXISTS `license_daily_stats` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `date` DATE NOT NULL UNIQUE,
  `total_users` INT DEFAULT 0,
  `active_users` INT DEFAULT 0,
  `online_users` INT DEFAULT 0,
  `new_activations` INT DEFAULT 0,
  `revenue` DECIMAL(10, 2) DEFAULT 0.00,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
