SET @has_line_post_status := (
  SELECT COUNT(*)
  FROM information_schema.columns
  WHERE table_schema = DATABASE()
    AND table_name = 'meal_allowance_lines'
    AND column_name = 'post_status'
);
SET @sql_line_post_status := IF(
  @has_line_post_status = 0,
  'ALTER TABLE meal_allowance_lines ADD COLUMN post_status VARCHAR(20) NOT NULL DEFAULT ''draft'' AFTER status',
  'SELECT 1'
);
PREPARE stmt_line_post_status FROM @sql_line_post_status;
EXECUTE stmt_line_post_status;
DEALLOCATE PREPARE stmt_line_post_status;

SET @has_line_posted_at := (
  SELECT COUNT(*)
  FROM information_schema.columns
  WHERE table_schema = DATABASE()
    AND table_name = 'meal_allowance_lines'
    AND column_name = 'posted_at'
);
SET @sql_line_posted_at := IF(
  @has_line_posted_at = 0,
  'ALTER TABLE meal_allowance_lines ADD COLUMN posted_at DATETIME NULL AFTER posted_expense_id',
  'SELECT 1'
);
PREPARE stmt_line_posted_at FROM @sql_line_posted_at;
EXECUTE stmt_line_posted_at;
DEALLOCATE PREPARE stmt_line_posted_at;

UPDATE meal_allowance_lines
SET post_status = CASE
  WHEN posted_expense_id IS NOT NULL THEN 'posted'
  ELSE 'draft'
END
WHERE COALESCE(post_status, '') = ''
   OR post_status NOT IN ('draft', 'selected', 'posted');

UPDATE meal_allowance_lines mal
JOIN meal_allowance_batches mab ON mab.id = mal.batch_id
SET
  mal.post_status = 'posted',
  mal.posted_at = COALESCE(mal.posted_at, mab.posted_at, mal.updated_at, mal.created_at)
WHERE mal.posted_expense_id IS NOT NULL;

SET @has_idx_line_batch_post_status := (
  SELECT COUNT(*)
  FROM information_schema.statistics
  WHERE table_schema = DATABASE()
    AND table_name = 'meal_allowance_lines'
    AND index_name = 'idx_meal_allowance_lines_batch_post_status'
);
SET @sql_idx_line_batch_post_status := IF(
  @has_idx_line_batch_post_status = 0,
  'ALTER TABLE meal_allowance_lines ADD KEY idx_meal_allowance_lines_batch_post_status (batch_id, post_status)',
  'SELECT 1'
);
PREPARE stmt_idx_line_batch_post_status FROM @sql_idx_line_batch_post_status;
EXECUTE stmt_idx_line_batch_post_status;
DEALLOCATE PREPARE stmt_idx_line_batch_post_status;

SET @has_idx_line_scope_lookup := (
  SELECT COUNT(*)
  FROM information_schema.statistics
  WHERE table_schema = DATABASE()
    AND table_name = 'meal_allowance_lines'
    AND index_name = 'idx_meal_allowance_lines_scope_lookup'
);
SET @sql_idx_line_scope_lookup := IF(
  @has_idx_line_scope_lookup = 0,
  'ALTER TABLE meal_allowance_lines ADD KEY idx_meal_allowance_lines_scope_lookup (employee_id, meal_allowance_mode, date_from, date_to, posted_expense_id)',
  'SELECT 1'
);
PREPARE stmt_idx_line_scope_lookup FROM @sql_idx_line_scope_lookup;
EXECUTE stmt_idx_line_scope_lookup;
DEALLOCATE PREPARE stmt_idx_line_scope_lookup;