How to clean customers for onboarding and review KYC¶

BEGIN TRANSACTION;
DO $$
DECLARE
    table_name TEXT;
    member_id TEXT[] := ARRAY['20']::TEXT[];
    delete_query TEXT;
    member_id_count INT;
    member_count_before INT;
    member_count_after INT;
    tables TEXT[] := ARRAY[
        're_kyc_suite_test_answers',
        're_kyc_suite_tests',
        'suite_test_answers',
        'suite_tests',
        'suite_tests_history',
        're_kyc_risk_scores',
        're_kyc_occupations',
        're_kyc_customer_details',
        're_kyc_appman_recognition',
        're_kyc_appman_liveness_attributes',
        're_kyc_appman_liveness',
        're_kyc_appman_idcard',
        're_kyc_appman',
        're_kyc',
        'ndid_customer_data',
        'ndid',
        'documents',
        'banks_history',
        'banks',
        'cdd',
        'occupations_history',
        'occupations',
        'risk_scores',
        'source_of_funds_history',
        'source_of_funds',
        'customer_details_history',
        'customer_details',
        'addresses_history',
        'addresses',
        'appman_recognition',
        'appman_liveness_attributes',
        'appman_liveness',
        'appman_idcard',
        'appman',
        'approver_re_kycs',
        'approvers',
        'pep_watch_list',
        'thaid_logs',
        'thaid_infos',
        'pre_members'
    ];
BEGIN
    member_id_count := array_length(member_id, 1);
    SELECT COUNT(*) INTO member_count_before FROM customer_details;
    RAISE NOTICE 'Before delete, current member amount : %', member_count_before;

    FOREACH table_name IN ARRAY tables
    LOOP
        BEGIN
            IF table_name = 'suite_test_answers' THEN
                IF member_id_count IS NULL THEN
                    delete_query := 'DELETE FROM suite_test_answers';
                ELSIF member_id_count = 1 THEN
                    delete_query := format($f$
                        DELETE FROM suite_test_answers
                        WHERE reference_id IN (
                            SELECT id FROM suite_tests WHERE member_id = %L
                        )
                    $f$, member_id[1]);
                ELSE
                    delete_query := format($f$
                        DELETE FROM suite_test_answers
                        WHERE reference_id IN (
                            SELECT id FROM suite_tests WHERE member_id = ANY(%L)
                        )
                    $f$, member_id);
                END IF;

            ELSIF table_name = 're_kyc_suite_test_answers' THEN
                IF member_id_count IS NULL THEN
                    delete_query := 'DELETE FROM re_kyc_suite_test_answers';
                ELSIF member_id_count = 1 THEN
                    delete_query := format($f$
                        DELETE FROM re_kyc_suite_test_answers
                        WHERE reference_id IN (
                            SELECT id FROM re_kyc_suite_tests WHERE member_id = %L
                        )
                    $f$, member_id[1]);
                ELSE
                    delete_query := format($f$
                        DELETE FROM re_kyc_suite_test_answers
                        WHERE reference_id IN (
                            SELECT id FROM re_kyc_suite_tests WHERE member_id = ANY(%L)
                        )
                    $f$, member_id);
                END IF;

            ELSE
                IF member_id_count IS NULL THEN
                    delete_query := format('DELETE FROM %I', table_name);
                ELSIF member_id_count = 1 THEN
                    delete_query := format('DELETE FROM %I WHERE member_id = %L', table_name, member_id[1]);
                ELSE
                    delete_query := format('DELETE FROM %I WHERE member_id = ANY(%L)', table_name, member_id);
                END IF;
            END IF;

            EXECUTE delete_query;
            RAISE NOTICE 'Deleted from table %', table_name;

        EXCEPTION
            WHEN undefined_table THEN
                RAISE NOTICE 'Table % does not exist, skipping.', table_name;
            WHEN undefined_column THEN
                RAISE NOTICE 'Table % has no member_id column, skipping.', table_name;
            WHEN others THEN
                RAISE NOTICE 'Unexpected error in table %: %', table_name, SQLERRM;
        END;
    END LOOP;
    SELECT COUNT(*) INTO member_count_after FROM customer_details;
    RAISE NOTICE 'After delete, current member amount : %', member_count_after;
END;
$$;


-- COMMIT;
-- ROLLBACK;