-- Cleanup script to reset user data (transactions, sales, purchases, inventory, products, partners, budgets)
-- while keeping wallets (resetting their balances and credit limits) and UOMs.
-- Usage: Execute this script in the kifi-v2 database.

DO $$
DECLARE
    uid INT;
    target_users INT[] := ARRAY[2, 3];
BEGIN
    FOREACH uid IN ARRAY target_users LOOP
        RAISE NOTICE 'Resetting data for user %', uid;

        -- SALES
        DELETE FROM invoice_payments WHERE invoice_id IN (SELECT id FROM invoices WHERE user_id = uid);
        DELETE FROM invoice_items WHERE invoice_id IN (SELECT id FROM invoices WHERE user_id = uid);
        DELETE FROM invoices WHERE user_id = uid;

        -- PURCHASES
        DELETE FROM purchase_payments WHERE po_id IN (SELECT id FROM purchase_orders WHERE user_id = uid);
        DELETE FROM purchase_order_items WHERE po_id IN (SELECT id FROM purchase_orders WHERE user_id = uid);
        DELETE FROM purchase_orders WHERE user_id = uid;

        -- INVENTORY & STOCK
        DELETE FROM inventory_movement_items WHERE movement_id IN (SELECT id FROM inventory_movements WHERE user_id = uid);
        DELETE FROM inventory_movements WHERE user_id = uid;
        DELETE FROM inventory_balances WHERE product_id IN (SELECT id FROM products WHERE user_id = uid);
        DELETE FROM inventory_locations WHERE user_id = uid;

        -- BUDGETS
        DELETE FROM budgets WHERE user_id = uid;

        -- TRANSACTIONS
        DELETE FROM transaction_items WHERE transaction_id IN (SELECT id FROM transactions WHERE user_id = uid);
        DELETE FROM transaction_attachments WHERE transaction_id IN (SELECT id FROM transactions WHERE user_id = uid);
        DELETE FROM recurring_transactions WHERE user_id = uid;
        DELETE FROM transactions WHERE user_id = uid;

        -- PRODUCTS
        DELETE FROM product_bom WHERE parent_product_id IN (SELECT id FROM products WHERE user_id = uid) OR component_product_id IN (SELECT id FROM products WHERE user_id = uid);
        DELETE FROM product_images WHERE product_id IN (SELECT id FROM products WHERE user_id = uid);
        DELETE FROM products WHERE user_id = uid;
        DELETE FROM product_categories WHERE user_id = uid;

        -- PARTNERS (Customers & Vendors)
        DELETE FROM customers WHERE user_id = uid;
        DELETE FROM vendors WHERE user_id = uid;

        -- WALLETS (Reset balances, keep structure)
        UPDATE wallets 
        SET balance = 0, 
            credit_limit = NULL, 
            fixed_amount = NULL, 
            cycle_date = NULL, 
            payment_cycle = NULL
        WHERE owner_id = uid;

    END LOOP;
END $$;