cbsnexora-backend/scripts/clear_all_client_data.sql

-- ============================================================
-- CLEAR ALL CLIENT DATA - Fineract CBS
-- Run: PGPASSWORD=root psql -h localhost -U postgres -d fineract_default -f scripts/clear_all_client_data.sql
-- ============================================================

BEGIN;

-- 1. Account transfers
DELETE FROM m_account_transfer_transaction;
DELETE FROM m_account_transfer_details;
DELETE FROM m_account_transfer_standing_instructions;
DELETE FROM m_account_transfer_standing_instructions_history;

-- 2. Accounting / Journal entries
DELETE FROM acc_gl_journal_entry;

-- 3. Portfolio associations
DELETE FROM m_portfolio_account_associations;

-- 4. Loans (child tables first)
DELETE FROM m_loan_transaction_repayment_schedule_mapping;
DELETE FROM m_loan_transaction_relation;
DELETE FROM m_loan_charge_paid_by;
DELETE FROM m_loan_installment_charge;
DELETE FROM m_loan_overdue_installment_charge;
DELETE FROM m_loan_tranche_charges;
DELETE FROM m_loan_tranche_disbursement_charge;
DELETE FROM m_loan_repayment_schedule_history;
DELETE FROM m_loan_repayment_schedule;
DELETE FROM m_loan_transaction;
DELETE FROM m_loan_charge;
DELETE FROM m_loan_collateral_management;
DELETE FROM m_loan_collateral;
DELETE FROM m_loan_disbursement_detail;
DELETE FROM m_loan_arrears_aging;
DELETE FROM m_loan_officer_assignment_history;
DELETE FROM m_loan_status_change_history;
DELETE FROM m_loan_delinquency_action;
DELETE FROM m_loan_delinquency_tag_history;
DELETE FROM m_loan_installment_delinquency_tag;
DELETE FROM m_loan_term_variations;
DELETE FROM m_loan_rate;
DELETE FROM m_loan_topup;
DELETE FROM m_loan_buy_down_fee_balance;
DELETE FROM m_loan_capitalized_income_balance;
DELETE FROM m_loan_progressive_model;
DELETE FROM m_loan_reage_parameter;
DELETE FROM m_loan_reamortization_parameter;
DELETE FROM m_loan_recalculation_details;
DELETE FROM m_loan_interest_recalculation_additional_details;
DELETE FROM m_loan_approved_amount_history;
DELETE FROM m_loan_reschedule_request_term_variations_mapping;
DELETE FROM m_loan_reschedule_request;
DELETE FROM m_loan_amortization_allocation_mapping;
DELETE FROM m_loan_account_locks;
DELETE FROM m_loanproduct_provisioning_entry;
DELETE FROM m_loan_originator_mapping;
DELETE FROM m_loan;

-- 5. Notes (all)
DELETE FROM m_note;

-- 6. Clear savings FK on client before deleting savings
UPDATE m_client SET default_savings_account = NULL;
UPDATE m_client SET default_savings_product = NULL;

-- 7. Savings (child tables first)
DELETE FROM m_savings_account_charge_paid_by;
DELETE FROM m_savings_account_transaction_tax_details;
DELETE FROM m_savings_account_transaction;
DELETE FROM m_savings_account_charge;
DELETE FROM m_savings_interest_incentives;
DELETE FROM m_savings_account_interest_rate_slab;
DELETE FROM m_savings_account_interest_rate_chart;
DELETE FROM m_savings_officer_assignment_history;
DELETE FROM m_savings_account;

-- 8. Documents & images
DELETE FROM m_document WHERE parent_entity_type = 'clients';

-- 9. Client nominee
DELETE FROM m_client_nominee;

-- 10. Family members
DELETE FROM m_family_members;

-- 11. KYC
DELETE FROM m_client_kyc_verification_log;
DELETE FROM m_client_kyc;

-- 12. Form60
DELETE FROM m_client_form60;

-- 13. Address
DELETE FROM m_client_address;
DELETE FROM m_address;

-- 14. Identifiers
DELETE FROM m_client_identifier;

-- 15. DIP / Mobile lookup logs
DELETE FROM m_mobile_lookup_log;

-- 16. Sanctions screening logs
DELETE FROM m_sanctions_screening_log;

-- 17. Client charges & transactions
DELETE FROM m_client_charge_paid_by;
DELETE FROM m_client_charge;
DELETE FROM m_client_transaction;
DELETE FROM m_client_transfer_details;

-- 18. Group-client mappings
DELETE FROM m_group_client;

-- 19. Client non-person
DELETE FROM m_client_non_person;

-- 20. Client collateral
DELETE FROM m_client_collateral_management;

-- 21. Other client sub-tables
DELETE FROM m_client_attendance;
DELETE FROM m_client_eou;
DELETE FROM m_client_msme;
DELETE FROM m_client_mudra_borrower;
DELETE FROM m_client_non_priority_sector;
DELETE FROM m_client_priority_sector;
DELETE FROM m_client_startup_india;
DELETE FROM m_client_import_export;

-- 22. Clear self-referencing FK, image FK, then delete clients
UPDATE m_client SET guardian_client_id = NULL;
UPDATE m_client SET image_id = NULL;
DELETE FROM m_client;

-- 23. Clean orphaned images
DELETE FROM m_image;

COMMIT;

SELECT '=== ALL CLIENT DATA CLEARED SUCCESSFULLY ===' AS result;