-- HRMS Face Attendance + Offline Sync + Field Mobile additive migration
-- Run ONCE in phpMyAdmin against the existing hrms database.
USE hrms;

CREATE TABLE IF NOT EXISTS mobile_api_tokens (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  admin_user_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_mat_user(admin_user_id),
  INDEX idx_mat_status(status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS employee_mobile_faces (
  employee_id INT UNSIGNED PRIMARY KEY,
  embedding LONGTEXT NOT NULL,
  face_image VARCHAR(255) DEFAULT NULL,
  updated_by INT UNSIGNED DEFAULT NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_emf_updated(updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS face_attendance_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event_id VARCHAR(80) NOT NULL UNIQUE,
  employee_id INT UNSIGNED NOT NULL,
  branch_id INT UNSIGNED DEFAULT NULL,
  action ENUM('MORNING_IN','MORNING_OUT','LUNCH_IN','FINAL_OUT') NOT NULL,
  event_time DATETIME NOT NULL,
  source ENUM('mobile_online','mobile_offline_sync','web') NOT NULL DEFAULT 'mobile_online',
  created_by INT UNSIGNED DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_fae_emp_date(employee_id,event_time),
  INDEX idx_fae_branch_date(branch_id,event_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS mobile_field_sync_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event_id VARCHAR(80) NOT NULL UNIQUE,
  admin_user_id INT UNSIGNED NOT NULL,
  local_session_id VARCHAR(80) NOT NULL,
  server_session_id BIGINT UNSIGNED DEFAULT NULL,
  event_type ENUM('start','location','end') NOT NULL,
  payload LONGTEXT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_mfse_user_session(admin_user_id,local_session_id),
  INDEX idx_mfse_server(server_session_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Existing field-tracker tables from the supplied HRMS build are used.
-- If they are not present yet, run the supplied SQL under sql/area_manager_tracking_admin_final.sql first.

CREATE TABLE IF NOT EXISTS mobile_face_sync_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event_id VARCHAR(80) NOT NULL UNIQUE,
  employee_id INT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_mfse_face_emp(employee_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
