SET @has_expense_reference_type := (
  SELECT COUNT(*)
  FROM information_schema.columns
  WHERE table_schema = DATABASE()
    AND table_name = 'expenses'
    AND column_name = 'reference_type'
);
SET @sql_expense_reference_type := IF(
  @has_expense_reference_type = 0,
  'ALTER TABLE expenses ADD COLUMN reference_type VARCHAR(50) NULL AFTER reference_label',
  'SELECT 1'
);
PREPARE stmt_expense_reference_type FROM @sql_expense_reference_type;
EXECUTE stmt_expense_reference_type;
DEALLOCATE PREPARE stmt_expense_reference_type;

SET @has_expense_employee_id := (
  SELECT COUNT(*)
  FROM information_schema.columns
  WHERE table_schema = DATABASE()
    AND table_name = 'expenses'
    AND column_name = 'employee_id'
);
SET @sql_expense_employee_id := IF(
  @has_expense_employee_id = 0,
  'ALTER TABLE expenses ADD COLUMN employee_id BIGINT UNSIGNED NULL AFTER reference_type',
  'SELECT 1'
);
PREPARE stmt_expense_employee_id FROM @sql_expense_employee_id;
EXECUTE stmt_expense_employee_id;
DEALLOCATE PREPARE stmt_expense_employee_id;

SET @has_expense_installment_number := (
  SELECT COUNT(*)
  FROM information_schema.columns
  WHERE table_schema = DATABASE()
    AND table_name = 'expenses'
    AND column_name = 'installment_number'
);
SET @sql_expense_installment_number := IF(
  @has_expense_installment_number = 0,
  'ALTER TABLE expenses ADD COLUMN installment_number INT NULL AFTER employee_id',
  'SELECT 1'
);
PREPARE stmt_expense_installment_number FROM @sql_expense_installment_number;
EXECUTE stmt_expense_installment_number;
DEALLOCATE PREPARE stmt_expense_installment_number;

SET @has_expense_ref_employee_unique := (
  SELECT COUNT(*)
  FROM information_schema.statistics
  WHERE table_schema = DATABASE()
    AND table_name = 'expenses'
    AND index_name = 'uq_expenses_reference_employee_installment'
);
SET @sql_expense_ref_employee_unique := IF(
  @has_expense_ref_employee_unique = 0,
  'ALTER TABLE expenses ADD UNIQUE KEY uq_expenses_reference_employee_installment (reference_type, source_ref_id, employee_id, installment_number)',
  'SELECT 1'
);
PREPARE stmt_expense_ref_employee_unique FROM @sql_expense_ref_employee_unique;
EXECUTE stmt_expense_ref_employee_unique;
DEALLOCATE PREPARE stmt_expense_ref_employee_unique;
