USE db_abos_v0.1;

-- Adds business code, tax/registration id, notes, and active-processes fields to
-- `businesses` (2026-07-25). The frontend edit form already reads/writes these
-- fields; they were missing from the table, so they were silently dropped on
-- both save and load. Purely additive — all columns NULL, no existing column
-- touched. `active_processes` stores a comma-separated list of process keys
-- (CRM, FINANCE, HRM, PROJECT, PROCUREMENT, REAL_ESTATE, LEGAL), matching the
-- existing `dim_subscription_plans.modules text` convention.

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'businesses' AND COLUMN_NAME = 'code'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE businesses ADD COLUMN code VARCHAR(20) NULL AFTER name',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'businesses' AND COLUMN_NAME = 'tax_id'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE businesses ADD COLUMN tax_id VARCHAR(50) NULL AFTER address',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'businesses' AND COLUMN_NAME = 'notes'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE businesses ADD COLUMN notes VARCHAR(1000) NULL AFTER status',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'businesses' AND COLUMN_NAME = 'active_processes'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE businesses ADD COLUMN active_processes VARCHAR(255) NULL AFTER notes',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;