-- ============================================================================
-- Test: Verify Credit Management Works with Cases
-- ============================================================================

-- Test 1: Check billable_parties view includes clients
SELECT 
    'Test 1: Billable Parties View' as test_name,
    COUNT(*) as total_count,
    COUNT(*) FILTER (WHERE party_type = 'customer') as customer_count,
    COUNT(*) FILTER (WHERE party_type = 'client') as client_count
FROM billable_parties;

-- Test 2: Check credit_limits table structure
SELECT 
    'Test 2: Credit Limits Table Columns' as test_name,
    column_name,
    data_type
FROM information_schema.columns
WHERE table_name = 'credit_limits'
AND column_name IN ('party_id', 'party_type', 'last_utilization_update', 'updated_at')
ORDER BY column_name;

-- Test 3: Check triggers exist
SELECT 
    'Test 3: Credit Triggers' as test_name,
    tgname as trigger_name,
    tgrelid::regclass as table_name
FROM pg_trigger
WHERE tgname LIKE '%credit%'
ORDER BY tgname;

-- Test 4: Check functions exist
SELECT 
    'Test 4: Credit Functions' as test_name,
    proname as function_name,
    pronargs as arg_count
FROM pg_proc
WHERE proname IN ('get_invoice_party', 'update_credit_utilization', 'trigger_update_credit_on_invoice_change')
ORDER BY proname;

-- Test 5: Simulate invoice creation from case (should NOT error)
DO $$
DECLARE
    v_test_client_id uuid;
    v_test_case_id uuid;
    v_test_org_id uuid;
    v_test_invoice_id uuid;
BEGIN
    -- Get a real client and org (if exists)
    SELECT client_id, org_id 
    INTO v_test_client_id, v_test_org_id
    FROM clients 
    WHERE isdeleted = false 
    LIMIT 1;
    
    IF v_test_client_id IS NULL THEN
        RAISE NOTICE 'Test 5: SKIPPED - No clients found';
        RETURN;
    END IF;
    
    -- Get or create a test case
    SELECT case_id 
    INTO v_test_case_id
    FROM cases 
    WHERE client_id = v_test_client_id 
      AND isdeleted = false 
    LIMIT 1;
    
    IF v_test_case_id IS NULL THEN
        RAISE NOTICE 'Test 5: SKIPPED - No cases found';
        RETURN;
    END IF;
    
    -- Create a test invoice (will be rolled back)
    BEGIN
        INSERT INTO invoices (
            reference_table,
            reference_id,
            org_id,
            amount,
            invoice_date,
            status
        ) VALUES (
            'cases',
            v_test_case_id,
            v_test_org_id,
            1000.00,
            CURRENT_DATE,
            'draft'
        ) RETURNING invoice_id INTO v_test_invoice_id;
        
        RAISE NOTICE 'Test 5: SUCCESS - Invoice % created from case % without errors!', 
            v_test_invoice_id, v_test_case_id;
        
        -- Test get_invoice_party function
        DECLARE
            v_party_id uuid;
            v_party_type varchar;
        BEGIN
            SELECT party_id, party_type 
            INTO v_party_id, v_party_type
            FROM get_invoice_party(v_test_invoice_id);
            
            RAISE NOTICE 'Test 5: Resolved party_id=%, party_type=%', v_party_id, v_party_type;
        END;
        
        -- Cleanup - rollback the test invoice
        DELETE FROM invoices WHERE invoice_id = v_test_invoice_id;
        
    EXCEPTION WHEN OTHERS THEN
        RAISE NOTICE 'Test 5: FAILED - Error: %', SQLERRM;
    END;
    
END$$;

-- Summary
DO $$
BEGIN
    RAISE NOTICE '============================================';
    RAISE NOTICE 'Credit Management Tests Complete!';
    RAISE NOTICE '============================================';
    RAISE NOTICE 'If all tests passed, the system is ready to:';
    RAISE NOTICE '1. Create invoices from cases';
    RAISE NOTICE '2. Track credit for both customers and clients';
    RAISE NOTICE '3. Use unified billable_parties view';
    RAISE NOTICE '============================================';
END$$;
