-- Diagnostic function to check stores linked to a user
-- Helps troubleshoot "No stores found" issue in store score widget
--
-- Parameters:
--   p_user_id: UUID of the user to check
--   p_customer_id: Optional customer ID to filter
--
-- Returns: Diagnostic information about user's stores

CREATE OR REPLACE FUNCTION diagnose_user_stores(
    p_user_id UUID,
    p_customer_id INTEGER DEFAULT NULL
)
RETURNS TABLE (
    diagnostic_type TEXT,
    value_count BIGINT,
    details TEXT
)
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        'Total stores linked'::TEXT,
        COUNT(*)::BIGINT,
        'Stores in store_user_link for this user'::TEXT
    FROM store_user_link
    WHERE user_id = p_user_id
    AND (p_customer_id IS NULL OR customer_id = p_customer_id)
    
    UNION ALL
    
    SELECT 
        'Active stores (active=1)'::TEXT,
        COUNT(*)::BIGINT,
        'Stores with active = 1'::TEXT
    FROM store_user_link
    WHERE user_id = p_user_id
    AND active = 1
    AND (p_customer_id IS NULL OR customer_id = p_customer_id)
    
    UNION ALL
    
    SELECT 
        'Inactive stores (active=0)'::TEXT,
        COUNT(*)::BIGINT,
        'Stores with active = 0'::TEXT
    FROM store_user_link
    WHERE user_id = p_user_id
    AND active = 0
    AND (p_customer_id IS NULL OR customer_id = p_customer_id)
    
    UNION ALL
    
    SELECT 
        'Stores with NULL active'::TEXT,
        COUNT(*)::BIGINT,
        'Stores where active IS NULL'::TEXT
    FROM store_user_link
    WHERE user_id = p_user_id
    AND active IS NULL
    AND (p_customer_id IS NULL OR customer_id = p_customer_id)
    
    UNION ALL
    
    SELECT 
        'Unique customer IDs'::TEXT,
        COUNT(DISTINCT customer_id)::BIGINT,
        'Number of different customers for this user'::TEXT
    FROM store_user_link
    WHERE user_id = p_user_id
    AND (p_customer_id IS NULL OR customer_id = p_customer_id)
    
    UNION ALL
    
    SELECT 
        'Sample store codes'::TEXT,
        COUNT(*)::BIGINT,
        COALESCE(
            (SELECT string_agg(store_code, ', ' ORDER BY store_code)
             FROM (SELECT DISTINCT store_code FROM store_user_link 
                   WHERE user_id = p_user_id 
                   AND active = 1 
                   AND (p_customer_id IS NULL OR customer_id = p_customer_id)
                   LIMIT 5) sub),
            'None'
        )::TEXT
    FROM store_user_link
    WHERE user_id = p_user_id
    AND active = 1
    AND (p_customer_id IS NULL OR customer_id = p_customer_id)
    LIMIT 1;
END;
$$;

-- Grant execute permission
GRANT EXECUTE ON FUNCTION diagnose_user_stores(UUID, INTEGER) TO authenticated;

-- Comment
COMMENT ON FUNCTION diagnose_user_stores IS 'Diagnostic function to check stores linked to a user. Helps troubleshoot store score widget issues';

