-- HRMS Area Manager Field Tracking
-- Run this once in phpMyAdmin on the existing `hrms` database.
-- Safe additive migration: it does NOT drop or replace existing HRMS tables.

USE hrms;

CREATE TABLE IF NOT EXISTS field_mobile_tokens (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  employee_id INT UNSIGNED NOT NULL,
  token_hash CHAR(64) NOT NULL UNIQUE,
  device_uuid VARCHAR(120) DEFAULT NULL,
  device_name VARCHAR(180) DEFAULT NULL,
  issued_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_used_at DATETIME DEFAULT NULL,
  expires_at DATETIME DEFAULT NULL,
  status ENUM('active','revoked') NOT NULL DEFAULT 'active',
  INDEX idx_fmt_employee(employee_id),
  INDEX idx_fmt_status(status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS field_tracking_sessions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  employee_id INT UNSIGNED NOT NULL,
  employee_code VARCHAR(50) NOT NULL,
  start_time DATETIME NOT NULL,
  start_latitude DECIMAL(10,7) NOT NULL,
  start_longitude DECIMAL(10,7) NOT NULL,
  start_accuracy DECIMAL(10,2) DEFAULT NULL,
  start_selfie VARCHAR(255) DEFAULT NULL,
  end_time DATETIME DEFAULT NULL,
  end_latitude DECIMAL(10,7) DEFAULT NULL,
  end_longitude DECIMAL(10,7) DEFAULT NULL,
  end_accuracy DECIMAL(10,2) DEFAULT NULL,
  end_selfie VARCHAR(255) DEFAULT NULL,
  total_distance_km DECIMAL(12,3) NOT NULL DEFAULT 0,
  status ENUM('active','completed','cancelled') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_fts_employee_date(employee_id,start_time),
  INDEX idx_fts_code_date(employee_code,start_time),
  INDEX idx_fts_status(status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS field_location_points (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  session_id BIGINT UNSIGNED NOT NULL,
  employee_id INT UNSIGNED NOT NULL,
  latitude DECIMAL(10,7) NOT NULL,
  longitude DECIMAL(10,7) NOT NULL,
  accuracy DECIMAL(10,2) DEFAULT NULL,
  speed_mps DECIMAL(10,3) DEFAULT NULL,
  recorded_at DATETIME NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_flp_session_time(session_id,recorded_at),
  INDEX idx_flp_employee_time(employee_id,recorded_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Optional mobile PIN storage. Passwords are stored as PHP password_hash() values.
CREATE TABLE IF NOT EXISTS field_mobile_credentials (
  employee_id INT UNSIGNED PRIMARY KEY,
  pin_hash VARCHAR(255) NOT NULL,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create a mobile PIN for an employee using PHP's password_hash(), not a plaintext value.
-- Example after opening the supplied setup page:
-- /field_mobile_access.php
