-- ============================================
-- Employee Monitor - Database Schema
-- Import this via cPanel phpMyAdmin
-- ============================================

CREATE DATABASE IF NOT EXISTS `employee_monitor` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `employee_monitor`;

-- Employees table
CREATE TABLE `employees` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `employee_id` VARCHAR(50) NOT NULL UNIQUE,
  `password` VARCHAR(255) NOT NULL,
  `full_name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(150),
  `department` VARCHAR(100),
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Admin users table
CREATE TABLE `admins` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(50) NOT NULL UNIQUE,
  `password` VARCHAR(255) NOT NULL,
  `full_name` VARCHAR(100),
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Work sessions (login/logout)
CREATE TABLE `sessions` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `employee_id` VARCHAR(50) NOT NULL,
  `session_token` VARCHAR(255) NOT NULL,
  `login_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `logout_time` DATETIME NULL,
  `total_active_seconds` INT DEFAULT 0,
  `total_idle_seconds` INT DEFAULT 0,
  `ip_address` VARCHAR(45),
  `computer_name` VARCHAR(100),
  INDEX `idx_employee_id` (`employee_id`),
  INDEX `idx_login_time` (`login_time`)
) ENGINE=InnoDB;

-- Screenshots
CREATE TABLE `screenshots` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `employee_id` VARCHAR(50) NOT NULL,
  `session_id` INT NOT NULL,
  `filename` VARCHAR(255) NOT NULL,
  `captured_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `file_size` INT DEFAULT 0,
  INDEX `idx_employee_id` (`employee_id`),
  INDEX `idx_captured_at` (`captured_at`)
) ENGINE=InnoDB;

-- Activity logs (app/URL tracking + idle)
CREATE TABLE `activity_logs` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `employee_id` VARCHAR(50) NOT NULL,
  `session_id` INT NOT NULL,
  `log_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `active_window` VARCHAR(500),
  `active_app` VARCHAR(200),
  `active_url` VARCHAR(1000),
  `status` ENUM('active','idle') DEFAULT 'active',
  `mouse_clicks` INT DEFAULT 0,
  `keystrokes` INT DEFAULT 0,
  INDEX `idx_employee_id` (`employee_id`),
  INDEX `idx_log_time` (`log_time`)
) ENGINE=InnoDB;

-- Heartbeat (live status)
CREATE TABLE `heartbeats` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `employee_id` VARCHAR(50) NOT NULL UNIQUE,
  `session_id` INT,
  `last_seen` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `status` ENUM('online','idle','offline') DEFAULT 'online',
  `active_window` VARCHAR(500),
  ON DUPLICATE KEY UPDATE
    last_seen = VALUES(last_seen)
) ENGINE=InnoDB;

-- Fix heartbeat: separate upsert handled in PHP
DROP TABLE IF EXISTS `heartbeats`;
CREATE TABLE `heartbeats` (
  `employee_id` VARCHAR(50) NOT NULL PRIMARY KEY,
  `session_id` INT,
  `last_seen` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `status` ENUM('online','idle','offline') DEFAULT 'online',
  `active_window` VARCHAR(500)
) ENGINE=InnoDB;

-- ============================================
-- Default admin account
-- Username: admin | Password: Admin@1234
-- ============================================
INSERT INTO `admins` (`username`, `password`, `full_name`)
VALUES ('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Administrator');

-- Sample employees (password for all: Employee@123)
-- Change these via the admin panel
INSERT INTO `employees` (`employee_id`, `password`, `full_name`, `department`) VALUES
('EMP001', '$2y$10$TKh8H1.PfjfwiVKREvJORuBMEMKREQnOBMnFCzBhJpfGpqMcZOdTm', 'Employee One', 'Operations'),
('EMP002', '$2y$10$TKh8H1.PfjfwiVKREvJORuBMEMKREQnOBMnFCzBhJpfGpqMcZOdTm', 'Employee Two', 'Operations'),
('EMP003', '$2y$10$TKh8H1.PfjfwiVKREvJORuBMEMKREQnOBMnFCzBhJpfGpqMcZOdTm', 'Employee Three', 'Operations'),
('EMP004', '$2y$10$TKh8H1.PfjfwiVKREvJORuBMEMKREQnOBMnFCzBhJpfGpqMcZOdTm', 'Employee Four', 'Operations'),
('EMP005', '$2y$10$TKh8H1.PfjfwiVKREvJORuBMEMKREQnOBMnFCzBhJpfGpqMcZOdTm', 'Employee Five', 'Operations');