-- HRMS Area Manager Mobile Tracking - FINAL ADMIN USER VERSION
-- Run this after your normal HRMS database/schema is imported.
-- Area Managers come from admin_users. Mobile PIN is separate and is set in Mobile Access.
-- Safe for the earlier employee-based tracking tables: existing tracking columns are kept
-- nullable and new records use admin_user_id.

CREATE TABLE IF NOT EXISTS field_mobile_credentials_admin (
  admin_user_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,
  INDEX idx_fmca_enabled (enabled)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS field_mobile_tokens (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  admin_user_id INT UNSIGNED NULL,
  employee_id INT UNSIGNED 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_admin(admin_user_id),
  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,
  admin_user_id INT UNSIGNED NULL,
  employee_id INT UNSIGNED NULL,
  employee_code VARCHAR(100) 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_admin_date(admin_user_id,start_time),
  INDEX idx_fts_employee_date(employee_id,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,
  admin_user_id INT UNSIGNED NULL,
  employee_id INT UNSIGNED 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_admin_time(admin_user_id,recorded_at),
  INDEX idx_flp_employee_time(employee_id,recorded_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Migration for installations that already have the earlier employee-based tables.
-- MySQL 8 supports ADD COLUMN IF NOT EXISTS.
ALTER TABLE field_mobile_tokens
  MODIFY employee_id INT UNSIGNED NULL;
ALTER TABLE field_mobile_tokens
  ADD COLUMN IF NOT EXISTS admin_user_id INT UNSIGNED NULL AFTER id;

ALTER TABLE field_tracking_sessions
  MODIFY employee_id INT UNSIGNED NULL,
  MODIFY employee_code VARCHAR(100) NULL;
ALTER TABLE field_tracking_sessions
  ADD COLUMN IF NOT EXISTS admin_user_id INT UNSIGNED NULL AFTER id;

ALTER TABLE field_location_points
  MODIFY employee_id INT UNSIGNED NULL;
ALTER TABLE field_location_points
  ADD COLUMN IF NOT EXISTS admin_user_id INT UNSIGNED NULL AFTER session_id;

CREATE INDEX IF NOT EXISTS idx_fmt_admin ON field_mobile_tokens(admin_user_id);
CREATE INDEX IF NOT EXISTS idx_fts_admin_date ON field_tracking_sessions(admin_user_id,start_time);
CREATE INDEX IF NOT EXISTS idx_flp_admin_time ON field_location_points(admin_user_id,recorded_at);

-- Old employee-token sessions cannot authenticate through the new Area Manager API.
UPDATE field_mobile_tokens
SET status='revoked'
WHERE admin_user_id IS NULL AND status='active';
