SET @has_review_status := (
  SELECT COUNT(*)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'print_error_logs'
    AND COLUMN_NAME = 'review_status'
);
SET @sql_add_review_status := IF(@has_review_status = 0,
  "ALTER TABLE print_error_logs ADD COLUMN review_status VARCHAR(30) NOT NULL DEFAULT 'logged' AFTER charge_amount",
  'SELECT 1'
);
PREPARE stmt_add_review_status FROM @sql_add_review_status;
EXECUTE stmt_add_review_status;
DEALLOCATE PREPARE stmt_add_review_status;

SET @has_reviewed_by := (
  SELECT COUNT(*)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'print_error_logs'
    AND COLUMN_NAME = 'reviewed_by'
);
SET @sql_add_reviewed_by := IF(@has_reviewed_by = 0,
  'ALTER TABLE print_error_logs ADD COLUMN reviewed_by BIGINT UNSIGNED NULL AFTER review_status',
  'SELECT 1'
);
PREPARE stmt_add_reviewed_by FROM @sql_add_reviewed_by;
EXECUTE stmt_add_reviewed_by;
DEALLOCATE PREPARE stmt_add_reviewed_by;

SET @has_reviewed_at := (
  SELECT COUNT(*)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'print_error_logs'
    AND COLUMN_NAME = 'reviewed_at'
);
SET @sql_add_reviewed_at := IF(@has_reviewed_at = 0,
  'ALTER TABLE print_error_logs ADD COLUMN reviewed_at DATETIME NULL AFTER reviewed_by',
  'SELECT 1'
);
PREPARE stmt_add_reviewed_at FROM @sql_add_reviewed_at;
EXECUTE stmt_add_reviewed_at;
DEALLOCATE PREPARE stmt_add_reviewed_at;

SET @has_review_note := (
  SELECT COUNT(*)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'print_error_logs'
    AND COLUMN_NAME = 'review_note'
);
SET @sql_add_review_note := IF(@has_review_note = 0,
  'ALTER TABLE print_error_logs ADD COLUMN review_note TEXT NULL AFTER reviewed_at',
  'SELECT 1'
);
PREPARE stmt_add_review_note FROM @sql_add_review_note;
EXECUTE stmt_add_review_note;
DEALLOCATE PREPARE stmt_add_review_note;

SET @has_review_idx := (
  SELECT COUNT(*)
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'print_error_logs'
    AND INDEX_NAME = 'idx_print_error_review'
);
SET @sql_add_review_idx := IF(@has_review_idx = 0,
  'ALTER TABLE print_error_logs ADD KEY idx_print_error_review (tenant_id, location_id, review_status, incident_date)',
  'SELECT 1'
);
PREPARE stmt_add_review_idx FROM @sql_add_review_idx;
EXECUTE stmt_add_review_idx;
DEALLOCATE PREPARE stmt_add_review_idx;

SET @has_review_fk := (
  SELECT COUNT(*)
  FROM information_schema.REFERENTIAL_CONSTRAINTS
  WHERE CONSTRAINT_SCHEMA = DATABASE()
    AND CONSTRAINT_NAME = 'fk_print_error_reviewed_by'
);
SET @sql_add_review_fk := IF(@has_review_fk = 0,
  'ALTER TABLE print_error_logs ADD CONSTRAINT fk_print_error_reviewed_by FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL',
  'SELECT 1'
);
PREPARE stmt_add_review_fk FROM @sql_add_review_fk;
EXECUTE stmt_add_review_fk;
DEALLOCATE PREPARE stmt_add_review_fk;