-- HRMS Face Attendance mobile integration
USE hrms;
CREATE TABLE IF NOT EXISTS face_mobile_users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 admin_user_id INT UNSIGNED NOT NULL UNIQUE,
 token_hash CHAR(64) NOT NULL UNIQUE,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 last_used_at DATETIME NULL,
 status ENUM('active','revoked') NOT NULL DEFAULT 'active',
 INDEX idx_fmu_admin(admin_user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS employee_mobile_faces (
 employee_id INT UNSIGNED PRIMARY KEY,
 branch_id INT UNSIGNED NULL,
 embedding LONGTEXT NOT NULL,
 face_image VARCHAR(255) NULL,
 updated_by INT UNSIGNED NULL,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 INDEX idx_emf_branch(branch_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS face_attendance_daily (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 employee_id INT UNSIGNED NOT NULL,
 branch_id INT UNSIGNED NULL,
 att_date DATE NOT NULL,
 morning_in DATETIME NULL,
 morning_out DATETIME NULL,
 lunch_in DATETIME NULL,
 final_out DATETIME NULL,
 source VARCHAR(30) NOT NULL DEFAULT 'FACE_MOBILE',
 created_by INT UNSIGNED NULL,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_fad_emp_date(employee_id,att_date),
 INDEX idx_fad_branch_date(branch_id,att_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
