-- Add VAT amount to expenses and recurring_expenses
-- Purpose: allow manual VAT entry and store it separately from total amount
-- Idempotent: safe to re-run migrations
-- Date: 2026-04-30

SET @dbname = DATABASE();

-- expenses.vat_amount
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = 'expenses' AND COLUMN_NAME = 'vat_amount') > 0,
  'SELECT 1',
  'ALTER TABLE expenses ADD COLUMN vat_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER amount'
));
PREPARE stmt FROM @preparedStatement;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- recurring_expenses.vat_amount
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = 'recurring_expenses' AND COLUMN_NAME = 'vat_amount') > 0,
  'SELECT 1',
  'ALTER TABLE recurring_expenses ADD COLUMN vat_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER amount'
));
PREPARE stmt FROM @preparedStatement;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;