

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;

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;
CREATE INDEX IF NOT EXISTS idx_fts_admin_date ON field_tracking_sessions(admin_user_id,start_time);
