-- Add employee hourly rates if missing
SET @db := DATABASE();

SET @sql := (
  SELECT IF(
    EXISTS(
      SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'employees' AND COLUMN_NAME = 'hourly_rate_local'
    ),
    'SELECT 1',
    "ALTER TABLE employees ADD COLUMN hourly_rate_local DECIMAL(10,2) NULL AFTER salary_type"
  )
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := (
  SELECT IF(
    EXISTS(
      SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'employees' AND COLUMN_NAME = 'hourly_rate_outstation'
    ),
    'SELECT 1',
    "ALTER TABLE employees ADD COLUMN hourly_rate_outstation DECIMAL(10,2) NULL AFTER hourly_rate_local"
  )
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Optional seed: put 0 instead of NULL so time tracking won't crash
UPDATE employees SET hourly_rate_local = 0 WHERE hourly_rate_local IS NULL;
UPDATE employees SET hourly_rate_outstation = 0 WHERE hourly_rate_outstation IS NULL;
