-- Adicionar campos de localização à tabela employees

ALTER TABLE employees 
ADD COLUMN city VARCHAR(100) NULL AFTER address,
ADD COLUMN state VARCHAR(50) NULL AFTER city,
ADD COLUMN postal_code VARCHAR(10) NULL AFTER state,
ADD COLUMN department VARCHAR(100) NULL AFTER cost_center,
ADD COLUMN work_location VARCHAR(255) NULL AFTER department,
ADD COLUMN latitude DECIMAL(10, 8) NULL AFTER work_location,
ADD COLUMN longitude DECIMAL(11, 8) NULL AFTER latitude;

-- Adicionar índices para melhor performance nas consultas de localização
CREATE INDEX idx_employees_city ON employees(city);
CREATE INDEX idx_employees_state ON employees(state);
CREATE INDEX idx_employees_department ON employees(department);
CREATE INDEX idx_employees_work_location ON employees(work_location);
CREATE INDEX idx_employees_location ON employees(latitude, longitude);