SET @has_employee_meal_enabled := (
  SELECT COUNT(*)
  FROM information_schema.columns
  WHERE table_schema = DATABASE()
    AND table_name = 'employees'
    AND column_name = 'meal_allowance_enabled'
);
SET @sql_employee_meal_enabled := IF(
  @has_employee_meal_enabled = 0,
  'ALTER TABLE employees ADD COLUMN meal_allowance_enabled TINYINT(1) NOT NULL DEFAULT 0 AFTER payroll_enabled',
  'SELECT 1'
);
PREPARE stmt_employee_meal_enabled FROM @sql_employee_meal_enabled;
EXECUTE stmt_employee_meal_enabled;
DEALLOCATE PREPARE stmt_employee_meal_enabled;

SET @has_employee_meal_mode := (
  SELECT COUNT(*)
  FROM information_schema.columns
  WHERE table_schema = DATABASE()
    AND table_name = 'employees'
    AND column_name = 'meal_allowance_mode'
);
SET @sql_employee_meal_mode := IF(
  @has_employee_meal_mode = 0,
  'ALTER TABLE employees ADD COLUMN meal_allowance_mode VARCHAR(20) NOT NULL DEFAULT ''daily'' AFTER meal_allowance_enabled',
  'SELECT 1'
);
PREPARE stmt_employee_meal_mode FROM @sql_employee_meal_mode;
EXECUTE stmt_employee_meal_mode;
DEALLOCATE PREPARE stmt_employee_meal_mode;

SET @has_employee_meal_amount := (
  SELECT COUNT(*)
  FROM information_schema.columns
  WHERE table_schema = DATABASE()
    AND table_name = 'employees'
    AND column_name = 'meal_allowance_amount'
);
SET @sql_employee_meal_amount := IF(
  @has_employee_meal_amount = 0,
  'ALTER TABLE employees ADD COLUMN meal_allowance_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00 AFTER meal_allowance_mode',
  'SELECT 1'
);
PREPARE stmt_employee_meal_amount FROM @sql_employee_meal_amount;
EXECUTE stmt_employee_meal_amount;
DEALLOCATE PREPARE stmt_employee_meal_amount;

SET @has_employee_meal_notes := (
  SELECT COUNT(*)
  FROM information_schema.columns
  WHERE table_schema = DATABASE()
    AND table_name = 'employees'
    AND column_name = 'meal_allowance_notes'
);
SET @sql_employee_meal_notes := IF(
  @has_employee_meal_notes = 0,
  'ALTER TABLE employees ADD COLUMN meal_allowance_notes TEXT NULL AFTER meal_allowance_amount',
  'SELECT 1'
);
PREPARE stmt_employee_meal_notes FROM @sql_employee_meal_notes;
EXECUTE stmt_employee_meal_notes;
DEALLOCATE PREPARE stmt_employee_meal_notes;

SET @has_absence_blocks_meal := (
  SELECT COUNT(*)
  FROM information_schema.columns
  WHERE table_schema = DATABASE()
    AND table_name = 'absence_types'
    AND column_name = 'blocks_meal_allowance'
);
SET @sql_absence_blocks_meal := IF(
  @has_absence_blocks_meal = 0,
  'ALTER TABLE absence_types ADD COLUMN blocks_meal_allowance TINYINT(1) NOT NULL DEFAULT 0 AFTER affects_payroll',
  'SELECT 1'
);
PREPARE stmt_absence_blocks_meal FROM @sql_absence_blocks_meal;
EXECUTE stmt_absence_blocks_meal;
DEALLOCATE PREPARE stmt_absence_blocks_meal;

SET @has_absence_non_working := (
  SELECT COUNT(*)
  FROM information_schema.columns
  WHERE table_schema = DATABASE()
    AND table_name = 'absence_types'
    AND column_name = 'counts_as_non_working_day'
);
SET @sql_absence_non_working := IF(
  @has_absence_non_working = 0,
  'ALTER TABLE absence_types ADD COLUMN counts_as_non_working_day TINYINT(1) NOT NULL DEFAULT 0 AFTER blocks_meal_allowance',
  'SELECT 1'
);
PREPARE stmt_absence_non_working FROM @sql_absence_non_working;
EXECUTE stmt_absence_non_working;
DEALLOCATE PREPARE stmt_absence_non_working;

CREATE TABLE IF NOT EXISTS meal_allowance_batches (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NULL,
  mode VARCHAR(20) NOT NULL DEFAULT 'daily',
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'draft',
  batch_code VARCHAR(80) NOT NULL,
  generated_at DATETIME NOT NULL,
  generated_by BIGINT UNSIGNED NULL,
  notes TEXT NULL,
  total_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  posted_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_meal_allowance_batches_code (batch_code),
  KEY idx_meal_allowance_batches_scope (tenant_id, location_id, mode, period_start, period_end),
  KEY idx_meal_allowance_batches_status (tenant_id, status, generated_at),
  CONSTRAINT fk_meal_allowance_batches_location FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE SET NULL,
  CONSTRAINT fk_meal_allowance_batches_generated_by FOREIGN KEY (generated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS meal_allowance_lines (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  batch_id BIGINT UNSIGNED NOT NULL,
  employee_id BIGINT UNSIGNED NOT NULL,
  meal_allowance_mode VARCHAR(20) NOT NULL DEFAULT 'daily',
  date_from DATE NOT NULL,
  date_to DATE NOT NULL,
  base_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  eligible_days INT NOT NULL DEFAULT 0,
  blocked_days INT NOT NULL DEFAULT 0,
  final_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  status VARCHAR(20) NOT NULL DEFAULT 'eligible',
  block_reason TEXT NULL,
  linked_absence_ids_json JSON NULL,
  posted_expense_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_meal_allowance_lines_batch_employee (batch_id, employee_id),
  KEY idx_meal_allowance_lines_employee (employee_id, meal_allowance_mode),
  KEY idx_meal_allowance_lines_posted (posted_expense_id),
  CONSTRAINT fk_meal_allowance_lines_batch FOREIGN KEY (batch_id) REFERENCES meal_allowance_batches(id) ON DELETE CASCADE,
  CONSTRAINT fk_meal_allowance_lines_employee FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET @has_expense_meal_unique := (
  SELECT COUNT(*)
  FROM information_schema.statistics
  WHERE table_schema = DATABASE()
    AND table_name = 'expenses'
    AND index_name = 'uq_expenses_source_ref'
);
SET @sql_expense_meal_unique := IF(
  @has_expense_meal_unique = 0,
  'ALTER TABLE expenses ADD UNIQUE KEY uq_expenses_source_ref (source_type, source_ref_id)',
  'SELECT 1'
);
PREPARE stmt_expense_meal_unique FROM @sql_expense_meal_unique;
EXECUTE stmt_expense_meal_unique;
DEALLOCATE PREPARE stmt_expense_meal_unique;