-- =====================================================
-- CLEAN MEMBER DATA
-- Hapus semua data anggota (members + users bertipe member)
-- AMAN: user admin/super_admin/manager/cashier TIDAK dihapus
-- Jalankan dengan: psql -d <database> -f clean_members.sql
-- =====================================================

BEGIN;

-- 1. Hapus notifikasi milik user bertipe member
DELETE FROM notifications
WHERE user_id IN (
    SELECT id FROM users WHERE role = 'member'
);

-- 2. Hapus SHU member shares
DELETE FROM shu_member_shares
WHERE member_id IN (SELECT id FROM members);

-- 3. Hapus virtual accounts milik member
DELETE FROM virtual_accounts
WHERE member_id IN (SELECT id FROM members);

-- 4. Hapus PPOB transactions milik member
DELETE FROM ppob_transactions
WHERE member_id IN (SELECT id FROM members);

-- 5. Hapus donation contributions milik member
DELETE FROM donation_contributions
WHERE member_id IN (SELECT id FROM members);

-- 6. Hapus community fee payments milik member
DELETE FROM community_fee_payments
WHERE member_id IN (SELECT id FROM members);

-- 7. Hapus data Community module baru (bills, donations)
DELETE FROM community_bills
WHERE member_id IN (SELECT id FROM members);

DELETE FROM community_donations
WHERE member_id IN (SELECT id FROM members);

-- 8. Hapus transaksi WasSerba milik member
--    (ws_transaction_items otomatis di-cascade dari ws_transactions)
DELETE FROM ws_transactions
WHERE member_id IN (SELECT id FROM members);

-- 9. Hapus order items & orders milik member
DELETE FROM order_items
WHERE order_id IN (
    SELECT id FROM orders
    WHERE member_id IN (SELECT id FROM members)
);

DELETE FROM orders
WHERE member_id IN (SELECT id FROM members);

-- 10. Hapus marketplace (items -> products milik member -> orders)
-- Hapus items yang merujuk produk milik member
DELETE FROM marketplace_order_items
WHERE product_id IN (
    SELECT id FROM marketplace_products
    WHERE seller_id IN (SELECT id FROM members)
);

-- Hapus items dari order milik member yang belum terhapus
DELETE FROM marketplace_order_items
WHERE order_id IN (
    SELECT id FROM marketplace_orders
    WHERE buyer_id  IN (SELECT id FROM members)
       OR seller_id IN (SELECT id FROM members)
);

DELETE FROM marketplace_orders
WHERE buyer_id  IN (SELECT id FROM members)
   OR seller_id IN (SELECT id FROM members);

-- Hapus produk milik member (meskipun sudah CASCADE, eksplisit lebih aman)
DELETE FROM marketplace_products
WHERE seller_id IN (SELECT id FROM members);

-- 11. Hapus data pinjaman (urutan: payments -> schedules -> applications)
DELETE FROM loan_payments
WHERE member_id IN (SELECT id FROM members);

DELETE FROM loan_schedules
WHERE loan_id IN (
    SELECT id FROM loan_applications
    WHERE member_id IN (SELECT id FROM members)
);

DELETE FROM loan_applications
WHERE member_id IN (SELECT id FROM members);

-- 11. Hapus data simpanan (transactions -> accounts)
DELETE FROM savings_transactions
WHERE member_id IN (SELECT id FROM members);

DELETE FROM savings_accounts
WHERE member_id IN (SELECT id FROM members);

-- 12. Hapus semua anggota (members)
DELETE FROM members;

-- 13. Hapus user bertipe member
DELETE FROM users
WHERE role = 'member';

-- =====================================================
-- VERIFIKASI
-- =====================================================
SELECT 'members'              AS tabel, COUNT(*) AS sisa FROM members
UNION ALL
SELECT 'users (member)'       AS tabel, COUNT(*) AS sisa FROM users WHERE role = 'member'
UNION ALL
SELECT 'users (non-member)'   AS tabel, COUNT(*) AS sisa FROM users WHERE role != 'member'
UNION ALL
SELECT 'savings_accounts'     AS tabel, COUNT(*) AS sisa FROM savings_accounts
UNION ALL
SELECT 'savings_transactions' AS tabel, COUNT(*) AS sisa FROM savings_transactions
UNION ALL
SELECT 'loan_applications'    AS tabel, COUNT(*) AS sisa FROM loan_applications
UNION ALL
SELECT 'ppob_transactions'    AS tabel, COUNT(*) AS sisa FROM ppob_transactions
UNION ALL
SELECT 'orders'               AS tabel, COUNT(*) AS sisa FROM orders;

COMMIT;