USE db_abos_v0.1;

-- Found during Finance re-test (2026-07-12): DimExpenseCategory entity declares an is_deleted
-- field (used by 5 of 8 DimExpenseCategoryRepository query methods, e.g.
-- findByIdAndTenantIdAndIsDeletedFalse), but dim_expense_categories never had that column —
-- same schema-drift pattern as Bug #3 (user_module_access). Every soft-delete-aware query against
-- this table failed with "Unknown column 'is_deleted' in 'field list'", including transitively
-- whenever FactTransaction/FactLedgerEntry eagerly fetched their linked category/subcategory.

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_expense_categories' AND COLUMN_NAME = 'is_deleted'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE dim_expense_categories ADD COLUMN is_deleted TINYINT NOT NULL DEFAULT 0',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;