-- ============================================
-- RajbariIT Player — Telemetry Database Schema
-- Upload to cPanel → phpMyAdmin → Import
-- ============================================

CREATE TABLE IF NOT EXISTS `devices` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `device_id` VARCHAR(36) NOT NULL UNIQUE COMMENT 'UUID generated on first install',
  `device_name` VARCHAR(255) DEFAULT '' COMMENT 'OS hostname',
  `os_name` VARCHAR(50) DEFAULT '' COMMENT 'win32, darwin, linux',
  `os_version` VARCHAR(100) DEFAULT '' COMMENT 'OS release version',
  `app_version` VARCHAR(20) DEFAULT '' COMMENT 'Player version e.g. 1.0.0',
  `arch` VARCHAR(20) DEFAULT '' COMMENT 'x64, arm64, etc.',
  `ip_address` VARCHAR(45) DEFAULT '' COMMENT 'Client IP (IPv4/IPv6)',
  `country` VARCHAR(100) DEFAULT '' COMMENT 'Country from IP (optional)',
  `first_seen` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 'First registration time',
  `last_seen` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last heartbeat time',
  `is_active` TINYINT(1) DEFAULT 1 COMMENT '1=active, 0=disabled by admin',
  `notes` TEXT DEFAULT NULL COMMENT 'Admin notes',
  INDEX `idx_device_id` (`device_id`),
  INDEX `idx_last_seen` (`last_seen`),
  INDEX `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Admin users table for dashboard login
CREATE TABLE IF NOT EXISTS `admin_users` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(50) NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) NOT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default admin user (password: aa11bb22AA11BB22)
-- The password hash is generated with PHP password_hash()
-- We'll set it properly via the setup script