SET @has_batch_posted_expense_id := (
  SELECT COUNT(*)
  FROM information_schema.columns
  WHERE table_schema = DATABASE()
    AND table_name = 'meal_allowance_batches'
    AND column_name = 'posted_expense_id'
);
SET @sql_batch_posted_expense_id := IF(
  @has_batch_posted_expense_id = 0,
  'ALTER TABLE meal_allowance_batches ADD COLUMN posted_expense_id BIGINT UNSIGNED NULL AFTER posted_at',
  'SELECT 1'
);
PREPARE stmt_batch_posted_expense_id FROM @sql_batch_posted_expense_id;
EXECUTE stmt_batch_posted_expense_id;
DEALLOCATE PREPARE stmt_batch_posted_expense_id;

UPDATE expenses e
JOIN meal_allowance_lines mal ON mal.id = e.source_ref_id
JOIN meal_allowance_batches mab ON mab.id = mal.batch_id
SET
  e.expense_category = 'EMPLOYEE_MEAL_ALLOWANCE',
  e.reference_label = CONCAT('Uang Makan Karyawan · ', COALESCE(NULLIF(mab.batch_code, ''), CONCAT('BATCH-', mab.id))),
  e.description = CASE
    WHEN LOWER(TRIM(COALESCE(mab.mode, 'daily'))) = 'weekly' THEN CONCAT('Batch Uang Makan WEEKLY ', DATE_FORMAT(mab.period_start, '%e %b'), ' - ', DATE_FORMAT(mab.period_end, '%e %b %Y'))
    WHEN mab.period_start = mab.period_end THEN CONCAT('Batch Uang Makan DAILY ', DATE_FORMAT(mab.period_end, '%e %b %Y'))
    ELSE CONCAT('Batch Uang Makan DAILY ', DATE_FORMAT(mab.period_start, '%e %b'), ' - ', DATE_FORMAT(mab.period_end, '%e %b %Y'))
  END
WHERE COALESCE(e.source_type, 'MANUAL') = 'MEAL_ALLOWANCE';

UPDATE meal_allowance_batches mab
JOIN (
  SELECT mal.batch_id, MIN(e.id) AS expense_id
  FROM expenses e
  JOIN meal_allowance_lines mal ON mal.id = e.source_ref_id
  WHERE COALESCE(e.source_type, 'MANUAL') = 'MEAL_ALLOWANCE'
  GROUP BY mal.batch_id
) mapped ON mapped.batch_id = mab.id
SET mab.posted_expense_id = mapped.expense_id
WHERE mab.posted_expense_id IS NULL;