-- =============================================================================
-- Adds the business_id column to finance fact tables whose JPA entities map a
-- Business relation and whose list/create queries filter on business_id. Some
-- older dev databases predate this column, causing
--   "Unknown column 'fs1_0.business_id'"  on bank/CC statement + transaction lists.
-- Guarded so environments that already have the column are unaffected.
-- Deploy: mysql -u root -p db_abos_v0.1 < db/fix_finance_business_id_columns.sql
-- =============================================================================
USE db_abos_v0.1;
SET @db := DATABASE();

SET @sql := (SELECT IF(COUNT(*)=0,
  'ALTER TABLE fact_statements ADD COLUMN business_id INT NULL AFTER tenant_id',
  'SELECT 1') FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_statements' AND COLUMN_NAME='business_id');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := (SELECT IF(COUNT(*)=0,
  'ALTER TABLE fact_statement_transactions ADD COLUMN business_id INT NULL AFTER tenant_id',
  'SELECT 1') FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_statement_transactions' AND COLUMN_NAME='business_id');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;