USE hrms;

CREATE TABLE IF NOT EXISTS field_mobile_credentials_admin (
  admin_user_id INT UNSIGNED NOT NULL 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,
  CONSTRAINT fk_fmca_admin FOREIGN KEY (admin_user_id) REFERENCES admin_users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add admin_user_id to existing tracking tables if it is not present.
SET @db = DATABASE();
SET @has_col = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='field_mobile_tokens' AND COLUMN_NAME='admin_user_id');
SET @sql = IF(@has_col=0,'ALTER TABLE field_mobile_tokens ADD COLUMN admin_user_id INT UNSIGNED NULL AFTER employee_id','SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
SET @has_col = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='field_tracking_sessions' AND COLUMN_NAME='admin_user_id');
SET @sql = IF(@has_col=0,'ALTER TABLE field_tracking_sessions ADD COLUMN admin_user_id INT UNSIGNED NULL AFTER employee_code','SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
SET @has_col = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='field_location_points' AND COLUMN_NAME='admin_user_id');
SET @sql = IF(@has_col=0,'ALTER TABLE field_location_points ADD COLUMN admin_user_id INT UNSIGNED NULL AFTER employee_id','SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @idx = (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='field_mobile_tokens' AND INDEX_NAME='idx_fmt_admin_user');
SET @sql = IF(@idx=0,'ALTER TABLE field_mobile_tokens ADD INDEX idx_fmt_admin_user(admin_user_id)','SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
SET @idx = (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='field_tracking_sessions' AND INDEX_NAME='idx_fts_admin_date');
SET @sql = IF(@idx=0,'ALTER TABLE field_tracking_sessions ADD INDEX idx_fts_admin_date(admin_user_id,start_time)','SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
SET @idx = (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='field_location_points' AND INDEX_NAME='idx_flp_admin_time');
SET @sql = IF(@idx=0,'ALTER TABLE field_location_points ADD INDEX idx_flp_admin_time(admin_user_id,recorded_at)','SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
