-- ============================================================================================
-- CONSOLIDATED PRODUCTION DEPLOYMENT SCRIPT
-- Combines: app_settings.sql, fix_user_module_access.sql, fix_sp_bulk_create_tenant_users.sql,
--           fix_finance_hrm_platform_procedures.sql, fix_dim_expense_categories.sql
-- See plans/production-sql-changes.md for the full rationale/risk notes behind each section.
--
-- Idempotent throughout — every CREATE TABLE uses IF NOT EXISTS, every ALTER is guarded by an
-- information_schema check, and every CREATE PROCEDURE is preceded by DROP PROCEDURE IF EXISTS.
-- Safe to re-run in full if a deployment is interrupted partway through.
--
-- Deploy this at the same time as the matching application build — several Java
-- *ProcedureRepository classes now declare parameter lists that only match the NEW procedure
-- signatures created in section 4 below. Running old app code against these new procedures (or
-- new app code against the old procedures) reproduces the exact bugs this script fixes.
--
-- Recommended: take a full mysqldump of db_abos_v0.1 before running this.
-- ============================================================================================

USE db_abos_v0.1;


-- ============================================================================================
-- SECTION 1 — app_settings (new feature, not a bug fix)
-- Adds a global, non-tenant-scoped table for login-screen/app-shell branding config.
-- ============================================================================================

CREATE TABLE IF NOT EXISTS app_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    app_title VARCHAR(255) NOT NULL,
    app_subtitle VARCHAR(255),
    document_title VARCHAR(255),
    copyright_holder VARCHAR(255),
    login_welcome_message VARCHAR(500),
    login_sign_in_label VARCHAR(100),
    logo_url VARCHAR(500),
    favicon_url VARCHAR(500),
    primary_color VARCHAR(20),
    login_background_color VARCHAR(20),
    footer_text VARCHAR(500),
    support_email VARCHAR(255),
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    updated_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
);

INSERT INTO app_settings (
    app_title, app_subtitle, document_title, copyright_holder,
    login_welcome_message, login_sign_in_label, logo_url, favicon_url,
    primary_color, login_background_color, footer_text, support_email
)
SELECT
    'ABOS V1', 'Agentic ERP', 'ABOS — TechMAC Agentic ERP', 'NSP Foundation',
    'Sign in to your ERP workspace', 'Sign In', '', '',
    '#1a237e', '#f0f4ff', '© 2026 TechMAC. All rights reserved.', 'dev@techmac.co.uk'
WHERE NOT EXISTS (SELECT 1 FROM app_settings);


-- ============================================================================================
-- SECTION 2 — Bug #3: user_module_access schema drift
-- The UserModuleAccess JPA entity expects columns `module`, `is_deleted`, `created_at`,
-- `updated_at`, but the table as originally created only had (id, user_id, module_name,
-- access_level, tenant_id). Every existsBy.../findBy...IsDeletedFalse query threw "Unknown
-- column" and TENANT_MANAGER was locked out of every module-gated endpoint as a result.
-- ============================================================================================

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_module_access' AND COLUMN_NAME = 'module_name'
);
SET @sql = IF(@col_exists > 0,
    'ALTER TABLE user_module_access CHANGE COLUMN module_name module VARCHAR(50) NOT NULL',
    '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 = 'user_module_access' AND COLUMN_NAME = 'created_at'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE user_module_access ADD COLUMN created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6)',
    '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 = 'user_module_access' AND COLUMN_NAME = 'updated_at'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE user_module_access ADD COLUMN updated_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)',
    '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 = 'user_module_access' AND COLUMN_NAME = 'is_deleted'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE user_module_access ADD COLUMN is_deleted TINYINT NOT NULL DEFAULT 0',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


-- ============================================================================================
-- SECTION 3 — Bug #4: sp_bulk_create_tenant_users contract mismatch
-- The old procedure's JSON contract (first/last/hash/role/scope/dept) never matched what
-- UserService.createUserViaSp() actually sends (firstName/lastName/passwordHash/passwordSalt/
-- userType/roleId/moduleAccess), so v_role_name was always NULL, the INSERT INTO roles violated
-- its NOT NULL constraint, the blanket EXIT HANDLER swallowed the error silently, and every
-- tenant-admin/tenant-manager creation failed with an opaque 500. Also fixed a second mismatch:
-- AuthProcedureRepository declares (p_tenant_id, p_users_json, p_created_by, OUT p_created_count),
-- which never matched the old procedure's (p_tenant_id, p_users_json, p_creator_id) with no OUT
-- param at all.
-- ============================================================================================

DROP PROCEDURE IF EXISTS sp_bulk_create_tenant_users;

DELIMITER $$

CREATE PROCEDURE sp_bulk_create_tenant_users(
    IN p_tenant_id INT,
    IN p_users_json JSON,
    /* Example payload element:
    {
      "firstName": "John", "lastName": "Doe", "email": "j@test.com",
      "passwordHash": "$2a$...", "passwordSalt": "uuid", "userType": "TENANT_MANAGER",
      "roleId": 5,
      "moduleAccess": [{"module": "CRM", "accessLevel": "WRITE"}]
    }
    */
    IN p_created_by INT,
    OUT p_created_count INT
)
BEGIN
    DECLARE v_user_count INT DEFAULT 0;
    DECLARE v_module_count INT DEFAULT 0;
    DECLARE i INT DEFAULT 0;
    DECLARE j INT DEFAULT 0;

    DECLARE v_first VARCHAR(100);
    DECLARE v_last VARCHAR(100);
    DECLARE v_email VARCHAR(255);
    DECLARE v_hash VARCHAR(512);
    DECLARE v_salt VARCHAR(255);
    DECLARE v_user_type VARCHAR(20);
    DECLARE v_role_id INT;
    DECLARE v_new_user_id INT;
    DECLARE v_module_json JSON;
    DECLARE v_module_name VARCHAR(50);
    DECLARE v_access_level VARCHAR(20);

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    SET v_user_count = JSON_LENGTH(p_users_json);

    WHILE i < v_user_count DO
        SET v_first     = JSON_UNQUOTE(JSON_EXTRACT(p_users_json, CONCAT('$[', i, '].firstName')));
        SET v_last      = JSON_UNQUOTE(JSON_EXTRACT(p_users_json, CONCAT('$[', i, '].lastName')));
        SET v_email     = JSON_UNQUOTE(JSON_EXTRACT(p_users_json, CONCAT('$[', i, '].email')));
        SET v_hash       = JSON_UNQUOTE(JSON_EXTRACT(p_users_json, CONCAT('$[', i, '].passwordHash')));
        SET v_salt       = JSON_UNQUOTE(JSON_EXTRACT(p_users_json, CONCAT('$[', i, '].passwordSalt')));
        SET v_user_type  = JSON_UNQUOTE(JSON_EXTRACT(p_users_json, CONCAT('$[', i, '].userType')));
        SET v_role_id    = JSON_EXTRACT(p_users_json, CONCAT('$[', i, '].roleId'));
        SET v_module_json = JSON_EXTRACT(p_users_json, CONCAT('$[', i, '].moduleAccess'));

        INSERT INTO users (
            tenant_id, first_name, last_name, email, password_hash, password_salt,
            role_id, status, is_super_admin, user_type, is_enabled, created_by
        ) VALUES (
            p_tenant_id, v_first, v_last, v_email, v_hash, v_salt,
            v_role_id, 'Active', 0, v_user_type, b'1', p_created_by
        );

        SET v_new_user_id = LAST_INSERT_ID();

        IF v_module_json IS NOT NULL THEN
            SET v_module_count = JSON_LENGTH(v_module_json);
            SET j = 0;
            WHILE j < v_module_count DO
                SET v_module_name  = JSON_UNQUOTE(JSON_EXTRACT(v_module_json, CONCAT('$[', j, '].module')));
                SET v_access_level = JSON_UNQUOTE(JSON_EXTRACT(v_module_json, CONCAT('$[', j, '].accessLevel')));
                INSERT INTO user_module_access (user_id, module, access_level, tenant_id, is_deleted)
                VALUES (v_new_user_id, v_module_name, IFNULL(v_access_level, 'READ'), p_tenant_id, 0);
                SET j = j + 1;
            END WHILE;
        END IF;

        SET i = i + 1;
    END WHILE;

    SET p_created_count = v_user_count;

    COMMIT;
END$$

DELIMITER ;


-- ============================================================================================
-- SECTION 4 — Bugs #7 / #9 / #13: CRM/Finance/HRM/Platform-Admin procedure contract drift
-- Six procedures rewritten to match the parameter lists and table columns the Java layer
-- actually needs. See plans/qa-bug-report.md Bugs #7, #9, #13 for full before/after evidence.
-- ============================================================================================

-- --- 4a. sp_create_opportunity — was missing p_tenant_id, ignored 6 fields Java already sends,
--         and never populated fact_opportunities.full_name (NOT NULL, no default) — Bug #9.
DROP PROCEDURE IF EXISTS sp_create_opportunity;

DELIMITER $$

CREATE PROCEDURE sp_create_opportunity(
    IN p_tenant_id INT,
    IN p_company_id INT,
    IN p_contact_id INT,
    IN p_title VARCHAR(255),
    IN p_status VARCHAR(50),
    IN p_source VARCHAR(100),
    IN p_probability INT,
    IN p_deal_value DECIMAL(15,2),
    IN p_currency VARCHAR(10),
    IN p_assigned_to INT,
    IN p_expected_close_date DATE,
    IN p_notes TEXT,
    IN p_created_by INT,
    OUT p_opportunity_id BIGINT
)
BEGIN
    DECLARE v_full_name VARCHAR(255);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    SET v_full_name = p_title;
    IF p_contact_id IS NOT NULL THEN
        SELECT CONCAT(first_name, ' ', last_name) INTO v_full_name
        FROM dim_contacts
        WHERE id = p_contact_id AND tenant_id = p_tenant_id;
        IF v_full_name IS NULL THEN
            SET v_full_name = p_title;
        END IF;
    END IF;

    INSERT INTO fact_opportunities (
        tenant_id, company_id, contact_id, full_name, title, deal_title, status, source,
        probability, deal_value, currency, assigned_to, expected_close_date, notes, created_by
    ) VALUES (
        p_tenant_id, p_company_id, p_contact_id, v_full_name, p_title, p_title, p_status, p_source,
        p_probability, p_deal_value, p_currency, p_assigned_to, p_expected_close_date, p_notes, p_created_by
    );

    SET p_opportunity_id = LAST_INSERT_ID();
    COMMIT;
END$$

DELIMITER ;

-- --- 4b. sp_create_invoice_with_ledger — was missing p_tenant_id, ignored 5 fields Java already
--         sends, and never populated fact_invoices.client_name (NOT NULL, no default) — Bug #9.
DROP PROCEDURE IF EXISTS sp_create_invoice_with_ledger;

DELIMITER $$

CREATE PROCEDURE sp_create_invoice_with_ledger(
    IN p_tenant_id INT,
    IN p_opportunity_id BIGINT,
    IN p_wallet_id INT,
    IN p_client_id INT,
    IN p_invoice_no VARCHAR(100),
    IN p_amount DECIMAL(15,2),
    IN p_tax_amount DECIMAL(15,2),
    IN p_currency VARCHAR(10),
    IN p_issue_date DATE,
    IN p_due_date DATE,
    IN p_notes TEXT,
    IN p_created_by INT,
    OUT p_invoice_id BIGINT
)
BEGIN
    DECLARE v_client_name VARCHAR(255);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    SELECT company_name INTO v_client_name
    FROM dim_companies
    WHERE id = p_client_id AND tenant_id = p_tenant_id;

    INSERT INTO fact_invoices (
        tenant_id, opportunity_id, wallet_id, client_id, invoice_no, client_name, amount,
        tax_amount, currency, status, issue_date, due_date, notes, created_by
    ) VALUES (
        p_tenant_id, p_opportunity_id, p_wallet_id, p_client_id, p_invoice_no, v_client_name,
        p_amount, p_tax_amount, p_currency, 'Draft', p_issue_date, p_due_date, p_notes, p_created_by
    );

    SET p_invoice_id = LAST_INSERT_ID();

    INSERT INTO fact_ledger_entries (
        tenant_id, wallet_id, entry_type, amount, reference_entity, reference_id, created_by
    ) VALUES (
        p_tenant_id, p_wallet_id, 'Debit', p_amount + p_tax_amount, 'Invoice', p_invoice_id, p_created_by
    );

    COMMIT;
END$$

DELIMITER ;

-- --- 4c. sp_pay_invoice — original mismatch was missing p_tenant_id/p_client_wallet_id/
--         p_corp_wallet_id; also found (Bug #13) fact_transactions.description is NOT NULL with
--         no default and was never populated.
DROP PROCEDURE IF EXISTS sp_pay_invoice;

DELIMITER $$

CREATE PROCEDURE sp_pay_invoice(
    IN p_tenant_id INT,
    IN p_invoice_id BIGINT,
    IN p_client_wallet_id INT,
    IN p_corp_wallet_id INT,
    IN p_updated_by INT
)
BEGIN
    DECLARE v_total_amount DECIMAL(15,2);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    SELECT (amount + tax_amount) INTO v_total_amount FROM fact_invoices WHERE id = p_invoice_id;

    UPDATE fact_invoices SET status = 'Paid', updated_by = p_updated_by
    WHERE id = p_invoice_id AND tenant_id = p_tenant_id;

    INSERT INTO fact_transactions (
        tenant_id, from_wallet_id, to_wallet_id, amount, description, reference_entity,
        reference_id, transaction_type, status, created_by
    ) VALUES (
        p_tenant_id, p_client_wallet_id, p_corp_wallet_id, v_total_amount,
        CONCAT('Payment for Invoice #', p_invoice_id), 'Invoice', p_invoice_id, 'Income',
        'Completed', p_updated_by
    );

    INSERT INTO fact_ledger_entries (tenant_id, wallet_id, entry_type, amount, reference_entity, reference_id, created_by)
    VALUES (p_tenant_id, p_client_wallet_id, 'Debit', v_total_amount, 'Invoice', p_invoice_id, p_updated_by);

    COMMIT;
END$$

DELIMITER ;

-- --- 4d. sp_onboard_employee_full — was missing 6 fields Java sends (phone, manager, location,
--         currency, password_salt, department/designation resolution). Rewritten to accept the
--         full contract OnboardEmployeePayload already sends.
DROP PROCEDURE IF EXISTS sp_onboard_employee_full;

DELIMITER $$

CREATE PROCEDURE sp_onboard_employee_full(
    IN p_tenant_id INT,
    IN p_first_name VARCHAR(100),
    IN p_last_name VARCHAR(100),
    IN p_email VARCHAR(255),
    IN p_phone VARCHAR(50),
    IN p_department_id INT,
    IN p_designation_id INT,
    IN p_contract_type VARCHAR(50),
    IN p_salary DECIMAL(15,2),
    IN p_currency VARCHAR(10),
    IN p_join_date DATE,
    IN p_manager VARCHAR(100),
    IN p_location VARCHAR(100),
    IN p_contract_start DATE,
    IN p_contract_end DATE,
    IN p_allowances_json JSON,
    IN p_create_user TINYINT,
    IN p_password_hash VARCHAR(512),
    IN p_password_salt VARCHAR(255),
    IN p_role_id INT,
    IN p_created_by INT,
    OUT p_employee_id INT
)
BEGIN
    DECLARE v_user_id INT DEFAULT NULL;
    DECLARE v_department_name VARCHAR(100) DEFAULT NULL;
    DECLARE v_job_title VARCHAR(100) DEFAULT NULL;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    IF p_department_id IS NOT NULL THEN
        SELECT department_name INTO v_department_name
        FROM dim_departments
        WHERE id = p_department_id AND tenant_id = p_tenant_id;
    END IF;

    IF p_designation_id IS NOT NULL THEN
        SELECT title INTO v_job_title
        FROM dim_designations
        WHERE id = p_designation_id AND tenant_id = p_tenant_id;
    END IF;

    IF p_create_user = 1 THEN
        INSERT INTO users (
            tenant_id, first_name, last_name, email, password_hash, password_salt, role_id,
            is_enabled, created_by
        ) VALUES (
            p_tenant_id, p_first_name, p_last_name, p_email, p_password_hash, p_password_salt,
            p_role_id, b'1', p_created_by
        );
        SET v_user_id = LAST_INSERT_ID();
    END IF;

    INSERT INTO dim_employees (
        tenant_id, user_id, department_id, designation_id, first_name, last_name, email, phone,
        department, job_title, contract_type, salary, currency, join_date, manager, location,
        created_by
    ) VALUES (
        p_tenant_id, v_user_id, p_department_id, p_designation_id, p_first_name, p_last_name,
        p_email, p_phone, v_department_name, v_job_title, p_contract_type, p_salary, p_currency,
        p_join_date, p_manager, p_location, p_created_by
    );
    SET p_employee_id = LAST_INSERT_ID();

    INSERT INTO dim_employee_contracts (
        tenant_id, employee_id, start_date, end_date, allowances_json, created_by
    ) VALUES (
        p_tenant_id, p_employee_id, p_contract_start, p_contract_end, p_allowances_json, p_created_by
    );

    INSERT INTO dim_wallets (
        tenant_id, wallet_name, owner_type, employee_id, currency, created_by
    ) VALUES (
        p_tenant_id, CONCAT(p_first_name, ' ', p_last_name, ' Wallet'), 'Employee', p_employee_id,
        p_currency, p_created_by
    );

    COMMIT;
END$$

DELIMITER ;

-- --- 4e. sp_terminate_employee — dim_employees had no columns to record termination_date/reason;
--         added them (idempotent), then rewrote the procedure to store what
--         TerminateEmployeePayload already sends instead of silently dropping it.
SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_employees' AND COLUMN_NAME = 'termination_date'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE dim_employees ADD COLUMN termination_date DATE NULL',
    '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 = 'dim_employees' AND COLUMN_NAME = 'termination_reason'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE dim_employees ADD COLUMN termination_reason VARCHAR(500) NULL',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

DROP PROCEDURE IF EXISTS sp_terminate_employee;

DELIMITER $$

CREATE PROCEDURE sp_terminate_employee(
    IN p_tenant_id INT,
    IN p_employee_id INT,
    IN p_termination_date DATE,
    IN p_reason VARCHAR(500),
    IN p_updated_by INT
)
BEGIN
    DECLARE v_user_id INT;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    SELECT user_id INTO v_user_id FROM dim_employees WHERE id = p_employee_id;

    UPDATE dim_employees
    SET status = 'Terminated',
        is_deleted = 1,
        termination_date = p_termination_date,
        termination_reason = p_reason,
        updated_by = p_updated_by
    WHERE id = p_employee_id AND tenant_id = p_tenant_id;

    UPDATE dim_wallets
    SET status = 'Frozen', updated_by = p_updated_by
    WHERE employee_id = p_employee_id AND tenant_id = p_tenant_id;

    IF v_user_id IS NOT NULL THEN
        UPDATE users SET is_enabled = b'0', is_deleted = 1, updated_by = p_updated_by
        WHERE id = v_user_id AND tenant_id = p_tenant_id;

        UPDATE user_api_keys SET is_deleted = 1 WHERE user_id = v_user_id;
    END IF;

    COMMIT;
END$$

DELIMITER ;

-- --- 4f. sp_suspend_tenant — Java already sends p_reason/p_suspend_until, but the procedure
--         ignored both. Store them in platform_audit_logs.description (existing JSON audit-trail
--         column) rather than adding new tenants columns.
DROP PROCEDURE IF EXISTS sp_suspend_tenant;

DELIMITER $$

CREATE PROCEDURE sp_suspend_tenant(
    IN p_tenant_id INT,
    IN p_reason VARCHAR(500),
    IN p_suspend_until DATE,
    IN p_performed_by INT
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    UPDATE tenants SET status = 'Suspended', updated_by = p_performed_by WHERE id = p_tenant_id;
    UPDATE users SET is_enabled = b'0', status = 'Inactive', updated_by = p_performed_by
    WHERE tenant_id = p_tenant_id;

    INSERT INTO platform_audit_logs (tenant_id, action_type, performed_by, description)
    VALUES (
        p_tenant_id, 'SUSPEND_TENANT', p_performed_by,
        JSON_OBJECT('status', 'Suspended', 'reason', p_reason, 'suspendUntil', p_suspend_until)
    );

    COMMIT;
END$$

DELIMITER ;

-- Not included (no SQL change needed — Java-only fixes, see plans/java-fixes-plan.md):
--   sp_soft_delete_user, sp_soft_delete_company, sp_link_employee_to_user,
--   sp_update_opportunity_status, sp_assign_role_to_user, sp_snapshot_tenant_usage.


-- ============================================================================================
-- SECTION 5 — Bug #14: dim_expense_categories schema drift
-- DimExpenseCategory entity declares is_deleted, but dim_expense_categories never had that
-- column. Broke every soft-delete-aware category lookup, surfacing transitively whenever a
-- Transaction or Ledger entry with a linked expense category/subcategory was read.
-- ============================================================================================

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;


-- ============================================================================================
-- SECTION 6 — Feature addition (2026-07-16): dim_financial_entities contact fields
-- Adds bank contact number/address/fax/email plus connected phone/email/given address to the
-- saved Bank/CreditCard account record. Purely additive, all nullable. See
-- plans/production-sql-changes.md item 6 for full rationale.
-- ============================================================================================

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_financial_entities' AND COLUMN_NAME = 'bank_contact_number'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE dim_financial_entities ADD COLUMN bank_contact_number VARCHAR(50) NULL AFTER bank_or_issuer_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 = 'dim_financial_entities' AND COLUMN_NAME = 'bank_address'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE dim_financial_entities ADD COLUMN bank_address VARCHAR(500) NULL AFTER bank_contact_number',
    '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 = 'dim_financial_entities' AND COLUMN_NAME = 'bank_fax_number'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE dim_financial_entities ADD COLUMN bank_fax_number VARCHAR(50) NULL AFTER bank_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 = 'dim_financial_entities' AND COLUMN_NAME = 'bank_email'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE dim_financial_entities ADD COLUMN bank_email VARCHAR(255) NULL AFTER bank_fax_number',
    '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 = 'dim_financial_entities' AND COLUMN_NAME = 'connected_phone_no'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE dim_financial_entities ADD COLUMN connected_phone_no VARCHAR(50) NULL AFTER bank_email',
    '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 = 'dim_financial_entities' AND COLUMN_NAME = 'connected_email'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE dim_financial_entities ADD COLUMN connected_email VARCHAR(255) NULL AFTER connected_phone_no',
    '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 = 'dim_financial_entities' AND COLUMN_NAME = 'given_address'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE dim_financial_entities ADD COLUMN given_address VARCHAR(500) NULL AFTER connected_email',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ============================================================================================
-- END OF SCRIPT
-- ============================================================================================