SET @has_absence_status := (
  SELECT COUNT(*)
  FROM information_schema.columns
  WHERE table_schema = DATABASE()
    AND table_name = 'employee_absences'
    AND column_name = 'status'
);
SET @sql_add_absence_status := IF(
  @has_absence_status = 0,
  'ALTER TABLE employee_absences ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT ''active'' AFTER note',
  'SELECT 1'
);
PREPARE stmt_add_absence_status FROM @sql_add_absence_status;
EXECUTE stmt_add_absence_status;
DEALLOCATE PREPARE stmt_add_absence_status;

SET @has_absence_voided_at := (
  SELECT COUNT(*)
  FROM information_schema.columns
  WHERE table_schema = DATABASE()
    AND table_name = 'employee_absences'
    AND column_name = 'voided_at'
);
SET @sql_add_absence_voided_at := IF(
  @has_absence_voided_at = 0,
  'ALTER TABLE employee_absences ADD COLUMN voided_at DATETIME NULL AFTER status',
  'SELECT 1'
);
PREPARE stmt_add_absence_voided_at FROM @sql_add_absence_voided_at;
EXECUTE stmt_add_absence_voided_at;
DEALLOCATE PREPARE stmt_add_absence_voided_at;

SET @has_absence_voided_by := (
  SELECT COUNT(*)
  FROM information_schema.columns
  WHERE table_schema = DATABASE()
    AND table_name = 'employee_absences'
    AND column_name = 'voided_by'
);
SET @sql_add_absence_voided_by := IF(
  @has_absence_voided_by = 0,
  'ALTER TABLE employee_absences ADD COLUMN voided_by BIGINT UNSIGNED NULL AFTER voided_at',
  'SELECT 1'
);
PREPARE stmt_add_absence_voided_by FROM @sql_add_absence_voided_by;
EXECUTE stmt_add_absence_voided_by;
DEALLOCATE PREPARE stmt_add_absence_voided_by;

SET @has_absence_void_reason := (
  SELECT COUNT(*)
  FROM information_schema.columns
  WHERE table_schema = DATABASE()
    AND table_name = 'employee_absences'
    AND column_name = 'void_reason'
);
SET @sql_add_absence_void_reason := IF(
  @has_absence_void_reason = 0,
  'ALTER TABLE employee_absences ADD COLUMN void_reason TEXT NULL AFTER voided_by',
  'SELECT 1'
);
PREPARE stmt_add_absence_void_reason FROM @sql_add_absence_void_reason;
EXECUTE stmt_add_absence_void_reason;
DEALLOCATE PREPARE stmt_add_absence_void_reason;

SET @has_absence_voided_by_fk := (
  SELECT COUNT(*)
  FROM information_schema.referential_constraints
  WHERE constraint_schema = DATABASE()
    AND table_name = 'employee_absences'
    AND constraint_name = 'fk_employee_absence_voided_by'
);
SET @sql_add_absence_voided_by_fk := IF(
  @has_absence_voided_by_fk = 0,
  'ALTER TABLE employee_absences ADD CONSTRAINT fk_employee_absence_voided_by FOREIGN KEY (voided_by) REFERENCES users(id) ON DELETE SET NULL',
  'SELECT 1'
);
PREPARE stmt_add_absence_voided_by_fk FROM @sql_add_absence_voided_by_fk;
EXECUTE stmt_add_absence_voided_by_fk;
DEALLOCATE PREPARE stmt_add_absence_voided_by_fk;

SET @has_absence_status_index := (
  SELECT COUNT(*)
  FROM information_schema.statistics
  WHERE table_schema = DATABASE()
    AND table_name = 'employee_absences'
    AND index_name = 'idx_employee_absence_status_range'
);
SET @sql_add_absence_status_index := IF(
  @has_absence_status_index = 0,
  'ALTER TABLE employee_absences ADD KEY idx_employee_absence_status_range (tenant_id, status, employee_id, date_from, date_to)',
  'SELECT 1'
);
PREPARE stmt_add_absence_status_index FROM @sql_add_absence_status_index;
EXECUTE stmt_add_absence_status_index;
DEALLOCATE PREPARE stmt_add_absence_status_index;