-- ============================================================
-- FinServe LMS — Clear Transactional Data
-- ============================================================
-- SAFE TO RE-RUN: All statements are idempotent.
-- PRESERVES:       Organisation, Branches, Users, Roles, Permissions,
--                  Members, Loan Products, Chart of Accounts (structure),
--                  System Config, Staff, Groups (structure), etc.
--
-- Deletion order respects FK constraints (children before parents).
-- ============================================================

-- Temporarily disable FK checks for safety (PostgreSQL)
SET session_replication_role = 'replica';

-- ════════════════════════════════════════════════════════════
-- 1. LOAN MODULE — child tables first
-- ════════════════════════════════════════════════════════════
DELETE FROM loan_recoveries;
DELETE FROM loan_write_offs;
DELETE FROM loan_waivers;
DELETE FROM loan_top_ups;
DELETE FROM loan_insurances;
DELETE FROM loan_approvals;
DELETE FROM loan_appraisals;
DELETE FROM loan_guarantors;
DELETE FROM loan_collaterals;
DELETE FROM loan_repayments;
DELETE FROM loan_schedules;
DELETE FROM loan_status_history;
DELETE FROM provision_records;
DELETE FROM collection_activities;
DELETE FROM promise_to_pay;
DELETE FROM collection_schedules;

-- ════════════════════════════════════════════════════════════
-- 2. JOURNAL ENTRIES (depend on loans, chart_of_accounts)
-- ════════════════════════════════════════════════════════════
DELETE FROM journal_entry_lines;
DELETE FROM journal_entries;

-- ════════════════════════════════════════════════════════════
-- 3. LOANS (parent — depends on members, products, groups)
-- ════════════════════════════════════════════════════════════
DELETE FROM loans;

-- ════════════════════════════════════════════════════════════
-- 4. SAVINGS / SHARES / FIXED DEPOSITS
-- ════════════════════════════════════════════════════════════
DELETE FROM savings_transactions;
DELETE FROM standing_orders;
DELETE FROM fixed_deposits;
DELETE FROM share_transactions;
DELETE FROM share_accounts;
DELETE FROM savings_accounts;

-- ════════════════════════════════════════════════════════════
-- 5. CASHBOOK & INTER-BRANCH
-- ════════════════════════════════════════════════════════════
DELETE FROM cashbook_entries;
DELETE FROM inter_branch_transfers;

-- ════════════════════════════════════════════════════════════
-- 6. EXPENSES
-- ════════════════════════════════════════════════════════════
DELETE FROM expenses;

-- ════════════════════════════════════════════════════════════
-- 7. PAYROLL
DELETE FROM staff_salary_components;
DELETE FROM payroll_items;
DELETE FROM cash_advance_repayments;
DELETE FROM cash_advances;
DELETE FROM payroll_runs;

-- ════════════════════════════════════════════════════════════
-- 8. AUDIT / NOTIFICATIONS / AML / GROUP MEETINGS
-- ════════════════════════════════════════════════════════════
DELETE FROM audit_logs;
DELETE FROM notifications;
DELETE FROM aml_records;
DELETE FROM group_meeting_attendance;
DELETE FROM group_meetings;

-- ════════════════════════════════════════════════════════════
-- 9. BUDGET actuals (keep budget structure, reset amounts)
-- ════════════════════════════════════════════════════════════
DELETE FROM budget_lines;
UPDATE budgets SET total_actual = 0, variance = 0;

-- ════════════════════════════════════════════════════════════
-- 10. RESET ACCOUNT BALANCES (keep Chart of Accounts structure)
-- ════════════════════════════════════════════════════════════
UPDATE chart_of_accounts
SET balance = 0,
    ytd_balance = 0,
    opening_balance = 0;

UPDATE org_bank_accounts
SET current_balance = 0,
    last_reconciled_at = NULL,
    last_recon_balance = NULL;

UPDATE cashbook_accounts
SET balance = 0,
    opening_balance = 0;

-- Reset group savings balance
UPDATE groups SET group_savings_balance = 0;

-- ════════════════════════════════════════════════════════════
-- 11. RESET MEMBER RISK / CREDIT STATUS
-- ════════════════════════════════════════════════════════════
UPDATE members
SET risk_level = 'LOW',
    risk_score = NULL,
    risk_assessed_at = NULL,
    risk_assessed_by_id = NULL,
    crb_status = 'NOT_CHECKED',
    crb_score = NULL,
    crb_checked_at = NULL,
    crb_checked_by_id = NULL,
    crb_report_url = NULL,
    kyc_level = 'NONE',
    kyc_score = 0,
    kyc_completed_at = NULL,
    kyc_reviewed_by_id = NULL,
    pep_flag = false,
    sanctions_flag = false,
    edd_required = false,
    edd_completed_at = NULL,
    income_verified = false,
    income_verified_at = NULL,
    income_verified_by_id = NULL;

-- Reset group member contributions
UPDATE group_members
SET total_contributions = 0,
    contribution_freq_met = 0;

-- Re-enable FK checks
SET session_replication_role = 'origin';

-- ============================================================
-- VERIFICATION
-- ============================================================
SELECT 'loans' AS tbl, COUNT(*) AS remaining FROM loans
UNION ALL
SELECT 'loan_repayments', COUNT(*) FROM loan_repayments
UNION ALL
SELECT 'loan_schedules', COUNT(*) FROM loan_schedules
UNION ALL
SELECT 'journal_entries', COUNT(*) FROM journal_entries
UNION ALL
SELECT 'journal_entry_lines', COUNT(*) FROM journal_entry_lines
UNION ALL
SELECT 'savings_accounts', COUNT(*) FROM savings_accounts
UNION ALL
SELECT 'savings_transactions', COUNT(*) FROM savings_transactions
UNION ALL
SELECT 'chart_of_accounts (config kept)', COUNT(*) FROM chart_of_accounts
UNION ALL
SELECT 'organisations (config kept)', COUNT(*) FROM organisations
UNION ALL
SELECT 'members (config kept)', COUNT(*) FROM members
ORDER BY tbl;