cbsnexora-backend/database_migration_facility_charges.sql

-- Migration: Add facility charge columns to product facility config tables
-- Each facility (ATM, UPI, IMPS, etc.) links to an existing m_charge entry
-- When account is activated, enabled facilities auto-apply their linked charge

-- Current Product Facility Config
ALTER TABLE m_current_product_facility_config
    ADD COLUMN IF NOT EXISTS atm_facility_charge_id         BIGINT NULL,
    ADD COLUMN IF NOT EXISTS atm_withdrawal_charge_id       BIGINT NULL,
    ADD COLUMN IF NOT EXISTS upi_facility_charge_id         BIGINT NULL,
    ADD COLUMN IF NOT EXISTS pos_transactions_charge_id     BIGINT NULL,
    ADD COLUMN IF NOT EXISTS cheque_book_charge_id          BIGINT NULL,
    ADD COLUMN IF NOT EXISTS imps_facility_charge_id        BIGINT NULL,
    ADD COLUMN IF NOT EXISTS sms_alerts_charge_id           BIGINT NULL,
    ADD COLUMN IF NOT EXISTS bbps_facility_charge_id        BIGINT NULL,
    ADD COLUMN IF NOT EXISTS insurance_cover_charge_id      BIGINT NULL,
    ADD COLUMN IF NOT EXISTS bepg_facility_charge_id        BIGINT NULL,
    ADD COLUMN IF NOT EXISTS neft_facility_charge_id        BIGINT NULL,
    ADD COLUMN IF NOT EXISTS rtgs_facility_charge_id        BIGINT NULL,
    ADD COLUMN IF NOT EXISTS inter_bank_total_cap_charge_id BIGINT NULL,
    ADD COLUMN IF NOT EXISTS deposit_facility_kyc_charge_id BIGINT NULL,
    ADD COLUMN IF NOT EXISTS virtual_debit_card_charge_id   BIGINT NULL;

-- Foreign key constraints for current product
ALTER TABLE m_current_product_facility_config
    ADD CONSTRAINT fk_cpfc_atm_charge         FOREIGN KEY (atm_facility_charge_id)         REFERENCES m_charge(id),
    ADD CONSTRAINT fk_cpfc_atm_wd_charge       FOREIGN KEY (atm_withdrawal_charge_id)       REFERENCES m_charge(id),
    ADD CONSTRAINT fk_cpfc_upi_charge          FOREIGN KEY (upi_facility_charge_id)         REFERENCES m_charge(id),
    ADD CONSTRAINT fk_cpfc_pos_charge          FOREIGN KEY (pos_transactions_charge_id)     REFERENCES m_charge(id),
    ADD CONSTRAINT fk_cpfc_chq_charge          FOREIGN KEY (cheque_book_charge_id)          REFERENCES m_charge(id),
    ADD CONSTRAINT fk_cpfc_imps_charge         FOREIGN KEY (imps_facility_charge_id)        REFERENCES m_charge(id),
    ADD CONSTRAINT fk_cpfc_sms_charge          FOREIGN KEY (sms_alerts_charge_id)           REFERENCES m_charge(id),
    ADD CONSTRAINT fk_cpfc_bbps_charge         FOREIGN KEY (bbps_facility_charge_id)        REFERENCES m_charge(id),
    ADD CONSTRAINT fk_cpfc_ins_charge          FOREIGN KEY (insurance_cover_charge_id)      REFERENCES m_charge(id),
    ADD CONSTRAINT fk_cpfc_bepg_charge         FOREIGN KEY (bepg_facility_charge_id)        REFERENCES m_charge(id),
    ADD CONSTRAINT fk_cpfc_neft_charge         FOREIGN KEY (neft_facility_charge_id)        REFERENCES m_charge(id),
    ADD CONSTRAINT fk_cpfc_rtgs_charge         FOREIGN KEY (rtgs_facility_charge_id)        REFERENCES m_charge(id),
    ADD CONSTRAINT fk_cpfc_ibtc_charge         FOREIGN KEY (inter_bank_total_cap_charge_id) REFERENCES m_charge(id),
    ADD CONSTRAINT fk_cpfc_dep_charge          FOREIGN KEY (deposit_facility_kyc_charge_id) REFERENCES m_charge(id),
    ADD CONSTRAINT fk_cpfc_vdc_charge          FOREIGN KEY (virtual_debit_card_charge_id)   REFERENCES m_charge(id);

-- Savings Product Facility Config
ALTER TABLE m_savings_product_facility_config
    ADD COLUMN IF NOT EXISTS atm_facility_charge_id         BIGINT NULL,
    ADD COLUMN IF NOT EXISTS atm_withdrawal_charge_id       BIGINT NULL,
    ADD COLUMN IF NOT EXISTS upi_facility_charge_id         BIGINT NULL,
    ADD COLUMN IF NOT EXISTS pos_transactions_charge_id     BIGINT NULL,
    ADD COLUMN IF NOT EXISTS cheque_book_charge_id          BIGINT NULL,
    ADD COLUMN IF NOT EXISTS imps_facility_charge_id        BIGINT NULL,
    ADD COLUMN IF NOT EXISTS sms_alerts_charge_id           BIGINT NULL,
    ADD COLUMN IF NOT EXISTS bbps_facility_charge_id        BIGINT NULL,
    ADD COLUMN IF NOT EXISTS insurance_cover_charge_id      BIGINT NULL,
    ADD COLUMN IF NOT EXISTS bepg_facility_charge_id        BIGINT NULL,
    ADD COLUMN IF NOT EXISTS neft_facility_charge_id        BIGINT NULL,
    ADD COLUMN IF NOT EXISTS rtgs_facility_charge_id        BIGINT NULL,
    ADD COLUMN IF NOT EXISTS inter_bank_total_cap_charge_id BIGINT NULL,
    ADD COLUMN IF NOT EXISTS deposit_facility_kyc_charge_id BIGINT NULL,
    ADD COLUMN IF NOT EXISTS virtual_debit_card_charge_id   BIGINT NULL;

-- Foreign key constraints for savings product
ALTER TABLE m_savings_product_facility_config
    ADD CONSTRAINT fk_spfc_atm_charge         FOREIGN KEY (atm_facility_charge_id)         REFERENCES m_charge(id),
    ADD CONSTRAINT fk_spfc_atm_wd_charge       FOREIGN KEY (atm_withdrawal_charge_id)       REFERENCES m_charge(id),
    ADD CONSTRAINT fk_spfc_upi_charge          FOREIGN KEY (upi_facility_charge_id)         REFERENCES m_charge(id),
    ADD CONSTRAINT fk_spfc_pos_charge          FOREIGN KEY (pos_transactions_charge_id)     REFERENCES m_charge(id),
    ADD CONSTRAINT fk_spfc_chq_charge          FOREIGN KEY (cheque_book_charge_id)          REFERENCES m_charge(id),
    ADD CONSTRAINT fk_spfc_imps_charge         FOREIGN KEY (imps_facility_charge_id)        REFERENCES m_charge(id),
    ADD CONSTRAINT fk_spfc_sms_charge          FOREIGN KEY (sms_alerts_charge_id)           REFERENCES m_charge(id),
    ADD CONSTRAINT fk_spfc_bbps_charge         FOREIGN KEY (bbps_facility_charge_id)        REFERENCES m_charge(id),
    ADD CONSTRAINT fk_spfc_ins_charge          FOREIGN KEY (insurance_cover_charge_id)      REFERENCES m_charge(id),
    ADD CONSTRAINT fk_spfc_bepg_charge         FOREIGN KEY (bepg_facility_charge_id)        REFERENCES m_charge(id),
    ADD CONSTRAINT fk_spfc_neft_charge         FOREIGN KEY (neft_facility_charge_id)        REFERENCES m_charge(id),
    ADD CONSTRAINT fk_spfc_rtgs_charge         FOREIGN KEY (rtgs_facility_charge_id)        REFERENCES m_charge(id),
    ADD CONSTRAINT fk_spfc_ibtc_charge         FOREIGN KEY (inter_bank_total_cap_charge_id) REFERENCES m_charge(id),
    ADD CONSTRAINT fk_spfc_dep_charge          FOREIGN KEY (deposit_facility_kyc_charge_id) REFERENCES m_charge(id),
    ADD CONSTRAINT fk_spfc_vdc_charge          FOREIGN KEY (virtual_debit_card_charge_id)   REFERENCES m_charge(id);