-- =============================================================================
-- Adds the business_id column to every table whose JPA entity maps a Business
-- relation (@JoinColumn(name="business_id")) but whose column is absent in older
-- dev databases created from the pre-business_id schema dump. Missing columns
-- cause runtime errors like:
--   "Unknown column 'c1_0.business_id' in 'field list'"
-- on finance list endpoints (wallets, invoices, etc.).
--
-- Idempotent: each ALTER is guarded on information_schema, so re-running is a
-- no-op on databases that already have the column.
--
-- Deploy: mysql -u root -p db_abos_v0.1 < db/fix_all_business_id_columns.sql
-- =============================================================================
USE db_abos_v0.1;
SET @db := DATABASE();

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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